MySQL基础笔记(三) 复杂查询
来源:互联网 发布:网络口语培训哪家好 编辑:程序博客网 时间:2024/04/29 03:28
所谓复杂查询,指涉及多个表、具有嵌套等复杂结构的查询。这里简要介绍典型的几种复杂查询格式。
一、连接查询
连接是区别关系与非关系系统的最重要的标志。通过连接运算符可以实现多个表查询。连接查询主要包括内连接、外连接等。
假设有Student
和Grade
两个表如下:
+-----+--------+-------+ +-----+------------+--------+| sID | sName | sDept | | gID | gCourse | gScore |+-----+--------+-------+ +-----+------------+--------+| 1 | Paul | CS | | 1 | Math | 87 || 2 | Oliver | MS | | 2 | English | 95 || 3 | Jack | SE | | 3 | Physics | 76 || 4 | Robin | CS | | 7 | Philosophy | 76 |+-----+--------+-------+ +-----+------------+--------+
1.1 内连接
内连接(INNER JOIN)使用比较运算符进行表间列数据的比较操作,并列出这些表中与连接条件相匹配的数据行,组合成新的记录。
当比较操作符是=
时,称为等值连接:
SELECT * FROM Student INNER JOIN Grade ON Student.sID = Grade.gID;
等价于
SELECT * FROM Student,Grade WHERE Student.sID = Grade.gID;
结果如下:
+-----+--------+-------+-----+---------+--------+| sID | sName | sDept | gID | gCourse | gScore |+-----+--------+-------+-----+---------+--------+| 1 | Paul | CS | 1 | Math | 87 || 2 | Oliver | MS | 2 | English | 95 || 3 | Jack | SE | 3 | Physics | 76 |+-----+--------+-------+-----+---------+--------+
可以看出,在内连接查询中,只有满足条件的记录才能出现在结果关系中。
1.2 外连接
与内连接不同的是,外连接返回的查询结果集中不仅包含符合连接条件的行,而且还包括左表(左连接)、右表(右连接)或两个表(全外连接)中的所有数据行。
1.2.1 左连接
LEFT JOIN(左连接),即LEFT OUTER JOIN
,返回左表的全部记录,即使右表中没有对应匹配记录。
SELECT * FROM Student LEFT JOIN Grade ON Student.sID = Grade.gID;
结果如下:
+-----+--------+-------+------+---------+--------+| sID | sName | sDept | gID | gCourse | gScore |+-----+--------+-------+------+---------+--------+| 1 | Paul | CS | 1 | Math | 87 || 2 | Oliver | MS | 2 | English | 95 || 3 | Jack | SE | 3 | Physics | 76 || 4 | Robin | CS | NULL | NULL | NULL |+-----+--------+-------+------+---------+--------+
1.2.2 右连接
RIGHT JOIN(右连接),即RIGHT OUTER JOIN
,返回右表的全部记录,即使左表中没有对应匹配记录。
SELECT * FROM Student RIGHT JOIN Grade ON Student.sID = Grade.gID;
结果如下:
+------+--------+-------+-----+------------+--------+| sID | sName | sDept | gID | gCourse | gScore |+------+--------+-------+-----+------------+--------+| 1 | Paul | CS | 1 | Math | 87 || 2 | Oliver | MS | 2 | English | 95 || 3 | Jack | SE | 3 | Physics | 76 || NULL | NULL | NULL | 7 | Philosophy | 76 |+------+--------+-------+-----+------------+--------+
1.2.3 全连接
FULL JOIN(全连接),即FULL OUTER JOIN
,返回左表、右表的全部记录,即使没有对应的匹配记录。
**注意:**MySQL不支持FULL JOIN
,不过可以通过UNION
关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟。
SELECT * FROM Student LEFT JOIN Grade ON Student.sID = Grade.gIDUNIONSELECT * FROM Student RIGHT JOIN Grade ON Student.sID = Grade.gID;
结果如下:
+------+--------+-------+------+------------+--------+| sID | sName | sDept | gID | gCourse | gScore |+------+--------+-------+------+------------+--------+| 1 | Paul | CS | 1 | Math | 87 || 2 | Oliver | MS | 2 | English | 95 || 3 | Jack | SE | 3 | Physics | 76 || 4 | Robin | CS | NULL | NULL | NULL || NULL | NULL | NULL | 7 | Philosophy | 76 |+------+--------+-------+------+------------+--------+
另外,如果在一个连接查询中涉及到的两个表是同一个表,这种查询称为自连接查询。为了防止产生二义性,自连接查询中通常对表使用别名。
二、子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在 SELECT 子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件。
子查询中常用的操作符有ANY
、SOME
、ALL
、EXISTS
、IN
,也可以使用比较运算符。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中,而且可以进行多层嵌套。
2.1 在条件表达式中产生标量的子查询
SELECT *FROM scoreWHERE id = (SELECT event_id FROM event WHERE date='2015-07-01' AND type='Q');
所谓标量,就是单个属性的一个原子值。当子查询出现在 WHERE 子句中的比较运算符(= ,>, >= ,< , <= ,<>)的右边,其输出结果应该只有一个才对。很容易理解,如果返回多条结果,就无法进行比较,系统就会报错。
又如:
SELECT * FROM teacher WHERE birth = MIN(birth); /*错误*/
这个查询是错的!因为MySQL不允许在子句里面使用统计函数,所以改用子查询:
SELECT *FROM teacherWHERE birth = (SELECT MIN(birth) FROM teacher);
2.2 在条件表达式中产生集合的子查询
如果子查询的输出是一个结果集合,可以通过 ANY、ALL、IN 进行比较。
2.2.1 ANY与SOME
ANY
和SOME
关键字是同义词,表示满足其中任一条件。它们允许创建一个表达式对子查询的返回结果集进行比较:
SELECT num1FROM t1WHERE num1 > ANY(SELECT num2 FROM t2);
上面的子查询返回 t2 的 num2 列,然后将 t1 中的 num1 值与之进行比较,只要大于 num2 的任何一个值,即为符合查询条件的结果。
等价于:
SELECT num1FROM t1WHERE num1 > SOME(SELECT num2 FROM t2);
2.2.2 ALL
与ANY/SOME
不同,使用ALL
时需要同时满足所有内层查询的条件。
SELECT num1FROM t1WHERE num1 > ALL(SELECT num2 FROM t2);
上面的子查询还是返回 t2 的 num2 列,然后将 t1 中的 num1 值与之进行比较。但是只有大于所有 num2 值的 num1 才是符合查询条件的结果。
2.2.3 IN
IN
关键字后接一个子查询,若在子查询结果集中,返回true,否则返回false。与之相对的是NOT IN
。
SELECT num1FROM t1WHERE num1 IN (SELECT num2 FROM t2);
2.3 在条件表达式中测试空/非空的子查询
EXISTS
关键字后接一个任意的子查询,系统对子查询进行运算以判断它是否返回行。
- 若至少返回一行,那么 EXISTS 的结果为 true,此时外层查询语句将进行查询;
- 若没有返回任何行,那么 EXISTS 的结果为 false,此时外层语句将不进行查询。
SELECT sNameFROM StudentWHERE EXISTS (SELECT * FROM Grade WHERE gScore < 60);
EXISTS
和NOT EXISTS
的结果只取决于是否会返回行,而不取决于这些行的内容。
2.4 关联子查询
一般的子查询只计算一次,其结果用于外层查询。但关联子查询需要计算多次。
子查询中使用了主查询中的某些字段,主查询每扫描一行都要执行一次子查询,这种子查询称为关联子查询(Correlated Subquery)。
SELECT sNameFROM StudentWHERE '450' NOT IN (SELECT courseID FROM Course WHERE sID = Student.sID);
上面的子查询中使用了 Student 表的 sID 字段。对于 Student 表中每一个 sID 都会执行一次子查询。
2.5 FROM子句中的子查询
子查询可以用括号括起来作为一个关系,从而出现在 FROM 列表中。由于子查询的结果关系没有正式的名字,故必须给它取一个别名。
SELECT *FROM Grade, (SELECT * FROM Student WHERE sDept='CS')xWHERE x.sID=Grade.gID;
x 就是子查询的结果关系的别名。
三、合并查询结果
利用UNION
或UNION ALL
关键字,可以将多个 SELECT 语句的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同。
- UNION:合并查询结果时,删除重复的记录,返回的行都是唯一的。
- UNION ALL:合并查询结果时,不删除重复行。
3.1 UNION ALL
SELECT * FROM Student UNION ALLSELECT * FROM Student;
结果如下:
+-----+--------+-------+| sID | sName | sDept |+-----+--------+-------+| 1 | Paul | CS || 2 | Oliver | MS || 3 | Jack | SE || 4 | Robin | CS || 1 | Paul | CS || 2 | Oliver | MS || 3 | Jack | SE || 4 | Robin | CS |+-----+--------+-------+
3.2 UNION
SELECT * FROM Student UNIONSELECT * FROM Student;
结果如下:
+-----+--------+-------+| sID | sName | sDept |+-----+--------+-------+| 1 | Paul | CS || 2 | Oliver | MS || 3 | Jack | SE || 4 | Robin | CS |+-----+--------+-------+
个人站点:http://songlee24.github.com
- MySQL基础笔记(三) 复杂查询
- MySQL基础笔记(三) 复杂查询
- Oracle复杂查询(三)
- SQL复杂查询(三)
- mysql基础条件查询(三)
- 数据库SQL Server2012笔记(三)——表的复杂查询
- mysql 复杂查询
- MySQL 复杂查询
- mysql复杂查询
- MySql--复杂查询
- MySQL之复杂查询
- Mysql进行复杂查询
- Mysql进行复杂查询
- MySQL复杂查询
- Oracle 学习笔记 复杂 查询(重点)
- 《MySQL必知必会》学习笔记七(复杂查询)------掌握部分
- MYSQL基础上机练习题(三) 数据查询
- MySql查询(三)
- Spring的编程式事务与声明式事务区别
- SQLSERVER2008 忘记SA密码且windows账号无法登录的解决办法
- iptables小总结
- mysql锁-for update
- Qt中解决 QLabel 点击事件通过qss+focuspolicy方法实现
- MySQL基础笔记(三) 复杂查询
- Android图形系统之Surface、SurfaceView、SurfaceHolder及SurfaceHolder.Callback之间的联系
- mysql锁和索引之间的关系
- OC大补之法
- 在Ubuntu 11.04中执行sudo apt-get update命令时出现404错误
- [Objective-C] 通过objc_get/setAssociatedObject 在category里加入成员变量
- List,set,Map 的用法和区别
- 【Java 学习笔记】 HashMultimap(guava)
- Android学习 18 ->网格控件GridView