6

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

完结,欢迎大家指出错误,补充另外写法。喜欢的话点个Star,或Fork到自己仓库。


guoxiaoxu
440 声望405 粉丝

越努力,越幸运