MySQL常用命令，34道練習題...

Me_Kun 發布于2019-08-16 11:03 / 3279人閱讀

摘要：常用命令總結感謝的第一個，值得紀念哈。自動忽略第二步找出員工最高薪水的人第三步找出薪水大于即可補充類似于中的取得薪水最高的前五名員工取得薪水最高的第六名到第十名。

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


員工編號
員工姓名
員工入職日期
領導編號
領導姓名
領導入職日期



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


員工編號
員工姓名
員工入職日期
領導編號
領導姓名
領導入職日期
部門名稱



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()計數。
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到自己倉庫。

員工編號	員工姓名	員工入職日期	領導編號	領導姓名	領導入職日期
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

員工編號	員工姓名	員工入職日期	領導編號	領導姓名	領導入職日期	部門名稱
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

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/70969.html

類的加載機制 - 收藏集 - 掘金

摘要：是現在廣泛流行的代從開始學習系列之向提交代碼掘金讀完本文大概需要分鐘。為了進行高效的垃圾回收，虛擬機把堆內存劃分成新生代老年代和永久代中無永久代，使用實現三塊區域。 React Native 開源項目 - 仿美團客戶端 (Android、iOS 雙適配) - Android - 掘金推薦 React Native 學習好項目，仿照美團客戶端... 極簡 GitHub 上手教程 - 工具...

Gilbertat 2019-08-14 18:40 評論0 收藏0
5道 JavaScript 練習題

摘要：在深入理解系列你真懂嗎答案詳解中最后的看到的一些練習題，并嘗試自己做且記錄下來。接著在對變量進行大小判斷。很適合進階的來做練習。在深入理解JavaScript系列（20）：《你真懂JavaScript嗎？》答案詳解中最后的看到的一些練習題，并嘗試自己做且記錄下來。找出數字數組中最大的元素（使用Math.max函數） var arr = [61, 22, 31, 4, 5]; // ...

newtrek 2019-08-21 10:42 評論0 收藏0
精選50道Python面試題，快來看看你已經掌握了多少道吧

摘要：從存儲的字符串表示中檢索原始對象的過程稱為。這稱為命名空間。如果需要八進制或十六進制表示，請使用內置函數或。和有什么區別返回對象，而返回列表，并使用相同的內存，無論范圍大小是多少。它提供了靈活性，并允許開發人員為他們的項目使用正確的工具。 ...

zzir 2021-11-18 10:02 評論0 收藏0

發表評論

登陸后可評論

0條評論

Me_Kun

男|高級講師

我要關注我要私信

TA的文章

基于javaweb+jsp的機房設備管理系統

閱讀 1349·2021-11-25 09:43
當程序員被問到如何形容你的專業時，他的回答亮了摸摸頭

閱讀 1912·2021-11-12 10:36
主機ip一般是什么-主機ip地址有什么要求？

閱讀 6040·2021-09-22 15:05
浮動元素

閱讀 3491·2019-08-30 15:55
電競CSGO數據API接口 - 【戰隊數據列表】API調用示例代碼

閱讀 2023·2019-08-26 14:06
Webpack 4.X 從入門到精通 - loader（五）

閱讀 3652·2019-08-26 12:17
使用React、Electron、Dva、Webpack、Node.js、Websocket快速構建

閱讀 512·2019-08-23 17:55
#JavaScript# IE兼容

閱讀 2460·2019-08-23 16:23

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

MySQL常用命令，34道練習題...

相關文章

類的加載機制 - 收藏集 - 掘金

5道 JavaScript 練習題

精選50道Python面試題，快來看看你已經掌握了多少道吧

發表評論

0條評論

Me_Kun

男|高級講師

TA的文章

基于javaweb+jsp的機房設備管理系統

當程序員被問到如何形容你的專業時，他的回答亮了摸摸頭

主機ip一般是什么-主機ip地址有什么要求？

浮動元素

電競CSGO數據API接口 - 【戰隊數據列表】API調用示例代碼

Webpack 4.X 從入門到精通 - loader（五）

使用React、Electron、Dva、Webpack、Node.js、Websocket快速構建

#JavaScript# IE兼容

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

MySQL常用命令，34道練習題...

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！