MySQL学习记录（6）

学习内容：数据表的复杂查询

---

一、子查询

也叫嵌套查询，在一个查询语句中嵌套另一个查询。子查询中常用操作符有ANY、SOME、ALL、IN、EXISTS等。

1.带比较运算符

简单子查询的内层子查询通常作为搜索条件的一部分呈现在WHERE子句中。

SELECT 学号,姓名,性别,年龄 FROM student
WHERE 年龄=
(SELECT 年龄 FROM student WHERE 学号='101');
--查询学生表student中与学号为“101”的学生年龄相同的学生信息。先执行子查询，查找学号为‘101’的学生年龄，得出19，再执行主查询。

2.带IN关键字

使用IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层循环语句进行比较。

USE School；
SELECT 课程名 FROM course
WHERE 课程号 IN
(SELECT 课程号 FROM score WHERE 学号= '103');
--在score表中查询学号为“103”的学生参加考试的课程号，然后根据课程号查询其课程名称

3.带ANY关键字

通过使用比较运算符来连接ANY得到的结果，可用于比较某一列的值是否全都大于ANY后面子查询中的最小值，或者小于ANY后面子查询中的最大值。

USE school;
SELECT 课程号,学号,分数 FROM score
WHERE 课程号='C-105' and 分数>ANY
(SELECT 分数 FROM score WHERE 课程号='C-245')
ORDER BY 分数 DESC;
--查询课程编号为“C-105”课程且成绩至少高于课程编号为“C-245”课程的学生的课程号、学号和分数，并按分数从高到低次序排列

4.带ALL关键字

ALL关键字需要同时满足所有内层查询的条件。即大于最大值，小于最小值。

USE School；
SELECT  课程号,学号,分数 FROM score
WHERE 课程号='C-105' and 分数>ALL
(SELECT 分数 FROM score WHERE 课程号= 'C-245')
ORDER BY 分数 DESC;
--查询课程编号为“C-105”课程且成绩都高于课程编号为“C-245”课程的学生的课程号、学号和分数，并按分数从高到低次序排列

5.带SOME关键字

SOME与ANY用法相似，意义不同。SOME通常用于比较满足查询结果中的任意一个值。当在SOME运算符前面使用“=”时，就代表了IN关键字的用途。

USE School；
SELECT 课程名 FROM course
WHERE 课程号=SOME
(SELECT 课程号 FROM score WHERE 学号= '103');
--在score表中查询学号为“103”的学生参加考试的课程号，然后根据课程号查询其课程名称

6.带EXISTS关键字

EXISTS关键字应用于子查询中，返回的是一个布尔值。只要子句查询到数据，则返回true，进行外层查询；否则返回false，外层查询将不进行。EXISTS关键字用在WHERE 子句中。

USE School；
SELECT * FROM teacher a
WHERE EXISTS
(SELECT * FROM course b 
WHERE A.教师编号=B.任课老师编号);
--先查询课程表course中是否存在与教师表teacher中相等的教师编号，若存在则返回true,外层语句再对表teacher进行查询。

二、合并查询结果

将多个SELECT语句的查询结果合并起来显示。

每个SELECT语句之间需要用 UNION 或 UNION ALL 关键字来连接。

• UNION:将所有查询结果合并在一起，然后去掉相同的记录。
• UNION ALL: 只是简单合并在一起。

语法格式：

SELECT 语句1 
UNION|UNION ALL
SELECT 语句2
UNION|UNION ALL···
SELECT n;

USE School；
SELECT 姓名,性别,年龄 FROM student
UNION 
SELECT 姓名,性别,年龄 FROM teacher
ORDER BY 年龄;
--查询teacher表中与student表中的所有教师和学生的姓名、性别和年龄,并按照年龄进行排序

三、内连接查询

1.笛卡尔积查询

针对多表查询，没有指定的查询条件，查询的是多个表中的全部记录，返回到具体结果是每张表中列的和、行的积。
使用多表连接查询时，一定要设置查询条件，要尽量避免查询结果中笛卡尔积的产生，因为会降低数据库的访问效率。

USE School；
SELECT *FROM student,teacher;

2.内连接的简单查询

使用比较运算符比较被连接列的列值。

USE school；;
SELECT * FROM student INNER JOIN score
ON student.学号=score.学号;
--使用内连接查询学生信息表和考试成绩表

3.等值内连接查询

等值连接就是指表之间通过“等于”关系连接起来，产生一个连接临时表，然后对该临时表进行处理后生成最终结果。

USE School；
SELECT student.姓名,score.课程号,score.分数
FROM student,score
WHERE student.学号=score.学号;
--查询所有学生的姓名、课程号和分数列，先按照student.学号=score.学号条件将student和score表连接起来，产生一个临时表，然后从其中选择出student.姓名,score.课程号,score.分数。

USE School；
SELECT y.课程号, AVG(y.分数) AS '分数'
FROM student x,score y
WHERE x.学号=y.学号 AND x.班号='09031' AND y.分数 IS NOT NULL
GROUP BY y.课程号;
--查询“09031”班级所选课程的平均分

4.自连接的内连接查询

对同一个表进行连接，可以把一个表的某行与同一表中的另一行连接起来。

USE school；
SELECT s1.学号, s1.姓名, s1.性别
FROM student AS s1, student AS s2
WHERE s1.学号=s2.学号 AND s2.班号= '09031';
--查询班号=‘09031’的学生信息

5.带条件的内连接查询

在查询过程中，通过添加过滤条件限制查询的结果，使查询结果更加准确。

USE School；
SELECT x.课程号,x.学号,x.分数
FROM score x, score y
WHERE x.课程号='C-105' AND x.分数>y.分数 AND y.学号='105' AND y.课程号='C-105'
ORDER BY x.分数 DESC；
--查询选项C-105课程的成绩高于105号学生成绩的所有学生记录，并将成绩从高到低排序。

四、外连接查询

可以查询中符合条件的结果后还能显示出某张表中不符合条件的数据。
语法格式：

SELECT column_name1,column_name2,···
FROM table1 LEFT|RIGHT|JOIN table2
ON conditions;

• table1：数据表1，常被称为左表。
• table2：数据表2，常被成为右表。
• LEFT JOIN（左连接）：查询结果为符合条件部分+左表余下数据。
• RIGHT JOIN（右连接）：查询结果为符合条件部分+右表余下数据。
• ON conditions：条件，与WHERE子句后面的写法一样。

五、使用正则表达式查询

使用REGEXP关键字来匹配查询正则表达式。
语法格式：

字段名 REGEXP '匹配方式'

字符	描述
^	匹配字符串开始的位置
$	匹配字符串结束的位置
.	替代字符串中的任意一个字符，包括回车和换行
[字符集合]	匹配“字符集合”中的任何一个字符
[^字符集合]	匹配除了“字符集合”以外的任何一个字符
S1\S2\S3	匹配 S1,S2,S3 中的任意一个字符串
*	代表多个该符号之前的字符，包括0和1个
+	代表多个该符号之前的字符，包括1个
字符串{n}	字符串出现n次
字符串{m,n}	字符串出现至少m次，最多n次

USE school;
SELECT * FROM info WHERE name REGEXP '^L';
--从info表name字段中查询以字母“L”开头的记录
USE school;
SELECT * FROM info WHERE name REGEXP 'aaa$';
--从info表name字段中查询以字符串“aaa”结尾的记录
USE school;
SELECT * FROM info WHERE name REGEXP '^L..y$';
--从info表name字段中查询以字母“L”开头，以字母“y”结尾，中间有两个任意字符的记录
USE school;
SELECT * FROM info WHERE name REGEXP '[eoc]';
--从info表name字段中查询包含e、o、c3个字母中任意一个的记录
USE school;
SELECT * FROM info WHERE name REGEXP '[0-9a-c]';
--从info表name字段中查询包含数字或字母a、b、c的记录
USE school;
SELECT * FROM info WHERE name REGEXP '[^a-w0-9]';
--从info表name字段中查询包含“a”到“w”字母和数字以外的字符的记录
USE school;
SELECT * FROM info WHERE name REGEXP 'ic|uc|bd';
--从info表name字段中查询包含“ic、uc和bd”这3个字符串中任意一个的记录
USE school;
SELECT * FROM info WHERE name REGEXP 'a*c';
--从info表name字段中查询字母“c”之前出现过“a”的记录，“a*c”表示字母c之前有0个或多个a出现
USE school;
SELECT * FROM info WHERE name REGEXP 'a+c';
--从info表name字段中查询字母“c”之前出现过“a”的记录，“a+c”表示字母c之前至少有1个a出现
USE school;
SELECT * FROM info WHERE name REGEXP 'a{3}';
--从info表name字段中查询出现过“a”3次的记录
USE school;
SELECT * FROM info WHERE name REGEXP 'ab{1,3}';
--从info表name字段中查询出现过“ab”最少一次，最多3次的记录