首页 > 代码库 > 数据库 dml
数据库 dml
一. SQL语言
1.DDL 数据库模式定义语言(create alter drop __对对象操作)
2.DML 数据操纵语言 (insert delete update)
3.DCL 数据控制语言 (grant revoke)
4.流程控制语言(if while)
二.
输出记录前几条 用limit mysql不支持top
返回天数 datediff(Startdatetime.EndDatetime)
练习题:
CREATE DATABASE student
USE student
CREATE TABLE s(sno CHAR(2) NOT NULL ,
sname CHAR(10) NULL,
sex CHAR(2) NULL DEFAULT ‘男‘ ,
birthday DATETIME NULL,
id CHAR(18) NULL
);
CREATE TABLE c (cno CHAR(2) NOT NULL ,
cname CHAR(10) NULL, PRIMARY KEY(cno));
CREATE TABLE sc(sno CHAR(2) NOT NULL ,
cno CHAR(2) NOT NULL ,
score DECIMAL(5,1) NULL , PRIMARY KEY(sno,cno),
FOREIGN KEY(sno) REFERENCES s(sno) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(cno) REFERENCES c(cno),
CHECK(score>=0 AND score<=100)
);
CREATE TABLE test2(
FNumber CHAR(3) NOT NULL,
FAmount INT
);
INSERT INTO test2 VALUES(‘Rk1‘,10);
INSERT INTO test2 VALUES(‘Rk2‘,20);
INSERT INTO test2 VALUES(‘Rk3‘,-30);
INSERT INTO test2 VALUES(‘Rk4‘,-10);
SELECT FNumber,
(CASE WHEN FAmount>0 THEN FAmount
ELSE 0
END) AS 收入,
(CASE WHEN FAmount<0 THEN ABS(FAmount)
ELSE 0
END) AS 支出
FROM test2;
CREATE TABLE test3
(
dates DATE NOT NULL,
sname VARCHAR(8),
score CHAR(2)
)
INSERT INTO test3 VALUES (‘2008-8-8‘,‘拜仁‘,‘胜‘);
INSERT INTO test3 VALUES (‘2008-8-9‘,‘奇才‘,‘胜‘);
INSERT INTO test3 VALUES (‘2008-8-9‘,‘湖人‘,‘胜‘);
INSERT INTO test3 VALUES (‘2008-8-10‘,‘拜仁‘,‘负‘);
INSERT INTO test3 VALUES (‘2008-8-10‘,‘拜仁‘,‘负‘);
INSERT INTO test3 VALUES (‘2008-8-12‘,‘奇才‘,‘胜‘);
SELECT sname,
SUM(CASE
WHEN score=‘胜‘ THEN 1
ELSE 0
END) AS 胜 ,
SUM(CASE
WHEN score=‘负‘ THEN 1
ELSE 0
END) AS 负
FROM test3
GROUP BY sname;
CREATE TABLE CallRecords
(
id INT AUTO_INCREMENT PRIMARY KEY,
CallNumber CHAR(3) NOT NULL,
TelNum CHAR(20),
StartDatetime DATETIME,
EndDatetime DATETIME
)
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘001‘,‘11111111‘,‘2011-2-3 10:33‘,‘2011-2-3 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘001‘,‘01111111‘,‘2011-2-3 13:33‘,‘2011-2-3 14:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘001‘,‘11111111‘,‘2011-2-3 15:33‘,‘2011-2-3 15:55‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘002‘,‘01111111‘,‘2011-2-4 10:33‘,‘2011-2-4 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘002‘,‘11111111‘,‘2011-2-5 10:33‘,‘2011-2-5 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘003‘,‘11111111‘,‘2011-2-6 10:33‘,‘2011-2-6 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘001‘,‘01111111‘,‘2011-2-7 10:33‘,‘2011-2-7 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘004‘,‘11111111‘,‘2011-2-8 10:33‘,‘2011-2-8 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘002‘,‘01111111‘,‘2011-2-9 10:33‘,‘2011-2-9 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘001‘,‘11111111‘,‘2011-2-10 10:33‘,‘2011-2-10 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘005‘,‘11111111‘,‘2011-2-11 10:33‘,‘2011-2-11 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘005‘,‘01111111‘,‘2011-2-12 10:33‘,‘2011-2-12 10:39‘);
INSERT INTO CallRecords(CallNumber,TelNum,StartDatetime,EndDatetime) VALUES(‘001‘,‘11111111‘,‘2011-2-13 10:33‘,‘2011-2-13 10:39‘);
SELECT * FROM CallRecords
ORDER BY TIMESTAMPDIFF(SECOND,startdatetime,enddatetime) DESC
LIMIT 5;
SELECT SUM(TIMESTAMPDIFF (SECOND,startdatetime,enddatetime))
FROM CallRecords
WHERE TelNum LIKE ‘0%‘
SELECT CallNumber FROM CallRecords
WHERE MONTH(startdatetime)=MONTH (NOW())
ORDER BY TIMESTAMPDIFF(SECOND,startdatetime,enddatetime) DESC
LIMIT 0,3;
SELECT CallNumber, COUNT(*) FROM CallRecords
WHERE MONTH(startdatetime)=MONTH (NOW())
GROUP BY CallNumber
ORDER BY COUNT(*) DESC
LIMIT 3 ;
USE student
SELECT CallNumber,TelNum,TIMESTAMPDIFF (SECOND ,StartDatetime,EndDatetime) FROM CallRecords
UNION ALL
SELECT ‘汇总‘,
SUM ((
CASE
WHEN TelNum NOT LIKE ‘0%‘ THEN TIMESTAMPDIFF (SECOND ,StartDatetime,EndDatetime)
ELSE 0
END
)) AS 市内通话,
SUM ((
CASE
WHEN TelNum LIKE ‘0%‘ THEN TIMESTAMPDIFF (SECOND ,StartDatetime,EndDatetime)
ELSE 0
END
)) AS 长途通话
FROM CallRecords