mysql> select *from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | 你好 | gz |
| 30 | SALES | CHICAGO |
| 50 | sad | we |
| 60 | nihao | we |
| 100 | guangd | jieyang |
| 1001 | 广东 | jieyang |
+--------+------------+----------+
mysql> select *from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 2525 | SMITH | CLERK | 7902 | 1980-12-17 | 899.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
1、取得每个部门最高薪水的人员名称
select
deptno,max(sal) from emp group by deptno;
将该表作为一张 临时表
//此时必须有别名
//分组函数在where 执行之后执行 此处 on 相似于 where
select
t.maxSal,e.ename,t.deptno
from
(select deptno,max(sal) as maxSal from emp group by deptno) t
left join
emp e
on
e.sal = t.maxSal ;
2、哪些人的薪水在部门的平均薪水之上
//显然需要两张表连接
select avg(sal),deptno from emp group by deptno;
//将上表作为临时表
select e.ename,e.sal ,t.avgSal
from emp e
join (select avg(sal)as avgSal,deptno from emp group by deptno) t
on
e.sal > t.avgSal;
3、取得部门中(所有人的)平均的薪水等级,如下:
//部门人薪资等级 的 平均数
//逐步 转为一张表 (联合薪资表)
select ename,grade ,deptno
from emp
left join
salgrade
on
sal between losal and hisal;
//求平均薪资等级
select t.deptno,avg(grade)
from (
select e.ename,s.grade,e.deptno
from emp e
left join
salgrade s
on
e.sal between s.losal and s.hisal
) t
group by deptno;
4、不准用组函数(Max),取得最高薪水
//注意 : limit 不加 括号
select sal from emp order by sal desc limit 0,1;
5、取得平均薪水最高的部门的部门编号
//找出各部门平均薪资水平
select deptno,avg(sal) as sal from emp group by deptno;
//作为临时表
//再找出各部门薪资水平最高的
select max(t.sal) as maxSal from
(select deptno,avg(sal) as sal from emp group by deptno) t
;
//将以上两张表 计较
select a.deptno
from (select deptno,avg(sal) as sal from emp group by deptno) a
join
(select max(t.avg) as maxSal from
(select deptno,avg(sal) as avg from emp group by deptno) t) b
on a.sal =b.maxSal ;
/*select t.deptno,max(t.sal)
from (select deptno,avg(sal) as sal from emp group by deptno) t
;*///这样不可以 因为 deptno 还没有group by 不符合语法
6、取得平均薪水最高的部门的部门名称
由上一题可知 最高薪资部门
select a.deptno
from (select deptno,avg(sal) as sal from emp group by deptno) a
join
(select max(t.avg) as maxSal from
(select deptno,avg(sal) as avg from emp group by deptno) t) b
on a.sal =b.maxSal ;
//将上表作为临时表
select t.deptno, d.dname from(
select a.deptno
from (select deptno,avg(sal) as sal from emp group by deptno) a
join
(select max(t.avg) as maxSal from
(select deptno,avg(sal) as avg from emp group by deptno) t) b
on a.sal =b.maxSal) t
join dept d
on
d.deptno = t.deptno;
7、求平均薪水的等级最低的部门的部门名称
//找出各部门平均薪资水平
select avg(sal) from emp group by deptno;
//内连接 非等值关系
select s.grade , t.sal,t.deptno from
(select deptno,avg(sal)as sal from emp group by deptno) t
join
salgrade s
on
t.sal between s.losal and s.hisal;
8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
//先找出普通员工最高薪资 (显然需要两张表) 先找不是领导的
select distinct mgr from emp where mgr is not null;
//找出最高薪资的 员工
select max(sal) from
emp
where empno not in (select distinct mgr from emp where mgr is not null);
//
select e.ename from emp e
join
(select max(sal)as maxSal from
emp
where empno not in (select distinct mgr from emp where mgr is not null)) t
on e.sal > t.maxSal;
9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 0,5;
10、取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;
11、取得最后入职的5名员工
select ename,hiredate from emp order by hiredate desc limit 0,5;
12、取得每个薪水等级有多少员工
select s.grade,e.ename from emp e
join salgrade s
on e.sal between s.losal and s.hisal ;
select t.grade,count(*) from
(select s.grade,e.ename from emp e
join salgrade s
on e.sal between s.losal and s.hisal) t
group by t.grade;
select s.grade,count(*) from
emp e
join
salgrade s
on e.sal between s.losal and s.hisal
group by s.grade;
13、S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit;
// 1.找出没选过“黎明”老师的所有学生姓名。
select cno from c1 where cteacher = '黎明';
select sno from sc where cno = (select cno from c1 where cteacher = '黎明');
select distinct sno from sc where sno not in (select sno from sc where cno = (select cno from c1 where cteacher = '黎明'));
select sname from s where sno = (select distinct sno from sc where sno not in (select sno from sc where cno = (select cno from c1 where cteacher = '黎明')));
//2:列出2门以上(含2门)不及格学生姓名及平均成绩。
select sno, scgrade from sc where scgrade <60;
select sno,count(*) as sum from (select sno, scgrade from sc where scgrade <60) t group by t.sno having sum >=2 ;
select s.sno,s.cno,s.scgrade from sc s
join (select sno,count(*) as sum from (select sno, scgrade from sc where scgrade <60) t group by t.sno having sum >=2 ) t
on t.sno = s.sno;
select t.sno,avg(t.scgrade)as avgS from (
select s.sno,s.cno,s.scgrade from sc s
join (select sno,count(*) as sum from (select sno, scgrade from sc where scgrade <60) t group by t.sno having sum >=2 ) t
on t.sno = s.sno
) t
group by t.sno;
select t.sno from (select t.sno,avg(t.scgrade)as avgS from (
select s.sno,s.cno,s.scgrade from sc s
join (select sno,count(*) as sum from (select sno, scgrade from sc where scgrade <60) t group by t.sno having sum >=2 ) t
on t.sno = s.sno
) t
group by t.sno) t;
select s.sno,s.sname,x.avgS from s s,(select t.sno,avg(t.scgrade)as avgS from (
select s.sno,s.cno,s.scgrade from sc s
join (select sno,count(*) as sum from (select sno, scgrade from sc where scgrade <60) t group by t.sno having sum >=2 ) t
on t.sno = s.sno
) t
group by t.sno) x
where s.sno in (select t.sno from (select t.sno,avg(t.scgrade)as avgS from (
select s.sno,s.cno,s.scgrade from sc s
join (select sno,count(*) as sum from (select sno, scgrade from sc where scgrade <60) t group by t.sno having sum >=2 ) t
on t.sno = s.sno
) t
group by t.sno) t);
//3:即学过1号课程又学过2号课所有学生的姓名。
select a.sno from
(select sno from sc where cno = 1) a
where a.sno in (select sno from sc where cno = 2);
select distinct x.sno,x.sname from s x
join
(select a.sno from
(select sno from sc where cno = 1) a
where a.sno in (select sno from sc where cno = 2)) t
on
x.sno in (select s.sno from (
select a.sno from
(select sno from sc where cno = 1) a
where a.sno in (select sno from sc where cno = 2)
) s ) order by x.sno;
select distinct x.sno,x.sname from s x
join
(select a.sno from
(select sno from sc where cno = 1) a
where a.sno in (select sno from sc where cno = 2)) t
on
x.sno in (select s.sno from (
select a.sno from
(select sno from sc where cno = 1) a
where a.sno in (select sno from sc where cno = 2)
) s );
14、列出所有员工及领导的姓名
select
a.ename,b.ename as manager
from
emp a
left join
emp b
on
a.mgr = b.empno;
15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select
a.empno,a.ename,b.ename as manager,a.hiredate,t.dname
from
emp a
join
emp b
join
dept t
on
(a.mgr = b.empno)
and
(a.hiredate < b.hiredate)
and
a.deptno = t.deptno
;
16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select
t.deptno,e.*
from
dept t
left join
emp e
on
t.deptno = e.deptno;
17、列出至少有5个员工的所有部门
select
//Expression #1 of SELECT list is not in GROUP 查询字段必须在 group by 生命之下
t.deptno
from
(select
e.*
from
dept t
left join
emp e
on
t.deptno = e.deptno) t
group by
t.deptno
having count(t.ename) >= 5;
18、列出薪金比"SMITH"多的所有员工信息.
select
*
from
emp
where
sal > (select sal from emp where ename = 'smith');
19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
select
e.empno,e.ename,e.deptno ,t.count
from
emp e
join
(
select d.deptno ,d.dname , count(e.ename) as count
from
dept d
join
emp e
on
d.deptno = e.deptno
group by
e.deptno
) t
on
e.deptno = t.deptno
where
job = 'clerk';
20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
select
a.job,b.count
from
(
select job
from
emp
group by
job
having
(min(sal) > 1500)
) a
join
(
select job ,count(ename) as count
from emp
group by job
) b
on
a.job = b.job;
21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
select deptno,dname
from dept
where dname = 'SALES';
select
t.deptno,t.dname,e.ename
from
emp e
join
(
select deptno,dname
from dept
where dname = 'SALES'
) t
on e.deptno = t.deptno;
22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
select e.ename,e1.ename as manager ,e.deptno,d.dname,s.grade
from
emp e
join
(
select avg(sal) as avg from emp
) t
join
dept d
join
emp e1
join
salgrade s
on (e.sal > t.avg )
and
(d.deptno = e.deptno)
and
(e.mgr = e1.empno)
and
(e.sal between s.losal and s.hisal);
23、列出与"SCOTT"从事相同工作的所有员工及部门名称
select job from emp where ename = 'scott';
select
e.ename ,d.dname
from
emp e
join
(select job from emp where ename = 'scott') t
join
dept d
on
e.job = t.job and e.deptno = d.deptno ;
24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
************************************
待定
select ename,sal from emp where deptno = 30;
select e.ename
from
emp e
where
e.sal in (select sal from emp where deptno = 30)
and
e.deptno != 30;
25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
select ename,sal from emp where deptno = 30;
select max(t.sal)
from
(
select ename,sal from emp where deptno = 30
) t
;
select
e.ename ,e.sal,d.dname
from
emp e
join
(
select max(t.sal) as maxSal
from
(
select ename,sal from emp where deptno = 30
) t
) t
join
dept d
on e.sal > t.maxSal
and
e.deptno != 30
and
d.deptno = e.deptno;
26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
select deptno,(now() - avg(hiredate) )/1000 /60/60/24 as timeGap from emp
group by deptno;
select
deptno,count(ename) as count ,avg(sal) as avgSal
from
emp
group by
deptno ;
select
e.deptno,count(e.ename) as count ,avg(e.sal) as avgSal ,t.timeGap
from
emp e
join
(
select deptno,(now() - avg(hiredate) )/1000 /60/60/24 as timeGap from emp
group by deptno
) t
on
e.deptno = t.deptno
group by
e.deptno
order by
deptno asc ;
27、列出所有员工的姓名、部门名称和工资。
select e.ename,e.sal,d.dname from emp e ,dept d where e.deptno = d.deptno ;
28、列出所有部门的详细信息和人数
select * from dept;
select d.* ,t.count
from
dept d
join
(select deptno,count(ename) as count from emp group by deptno) t
on d.deptno = t.deptno;
29、列出各种工作的最低工资及从事此工作的雇员姓名
select min(sal) as minSal from emp group by job;
select
e.ename,e.sal
from
emp e
join
(
select min(sal) as minSal from emp group by job
) t
on e.sal = t.minSal;
30、列出各个部门的MANAGER(领导)的最低薪金
select ename,deptno,sal from emp where empno in (select distinct mgr from emp where mgr is not null);
select
min(t.sal)
from
(
select ename,deptno,sal from emp where empno in (select distinct mgr from emp where mgr is not null)
) t
group by
t.deptno;
select
e.ename,t.minSal
from emp e
join
(
select
min(t.sal) as minSal
from
(
select ename,deptno,sal from emp where empno in (select distinct mgr from emp where mgr is not null)
) t
group by
t.deptno
) t
on e.sal = t.minSal;
31、列出所有员工的年工资,按年薪从低到高排序
select ename, (sal+ifnull(comm,0)) * 12 as yearSal from emp order by yearSal asc;
32、求出员工领导的薪水超过3000的员工名称与领导名称
select distinct e.ename,e.sal from emp e
join
emp e1
on
e.empno = e1.mgr
where e.empno in( select distinct mgr from emp where mgr is not null) and e.sal > 2500;
33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
select deptno,dname from dept where dname like '%s%' or 's%' or '%s'
;
select
t.deptno,t.dname,avg(e.sal)
from emp e
right join
(select deptno,dname from dept where dname like '%s%' or 's%' or '%s') t
on e.deptno = t.deptno
group by
t.deptno;
34、给任职日期超过30年的员工加薪10%.
select ename as timeGap from emp group by ename having ((now() - avg(hiredate) )/1000 /60/60/24/365) >30;