SELECT * FROM ruozedata.rzdata
--部门表
dept 部门表(deptno部门编号/dname部门名称/loc地点)
create table dept (
deptno numeric(2),
dname varchar(14),
loc varchar(13)
);
insert into dept values(10,'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');
--工资等级表
salgrade工资等级表(grade 等级/losal此等级的最低/hisal此等级的最高)
create table salgrade (
grade numeric,
losal numeric,
hisal numeric
);
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);
--员工表
emp 员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪资/comm奖金/deptno部门号)
工资=薪资+奖金
1.
create table emp (
empno numeric(4) not null,
ename varchar(10),
job varchar(9),
mgr numeric(4),
hiredate datetime,
sal numeric(7, 2),
comm numeric(7, 2),
deptno numeric(2)
);
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, '1982-12-09', 3000, 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, '1983-01-12', 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, '1982-01-23', 1300, null, 10);
#聚合 函数 sum() max() min() count()
#分组 语法 group by ... having ...
#1.求员工表所有人的薪水和，as salsum是别名。
select sum(sal) as slasum from emp;
#2.求每个部门的所有人的薪水和
select deptno,sum(sal) as slasum from emp group by deptno;
#总结：group by 后面出现多少个字段，那么select 后面也要一模一样；当出现“每”或者“各”关键字时，则表示为分组
#3.求每个部门的每个岗位的所有人的薪水和
select deptno,job,sum(sal) as salsum from emp group by deptno,job;
#4.求每个部门的每个岗位的所有人的薪水和，及人数
select deptno,job,sum(sal) as salsum,count(deptno) as pnum from emp group by deptno,job;
#5.求薪水大于1500的哪些部门 ----> 每个部门的薪水和---->哪些薪水大于1500的部门
select deptno,sum(sal) as salsum from emp group by deptno having sum(sal) > 1500;
#having 是放在group by 后面对结果集再次进行筛选
#子查询
select t.* from (select deptno,sum(sal) as salsum from emp group by deptno) as t where t.salsum > 1500;
#关联 join left join,right join,inner join,join
create table testa(aid int,aname varchar(100),address varchar(100));
create table testb(bid int,bname varchar(100),age int);
create table testsal(cid int,sal int);
delete from testsal;
insert into testsal values(1,100);
insert into testsal values(2,300);
insert into testsal values(4,700);
insert into testsal values(8,1300);
insert into testsal values(9,3300);
select * from testsal;
insert into testa values(1,'xiao1','SH1');
insert into testa values(2,'xiao2','SH1');
insert into testa values(3,'xiao3',null);
insert into testa values(4,'xiao4','SH2');
insert into testa values(5,'xiao5','SH2');
insert into testb values(1,'xiao1',10);
insert into testb values(2,'xiao2',20);
insert into testb values(3,'xiao3',30);
insert into testb values(4,'xiao4',40);
insert into testb values(4,'xiao44',440);
insert into testb values(7,'xiao7',70);
insert into testb values(8,'xiao8',80);
insert into testb values(9,'xiao9',90);
#left join
select a.*,b.* from testa a left join testb b on a.aid=b.bid;
#left join是以左表为主，左表数据最全，右表用来匹配的，匹配多少算多少
aid aname address bid bname age
1 xiao1 SH1 1 xiao1 10
2 xiao2 SH1 2 xiao2 20
3 xiao3 null 3 xiao3 30
4 xiao4 SH2 4 xiao4 40
4 xiao4 SH2 4 xiao44 440
5 xiao5 SH2 null null null
#right join
select a.*,b.* from testa a right join testb b on a.aid=b.bid;
#查询结果
1 xiao1 SH1 1 xiao1 10
2 xiao2 SH1 2 xiao2 20
3 xiao3 3 xiao3 30
4 xiao4 SH2 4 xiao4 40
4 xiao4 SH2 4 xiao44 440
7 xiao7 70
8 xiao8 80
9 xiao9 90
#inner join 等值连接，两边都要存在
select a.*,b.* from testa a inner join testb b on a.aid=b.bid;
#查询结果为
1 xiao1 SH1 1 xiao1 10
2 xiao2 SH1 2 xiao2 20
3 xiao3 3 xiao3 30
4 xiao4 SH2 4 xiao4 40
4 xiao4 SH2 4 xiao44 440
#笛卡儿集
select a.*,b.* from testa a,testb b;
#等值连接
select a.*,b.* from testa a,testb b where a.aid=b.bid;
#哪些人 有薪水的有年龄的有地址/
select s.*,b.*,a.* from testsal s left join testb b on s.cid=b.bid left join
testa a on s.cid=a.aid where s.sal is not null and b.age is not null and a.address is not null;
select s.*,b.*,a.* from testsal s left join testb b on s.cid =b.bid left join testa a on
b.bid =a.aid where s.sal is not null and b.age is not null and a.address is not null;
select s.*,b.*,a.* from testsal s left join testb b on s.cid =b.bid right join testa a on
b.bid =a.aid;
#哪些人有薪水，有年龄，有地址
select s.* ,t.* from testsal s left join (select b.*,a.* from testb b right join testa a on b.bid=a.aid)
t on s.cid=t.aid where s.sal is not null and t.age is not null and t.address is not null;
select s.*,t.* from testsal s inner join (select b.*,a.* from testb b inner join testa a on b.bid=a.aid)
t on s.cid = t.aid;
#union all 不去重 ,union 去重
select aid,aname from testa union all select bid,bname from testb;
select aid,aname from testa union select bid,bname from testb;
#哪些部门的薪水最高两位的工种有哪些？（分组取前几位）
#1.每个部门每个工种的薪水和
select * from emp;
create view sal as select deptno,job,sum(sal+ifnull(comm,0)) as sal from emp group by deptno,job;
select * from sal;
#2.从1结果集找出哪些部门的工种薪水和最高的前2位工种是什么？
select a.* from sal a where (select count(*) from sal b where a.deptno=b.deptno and a.sal > b.sal);
#假设取薪水和最高的每个部门的哪个工种？
select * from sal;
insert into emp values (1000,'BOSS','BOSS',null,'1981-06-09',9000,null,10);
#求 Top 1
select a.* from sal a where (select count(*) from sal b where a.deptno=b.deptno and a.sal<b.sal) =0
order by a.deptno;
#求 Top 2
select a.* from sal a where (select count(*) from sal b where a.deptno=b.deptno and a.sal<b.sal)<=1
order by a.deptno asc,a.sal desc;
