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;
EXPLAIN select * from dept;
EXPLAIN select DEPTNO from dept;
select * from emp;
EXPLAIN select * from emp;
EXPLAIN select EMPNO from emp;
select * from salgrade;
EXPLAIN select * from salgrade;
EXPLAIN select GRADE from salgrade;
select * from bonus;
EXPLAIN select * from bonus;
EXPLAIN select ENAME from bonus;-- 对emp表查询
select * from emp;
EXPLAIN select empno,ename,sal from emp;
EXPLAIN select * from emp where sal >2000;
select empno,ename,job,mgr from emp where sal >2000;-- 起别名：
select empno 员工编号,ename 姓名,sal 工资 from emp;
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
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按照降序排列
-- 查看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;-- 函数举例：
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):多条数据，最终展示一个结果 -- 多行函数
-- 单行函数包含：
--1.字符串函数
-- substring字符串截取，2:从字符下标为2开始，3：截取长度3 （下标从1开始）
select ename,length(ename),substring(ename,2,3) from emp;--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(999,'lili','SALASMAN',7698,now(),1000,null,30);--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 '岗位',
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;--6.其他函数
select database(),user(),version() from dual;-- 多行函数：
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;
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;-- 统计各个部门的平均工资 ,只显示平均工资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'
# 单表查询总结
# 【1】select语句总结
#selectcolumn,group_function(column)#fromtable
# [where condition]
# [group by group_by_expression]
# [having group_condition]
# [order by column];
# 注意：顺序固定，不可以改变顺序
# 【2】select语句的执行顺序
#from--where -- group by– select-having-order by-- 单表查询练习：
-- 列出工资最小值小于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;-- 查询员工的编号，姓名，部门编号：
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语法 ：筛选条件和连接条件是分开的
-- 外连接查询
-- 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*#fromemp e#fullouter join dept d#one.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可以省略不写
-- 三表连接查询
-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
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
-- 自连接查询
-- 查询员工的编号、姓名、上级编号,上级的姓名
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;--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
-- 引入子查询：
-- 查询所有比“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】不相关子查询分类：
# 根据子查询的结果行数，可以分为单行子查询和多行子查询。
-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资。
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')
