数据库知识点四

74 阅读 0 评论 49 点赞
我是靠谱客的博主忧郁盼望，最近开发中收集的这篇文章主要介绍数据库知识点四，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述

#### 练习

1. 查看1年级1班所有同学的语文成绩是多少?

   ```
   1:数据来自那些表?查看成绩，学生姓名，科目,班级名称。表:t_stu_subject_score,student,subject,class
   2:过滤条件来自哪些表?1年级1班：class表，语文:subject表
   3:4张表要有3个连接条件
   
   SELECT s.name,su.name,sss.score,c.name
   FROM student s,class c,subject su,t_stu_subject_score sss
   WHERE s.class_id=c.id
   AND	s.id=sss.stu_id
   AND su.id=sss.subject_id
   AND c.name='1年级1班'
   AND su.name='语文'
     
   ```

   

2. 统计1年级1班数学成绩的平均值?

   ```
   SELECT AVG(sss.score)
   FROM student s,class c,subject su,t_stu_subject_score sss
   WHERE s.class_id=c.id
   AND	s.id=sss.stu_id
   AND su.id=sss.subject_id
   AND c.name='1年级1班'
   AND su.name='数学'
   ```

   

3. 统计6年级的英语成绩的平均值?

   ```
   SELECT AVG(sss.score)
   FROM student s,class c,subject su,t_stu_subject_score sss
   WHERE s.class_id=c.id
   AND	s.id=sss.stu_id
   AND su.id=sss.subject_id
   AND c.name LIKE '6年级%'
   AND su.name='英语'
   ```

   

4. 查看4年级数学成绩最高的同学名字?

   ```
   1:4年级数学最高的分数是多少?(子查询)
   2:找到4年级同学中数学分数=该最高值的
   
   1:
   SELECT MAX(sss.score)
   FROM student s,class c,subject su,t_stu_subject_score sss
   WHERE s.class_id=c.id
   AND	s.id=sss.stu_id
   AND su.id=sss.subject_id
   AND c.name LIKE '4年级%'
   AND su.name='数学'
   
   2:
   SELECT s.name,c.name,sss.score
   FROM student s,class c,subject su,t_stu_subject_score sss
   WHERE s.class_id=c.id
   AND	s.id=sss.stu_id
   AND su.id=sss.subject_id
   AND c.name LIKE '4年级%'
   AND su.name='数学'
   AND sss.score=(SELECT MAX(sss.score)
   		       FROM student s,class c,subject su,t_stu_subject_score sss
   			   WHERE s.class_id=c.id
   			   AND	s.id=sss.stu_id
   			   AND su.id=sss.subject_id
   			   AND c.name LIKE '4年级%'
   			   AND su.name='数学')
   						 
   ```

   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
5. 查看"刘苍松"所带班级的英语平均分?

   ```
   1:数据来自哪些表
     t_stu_subject_score,teacher,class,subject
   2:过滤条件来自哪些表?
     teacher,subject
     
   SELECT AVG(sss.score)
   FROM student s,class c,teacher t,subject su,t_stu_subject_score sss
   WHER s.class_id=c.id
   AND s.id=sss.stu_id
   AND sss.subject_id=su.id
   AND c.teacher_id=t.id
   AND t.name='刘苍松'
   AND su.name='英语'
     
   ```

   

6. 查看工资最高的老师所带班级的各科成绩的平均分，最高分和最低分分别是多少?

   ```
   1:组织统计的数据
     科目  最高分  				最低分   			平均分
     语文  MAX(sss.score)     MIN(sss.score)    	AVG(sss.score)
     需统计的数据展示的内容有科目，分数。按照科目分组统计分数即可
   
   2:查看工资最高的老师所带班级所有同学每门课的分数是多少(要统计的数据)
     2.1:工资最高的老师是谁?
     	  SELECT name 
     	  FROM teacher 
     	  WHERE salary=(SELECT MAX(salary) FROM teacher)
     
     2.2:该老师所在的班级
    
     2.2:关联查询
     	  数据来自哪些表?
         成绩:t_stu_subject_score  科目:subject
         过滤条件?
         工资最高(2.1的子查询完成)的老师的名字 teacher表
     
   SELECT MAX(sss.score),MIN(sss.score),AVG(sss.score),su.name
   FROM student s,class c,t_stu_subject_score sss,subject su,teacher t
   WHERE s.class_id=c.id
   AND s.id=sss.stu_id
   AND sss.subject_id=su.id
   AND c.teacher_id=t.id
   AND t.salary=(SELECT MAX(salary) FROM teacher)
   GROUP BY su.name
       
         
   ```

   

7. 查看所有大队长的5门成绩平均分是多少？

   ```
   SELECT s.name,AVG(sss.score) avg_score
   FROM student s,t_stu_subject_score sss,subject su
   WHERE s.id=sss.stu_id
   AND sss.subject_id=su.id
   AND s.job='大队长'
   GROUP BY s.name
   ORDER BY avg_score DESC
   ```

   

8. 教"数学"的老师所带班的英语平均分分别是多少? 

   ```
   
   1:查看假定1年级1班和1年级2班所有学生英语成绩
   SELECT s.name,su.name,sss.score,c.name
   FROM student s,class c,teacher t,t_stu_subject_score sss,subject su
   WHERE s.class_id=c.id
   AND c.teacher_id=t.id
   AND s.id=sss.stu_id
   AND sss.subject_id=su.id
   AND su.name='英语'
   AND c.name IN ('1年级1班','1年级2班')
   
   2:统计两个班英语的平均值
   SELECT AVG(sss.score),c.name
   FROM student s,class c,teacher t,t_stu_subject_score sss,subject su
   WHERE s.class_id=c.id
   AND c.teacher_id=t.id
   AND s.id=sss.stu_id
   AND sss.subject_id=su.id
   AND su.name='英语'
   AND c.name IN ('1年级1班','1年级2班')
   GROUP BY c.name
   
   3:查看教数学的老师所带的班级名称?
   SELECT c.name
   FROM class c,teacher t,subject su
   WHERE c.teacher_id=t.id
   AND t.subject_id=su.id
   AND su.name='数学'
   
   4:将步骤三内容作为自查徐替换步骤2中班级名称的比较即可
   SELECT AVG(sss.score),c.name
   FROM student s,class c,teacher t,t_stu_subject_score sss,subject su
   WHERE s.class_id=c.id
   AND c.teacher_id=t.id
   AND s.id=sss.stu_id
   AND sss.subject_id=su.id
   AND su.name='英语'
   AND c.name IN (SELECT c.name
   			   FROM class c,teacher t,subject su
   			   WHERE c.teacher_id=t.id
   			   AND t.subject_id=su.id
   			   AND su.name='数学')
   GROUP BY c.name
   ```

   

9. 2年级语文最高分学生所在班里的女同学都来自哪个城市?

   ```
   1:查询需求
     学生来自哪个城市?(去重)
   2:过滤条件:
     2.1女同学
     2.2班级号与2年级语文最高分的同学相同
   3:从哪查?
     student,location
     
     
   先解决:2年级语文最高分的同学的班级号
   1:找到2年级语文最高分是多少
     class,student,subject,t_stu_subject_score
     
     先列出2年级所有学生的语文成绩
     SELECT s.name,su.name,sss.score,c.name
     FROM student s,class c,t_stu_subject_score sss,subject su
     WHERE s.class_id=c.id
     AND s.id=sss.stu_id
     AND sss.subject_id=su.id
     AND c.name LIKE '2年级%'
     AND su.name='语文'
     
     找到2年级语文最高分?
     SELECT MAX(sss.score)
     FROM student s,class c,t_stu_subject_score sss,subject su
     WHERE s.class_id=c.id
     AND s.id=sss.stu_id
     AND sss.subject_id=su.id
     AND c.name LIKE '2年级%'
     AND su.name='语文'
     
     找谁的成绩是最高分，且她所在的班级
     SELECT c.name
     FROM student s,class c,t_stu_subject_score sss,subject su
     WHERE s.class_id=c.id
     AND s.id=sss.stu_id
     AND sss.subject_id=su.id
     AND su.name='语文'
     AND c.name LIKE '2年级%'
     AND sss.score=(SELECT MAX(sss.score)
     				 FROM student s,class c,t_stu_subject_score sss,subject su
     				 WHERE s.class_id=c.id
     				 AND s.id=sss.stu_id
     				 AND sss.subject_id=su.id
    				 AND c.name LIKE '2年级%'
    				 AND su.name='语文')
   
   查看2年级1班和3班所有的女同学所在的城市
   SELECT s.name,s.gender,l.name,c.name
   FROM student s,location l,class c
   WHERE s.location_id=l.id
   AND s.class_id=c.id
   AND s.gender='男'
   AND c.name IN ('2年级1班','2年级3班')
   
   最终将IN的部分换成上面的查询
   SELECT s.name,s.gender,l.name,c.name
   FROM student s,location l,class c
   WHERE s.location_id=l.id
   AND s.class_id=c.id
   AND s.gender='男'
   AND c.name IN (SELECT c.name
     			   FROM student s,class c,t_stu_subject_score sss,subject su
     			   WHERE s.class_id=c.id
     			   AND s.id=sss.stu_id
     			   AND sss.subject_id=su.id
     			   AND su.name='语文'
     			   AND c.name LIKE '2年级%'
     			   AND sss.score=(SELECT MAX(sss.score)
     				 			 FROM student s,class c,t_stu_subject_score sss,subject su
     				 		     WHERE s.class_id=c.id
     				 			 AND s.id=sss.stu_id
     				 		     AND sss.subject_id=su.id
    				 	         AND c.name LIKE '2年级%'
    				 		     AND su.name='语文'))
   
   
   
   ```

   

#### 内连接

##### 语法

```
SELECT t1.xxx,t2.xxx,...
FROM 表1 t1
JOIN 表2 t2 ON 与表1的连接条件
[JOIN 表3 t3 ON 与表1或表2的连接条件]

```

内连接的查询与关联查询一致，却别在于我们将关联关系(连接条件)单独定义在JOIN后面跟的ON子句上。

优点:关联查询时结构清晰，连接条件与过滤条件分开在不同的子句定义。ON子句定义连接条件，WHERE子句定义过滤条件

##### 例

- 查看每个班的班主任是谁?

  1. 原来的写法

     ```
     SELECT c.name,t.name
     FROM class c,teacher t
     WHERE c.teacher_id=t.id
     ```

  2. 内连接的写法

     ```
     SELECT c.name,t.name
     FROM class c
     JOIN teacher t ON c.teacher_id=t.id
     ```

     

- 查看范传奇和刘苍松带的班级是哪个?

  1. 原来的写法

     ```
     SELECT c.name,t.name
     FROM class c,teacher t
     WHERE c.teacher_id=t.id     连接条件
     AND t.name IN ('范传奇','刘苍松')
     ```

     

  2. 内连接的写法

     ```
     SELECT c.name,t.name
     FROM class c
     JOIN teacher t ON c.teacher_id=t.id
     WHERE t.name IN ('范传奇','刘苍松')
     ```

     

- 查看1年级1班的学生的名字和其所来自的城市?

  1. 原来的写法

     ```
     SELECT s.name,c.name,l.name
     FROM student s,class c,location l
     WHERE s.class_id=c.id
     AND s.location_id=l.id
     AND c.name='1年级1班'
     ```

     

  2. 内连接写法

     ```
     SELECT s.name,c.name,l.name
     FROM student s
     JOIN class c ON s.class_id=c.id
     JOIN location l ON s.location_id=l.id
     WHERE c.name='1年级1班'
     ```

     

- 查看2年级每个班的学生的名字以及其班主任的名字?

  ```
  SELECT s.name,c.name,t.name
  FROM student s
  JOIN class c ON s.class_id=c.id
  JOIN teacher t ON c.teacher_id=t.id
  WHERE c.name LIKE '2年级%'
  ```

  

- 查看3年级2班每个同学的数学成绩，并按成绩的降序展示

  ```
  SELECT s.name,su.name,sss.score,c.name
  FROM class c
  JOIN student s ON s.class_id=c.id
  JOIN t_stu_subject_score sss ON s.id=sss.stu_id
  JOIN subject su ON sss.subject_id=su.id
  WHERE c.name='3年级2班'
  AND su.name='数学'
  ORDER BY sss.score DESC
  ```

  

- 查看4年级每个班的数学平均分是多少?

  ```
  SELECT AVG(sss.score),su.name,c.name
  FROM class c
  JOIN student s ON s.class_id=c.id
  JOIN t_stu_subject_score sss ON s.id=sss.stu_id
  JOIN subject su ON sss.subject_id=su.id
  WHERE c.name LIKE '4年级%'
  AND su.name='数学'
  GROUP BY c.name,su.name     班级名相同且科目相同的记录看作一组
  ```

  GROUP BY子句中如果出现了多个字段，则是将结果集中指定的这些字段值的组合相同的记录看作一组。



#### 外连接

外连接的作用是将关联查询中不满足关联条件的记录显示在结果集中。

外连接分类:

- 左外连接 LEFT JOIN

  左外连接是将LEFT JOIN左侧的表做为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于LEFT JOIN右侧表的字段时，不满足连接条件的记录值全为NULL

- 右外连接 RIGHT JOIN

  右外连接是将RIGHT JOIN右侧的表做为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于RIGHT JOIN左侧表的字段时，不满足连接条件的记录值全为NULL

##### 例

- 查看每个班级名字和班主任的名字

  ```
  SELECT c.name,t.name
  FROM class c
  JOIN teacher t ON c.teacher_id=t.id
  ```

  class表中如:1年级4班没有被列出在结果集中

  teacher表中如:韩少云没有被列出在结果集中

  原因:这些记录不满足连接条件 c.teacher_id=t.id

  结论:不满足连接条件的记录不会在关联查询和内连接中被查询出来

  

- 查看每个班级名字和班主任的名字,如果该老师不带班也要把老师显示出来

  需求:当teacher表中有不满足连接条件的记录时，也要将其显示出来

  ```
  SELECT c.name,t.name
  FROM class c
  RIGHT JOIN teacher t ON c.teacher_id=t.id
  ```

  

- 查看每个班级名字和班主任的名字,如果该班没有班主任，也要将该班级查询出来

  ```
  SELECT c.name,t.name
  FROM class c
  LEFT JOIN teacher t ON c.teacher_id=t.id
  ```

  

- UNION取并集，将左连接与右连接并集，可以达到全连接效果

  union可以连接两个DQL语句的结果集，并将它们并为一个结果集，其中重复的记录仅显示一次。

  ```
  SELECT c.name,t.name
  FROM class c
  LEFT JOIN teacher t ON c.teacher_id=t.id
  UNION
  SELECT c.name,t.name
  FROM class c
  RIGHT JOIN teacher t ON c.teacher_id=t.id
  ```

  

#### 自连接

同一张表中的一条数据可以对应多条数据。

自连接通常用于保存具有相同属性且存在上下级关系的树状结构数据使用

例如

- 公司中的组织架构
- 电商中的类别树

例

- 查看每个老师和他的领导是谁?

  ```
  关联查询写法:
  SELECT t.name,m.name
  FROM teacher t,teacher m
  WHERE t.manager=m.id
  
  内连接
  SELECT t.name,m.name
  FROM teacher t
  JOIN teacher m ON t.manager=m.id
  ```

  

- 查看刘苍松的下属都有谁?

  ```
  SELECT t.name
  FROM teacher t
  JOIN teacher m ON t.manager=m.id
  WHERE m.name='刘苍松'
  ```

  

- 把所有老师和他的上司列出来，没有上司的也要把该老师列出来

  ```
  SELECT t.name,m.name
  FROM teacher t
  LEFT JOIN teacher m ON t.manager=m.id
  ```

  

- 查看刘苍松的下属所带班级的名称分别是什么?

  ```
  SELECT t.name,c,name,m.name
  FROM teacher t
  JOIN teacher m ON t.manager=m.id
  JOIN class c ON c.teacher_id=t.id
  WHERE m.name='刘苍松'
  ```

  

- 查看3年级2班的班长是谁?

  ```
  SELECT s.name
  FROM student s
  JOIN class c ON c.id=s.class_id
  WHERE c.name='3年级2班'
  AND s.id=s.team_leader
  ```

  

- 刘苍松的下属所带班级的班长都是谁?

  ```
  SELECT s.name,c.name,t.name,m.name
  FROM teacher t
  JOIN teacher m ON t.manager=m.id
  JOIN class c ON c.teacher_id=t.id
  JOIN student s ON s.class_id=c.id
  WHERE m.name='刘苍松'
  AND s.id=s.team_leader
  ```

  

- 年龄最大的学生所在班的班主任的上司是谁?

  ```
  SELECT 
  	s.name '学生的名字',c.name '所在班级',
  	t.name '班主任',m.name '班主任的上司'
  FROM student s
  JOIN class c ON s.class_id=c.id
  JOIN teacher t ON c.teacher_id=t.id
  JOIN teacher m ON t.manager=m.id
  WHERE s.birth=(SELECT MIN(birth) FROM student)
  
  ```

  

- 刘苍松的下属所带的班级各多少人?

  ```
  1:统计的是哪张表的记录数?
    学生的数量。数据来自学生表student
  2:过滤条件
    上司是刘苍松，来自表:teacher(上司表)
  3:学生表与上司表没有直接联系，因此要找到关联关系表
    student->class->teacher(老师)->teacher(上司)
  
  准备要统计的数据
  列出的数据:
  学生名,所在班级,班主任，班主任的上司
  SELECT s.name,c.name,t.name,m.name
  FROM student s
  JOIN class c ON s.class_id=c.id
  JOIN teacher t ON c.teacher_id=t.id
  JOIN teacher m ON t.manager=m.id
  WHERE m.name='刘苍松'
  
  最终:
  SELECT COUNT(*),s.gender,c.name,t.name,m.name
  FROM student s
  JOIN class c ON s.class_id=c.id
  JOIN teacher t ON c.teacher_id=t.id
  JOIN teacher m ON t.manager=m.id
  WHERE m.name='刘苍松'
  GROUP BY s.gender,c.name,t.name,m.name
  ORDER BY c.name
  
  
  ```

  

#### 关联查询中未指定连接条件，或指定的连接条件无效时会产生笛卡尔积

结果集的数据量是参数查询表中记录数的乘积,除非应用需要，否则**应尽量避免**

```
SELECT COUNT(*)
FROM class c,teacher t