【数据库基础知识五】聚合函数取别名，可便于按该字段的结果排序，N表联合查询至少要有N-1个连接条件，并且这些连接条件都要同时满足，在关联查询同时添加过滤条件

为聚合函数取别名，可便于按该字段的结果排序

查看每个科目老师的平均工资，平均工资从少到多排序

SELECT AVG(salary）avg, subject_id

FROM teacher

GROUP BY subject_id

ORDER BY avg

在分组统计中使用过滤条件

SELECT AVG(salary), subject_id

FROM teacher

WHERE AVG(salary) >6000(报错)

GROUP BY subject_id

聚合函数不能被应用于WHERE子句中

本质原因是过滤时机不同，WHERE子句中添加的过滤条件是在第一次从表中逐条检索数据时生效的，从而产生查询结果集。

实际的过滤实际应当是在从表中查询出结果集并针对该结果集分组统计得出统计结果后再进行过滤

HAVING子句

HAVING子句是紧跟在GROUP BY子句之后的子句，它的作用是添加过滤条件针对分组统计结果后的数据进行筛选

HAVING子句可以利用聚合函数的统计的结果进行过滤

*查看每个科目老师的平均工资，前提是之查看平均工资高于6000的?

SELECT AVG(salary), subject_id

FROM teacher

GROUP BY subject_id

HAVING AVG(salary)>6000

查看每个科目老师的平均工资，前提是该科目老师的最高工资高于5000?

SELECT AVG(salary), subject_id

FROM teacher

GROUP BY subject_id

HAVING MAX(salary)>5000;

查看科目老师的工资总和,前提是该科目老师的平均奖金高于4000?

SELECT AVG(salary), subject_id

FROM teacher

GROUP BY subject_id

HAVING AVG(IFNULL(comm,0))>4000;

子查询

嵌套在其他SQL语句中的一条DQL语句，那个这个DQL就称为是子查询。

子查询常被应用于:

• DQL中 ： 可以基于一个查询结果集进行查询(最常被用于DQL语句)
• DML中 : 可以基于一个查询结果集进行增删改操作
• DDL中 ： 可以基于一个查询结果集进行数据对象操作(创建表，视图等)

在DQL语句中使用子查询

子查询在DQL中使用时必须使用"()"括起来

哪个老师的工资高于"王克晶"的工资?

1. 查询王克晶的工资?

SELECT salary FROM teacher WHERE name='王克晶’;

查看谁的工资高于她(工资>8000)？

SELECT salary FROM teacher

WHERE salary>(SELECT salary FROM teacher WHERE name='王克晶';

查看高于老师平均工资的那些老师的工资分别是多少?

1、确定条件=》老师的平均工资

SELECT AVG(salary) FROM teacher

2、基于条件查询

SELECT name,salary

FROM teacher

WHERE salary>(SELECT AVG(salary) FROM teacher);

查看与"祝雷"和"李费水"在同一个班的学生都有谁？

1:"李费水"在哪个班(class_id)?

SELECT class_id FROM student WHERE name='李费水'

2:查看谁的班级ID和李费水的相同

SELECT name,class_id
FROM student
WHERE class_id=(SELECT class_id FROM student WHERE name='李费水')

查看工资最高的那个老师的工资及奖金是多少?

1、查看老师的最高工资

SELECT MAX(salary)  FROM teacher

2、查看工资最高的那个老师的工资及奖金是多少

SELECT name ,salary ,comm

FROM teacher

WHERE salary=(SELECT MAX(salary)  FROM teacher);

子查询分类

单行单列子查询:查询结果集为一行一列，即:只有一个值

通常用于过滤条件，可以搭配:=,>,>=,<,<=,<>使用

多行单列子查询:查询结果集为多行一列，即:有多个值

通常用于过滤条件，但是要搭配:IN,ALL,ANY使用

IN等效于"="做等于判断，只不过是判断等于列表其中之一

ALL和ANY用于">,>=,<,<="使用:

• 1. >ANY：大于列表之一(大于列表最小的即可)
  2. >ALL: 大于列表所有(大于列表最大的)
  3. <ANY：小于列表之一(小于列表最大的即可)
  4. <ALL: 小于列表所有(小于列表最小的)

• 多行多列子查询:查询结果集为一个表.

  1. 常被应用于DQL语句中的FROM子句中当作一张表看待
  2. 被用作DDL语句中将一个结果集当作一张表创建出来

多行单列子查询

查看与"祝雷"和"李费水"在同一个班的学生都有谁？

1、查看"祝雷"和"李费水"的班级号

SELECT class_id FROM student WHERE name IN('祝雷','李费水')

2、查看与"祝雷"和"李费水"在同一个班的学生都有谁

SELECT name,class_id
FROM student
WHERE class_id=(SELECT class_id FROM student WHERE name IN ('祝雷','李费水'))
上述代码会出现错误:子查询返回了多于1行记录，原因不可能用"="来同时满足等于多个值的判断

实际需求:只要班级编号等于子查询结果的其中之一即可
当判断等于某个子查询结果时，如果该子查询为一个多行单列子查询时，要搭配IN来判断等于其中之一

SELECT name,class_id
FROM student
WHERE class_id IN (SELECT class_id FROM student WHERE name IN ('祝雷','李费水'))

查看比科目2和科目3老师工资都高的老师都有谁?

1:知道科目2和科目4老师的工资分别是多少?

SELECT salary FROM teacher WHERE subject_id IN (2,4)

2:大于上述查询所有工资即可
SELECT name,salary
FROM teacher
WHERE salary>ALL(SELECT salary FROM teacher WHERE subject_id IN (2,4))

多行多列子查询

 在DDL语句中使用

  创建一张表，表明:teacher_salary_info。该表字段:max_sal,min_sal,avg_sal,sum_sal,subject_id

  表中记录了每个科目老师的最高工资，最低工资，平均工资和工资总和以及对应的科目

表中数据对应的查询语句:
SELECT
   MAX(salary) max_sal,MIN(salary) min_sal,
   AVG(salary) avg_sal,SUM(salary) sum_sal,subject_id
FROM teacher
GROUP BY subject_id
上述DQL查询结果集就是我们目标要创建的表teacher_salary_info希望的样子与数据

CREATE TABLE teacher_salary_info
AS
SELECT
   MAX(salary) max_sal,MIN(salary) min_sal,
   AVG(salary) avg_sal,SUM(salary) sum_sal,subject_id
FROM teacher
GROUP BY subject_id

关联查询

查询结果集中的数据来自于多张表，而表与表中的数据之间存在的对应关系被称为关联关系。

两张表中的数据就可以产生关联关系，常见的关联关系有三种

 一对一:A表中的一条记录仅唯一对应B表中的一条记录
 一对多:A表中的一条记录可以对应B表中的多条记录
 多对多:A表与B表双向都是一对多关系就称为多对多关系

关联查询就是指联合多张表查询数据形成一个查询结果集的过程。

*在关联查询中的重中之重就是指定关联条件，也叫连接条件

*原则上，N张表关联查询要有至少N-1个连接条件

例如:3张表关联查询至少要有2个连接条件

笛卡尔积

缺少连接条件时会产生笛卡尔积，会将A表每条记录与B表每条记录都建立一次连接，产生巨量数据，这通常是一个无意义的结果集，要尽量避免

数据量计算:

以两张表为例:若A表10条记录，B表10条记录，笛卡尔积会产生两张表数据量乘积的条数。

即:10*10等于100条记录

关联查询语法:

SELECT 字段
FROM 表A,表B[,表C,...]
WHERE 连接条件
AND 过滤条件
注意:表与表之间的连接条件必须与过滤条件同时满足！否则会产生笛卡尔积

查看每个学生的名字，年龄以及其所在的班级名称和所在楼层

1:先确定数据来自于哪些表?
  学生信息来自于student表，班级信息来自于class表

  明确FROM子句:
  FROM student,class

2:确定两张表之间的数据关联关系
  学生表中某条记录的class_id的值与班级表中某条记录的id值相同的建立连接关系 

  明确WHERE子句中的连接条件:
  WHERE student.class_id=class.id

3:查询相应的字段并指定对应的过滤条件...
  查询数据时要指定表名.列名来明确数据来自哪张表的字段，此时由于表名可能较长，导致SQL编写可读性差:
  SELECT student.name,student.age,class.name,class.floor
  FROM student,class
  WHERE student.class_id=class.id

  解决办法，为表取别名:
  SELECT s.name,s.age,c.name,c.floor
  FROM student s,class c
  WHERE s.class_id=c.id

查看每个班级的名字，所在楼层，以及班主任的名字和工资

1:先确定数据来自于哪些表?
  班级表class和老师表teacher
  明确FROM子句:
  FROM class c,teacher t

2:确定两张表之间的数据关联关系
  班级表中的teacher_id字段的值与teacher表id字段值相同的记录关联在一起
  明确WHERE子句中的连接条件:
  WHERE c.teacher_id=t.id

3:查询相应的字段并指定对应的过滤条件...
  查询数据时要指定表名.列名来明确数据来自哪张表的字段，此时由于表名可能较长，导致SQL
  SELECT c.name,c.floor,t.name,t.salary
  FROM class c,teacher t
  WHERE c.teacher_id=t.id

在关联查询同时添加过滤条件

**关联条件要与过滤条件同时满足。条件之间要用AND连接**

查看"王克晶"是哪个班的班主任?

1:确定数据来自哪些表?
  查看的是班级信息，因此数据仅来自于班级表class

2:过滤条件来自哪张表?    老师的名字=王克晶
  过滤条件来自于老师表teacher：teacher.name='王克晶'

3:班级表与老师表的关联关系?
  class.teacher_id=teacher.id

SELECT c.name,t.name
FROM class c,teacher t
WHERE c.teacher_id=t.id     连接条件
AND t.name='王克晶'          过滤条件要与连接条件同时满足!!!  

N表联合查询至少要有N-1个连接条件，并且这些连接条件都要同时满足

查看老师"范传奇"所带的班的学生都有谁?

1:数据来自哪些表?
  学生信息来自于student,老师信息来自于teacher

2:过滤条件来自哪张表?
  老师的名字是"范传奇"，条件来自于teacher表。

3:student表于teacher表的关联关系?
问题:student表与teacher表本身之间没有关系。
解决办法:找到其他与这两张表之间都有关系的表，作为中间建立联系的表
班级表:class表.该表与student表关联条件:student.class_id=class.id
              该表与teacher表关联条件:class.teacher_id=teacher.id

SELECT s.name,s.age,s.class_id,c.id,c.name,c.teacher_id,t.id,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id    该条件确定学生表记录与班级表记录的对应关系
AND   c.teacher_id=t.id  该条件确定班级表记录与老师表记录的对应关系
AND t.name='范传奇'

查询教语文的老师的平均工资是多少?

1:统计前先准备数据->查询所有语文老师的工资
  1.1:查询的数据来自哪些表?  查工资salary来自teacher表
  1.2:过滤条件:科目名为"语文"  科目来自于科目表subject的name字段
  1.3:teacher表与subject表关联条件:teacher.subject_id=subject.id
2:在SELECT子句中对salary字段添加AVG()聚合函数  

SELECT AVG(t.salary)
FROM teacher t,subject su
WHERE t.subject_id=su.id
AND su.name='语文'

查看所有教**英语**的老师带的来自**上海**或**南京**的学生都有谁?

结果集中要体现:学生的名字，年龄，所在班级名称，老师名称以及来自的城市

SELECT s.name,s.age,c.name,t.name,l.name
FROM student s,class c,teacher t,location l,subject su
WHERE s.class_id=c.id          学生与班级建立关联关系
AND c.teacher_id=t.id             班级与老师建立关联关系
AND t.subject_id=su.id            老师与科目建立关联关系
AND s.location_id=l.id            学生与所在地建立关联关系
AND su.name='英语'            科目为英语的记录
AND l.name IN ('上海','南京')

查询工资最低的老师带的学生共多少人?

1:未知条件->谁的工资最低?  查询出最低工资是多少，之后谁的工资等于它
求出最低工资是多少:
SELECT MIN(salary) FROM teacher

2:数据来自那些表?  student表
3:过滤条件来自哪些表? 查询最低工资的老师，过滤条件来自于teacher表
4:student表与teacher表没有直接关联关系，因此还要借助:class表

准备所有要统计的数据，可以先将最低工资的老师和他的学生查询出来
SELECT s.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id    连接条件
AND c.teacher_id=t.id    连接条件
AND t.salary=(SELECT MIN(salary) FROM teacher)     该老师的工资等于最低工资

添加聚合函数
SELECT COUNT(*)
FROM student s,class c,teacher t
WHERE s.class_id=c.id    连接条件
AND c.teacher_id=t.id    连接条件
AND t.salary=(SELECT MIN(salary) FROM teacher)

关联关系通常是建立在主外键等值连接的基础上的

以学生表Student和班级表Class为例:

班级表中id字段作为班级表的**主键**:primary key(pk).   主键:非空且唯一

学生表中的学生为了与班级表的记录产生对应关系，因此在学生表中以class_id字段记录了其所在的班级的主键字段的值。那么class_id就称为是class表主键所对应的**外键**:foreign key(fk)

保存外键字段值的表在关联关系(一对多)中处于"多"的一方

多对多关系

两张表之间双向都是一对多就是多对多关系。多对多关系需要依靠一张关联关系表维系。

关联关系表中有两个字段分别记录了这两张表的主键字段。

t_stu_subject_score表，该表有两个字段stu_id，subject_id分别记录了student表的id和subject表的id。因此依靠这张表就可以实现student与subject表的多对多关系。

实际查询时，写法就是三张表关联查询即可