MySQL常用命令，34道練習(xí)題...

Me_Kun 發(fā)布于2019-08-16 11:03 / 3278人閱讀

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

MySql常用命令總結(jié)

PS：感謝Dean Xu的第一個(gè)Star，值得紀(jì)念哈。

SQL腳本下載地址：歡迎star

1、使用SHOW語句找出在服務(wù)器上當(dāng)前存在什么數(shù)據(jù)庫：

mysql> SHOW DATABASES;

2、創(chuàng)建一個(gè)數(shù)據(jù)庫MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3、選擇你所創(chuàng)建的數(shù)據(jù)庫

mysql> USE MYSQLDATA; (按回車鍵出現(xiàn)Database changed 時(shí)說明操作成功！)

4、查看現(xiàn)在的數(shù)據(jù)庫中存在什么表

mysql> SHOW TABLES;

5、創(chuàng)建一個(gè)數(shù)據(jù)庫表

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

6、顯示表的結(jié)構(gòu)：

mysql> DESCRIBE MYTABLE;

7、往表中加入記錄

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

8、用文本方式將數(shù)據(jù)裝入數(shù)據(jù)庫表中（例如D:/mysql.txt）

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

9、導(dǎo)入.sql文件命令（例如D:/mysql.sql）

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

10、刪除表

mysql>drop TABLE MYTABLE;

11、清空表

mysql>delete from MYTABLE;

12、更新表中數(shù)據(jù)

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

匿名帳戶刪除、 root帳戶設(shè)置密碼:

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”;

全局管理權(quán)限：

FILE: 在MySQL服務(wù)器上讀寫文件。

PROCESS: 顯示或殺死屬于其它用戶的服務(wù)線程。

RELOAD: 重載訪問控制表，刷新日志等。

SHUTDOWN: 關(guān)閉MySQL服務(wù)。

數(shù)據(jù)庫/數(shù)據(jù)表/數(shù)據(jù)列權(quán)限：

ALTER: 修改已存在的數(shù)據(jù)表(例如增加/刪除列)和索引。

CREATE: 建立新的數(shù)據(jù)庫或數(shù)據(jù)表。

DELETE: 刪除表的記錄。

DROP: 刪除數(shù)據(jù)表或數(shù)據(jù)庫。

INDEX: 建立或刪除索引。

INSERT: 增加表的記錄。

SELECT: 顯示/搜索表的記錄。

UPDATE: 修改表中已存在的記錄。

特別的權(quán)限：

ALL: 允許做任何事(和root一樣)。

USAGE: 只允許登錄–其它什么也不允許做。

MySQL-Practice-Questions 1、取得每個(gè)部門最高薪水的人員名稱

第一步：取得每個(gè)部門最高薪水『按照部門分組求最大值』

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

deptno	maxsal
10	5000.00
20	3000.00
30	2850.00

第二步：將上面的查詢結(jié)果當(dāng)作臨時(shí)表t，t表和emp e表進(jìn)行連接

條件：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

第二步：將上面的查詢結(jié)果當(dāng)作臨時(shí)表t，與emp e表進(jìn)行連接

條件：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

第二部：將上面的查詢結(jié)果當(dāng)作臨時(shí)表t，t表和salgrade s表進(jìn)行關(guān)聯(lián)

條件：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

第一步：每個(gè)員工的薪水等級(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

第二步：在以上基礎(chǔ)上繼續(xù)以部門編號分組，求平均薪水等級

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、不用組函數(shù)(MAX),取得最高薪水(給出兩種解決方案)

方案一：按照薪水降序排，取得第一個(gè)

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、取得平均薪水最高的部門的編號(至少給出兩種解決方案)

第一種方案：平均薪水降序排取第一個(gè)

第一步：取得每個(gè)部門的平均薪水

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

第三步：將第一步和第二步結(jié)合

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函數(shù)

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、求平均薪水的等級最高的部門的部門名稱

第一步：求各個(gè)部門平均薪水的等級

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;

第三步：將第一步和第二步聯(lián)合

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、取得比普通員工的最高薪水還要高的領(lǐng)導(dǎo)人姓名

第一步：取得普通員工

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

以上語句無法查村到結(jié)果，因?yàn)閚ot 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

補(bǔ)充：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、取得每個(gè)薪水等級有多少員工

第一步：找出每個(gè)員工的薪水的等級

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

第二步：在以上結(jié)果的基礎(chǔ)上，按照grade進(jìn)行分組，count計(jì)數(shù)

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、面試題(建議自己動手設(shè)計(jì)下)

S 學(xué)生表

sno(pk)	sname
1	張三
2	李四
3	王五
4	趙六

C 課程表

cno(pk)	cname	cteacher
1	linux	張老師
2	MySQL	李老師
3	Git	王老師
4	Java	趙老師
5	Redis	黎明

SC 學(xué)生選課表

【sno+cno是復(fù)合主鍵，主鍵只有一個(gè)，同時(shí)sno、cno又是外鍵。外鍵可以有兩個(gè)】

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、找出沒選過“黎明”老師的所有學(xué)生姓名

第一步：找出黎明老師所授課的編號

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

cno
5

第二步：通過學(xué)生選課表查詢cno=上面結(jié)果的sno，這些sno是選黎明老師課程的學(xué)號

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

sno
3
4

第三步：在學(xué)生表中查詢sno not in 上面結(jié)果的數(shù)據(jù)

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

sname
張三
李四

2、列出2門以上(含2門)不及格學(xué)生姓名及平均成績

第一步：找出分?jǐn)?shù)小于60并且按sno分組，計(jì)數(shù)大于2的

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

sno
1

第二步：與學(xué)生表S進(jìn)行連接

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	張三

第三步：找出每個(gè)學(xué)生的平均成績

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

sno	avggrade
1	50
2	69.66666666666667
3	74.4
4	75.66666666666667

第四步：第二步當(dāng)作臨時(shí)表t1和第三步當(dāng)作臨時(shí)表t2進(jìn)行聯(lián)合

`
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、即學(xué)過1號課又學(xué)過2號課所有學(xué)生的姓名

第一步：找出學(xué)過1號課程的學(xué)生

select sno from SC where cno=1;

sno
1
3

第二步：找出學(xué)過2號課程的學(xué)生

select sno from SC where cno=2;

sno
1
2
3

第三步：將第一步和第二部進(jìn)行聯(lián)合

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

sno
1
3

第四步：將上面結(jié)果和S表進(jìn)行聯(lián)合

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、列出所有員工及領(lǐng)導(dǎo)名字

表的自關(guān)聯(lián)emp a<員工表> emp b <領(lǐng)導(dǎo)表>

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、列出受雇日期早于其直接上級領(lǐng)導(dǎo)的所有員工編號，姓名、部門名稱

第一步：表的自關(guān)聯(lián)emp a<員工表> emp b <領(lǐng)導(dǎo)表>找出所有員工

select
  a.empno "員工編號", a.ename "員工姓名",a.hiredate "員工入職日期",
  b.empno "領(lǐng)導(dǎo)編號",b.ename "領(lǐng)導(dǎo)姓名",b.hiredate "領(lǐng)導(dǎo)入職日期"
from
  emp a
join
  emp b
on
  a.mgr=b.empno
where
  a.hiredate


員工編號
員工姓名
員工入職日期
領(lǐng)導(dǎo)編號
領(lǐng)導(dǎo)姓名
領(lǐng)導(dǎo)入職日期



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表進(jìn)行關(guān)聯(lián)
select
  a.empno "員工編號", a.ename "員工姓名",a.hiredate "員工入職日期",
  b.empno "領(lǐng)導(dǎo)編號",b.ename "領(lǐng)導(dǎo)姓名",b.hiredate "領(lǐng)導(dǎo)入職日期",
  d.dname "部門名稱"
from
  emp a
join
  emp b
on
  a.mgr=b.empno
join
  dept d
on
  a.deptno=d.deptno
where
  a.hiredate


員工編號
員工姓名
員工入職日期
領(lǐng)導(dǎo)編號
領(lǐng)導(dǎo)姓名
領(lǐng)導(dǎo)入職日期
部門名稱



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、列出部門名稱和這些員工信息同時(shí)列出那些沒有員工的部門
PS；使用表關(guān)聯(lián)和右外連接emp e <員工表> dept d <部門表>
select
  e.*,d.dname
from
  emp e
right join
  dept d
on
  e.deptno=d.deptno;
空間利用，不展示了。
17、列出至少有五個(gè)員工的部門詳細(xì)信息
PS:分組可以使用多個(gè)字段聯(lián)合起來。
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”(辦事員)的姓名及其部門名稱，部門人數(shù)
第一步：找出工作是“CLERK”所有員工
select ename from emp where job="CLERK";

+--------+
| ename  |
+--------+
| SMITH  |
| ADAMS  |
| JAMES  |
| MILLER |
+--------+
4 rows in set (0.00 sec)
第二步：進(jìn)行表關(guān)聯(lián)，得出部門名稱
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)

第三步：按部門編號分組，求每個(gè)部門人數(shù)
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)
第四步： 將第二步和第三步(當(dāng)作臨時(shí)表t)進(jìn)行關(guān)聯(lián)
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的各種工作及從事此工作的全部雇員人數(shù)
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";
第二步：表關(guān)聯(lián)
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、列出薪金高于公司平均薪金的所有員工、所在的部門、上級領(lǐng)導(dǎo)、雇員的工資等級
emp a <員工表> 

emp b <領(lǐng)導(dǎo)表> 

dept d <部門表> 

salgrade <工資等級表> 

第一步：先不考慮公司的平均薪水，表自關(guān)聯(lián)取出后面數(shù)據(jù) 

第二步：在后面加where條件

第三步：在emp b<領(lǐng)導(dǎo)表>上加left。左邊表全部顯示，因?yàn)镵ING是大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查找上面結(jié)果，并排出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) 數(shù)據(jù)量不夠。
25、列出薪金高于在30部門工作的所有員工的薪金的員工姓名和薪金，部門名稱
第一步：找出30部門最大的薪金

mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+
1 row in set (0.00 sec)
第二步：員工表和部門表進(jìn)行關(guān)聯(lián)
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、列出每個(gè)部門工作的員工數(shù)量，平均工資、平均服務(wù)期限
第一步：使用右外連接，部門表全部顯示。按領(lǐng)導(dǎo)編號分組(部門編號有些為空)，count()計(jì)數(shù)，求出每個(gè)部門的員工數(shù)量
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)
第二步：在以上查詢結(jié)果的基礎(chǔ)上，求平均工資。利用ifnull函數(shù)處理NULL。
select
  d.deptno "部門編號",count(e.ename) "員工數(shù)量",ifnull(avg(sal),0) "平均工資"
from
  emp e
right join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno;

  +--------------+--------------+--------------+
  | 部門編號     | 員工數(shù)量     | 平均工資     |
  +--------------+--------------+--------------+
  |           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)
第三步：在以上結(jié)果的基礎(chǔ)上，求平均服務(wù)期限。使用ifnull()、to_days()、now()函數(shù)。 參考avg(sal),只是把每個(gè)員工的服務(wù)期限放到avg()函數(shù)中

先求出每個(gè)員工的服務(wù)年限：
select (to_days(now()) - to_days(hiredate))/365 from emp;
select
  d.deptno "部門編號",
  count(e.ename) "員工數(shù)量",
  ifnull(avg(sal),0) "平均工資",
  ifnull(avg((to_days(now()) - to_days(hiredate))/365),0) "服務(wù)期限"
from
  emp e
right join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno;

  +--------------+--------------+--------------+--------------+
  |  部門編號     | 員工數(shù)量     | 平均工資        | 服務(wù)期限     |
  +--------------+--------------+--------------+--------------+
  |           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;

  不展示數(shù)據(jù)了
28、列出所有部門的詳細(xì)信息和人數(shù)
PS:需要使用右外連接，顯示全部部門。按部門多個(gè)字段分組，并按員工姓名計(jì)數(shù)。
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()函數(shù)求工資最小值
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)
第二步：將上面的查詢結(jié)果當(dāng)作臨時(shí)表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、列出各個(gè)部門MANAGER的最低薪金
PS:找出每個(gè)部門的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、求出員工領(lǐng)導(dǎo)的薪水超過3000的員工姓名和領(lǐng)導(dǎo)名稱
PS：表的自關(guān)聯(lián)，條件是領(lǐng)導(dǎo)的薪水大于3000
select
  a.ename "員工姓名",a.sal "員工薪水", b.ename "領(lǐng)導(dǎo)姓名",b.sal "領(lǐng)導(dǎo)薪水"
from
  emp a
join
  emp b
on
  a.mgr=b.empno
where
  b.sal > 3000;

  +--------------+--------------+--------------+--------------+
| 員工姓名     | 員工薪水     | 領(lǐng)導(dǎo)姓名     | 領(lǐng)導(dǎo)薪水     |
+--------------+--------------+--------------+--------------+
| 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”字符的部門員工的工資合計(jì)，部門人數(shù)
第一步：先找出所有部門的員工，使用右連接，顯示全部部門
select
  e.*,d.*
from    
  emp e
right join
  dept d
on
  e.deptno=d.deptno;

  共17條記錄，數(shù)據(jù)不展示。
第二步：在以上結(jié)果的基礎(chǔ)上，按部門編號分組，使用sum()函數(shù)求和，count()計(jì)數(shù)。
select
   d.deptno "部門編號",d.dname "部門名稱",
   ifnull(sum(e.sal),0) "工資合計(jì)",count(e.ename) "部門人數(shù)"
from    
  emp e
right join
  dept d
on
  e.deptno=d.deptno
group by
  d.deptno;

  +--------------+--------------+--------------+--------------+
| 部門編號     | 部門名稱     | 工資合計(jì)     | 部門人數(shù)     |
+--------------+--------------+--------------+--------------+
|           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進(jìn)行模糊查詢，并按部門姓名分組
select
   d.dname "部門名稱",
   ifnull(sum(e.sal),0) "工資合計(jì)",count(e.ename) "部門人數(shù)"
from    
  emp e
right join
  dept d
on
  e.deptno=d.deptno
where
  d.dname like "%S%"
group by
  d.dname;

  +--------------+--------------+--------------+
  | 部門名稱     | 工資合計(jì)        | 部門人數(shù)     |
  +--------------+--------------+--------------+
  | OPERATIONS   |         0.00 |            0 |
  | RESEARCH     |     10875.00 |            5 |
  | SALES        |      9400.00 |            6 |
  +--------------+--------------+--------------+
  3 rows in set (0.00 sec)
34、給任職超過30年的員工加薪10%，
第一步：創(chuàng)建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
完結(jié)，歡迎大家指出錯誤，補(bǔ)充另外寫法。喜歡的話點(diǎn)個(gè)Star，或Fork到自己倉庫。

員工編號	員工姓名	員工入職日期	領(lǐng)導(dǎo)編號	領(lǐng)導(dǎo)姓名	領(lǐng)導(dǎo)入職日期
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

員工編號	員工姓名	員工入職日期	領(lǐng)導(dǎo)編號	領(lǐng)導(dǎo)姓名	領(lǐng)導(dǎo)入職日期	部門名稱
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

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/70969.html

發(fā)表評論

登陸后可評論

0條評論

Me_Kun

男|高級講師

我要關(guān)注我要私信

TA的文章

基于javaweb+jsp的機(jī)房設(shè)備管理系統(tǒng)

閱讀 1347·2021-11-25 09:43
當(dāng)程序員被問到如何形容你的專業(yè)時(shí)，他的回答亮了摸摸頭

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

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

閱讀 3490·2019-08-30 15:55
電競CSGO數(shù)據(jù)API接口 - 【戰(zhàn)隊(duì)數(shù)據(jù)列表】API調(diào)用示例代碼

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

閱讀 3651·2019-08-26 12:17
使用React、Electron、Dva、Webpack、Node.js、Websocket快速構(gòu)建

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

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

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

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

MySQL常用命令，34道練習(xí)題...

相關(guān)文章

類的加載機(jī)制 - 收藏集 - 掘金

5道 JavaScript 練習(xí)題

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

發(fā)表評論

0條評論

Me_Kun

男|高級講師

TA的文章

基于javaweb+jsp的機(jī)房設(shè)備管理系統(tǒng)

當(dāng)程序員被問到如何形容你的專業(yè)時(shí)，他的回答亮了摸摸頭

主機(jī)ip一般是什么-主機(jī)ip地址有什么要求？

浮動元素

電競CSGO數(shù)據(jù)API接口 - 【戰(zhàn)隊(duì)數(shù)據(jù)列表】API調(diào)用示例代碼

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

使用React、Electron、Dva、Webpack、Node.js、Websocket快速構(gòu)建

#JavaScript# IE兼容

最新活動

資訊專欄INFORMATION COLUMN

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

MySQL常用命令，34道練習(xí)題...

相關(guān)文章

發(fā)表評論

0條評論

男|高級講師

TA的文章

最新活動

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