数据准备：
create database test2;
use test2;
-- 创建部门表
create table dept(
deptno int primary key, -- 部门编号
dname varchar(20), -- 部门名称
loc varchar(20) -- 部门地址
);
-- 给部门表添加数据
insert into dept values(10,'accounting','new york'),(20,'research','dallas'),(30,'sales','chigago'),(40,'operations','boston');
-- 创建员工表
create table emp(
empno int primary key, -- 员工编号
ename varchar(20), -- 员工姓名
job varchar(9), -- 员工工作
mgr int, -- 员工直属领导编号
hiredate date, -- 入职时间
sal double, -- 员工工资
comm double, -- 员工奖金
deptno int -- 对应dept表的外键
);
-- 添加 部门 和 员工 之间的主外键关系
alter table emp add CONSTRAINT foreign key(deptno) REFERENCES dept(deptno);
-- 给员工表添加数据
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman', 7698, '1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566, 'jones','manager' , 7839,'1981-04-02' ,2975,null, 20);
insert into emp values(7654,'martin','salesman', 7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manager', 7839, '1981-06-09' , 2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3900,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13', 1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null, 20);
insert into emp values(7934, 'miller', 'clerk',7782,'1981-01-23',1300,null,10);
-- 创建工资等级表
create table salgrade(
grade int, -- 工资等级
losal double, -- 最低工资
hisal double -- 最高工资
);
-- 给工资等级表添加数据
insert into salgrade values (1, 700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);
-- 练习
-- 1、返回工资高于 30部门所有员工工资水平的员工信息
-- select * from emp where deptno in(select deptno from emp where sal>30);
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
SELECT * FROM emp WHERE sal > all(SELECT sal FROM emp WHERE deptno = 30);
-- 2、返回员工工作及其从事此工作的最低工资
-- select job from emp;
-- select min(sal) from emp;
select job,min(sal) sal from emp group by job ;
-- 3、计算出员工的年薪，并且以年薪排序
select sal,comm from emp;
-- ifnull(comm,0) comm没有就用0代替
select ename,(sal*12+ifnull(comm,0)) year_sal from emp order by year_sal ;
-- 4、返回工资处于第四级别的员工的姓名
-- select hisal from salgrade where grade='4';
-- select losal from salgrade where grade='4';
select * from emp where sal between (select losal from salgrade where grade='4') and (select hisal from salgrade where grade='4');
select * from emp where sal >= (select losal from salgrade where grade='4') and sal<=(select hisal from salgrade where grade='4');
-- 5、返回工资为二等级的职员名字、部门所在地
-- select * from emp,salgrade;
-- select * from emp,salgrade,dept where grade='2';
select ename,loc
from (select * from emp where sal between (select losal from salgrade where grade='4') and (select hisal from salgrade where grade='4')) a,dept b
where a.deptno=b.deptno;
select ename,dname from emp join dept
on emp.deptno=dept.deptno
and (sal between (select losal from salgrade where grade='2') and (select hisal from salgrade where grade=2));
-- exists 子查询关键字
select from emp a where exists(select from emp b where a.age>60);
select from emp a where exists(select from dept where a.dept_id=dept.deptno);
-- 自关联查询
-- 创建表,并建立自关联约束
create table t_sanguo(
eid int primary key ,
ename varchar(20),
manager_id int,foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);
-- 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
select a.ename '人物',b.ename '上级' from t_sanguo a,t_sanguo b where a.manager_id=b.eid;
