Java基础：数据库（下）

常见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 ;

连接字串：

--oracleSELECT (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;

限定行：

--oracleSELECT name FROM users WHERE rownum <=3;

--mysqlSELECT 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

idclassroom_idnamelocation144044-4044th233033-3033rd322022-2022nd411011-1011st

table_subject

idsubject_idnamelevelteacher_id1CSC2014001Database SystemsB-2CST20140022CSC2014002C Programming LanguageB-1CST20140013CSC2014003Operating SystemsB-3CST20140044CSC2014004Object-Oriented ProgrammingB-1CST2014003

table_teacher

idteacher_idnameagegenderposition1CST2014001Duncan481Prof.2CST2014002Julie302A.Prof.3CST2014003Wong401A.Prof.4CST2014004Kim422Prof.

table_student

idstudent_idnameagegender1CS2014001John1812CS2014002Mary1723CS2014003Jack1814CS2014004Tom1815CS2014005Lucy1826CS2014006Roy171

table_exam_result

idsubject_idstudent_idmarktimeclassroom_idteacher_id1CSC2014002CS2014001802014-06-204404CST20140012CSC2014002CS2014002702014-06-204404CST20140013CSC2014002CS2014003602014-06-204404CST20140014CSC2014002CS2014004502014-06-204404CST20140015CSC2014002CS2014005402014-06-204404CST20140016CSC2014002CS2014006452014-06-204404CST20140017CSC2014004CS2014001752014-06-213303CST20140048CSC2014004CS2014003452014-06-213303CST20140049CSC2014004CS2014005652014-06-213303CST201400410CSC2014001CS2014002332014-06-152202CST201400211CSC2014001CS2014004602014-06-152202CST201400212CSC2014003CS2014001652014-06-101101CST201400313CSC2014003CS2014004502014-06-101101CST2014003

例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,markFROM 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 sLEFT JOIN (SELECT * FROM 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 sLEFT JOIN (SELECT student_id,subject_id,mark FROM table_exam_result) AS e       ON s.student_id = e.student_id

在左联接的辅表部分，可以查询出一些字段不用于返回结果集，而用于联表条件（ON）。

右外联接（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;

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，上级部门名字：

iddept_idparent_idname1D1000nullCEO Office2D1100D1000Sales Dept.3D1200D1000I.T. Dept.4D1300D1000Financial Dept.5D1101D1100International Sales Dept.6D1102D1100Domestic Sales Dept.7D1201D1200Network Dept.8D1202D1200Software Dept.

--t作主表，parent作上级表SELECT t.dept_id,t.name,parent.dept_id AS parent_id,parent.name AS parent_name FROM table_dept AS tLEFT JOIN table_dept AS parentON 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_idWHERE s.student_id = 'CS2014004';

数据库基础知识请看：《JAVA基础：数据库（上）》