mysql 伪表_MySql系列05：MySql中DQL查询操作

1、表的准备

准备四张表：

dept(部门表)、emp(员工表)、salgrade(薪资等级表)、bonus(奖金表)

create table DEPT(

DEPTNO int(2) not null,

DNAME VARCHAR(14),

LOC VARCHAR(13)

);

# 表级约束，添加主键

alter table DEPT

add constraint PK_DEPT primary key (DEPTNO);

create table EMP

(

EMPNO int(4) primary key,

ENAME VARCHAR(10),

JOB VARCHAR(9),

MGR int(4),

HIREDATE DATE,

SAL double(7,2),

COMM double(7,2),

DEPTNO int(2)

);

# 表级约束，添加主键

alter table EMP

add constraint FK_DEPTNO foreign key (DEPTNO)

references DEPT (DEPTNO);

create table SALGRADE

(

GRADE int primary key,

LOSAL double(7,2),

HISAL double(7,2)

);

create table BONUS

(

ENAME VARCHAR(10),

JOB VARCHAR(9),

SAL double(7,2),

COMM double(7,2)

);

insert into DEPT (DEPTNO, DNAME, LOC)

values (10, 'ACCOUNTING', 'NEW YORK');

insert into DEPT (DEPTNO, DNAME, LOC)

values (20, 'RESEARCH', 'DALLAS');

insert into DEPT (DEPTNO, DNAME, LOC)

values (30, 'SALES', 'CHICAGO');

insert into DEPT (DEPTNO, DNAME, LOC)

values (40, 'OPERATIONS', 'BOSTON');

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into SALGRADE (GRADE, LOSAL, HISAL)

values (1, 700, 1200);

insert into SALGRADE (GRADE, LOSAL, HISAL)

values (2, 1201, 1400);

insert into SALGRADE (GRADE, LOSAL, HISAL)

values (3, 1401, 2000);

insert into SALGRADE (GRADE, LOSAL, HISAL)

values (4, 2001, 3000);

insert into SALGRADE (GRADE, LOSAL, HISAL)

values (5, 3001, 9999);

-- 查看表：

select * from dept;

-- 部门表：dept:department 部分 ，loc - location 位置

select * from emp;

-- 员工表：emp:employee 员工 ,mgr :manager上级领导编号，hiredate 入职日期 firedate 解雇日期 ，common：补助

-- deptno 外键 参考 dept - deptno字段

-- mgr 外键 参考 自身表emp - empno 产生了自关联

select * from salgrade;

-- losal - lowsal

-- hisal - highsal

select * from bonus;

2、单表查询

2.1、简单的SQL查询

-- 对emp表查询：

select * from emp; -- *代表所有数据

-- 显示部分列：

select empno,ename,sal from emp;

-- 显示部分行：where子句

select * from emp where sal > 2000;

-- 显示部分列，部分行：

select empno,ename,job,mgr from emp where sal > 2000;

-- 起别名：

select empno 员工编号,ename 姓名,sal 工资 from emp; -- as 省略，''或者""省略了

-- as alias 别名

select empno as 员工编号,ename as 姓名,sal as 工资 from emp;

select empno as '员工编号',ename as "姓名",sal as 工资 from emp;

-- > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '编号,ename as "姓 名",sal as 工资 from emp' at line 1

-- 错误原因：在别名中有特殊符号的时候，''或者""不可以省略不写

select empno as 员工 编号,ename as "姓 名",sal as 工资 from emp;

-- 算术运算符：

select empno,ename,sal,sal+1000 as '涨薪后',deptno from emp where sal < 2500;

select empno,ename,sal,comm,sal+comm from emp; -- ？？？后面再说

-- 去重操作：

select job from emp;

select distinct job from emp;

select job,deptno from emp;

select distinct job,deptno from emp; -- 对后面的所有列组合 去重 ，而不是单独的某一列去重

-- 排序：

select * from emp order by sal; -- 默认情况下是按照升序排列的

select * from emp order by sal asc; -- asc 升序，可以默认不写

select * from emp order by sal desc; -- desc 降序

select * from emp order by sal asc ,deptno desc; -- 在工资升序的情况下，deptno按照降序排列

2.2、where子句

指定查询条件使用where子句，可以查询符合条件的部分记录

-- 查看emp表：

select * from emp;

-- where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据：

-- where 子句 + 关系运算符

select * from emp where deptno = 10;

select * from emp where deptno > 10;

select * from emp where deptno >= 10;

select * from emp where deptno < 10;

select * from emp where deptno <= 10;

select * from emp where deptno <> 10;

select * from emp where deptno != 10;

select * from emp where job = 'CLERK';

select * from emp where job = 'clerk'; -- 默认情况下不区分大小写

select * from emp where binary job = 'clerk'; -- binary区分大小写

select * from emp where hiredate < '1981-12-25';

-- where 子句 + 逻辑运算符：and

select * from emp where sal > 1500 and sal < 3000; -- (1500,3000)

select * from emp where sal > 1500 && sal < 3000;

select * from emp where sal > 1500 and sal < 3000 order by sal;

select * from emp where sal between 1500 and 3000; -- [1500,3000]

-- where 子句 + 逻辑运算符：or

select * from emp where deptno = 10 or deptno = 20;

select * from emp where deptno = 10 || deptno = 20;

select * from emp where deptno in (10,20);

select * from emp where job in ('MANAGER','CLERK','ANALYST');

-- where子句 + 模糊查询：

-- 查询名字中带A的员工 -- %代表任意多个字符 0,1,2，.....

select * from emp where ename like '%A%' ;

-- -任意一个字符

select * from emp where ename like '__A%' ;

-- 关于null的判断：

select * from emp where comm is null;

select * from emp where comm is not null;

-- 小括号的使用 ：因为不同的运算符的优先级别不同，加括号为了可读性

select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or and > or

select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500);

select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

2.3、使用函数

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。 (在sql中使用函数)

函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。MySQL中的函数主要分为单行函数和多行函数两大类，下面我们将详细讲解这两大类函数。

单行函数

单行函数是指对每一条记录输入值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

多行函数

多行函数是指对一组数据进行运算，针对这一组数据(多行记录)只返回一个结果，也称为分组函数。

-- 函数举例：

select empno,ename,lower(ename),upper(ename),sal from emp;

-- 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能

-- 函数作用：为了提高select的能力

-- 注意：函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已。

select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;

-- 函数的分类：

-- lower(ename),upper(ename) ：改变每一条结果，每一条数据对应一条结果 -- 单行函数

-- max(sal),min(sal),count(sal),sum(sal),avg(sal):多条数据，最终展示一个结果 -- 多行函数

PS：除了多行函数(max,min,count,sum,avg)，都是单行函数

2.3.1、单行函数

1.字符串函数 (String StringBuilder)

函数描述CONCAT(str1, str2, ···, strn)将str1、str2···strn拼接成一个新的字符串

INSERT(str, index, n, newstr)将字符串str从第index位置开始的n个字符替换成字符串newstr

LENGTH(str)获取字符串str的长度

LOWER(str)将字符串str中的每个字符转换为小写

UPPER(str)将字符串str中的每个字符转换为大写

LEFT(str, n)获取字符串str最左边的n个字符

RIGHT(str, n)获取字符串str最右边的n个字符

LPAD(str, n, pad)使用字符串pad在str的最左边进行填充，直到长度为n个字符为止

RPAD(str, n, pad)使用字符串pad在str的最右边进行填充，直到长度为n个字符为止

LTRIM(str)去除字符串str左侧的空格

RTRIM(str)去除字符串str右侧的空格

TRIM(str)去除字符串str左右两侧的空格

REPLACE(str,oldstr,newstr)用字符串newstr替换字符串str中所有的子字符串oldstr

REVERSE(str)将字符串str中的字符逆序

STRCMP(str1, str2)比较字符串str1和str2的大小

SUBSTRING(str,index,n)获取从字符串str的index位置开始的n个字符

2. 数值函数 (Math)

函数描述ABS(num)返回num的绝对值

CEIL(num)返回大于num的最小整数(向上取整)

FLOOR(num)返回小于num的最大整数(向下取整)

MOD(num1, num2)返回num1/num2的余数(取模)

PI()返回圆周率的值

POW(num,n)/POWER(num, n)返回num的n次方

RAND(num)返回0~1之间的随机数

ROUND(num, n)返回x四舍五入后的值，该值保留到小数点后n位

TRUNCATE(num, n)返回num被舍去至小数点后n位的值

3. 日期与时间函数

函数描述CURDATE()返回当前日期

CURTIME()返回当前时间

NOW()返回当前日期和时间

SYSDATE()返回该函数执行时的日期和时间

DAYOFYEAR(date)返回日期date为一年中的第几天

WEEK(date)/WEEKOFYEAR(date)返回日期date为一年中的第几周

DATE_FORMAT(date, format)返回按字符串format格式化后的日期date

DATE_ADD(date, INTERVAL expr unit)

/ADDDATE(date, INTERVAL expr unit)返回date加上一个时间间隔后的新时间值

DATE_SUB(date, INTERVAL expr unit)

/SUBDATE(date, INTERVAL expr unit)返回date减去一个时间间隔后的新时间值

DATEDIFF(date1, date2)返回起始日期date1与结束日期date2之间的间隔天数

-- 单行函数包含：

-- 1.字符串函数

select ename,length(ename),substring(ename,2,3) from emp;

-- substring字符串截取，2:从字符下标为2开始，3：截取长度3 (下标从1开始)

-- 2.数值函数

select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表

select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入; -- 如果没有where条件的话，from dual可以省略不写

select ceil(sal) from emp;

select 10/3,10%3,mod(10,3) ;

-- 3.日期与时间函数

select * from emp;

select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒

select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒

insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);

-- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构的

desc emp;

4. 流程函数( IF SWITCH)

间隔类型描述IF(condition, t, f)如果条件condition为真，则返回t，否则返回f

IFNULL(value1, value2)如果value1不为null，则返回value1，否则返回value2

NULLIF(value1, value2)如果value1等于value2，则返回null，否则返回value1

CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END如果value等于value1，则返回result1，···，否则返回result

CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END如果条件condition1为真，则返回result1，···，否则返回result

5.JSON函数

函数描述JSON_APPEND()在JSON文档中追加数据

JSON_INSERT ()在JSON文档中插入数据

JSON_REPLACE ()替换JSON文档中的数据

JSON_REMOVE ()从JSON文档的指定位置移除数据

JSON_CONTAINS()判断JSON文档中是否包含某个数据

JSON_SEARCH()查找JSON文档中给定字符串的路径

6.其他函数

函数描述DATABASE()返回当前数据库名

VERSION()返回当前MySQL的版本号

USER()返回当前登录的用户名

INET_ATON(IP)返回IP地址的数字表示

INET_NTOA返回数字代表的IP地址

PASSWORD(str)实现对字符串str的加密操作

FORMAT(num, n)实现对数字num的格式化操作，保留n位小数

CONVERT(data, type)实现将数据data转换成type类型的操作

-- 4.流程函数

-- if相关

select empno,ename,sal,if(sal>=2500,'高薪','底薪') as '薪资等级' from emp; -- if-else 双分支结构

select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null，那么取值为0 -- 单分支

select nullif(1,1),nullif(1,2) from dual; -- 如果value1等于value2，则返回null，否则返回value1

-- case相关：

-- case等值判断

select empno,ename,job,

case job

when 'CLERK' then '店员'

when 'SALESMAN' then '销售'

when 'MANAGER' then '经理'

else '其他'

end as '岗位', #以end结束，case..end是一个字段

sal from emp;

-- case区间判断:

select empno,ename,sal,

case

when sal<=1000 then 'A'

when sal<=2000 then 'B'

when sal<=3000 then 'C'

else 'D'

end '工资等级',

deptno from emp;

from emp;

-- 5.JSON函数

-- 6.其他函数

select database(),user(),version() from dual;

2.3.2、多行函数

对一组数据进行运算，针对一组数据(多行记录)只返回一个结果，也称分组函数

多行函数包含

函数描述COUNT()统计表中记录的数目

SUM()计算指定字段值的总和

AVG()计算指定字段值的平均值

MAX()统计指定字段值的最大值

MIN()统计指定字段值的最小值

-- 多行函数：

select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;

select * from emp;

-- 多行函数自动忽略null值

select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;

-- max(),min(),count()针对所有类型 sum(),avg() 只针对数值型类型有效

select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;

-- count --计数

-- 统计表的记录数：方式1：

select * from emp;

select count(ename) from emp;

select count(*) from emp;

-- 统计表的记录数：方式2

select 1 from dual;

select 1 from emp;

select count(1) from emp;

2.4、group_by分组

【1】group by : 用来进行分组

【2】sql展示

select * from emp;

-- 统计各个部门的平均工资

select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用

select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组

select deptno,avg(sal) from emp group by deptno order by deptno desc;

-- 统计各个岗位的平均工资

select job,avg(sal) from emp group by job;

select job,lower(job),avg(sal) from emp group by job;

2.5、having分组后筛选

-- 统计各个部门的平均工资 ,只显示平均工资2000以上的 - 分组以后进行二次筛选 having

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;

select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;

-- 统计各个岗位的平均工资,除了MANAGER

-- 方法1：

select job,avg(sal) from emp where job != 'MANAGER' group by job;

-- 方法2：

select job,avg(sal) from emp group by job having job != 'MANAGER' ;

-- where在分组前进行过滤的，having在分组后进行后滤。

2.6、单表查询总结

【1】select语句总结

select column, group_function(column)

from table

[where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

注意：顺序固定，不可以改变顺序

【2】select语句的执行顺序

from–where – group by– select - having- order by

【3】单表查询练习：

-- 单表查询练习：

-- 列出工资最小值小于2000的职位

select job,min(sal)

from emp

group by job

having min(sal) < 2000 ;

-- 列出平均工资大于1200元的部门和工作搭配组合

select deptno,job,avg(sal)

from emp

group by deptno,job

having avg(sal) > 1200

order by deptno;

-- 统计[人数小于4的]部门的平均工资。

select deptno,count(1),avg(sal)

from emp

group by deptno

having count(1) < 4

-- 统计各部门的最高工资，排除最高工资小于3000的部门。

select deptno,max(sal)

from emp

group by deptno

having max(sal) < 3000;

3、多表查询

3.1、99语法：交叉连接，自然连接，内连接查询

3.1.1【1】多表查询引入：

实际开发中往往需要针对两张甚至更多张数据表进行操作，而这多张表之间需要使用主键和外键关联在一起，然后使用连接查询来查询多张表中满足要求的数据记录。

一条SQL语句查询多个表，得到一个结果，包含多个表的数据。效率高。在SQL99中，连接查询需要使用join关键字实现。

提供了多种连接查询的类型： cross natural using on

交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。比如：有两个表，左表有m条数据记录，x个字段，右表有n条数据记录，y个字段，则执行交叉连接后将返回m*n条数据记录，x+y个字段。笛卡儿积示意图如图所示。

3.1.2【2】sql展示：

-- 查询员工的编号，姓名，部门编号：

select * from emp;

select empno,ename,deptno from emp;

-- 查询员工的编号，姓名，部门编号,部门名称：

select * from emp; -- 14条记录

select * from dept; -- 4条记录

-- 多表查询 ：

-- 交叉连接：cross join

select *

from emp

cross join dept; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义

select *

from emp

join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以

-- 自然连接：natural join

-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单

select *

from emp

natural join dept;

select empno,ename,sal,dname,loc

from emp

natural join dept;

-- 缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低

-- 解决： 指定表名：

select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno

from emp

natural join dept;

-- 缺点：表名太长

-- 解决：表起别名

select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno

from emp e

natural join dept d;

-- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：

-- 解决： 内连接 - using子句：

select *

from emp e

inner join dept d -- inner可以不写

using(deptno) -- 这里不能写natural join了 ,这里是内连接

-- using缺点：关联的字段，必须是同名的

-- 解决： 内连接 - on子句：

select *

from emp e

inner join dept d

on (e.deptno = d.deptno);

-- 多表连接查询的类型： 1.交叉连接 cross join 2. 自然连接 natural join

-- 3. 内连接 - using子句 4.内连接 - on子句

-- 综合看：内连接 - on子句

select *

from emp e

inner join dept d

on (e.deptno = d.deptno)

where sal > 3500;

-- 条件：

-- 1.筛选条件 where having

-- 2.连接条件 on,using,natural

-- SQL99语法 ：筛选条件和连接条件是分开的

3.2、99语法：外连接查询

-- inner join - on子句： 显示的是所有匹配的信息

select *

from emp e

inner join dept d

on e.deptno = d.deptno;

select * from emp;

select * from dept;

-- 问题：

-- 1.40号部门没有员工，没有显示在查询结果中

-- 2.员工scott没有部门，没有显示在查询结果中

-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据

-- 左外连接： left outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果

select *

from emp e

left outer join dept d

on e.deptno = d.deptno;

-- 右外连接： right outer join -- 右面的那个表的信息，即使不匹配也可以查看出效果

select *

from emp e

right outer join dept d

on e.deptno = d.deptno;

-- 全外连接 full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据

-- scott ，40号部门都可以看到

select *

from emp e

full outer join dept d

on e.deptno = d.deptno;

-- 解决mysql中不支持全外连接的问题：

select *

from emp e

left outer join dept d

on e.deptno = d.deptno

union -- 并集 去重 效率低

select *

from emp e

right outer join dept d

on e.deptno = d.deptno;

select *

from emp e

left outer join dept d

on e.deptno = d.deptno

union all-- 并集 不去重 效率高

select *

from emp e

right outer join dept d

on e.deptno = d.deptno;

-- mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持(oracle中支持)

-- outer可以省略不写

3.3、99语法：三表连接查询

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级

select * from emp;

select * from dept;

select * from salgrade;

select e.ename,e.sal,e.empno,e.deptno,d.dname,s.*

from emp e

right outer join dept d

on e.deptno = d.deptno

inner join salgrade s

on e.sal between s.losal and s.hisal

3.4、99语法：自连接查询

-- 查询员工的编号、姓名、上级编号,上级的姓名

select * from emp;

select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名

from emp e1

inner join emp e2

on e1.mgr = e2.empno;

-- 左外连接：

select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名

from emp e1

left outer join emp e2

on e1.mgr = e2.empno;

3.5、92语法：多表查询

-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称：

select e.empno,e.ename,e.sal,e.deptno,d.dname

from emp e,dept d

-- 相当于99语法中的cross join ,出现笛卡尔积，没有意义

select e.empno,e.ename,e.sal,e.deptno,d.dname

from emp e,dept d

where e.deptno = d.deptno;

-- 相当于99语法中的natural join

-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称，查询出工资大于2000的员工

select e.empno,e.ename,e.sal,e.deptno,d.dname

from emp e,dept d

where e.deptno = d.deptno and e.sal > 2000;

-- 查询员工的名字，岗位，上级编号，上级名称(自连接)：

select e1.ename,e1.job,e1.mgr ,e2.ename

from emp e1,emp e2

where e1.mgr = e2.empno;

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级

select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade

from emp e,dept d,salgrade s

where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal;

-- 总结：

-- 1.92语法麻烦

-- 2.92语法中 表的连接条件 和 筛选条件 是放在一起的没有分开

-- 3.99语法中提供了更多的查询连接类型：cross,natural,inner,outer

4、子查询

4.1、不相关子查询

【1】什么是子查询？

一条SQL语句含有多个select

-- 引入子查询：

-- 查询所有比“CLARK”工资高的员工的信息

-- 步骤1：“CLARK”工资

select sal from emp where ename = 'CLARK' -- 2450

-- 步骤2：查询所有工资比2450高的员工的信息

select * from emp where sal > 2450;

-- 两次命令解决问题 --》效率低 ，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是

-- 因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改

-- 将步骤1和步骤2合并 --》子查询：

select * from emp where sal > (select sal from emp where ename = 'CLARK');

-- 一个命令解决问题 --》效率高

【2】执行顺序：

先执行子查询，再执行外查询；

【3】不相关子查询：

子查询可以独立运行，称为不相关子查询。

【4】不相关子查询分类：

根据子查询的结果行数，可以分为单行子查询和多行子查询。

单行子查询：子查询返回结果为一行

多行子查询：子查询返回结果为多行

4.1.1、单行子查询

-- 单行子查询：

-- 查询工资高于平均工资的雇员名字和工资。

select ename,sal

from emp

where sal > (select avg(sal) from emp);

-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。

select ename,sal

from emp

where deptno = (select deptno from emp where ename = 'CLARK')

and

sal < (select sal from emp where ename = 'CLARK')

-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息

select *

from emp

where job = (select job from emp where ename = 'SCOTT')

and

hiredate < (select hiredate from emp where ename = 'SCOTT')

4.1.2、多行子查询

-- 多行子查询：

-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。

-- 查询雇员信息

select * from emp;

-- 查询部门20中的雇员信息

select * from emp where deptno = 20;-- CLERK,MANAGER,ANALYST

-- 部门10的雇员的职务：

select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK

-- 查询部门20中职务同部门10的雇员一样的雇员信息。

select * from emp

where deptno = 20

and job in (select job from emp where deptno = 10)

-- > Subquery returns more than 1 row

select * from emp

where deptno = 20

and job = any(select job from emp where deptno = 10)

-- 【2】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。

-- 查询雇员的编号、名字和工资

select empno,ename,sal from emp

-- “SALESMAN”的工资：

select sal from emp where job = 'SALESMAN'

-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。

-- 多行子查询：

select empno,ename,sal

from emp

where sal > all(select sal from emp where job = 'SALESMAN');

-- 单行子查询：

select empno,ename,sal

from emp

where sal > (select max(sal) from emp where job = 'SALESMAN');

-- 【3】查询工资低于任意一个“CLERK”的工资的雇员信息。

-- 查询雇员信息

select * from emp;

-- 查询工资低于任意一个“CLERK”的工资的雇员信息

select *

from emp

where sal < any(select sal from emp where job = 'CLERK')

and job != 'CLERK'

-- 单行子查询：

select *

from emp

where sal < (select max(sal) from emp where job = 'CLERK')

and job != 'CLERK'

4.2、相关子查询

【1】不相关的子查询引入：

不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。

相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询

【2】不相关的子查询优缺点：

好处：简单 功能强大(一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现)

缺点：稍难理解

【3】sql展示：

-- 【1】查询最高工资的员工 (不相关子查询)

select * from emp where sal = (select max(sal) from emp)

-- 【2】查询本部门最高工资的员工

-- 方法1：通过不相关子查询实现：

select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)

union

select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)

union

select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)

-- 缺点：语句比较多，具体到底有多少个部分未知

-- 方法2： 相关子查询

select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno

-- 【3】查询工资高于其所在岗位的平均工资的那些员工 (相关子查询)

-- 方法1：不相关子查询：

select * from emp where job = 'CLERK' and sal >= (select avg(sal) from emp where job = 'CLERK')

union ......

-- 方法2：相关子查询：

select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job)