day06-多表查询02

多表查询02

4.表复制

• 自我复制数据（蠕虫复制）
  

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以用此法为表创建海量数据

1. -- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以用此法为表创建海量数据
3. CREATE TABLE my_tab01(
4.         id INT ,
5.         `name` VARCHAR(32),
6.         sal DOUBLE,
7.         job VARCHAR(32),
8.         deptno INT
9.         )
10.        
11. DESC my_tab01
12. SELECT * FROM my_tab01
14. -- 演示如何自我复制
15. -- 1.先把emp表的记录复制到my_tab01
16. INSERT INTO my_tab01
17.         (id,`name`,sal,job,deptno)
18.         SELECT empno,ename,sal,job,deptno FROM emp;
19.        
20. -- 2.自我复制
21. INSERT INTO my_tab01
22.         SELECT * FROM my_tab01;
24. SELECT COUNT(*) FROM my_tab01;

复制代码

.....

• 思考：如何删掉一张表的重复记录
  

1. -- 如何删掉一张表的重复记录
2. -- 1.先创建一张表 my_tab02
3. CREATE TABLE my_tab02 LIKE emp; -- 这个语句将 emp表的结构（列），复制到my_tab02
4. DESC my_tab02;
5. -- 2.让my_tab02 有重复的记录
6. INSERT INTO my_tab02
7.         SELECT * FROM emp;
8.        
9. SELECT * FROM my_tab02;
11. -- 3.考虑去重
12. /*
13.         思路：
14.         （1）先创建一张临时表 my_tmp,该表的结构和 my_tab02 一样
15.         （2）把 my_tab02 的记录通过 distinct 关键字处理后，把记录复制到 my_tmp
16.         （3）清除掉 my_tab02 的记录
17.         （4）把 my_tmp 的记录复制到 my_tab02 中
18.         （5）drop 掉 my_tmp表
19. */
20. -- 3.1 先创建一张临时表 my_tmp,该表的结构和 my_tab02 一样
21. CREATE TABLE my_tmp LIKE my_tab02;
22. -- 3.2 把 my_tab02 的记录通过 distinct 关键字处理后，把记录复制到 my_tmp
23. INSERT INTO my_tmp
24.         SELECT DISTINCT * FROM my_tab02;
25. -- 3.3 清除掉 my_tab02 的记录
26. DELETE FROM my_tab02;
27. -- 3.4 把 my_tmp 的记录复制到 my_tab02 中
28. INSERT INTO my_tab02
29.         SELECT * FROM my_tmp;
30. -- 3.5 drop 掉 my_tmp表
31. DROP TABLE my_tmp;
33. SELECT * FROM my_tab02;

复制代码

5.合并查询

• 介绍
  

有时候在实际应用中，为了合并多条select语句的结果，可以使用集合操作符号union、union all、
5.1union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
5.2union

该操作符与union all相似。但是会自动去掉结果集中的重复行
6.外连接

前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔集进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的就不显示
比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门

1. -- 外连接
2. -- 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
4. -- 使用我们学过的多表查询的SQL，看看效果如何？
5. SELECT dname,ename,job
6.         FROM emp,dept
7.         WHERE emp.deptno = dept.deptno
8.         ORDER BY dname
9. -- 原先的办法只能显示有员工的记录，如果有一个部门没有员工，就无法显示该部门

复制代码

如下：只能显示三个部门
这时候就需要用到外连接

• 外连接
  

• 左外连接（如果左侧的表完全显示，我们就说是左外链接）
  1. select ... from 表1 left join 表2 on 条件

  复制代码
  表1就是左表 ，表2就是右表
  左侧的表完全显示是指，即使左边的表跟右边的表没有匹配上，也会将左侧的表完全显示
  
• 右外连接（如果右侧的表完全显示，我们就说是右外链接）
  1. select ... from 表1 right join 表2 on 条件

  复制代码
  表1为左表 ，表2为右表
  右侧的表完全显示是指，即使右边的表跟左边的表没有匹配上，也会将右侧的表完全显示
  

例子

先创建两张表stu，exam

1. -- 创建 stu
2. CREATE TABLE stu(
3.         id INT,
4.         `name` VARCHAR(32)
5. );
6. INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
7. SELECT * FROM stu;
8. -- 创建 exam
9. CREATE TABLE exam(
10.         id INT,
11.         grade INT
12. );
13. INSERT INTO exam VALUES(1,56),(2,76),(11,8);
14. SELECT * FROM exam;

复制代码

要求1：使用左连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号  ）

1. -- 使用左连接
2. -- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号）
3. SELECT `name`,stu.id,grade
4.         FROM stu,exam
5.         WHERE stu.id = exam.id
7. -- 改成左外连接
8. SELECT `name`,stu.id,grade
9.         FROM stu LEFT JOIN exam
10.         ON stu.id = exam.id

复制代码

要求2：右连接（显示所有成绩，如果没有名字匹配就显示空）

1. -- 右外连接
2. SELECT `name`,stu.id,grade
3.         FROM stu RIGHT JOIN exam
4.         ON stu.id = exam.id

复制代码

例子2

列出部门名称和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门

• 使用左外连接实现
  
• 使用右外连接实现
  

1. -- 列出部门名称和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门
2. -- 1. 使用左外连接实现
3. SELECT dname,ename,job
4.         FROM dept LEFT JOIN emp
5.         ON dept.deptno = emp.deptno
7. -- 2. 使用右外连接实现
8. SELECT dname,ename,job
9.         FROM emp RIGHT JOIN dept
10.         ON dept.deptno = emp.deptno

复制代码

在实际的开发中绝大多数情况下使用的是前面学过的连接

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！