mysql学习十一：示例

1.取得每个部门最高薪水的人员名称。一张表。

第一步：求出每个部门的最高薪水，以部门分组

select e.deptno,max(e.sal) as maxsal from emp e group by e.deptno;

第二步：将以上查询结果当成一个临时表t(deptno,maxsal)，此临时表作为查询的主表；再与此表e表连接找出人员名称，t.deptno=e.deptno作为连接条件，maxsal=e.sal作为查询条件。

select xxx from t
join emp e on e.deptno=t.deptno
where t.maxsal=e.sal;

SELECT
    e.deptno,
    e.ename,
    t.maxsal,
    e.sal 
FROM
    ( SELECT e.deptno, max( e.sal ) AS maxsal FROM emp e GROUP BY e.deptno ) t
JOIN emp e ON t.deptno = e.deptno 
WHERE
    t.maxsal = e.sal 
ORDER BY
    e.deptno;

2.哪些人的薪水在部门平均薪水之上

同上题一样，分两步，第一步：求出每个部门的平均薪水

SELECT
    e.deptno,
    avg( e.sal ) AS avgsal 
FROM
    emp e 
GROUP BY
    e.deptno

第二步：将以上查询结果作为临时表t，再与此表连接，此表中的每条数据与临时表中部门的平均薪水作比较。

select xxx from t
join emp e on e.deptno=t.deptno
where e.sal > t.avgsal;

select t.deptno,e.ename 
from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t
join emp e on e.deptno=t.deptno
where e.sal > t.avgsal;

3.取得部门中（所有人）的平均薪水等级

3.1取得部门中所有人的平均薪水的等级

第一步：求出部门的平均薪水

select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno;

第二：以上的平均薪水，对应的等级

将第一步查询结果，作为临时表t ，作为from查询主表

select xxx from t
join salgrade s on t.avgsal between s.losal and s.hisal;

select t.deptno,t.avgsal,s.grade from 
(select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t
join salgrade s on t.avgsal between s.losal and s.hisal;

3.2取得部门中所有人的平均的薪水等级

第一步：求出每个人的薪水等级

select e.deptno,e.name,s.grade from emp e
join salgrade s on e.sal between s.losal and s.hisal
order by e.deptno;

第二步：求出部门的平均的薪水等级。将以上查询结果，当作临时表t，以部门分组，求出s.grade的平均值

select t.deptno,avg(t.grade) as avgGrade
from t
group by t.deptno;

select t.deptno,avg(t.grade) as avgGrade
from (select e.deptno,e.name,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal order by e.deptno) t
group by t.deptno;

4.不使用组函数max，取得最高薪水，最低薪水

方案一：

select sal from emp order by sal desc limit 1

select sal from emp order by sal asc limit 1

方案二：

select sal from emp where sal not in (select distinct a.sal from emp a
join emp b on a.sal < b.sal);

select sal from emp where sal not in (select distinct a.sal from emp a
join emp b on a.sal > b.sal);

5.取得平均薪水最高的部门的部门编号

第一步：求出各部门的平均薪水

select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno;

第二：对以上查询结果，取出最大值

select max(t.avgsal) as maxavgsal from 
(select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno) t;

如果最大值有多个并列，

以上结果求出的最大值，只有一个

以下查询，则列出多个

select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno
having avgsal=(select max(t.avgsal) as maxavgsal 
from (select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno) t);

6.求出平均薪水最高的部门的部门名称

select e.deptno,d.dname, avg(e.sal) as avgsal from emp e 
join dept d on e.deptno=d.deptno
group by e.deptno,d.dname
having avgsal=select max(t.avgsal) as maxavgsal 
from (select e.deptno, avg(e.sal) as avgsal from emp e group by e.deptno t);

或者将5题的结果作为临时表，再从dept表中查找部门名称，也可以。

7.求平均薪水的等级最低的部门的部门名称

第一步：部门的平均薪水

select e.deptno,d.dname,avg(e.sal) as avgsal 
from emp e join dept d on e.deptno=d.deptno group by e.deptno,d.dname;

第二步：将以上查询结果，作为临时表t，与salgrade表进行连接：t.avgsal between s.losal and s.hisal

select t.dept,t.dname,s.grade 
from t  join salgrade s on t.avgsal between s.losal and s.hisal;

第三步：将以上查询结果，作为临时表t

select min(t.grade) as minGrade from t;

第四步：having或where。

最终结果：

select t.deptno,t.dname,s.grade from 
(select e.deptno,e.dname,avg(e.sal) as avgsal
from emp e join dept d on e.deptno=d.deptno group by e.deptno,d.dname) t
join salgrade s on t.avgsal between s.losal and s.hisal
where s.grade=(
select min(t.grade) as minGrade from (
select t.deptno,t.dname,s.grade from (
select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by e.deptno,d.dname) t
join salgrade s on t.avgsal between s.losal and s.hisal) t);

8.取得比普通员工最高薪水还要高的经理人姓名

第一步，找出普通员工的最高薪水

1.1找出经理

select distinct mgr from emp;

1.2找出普通员工

in会自动忽略空值;not in 不会自动忽略空值。

如果有null值参与运算，就会出算。因此，在not in时要手动去掉空值。

select * from emp where empno not in (select distinct mgr from emp where mgr is not null);

1.3找出普通员工的最高薪水

select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null);

2.找出薪水比普通员的最高薪水还要高的经理人姓名

select e.ename from emp e where e.sal > 
(select max(sal) as maxsal from emp where empno not in 
(select distinct mgr from emp where mgr is not null));

9.取得薪水最高的前五名员工

select * from emp order by sal desc limit 0,5;

mysql默认的排序是升序asc

10.取得薪水最高的第六到第十名员工

select * from emp order by sal desc limit 5, 5;

mysql的下标，是从0开始的。

11.取得最近入职的5名员工

select * from emp order by hiredate desc limit 0, 5;

排序除了可以通过数值，还可以通过日期date来排序

12.取得每个薪水等级有多少员工

1.取得每个员工的薪水等级

SELECT
    e.ename,
    s.grade 
FROM
    emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal 
    AND s.hisal 
ORDER BY
    s.grade;

select t.grade,count(t.ename) from (SELECT
    e.ename,
    s.grade 
FROM
    emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal 
    AND s.hisal 
) t group by t.grade;

13.有3个表s学生表，c课程表，sc学生选课表

s(sno, sname)学号，姓名

c(cno,cname,cteacher)课号，课名，老师

sc(scno, cno, scgrade)学号，课号，成绩

问题：

1.找出没有选过“黎明”老师的所有学生的姓名

2.列出2门以上(含2门）不及格学生姓名及平均成绩。

3.既学过1号课程又学过2号课程的所有学生的姓名

CREATE TABLE s (
sno INT ( 4 ) PRIMARY KEY auto_increment,
sname VARCHAR ( 32 ) 
);

CREATE TABLE c(
cno INT ( 4 ) PRIMARY KEY auto_increment,
cname VARCHAR ( 32 ),
cteacher varchar(32)

);

学生与课程表之间，是多对多的关系；因此成绩表的主键，设置为学生与课程表id联合主键；

同时成绩表的sno和学生表是外键的关系，成绩表的cno和课程表是外键关系。

CREATE TABLE sc(
sno INT ( 4 ),
cno INT ( 4 ),
scgrade double(3, 1),
constraint sc_sno_cno_pk primary key(sno, cno),
constraint sc_sno_fk foreign key(sno) references s(sno),
constraint sc_cno_fk foreign key(cno) references c(cno)
);

修改为中文字符集

ALTER TABLE `mydb`.`s` CHARACTER SET = utf8, COLLATE = utf8_general_ci;

ALTER TABLE `mydb`.`s` 
MODIFY COLUMN `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `sno`;

insert into s(sname) values('张三'), ('李四'), ('王五'), ('赵六');

ALTER TABLE `mydb`.`c` 
MODIFY COLUMN `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `cno`,
MODIFY COLUMN `cteacher` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `cname`,
CHARACTER SET = utf8, COLLATE = utf8_general_ci;

insert into c(cname,cteacher) values('java','吴老师'), ('c','王老师'), ('python','张老师'), ('mysql', '黎明');

insert into sc(sno,cno, scgrade) values(1,1,30), (1,2,50),(1,3,80),(1,4,90),(1,5,70),(2,2,80),(2,3,50),(2,4,70),(2,5,80),(3,1,60),(3,2,70),(3,3,80),(4,3,50);

1.1找出黎明老师的授课编号

select cno from c where cteacher='黎明';

1.2找出选过黎明老师的学生编号

select sno from sc where cno=(select cno from c where cteacher='黎明');

1.3没有选过的黎明老师的学生

select * from s where sno not in 
(select sno from sc where cno=(select cno from c where cteacher='黎明'))

2.1找出2门不及学生的编号

SELECT
    sc.sno,
    count( * ) AS studentNum 
FROM
    sc 
WHERE
    scgrade < 60 GROUP BY sc.sno HAVING studentNum >= 2;

2.2找出其姓名

SELECT
    sc.sno,
    s.sname,
    count( * ) AS studentNum 
FROM
    sc
    JOIN s ON sc.sno = s.sno 
WHERE
    scgrade < 60 GROUP BY sc.sno, s.sname HAVING studentNum >= 2;

此表作为临时表t1

2.3学生的平均成绩

select sc.sno, avg(sc.scgrade) as scgrade from sc group by sc.sno;

此表作为临时表t2

2.4 2门以上(含2门）不及格学生姓名及平均成绩

select t1.sname,t2.avgscgrade
from t1
join t2 on t1.sno=t2.sno;

或者

SELECT
    sc.sno,
    s.sname,
    count( * ) AS studentNum,
    avg(sc.scgrade) as avggrade
FROM
    sc
    JOIN s ON sc.sno = s.sno 
WHERE
    scgrade < 60 GROUP BY sc.sno, s.sname HAVING studentNum >= 2;

3.1

SELECT
    sc.sno,s.sname
FROM
    sc 
JOIN s ON sc.sno=s.sno
WHERE
    cno = 1 
    AND sc.sno IN ( SELECT sno FROM sc WHERE cno = 2 );

14.列出所有员工及领导的名字

有些员工，没有mgr，因此必须用外连接

select e.ename, b.ename as leadername from emp e left join emp b on e.mgr=b.empno;

15.列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称

select e.empno,e.ename,d.deptno,d.dname
from emp e
join emp b on e.mgr=b.empno
join dept d on e.deptno=d.deptno
where e.hiredate < b.hiredate;

16.列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门

select d.dname,e.*
from emp e
right join dept d on e.deptno=d.deptno

17.列出至少有5个员工的所有部门

1.1求出每个部门的员工数量

select e.deptno, count(e.ename) as total
from emp e
group by e.deptno;

1.2

select e.deptno, count(e.ename) as total
from emp e
group by e.deptno
having total >= 5;

18.列出薪水比bob高的所有员工信息

1.1 bob的薪水

select sal from emp where ename='bob'

1.2

select sal from emp where sal > (select sal from emp where ename='bob');

19.列出所有clerk职位的姓名及其部门名称，部门人数

1.1clerk职位的姓名及其部门名称

select e.ename,e.deptno,d.dname
from emp e
join dept d on e.deptno=d.deptno
where e.job='clerk';

此表作为t1

1.2部门人数

select e.deptno,count(e.ename) as totalEmp from emp e group by e.deptno;

此表作为t2

select xxx

from t1

join t2

on t1.deptno=t2.deptno

select t1.deptno,t1.dname,t1.ename,t2.totalEmp

from (select e.ename,e.deptno,d.dname
from emp e
join dept d on e.deptno=d.deptno
where e.job='clerk') t1

join (select e.deptno,count(e.ename) as totalEmp from emp e group by e.deptno) t2

on t1.deptno=t2.deptno

20.列出最低薪水大于3000的各种工作及从事此工作的全部雇员人数

第一步：求出每种工作岗位的最低薪水

select e.job, min(e.sal) as minsal
from emp e
group by e.job;

第二步：having

select e.job, min(e.sal) as minsal
from emp e
group by e.job
having minsal > 3000;

第三步：此工作岗位的总人数

select e.job, min(e.sal) as minsal, count(e.ename) as totalEmp
from emp e
group by e.job
having minsal > 3000;

21.列出在部门finance工作的员工的姓名，假定不知道finance部门的部门编号

select deptno from dept where dname='finance'

select ename from emp where deptno = (select deptno from dept where dname='finance');

22.列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级

第一步：求出公司的平均薪水

select avg(sal) avgsal from emp;

第二步：

select xxx

from emp e

join dept d on e.deptno=d.deptno

left join emp b on e.mgr=b.empno

join salgrade s on e.sal between s.losal and s.hisal

where e.sal > 第一步的平均值

select d.dname,e.ename,b.ename as leadername,s.grade
from emp e
join dept d on e.deptno=d.deptno
left join emp b on e.mgr=b.empno
join salgrade s on e.sal between s.losal and s.hisal
where e.sal > (select avg(sal) avgsal from emp);

23.列出与didy从事相同工作的所有员工及部门名称

didy从事相的工作

select job from emp where ename='didy';

及其部门名称

SELECT
    d.dname,
    e.* 
FROM
    emp e
    JOIN dept d ON e.deptno = d.deptno 
WHERE
    e.job = ( SELECT job FROM emp WHERE ename = 'didy' );

24.列出薪金等于部门3中员工的薪金的其它员工的姓名和薪金

部门3中员工的薪金

select distinct sal from emp where deptno=3;

select ename,sal from emp where sal in (select distinct sal from emp where deptno=3) and deptno<>3;

25.列出薪金高于在部门3工作的所有员工的薪金的员工姓名和薪金、部门名称

部门3中的最高薪水

select max(sal) as maxsal from emp where deptno=3;

select d.dname,e.ename,e.sal
from emp e
join dept d on e.deptno=d.deptno
where e.sal > (select max(sal) as maxsal from emp where deptno=3) ;

26.列出在每个部门工作的员工数量、平均工资、平均服务期限

select e.deptno, count(e.ename) as totalEmp,avg(e.sal) as avgsal,
avg((TO_DAYS(now()) - TO_DAYS(hiredate))/365) as avgserveryear 
from emp  e group by e.deptno

27.列出所有员工的姓名、部门名称和工资

select d.dname,e.ename,e.sal from emp e 
right join dept d on e.deptno=d.deptno

28.列出所有部门的详细信息和人数

select e.deptno,d.dname,d.loc,count(e.ename) as totalEmp
from emp e
right join dept d on e.deptno=d.deptno
group by d.deptno, d.dname,d.loc;

29.列出各种工作的最低工资及从事此工作的雇员姓名

select e.job,min(e.sal) as minsal 
from emp e
group by e.job;

将以上查询结果作为临时表t

select xx

from emp e

join t on e.job=t.job

where e.sal=t.minsal

select e.ename
from emp e
join (select e.job,min(e.sal) as minsal 
from emp e
group by e.job) t on e.job=t.job
where e.sal=t.minsal;

30.列出各个部门manager的最低薪金

select e.deptno,min(e.sal) as minsal
from emp e
where e.job='manager'
group by e.deptno;

31.列出所有员工的年工资，按年薪从低到高排序

select ename,(sal+ifnull(comm, 0))*12 as yearsal from emp order by yearsal asc;

32.求出员工领导的薪水超过3000的员工名称和领导名称

select e.ename,b.ename as leadername
from emp e
join emp b on e.mgr=b.empno
where b.sal > 3000;

33.求出部门中名称带f字符的部门员工的工资合计、部门人数

select d.dname,sum(e.sal) as sumsal,count(e.ename) as totalEmp
from emp e
join dept d on e.deptno=d.deptno
where d.dname like '%f%'
group by d.dname;

34.给任职日期超过5年的员工加薪10%

备份表：

create table emp_bask as select * from emp;

update emp_bak set sal=sal*1.1 where (TO_DAYS(now()) - TO_DAYS(hiredate))/365 > 5;

posted on 2018-09-16 23:58 myworldworld 阅读(351) 评论(0) 收藏举报

刷新页面返回顶部

myworldworld

mysql学习十一：示例

导航

公告