数据库DML

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
)) A