MySQL常用命令，34道练习题...

MySql常用命令总结

PS：感谢Dean Xu的第一个Star，值得纪念哈。

SQL脚本下载地址：欢迎star

1、使用SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

2、创建一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3、选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)

4、查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5、创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6、显示表的结构：

mysql> DESCRIBE MYTABLE;

7、往表中加入记录

mysql> insert into MYTABLE values (”hyq”,”M”);

8、用文本方式将数据装入数据库表中（例如D:/mysql.txt）

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9、导入.sql文件命令（例如D:/mysql.sql）

mysql>use database;
mysql>source d:/mysql.sql;

10、删除表

mysql>drop TABLE MYTABLE;

11、清空表

mysql>delete from MYTABLE;

12、更新表中数据

mysql>update MYTABLE set sex=”f” where name=’hyq’;

匿名帐户删除、 root帐户设置密码:

use mysql;
delete from User where User=”";
update User set Password=PASSWORD(’newpassword’) where User=’root’;

GRANT的常用用法如下：

grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;

全局管理权限：

FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。

数据库/数据表/数据列权限：

ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。

特别的权限：

ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

MySQL-Practice-Questions

1、取得每个部门最高薪水的人员名称

第一步：取得每个部门最高薪水『按照部门分组求最大值』

mysql> select deptno,max(sal) as maxsal from emp group by deptno;

deptno	maxsal
10	5000.00
20	3000.00
30	2850.00

第二步：将上面的查询结果当作临时表t，t表和emp e表进行连接

条件：e.deptno=t.deptno and e.sal=t.sal

mysql> select
    ->   e.ename t.*
    -> from
    ->   emp e
    -> join
    ->  (select deptno,max(sal) as maxsal from emp group by deptno) t
    -> on
    ->  e.deptno=t.deptno and e.sal = t.maxsal;

ename	deptno	maxsal
BLAKE	30	2850.00
SCOTT	20	3000.00
KING	10	5000.00
FORD	20	3000.00

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

第一步：找出部门的平均薪水『按部门编号分组求平均薪水』

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

deptno	avgsal
10	2916.666667
20	2175.000000
30	1566.666667

第二步：将上面的查询结果当作临时表t，与emp e表进行连接

条件：t.deptno=t.deptno and e.sal > t.avgsal

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

ename	sal	deptno	avgsal
ALLEN	1600.00	30	1566.666667
JONES	2975.00	20	2175.000000
BLAKE	2850.00	30	1566.666667
SCOTT	3000.00	20	2175.000000
KING	5000.00	10	2916.666667
FORD	3000.00	20	2175.000000

3、1取得部门中(所有人)平均薪水的等级

第一步：取得部门中的平均薪水

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

deptno	avgsal
10	2916.666667
20	2175.000000
30	1566.666667

第二部：将上面的查询结果当作临时表t，t表和salgrade s表进行关联

条件：e.sal between s.losal and s.hisal

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

deptno	avgsal	grade
10	2916.666667	4
20	2175.000000	4
30	1566.666667	3

3、2取得部门中(所有人)薪水的平均等级

PS：感谢westmelon提出错误，并给出了解决方案。如下：

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

第一步：每个员工的薪水等级(oder by 以部门编号排序，为了好理解)

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

ename	sal	deptno	grade
MILLER	1300.00	10	2
KING	5000.00	10	5
CLARK	2450.00	10	4
ADAMS	1100.00	20	1
SCOTT	3000.00	20	4
FORD	3000.00	20	4
JONES	2975.00	20	4
SMITH	800.00	20	1
MARTIN	1250.00	30	2
ALLEN	1600.00	30	3
JAMES	950.00	30	1
BLAKE	2850.00	30	4
WARD	1250.00	30	2
TURNER	1500.00	30	3

第二步：在以上基础上继续以部门编号分组，求平均薪水等级

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

deptno	avggrade
10	3.6667
20	2.8000
30	2.5000

4、不用组函数(MAX),取得最高薪水(给出两种解决方案)

方案一：按照薪水降序排，取得第一个

mysql> select sal from emp order by sal desc limit 1;

方案二：自连接

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

sal
5000.00

5、取得平均薪水最高的部门的编号(至少给出两种解决方案)

第一种方案：平均薪水降序排取第一个

第一步：取得每个部门的平均薪水

mysql> select deptno,avg(sal) avgsal from emp group by deptno;

deptno	avgsal
10	2916.666667
20	2175.000000
30	1566.666667

第二步：取得平均薪水的最大值

mysql> select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

avgsal
2916.666667

第三步：将第一步和第二步结合

select
  deptno,avg(sal) as avgsal
from
  emp
group by
    deptno
having
    avg(sal)=( select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);

deptno	avgsal
10	2916.666667

第二种方案：MAX函数

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

deptno	avgsal
10	2916.666667

6、取得平均薪水最高的部门的部门名称

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

dname	avgsal
ACCOUNTING	2916.666667

7、求平均薪水的等级最高的部门的部门名称

第一步：求各个部门平均薪水的等级

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

dname	avgsal	grade
ACCOUNTING	2916.666667	4
RESEARCH	2175.000000	4
SALES	1566.666667	3

第二步：获得最高等级

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

第三步：将第一步和第二步联合

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

dname	avgsal	grade
ACCOUNTING	2916.666667	4
RESEARCH	2175.000000	4

8、取得比普通员工的最高薪水还要高的领导人姓名

第一步：取得普通员工

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

以上语句无法查村到结果，因为not in 不会自动忽略NULL，需要自己手动排除NULL。 in 自动忽略NULL

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

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800.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
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
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
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

第二步：找出员工最高薪水的人

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

max(sal)
1600.00

第三步：找出薪水大于1600即可

select ename,sal from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));

ename	sal
JONES	2975.00
BLAKE	2850.00
CLARK	2450.00
SCOTT	3000.00
KING	5000.00
FORD	3000.00

补充：case ... when ... then ... when ... then ... else ... end 类似于Java中的switch..case

select
  ename,sal,(case job when 'MANAGER' then sal*1.1 when 'CLERK' then sal*1.5 end) as newsal
from
  emp;

ename	sal	newsal
SMITH	800.00	1200.00
ALLEN	1600.00	NULL
WARD	1250.00	NULL
JONES	2975.00	3272.50
MARTIN	1250.00	NULL
BLAKE	2850.00	3135.00
CLARK	2450.00	2695.00
SCOTT	3000.00	NULL
KING	5000.00	NULL
TURNER	1500.00	NULL
ADAMS	1100.00	1650.00
JAMES	950.00	1425.00
FORD	3000.00	NULL
MILLER	1300.00	1950.00

select
  ename,sal,(case job when 'MANAGER' then sal*1.1 when 'CLERK' then sal*1.5 else sal end) as newsal
from
  emp;

ename	sal	newsal
SMITH	800.00	1200.00
ALLEN	1600.00	1600.00
WARD	1250.00	1250.00
JONES	2975.00	3272.50
MARTIN	1250.00	1250.00
BLAKE	2850.00	3135.00
CLARK	2450.00	2695.00
SCOTT	3000.00	3000.00
KING	5000.00	5000.00
TURNER	1500.00	1500.00
ADAMS	1100.00	1650.00
JAMES	950.00	1425.00
FORD	3000.00	3000.00
MILLER	1300.00	1950.00

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

mysql> select ename,sal from emp order by sal desc limit 5;

ename	sal
KING	5000.00
FORD	3000.00
SCOTT	3000.00
JONES	2975.00
BLAKE	2850.00

10、取得薪水最高的第六名到第十名。

``
mysql> select ename,sal from emp order by sal desc limit 5,5;
``

ename	sal
CLARK	2450.00
ALLEN	1600.00
TURNER	1500.00
MILLER	1300.00
WARD	1250.00

11、取得最后入职的五名员工

mysql> select ename,hiredate from emp order by hiredate desc limit 5;

ename	hiredate
ADAMS	1987-05-23
SCOTT	1987-04-19
MILLER	1982-01-23
JAMES	1981-12-03
FORD	1981-12-03

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

第一步：找出每个员工的薪水的等级

mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

ename	sal	grade
SMITH	800.00	1
ALLEN	1600.00	3
WARD	1250.00	2
JONES	2975.00	4
MARTIN	1250.00	2
BLAKE	2850.00	4
CLARK	2450.00	4
SCOTT	3000.00	4
KING	5000.00	5
TURNER	1500.00	3
ADAMS	1100.00	1
JAMES	950.00	1
FORD	3000.00	4
MILLER	1300.00	2

第二步：在以上结果的基础上，按照grade进行分组，count计数

select
  s.grade,count(*)
from
  emp e
join
  salgrade s
on
  e.sal between s.losal and s.hisal
group by
  s.grade;

grade	count(*)
1	3
2	3
3	2
4	5
5	1

13、面试题(建议自己动手设计下)

S 学生表

sno(pk)	sname
1	张三
2	李四
3	王五
4	赵六

C 课程表

cno(pk)	cname	cteacher
1	linux	张老师
2	MySQL	李老师
3	Git	王老师
4	Java	赵老师
5	Redis	黎明

SC 学生选课表

【sno+cno是复合主键，主键只有一个，同时sno、cno又是外键。外键可以有两个】

sno	cno	scgrede
1	1	50
1	2	50
1	3	50
2	2	80
2	3	70
2	4	59
3	1	60
3	2	61
3	3	99
3	4	100
3	5	52
4	3	82
4	4	99
4	5	46

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

第一步：找出黎明老师所授课的编号

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

cno
5

第二步：通过学生选课表查询cno=上面结果的sno，这些sno是选黎明老师课程的学号

``
select sno from SC where cno = (select cno from C where cteacher = '黎明');
``

sno
3
4

第三步：在学生表中查询sno not in 上面结果的数据

select
  sname
from
  S  
where
    sno not in (select sno from SC where cno = (select cno from  C where cteacher = '黎明'));

sname
张三
李四

2、列出2门以上(含2门)不及格学生姓名及平均成绩

第一步：找出分数小于60并且按sno分组，计数大于2的

select
  sc.sno
from
  SC sc
where
  sc.scgrade < 60
group by
  sc.sno
having
  count(*) >=2;

sno
1

第二步：与学生表S进行连接

select
  sc.sno,s.sname
from
  SC sc
join
  S s
on
  sc.sno=s.sno
where
  sc.scgrade < 60
group by
    sc.sno,s.sname
having
  count(*) >=2;

sno	sname
1	张三

第三步：找出每个学生的平均成绩

select sno,avg(scgrade) as avggrade from SC group by sno;

sno	avggrade
1	50
2	69.66666666666667
3	74.4
4	75.66666666666667

第四步：第二步当作临时表t1和第三步当作临时表t2进行联合

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

select
  t1.sno,t1.sname,t2.avggrade
from
  (select
    sc.sno,s.sname
  from
    SC sc
  join
    S s
  on
    sc.sno=s.sno
  where
    sc.scgrade < 60
  group by
      sc.sno,s.sname
  having
    count(*) >=2) t1
join
  (select sno,avg(scgrade) as avggrade from SC group by sno) t2
on
  t1.sno=t2.sno;

sno	sname	avggrade
1	张三	50

3、即学过1号课又学过2号课所有学生的姓名

第一步：找出学过1号课程的学生

select sno from SC where cno=1;

sno
1
3

第二步：找出学过2号课程的学生

select sno from SC where cno=2;

sno
1
2
3

第三步：将第一步和第二部进行联合

select sno from SC where cno=1 and sno in(select sno from SC where cno=2);

sno
1
3

第四步：将上面结果和S表进行联合

select
  sc.sno,s.sname
from
  SC sc
join
  S s
on
  sc.sno=s.sno
where
  sc.cno=1 and sc.sno in(select sno from SC where cno=2);

sno	sname
1	张三
3	王五

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

表的自关联emp a<员工表> emp b <领导表>

select
  a.ename empname,b.ename leardername
from
  emp a
left join
  emp b
on
  a.mgr=b.empno;

  +---------+-------------+
| empname | leardername |
+---------+-------------+
| SMITH   | FORD        |
| ALLEN   | BLAKE       |
| WARD    | BLAKE       |
| JONES   | KING        |
| MARTIN  | BLAKE       |
| BLAKE   | KING        |
| CLARK   | KING        |
| SCOTT   | JONES       |
| TURNER  | BLAKE       |
| ADAMS   | SCOTT       |
| JAMES   | BLAKE       |
| FORD    | JONES       |
| MILLER  | CLARK       |
+---------+-------------+
13 rows in set (0.06 sec)

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

第一步：表的自关联emp a<员工表> emp b <领导表>找出所有员工

select
  a.empno '员工编号', a.ename '员工姓名',a.hiredate '员工入职日期',
  b.empno '领导编号',b.ename '领导姓名',b.hiredate '领导入职日期'
from
  emp a
join
  emp b
on
  a.mgr=b.empno
where
  a.hiredate<b.hiredate;

员工编号	员工姓名	员工入职日期	领导编号	领导姓名	领导入职日期
7369	SMITH	1980-12-17	7902	FORD	1981-12-03
7499	ALLEN	1981-02-20	7698	BLAKE	1981-05-01
7521	WARD	1981-02-22	7698	BLAKE	1981-05-01
7566	JONES	1981-04-02	7839	KING	1981-11-17
7698	BLAKE	1981-05-01	7839	KING	1981-11-17
7782	CLARK	1981-06-09	7839	KING	1981-11-17

第二步：与dept表进行关联

select
  a.empno '员工编号', a.ename '员工姓名',a.hiredate '员工入职日期',
  b.empno '领导编号',b.ename '领导姓名',b.hiredate '领导入职日期',
  d.dname '部门名称'
from
  emp a
join
  emp b
on
  a.mgr=b.empno
join
  dept d
on
  a.deptno=d.deptno
where
  a.hiredate<b.hiredate;

员工编号	员工姓名	员工入职日期	领导编号	领导姓名	领导入职日期	部门名称
7369	SMITH	1980-12-17	7902	FORD	1981-12-03	RESEARCH
7499	ALLEN	1981-02-20	7698	BLAKE	1981-05-01	SALES
7521	WARD	1981-02-22	7698	BLAKE	1981-05-01	SALES
7566	JONES	1981-04-02	7839	KING	1981-11-17	RESEARCH
7698	BLAKE	1981-05-01	7839	KING	1981-11-17	SALES
7782	CLARK	1981-06-09	7839	KING	1981-11-17	ACCOUNTING

16、列出部门名称和这些员工信息同时列出那些没有员工的部门

PS；使用表关联和右外连接emp e <员工表> dept d <部门表>

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

空间利用，不展示了。

17、列出至少有五个员工的部门详细信息

PS:分组可以使用多个字段联合起来。

select
  d.deptno,d.dname,d.loc,count(e.ename)
from
  emp e
join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno,d.dname,d.loc
having
  count(e.ename)>=5;

  +--------+----------+---------+----------------+
| deptno | dname    | loc     | count(e.ename) |
+--------+----------+---------+----------------+
|     20 | RESEARCH | DALLAS  |              5 |
|     30 | SALES    | CHICAGO |              6 |
+--------+----------+---------+----------------+
2 rows in set (0.07 sec)

18、列出薪金比“SMITH”多的所有员工

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

空间利用，不展示了。

19、列出所有“CLERK”(办事员)的姓名及其部门名称，部门人数

第一步：找出工作是“CLERK”所有员工

select ename from emp where job='CLERK';

+--------+
| ename  |
+--------+
| SMITH  |
| ADAMS  |
| JAMES  |
| MILLER |
+--------+
4 rows in set (0.00 sec)

第二步：进行表关联，得出部门名称

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

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| MILLER | ACCOUNTING |
+--------+------------+
4 rows in set (0.00 sec)

第三步：按部门编号分组，求每个部门人数

select deptno,count(*) as totalEmp from emp group by deptno;

+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)

第四步：将第二步和第三步(当作临时表t)进行关联

select
  e.ename,d.dname,t.totalEmp
from
  dept d
join
  emp e
on
  e.deptno=d.deptno
join
  (select deptno,count(*) as totalEmp from emp group by deptno)  t
on
  t.deptno=d.deptno
where
  e.job='CLERK';

  +--------+------------+----------+
| ename  | dname      | totalEmp |
+--------+------------+----------+
| SMITH  | RESEARCH   |        5 |
| ADAMS  | RESEARCH   |        5 |
| JAMES  | SALES      |        6 |
| MILLER | ACCOUNTING |        3 |
+--------+------------+----------+
4 rows in set (0.00 sec)

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

mysql> select job,min(sal),count(*) as totalEmp from emp group by job having min(sal)>1500;

+-----------+----------+----------+
| job       | min(sal) | totalEmp |
+-----------+----------+----------+
| ANALYST   |  3000.00 |        2 |
| MANAGER   |  2450.00 |        3 |
| PRESIDENT |  5000.00 |        1 |
+-----------+----------+----------+
3 rows in set (0.00 sec)

21、列出部门在“SALES”<销售部>工作的姓名，假定不知道销售部的部门的部门编号

第一步：查处部门编号(30)

select deptno from dept where dname='SALES';

第二步：表关联

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

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

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

emp a <员工表>

emp b <领导表>

dept d <部门表>

salgrade <工资等级表>

第一步：先不考虑公司的平均薪水，表自关联取出后面数据

第二步：在后面加where条件

第三步：在emp b<领导表>上加left。左边表全部显示，因为KING是大BOSS，不能没有他。

select
  a.ename empname,d.deptno,b.ename leardername,s.grade
from
  emp a
join
  dept d
on
  a.deptno=d.deptno
left join
  emp b
on
  a.mgr=b.empno
join
  salgrade s
on
  a.sal between s.losal and s.hisal
where
  a.sal>(select avg(sal) from emp);

  +---------+--------+-------------+-------+
  | empname | deptno | leardername | grade |
  +---------+--------+-------------+-------+
  | JONES   |     20 | KING        |     4 |
  | BLAKE   |     30 | KING        |     4 |
  | CLARK   |     10 | KING        |     4 |
  | SCOTT   |     20 | JONES       |     4 |
  | KING    |     10 | NULL        |     5 |
  | FORD    |     20 | JONES       |     4 |
  +---------+--------+-------------+-------+
  6 rows in set (0.00 sec)

23、列出所有与“SCOTT”从事相同工作的所有员工及部门名称

select
  e.ename,e.job,d.dname
from
  emp e
join
  dept d
on
  e.deptno=d.deptno
where
  e.job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';

  +-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+
1 row in set (0.00 sec)

24、列出薪金等于部门20中员工薪金的其他员工的姓名和薪金

第一步：找出30部门中所有员工的薪金，并且去重

第二步：使用in查找上面结果，并排出30部门的

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

Empty set (0.00 sec) 数据量不够。

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

第一步：找出30部门最大的薪金

mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+
1 row in set (0.00 sec)

第二步：员工表和部门表进行关联

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

  +-------+---------+------------+
  | ename | sal     | dname      |
  +-------+---------+------------+
  | JONES | 2975.00 | RESEARCH   |
  | SCOTT | 3000.00 | RESEARCH   |
  | KING  | 5000.00 | ACCOUNTING |
  | FORD  | 3000.00 | RESEARCH   |
  +-------+---------+------------+
  4 rows in set (0.00 sec)

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

第一步：使用右外连接，部门表全部显示。按领导编号分组(部门编号有些为空)，count()计数，求出每个部门的员工数量

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

  +--------+----------------+
| deptno | count(e.ename) |
+--------+----------------+
|     10 |              3 |
|     20 |              5 |
|     30 |              6 |
|     40 |              0 |
|     50 |              0 |
|     60 |              0 |
+--------+----------------+
6 rows in set (0.01 sec)

第二步：在以上查询结果的基础上，求平均工资。利用ifnull函数处理NULL。

select
  d.deptno '部门编号',count(e.ename) '员工数量',ifnull(avg(sal),0) '平均工资'
from
  emp e
right join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno;

  +--------------+--------------+--------------+
  | 部门编号     | 员工数量     | 平均工资     |
  +--------------+--------------+--------------+
  |           10 |            3 |  2916.666667 |
  |           20 |            5 |  2175.000000 |
  |           30 |            6 |  1566.666667 |
  |           40 |            0 |     0.000000 |
  |           50 |            0 |     0.000000 |
  |           60 |            0 |     0.000000 |
  +--------------+--------------+--------------+
  6 rows in set (0.00 sec)

第三步：在以上结果的基础上，求平均服务期限。使用ifnull()、to_days()、now()函数。参考avg(sal),只是把每个员工的服务期限放到avg()函数中

先求出每个员工的服务年限：

select (to_days(now()) - to_days(hiredate))/365 from emp;

select
  d.deptno '部门编号',
  count(e.ename) '员工数量',
  ifnull(avg(sal),0) '平均工资',
  ifnull(avg((to_days(now()) - to_days(hiredate))/365),0) '服务期限'
from
  emp e
right join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno;

  +--------------+--------------+--------------+--------------+
  |  部门编号     | 员工数量     | 平均工资        | 服务期限     |
  +--------------+--------------+--------------+--------------+
  |           10 |            3 |  2916.666667 |  35.93793333 |
  |           20 |            5 |  2175.000000 |  33.96494000 |
  |           30 |            6 |  1566.666667 |  36.23651667 |
  |           40 |            0 |     0.000000 |   0.00000000 |
  |           50 |            0 |     0.000000 |   0.00000000 |
  |           60 |            0 |     0.000000 |   0.00000000 |
  +--------------+--------------+--------------+--------------+
  6 rows in set (0.01 sec)

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

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

  不展示数据了

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

PS:需要使用右外连接，显示全部部门。按部门多个字段分组，并按员工姓名计数。

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

  +--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
|     50 | HR         | SY       |              0 |
|     60 | NULL       | MARKET   |              0 |
+--------+------------+----------+----------------+
6 rows in set (0.00 sec)

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

第一步：按工作岗位分组，使用min()函数求工资最小值

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

  +-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+
5 rows in set (0.00 sec)

第二步：将上面的查询结果当作临时表t，

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

  +--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
7 rows in set (0.00 sec)

30、列出各个部门MANAGER的最低薪金

PS:找出每个部门的MANAGER，并按部门编号分组

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

  +--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)

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

PS：年薪=(工资+佣金)×12，需要判断佣金是否为null

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

  +--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)

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

PS：表的自关联，条件是领导的薪水大于3000

select
  a.ename '员工姓名',a.sal '员工薪水', b.ename '领导姓名',b.sal '领导薪水'
from
  emp a
join
  emp b
on
  a.mgr=b.empno
where
  b.sal > 3000;

  +--------------+--------------+--------------+--------------+
| 员工姓名     | 员工薪水     | 领导姓名     | 领导薪水     |
+--------------+--------------+--------------+--------------+
| JONES        |      2975.00 | KING         |      5000.00 |
| BLAKE        |      2850.00 | KING         |      5000.00 |
| CLARK        |      2450.00 | KING         |      5000.00 |
+--------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)

33、求出部门名称中带“S”字符的部门员工的工资合计，部门人数

第一步：先找出所有部门的员工，使用右连接，显示全部部门

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

  共17条记录，数据不展示。

第二步：在以上结果的基础上，按部门编号分组，使用sum()函数求和，count()计数。<r>

select
   d.deptno '部门编号',d.dname '部门名称',
   ifnull(sum(e.sal),0) '工资合计',count(e.ename) '部门人数'
from    
  emp e
right join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno;

  +--------------+--------------+--------------+--------------+
| 部门编号     | 部门名称     | 工资合计     | 部门人数     |
+--------------+--------------+--------------+--------------+
|           10 | ACCOUNTING   |      8750.00 |            3 |
|           20 | RESEARCH     |     10875.00 |            5 |
|           30 | SALES        |      9400.00 |            6 |
|           40 | OPERATIONS   |         0.00 |            0 |
|           50 | HR           |         0.00 |            0 |
|           60 | NULL         |         0.00 |            0 |
+--------------+--------------+--------------+--------------+

第三步：使用like进行模糊查询，并按部门姓名分组

select
   d.dname '部门名称',
   ifnull(sum(e.sal),0) '工资合计',count(e.ename) '部门人数'
from    
  emp e
right join
  dept d
on
  e.deptno=d.deptno
where
  d.dname like '%S%'
group by
  d.dname;

  +--------------+--------------+--------------+
  | 部门名称     | 工资合计        | 部门人数     |
  +--------------+--------------+--------------+
  | OPERATIONS   |         0.00 |            0 |
  | RESEARCH     |     10875.00 |            5 |
  | SALES        |      9400.00 |            6 |
  +--------------+--------------+--------------+
  3 rows in set (0.00 sec)

34、给任职超过30年的员工加薪10%，

第一步：创建emp_bak

`create table emp_bak as select * from emp;
`

第二步：使用(to_days(now())-to_days(hiredate))/35 >30

mysql> update emp_bak set sal=sal*1.1 where (to_days(now()) - to_days(hiredate))/365 >30;

Query OK, 14 rows affected (0.34 sec)
Rows matched: 14  Changed: 14  Warnings: 0