本章我们学习下数据库的查询语句

先插入点数据：

学生基本信息表的结构

CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    UNIQUE KEY (id_number)
);

学生成绩表的结构

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

我们给这两个表插入一些数据：

INSERT INTO student_info(number, name, sex, id_number, department, major, enrollment_time) VALUES   (20180101, '杜子腾', '男', '158177199901044792', '计算机学院', '计算机科学与工程', '2018-09-01'),(20180102, '杜琦燕', '女', '151008199801178529', '计算机学院', '计算机科学与工程', '2018-09-01'), (20180103, '范统', '男', '17156319980116959X', '计算机学院', '软件工程', '2018-09-01'),(20180104, '史珍香', '女', '141992199701078600', '计算机学院', '软件工程', '2018-09-01'),(20180105, '范剑', '男', '181048199308156368', '航天学院', '飞行器设计', '2018-09-01'),(20180106, '朱逸群', '男', '197995199501078445', '航天学院', '电子信息', '2018-09-01');

图片.png

INSERT INTO student_score (number, subject, score) VALUES  (20180101, '母猪的产后护理', 78), (20180101, '论萨达姆的战争准备', 88),(20180102, '母猪的产后护理', 100),(20180102, '论萨达姆的战争准备', 98),(20180103, '母猪的产后护理', 59),(20180103, '论萨达姆的战争准备', 61),(20180104, '母猪的产后护理', 55),(20180104, '论萨达姆的战争准备', 46);

查询语句的基本格式 (总)

select 哪列 from 哪个表
where 检索条件
group by 分组依据列
having 组提取条件
order by 排序依据列
limit 开始行, 限制条数

select子句和 from子句是不可缺少的其他子句都是可选的

查询单个列

查看某个表中的某一列的数据的通用格式是这样：

SELECT 列名 FROM 表名;

比如查看student_info表中的number列的数据可以这么写：

SELECT number FROM student_info;

列的别名 as

我们也可以为结果集中的列重新定义一个别名，命令格式如下：

SELECT 列名 [AS] 列的别名 FROM 表名;

我们看到AS被加了个中括号，意味着可有可无，没有AS的话，列名和列的别名之间用空白字符隔开就好了。

比如我们想给number列起个别名，可以使用下边这两种方式之一：

方式一

SELECT number AS 学号 FROM student_info;

方式二：

SELECT number 学号 FROM student_info;

图片.png

查询多个列

如果想查询多个列的数据，可以在SELECT后边写多个列名，用逗号,分隔开就好：

SELECT 列名1, 列名2, ... 列名n FROM 表名;

需要注意的是，查询列表中的列名可以按任意顺序摆放，结果集将按照我们指定的列名顺序显示。

比如我们查询student_info中的多个列：

SELECT number, name, id_number, major FROM student_info;

查询所有列 *

SELECT * FROM 表名;

比如查询student_score表的全部列

查询结果去重`DISTINCT`

有的时候我们查询某个列的数据时会有一些重复的结果，比如我们查询一下student_info表的学院信息：

如果我们想去除重复结果的话，可以将DISTINCT放在被查询的列前边，就是这样：

 SELECT distinct department FROM student_info;

如果要去重多列的

两条结果重复的意思是：两条结果的每一个列中的值都相同。比如查询学院和专业信息：

查询结果中第1、2行记录中的department和major列都相同，所以这两条记录就是重复的，同理，第3、4行也是重复的。

如果我们想对多列查询的结果去重的话，可以直接把DISTINCT放在被查询的列的最前边：

SELECT DISTINCT 列名1, 列名2, ... 列名n  FROM 表名;

这里使用：

SELECT DISTINCT department, major FROM student_info;

这样就去重了二列结果相同的

限制查询结果条数 limit

有时候查询结果的条数会很多，都显示出来可能会撑爆屏幕

限制结果集中的记录条数的方式，就是在查询语句的末尾使用这样的语法：

LIMIT 开始行, 限制条数;

开始行指的是我们想从第几行数据开始查询，限制条数是结果集中最多包含多少条记录。

比如我们查询一下student_info表，从第0条记录开始，最多查询2条记录可以这么写：

 SELECT number, name, id_number, major FROM student_info LIMIT 0, 2;

注意：在计算机中都是从0开始计数的，所以我们平时所说的第1条记录在计算机中算是第0条。比如student_info表里的6条记录在计算机中依次表示为：第0条、第1条、第2条、第3条、第4条、第5条。

图片.png

如果指定的开始行大于结果中的行数，那查询结果就什么都没有：

mysql> SELECT number, name, id_number, major FROM student_info LIMIT 6, 2;
Empty set (0.00 sec)

使用默认的开始行第0行

LIMIT后边也可以只有一个参数，那这个参数就代表着限制行数。也就是说我们可以不指定开始行，默认的开始行就是第0行，比如我们可以这么写：

 SELECT number, name, id_number, major FROM student_info LIMIT 3;

图片.png

查询经过计算的列

select 查询后面是可以计算的。

比如：

查询学生的姓名和学号的序号

查询常量列

查询select后面跟个常量会出来个全是这个常量的列：

图片.png

查询排序 order by

我们可以用下边的语法来指定返回结果的记录按照某一列的值进行排序：

ORDER BY 列名 ASC|DESC

ASC是升序，DESC是降序。 |表示选一个
默认是ASC升序

这回我们用student_score表测试一下：

SELECT * FROM student_score ORDER BY score ;

再试试DESC降序排序

也可以多列排序：

比如我们想让对student_score的查询结果先按照subjuect升序排序，再按照score值从大到小的顺序进行排列，可以这么写：

 SELECT * FROM student_score ORDER BY subject, score DESC;

利用limit和order by可以找前几名或者后几名

我们还可以让ORDER BY语句和LIMIT语句结合使用，不过 ORDER BY 语句必须放在 LIMIT 语句前边，比如这样：

找出成绩最低的那条记录了。

SELECT * FROM student_score ORDER BY score LIMIT 1;

图片.png

消除取值相同的行 distinct

格式

select distinct 列名 from 表名

比如student_score中学号相同的结果消去

select distinct number from student_score;

图片.png

where 简单搜索条件

使用where限制搜索条件

总结如下：

where从查询条件如下：

比较运算符

查询student_info表中名字是范剑的学生的一些信息

 SELECT number, name, id_number, major FROM student_info WHERE name = '范剑';

图片.png

查询学号大于20180103的学生信息可以这样写:

SELECT number, name, id_number, major FROM student_info WHERE number > 20180103;

图片.png

查询专业不是计算机科学与工程的一些学生信息可以这么写：

 SELECT number, name, id_number, major FROM student_info WHERE major != '计算机科学与工程';

图片.png

确定范围 between and

查询学号不在20180102~20180104这个区间内的所有学生信息，可以这么写：

SELECT number, name, id_number, major FROM student_info WHERE number NOT BETWEEN 20180102 AND 20180104;

图片.png

匹配列表中的元素 in

有时候搜索条件中指定的匹配值并不是单个值，而是一个列表，只要匹配到列表中的某一项就算匹配成功，这种情况可以使用IN操作符：

比如我们想查询软件工程和飞行器设计专业的学生信息，可以这么写：

SELECT number, name, id_number, major FROM student_info WHERE major IN ('软件工程', '飞行器设计');

图片.png

如果想查询不是这两个专业的学生的信息，可以这么写：not in

SELECT number, name, id_number, major FROM student_info WHERE major NOT IN ('软件工程', '飞行器设计');

图片.png

匹配`NULL`值

查询name列不是NULL值的方式：

SELECT number, name, id_number, major FROM student_info WHERE name IS NOT NULL;

图片.png

注意，不能直接==NULL来比较是不是NULL，必须用is NULL判断

多个搜索条件的查询 and or

AND操作符

使用AND操作符来连接多个搜索条件

比如我们想从student_score表中找出科目为'母猪的产后护理'并且成绩大于75分的记录，可以这么写：

SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > 75;

图片.png

OR操作符

符合某一个搜索条件的时候就将其加入结果集中

比如我们想从student_score表中找出成绩大于95分或者小于55分的记录，可以这么写：

 SELECT * FROM student_score WHERE score > 95 OR score < 55;

图片.png

混合使用注意优先级最好使用()避免

比方说我们想从student_score表中找出课程为'论萨达姆的战争准备'，并且成绩大于95分或者小于55分的记录，那我们可能会这么写：

 SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = '论萨达姆的战争准备';

图片.png

为什么结果中仍然会有'母猪的产后护理'课程的记录呢？

因为：AND操作符的优先级高于OR操作符，也就是说在判断某条记录是否符合条件时会先检测AND操作符两边的搜索条件。

可以被看作下边这两个条件中任一条件成立则整个式子成立：

score > 95
score < 55 AND subject = '论萨达姆的战争准备'

为了避免这种尴尬，在一个查询中有多个搜索条件时最好使用小括号()来显式的指定各个搜索条件的检测顺序，比如上边的例子可以写成下边这样：

 SELECT * FROM student_score WHERE (score > 95 OR score < 55) AND subject = '论萨达姆的战争准备';

图片.png

通配符

一般格式是 列名【NOT】 like <匹配串>

图片.png

比方说我们只是想看看姓'杜'的学生信息，而不能精确的描述出这些姓'杜'的同学的完整姓名，我们称这种查询为模糊查询。MySQL中使用下边这两个操作符来支持模糊查询：

操作符	示例	描述
`LIKE`	`a LIKE b`	a匹配b
`NOT LIKE`	`a NOT LIKE b`	a不匹配b

既然我们不能完整描述要查询的信息，那就用某个符号来替代这些模糊的信息，这个符号就被称为通配符。MySQL中支持下边这两个通配符：

需要加入通配符协助。

%：代表任意一个字符串。

比方说我们想查询student_info表中name以'杜'开头的记录，我们可以这样写：

 SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜%';

图片.png

或者我们只知道学生名字里边包含了一个'香'字，那我们可以这么查：

SELECT number, name, id_number, major FROM student_info WHERE name LIKE '%香%';

图片.png

_：代表任意一个字符。与%不同限制了字符数

比方说我们想查询姓'范'，并且姓名只有2个字符的记录，可以这么写：

SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范_';

图片.png

[]表示匹配列表里面的一个字符

图片.png

[^]表示不匹配列表中的任意一个字符

图片.png

RTRIM防止列表尾随空格 RTRIM(列名)

图片.png

注意: sname 列定义的是非Unicode定长，即char(10) ，因此可以分配10个字节的空间，不足的部分用空格补齐。即对'王敏'实际存储的是'王敏'后边加6个空格---称为尾随空格。]()

在进行like 字符串匹配的时候，系统并不会自动去掉尾随空格，空格是一个字符，也满足_通配符，因此会出现二个字的情况]()

解决方法：可以使用函数 RTRIM]()

RTRIM(列名)]()

其功能是去掉指定列中尾随的空格，返回没有尾随空格的数据。例如，将例子中的语句改为

select Sname from Student where RTRIM(Sname) like '王__'

举例:

其中，str是要去除空格的字符串。RTRIM函数会返回一个新的字符串，该字符串是去除了str末尾空格的结果。例如，下面的SQL语句可以去除字符串末尾的空格并输出结果：

SELECT RTRIM(' Hello World   ');

输出结果为：

' Hello World'

转义字符 `\`

如果待匹配的字符串中本身就包含普通字符'%'或者'_'该咋办，怎么区分它是一个通配符还是一个普通字符呢？

答：如果匹配字符串中需要普通字符'%'或者'_'的话，需要在它们前边加一个反斜杠\来和通配符区分开来，也就是说：

'%'代表普通字符'%'
'_'代表普通字符'_'

比方说这样：

SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范\_';

由于student_info表中没有叫范_的学生，所以查询结果为空。

使用函数计算数据

图片.png

COUNT函数统计行数

COUNT函数使用来统计行数的，它有下边两种使用方式：

COUNT(*)：对表中行的数目进行计数，不管列的值是不是NULL。
COUNT(列名)：对特定的列进行计数，会忽略掉该列为NULL的行。

我们来数一下student_info表中有几行记录吧：

 SELECT COUNT(*) FROM student_info;

MAX函数

MAX函数是用来查询某列中数据的最大值，以student_score表中的score列为例来看一下：

SELECT MAX(score) FROM student_score;

MIN函数

MIN函数是用来查询某列中数据的最小值，以student_score表中的score列为例来看一下：

 SELECT MIN(score) FROM student_score;

SUM函数

SUM函数是用来计算某列数据的和，还是以student_score表中的score列为例来看一下：

SELECT SUM(score) FROM student_score;

AVG函数

AVG函数是用来计算某列数据的平均数，还是以student_score表中的score列为例来看一下：

 SELECT AVG(score) FROM student_score;

注意计算函数不能出现在where后面，要用子查询

聚集函数中DISTINCT的使用

可以选择使用DISTINCT来过滤掉这些重复数据。

比方说我们想查看一下student_info表中存储了多少个专业的学生信息，就可以这么写：

SELECT COUNT(DISTINCT major) FROM student_info;

图片.png

一共4个专业

文本处理函数

名称	调用示例	示例结果	描述
`LEFT`	`LEFT('abc123', 3)`	`abc`	给定字符串从左边取指定长度的子串
`RIGHT`	`RIGHT('abc123', 3)`	`123`	给定字符串从右边取指定长度的子串
`LENGTH`	`LENGTH('abc')`	`3`	给定字符串的长度
`LOWER`	`LOWER('ABC')`	`abc`	给定字符串的小写格式
`UPPER`	`UPPER('abc')`	`ABC`	给定字符串的大写格式
`LTRIM`	`LTRIM(' abc')`	`abc`	给定字符串左边空格去除后的格式
`RTRIM`	`RTRIM('abc ')`	`abc`	给定字符串右边空格去除后的格式
`SUBSTRING`	`SUBSTRING('abc123', 2, 3)`	`bc1`	给定字符串从指定位置截取指定长度的子串
`CONCAT`	`CONCAT('abc', '123', 'xyz')`	`abc123xyz`	将给定的各个字符串拼接成一个新字符串

日期和时间处理函数

名称	调用示例	示例结果	描述
`NOW`	`NOW()`	`2019-08-16 17:10:43`	返回当前日期和时间
`CURDATE`	`CURDATE()`	`2019-08-16`	返回当前日期
`CURTIME`	`CURTIME()`	`17:10:43`	返回当前时间
`DATE`	`DATE('2019-08-16 17:10:43')`	`2019-08-16`	将给定日期和时间值的日期提取出来
`DATE_ADD`	`DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY)`	`2019-08-18 17:10:43`	将给定的日期和时间值添加指定的时间间隔
`DATE_SUB`	`DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY)`	`2019-08-14 17:10:43`	将给定的日期和时间值减去指定的时间间隔
`DATEDIFF`	`DATEDIFF('2019-08-16', '2019-08-17');`	`-1`	返回两个日期之间的天数（负数代表前一个参数代表的日期比较小）
`DATE_FORMAT`	`DATE_FORMAT(NOW(),'%m-%d-%Y')`	`08-16-2019`	用给定的格式显示日期和时间

在使用这些函数时需要注意一些地方：

在使用DATE_ADD和DATE_SUB这两个函数时需要注意，增加或减去的时间间隔单位可以自己定义，下边是MySQL支持的一些时间单位：

时间单位	描述
`MICROSECOND`	毫秒
`SECOND`	秒
`MINUTE`	分钟
`HOUR`	小时
`DAY`	天
`WEEK`	星期
`MONTH`	月
`QUARTER`	季度
`YEAR`	年

如果我们相让2019-08-16 17:10:43这个时间值增加2分钟，可以这么写：

SELECT DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 MINUTE);

图片.png

在使用DATE_FORMAT函数时需要注意，我们可以通过一些所谓的格式符来自定义日期和时间的显示格式，下边是MySQL中常用的一些日期和时间的格式符以及它们对应的含义：

格式符	描述
`%b`	简写的月份名称（Jan、Feb、...、Dec)
`%D`	带有英文后缀的月份中的日期（0th、1st、2nd、...、31st)）
`%d`	数字格式的月份中的日期(00、01、02、...、31)
`%f`	微秒（000000-999999）
`%H`	二十四小时制的小时 (00-23)
`%h`	十二小时制的小时 (01-12)
`%i`	数值格式的分钟(00-59)
`%M`	月份名（January、February、...、December）
`%m`	数值形式的月份(00-12)
`%p`	上午或下午（AM代表上午、PM代表下午）
`%S`	秒(00-59)
`%s`	秒(00-59)
`%W`	星期名（Sunday、Monday、...、Saturday）
`%w`	周内第几天（0=星期日、1=星期一、 6=星期六）
`%Y`	4位数字形式的年（例如2019）
`%y`	2位数字形式的年（例如19）

比如：

SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');

图片.png

数值处理函数

下边列举一些数学上常用到的函数，在遇到需要数学计算的业务时会很有用：

名称	调用示例	示例结果	描述
`ABS`	`ABS(-1)`	`1`	取绝对值
`Pi`	`PI()`	`3.141593`	返回圆周率
`COS`	`COS(PI())`	`-1`	返回一个角度的余弦
`EXP`	`EXP(1)`	`2.718281828459045`	返回e的指定次方
`MOD`	`MOD(5,2)`	`1`	返回除法的余数
`RAND`	`RAND()`	`0.7537623539136372`	返回一个随机数
`SIN`	`SIN(PI()/2)`	`1`	返回一个角度的正弦
`SQRT`	`SQRT(9)`	`3`	返回一个数的平方根
`TAN`	`TAN(0)`	`0`	返回一个角度的正切

分组查询 group by

图片.png

比如student_score表中按照subject列分组后的图示就是这样：

图片.png

具体语句如下：

SELECT subject, AVG(score) FROM student_score GROUP BY subject;

图片.png

注意：`非分组列`出现在查询列表报错

比如

SELECT number, subject, AVG(score) FROM student_score GROUP BY subject;

图片.png

本例中的查询列表处放置了既非分组列、又非聚集函数的number列，那我们想表达啥意思呢？

从各个分组中的记录中取一条记录的number列？
该取分组中的哪条记录为好呢？

比方说对于'母猪的产后护理'这个分组中的记录来说，该分组中有4条记录，那number列的值应该取20180101，还是20180102，还是20180103，还是20180104呢？这个我们也不知道，也就是说把非分组列放到查询列表中会引起争议，导致结果不确定。

小贴士：

其实假如分组后的每个分组的所有记录的某个非分组列的值都一样，那我把该非分组列加入到查询列表中也没啥问题呀。比方说按照subject列进行分组后，假如在'母猪的产后护理'的分组中各条记录的number列的值都相同，在'论萨达姆的战争准备'的分组中各条记录的number列的值也都相同，那么我们把number列放在查询列表中也没啥问题。可能设计MySQL的大叔觉得这种说法也有点儿道理，他们提出了一个称之为ONLY_FULL_GROUP_BY的SQL模式，当我们关闭这个SQL模式时，就允许把非分组列放到查询列表中。当然，什么是SQL模式，怎么开启和关闭这个称之为ONLY_FULL_GROUP_BY的SQL模式，不是我们初学者要考虑的问题，等以后大家变牛的时候可以再到文档中去查看。

带有WHERE子句的分组查询

比如老师觉得各个科目的平均分太低了，所以想先把分数低于60分的记录去掉之后再统计平均分，就可以这么写:

SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;

图片.png

步骤解析：

将记录进行过滤后分组。

在进行分组的时候将过滤掉不符合WHERE子句的记录，所以，最后的分组情况其实是这样的（少于60分的记录被过滤掉了）：
分别对各个分组进行数据统计。

统计之后就产生了上述的结果。

having 分组过滤条件

如果我们不想在结果集中得到这么多记录，只想把那些符合某些条件的分组加入到结果集，从而减少结果集中记录的条数，那就需要把针对分组的条件放到HAVING子句了

比方说老师想要查询平均分大于73分的课程，就可以这么写：

SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 73;

图片.png

说明计算函数可以放在having后面

分组和排序

如果我们想对各个分组查询出来的统计数据进行排序，需要为查询列表中有聚集函数的表达式添加别名，比如想按照各个学科的平均分从大到小降序排序，可以这么写：

SELECT subject, AVG(score) AS avg_score FROM student_score GROUP BY subject ORDER BY avg_score DESC;

图片.png

嵌套分组

有时候按照某个列进行分组太笼统，一个分组内可以被继续划分成更小的分组。比方说对于student_info表来说，我们可以先按照department来进行分组，所以可以被划分为2个分组：

image_1c7e7psvl50i1icl19vpujjfd09.png-156.4kB

我们觉得这样按照department分组后，各个分组可以再按照major来继续分组，从而划分成更小的分组，所以再次分组之后的样子就是这样：

image_1c7e9f21il3a1ao6duddp3kj3m.png-136.5kB

所以现在有了2个大分组，4个小分组，我们把这种对大的分组下继续分组的的情形叫做嵌套分组，如果你乐意，你可以继续把小分组划分成更小的分组。我们只需要在GROUP BY子句中把各个分组列依次写上，用逗号,分隔开就好了。比如这样：

SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;

图片.png

使用分组注意事项

使用分组来统计数据给我们带来了非常大的便利，但是要随时提防有坑的地方：

如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。
如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上。
如果查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。
GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。
WHERE子句和HAVING子句的区别。

WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

子查询

也就是二条查询语句结合:

 SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');

图片.png

我们把第二条查询语句用小括号()扩起来作为一个操作数放到了第一条的搜索条件处，这样就起到了合并两条查询语句的作用。

由标量子查询作为的操作数组成的搜索条件只要符合表达语法就可以。比方说我们来查询学号大于'杜琦燕'的学号的学生成绩，可以这么写：

 SELECT * FROM student_score WHERE number > (SELECT number FROM student_info WHERE name = '杜琦燕');

列子查询 in

 SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');

很显然第一条查询语句的结果集中并不是一个单独的值，而是一个列（本例中第一条查询语句的结果集中该列包含2个值，分别是：20180101和20180102），所以它对应的子查询也被称之为列子查询。因为列子查询得到的结果是多个值，相当于一个列表。

行子查询

SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1);

图片.png

该子查询的查询列表是number, '母猪的产后护理'，其中number是列名，'母猪的产后护理'是一个常数。我们在子查询语句中加了LIMIT 1这个子句，意味着子查询最多只能返回一条记录，所以该子查询就可以被看作一个行子查询。

表子查询

如果子查询结果集中包含多行多列，那么这个子查询也可以被称之为表子查询，比如这样：

 SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');

图片.png

在这个例子中的子查询执行之后的结果集中包含多行多列，所以可以被看作是一个表子查询。

EXISTS和NOT EXISTS子查询

有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集，这时可以用到下边这两个操作符：

操作符	示例	描述
`EXISTS`	`EXISTS (SELECT ...)`	当子查询结果集不是空集时表达式为真
`NOT EXISTS`	`NOT EXISTS (SELECT ...)`	当子查询结果集是空集时表达式为真

 SELECT * FROM student_score WHERE EXISTS
 (SELECT * FROM student_info WHERE number = 20180108);

Empty set (0.00 sec)

其中子查询的意思是在student_info表中查找学号为20180108的学生信息，很显然并没有学号为20180108的学生，所以子查询的结果集是一个空集，于是EXISTS表达式的结果为FALSE，所以外层查询也就不查了，直接返回了一个Empty set，表示没有结果、

若是not exists

图片.png

对同一个表的子查询

 SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理');

我们使用子查询先统计出了'母猪的产后护理'这门课的平均分，然后再到外层查询中使用这个平均分组成的表达式来作为搜索条件去查找大于平均分的记录。

连接表 join

比如：连接学生信息表和学生成绩表

select * from student_info AS I join student_score AS S on I.number=S.number;

图片.png

但是为啥会有二个重复的number列呢？

没有定义查询的，直接使用*全部查询，需要明确重复的查询的表名，比如 表名.列名

select I.number,name,sex,id_number, department,major,enrollment_time,subject,score from student_info AS I join student_score AS S on I.number=S.number;

图片.png

有分组的 group by的多表查询

比如: 统计计算机科学与工程系的每门课程的选课人数

select I.number,major,count(*) as '选课数' from student_info AS I join student_score AS S on I.number=S.number 
where major='计算机科学与工程'
group by number;

图片.png

自己连接自己自连接

从意思上来看就是自己表连接自己，但在逻辑上看作二张表

注意实现方式：必须为表取别名

比如查询与杜子腾在同一个系学习的学生的姓名和所在系

select s2.name,s2.major from student_info s1 join  student_info s2 
on s1.major=s2.major -- 是同一个系的人
where s1.name='杜子腾'
and s2.name!= '杜子腾';  --s2作为结果表，去除'杜子腾'这个人

select s2.name,s2.major from student_info s1 join  student_info s2  on s1.major=s2.major where s1.name='杜子腾' and s2.name!='杜子腾';

图片.png

左右外连接 left|right

left|right join on

注意：左外连接的含义是限制表2中的数据必须满足条件，而不管表1中的数据是否满足连接条件，均输出表1中的内容

右外连接的含义是限制表1中的数据必须满足条件，而不管表2中的数据是否满足连接条件，均输出表2中的内容

举例：

SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN student_score ON student_info.number = student_score.number;

图片.png

使用top限制结果集

在查询时候，有时候只希望列出结果集中的前几行结果，而不是全部结果，比如竞赛的成绩前三名

图片.png

如果有 distinct的话，则在distinct之后，查询列表的前面select distinct top 3 数据

图片.png

数据库查询

查询语句的基本格式 (总)

查询单个列

列的别名 as

查询多个列

查询所有列 *

查询结果去重DISTINCT

限制查询结果条数 limit

使用默认的开始行 第0行

查询经过计算的列

查询常量列

查询排序 order by

消除取值相同的行 distinct

where 简单搜索条件

比较运算符

确定范围 between and

匹配列表中的元素 in

匹配NULL值

注意，不能直接==NULL来比较是不是NULL，必须用is NULL判断

多个搜索条件的查询 and or

AND操作符

OR操作符

混合使用 注意优先级 最好使用()避免

通配符

转义字符 \

使用函数计算数据

COUNT函数 统计行数

MAX函数

MIN函数

SUM函数

AVG函数

注意计算函数不能出现在where后面，要用子查询

聚集函数中DISTINCT的使用

文本处理函数

日期和时间处理函数

数值处理函数

分组查询 group by

注意：非分组列出现在查询列表报错

带有WHERE子句的分组查询

having 分组过滤条件

分组和排序

嵌套分组

使用分组注意事项

子查询

EXISTS和NOT EXISTS子查询

对同一个表的子查询

连接表 join

有分组的 group by的多表查询

自己连接自己 自连接

左右外连接 left|right

使用top限制结果集

查询结果去重`DISTINCT`

使用默认的开始行第0行

匹配`NULL`值

混合使用注意优先级最好使用()避免

转义字符 `\`

COUNT函数统计行数

注意：`非分组列`出现在查询列表报错

自己连接自己自连接