MySQL read 查询语句7 复制 去重 外连接

-- 子查询练习
-- 每个部门工资高于本部门平均工资的人资料
SELECT ename,sal,temp.avg_sal,emp.deptno FROM emp,(
        SELECT deptno ,AVG(sal) AS avg_sal
        FROM emp
        GROUP BY deptno
        ) temp
    WHERE emp.deptno = temp.deptno
    AND emp.sal > temp.avg_sal

-- 每个部门工资最高的人详细资料
SELECT ename,sal,emp.deptno
        FROM emp,(
        SELECT deptno ,MAX(sal) AS max_sal
        FROM emp
        GROUP BY deptno
        ) temp
    WHERE emp.deptno = temp.deptno
    AND emp.sal = temp.max_sal

-- 查询每个部门的信息（包括：部门名，编号，地址）和人员数量
-- 构建临时表显示部门人数
SELECT deptno,COUNT(*) FROM emp
    GROUP BY deptno
    
SELECT dname,loc,temp.* #表名.* 表示将该表所有列显示
        FROM dept, (SELECT deptno,COUNT(*) AS '人数'
            FROM emp
            GROUP BY deptno) temp
        WHERE temp.deptno = dept.deptno

-- 表复制（蠕虫复制）
-- 对某个sql语句进行效率测试，需要海量数据，可以使用复制创建海量数据
CREATE TABLE my_tab01
    (id INT ,
     `name` VARCHAR(32),
     sal DOUBLE,
     job VARCHAR(32),
     deptno INT );
DESC my_tab01;

-- 如何自我复制
-- 1.先把emp 复制到 my_tab01
INSERT INTO my_tab01
    (id,`name`,sal,job,deptno)
    SELECT empno, ename,sal,job,deptno FROM emp;
-- 自我复制
INSERT INTO my_tab01
    SELECT * FROM my_tab01
 
-- 去重
-- 1.先创建一张表 my_tab02
-- 2.让my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp; # 表结构（列）复制过来

INSERT INTO my_tab02
    SELECT * FROM emp;
SELECT * FROM my_tab02
-- 3.去重
/*
   1.先创建一张临时表my_temp, 该表的结构和my_table02一样
   2.把my_temp 的记录 通过distinct 关键字 处理后 把记录复制到my_temp
   3.清除my_tab02 表的记录
   4.把my_temp表记录复制到my_tab02
   5.删除my_temp
*/
CREATE TABLE my_temp LIKE my_tab02;
INSERT INTO my_temp
        SELECT DISTINCT * FROM my_tab02;

DELETE FROM my_tab02
INSERT INTO my_tab02
        SELECT * FROM my_temp
DROP TABLE my_temp
SELECT * FROM my_tab02

-- 合并查询 UNION(去重) 与 UNION ALL(不去重)
SELECT ename,sal,job FROM emp WHERE sal > 2500
UNION
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'

-- 外连接
-- 列出部门名称和这些部门的员工名称和工作，同时显示没有员工的部门

-- 多表查询显示不完全 如下  
   SELECT dname,ename,job
    FROM emp,dept
    WHERE emp.deptno = dept.deptno
    ORDER BY dname

-- 创建 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

-- 使用左外连接，显示所有人成绩，如果没有该人成绩也要显示姓名和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;

-- 右外连接 显示所有成绩，没有名字匹配显示空
-- 右表完全显示，左表不匹配的显示null
SELECT `name`,stu.id,grade
    FROM stu RIGHT JOIN exam
    ON stu.id = exam.id;

-- 练习题 列出部门名称和这些部门员工名字和工作
-- 同时列出那些没有员工的部门名
SELECT dname,ename,job
    FROM dept LEFT JOIN emp #左外连接
    ON dept.deptno = emp.deptno;
    
SELECT dname,ename,job
    FROM emp RIGHT JOIN dept #右外连接
    ON dept.deptno = emp.deptno;
-- 小结：实际使用最多的是内连接多表查询