# MySQL综合练习

表结构

--部门表
create table dept
(
deptno int(3) primary key,
dname varchar(14),
loc varchar(13)
);

--雇员表
create table emp
(
empno int(4) not null primary key,
ename varchar(10),
job varchar(10),
mgr int(4),
hiredate datetime,
sal double,
comm double,
deptno int(3),
foreign key(deptno) references dept(deptno)
);

--工资级别表
create table salgrade
(
grade int(3) primary key,
losal int(3),
hisal int(3)
); 

2.插入数据（进行初始化）

use company;

--往部门表中查数据
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');
insert into dept values(50,'Admin','Washing');

--往雇员表中插数据
insert into emp values(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);
insert into emp values(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);
insert into emp values(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);
insert into emp values(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);
insert into emp values(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);
insert into emp values(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);
insert into emp values(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);
insert into emp values(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);

--往工资级别表中插数据
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,5000);
insert into salgrade values(6,5001,10000);

1.查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

SELECT
    empno,
    ename,
    sal,
    deptno
FROM emp
WHERE sal = (SELECT max(sal) FROM emp);

2.薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。

SELECT
    avg(sal) avg_sal,
    deptno
FROM
    emp
WHERE
    sal > 1200
GROUP BY
    deptno
HAVING
    avg_sal > 1500
ORDER BY
    avg_sal DESC;

3.查询每个雇员和其所在的部门名

SELECT
    ename,dname
FROM
    emp,dept
WHERE(emp.deptno = dept.deptno);

-- 或者（推荐）(on中就写连接条件，where中就写过滤条件，各司其职)

SELECT
    ename,
    dname
FROM
    emp
JOIN dept ON (emp.deptno = dept.deptno);

4.查询每个雇员姓名及其工资所在的等级

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

5.查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。

SELECT
    ename,
    dname,
    grade
FROM
    emp e
JOIN dept d ON (e.deptno = d.deptno)
JOIN salgrade s ON (
    e.sal BETWEEN s.losal
    AND s.hisal
)
WHERE
    ename NOT LIKE '_a%';

6.查询每个雇员和其经理的姓名

SELECT
    e1.ename,
    e2.ename
FROM
    emp e1,
    emp e2
WHERE
    (e1.mgr = e2.empno);

SELECT
    e1.ename,
    e2.ename
FROM
    emp e1
JOIN emp e2 ON (e1.mgr = e2.empno);

7.查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））

SELECT
    e1.ename,
    e2.ename
FROM
    emp e1
LEFT JOIN emp e2 ON (e1.mgr = e2.empno);

8.查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）

SELECT
    ename,
    dname
FROM
    emp e
RIGHT JOIN dept d ON (e.deptno = d.deptno);

9.查询每个部门中工资最高的人的姓名、薪水和部门编号

-- 先求出每个部门中的最高工资：
select max(sal) max_sal,deptno from emp group by deptno
-- 在使用连接查询：
select ename,sal,e.deptno
from emp e join
(select max(sal) max_sal,deptno from emp group by deptno) t
on(e.sal = t.max_sal and e.deptno = t.deptno);

10.查询每个部门平均工资所在的等级

select deptno,avg_sal,grade from salgrade
join(select deptno,avg(sal) avg_sal from emp group by deptno) t
on(t.avg_sal between salgrade.losal and salgrade.hisal);
或者：
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);

11.查询每个部门内平均的薪水等级

先求每个人的薪水等级
select ename,deptno,grade from emp join salgrade s 
on(emp.sal between s.losal and s.hisal);
再按组进行分组求平均
select deptno,avg(grade) from
(select ename,deptno,grade from emp join salgrade s 
on(emp.sal between s.losal and s.hisal)) t
group by deptno;

12.查询雇员中有哪些人是经理人：

select ename from emp where empno in(select distinct mgr from emp);
或者：
select ename from emp join
(select distinct mgr from emp) t
on(emp.empno=t.mgr);

13.平均薪水最高的部门的部门编号

　1：先求出每个部门的平均薪水和部门号（把这个看成一张表）
select avg(sal) avg_sal,deptno from emp group by deptno;
　2：再求出平均薪水最高值（把这个看成一个值）
select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t;

　3：对1表使用2条件进行查询即可
select avg_sal,deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno) t1
where avg_sal=
(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2);

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

select dname from dept where deptno = 
( select deptno from
     (select avg(sal) avg_sal,deptno from emp group by deptno) t1
  where avg_sal=
     (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2)
);

15.查询平均薪水的等级最低的部门名称

1.求平均薪水
select avg(sal) avg_sal,deptno from emp group by deptno;

2.求平均薪水的等级
select avg_sal,deptno,grade from
  (select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal);

3.求平均薪水的等级最低的那个值
select min(grade) from
(  select avg_sal,deptno,grade from
      (select avg(sal) avg_sal,deptno from emp group by deptno) t
   join salgrade s on(t.avg_sal between s.losal and hisal)
) t3;

4.平均薪水的等级最低的部门的部门编号(将2和3组合起来，将2看成要查询的表，将3看成查询条件)
select deptno from
(   select avg_sal,deptno,grade from
       (select avg(sal) avg_sal,deptno from emp group by deptno) t
    join salgrade s on(t.avg_sal between s.losal and hisal)
) t2
where grade=
(   select min(grade) from
    (   select avg_sal,deptno,grade from
           (select avg(sal) avg_sal,deptno from emp group by deptno) t
            join salgrade s on(t.avg_sal between s.losal and hisal)
    ) t3
);

5.平均薪水的等级最低的部门名称
select dname,deptno from dept where deptno in
(  select deptno from
     (   select avg_sal,deptno,grade from
             (select avg(sal) avg_sal,deptno from emp group by deptno) t
         join salgrade s on(t.avg_sal between s.losal and hisal)
     ) t2
   where grade=
      (   select min(grade) from
         (   select avg_sal,deptno,grade from
                    (select avg(sal) avg_sal,deptno from emp group by deptno) t
                 join salgrade s on(t.avg_sal between s.losal and hisal)
      ) t3
      )
);
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,732评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,496评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,264评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,807评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,806评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,675评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,029评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,683评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 41,704评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,666评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,773评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,413评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,016评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,204评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,083评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,503评论 2赞 343