Java基础：数据库（下）

于 2014-07-27 02:24:17 发布
阅读量591 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hsdsmljj/article/details/37767919
版权

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

idclassroom_idnamelocation
144044-4044th
233033-3033rd
322022-2022nd
411011-1011st


table_subject

idsubject_idnamelevelteacher_id
1CSC2014001Database SystemsB-2CST2014002
2CSC2014002C Programming LanguageB-1CST2014001
3CSC2014003Operating SystemsB-3CST2014004
4CSC2014004Object-Oriented ProgrammingB-1CST2014003


table_teacher

idteacher_idnameagegenderposition
1CST2014001Duncan481Prof.
2CST2014002Julie302A.Prof.
3CST2014003Wong401A.Prof.
4CST2014004Kim422Prof.


table_student

idstudent_idnameagegender
1CS2014001John181
2CS2014002Mary172
3CS2014003Jack181
4CS2014004Tom181
5CS2014005Lucy182
6CS2014006Roy171


table_exam_result

idsubject_idstudent_idmarktimeclassroom_idteacher_id
1CSC2014002CS2014001802014-06-204404CST2014001
2CSC2014002CS2014002702014-06-204404CST2014001
3CSC2014002CS2014003602014-06-204404CST2014001
4CSC2014002CS2014004502014-06-204404CST2014001
5CSC2014002CS2014005402014-06-204404CST2014001
6CSC2014002CS2014006452014-06-204404CST2014001
7CSC2014004CS2014001752014-06-213303CST2014004
8CSC2014004CS2014003452014-06-213303CST2014004
9CSC2014004CS2014005652014-06-213303CST2014004
10CSC2014001CS2014002332014-06-152202CST2014002
11CSC2014001CS2014004602014-06-152202CST2014002
12CSC2014003CS2014001652014-06-101101CST2014003
13CSC2014003CS2014004502014-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,
	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
但业务不需要这么多字段，因此可以调整为： 

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
在左联接的辅表部分，可以查询出一些字段不用于返回结果集，而用于联表条件（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_idname
1D1000nullCEO Office
2D1100D1000Sales 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 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';


数据库基础知识请看： 《JAVA基础：数据库（上）》
hsdsmljj
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值