数据库DML
来源:互联网 发布:淘宝登录不了怎么回事 编辑:程序博客网 时间:2024/06/06 11:44
2.DML 数据操纵语言
3.DCL 数据控制语言 (grant
4.流程控制语言(if while)
二.
输出记录前几条 用limit
练习题:
CREATE DATABASE student
USE student
CREATE TABLE s(sno CHAR(2) NOT NULL ,
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
FAmount
);
INSERT INTO test2 VALUES(\'Rk1\',10);
INSERT INTO test2 VALUES(\'Rk2\',20);
INSERT INTO test2 VALUES(\'Rk3\',-30);
INSERT INTO test2 VALUES(\'Rk4\',-10);
(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
(
)
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
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
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 市内通话,
CASE
WHEN TelNum LIKE \'0%\' THEN TIMESTAMPDIFF (SECOND ,StartDatetime,EndDatetime)
ELSE 0
END
)) A
- 数据库DML
- 数据库DML
- 数据库DML
- DML 数据库
- 数据库操纵语言DML
- 数据库操纵语言DML
- 数据库: DML、DDL、DCL
- 数据库-DML触发器
- 数据库DML语句
- 数据库常用DML修改
- 数据库:DDL、DML
- 数据库---DML触发器
- 数据库操纵语言DML
- 数据库 DDL","DML","DCL"
- MySQL数据库DML操作
- GP数据库DML语句
- 数据库DML语句insert
- 数据库操纵语言DML
- editplus中tab转换为空格
- EditPlus 快捷键
- excel时间和日期函数
- 【转载】c排序
- oracle学习笔记——视图、索引(转载)
- 数据库DML
- oracle 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常【转载】
- win7 双网卡内外网
- netsh命令配置网络dns、ip、gateway
- oracle游标--PREPARE
- pro*c动态SQL技术-游标
- 进程上下文与中断上下文
- Linux 代码格式化工具 indent
- 如何让Outlook接收邮件时不删除服务器上的邮件