SQL語言詳解

• 2022 年 4 月 22 日
• 筆記
• 計算機基礎

SQL

1. 概述

Structured Query Language 結構化查詢語言

• 結構化查詢語言（Structured Query Language）簡稱SQL，是一種數據庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理數據。
• SQL語言使我們有能力訪問數據庫，並且SQL是一種ANSI（美國國家標準化組織）的標準計算機語言，各大數據庫 廠商在生產數據庫軟件的時候，幾乎都會去支持SQL的語法，以使得用戶在使用軟件時更加容易上手，以及在不同 廠商軟件之間進行切換時更加適應，因為大家的SQL語法都差不多。
• SQL語言功能很強，十分簡潔，核心功能只用了9個動詞。語法接近英語口語，所以，用戶很容易學習和使用。

SQL全稱叫做結構化查詢語言，結構化是什麼意思？

結構化數據

• 結構化數據也稱作行數據，是由二維表結構來邏輯表達和實現的數據，嚴格地遵循數據格式與長度規範，主要通過關係型數據庫進行存儲和管理。
• 與結構化數據相對的是不適於由數據庫二維表來表現的非結構化數據，包括所有格式的辦公文檔、XML、HTML、 各類報表、圖片和音頻、視頻信息等。
• 通俗來說，結構化數據會有嚴格的行列對齊，便於解讀與理解。

SQL語法分類

SQL主要語法分為兩個部分：數據定義語言 (DDL)和數據操縱語言 (DML) 。

• DDL語法使我們有能力創建或刪除表，以及數據庫、索引等各種對象，但是不涉及表中具體數據操作：

  • CREATE DATABASE – 創建新數據庫
  • CREATE TABLE – 創建新表

• DML語法是我們有能力針對表中的數據進行插入、更新、刪除、查詢操作：

  • SELECT – 從數據庫表中獲取數據
  • UPDATE – 更新數據庫表中的數據
  • DELETE – 從數據庫表中刪除數據
  • INSERT – 向數據庫表中插入數據

什麼是數據庫？什麼是數據庫管理系統？什麼是SQL？他們之間的關係是什麼？

• 數據庫

  • 英文單詞Database,簡稱DB。 按照一定格式存儲數據的一些文件的組合。顧名思義:存儲數據的倉庫，實際上就是一堆文件。這些文件中存儲了具有特定格式的數據。

• 數據庫管理系統

  • Database Management,簡稱DBMS。數據庫管理系統是專門用來管理數據庫中數據的，數據庫管理系統可以對數據庫當中的數據進行增刪改查

  • 常見的數據庫管理系統

    • MySQL、Oracle、 MS SqlServer、 DB2、sybase等…

• SQL

  • 結構化查詢語句

• 三者之間的關係?

  • 程序員需要學習SQL語句，程序員通過編寫SQL語句，然後DBMS負責執行SQL語句，最終來完成數據庫中數據的增刪改查操作。

  DBMS --執行--> SQL --操作--> DB
  

準備

此文學習需要用到此sql數據，建議先將數據導入到庫中

數據放到了末尾

表名區分大小寫問題

表的列名是不區分大小寫的

• win10 mysql不區分大小寫
• linux mysql區分大小寫
  • 讓MYSQL不區分表名大小寫的方法其實很簡單：
  • 用ROOT登錄，修改/etc/my.cnf
  • 在[mysqld]下加入一行：lower_case_table_names=1
  • 重新啟動數據庫即可

2. DDL

Data Definition Language 數據定義語言

• 凡是帶有create、drop、alter 的都是DDL

• DDL主要操作的是表的結構。不是表中的數據

• 這個增刪改和DML不同，這個主要是對錶結構進行操作

• 不同數據庫的DDL略微有些差異，此文只提及最基本的DDL操作。

create database

• create database用於創建新的數據庫

  CREATE DATABASE [IF NOT EXISTS] database_name
  

• 例子：創建數據庫itcast

  create database if not exists itcast;
  

use database

• 選擇特定的數據庫：切換當前會話使用哪一個數據庫進行操作

drop database

• 刪除數據庫

  DROP DATABASE [IF EXISTS] database_name;
  

create table

create table 表名(
	字段名1 數據類型,
	字段名2 數據類型，
	字段名3 數據類型
);

------------------------------
varchar : 可變長度的字符串，根據送進來的字符串的長度自動分配
   char : 定長字符串，分配固定長度的空間區存儲數據
    int \ bigint 長整型=long（java）
  float \ double
   data \ datetime 
   clob : 字符大對象 最多存儲4G的字符串，超過255字符的都需要使用clob （character large object）
   blob : 二進制大對象 專門存儲圖片、聲音、視頻等流媒體數據 （binary large object）

show

• Show相關的語句可以幫助用戶查詢相關信息。
• 比如我們最常使用的查詢當前數據庫下有哪些表 show tables.

--1、顯示所有數據庫
show databases;

--2、顯示當前數據庫所有表
show tables;
SHOW TABLES [IN database_name]; --指定某個數據庫

--3、查詢顯示一張表的元數據信息
desc table_name;

3. DQL

Data Query Language 數據查詢語句

(凡是帶有select關鍵字的都是查詢語句)

Select語法

• 表名和列名不區分大小寫
• 關鍵詞的順序不能變

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];

關鍵詞的執行順序（很重要）

// 執行順序
from -> where -> group by -> having -> select -> order by -> limit

基本使用

-- 查看錶的所有數據
select * from table; -- 效率低，可讀性差

-- 查看具體字段的數據
select field from table;

-- 查詢多個字段的數據
select field, field from table;

-- 給查詢的列起別名，as可以省略保留空格
-- 只是將顯示的查詢結果改變，實際不變
select dname, deptno as deptname from dept;

-- 別名有空格需要加單或者雙引號，建議使用單引號
select dname, deptno as 'dept name' from dept;

-- 字段可以使用數學運算
select dname, deptno * 10 as deptno from dept;

-- 字段為常數時，會導出一個列，行數與表的行數相等
select 'KingDom' as name from emp;
-- +---------+
-- |  name   |
-- +---------+
-- | KingDom |
-- | KingDom |
-- | KingDom |
--    ...
-- +---------+

-- 查看錶的結構 descripe
desc table;
-- 查看版本號
select version();
-- 查看此數據庫
select database();

where

• WHERE後面是一個布爾表達式（結果要麼為true，要麼為false），用於查詢過濾，當布爾表達式為true時，返回select後面expr表達式的結果，否則返回空
• 在WHERE表達式中，可以使用數據庫支持的任何函數和運算符，但聚合函數除外

-- = 判斷是否相等
select dname, deptno from dept where loc = 'dallas';

-- <> or != 判斷是否不等
select dname, deptno from dept where loc <> 'dallas';

-- <=, <, >, >=
select dname, deptno from dept where loc > 'dallas';
select dname, deptno, loc from dept where loc >= 'dallas';

-- between ... and ...
-- 等同於 >= and <=
select dname, deptno, loc from dept where loc between 'dallas' and 'dallas';
select dname, deptno, loc from dept where loc >= 'dallas' and <= 'dallas';

-- 在數據庫中null不能使用等號衡量，需要使用is null
select empno, ename, sal, comm from emp where comm is null;
select empno, ename, sal, comm from emp where comm is not null;

-- and / or
select empno, ename, sal, comm from emp where comm is not null and comm = 300;
select empno, ename, sal, comm from emp where sal = 1600 or comm = 300;
-- and和or同時出現 and優先級更高，同時使用需要加括號
select empno, ename, sal, comm from emp where sal > 1600 and (deptno = 10 or deptno = 20);
  
-- in 
select empno, ename, sal, job from emp where job in ('manager', 'salesman');
-- not取反
select empno, ename, sal, job from emp where job not in ('manager', 'salesman');

-- like
-- %:匹配任意多個字符
-- 下劃線_:任意一個字符
-- %和_都是特殊字符，代表字符時需要加上反斜線轉義
select ename from emp where ename like '%o%';
select ename from emp where ename like '%\_%';

數據處理函數

• 單行處理函數：一個輸入對應一個輸出

• 多行處理函數：多個輸入對應一個輸出

• 函數之間可以嵌套調用

單行處理函數

Lower\upper

• 轉換小寫\轉換大寫

select empno, ename, lower(job) from emp;
select empno, ename, lower(job) as job from emp;

substr(串，起始下標，取出長度)

• 取子串，起始下標從1開始

select empno, ename, substr(lower(job), 1, 3) as job from emp;

-- 不區分大小寫
select ename from emp where substr(ename, 1, 1) = 'S';
select ename from emp where substr(ename, 1, 1) = 's';

-- 首字母大寫，兩個字符串需要使用concat連接
select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) as ename from emp;

trim

• 去空格

select * from emp where ename = trim('  King');

round(數字, 保留幾位小數)

• 四捨五入

round(199.22, -1)
// 200, -1是保留十數位

rand()

• 產生0-1隨機數

rand()

ifnull()

• 判斷是否為null，是null的話可以將其值替換

select ename, (sal + ifnull(comm, 0)) * 10 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  8000.00 |
| ALLEN  | 19000.00 |
| WARD   | 17500.00 |
| JONES  | 29750.00 |
| MARTIN | 26500.00 |
| BLAKE  | 28500.00 |
		...
+--------+----------+

case..when..then..when..then..else..end

• 分支語句，有點像switch

select ename, job, sal, (case job when 'MANAGER' then sal * 1.1 when 'SALESMAN' then sal * 1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename  | job       | sal     | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
					...
+--------+-----------+---------+---------+

分組函數

多行處理函數

分組函數在使用的時候必須先進行分組，然後才能用。如果你沒有對數據進行分組，整張表默認為一組。

• 分組函數自動忽略null

• 分組函數不能只用使用在where子句中

  • 因為分組函數在使用的時候必須先分組之後能使用

  • where執行的時候還沒有分組，所以where後面不能出現分組函數

  • 執行順序

    from -> where -> group by -> select -> order by
    

分組函數

• 函數的最大特點是不管原始數據有多少行記錄，經過聚合操作只返回一條數據，這一條數據就是聚合的結果

min、max、avg、sum、count

-- 返回薪資最小值
select min(sal) from emp;
-- 返回薪資最大值
select max(sal) from emp;
-- 返回薪資的平均值
select avg(sal) from emp;
-- 返回薪資的總和
select sum(sal) from emp;
-- 返回領取薪資的人個數（不一定為行數，若存在null）
select count(sal) from emp;

select count(*) from emp; -- 14 統計行數
select count(comm) from emp; -- 4 統計除null的數據數

Group by

• GROUP BY語句用於結合分組函數，根據一個或多個列對結果集進行分組
• 如果沒有group by語法，則表中的所有行數據當成一組

-- 按照工作崗位對工資求平均
select job, avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

-- 可以對多個列分組
select deptno, job, max(sal) from emp group by deptno, job order by deptno;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+

注意：

• 在一條select語句中，如果有group by語句的話，select後面只能跟參加分組的字段和分組函數
  • 避免出現一個字段多個值的歧義
  • 參加分組的字段：因為分組的依據就是該字段，因為組內的值相同不存在歧義
  • 分組函數：分組函數對組內進行操作，是多進一出，也不存在歧義

-- 此語句會報錯，因為sal既參加分組的字段，也並未使用分組函數
select job, sal from emp group by job;

Having

• 在SQL中增加HAVING子句原因是，WHERE關鍵字無法與聚合函數一起使用
• HAVING子句可以讓我們篩選分組後的各組數據，並且可以在Having中使用聚合函數，因為此時where，group by 已經執行結束，結果集已經確定

-- 找出平均薪資大於2k的部門
where select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     10 | 2916.666667 |
+--------+-------------+

HAVING與WHERE區別

• having是在分組後對數據進行過濾
• where是在分組前對數據進行過濾
• having後面可以使用聚合函數
• where後面不可以使用聚合函數

order by

排序

• ORDER BY 語句用於根據指定的列對結果集進行排序
• ORDER BY 語句默認按照升序（ASC）對記錄進行排序。如果您希望按照降序對記錄進行排序，可以使用DESC關鍵字

select ename from emp order by ename;
-- 先按薪資排序，薪資相同看名字
select ename,sal from emp order by sal asc ename asc;
-- 按照第幾列排序，只做了解
select empno, ename, job from emp order 3;
-- desc降序
select empno, ename, job, sal from emp where sal between 1250 and 3000 order by sal desc;

limit

• LIMIT用於限制SELECT語句返回的行數
• LIMIT接受一個或兩個數字參數，這兩個參數都必須是非負整數常量。
• 第一個參數指定要返回的第一行的偏移量，第二個參數指定要返回的最大行數。當給出單個參數時，它代表最大行數，並且偏移量默認為0。

-- limit startIndex(可缺省從0), length 
select ename, sal from emp order by sal desc limit 2, 3;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

distinct

去重

-- 查看有哪些job
select distinct job from emp;

-- distinct只能出現在最前方，表示聯合去重
select distinct job, comm from emp;
+-----------+---------+
| job       | comm    |
+-----------+---------+
| CLERK     |    NULL |
| SALESMAN  |  300.00 |
| SALESMAN  |  500.00 |
| MANAGER   |    NULL |
| SALESMAN  | 1400.00 |
| ANALYST   |    NULL |
| PRESIDENT |    NULL |
| SALESMAN  |    0.00 |
+-----------+---------+

-- job的總數量
select count(distinct job) from emp;

4. Join關聯查詢

從一張表中單獨查詢，稱為單表查詢。emp表和dept表聯合起來查詢數據，從emp表中取員工名字，從dept表中取部門名字。這種跨表查詢，多張表聯合起來查詢數據，被稱為連接查詢。

語法

select
	...
from
	...
join
	...
on
	...

4.1 內連接

• 只有進行連接的兩個表中都存在與連接條件相匹配的數據才會被留下來

  

等值連接

-- 查詢每個人的部門名
select 
	e.ename, d.dname 
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno;

非等值連接

-- 查詢每個人的薪資等級
select 
	e.ename, e.sal, s.grade 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and 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 |
+--------+---------+-------+

4.2 外連接

會將主表沒有匹配上的例子也輸出出來

左外連接

• left join中文叫做是左外連接(Left Outer Join)或者左連接，其中outer可以省略，left outer join是早期的寫法。 left join的核心就在於left左。左指的是join關鍵字左邊的表，簡稱左表。
• 通俗解釋：join時以左表的全部數據為準，右邊與之關聯；左表數據全部返回，右表關聯上的顯示返回，關聯不上的顯示null返回

-- 查詢每個人的上級名
-- king沒有老闆，顯示為null
select e.ename, d.ename from emp e left join emp d on e.mgr = d.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

右外連接

• 同理
• right表示右邊的表是主表

select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

-- OPERATIONS 崗位沒有人，顯示為null

4.3 多表相連

-- a表與b、c表相連
select
	...
from
	a
join 
	b
on
	...
join
	c
on
	...

找出每個員工的部門名稱以及工資等級，要求顯示員工名、部門名、薪資、薪資等級?

select 
	e.ename name, d.dname bumen, e.sal sal, s.grade salgrade 
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal;
+--------+------------+---------+----------+
| name   | bumen      | sal     | salgrade |
+--------+------------+---------+----------+
| SMITH  | RESEARCH   |  800.00 |        1 |
| ALLEN  | SALES      | 1600.00 |        3 |
| WARD   | SALES      | 1250.00 |        2 |
| JONES  | RESEARCH   | 2975.00 |        4 |
| MARTIN | SALES      | 1250.00 |        2 |
| BLAKE  | SALES      | 2850.00 |        4 |
| CLARK  | ACCOUNTING | 2450.00 |        4 |
| SCOTT  | RESEARCH   | 3000.00 |        4 |
| KING   | ACCOUNTING | 5000.00 |        5 |
| TURNER | SALES      | 1500.00 |        3 |
| ADAMS  | RESEARCH   | 1100.00 |        1 |
| JAMES  | SALES      |  950.00 |        1 |
| FORD   | RESEARCH   | 3000.00 |        4 |
| MILLER | ACCOUNTING | 1300.00 |        2 |
+--------+------------+---------+----------+

5. 子查詢

select語句中嵌套select語句

select
	...(select)
from
	...(select)
where
	...(select)

示例

找出比最低工資高的員工

select ename, sal from emp where sal > (select min(sal) from emp);

找出每個工作崗位的平均工資的等級

select 
	s.job, s.avgsal, d.grade 
from 
	(select 
		job, avg(sal) avgsal 
	 from 
	 	emp 
	 group by job) s 
join 
	salgrade d 
on 
	s.avgsal between d.losal and d.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+

6. 總結

關鍵詞的書寫順序

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...

關鍵詞的執行順序

from -> where -> group by -> having -> select -> order by -> limit

7. 練習題

1. 取得每個部門最高薪水的人員名稱

   先將每個部門最高的薪水和部門號給取出來，然後再將其和原表進行內聯，找出結果

 select 
 	e.ename ename, e.sal sal, e.deptno 
 from 
 	emp e 
 join 
 	(select max(sal) maxsal, deptno from emp group by deptno) d 
 on 
 	e.sal = d.maxsal and e.deptno = d.deptno;
 	
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

2. 取得每個部門薪資比平均薪資高的人

   與前一個題類似，我們只需先找出平均工資，然後再對比就行

select 
	e.ename ename, e.sal sal, e.deptno 
from 
	emp e 
join 
	(select avg(sal) avgsal, deptno from emp group by deptno) d 
on 
	e.sal > d.avgsal and e.deptno = d.deptno;
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| FORD  | 3000.00 |     20 |
| SCOTT | 3000.00 |     20 |
| JONES | 2975.00 |     20 |
| BLAKE | 2850.00 |     30 |
| ALLEN | 1600.00 |     30 |
| KING  | 5000.00 |     10 |
+-------+---------+--------+

3. 取得部門中所有人的平均薪水等級

   先取出所有人的分組和平均薪資等級，再將其求取平均

select 
	deptno, avg(grade) 
from 
	(select 
     	e.deptno, s.grade 
     from 
     	emp e
     join 
     	salgrade s 
     on 
     	e.sal between s.losal and s.hisal) em 
group by 
	deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
|     20 |     2.8000 |
|     30 |     2.5000 |
|     10 |     3.6667 |
+--------+------------+

4. 不準使用函數（MAX）取得最高薪水

select 
	sal 
from 
	emp 
order by 
	sal desc 
limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

5. 取得平均薪水最高的部門的部門編號

select 
	deptno 
from 
	(select 
		deptno, avg(sal) avgsal 	
	from 
		emp 
	group by 
		deptno 
	order by 
		avgsal desc) em 
limit 1;
+--------+
| deptno |
+--------+
|     10 |
+--------+

6. 取得平均薪水最高的部門的部門名稱

select 
	d.dname 
from 
	(select 
		deptno, avg(sal) avgsal 	
	from 
		emp 
	group by 
		deptno 
	order by 
		avgsal desc) em 
join 
	dept d
on
	em.deptno = d.deptno
limit 1;
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+

7. 求平均薪水的等級最低的部門的部門名稱

select 
	d.dname
from
    (select 
        deptno, avg(grade) as avgg
    from 
        (select 
            e.deptno, s.grade 
         from 
            emp e
         join 
            salgrade s 
         on 
            e.sal between s.losal and s.hisal) em 
    group by 
        deptno
    order by
    	avgg asc
    limit 1) nw
join 
	dept d
on
	nw.deptno = d.deptno;
+-------+
| dname |
+-------+
| SALES |
+-------+

8. 取得比普通員工（員工代碼沒有在mgr字段上出現的）的最高薪水還要高的領導人姓名

   只需要找出員工的最高薪水就行，只要薪水比這個最高薪水高那肯定是領導

   需要去除null，不然無法篩選

select 
	ename 
from 
	emp 
where 
	sal > (select 
           		max(sal) 
           from 
           		emp 
           where 
           		empno not in (select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

9. 取出薪水最高的前五名員工

select 
	ename, sal 
from 
	emp 
order by 
	sal desc 
limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

10. 取得薪水最高的第六到第十名員工

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. 取得最後入職的5名員工

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. 取得每個薪水等級有多少員工

select grade,count(grade) from (select s.grade as grade from emp e join salgrade s on e.sal between s.losal and s.hisal) em group by grade;
// 等價，後者更簡潔
select s.grade, count(*) as grade from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+-------+-------+
| grade | grade |
+-------+-------+
|     1 |     3 |
|     3 |     2 |
|     2 |     3 |
|     4 |     5 |
|     5 |     1 |
+-------+-------+

14. 列出所有員工及領導的姓名

select 
	ee.ename, eb.ename 
from 
	emp ee 
left join 
	emp eb 
on 
	ee.mgr = eb.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

15. 列出受雇日期早於直接上級的所有員工的編號、姓名、部門名稱

select 
	e.empno, e.ename, d.dname 
from 
	emp e 
join 
	emp e2 
on 
	e.mgr = e2.empno and e.hiredate < e2.hiredate 
join 
	dept d 
on 
	e.deptno = d.deptno;
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+

8. 數據

CREATE DATABASE if not EXISTS bjpowernode;
USE bjpowernode;

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT (
    DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
);

CREATE TABLE EMP (   
    EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
);

CREATE TABLE SALGRADE ( 
    GRADE INT,
	LOSAL INT,
	HISAL INT 
);

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); 
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999);