多表查詢02 4.表複製 自我複製數據（蠕蟲複製） 有時，為了對某個sql語句進行效率測試，我們需要海量數據時，可以用此法為表創建海量數據 -- 為了對某個sql語句進行效率測試，我們需要海量數據時，可以用此法為表創建海量數據 CREATE TABLE my_tab01( id INT , `nam ...
多表查詢02
4.表複製
- 自我複製數據（蠕蟲複製）
有時，為了對某個sql語句進行效率測試，我們需要海量數據時，可以用此法為表創建海量數據
-- 為了對某個sql語句進行效率測試，我們需要海量數據時，可以用此法為表創建海量數據
CREATE TABLE my_tab01(
id INT ,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT
)
DESC my_tab01
SELECT * FROM my_tab01
-- 演示如何自我複製
-- 1.先把emp表的記錄複製到my_tab01
INSERT INTO my_tab01
(id,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno FROM emp;
-- 2.自我複製
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;
.....
- 思考：如何刪掉一張表的重覆記錄
-- 如何刪掉一張表的重覆記錄
-- 1.先創建一張表 my_tab02
CREATE TABLE my_tab02 LIKE emp; -- 這個語句將 emp表的結構（列），複製到my_tab02
DESC my_tab02;
-- 2.讓my_tab02 有重覆的記錄
INSERT INTO my_tab02
SELECT * FROM emp;
SELECT * FROM my_tab02;
-- 3.考慮去重
/*
思路：
（1）先創建一張臨時表 my_tmp,該表的結構和 my_tab02 一樣
（2）把 my_tab02 的記錄通過 distinct 關鍵字處理後，把記錄複製到 my_tmp
（3）清除掉 my_tab02 的記錄
（4）把 my_tmp 的記錄複製到 my_tab02 中
（5）drop 掉 my_tmp表
*/
-- 3.1 先創建一張臨時表 my_tmp,該表的結構和 my_tab02 一樣
CREATE TABLE my_tmp LIKE my_tab02;
-- 3.2 把 my_tab02 的記錄通過 distinct 關鍵字處理後，把記錄複製到 my_tmp
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
-- 3.3 清除掉 my_tab02 的記錄
DELETE FROM my_tab02;
-- 3.4 把 my_tmp 的記錄複製到 my_tab02 中
INSERT INTO my_tab02
SELECT * FROM my_tmp;
-- 3.5 drop 掉 my_tmp表
DROP TABLE my_tmp;
SELECT * FROM my_tab02;
5.合併查詢
- 介紹
有時候在實際應用中，為了合併多條select語句的結果，可以使用集合操作符號union、union all、
5.1union all
該操作符用於取得兩個結果集的並集。當使用該操作符時，不會取消重覆行
5.2union
該操作符與union all相似。但是會自動去掉結果集中的重覆行
6.外連接
前面我們學習的查詢，是利用where子句對兩張表或者多張表，形成的笛卡爾集進行篩選，根據關聯條件，顯示所有匹配的記錄，匹配不上的就不顯示
比如：列出部門名稱和這些部門的員工名稱和工作，同時要求顯示出那些沒有員工的部門
-- 外連接
-- 比如：列出部門名稱和這些部門的員工名稱和工作，同時要求顯示出那些沒有員工的部門
-- 使用我們學過的多表查詢的SQL，看看效果如何？
SELECT dname,ename,job
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
-- 原先的辦法只能顯示有員工的記錄，如果有一個部門沒有員工，就無法顯示該部門
如下：只能顯示三個部門
這時候就需要用到外連接
- 外連接
-
左外連接（如果左側的表完全顯示，我們就說是左外鏈接）
select ... from 表1 left join 表2 on 條件表1就是左表 ，表2就是右表
左側的表完全顯示是指，即使左邊的表跟右邊的表沒有匹配上，也會將左側的表完全顯示
-
右外連接（如果右側的表完全顯示，我們就說是右外鏈接）
select ... from 表1 right join 表2 on 條件表1為左表 ，表2為右表
右側的表完全顯示是指，即使右邊的表跟左邊的表沒有匹配上，也會將右側的表完全顯示
例子
先創建兩張表stu，exam
-- 創建 stu
CREATE TABLE stu(
id INT,
`name` VARCHAR(32)
);
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
-- 創建 exam
CREATE TABLE exam(
id INT,
grade INT
);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;
要求1：使用左連接（顯示所有人的成績，如果沒有成績，也要顯示該人的姓名和id號 ）
-- 使用左連接
-- （顯示所有人的成績，如果沒有成績，也要顯示該人的姓名和id號）
SELECT `name`,stu.id,grade
FROM stu,exam
WHERE stu.id = exam.id
-- 改成左外連接
SELECT `name`,stu.id,grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id
要求2：右連接（顯示所有成績，如果沒有名字匹配就顯示空）
-- 右外連接
SELECT `name`,stu.id,grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id
例子2
列出部門名稱和這些部門的員工信息（名字和工作），同時列出那些沒有員工的部門
- 使用左外連接實現
- 使用右外連接實現
-- 列出部門名稱和這些部門的員工信息（名字和工作），同時列出那些沒有員工的部門
-- 1. 使用左外連接實現
SELECT dname,ename,job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 2. 使用右外連接實現
SELECT dname,ename,job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
在實際的開發中絕大多數情況下使用的是前面學過的連接
