MySQL高级查询操作

今天药忘吃喽~ 2023-09-26 23:50 179阅读 0赞

### 文章目录 ###

*  前言
 *  聚集函数
 *  分组查询：GROUP BY
 *  过滤：HAVING
 *  嵌套子查询
 *   *  比较运算中使用子查询
     *  带有IN的子查询
     *  SOME(子查询)
     *  ALL(子查询)
     *  EXISTS子查询

--------------------

# 前言 #

查询语句**书写顺序：**  
1、select  
2、from  
3、where  
4、group by  
5、having  
6、order by  
7、limit

查询语句**执行顺序：**  
数据库的时候先执行from，确定数来自哪张表  
再执行where，看看哪些行需要查询  
再执行group by，确定是否分组  
再执行having，过滤掉不要的组  
然后是select，再确定查询哪些列  
随后是order by排序  
最后是limit限定

![在这里插入图片描述][4ed55eff80c2461d8d04f65e9b682d75.png]

# 聚集函数 #

聚集函数（也叫集合函数），方便用户统计一些数据。  
`COUNT(*)`： 统计表中元组个数；  
`COUNT(列名)`：统计本列列值个数；  
`SUM(列名)`：计算列值总和（必须是数值型列）；  
`AVG(列名)`：计算列值平均值（必须是数值型列）；  
`MAX(列名)`：最大、最高；  
`MIN(列名)`：最小、最低。

**max和min可以统计数字型数据、字符型数据、日期型数据**

【例】查询最高最低的学生成绩

SELECT MAX(score),MIN(score)
    FROM stu

**sum和avg只适用于数字型数据**

【例】统计全部学生的平均成绩

SELECT AVG(score) 
    FROM stu

**count(\*)返回表中满足条件的行数**  
其中`*`也可以写具体的列名，但是空值不统计

【例】统计老师的总人数

SELECT COUNT(*) 
    FROM teacher

查询工作在HK的员工人数，最高工资及最低工资

SELECT count(*) AS 员工人数,max(sal) AS 最高工资,min(sal) AS 最低工资
    FROM emp e JOIN dept d 
    ON e.deptno = d.deptno 
    WHERE loc='HK';

聚合函数不能出现在 WHERE 子句中

# 分组查询：GROUP BY #

当有每个、每组的时候需要分组

SQL语句中使用了分组查询group by，在select子句中就要使用分组统计函数  
在select语句中查询分组的列

【例】查每个班级编号，平均成绩，进行分组

SELECT classid,AVG(score)
    FROM stu
    GROUP BY classid;

【例】统计每个出版社的出版图书的数目  
分析：如果能够将所有的图书，按照出版社的名称进行分组，然后我们在统计每一组的元组的个数，我们就能能到得到期望的数据。

比如book表中字段有：bookid，name，author，public，price

可以使用GROUP BY <列名>进行分组

在<列名>上值相同的元组被分在一组，该列称为分组依据列。  
然后可以使用聚集函数统计每一组的数据。

SELECT COUNT(*) , publish FROM book
    GROUP BY publish

【例】统计每个人所借图书的数目。

SELECT COUNT(bookid), Reader_id
    FROM Borrow 
    GROUP BY Reader_id
    Having COUNT(bookid)>2

【例】统计每个出版社出版图书的平均价格，并显示每个出版社的名称

SELECT publish, AVG(price) AS 平均价格
    FROM book
    GROUP BY publish

【例】统计每个岗位和部门都相同的人数

SELECT deptno,job,COUNT(*) AS 人数
    FROM emp
    GROUP BY deptno,job;

# 过滤：HAVING #

HAVING必须写在GROUP BY后面，ORDER BY前面，HAVING后面也是写条件的（和where相近）

HAVING子句用于过滤掉不满足条件的分组数据，HAVING 子句用于对分组统计后的结果进行筛选。满足HAVING 子句条件将会保留在结果中

WHERE子句中编写的是过滤筛选数据行的条件  
HAVING子句是使用分组统计函数的

【例】查询出版图书平均价格高于30元的出版社名称，并显示其图书平均价格。

SELECT publish,AVG(price) FROM book 
    GROUP BY publish
    HAVING AVG(price)>30

【例】查询出版图书多于2本的出版社名称和出版图书数目

SELECT publish, COUNT(*) FROM book 
    GROUP BY publish
    HAVING COUNT(*)>2

【例】查询部门编号，岗位，平均工资，其中岗位在销售、经理、职员之中，把岗位和部门都相同的分在一组，保留平均工资高于1000的，平均工资按降序排列

SELECT deptid,job,AVG(sal) AS 平均工资
    FROM emp
    WHERE job in('salesman','manager','clerk') 
    GROUP BY deptid,job
    HAVING avg(sal)>1000
    ORDER BY 平均工资 DESC;

【例】查询部门人数大于2的部门编号，部门名称，部门人数。  
员工表内字段：deptid  
部门表内字段：deptid，dname

SELECT d.deptid,dname,count(*)
    FROM emp e JOIN dept d 
    ON e.deptid = d.deptid
    GROUP BY d.deptno 
    HAVING COUNT(*)>2;

【例】查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。  
员工表内字段：deptid，sal  
部门表内字段：deptid，dname

SELECT d.deptid,dname,COUNT(*),avg(sal)
    FROM emp e,dept d
    WHERE e.deptid=d.deptid
    GROUP BY d.deptid
    HAVING avg(sal)>2000
    AND COUNT(*)>2
    ORDER BY COUNT(*);

# 嵌套子查询 #

括号内的查询叫做子查询，也叫内部查询，当条件不知道的时候用子查询

嵌套子查询就是子查询里面有个子查询

NOT IN后面的子查询要确保结果里不能包含空值（IS NOT NULL）

执行顺序：先内层后外层；先查子查询后查主查询

相关子查询运行时和主查询查询的表有关联，使用了主查询表某些列数据

【例】查询成绩最低的学生姓名和学号

解题思路：先写大框，最后写括号里面的东西（查询所有学生里面最低的成绩），变成填空题写

SELECT sname,stuid
    FROM stu
    WHERE score=(SELECT MIN(score) FROM stu);

【例】查询入学日期比二班入学日期最早的学生还要早的学生姓名，入学日期

SELECT sname,studate
    FROM stu
    WHERE studate<(SELECT min(studate) FROM stu WHERE classid=2);

## 比较运算中使用子查询 ##

【例】查询成绩最好的学生的姓名

SELECT name FROM Student 
    WHERE Score =(SELECT MAX(Score) FROM Student)

## 带有IN的子查询 ##

【例】查询与"C语言"在同一出版社的图书信息

SELECT * FROM Book WHERE publish 
    IN (SELECT publish FROM Book WHERE name='C语言')

【例】查询张三所借图书的图书编号

SELECT book_ID FROM Borrow 
    WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE name='张三')

【例】查询"张三"所借的图书的名称

SELECT name FROM Book 
    WHERE book_ID IN(SELECT book_ID FROM Borrow 
    WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE  name='张三'))

查询过程：  
第1步，查询 “张三” 的reader\_ID。  
第2步，依据 reader\_ID在Borrow表中找张三所借图书的book\_ID  
第3步，依据 book\_ID在Book表中找到图书名称。

【例】查询借书价格在20-40之间的读者的姓名

select name from reader 
    where reader_id in(select reader_id from borrow 
    	where book_id in( select book_id from book 
    	where price between 20 and 40))

查询张三 ‘借阅’ 计算机文化基础’的日期

select borrowdate from borrow 
    where reader_id in(
    	select  reader_id from reader 
    	where name='张三')
    	and 
    	book_id in(
    	select book_id from book 
    	where name='计算机文化基础')

查询借书价格在20-40之间的读者的姓名

select name from reader where reader_id in(
    	select reader_id from borrow 
        where book_id in(
        select book_id from book 
        where price between 20 and 40))

## SOME(子查询) ##

表示子查询的结果集合中某一个元素

【例】查询除不是最低价格外的所有图书

SELECT * FROM Book 
    WHERE price>SOME(SELECT price FROM Book)

【例】查询价格最低的图书信息

SELECT * FROM Book 
    WHERE NOT(price>SOME(SELECT price FROM Book))

## ALL(子查询) ##

表示子查询的全部结果

【例】查询书价最高的图书的信息

SELECT * FROM Book 
    WHERE price >=ALL(SELECT price FROM Book)

【例】查询评价书价最高的出版社名称

SELECT * FROM Book 
    GROUP BY public
    HAVING AVG(price)>=ALL(
    	SELECT AVG(price) FROM Book
    	GROUP BY public)