常见DML
通常意义上的DML指的CRUD,即Create、Retrieve、Update、Delete,对应的SQL分别为insert、select、update、delete。
INSERT
INSERT INTO [表名] ([列名1,列名2...]) VALUES([值1,值2...]);
INSERT INTO [表名] VALUES(值1,值2...);
INSERT INTO [表名] (列名1,列名2....) SELECT * FROM [源数据表] {WHERE [过滤条件]};
UPDATE
UPDATE [表名] SET [列名1=值1],[列名2=值2]... {WHERE [过滤条件]};
DELETE
若开启了日志,DELETE后的数据可以通过日志找回。
DELETE FROM [表名] {WHERE [过滤条件]};
TRUNCATE
清空数据,不留日志。
TRUNCATE TABLE [表名];
*DROP
删除表结构。
DROP TABLE [表名];
SELECT
投影查询:
SELECT [{表名.}列1,{表名.}列2...] FROM [表名];
SELECT * FROM [表名];
别名:
SELECT [表别名].[列1] {AS} [列1别名],[表别名].[列2] {AS} [列2别名] FROM [表名] {AS} [表别名];
计算:
SELECT id,name,(mark*ifpassed) AS marks FROM exams ;
连接字串:
--oracle SELECT (FirstName+' '+LastName) AS FullName FROM People; SELECT id || name AS result from users;
--通用 SELECT concat(id,name) from users; SELECT concat(id,concat(name,age) as n_a) as i_n_a from users;
去重复:
SELECT DISTINCT [列名1],[列名2]... FROM [表名]; SELECT DISTINCT name FROM users;
限定行:
--oracle SELECT name FROM users WHERE rownum <=3;
--mysql SELECT name FROM users LIMIT 3;
升降序排列:
SELECT * FROM users ORDER BY id ASC; SELECT * FROM users ORDER BY id DESC; SELECT * FROM users ORDER BY id,age DESC;
条件过滤:
单条件:
SELECT * FROM users WHERE id = 1;
多条件:AND/OR/NOT/BETWEEN AND/IN:
SELECT * FROM users WHERE gender = 'male' AND age > 18; SELECT * FROM users WHERE age < 18 OR age > 60; SELECT * FROM users WHERE job IS NOT NULL; SELECT * FROM users WHERE NOT gender != 'female'; SELECT * FROM users WHERE birthday BETWEEN TO_DATE('1990-1-1','yyyy-mm-dd') AND TO_DATE('2000-12-31','yyyy-mm-dd'); SELECT * FROM users WHERE id IN (1,2,3);
模糊查询:
--匹配任意位 SELECT * FROM users WHERE name LIKE '%Tom%';
--匹配一位 SELECT * FROM users WHERE name LIKE '_Tom_';
--特殊字符匹配,定义转义符,转义符后面的为待匹配的特殊字符 --匹配以'%'结尾的记录,以t做转义符 SELECT * FROM users WHERE name LIKE '%t%' ESCAPE 't'; --匹配包含'%'的记录 SELECT * FROM users WHERE name LIKE '%t%%' ESCAPE 't';
DML执行顺序
from 表名:生成临时表。
where 条件:筛选临时表。
group by 列名:进行分组。
having 条件:组过滤。
select 列名,函数:分拣出需要的数据。
order by 列名:进行排序。
聚合函数
所谓聚合函数,就是对结果集进行汇总等操作,每次聚合返回的是一行计算的值。
COUNT:
计算结果集中所有记录的数目。
SELECT COUNT(*) FROM [表名] {WHERE [过滤条件]};
--计算某列非空的记录数,默认,ALL关键字可省略 SELECT COUNT(ALL [列名]) FROM [表名] {WHERE [过滤条件]};
--计算某列非重复的记录数 SELECT COUNT(DISTINCT [列名]) FROM [表名] {WHERE [过滤条件]};
SUM/AVG/MAX/MIN:
使用求和、求均值、求最大最小值函数时,必须指定列名。SELECT SUM({ALL} [列名]) FROM [表名] {WHERE [过滤条件]}; SELECT AVG({ALL} [列名]) FROM [表名] {WHERE [过滤条件]}; SELECT MAX({ALL} [列名]) FROM [表名] {WHERE [过滤条件]}; SELECT MIN({ALL} [列名]) FROM [表名] {WHERE [过滤条件]};
汇总分组
当使用了聚合函数而不分组,SELECT若选中其他列名,将只返回一条记录。
使用了聚合函数后,通常需要配合使用GROUP BY来对结果集进行分组,每一条聚合结果对应每一组。
目的:
利用聚合函数,汇总某一类具有共同点的记录,直接呈现给需要的应用程序。通常是业务需求,譬如显示某一类商品在全年的总销售量。
条件控制:
1.通过聚合函数的结果来过滤,而不是过滤实际数值。
2.WHERE在HAVING前起作用。
3.WHERE不能过滤聚合函数的结果,而HAVING可以。
查询限制:
只能查询:
1.分组的字段
2.聚合函数
实例
表结构:
初始数据:
table_classroom
id classroom_id name location 1 4404 4-404 4th 2 3303 3-303 3rd 3 2202 2-202 2nd 4 1101 1-101 1st
table_subject
id subject_id name level teacher_id 1 CSC2014001 Database Systems B-2 CST2014002 2 CSC2014002 C Programming Language B-1 CST2014001 3 CSC2014003 Operating Systems B-3 CST2014004 4 CSC2014004 Object-Oriented Programming B-1 CST2014003
table_teacher
id teacher_id name age gender position 1 CST2014001 Duncan 48 1 Prof. 2 CST2014002 Julie 30 2 A.Prof. 3 CST2014003 Wong 40 1 A.Prof. 4 CST2014004 Kim 42 2 Prof.
table_student
id student_id name age gender 1 CS2014001 John 18 1 2 CS2014002 Mary 17 2 3 CS2014003 Jack 18 1 4 CS2014004 Tom 18 1 5 CS2014005 Lucy 18 2 6 CS2014006 Roy 17 1
table_exam_result
id subject_id student_id mark time classroom_id teacher_id 1 CSC2014002 CS2014001 80 2014-06-20 4404 CST2014001 2 CSC2014002 CS2014002 70 2014-06-20 4404 CST2014001 3 CSC2014002 CS2014003 60 2014-06-20 4404 CST2014001 4 CSC2014002 CS2014004 50 2014-06-20 4404 CST2014001 5 CSC2014002 CS2014005 40 2014-06-20 4404 CST2014001 6 CSC2014002 CS2014006 45 2014-06-20 4404 CST2014001 7 CSC2014004 CS2014001 75 2014-06-21 3303 CST2014004 8 CSC2014004 CS2014003 45 2014-06-21 3303 CST2014004 9 CSC2014004 CS2014005 65 2014-06-21 3303 CST2014004 10 CSC2014001 CS2014002 33 2014-06-15 2202 CST2014002 11 CSC2014001 CS2014004 60 2014-06-15 2202 CST2014002 12 CSC2014003 CS2014001 65 2014-06-10 1101 CST2014003 13 CSC2014003 CS2014004 50 2014-06-10 1101 CST2014003
例1:在考试结果表中分别查询出三门考试所有学生的平均分。
未分组汇总时,需要三条SQL:
SELECT AVG(MARK) FROM table_exam_result WHERE subject_id = 'CSC2014001'; SELECT AVG(MARK) FROMtable_exam_resultWHERE subject_id = 'CSC2014002'; SELECT AVG(MARK) FROMtable_exam_result WHERE subject_id = 'CSC2014003';
若要一次完成查询,需要进行分组汇总操作。
根据业务需求,此处按科目ID subject_id字段分组:
--GROUP BY分组后可以SELECT出的列:1.分组依据的字段,2.聚合函数 SELECT subject_id, AVG(mark) AS Average_Mark FROM table_exam_result GROUP BY subject_id;
例2:在考试结果表中分别查询出所有考生的总分,平均分。
SELECT student_id, SUM(mark), AVG(mark) FROM table_exam_result GROUP BY student_id;
例3:在考试结果表中查询出所有学生的高分(不低于70分的)科目数。--1.以学生ID student_id分组,2.需要WHERE过滤。 SELECT student_id, COUNT(*) AS Excellent_subjects FROM table_exam_result WHERE mark >= 70 GROUP BY student_id;
例4:在表考试结果表中查询出所有学生的不及格科目数。
SELECT student_id, COUNT(*) AS Failed_subjects FROM table_exam_result WHERE mark < 60 GROUP BY student_id;
例5:所有学生的均分。
SELECT student_id, AVG(mark) AS Average_marks FROM table_exam_result GROUP BY student_id;
例6:参考的总学生数。
当然,此例也可以不进行分组汇总:SELECT COUNT(*) AS total_students FROM ( SELECT student_id FROM table_exam_result GROUP BY student_id ) AS all_s;
SELECT COUNT(DISTINCT student_id) AS total_students FROM table_exam_result;
例7:考试总科目数。
同上例,此例也可以不分组:SELECT COUNT(*) AS total_subjects FROM ( SELECT subject_id FROM table_exam_result GROUP BY subject_id ) AS all_s;
SELECT COUNT(DISTINCT subject_id) AS total_students FROM table_exam_result;
例8:所有考生总分并排序。SELECT student_id, SUM(mark) AS total_marks FROM table_exam_result GROUP BY student_id ORDER BY total_marks DESC;
HAVING关键字:
对分组结果(聚合函数)的过滤。
例9:查询出不及格科目数两门(含)以上者:
SELECT student_id FROM table_exam_result WHERE mark < 60 GROUP BY student_id HAVING COUNT(*) >= 2;
子查询
嵌套的SELECT查询,主查询需要使用子查询所返回的结果集。
相关子查询:
依赖于外部查询的数据,外部查询每返回一行,子查询执行一次。查询次数:1+N。
SELECT [列名1],(子查询) AS [列名2] FROM [表名];
上例中,查询出不及格的考试条目,若需要显示学生姓名、考试名称,则可以使用相关子查询:
SELECT student_id, (SELECT name FROM table_student WHERE table_student.student_id = table_exam_result.student_id) AS student_name, subject_id, (SELECT name FROM table_subject WHERE table_subject.subject_id = table_exam_result.subject_id) AS subject_name, mark FROM table_exam_result WHERE mark < 60;
非相关子查询:
独立于外部查询的子查询,通常在FROM或WHERE后,子查询只有在进行条件筛选时执行一次。查询次数:1+1。
SELECT [列名1],[列名2],... FROM 表名 WHERE [列名2] IN (子查询);
SELECT * FROM (子查询) {WHERE [过滤条件]};
上例中,若用非相关子查询查询有不及格科目的学生姓名:
SELECT student_id,name FROM table_student WHERE student_id IN (SELECT student_id FROM table_exam_result WHERE mark < 60);
EXISTS运算符:
总是跟随着一个子查询,判断子查询是否返回任何结果,若子查询返回任意行则运算结果为真。
例:查询任意一门考试成绩低于40分的所有学生ID及姓名:
--相关子查询 SELECT s.student_id, s.name FROM table_student AS s WHERE EXISTS( SELECT e.student_id FROM table_exam_result AS e WHERE e.mark < 40 AND e.student_id = s.student_id );
ALL运算符:
子查询结果集的所有行均满足条件则运算结果为真。
例1:查询总分第一名的学生ID及总分:
--第一名的总分会大于或等于(其自身)任何一个学生,故用>=ALL来找出第一名。 SELECT student_id, SUM(mark) AS total_marks FROM table_exam_result GROUP BY student_id HAVING SUM(mark) >= ALL( SELECT SUM(mark) FROM table_exam_result GROUP BY student_id );
例2:查询总分为最后一名的学生ID及总分:
--最后一名的总分会大于或等于(其自身)任何一个学生,故用<=ALL来找出最后一名。 SELECT student_id, SUM(mark) FROM table_exam_result GROUP BY student_id HAVING SUM(mark) <= ALL( SELECT SUM(mark) FROM table_exam_result GROUP BY student_id );
ANY运算符:
子查询结果集至少有一行满足条件则运算结果为真。
:例1:查询除最后一名以外的所有学生ID及总分:
--最后一名总分不会大于任何一个学生(包括其自身),因此用>ANY来过滤掉最后一名 SELECT student_id, SUM(mark) FROM table_exam_result GROUP BY student_id HAVING SUM(mark) > ANY( SELECT SUM(mark) FROM table_exam_result GROUP BY student_id );
例2:查询除第一名以外的所有学生ID及总分:
--只有第一名总分不会小于任何一个学生(包括其自身),因此用<ANY来过滤掉第一名 SELECT student_id, SUM(mark) FROM table_exam_result GROUP BY student_id HAVING SUM(mark) < ANY( SELECT SUM(mark) FROM table_exam_result GROUP BY student_id );
多表查询
组合查询:
两表的字段【类型】及【数目】需要对应,方式包括UNION,INTERSECT,EXCEPT。
例:查询出所有学生及老师的ID及姓名。
SELECT student_id, name FROM table_student UNION SELECT teacher_id,name FROM table_teacher;
默认为去重复组合,e.g.{1,2,3,4} UNION {3,4,5} = {1,2,3,4,5}
*UNION ALL,不去重复,e.g.{1,2,3,4} UNION {3,4,5} = {1,2,3,4,3,4,5}
INTERSECT:交集,e.g.{1,2,3,4} INTERSECT {3} = {3}
EXCEPT(oracle为MINUS):差集,e.g.{1,2,3,4} EXCEPT {3,4,5,6} = {1,2,5,6}
联表查询:
弥补了子查询的缺点:复杂,性能差(相关子查询)。JOIN关键字指定要联的表,ON关键字指定联表条件。
1.内联接(INNER JOIN)
两表均有相关数据的条目才会被查询出,具有排他性(exclusive)。
例:查询出所有考试成绩信息,显示学生ID,学生姓名,科目ID,科目名,分数。
SELECT e.student_id, st.name, e.subject_id, s.name, e.mark FROM table_exam_result AS e INNER JOIN table_student AS st ON e.student_id = st.student_id INNER JOIN table_subject AS s ON e.subject_id = s.subject_id;
2.外联接(OUTER JOIN)
左外联接(LEFT OUTER JOIN)
左表(主表)满足条件的全部条目+右边(辅表)关联条目,辅表中不存在关联的条目中的字段以null表示。
结果集条目数=左表结果集条目数。
例:查询出所有学生的考试成绩,显示学生ID,学生姓名,科目ID,分数。
SELECT s.student_id, s.name, e.subject_id, e.mark FROM table_student AS s LEFT JOIN table_exam_result AS e ON s.student_id = e.student_id;
*当直接联表名时,等价于:
但业务不需要这么多字段,因此可以调整为:SELECT s.student_id, s.name, subject_id, mark FROM table_student AS s LEFT JOIN (SELECT * FROM table_exam_result) AS e ON s.student_id = e.student_id
在左联接的辅表部分,可以查询出一些字段不用于返回结果集,而用于联表条件(ON)。SELECT s.student_id, s.name, subject_id, mark FROM table_student AS s LEFT JOIN (SELECT student_id,subject_id,mark FROM table_exam_result) AS e ON s.student_id = e.student_id
右外联接(RIGHT OUTER JOIN)
左联接反过来,右表(主表)满足条件的全部条目+左边(辅表)关联条目,辅表中不存在关联的条目中的字段以null表示。
结果集条目数=右表结果集条目数。
SELECT s.student_id, s.name, e.subject_id, e.mark FROM table_exam_result AS e RIGHT JOIN table_student AS s ON s.student_id = e.student_id;
全外联接(FULL OUTER JOIN):
mysql不直接支持,左外+右外,左表及右表中满足条件的全部条目,两表中不存在关联的条目中的字段以null表示。
结果集条目数=左表结果集条目数+右表结果集条目数-非空条目。
SELECT s.student_id, s.name, e.subject_id, e.mark FROM table_exam_result AS e FULL JOIN table_student AS s ON s.student_id = e.student_id;
数据库基础知识请看: 《JAVA基础:数据库(上)》
3.交叉联接:
两表中满足条件的全部条目(笛卡尔积),全部显示,不需要ON联表条件。
结果集条目数=左表结果集条目数*右表结果集条目数
SELECT s.student_id, s.name, e.student_id, e.mark FROM table_exam_result AS e CROSS JOIN table_student AS s
*自联接:
用外联接实现,用于同一个表中有关联条目,存在树形结构。
如下表table_dept,使用自联接查询出所有部门的ID,名字,上级部门ID,上级部门名字:
id dept_id parent_id name 1 D1000 null CEO Office 2 D1100 D1000 Sales Dept. 3 D1200 D1000 I.T. Dept. 4 D1300 D1000 Financial Dept. 5 D1101 D1100 International Sales Dept. 6 D1102 D1100 Domestic Sales Dept. 7 D1201 D1200 Network Dept. 8 D1202 D1200 Software Dept. --t作主表,parent作上级表 SELECT t.dept_id,t.name,parent.dept_id AS parent_id,parent.name AS parent_name FROM table_dept AS t LEFT JOIN table_dept AS parent ON parent.dept_id = t.parent_id;
联表数据过滤:
与其他的过滤一样,可以使用WHERE进行条件过滤。需要注意的是,原则上应该先联表,再过滤。
如下显示学生ID为CS2014004的学生的姓名及考试总分。
SELECT s.student_id, s.name, SUM(e.mark) FROM table_student AS s LEFT JOIN table_exam_result AS e ON s.student_id = e.student_id WHERE s.student_id = 'CS2014004';