SQL语言04

### GROUP BY子句 -分组

GROUP BY 子句可以将结果集按照指定的字段值相同的记录分组,然后在组内根据聚合函数进行统计并得出结果

- GROUP BY子句是配合聚合函数使用的,如果SELECT子句中没有聚合函数,通常不会使用GROUP BY子句
- 当SELECT子句中出现了聚合函数,那么不在聚合函数中的其它字段都要出现在GROUP BY子句中.

#### 按照单字段分组

例

- 查看每种职位的老师平均工资是多少?

  ```sql
  SELECT AVG(salary),title 每组都统计平均工资,因为按照title分组,所以统计结果可以显示title
  FROM teacher
  GROUP BY title            将结果集按照title字段值相同的记录分组
  ```

  

- 查看每个班级各多少人?

  ```sql
  student表中class_id字段记录了该学生的班级号.那么班级号相同的记录就可以看作同一个班的学生,统计记录数
  
  SELECT COUNT(*),class_id
  FROM student
  GROUP BY class_id
  ```

  

- 查看学生每种职位各多少人,以及最大生日和最小生日?

  ```sql
  SELECT COUNT(*), MIN(birth) '最大生日',MAX(birth) '最小生日',job
  FROM student
  GROUP BY job
  ```



#### 按照多字段分组

GROUP BY子句中后面指定多个字段,此时会将结果集按照这些字段值组合相同的记录看作一组

例

- 查看同班级同性别的学生分别多少人?

  ```sql
  SELECT COUNT(*),gender,class_id
  FROM student
  GROUP BY gender,class_id 结果集中性别相同并且班级号相同的记录会被划分为一组
  ```

  

- 查看每个班每种职位各多少人?

  ```sql
  SELECT COUNT(*),job,class_id
  FROM student
  GROUP BY job,class_id
  ```

  

#### 按照聚合函数排序

- 查看每个科目老师的平均工资排名?

  ```sql
  SELECT AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  ORDER BY AVG(salary) DESC
  
  好的书写习惯:在SELECT子句中凡是出现了聚合函数,函数或表达式时都应当取别名来增加可读性
  并且当我们使用了别名时,在ORDER BY子句中还可以按照该别名排序
  SELECT AVG(salary) avg_sal,subject_id
  FROM teacher
  GROUP BY subject_id
  ORDER BY avg_sal DESC
  
  ```

  

#### 分组中的过滤条件

##### 问题

- 查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些.

  ```sql
  SELECT AVG(salary),subject_id
  FROM teacher
  WHERE AVG(salary)>6000
  GROUP BY subject_id
  
  ```

  

##### 错误

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

##### 原因

WHERE子句的过滤时机:在检索表中每条记录时用于过滤使用的,在生成结果集时WHERE会发挥作用.

我们希望将统计结果进行过滤,这应当实在检索表(WHERE发挥作用)之后生成结果集,并将结果集分组统计后才能得到统计结果从而进行过滤(这个过滤时机已经是在WHERE之后进行了).

#### HAVING子句

HAVING与WHERE一样都是用来添加过滤条件进行过滤的

他们的区别:

- **WHERE是对记录进行过滤**的,而**HAVING是对分组过滤**的

  只有满足HAVING要求的分组才会被保留

- 过滤时机不同

  - WHERE是在检索表时进行过滤的,产生结果集之前进行过滤的
  - HAVING是在产生结果集并根据GROUP BY对结果集分组后进行过滤的

- HAVING可以使用聚合函数作为过滤条件,WHERE不可以.

##### 例

- 查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些?

  ```sql
                            数据库对各子句的执行顺序
  SELECT AVG(salary),subject_id   4   根据筛选出的分组再进行统计并得到实际的结果集
  FROM teacher             1   确定数据来自哪张表
  GROUP BY subject_id            2   确定结果集按照那个字段分组,执行后结果集已经分号组
  HAVING AVG(salary)>6000         3   根据HAVING的过滤条件筛选出符合要求的分组
  ```

  

- 查看每个科目老师的平均工资，前提是该科目老师最高工资要超过9000

  ```sql
  SELECT AVG(salary) avg_sal,subject_id   4   将确定的两个分组按照AVG进行统计并生成结果集
  FROM teacher                   1   确定数据来自哪张表
  GROUP BY subject_id                 2   确定分组,一共6个分组    
  HAVING MAX(salary)>9000             3   仅有2个分组满足过滤要求
  ORDER BY avg_sal                5   对结果集按照指定的字段排序
  
  ```

  

- 查看科目老师的工资总和是多少?前提是该科老师的平均奖金要高于4000.

  ```sql
  SELECT SUM(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  HAVING AVG(IFNULL(comm,0))>4000
  
  ```

- 查看各科目男老师的平均工资是多少?前提是该科目老师最低工资高于4000.

  ```sql
  SELECT AVG(salary),subject_id
  FROM teacher
  WHERE gender='男'
  GROUP BY subject_id
  HAVING MIN(salary)>4000
  ```



### 子查询

#### 概念

嵌套在其他SQL语句中的一条DQL语句被称为子查询

#### 应用场景

- DQL中使用:基于一个查询结果集进行二次查询
- DML中使用:基于一个查询结果集对表数据进行增,删,改操作
- DDL中使用:基于结果集创建数据库对象(表,视图)

#### 子查询分类

- 单行单列子查询:查询结果集为一个值.仅一行一列
- 多行单列子查询:查询结果集为一组数据
- 多列子查询:结果集检索出来是一张表.多用于DDL语句.



#### 在DQL中使用子查询

##### 单行单列子查询

###### 例

- 查看比范传奇工资高的老师都有谁?

  ```sql
  1:未知条件:范传奇的工资是多少?
    SELECT salary FROM teacher WHERE name='范传奇'   通过这条DQL可以得知范传奇的工资:3000
    
  2:查看谁的工资高于范传奇的工资?
    SELECT name,salary 
    FROM teacher
    WHERE salary>(SELECT salary FROM teacher WHERE name='范传奇') 子查询要用()括起来,语法要求
  ```

  

- 查看哪些老师的工资是高于平均工资的?

  ```sql
  1:未知条件:老师的平均工资是多少?
    SELECT AVG(salary) FROM teacher
  2:查询要求:工资高于平均工资即可
    SELECT name,salary
    FROM teacher
    WHERE salary>(SELECT AVG(salary) FROM teacher)
  ```

- 查看和'李费水'在同一个班的学生都有谁?

  ```sql
  未知条件:李费水的班级号是多少?
  SELECT class_id FROM student WHERE name='李费水'
  
  SELECT name,gender,class_id
  FROM student
  WHERE class_id=(SELECT class_id FROM student WHERE name='李费水')
  ```

  

- 查看工资最高的老师的工资和奖金是多少?

  ```sql
  未知条件:最高工资是多少钱
  SELECT MAX(salary) FROM teacher
  
  SELECT name,salary,comm
  FROM teacher
  WHERE salary=(SELECT MAX(salary) FROM teacher)
  ```

  

##### 多行单列子查询

多行单列子查询会检索出一组数组,此时如果作为过滤条件使用时:

- 判断相等,不能再使用"=".任何一个值都不可能同时等于多个值,只能等于其中之一.

  因此判断相等时要伴随**IN**使用:IN(列表)

  **IN和NOT IN都可以使用**

  

- 判断>,>=,<,<=时,要伴随**ANY**和**ALL**使用

  - \>ANY(列表):大于列表中的其中之一即可.判断标准:大于最小的即可
  - \>ALL(列表):大于列表中所有.判断标准:大于最大的
  - <ANY(列表):小于列表其中之一,小于最大的即可
  - <ALL(列表):小于列表所有,小于最小的

###### 例

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

  ```sql
  未知条件:这两个人的班级号是多少?
  SELECT class_id FROM student WHERE name IN ('祝雷','李费水')
  
  SELECT name,gender,class_id
  FROM student
  WHERE class_id=(SELECT class_id
                  FROM student
                  WHERE name IN ('祝雷','李费水'))
  ```

  

  报错:子查询返回了多于一行的记录.因为等于判断不能同时等于多个值.

  实际写法:

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

  

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

  ```sql
  未知条件:科目2和科目4老师的工资都是多少?
  SELECT salary FROM teacher WHERE subject_id IN(2,4)
  
  SELECT name,salary,subject_id
  FROM teacher
  WHERE salary>ALL(SELECT salary FROM teacher WHERE subject_id IN(2,4))
  
  
  另一种思路
  未知条件:科目2和科目4老师的最高工资是多少?
  SELECT MAX(salary) FROM teacher WHERE subject_id IN (2,4)
  
  SELECT name,salary,subject_id
  FROM teacher
  WHERE salary>(SELECT MAX(salary) FROM teacher WHERE subject_id IN (2,4))
  
  ```

  



#### 在DML语句中使用子查询

##### 例

- 给与'范传奇'负责同一科目的所有老师工资涨500

  ```sql
  未知条件:'范传奇'负责的科目id?
  SELECT subject_id FROM teacher WHERE name='范传奇'
  
  基于上述DQL进行DML操作
  UPDATE teacher
  SET salary = salary + 500
  WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='范传奇')
  
  ```

  

#### 在DDL语句中使用子查询

以创建表为例,可以将一个查询结果集当做表创建出来

###### 例

- 创建一张表,该表中记录了每个科目老师的工资情况,要求展示:最高,最低,总和和平均工资以及该科目id

  ```sql
  SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  
  
  创建表:
  CREATE TABLE teacher_salary_info(
   max_sal DOUBLE(7,2),
      min_sal DOUBLE(7,2),
      sum_sal DOUBLE(7,2),
      avg_sal DOUBLE(7,2),
      subject_id INT
  )
  表创建后还要进行DML操作
  执行若干次的 INSERT ....
  上述操作太麻烦
  
  将子查询的结果集当做表创建出来
  SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  该子查询的结果集就是我们期望的表的样子
  那么此时我们可以执行如下DDL
  CREATE TABLE teacher_salary_info
  AS
  SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  上述SQL中生成的表字段名是使用SELECT子句中字段名,如果SELECT中含有函数或表达式时要指定别名,此时生成的
  表的字段名会采用该别名:
  CREATE TABLE teacher_salary_info
  AS
  SELECT 
   MAX(salary) max_sal,MIN(salary) min_sal,
   SUM(salary) sum_sal,AVG(salary) avg_sal,
   subject_id
  FROM teacher
  GROUP BY subject_id
  
  ```

  

## 关联查询(重点)

### 定义

联合多张表查询数据.查询结果集中的数据来自于多张表.

### 关联关系

表与表中的记录在设计是会产生一种对应关系,它称为关联关系

#### 关联关系的分类

- 一对一:A表的一条记录仅对应B表的一条记录,称为A与B表存在一对一关系
- 一对多:A表中的一条记录可以对应B表中的多条记录,称为A与B表存在一对多关系
- 多对多:当A与B表双向都存在一对多关系时就称为多对多关系

### 连接条件

在关联关系中,非常重要的一点就是指定连接条件.

作用是让数据库清楚两张表间记录与记录之间的对应关系.

如果指定的连接条件无效或没有指定连接条件时,会产生**笛卡尔积**,这通常是一个无意义的结果集,要尽量避免.

### 语法

```sql
SELECT 各表中的字段
FROM A表,B表[,C表...]
WHERE 连接条件    用来让数据库清楚表与表之间记录的对应关系
AND 过滤条件        用来筛选数据的.
                   注:连接条件要与过滤条件同时满足!!!

```



### 例

- 查看每个老师以及其负责课程科目名?

  ```sql
  SELECT teacher.name,teacher.age,subject.name
  FROM teacher,subject
  WHERE teacher.subject_id=subject.id
  
  为了区分字段属于哪张表,我们需要在字段前添加表名,格式为:表名.字段名
  当表名过长时,这样写过于臃肿.对此我们可以为表取别名,此时我们可以用:表别名.字段名
  SELECT t.name,t.age,s.name
  FROM teacher t,subject s
  WHERE t.subject_id=s.id
  
  
  ```

  数据库在进行关联查询时,会遍历A表每一条记录并与B表每条记录连接一次,但是只有满足连接条件的记录才会将它们中需要查询的字段提取出来形成结果集中的一条记录.

 

- 不指定连接条件时,会产生**笛卡尔积**

  ```sql
  SELECT t.name,t.age,s.name
  FROM teacher t,subject s
  ```

  **笛卡尔积的产生**

  **当不指定连接条件时,数据库在进行关联查询时,仍然会用A表一条记录与B表每条记录连接一次,并产生结果集中的一条记录.此时的数据量为A表记录数与B表记录数的乘积.**

  **当表中数据量大时,这样的结果集开销巨大,甚至可能导致服务器宕机.因此要尽量避免.**

  

- 查看班级的名称和对应的班主任(老师)是谁?

  ```
  1:确定数据来自哪些表,确定FROM子句
    需要查询班级名称,班级名称来自class表中的字段
    班主任名字(老师名字),老师名字来自teacher表中的字段
    确定:FROM class c,teacher t
    
  2:当表明确了,就要确定表与表中记录的对应关系,确定连接条件
    班级表中有一个字段teacher_id记录了该班级班主任(老师)的id
    确定连接条件:c.teacher_id=t.id
    
  SELECT c.name,t.name  
  FROM class c,teacher t
  WHERE c.teacher_id=t.id
  ```

  

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

  ```sql
  1:确定数据来自哪些表,确定FROM子句
    student s,class c
  2:当表明确了,就要确定表与表中记录的对应关系,确定连接条件
    s.class_id=c.id
  
  SELECT s.name,s.age,c.name,c.floor
  FROM student s,class c
  WHERE s.class_id=c.id
  
  
  ```

  

### 连接条件要与过滤条件同时满足

#### 例

- 王克晶是哪个班的班主任?列出:班级名称，楼层，老师名称，工资

  ```sql
  1:数据来自哪些表?
    teacher t,class c
  2:连接条件?
    c.teacher_id=t.id
  3:过滤条件?
    t.name='王克晶'
  
  SELECT c.name,c.floor,t.name,t.salary
  FROM teacher t,class c
  WHERE c.teacher_id=t.id           连接条件
  AND t.name='王克晶'            过滤条件
  
  ```

  

- 查看三年级的班级班主任都是谁?要列出班级名称，所在楼层，班主任名字和工资

  ```sql
  SELECT c.name,c.floor,t.name,t.salary
  FROM class c,teacher t
  WHERE c.teacher_id=t.id
  AND c.name LIKE '3年级%'
  ```

- 查看来自南京的学生都有谁?要列出城市名字，学生名字，年龄，性别

  ```sql
  SELECT l.name,s.name,s.age,s.gender
  FROM student s,location l
  WHERE s.location_id=l.id
  AND l.name='南京'
  ```

- 查看5年级的中队长都有谁?要列出学生名字，年龄，性别，职位和所在班级的名字以及楼层

  ```sql
  SELECT s.name,s.age,s.job,c.name,c.floor
  FROM student s,class c
  WHERE s.class_id=c.id
  AND c.name LIKE '5年级%'
  AND s.job='中队长'
  ```

  

### N张表关联查询

N张表关联查询就要有至少N-1个连接条件,并且这些**连接条件要同时满足**

#### 例

- 查看"范传奇"所带班级的学生都有谁?要列出:学生名字，年龄，班级名称，老师名字

  ```sql
  1:数据来自哪些表
    student s,class c,teacher t
  2:关联关系-3张表关联就要有2个连接条件
    学生表与班级表的关联关系:s.class_id=c.id
    班级表与老师表的关联关系:c.teacher_id=t.id
  3:过滤条件
    老师的名字:t.name='范传奇'
    
    SELECT s.name,s.age,c.name,t.name
    FROM student s,class c,teacher t
    WHERE s.class_id=c.id               关联条件    关联条件之间一定是AND连接
    AND c.teacher_id=t.id              关联条件
    AND t.name='范传奇'                 过滤条件
                                          关联条件要与过滤条件同时满足
  ```

  

- 查看1年级1班的同学的名字和来自的城市

  ```sql
  1:数据来自哪张表以及过滤条件来自哪张表?
    student s,location l,class c
  2:确定关联关系
    学生与班级的关系:s.class_id=c.id
    学生与城市的关系:s.location_id=l.id
  3:过滤条件?
    班级名字为:c.name='1年级1班'
    
  SELECT s.name,l.name,c.name  
  FROM student s,location l,class c
  WHERE s.class_id=c.id
  AND s.location_id=l.id
  AND c.name='1年级1班'
   
  
  ```

### 综合练习

#### 题干

```sql
1.查看来自北京的学生都是谁?
2.教"英语"的老师都是谁?
3.刘苍松所带班级的学生都有谁?
4.教语文的老师所带的班级有哪些?
5.王克晶所带的班级学生都来自哪些城市(去重)?
6.3年级的几个班主任都教哪些课程?
7.工资高于10000的老师所带班里的大队长都是谁?
8."李费水"的班主任教哪门课?
9.所在4楼的班里的大队长和中队长以及班主任都是谁?
10.全校最小的同学的班主任是谁?
```

#### 答案

```sql
1.查看来自北京的学生都是谁?
  SELECT s.name,l.name
  FROM student s,location l
  WHERE s.location_id=l.id
  AND l.name='北京'
  
2.教"英语"的老师都是谁?
  SELECT t.name,su.name
  FROM teacher t,subject su
  WHERE t.subject_id=su.id
  AND su.name='英语'
  
3.刘苍松所带班级的学生都有谁?
  SELECT t.name,s.name,c.name
  FROM student s,class c,teacher t
  WHERE s.class_id=c.id
  AND c.teacher_id=t.id
  AND t.name='刘苍松'

4.教语文的老师所带的班级有哪些?
  SELECT c.name,t.name,su.name
  FROM subject su,teacher t,class c
  WHERE su.id=t.subject_id
  AND t.id=c.teacher_id
  AND su.name='语文'
  
5.王克晶所带的班级学生都来自哪些城市(去重)?
  SELECT DISTINCT l.name
  FROM teacher t,class c,student s,location l
  WHERE t.id=c.teacher_id
  AND s.class_id=c.id
  AND s.location_id=l.id
  AND t.name='王克晶'
  
6.3年级的几个班主任都教哪些课程?
  SELECT c.name,t.name,su.name
  FROM class c,teacher t,subject su
  WHERE c.teacher_id=t.id
  AND t.subject_id=su.id
  AND c.name LIKE '3年级%'
  
7.工资高于10000的老师所带班里的大队长都是谁?
  SELECT s.name,s.job,c.name,t.name,t.salary
  FROM teacher t,class c,student s
  WHERE t.id=c.teacher_id
  AND s.class_id=c.id
  AND t.salary>10000
  AND s.job='大队长'
  
8."李费水"的班主任教哪门课?
  SELECT s.name,c.name,t.name,su.name
  FROM student s,class c,teacher t,subject su
  WHERE s.class_id=c.id
  AND c.teacher_id=t.id
  AND t.subject_id=su.id
  AND s.name='李费水'
  
9.所在4楼的班里的大队长和中队长以及班主任都是谁?
  SELECT s.name,s.job,c.name,c.floor,t.name
  FROM student s,class c,teacher t
  WHERE s.class_id=c.id
  AND c.teacher_id=t.id
  AND c.floor=4
  AND s.job IN ('大队长','中队长')
  
  不使用IN,改为使用OR
  SELECT s.name,s.job,c.name,c.floor,t.name
  FROM student s,class c,teacher t
  WHERE s.class_id=c.id
  AND c.teacher_id=t.id
  AND c.floor=4
  AND (s.job='大队长' OR job='中队长') 提高OR的优先级
  
10.全校最小的同学的班主任是谁?
   未知条件:全校最小的生日是哪天?
   子查询:
   SELECT MAX(birth) FROM student

   SELECT DISTINCT t.name 
   FROM student s,class c,teacher t
   WHERE s.class_id=c.id
   AND c.teahcer_id=t.id
   AND s.birth = (SELECT MAX(birth) FROM student) 最小生日有两个学生
```



### 关联查询中使用聚合函数

将关联查询的结果集进行统计

#### 例

- 查看范传奇所带班级的学生共多少人?

  ```sql
  1:查询出参与统计的所有记录
    1.1:查询出学生信息,查询条件"范传奇"所带班级(关联查询)
    1.2:查询的数据以及过滤条件来自哪些表?
        student s,class c,teacher t
    1.3:连接条件
        s.class_id=c.id
        c.teacher_id=t.id
    1.4:过滤条件
        老师的名字:t.name='范传奇'
  2:在查询的结果集基础上添加聚合函数统计实际结果
  
  1:查询待统计的记录--"范传奇"所带学生都有谁?
    SELECT s.name,c.name,t.name
    FROM student s,class c,teacher t
    WHERE s.class_id=c.id
    AND c.teacher_id=t.id
    AND t.name='范传奇'
  
  2:在上述SQL基础上添加聚合函数
    SELECT COUNT(*)
    FROM student s,class c,teacher t
    WHERE s.class_id=c.id
    AND c.teacher_id=t.id
    AND t.name='范传奇'
  ```

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

  ```sql
  1:准备参与统计的数据:语文老师的工资
    1.1:查询的数据和过滤条件来自哪些表?
        teacher t,subject su
    1.2:连接条件
        t.subject_id=su.id
    1.3:过滤条件
        su.name='语文'
    
    列出所有语文老师的工资
    SELECT t.salary
    FROM teacher t,subject su
    WHERE t.subject_id=su.id
    AND su.name='语文'
    
    2:在上述SQL基础上添加聚合函数
    SELECT AVG(t.salary)
    FROM teacher t,subject su
    WHERE t.subject_id=su.id
    AND su.name='语文'
  
  ```

  

- 查看教每门课老师的平均工资是多少(GROUP BY)?列出平均工资和科目名称

  ```sql
  1:准备参与统计的记录-将所有老师的工资及所带科目查询出来
    SELECT t.salary,su.name
    FROM teacher t,subject su
    WHERE t.subject_id=su.id
  
  2:在上述SQL基础上添加聚合函数以及分组
    SELECT AVG(t.salary),su.name
    FROM teacher t,subject su
    WHERE t.subject_id=su.id
    GROUP BY su.name         将科目名称一样的老师看作一组
  ```

  

- 仅查看平均工资高于6000的那些科目的老师平均工资是多少?列出平均工资和科目名称

  ```sql
  SELECT AVG(t.salary),su.name
  FROM teacher t,subject su
  WHERE t.subject_id=su.id
  GROUP BY su.name
  HAVING AVG(t.salary)>6000
  ```

  

- 查看工资最高的老师班里的学生共多少人?

  ```sql
  1:未知条件-->最高工资是多少
  2:列出需要参与统计的记录-->列出工资最高的老师所带班级的学生都有谁?
  3:再添加聚合函数统计即可
  
  1:找到最高工资
    SELECT MAX(salary) FROM teacher
  2:列出工资最高的老师所带班级的学生都有谁?
    SELECT s.name,c.name,t.name,t.salary
    FROM student s,class c,teacher t
    WHERE s.class_id=c.id
    AND c.teacher_id=t.id
    AND t.salary=(SELECT MAX(salary) FROM teacher)
  3:在上述SQL中添加聚合函数
    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 MAX(salary) FROM teacher)
  ```

### 综合练习

#### 题干

```sql
#1.教语文的老师带班级各多少学生？
SELECT t.name,COUNT(*)
FROM teacher t,student s,class c,subject su
WHERE s.class_id= t.id
AND t.subject_id = c.id
AND c.teacher_id = su.id
AND su.name = '语文'
GROUP BY t.name;
#2.每门课的老师所带的班级各多少学生
SELECT * FROM subject;
SELECT t.name,COUNT(*)
FROM teacher t,student s,class c,student su
WHERE s.class_id = t.id
AND t.subject_id = c.id
AND c.teacher_id = su.id
GROUP BY t.name;
#3.来自上海的学生的班主任都有谁
SELECT DISTINCT t.name
FROM teacher t,student s,location l,class c
WHERE s.class_id = t.id
AND t.subject_id = c.id
AND s.location_id = l.id
AND l.name = '上海';
#4.来自南京的学生共多少人
SELECT COUNT(s.name)
FROM student s,location l
WHERE s.location_id = l.id
AND l.name = '南京'
GROUP BY l.name;
#5.来自武汉的男同学和女同学分别多少人
SELECT COUNT(s.name)
FROM student s,location l
WHERE S.location_id = l.id
AND l.name = '武汉'
GROUP BY s.gender;
#6.每个城市的学生各多少人
SELECT COUNT(*),l.name
FROM student s,location l
WHERE s.location_id = l.id
GROUP BY l.name;
#7.高于平均工资的老师所带的班级分别多少学生
SELECT COUNT(*), t.name,c.name
FROM teacher t,student s,class c
WHERE s.class_id = t.id
AND t.subject_id = c.id
AND t.salary > (SELECT AVG(salary) FROM teacher)
GROUP BY t.name, c.name;
#8.每个老师班里各多少来自郑州的学生
SELECT COUNT(*),t.name
FROM teacher t,student s,class c,location l
WHERE c.teacher_id= t.id
AND s.class_id= c.id
AND s.location_id = l.id
AND l.name = '郑州'
GROUP BY t.name;