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

最新推荐文章于 2023-07-28 20:29:18 发布
最新推荐文章于 2023-07-28 20:29:18 发布
阅读量518 收藏 1
点赞数 1
文章标签： 数据库 面试 java
原文链接：https://segmentfault.com/a/1190000013259974
版权

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;
deptnomaxsal
105000.00
203000.00
302850.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;
enamedeptnomaxsal
BLAKE302850.00
SCOTT203000.00
KING105000.00
FORD203000.00

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

  • 第一步：找出部门的平均薪水『按部门编号分组求平均薪水』
select deptno,avg(sal) as avgsal from emp group by deptno;
deptnoavgsal
102916.666667
202175.000000
301566.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;
enamesaldeptnoavgsal
ALLEN1600.00301566.666667
JONES2975.00202175.000000
BLAKE2850.00301566.666667
SCOTT3000.00202175.000000
KING5000.00102916.666667
FORD3000.00202175.000000

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

  • 第一步：取得部门中的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
deptnoavgsal
102916.666667
202175.000000
301566.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;
deptnoavgsalgrade
102916.6666674
202175.0000004
301566.6666673

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 ;
enamesaldeptnograde
MILLER1300.00102
KING5000.00105
CLARK2450.00104
ADAMS1100.00201
SCOTT3000.00204
FORD3000.00204
JONES2975.00204
SMITH800.00201
MARTIN1250.00302
ALLEN1600.00303
JAMES950.00301
BLAKE2850.00304
WARD1250.00302
TURNER1500.00303
  • 第二步：在以上基础上继续以部门编号分组，求平均薪水等级
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;
deptnoavggrade
103.6667
202.8000
302.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;
deptnoavgsal
102916.666667
202175.000000
301566.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);
deptnoavgsal
102916.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);
deptnoavgsal
102916.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);
dnameavgsal
ACCOUNTING2916.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;
dnameavgsalgrade
ACCOUNTING2916.6666674
RESEARCH2175.0000004
SALES1566.6666673

第二步：获得最高等级

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);
dnameavgsalgrade
ACCOUNTING2916.6666674
RESEARCH2175.0000004

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);
empnoenamejobmgrhiredatesalcommdeptno
7369SMITHCLERK79021980-12-17800.00NULL20
7499ALLENSALESMAN76981981-02-201600.00300.0030
7521WARDSALESMAN76981981-02-221250.00500.0030
7654MARTINSALESMAN76981981-09-281250.001400.0030
7844TURNERSALESMAN76981981-09-081500.000.0030
7876ADAMSCLERK77881987-05-231100.00NULL20
7900JAMESCLERK76981981-12-03950.00NULL30
7934MILLERCLERK77821982-01-231300.00NULL10

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

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));
enamesal
JONES2975.00
BLAKE2850.00
CLARK2450.00
SCOTT3000.00
KING5000.00
FORD3000.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;
enamesalnewsal
SMITH800.001200.00
ALLEN1600.00NULL
WARD1250.00NULL
JONES2975.003272.50
MARTIN1250.00NULL
BLAKE2850.003135.00
CLARK2450.002695.00
SCOTT3000.00NULL
KING5000.00NULL
TURNER1500.00NULL
ADAMS1100.001650.00
JAMES950.001425.00
FORD3000.00NULL
MILLER1300.001950.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;
enamesalnewsal
SMITH800.001200.00
ALLEN1600.001600.00
WARD1250.001250.00
JONES2975.003272.50
MARTIN1250.001250.00
BLAKE2850.003135.00
CLARK2450.002695.00
SCOTT3000.003000.00
KING5000.005000.00
TURNER1500.001500.00
ADAMS1100.001650.00
JAMES950.001425.00
FORD3000.003000.00
MILLER1300.001950.00

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

mysql> select ename,sal from emp order by sal desc limit 5;
enamesal
KING5000.00
FORD3000.00
SCOTT3000.00
JONES2975.00
BLAKE2850.00

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

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

enamesal
CLARK2450.00
ALLEN1600.00
TURNER1500.00
MILLER1300.00
WARD1250.00

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

mysql> select ename,hiredate from emp order by hiredate desc limit 5;
enamehiredate
ADAMS1987-05-23
SCOTT1987-04-19
MILLER1982-01-23
JAMES1981-12-03
FORD1981-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;
enamesalgrade
SMITH800.001
ALLEN1600.003
WARD1250.002
JONES2975.004
MARTIN1250.002
BLAKE2850.004
CLARK2450.004
SCOTT3000.004
KING5000.005
TURNER1500.003
ADAMS1100.001
JAMES950.001
FORD3000.004
MILLER1300.002

第二步：在以上结果的基础上，按照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;
gradecount(*)
13
23
32
45
51

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

S 学生表

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

C 课程表

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

SC 学生选课表

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

snocnoscgrede
1150
1250
1350
2280
2370
2459
3160
3261
3399
34100
3552
4382
4499
4546

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;
snosname
1张三

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

select sno,avg(scgrade) as avggrade from SC group by sno;
snoavggrade
150
269.66666666666667
374.4
475.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;
snosnameavggrade
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);
snosname
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;
员工编号员工姓名员工入职日期领导编号领导姓名领导入职日期
7369SMITH1980-12-177902FORD1981-12-03
7499ALLEN1981-02-207698BLAKE1981-05-01
7521WARD1981-02-227698BLAKE1981-05-01
7566JONES1981-04-027839KING1981-11-17
7698BLAKE1981-05-017839KING1981-11-17
7782CLARK1981-06-097839KING1981-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;
员工编号员工姓名员工入职日期领导编号领导姓名领导入职日期部门名称
7369SMITH1980-12-177902FORD1981-12-03RESEARCH
7499ALLEN1981-02-207698BLAKE1981-05-01SALES
7521WARD1981-02-227698BLAKE1981-05-01SALES
7566JONES1981-04-027839KING1981-11-17RESEARCH
7698BLAKE1981-05-017839KING1981-11-17SALES
7782CLARK1981-06-097839KING1981-11-17ACCOUNTING

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到自己仓库。

weixin_34329187
关注
MySQL练习题~45道
MYgirlHHH的博客
04-12 2175
创建表并添加数据 -- 经典SQL练习题 CREATE TABLE STUDENT8 (SNO VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL, SSEX VARCHAR(2) NOT NULL, SBIRTHDAY DATETIME, CLASS VARCHAR(5)); CREATE TABLE COURSE (CNO VARCHAR(5) NOT NULL, CNAME VARCHAR(10) NOT NULL, TNO VARCHAR(10) NOT
mysql常见练习题45题
qq_40169177的博客
10-01 2200
create database school; use school; #学生表 create table Student ( Sno varchar(20) not null COMMENT ‘人名’, Sname varchar(20) not null COMMENT ‘姓名’, Ssex varchar(20) not null COMMENT ‘性别’, Sbirthday datet...
mysql常用命令练习题及答案
12-22
mysql一些常用命令的练习题及答案，适合初学者。
mysql作业34_MySQL常用命令，34道练习题（持续更新中）。
weixin_42500426的博客
02-08 148
1、使用SHOW语句找出在服务器上当前存在什么数据库：mysql> SHOW DATABASES;2、创建一个数据库MYSQLDATAmysql> CREATE DATABASE MYSQLDATA;3、选择你所创建的数据库mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)4、查看现在的数据库中存在什么表mysql&gt...
MySQL(15) 学习笔记大全
郑清
09-22 1273
点此进入原文链接 Windows服务 -- 启动MySQL net start mysql -- 创建Windows服务 sc create mysql binPath= mysqld_bin_path(注意：等号与值之间有空格) 连接与断开服务器 mysql -h 地址 -P 端口 -u 用户名 -p 密码 SHOW PROCESSLIST -- 显示哪些线程正在运行 SH...
【SQL】常用MySQL面试题（一）
NeverMore的博客
03-25 2207
部分转自：https://www.cnblogs.com/frankielf0921/p/5930743.html 1、数据库事务的四个特性及含义 数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。 原子性:整个事务中的所有操作，要么...
php开发面试题---Mysql常用命令行大全
weixin_33834628的博客
06-11 139
php开发面试题---Mysql常用命令行大全 一、总结 一句话总结： 常见关键词：create，use，drop，insert，update，select，where ，from。inner join，delete等 还是很好记的，用才能记得住，凭 合理性 写就好 1、mysql 连接Mysql 常用命令？ mysql -u root -p：密码前必须没有空格，否则让你重新输入...
MySQL习题.rar
11-04
"MySQL习题.rar"可能包含一系列与MySQL相关的练习题，旨在帮助用户深入理解和掌握MySQL的核心概念和技术。 1. **SQL基础**: SQL是Structured Query Language的缩写，是用于管理关系数据库的标准语言。习题可能会...
mysql练习题.pdf
11-20
下面将根据提供的练习题内容，详细阐述相关知识点。 1. 创建数据库的命令为`CREATE DATABASE`，例如：`CREATE DATABASE my_database;` 这条命令会在MySQL服务器上创建一个新的数据库。 2. 数据库完整性是确保数据...
MySQL练习题.pdf
06-27
15. **MySQL监控工具**：MySQL Workbench是常用的MySQL数据库管理和监控工具，可以用来查看数据库状态、执行查询和管理配置。 16. **undo log回收**：被标记为删除且不再需要的undo log由purge thread负责回收。 ...
Mysql经典面试题答案
09-18
Mysql经典面试题答案，富含很多bat面试题，经典实用。
常用sql指令一般面试都可通过
01-26
SELECT INSERT UPDATE DELETE 常用彙總函數 常用字串函數 常用日期與時間函數 常用數學函數 常用系統函數
mysql练习题-sql_practice.pdf
03-12
在进行具体的IT知识点阐述之前，需要先明确一下，本文件的内容实际上并不是直接的知识点，而是有关于“mysql练习题-sql_practice.pdf”文档的简介。此简介中包含了练习题相关的数据库表描述，以及对每个表进行操作的...
mysql基础---->基本命令与13道练习
04-25 8822
一个初学者的mysql学习总结，图示实战过程，基本命令与13道练习...
mysql数据库命令练习题及答案
不会修电脑的程序猿
08-29 4376
1、查询emp中最高薪水人的名字 2、查询每个部门中的最高薪水人的名字和所在的部门编号 3、查询薪水在平均薪水之上的雇员的名字 4、查询雇员的名字和所在部门的名字 5、查询薪水在在本部门平均薪水之上的雇员的名字 6、查询每个员工的薪水的等级,员工的姓名 7、查询每个部门的平均薪水的等级,部门的编号 8、查询雇员的名字，所在部门的名字，工资的等级 9、查询雇员的名字和其经理的名字 ...
MySQL常用的面试题
程序猿开发日志【学习永无止境】
04-22 2143
1、如何登陆mysql数据库 MySQL -u username -p 2、如何开启/关闭mysql服务 service mysql start/stop 3、查看mysql的状态 service mysql status 4、如何显示数所有数据库 show databases 5、如何获取表内所有字段对象的名称和
MYSQL常见面试题汇总
一步一个脚印，踏实努力，向着目标前进
07-28 3731
INNER JOIN只返回两个表中匹配的行，而LEFT JOIN返回左表中的所有行和匹配的右表中的行。如何使用INNER JOIN和LEFT JOIN区别连接多个表？其中，offset表示偏移量，count表示每页的数量。如何使用UNION操作合并多个查询结果？
mysql数据库练习题50道
最新发布
09-13
很抱歉，但我无法直接提供整个MySQL数据库练习...总结起来，解决MySQL数据库练习题的关键是熟悉SQL语法和常用命令，并根据具体的问题来编写合适的查询语句。希望这些指导对您有所帮助。如果您有其他问题，请随时提问。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值