mysql学习笔记-子查询

-- 1.创建表
    CREATE TABLE t_depttest (
        DEPTNO INT NOT NULL,
        Dname VARCHAR (20) NOT NULL
    );

-- 2.插入数据四条
INSERT INTO t_depttest
VALUES
    (10, 'ACCOUTING'),
    (20, 'research'),
    (10, 'SALES'),
    (10, 'OPERATIONS');

-- 3.查询表
SELECT DEPTNO,Dname FROM t_depttest;
SELECT * FROM t_emptest;


-- 4.笛卡尔积
select ename,job,sal,Dname FROM t_emptest,t_depttest ;

-- 5.相等连接
select ename, job,sal,Dname FROM t_emptest,t_depttest
    -- 指定连接条件
        WHERE t_emptest.DEPTNO = t_depttest.DEPTNO;

-- 6.不等连接
select ename, job,sal,Dname FROM t_emptest,t_depttest
    WHERE t_emptest.DEPTNO <> t_depttest.DEPTNO;

-- 7.自我连接
-- 查询雇员对应的经理的名字
SELECT t.ename, t.job,t.sal,mgr.ename FROM t_emptest t,t_emptest mgr WHERE mgr.EMPNO=t.MGR;

-- 8.多条件查询
-- 找到部门编号为20的数据，找到职务为manager的数据
SELECT * FROM t_emptest WHERE DEPTNO = 20;
SELECT * FROM t_emptest WHERE JOB = 'MANAGER';

SELECT * FROM t_emptest WHERE DEPTNO = 20 OR JOB = 'MANAGER';

-- 9.UNION 返回的列名相同 不排序
-- UNION 去除了 重复的数据，union all 不去除重复；
-- 求合集
SELECT * FROM t_emptest WHERE DEPTNO = 20
UNION ALL
SELECT * FROM t_emptest WHERE JOB = 'MANAGER';

-- 10.子查询
-- 10.1单行子查询
-- 显示工资最高的雇员信息
-- 可以使用运算符号(=,>,<,>=,<=,<>)，使用在where条件中
 SELECT * FROM t_emptest WHERE sal = (SELECT MAX(sal) FROM t_emptest); -- 单行单列

-- 10.2多行子查询
-- 显示与  部门编号为20的岗位  相同的雇员信息
-- not in -in
-- distinct 去重复，放在查询列表的最前面
select  DISTINCT job from t_emptest WHERE DEPTNO = 20;

SELECT * FROM t_emptest WHERE JOB in (select  DISTINCT job from t_emptest WHERE DEPTNO = 20); -- 多行单列

-- 显示雇员信息薪水高于    部门编号为20的所有雇员的工资
SELECT MAX(SAL) FROM t_emptest WHERE DEPTNO = 20;

SELECT * FROM t_emptest WHERE SAL > (SELECT MAX(SAL) FROM t_emptest WHERE DEPTNO = 20);

SELECT * FROM t_emptest WHERE SAL > ALL (SELECT SAL FROM t_emptest WHERE DEPTNO = 20);

-- 显示高于部门编号为20的任何雇员的工资的雇员信息
SELECT * FROM t_emptest WHERE SAL > ANY (SELECT SAL FROM t_emptest WHERE DEPTNO = 20);
SELECT * FROM t_emptest WHERE SAL > (SELECT min(SAL) FROM t_emptest WHERE DEPTNO = 20);

-- 11.给虚表加别名
select job,MAX(emp_tmp.avg_sal) FROM
(SELECT job,avg(sal) avg_sal FROM t_emptest GROUP BY JOB ) emp_tmp ;

-- 12. 标量子查询
select (SELECT MAX(sal) FROM t_emptest) highest_salary -- ,(SELECT avg(nvl2(comm,comm,0)) FROM t_emptest) avg_comm
,deptno,empno,ename FROM t_emptest;


-- 13.多列查询
-- 显示与SOCTT 部门、岗位 完全相同的所有雇员信息


-- SOCTT 部门、岗位
SELECT DEPTNO,JOB FROM t_emptest WHERE ENAME = 'SOCTT';  -- 单行多列

-- 单行子查询实现
select * from t_emptest WHERE DEPTNO = (SELECT DEPTNO FROM t_emptest WHERE ENAME = 'SOCTT') AND JOB = (SELECT JOB FROM t_emptest WHERE ENAME = 'SOCTT');

-- 多列的实现方式--多列加了个括号
-- 实现比较多列 （字段1，字段2）= （子查询）
select * from t_emptest WHERE (DEPTNO,JOB)=(SELECT DEPTNO,JOB FROM t_emptest WHERE ENAME = 'SOCTT');

-- 14.子查询返回多行多列时  用 in
-- 显示岗位或上级匹配于部门编号为20的所有雇员信息  
-- 非成对比较

-- select * from t_emptest WHERE (DEPTNO,JOB) in (SELECT MGR,JOB FROM t_emptest WHERE DEPTNO = 20 ) ORDER BY EMPNO;

select * from t_emptest WHERE JOB in (SELECT JOB FROM t_emptest WHERE DEPTNO = 20 ) and MGR in (SELECT MGR FROM t_emptest WHERE DEPTNO = 20 ) ORDER BY EMPNO;

-- 15.相关子查询
-- 属于标量查询，在查询列表中有多个列
-- 在 主查询中，没查询一条记录，需要重新做一次子查询，称为相关子查询
-- 显示每个部门的最高工资的信息
-- 多次重复执行子查询
select DEPTNO,max(SAL) from t_emptest  GROUP BY DEPTNO;

select DISTINCT e.DEPTNO, (SELECT max(SAL) FROM t_emptest e1 WHERE e1.DEPTNO=e.DEPTNO ) max_sal FROM t_emptest e;

-- 15.非相关子查询
-- 在主查询中，子查询只需要执行一次，子查询结果不会再变化，非相关子查询
-- 一般情况下内联子查询（from后），where 条件中出现的子查询
SELECT * FROM t_emptest WHERE sal = (SELECT MAX(sal) FROM t_emptest); -- 单行单列

-- 16. EXISTS
-- 显示工作在 NEW YORK的雇员信息