SQL高级查询

高级查询

1.聚合函数

MYSQL 中内置了 5 种聚合函数，分别是： SUM 、 MAX 、 MIN 、 AVG 、 COUNT 。

• sum ： 求和

  select sum(列) from table_name[其它子句]
  

• max : 求最大值

  select max(列) from table_name [其他子句];
  

• min : 求最小值

  select min(列) from table_name [其他子句];
  

• avg ： 求平均值

  select avg(列) from table_name [其他子句];
  

• count : 求数量

  select count(列) from table_name [其他子句]; 
  

2.group by

group by 是对数据进行分组，分组时，表中有相同值的分为一组。分组后可以进行聚合查询。

group by 分组后的查询中， select 的列不能出现除了 group by 分组条件以及聚合函数外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2; 

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having分组后条件;

3.多表查询

多表关系分为三种

• 一对多关系

  关系: 一个部门对应多个员工，一个员工对应一个部门

  实现: 在多的一方建立外键，指向一方的主键多对多

• 多对多

  关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

  实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 一对一

  关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

  实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

笛卡尔乘积现象

• 笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况

• 而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

4.等值连接查询

• 通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表

  n个表进行等值连接查询，最少需要n-1个等值条件来约束

select student.name,score.score from student,score where 
student.id=score.student_id

表的别名:

①. tableA as 别名1 , tableB as 别名2 ;

②. tableA 别名1 , tableB 别名2 ;

注意：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段

5.子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。相对于子查询来说，在外部直接执行的查询语句被称作主查询。

• 单列子查询: 返回单行单列数据的子查询

• 单行子查询: 返回单行多列数据的子查询

• 多行子查询: 返回数据是多行单列的数据

• 关联子查询: 子查询中如果使用了外部主SQL中的表或列，就说这个子查询跟外部SQL是相关的

(1).单行子查询

--查询软件部门下的所有员工
select * from emp e where e.dept_no = (select d.dept_no from dept d where d.d_name = '软件部')

1. 子查询的结果作为父查询条件的值
2. 父查询的查询条件是=时，子查询的结果集是单行单列数据。

(2).多列子查询

如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要
用到多行记录的操作符
如： in , all , any , not in
IN : 在指定的集合范围之内，多选一
NOT IN : 不在指定的集合范围之内
ANY : 子查询返回列表中，有任意一个满足即可
ALL : 子查询返回列表的所有值都必须满足

--统计所有的员工分布在那些部门的信息
select * from dept d where d.dept_no in (select e.dept_no from emp e);

1. 子查询查出的部门编号有三个值，该语句相当于select * from dept d where d.dept_no in(三个值)

查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.salary >any(select e2.salary from e2);

大于any 表示子查询的值的任意一个值，即大于最小的值。

-- 查询公司中比所有的助理工资高但不是助理的员工
select * emp e1 where e1.salary >all(select e2.salary from e2.emp where w2.job like'%助理');

大于all表示大于子查询中的所有值，即大于最大的值。

(3).关联子查询

关联子查询与外部查询（主查询）之间存在联系，并且内部子查询的结果依赖于外部

查询的值。换句话说，内部子查询的执行取决于外部查询的每一行。

SELECT student_name,
(SELECT score FROM scores WHERE subject = 'Math' AND
students.student_id = scores.student_id) AS math_score
FROM students;

6.自连接查询

多表查询不仅可以在多个表之间进行查询，也可以在一个表之中进行多表查询

员工信息和员工上级信息都是在员工表emp的，所以这样的查询是以当前表中mgr来关联当前表的另一列emp_no;在sql中提供了自连接查询的方式来完成这样的功能操作。

#查询当前公司员工和所属上级员工的信息
select
e1.emp_no as '员工编号'
e1.e_name as '员工姓名'
e2.emp_no as '上级编号'
e2.e_name as '上级姓名'
from emp e1,emp e2
where e1.mgr = e2.emp_no;

7.内连接查询

• 内连接查询使用 inner join 关键字实现， inner 可以省略。内连接查询时，条件用on 连接，多个条件使用 () 将其括起来。

• 内连接查询的是两张表交集部分的数据。

#查询每个部门的所有员工
select dept d_name,emp.e_name
from emp inner join dept on emp.dept_no=dept.dept_no;

8.外连接

外连接分为左外连接（ left outer join ） 和右外连接（ right outer join ）其值 outer 可以省略。外连接查时，条件用 on 连接，多个条件使用 () 将其括起来.左外连接表示以左表为主表，右外连接表示以右表为主表。

• 在左外连接中，关键字join左侧的表叫左表（主表），右侧的表叫右表（从表），左表的每一行数据去匹配右表中的每一行数据，如果左表与右表数据满足on条件，那么则相关数据返回的查询结果集中，如果左表的数据与右表的数据不满足on的条件，那么左表的数据也将返回的查询结果集中，而右表使用null填充结果集。

  SELECT 字段列表 FROM 表左 LEFT [ OUTER ] JOIN 表右 ON 条件 ... ;
  

• 右外连接与左外连接原理相同，只是右表的数据都显示，左表不满足on条件的记录用null填充结果。

  SELECT 字段列表 FROM 表右 RIGHT [ OUTER ] JOIN 表左 ON 条件 ... ;
  

9.综合查询

#统计2000年以后入职部门员工人数超过2人的部门
#按照部门人数从多到少排序输出，分页显示，每页两条。
select dept_no,count(*)
from emp
where hirdate>='2000-01-01'
group by dept_no having count(*)>=2 order by count(*) desc limit 0,1;

执行顺序解释

1.from：获取所有记录；

2.where：筛选掉不满足条件的记录，保留满足条件的记录；

3.group by:对满足条件的记录进行分组；

4.having：对分组后的记录进行筛选；

5.聚合函数：对数据聚合统计

6.select：查询出最终的数据

7.distinct：去重

8.order by：对选出的列进行排序

9.limit：取出指定行的列
*) desc limit 0,1;

执行顺序解释

1.from：获取所有记录；

2.where：筛选掉不满足条件的记录，保留满足条件的记录；

3.group by:对满足条件的记录进行分组；

4.having：对分组后的记录进行筛选；

5.聚合函数：对数据聚合统计

6.select：查询出最终的数据

7.distinct：去重

8.order by：对选出的列进行排序

9.limit：取出指定行的列