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)