SQL高级查询详解：从基础到内外联表查询全面解析

一、前言

SQL 是关系型数据库的标准语言，所有的关系型数据库都支持 SQL，比如 MySQL、Oracle、SQL Server、MS Access、DB2 等。不学习 SQL，就没法使用关系型数据库。SQL 仅仅是一种查询语言，它不是数据库，要想使用 SQL，您需要安装一款数据库。

有些数据库在标准 SQL 的基础上进行了扩展或者裁减，形成了不同的“方言”，比如：

• SQL Server 使用 T-SQL.
• Oracle 使用 PL/SQL；
• MS Access 使用的 SQL 被称为 JET SQL。

高级查询可以让我们的SQL语句更加灵活，完成更多的任务需求。

二、高级查询

做一下准备，为下面的查询操作建立数据库。

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=innoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=innoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=innoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=innoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=innoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

2.1、基础查询

-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT `sname`, `class_id` FROM student;
-- 别名 列明 不要用关键字
SELECT `sname` AS '姓名' , `class_id` AS '班级ID'
FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct `class_id` FROM student;

2.2、条件查询

-- 查询姓名为 fly1 的学生信息
SELECT * FROM `student` WHERE `sname` = 'fly1';
-- 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=5;

2.3、范围查询

-- 查询班级id 1 到 3 的学生的信息
SELECT * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;

2.4、判空查询

is null 判断造成索引失效。

# 索引 B+ 树
SELECT * FROM `student` WHERE `class_id` IS NOT NULL; #判断不为空
SELECT * FROM `student` WHERE `class_id` IS NULL;#判断为空


SELECT * FROM `student` WHERE `gender` <> '';
#判断不为空字符串
SELECT * FROM `student` WHERE `gender` = '';
#判断为空字符串

2.5、模糊查询

使用 like关键字，"%"代表任意数量的字符，_代表占位符。

-- 查询名字为 m 开头的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '谢%';
-- 查询姓名里第二个字为 小 的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';

2.6、分页查询

分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用。

使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0。

-- 查询第二条到第三条内容
SELECT * FROM `student` LIMIT 1,2;

2.7、查询后排序

关键字：order by field。

• asc:升序。
• desc:降序

SELECT * FROM `score` ORDER BY `num` ASC;
-- 按照多个字段排序
SELECT * FROM `score` ORDER BY `course_id` DESC,`num` DESC;

2.8、聚合查询

聚合函数	描述
sum()	计算某列的总和
avg()	计算某列的平均值
max()	计算某列的最大值
min()	计算某列的最小值
count()	计算某列的行数

SELECT sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

2.9、分组查询

分组加group_concat。

-- 分组加group_concat
SELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY `gender`;
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;

三、联表查询

可以把表想象成集合。
联表查询分为内联（inner join）和外联（left join，right join，full join）。内联类似交集，full join类似并集。
如果只写了join，那么默认是内联。

3.1、INNER JOIN

只取两张表有对应关系的记录。

SELECT cid FROM `course` INNER JOIN `teacher` ON course.teacher_id = teacher.tid;

3.2、LEFT JOIN

在内连接的基础上保留左表没有对应关系的记录。

select course.cid from `course` left join `teacher` on course.teacher_id = teacher.tid;

3.3、RIGHT JOIN

在内连接的基础上保留右表没有对应关系的记录。

select course.cid from `course` right join `teacher` on course.teacher_id = teacher.tid;

四、子查询/合并查询

4.1、单行子查询

select * from course where teacher_id = (select tid from teacher where tname = 'lucien')

4.2、多行子查询

多行子查询即返回多行记录的子查询。

• IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
• EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（ true ），否则，将返回一个假值（ false ）。当返回的值为 true 时，外层查询语句将进行查询；当返回的为false 时，外层查询语句不进行查询或者查询不出任何记录。
• ALL 关键字：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
• ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。

在 FROM 子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用。

示例：

select * from student where class_id in (select cid from course where teacher_id = 6);

select * from student where exists(select cid from course where cid = 5);


select student_id,sname FROM (SELECT * FROM score WHERE course_id = 5 OR course_id = 2) AS A LEFT JOIN student ON A.student_id = student.sid;

4.3 正则表达式

使用关键字：REGEXP。

选项	说明（自动加匹配二字）	例子	匹配值示例
^	文本开始字符	'^b’匹配以字母b开头的字符串	book, big,banana,bike
.	任何单个字符	'b.t’匹配任何b和t之间有一个字符	bit, bat,but, bite
*	0个或多个在它前面的字符	'f*n’匹配字符n前面有任意多个字符f	fn, fan,faan, abcn
+	前面的字符一次或多次	'ba+'匹配以b开头后面紧跟至少一个a	ba, bay,bare, battle
<字符串>	包含指定字符串的文本	‘fa’	fan, afa,faad
[字符集合]	字符集合中的任一个字符	'[xz]'匹配x或者z	dizzy,zebra, xray,extra
[^]	不在括号中的任何字符	'[^abc]'匹配任何不包含a、b或c的字符串	desk, fox,f8ke
字符串{n}	前面的字符串至少n次	b{2}匹配2个或更多的b	bbb, bbbb,bbbbbb
字符串{n,m}	前面的字符串至少n次，至多m次	b{2,4}匹配最少2个，最多4个b	bb, bbb,bbbb

SELECT * FROM `teacher` WHERE `tname` REGEXP '^long';

五、总结

1. 在实际使用中最好不要使用select *的方式查询数据，这种查询方式既不好分析数据，也会使查询效率降低。
2. SQL查询中，如果不清楚名称是不是关键字，最好使用反引号括起来，避免在词法语法分析时被当成关键字处理。