尚矽谷MySQL基礎學習筆記_ZenDei技術網路在線

資料庫MySQL學習筆記 [TOC] 寫在前面學習鏈接： "資料庫 MySQL 視頻教程全集" MySQL引入資料庫的好處 1. 持久化數據到本地 2. 可以實現結構化查詢，方便管理資料庫的相關概念 DB：資料庫（database）：存儲數據的“倉庫”，它保存了一系列有組織的數據。 DBMS： ...

寫在前面
MySQL引入
DQL（Data Query Language）數據查詢語言
DML（Data Manipulation Language）數據操作語言
DDL（Data Definition Language）數據定義語言
TCL（Transaction Control Language）事務控制語言
視圖
變數
存儲過程和函數
- 1. 存儲過程
- 2. 函數
流程式控制制結構

資料庫MySQL學習筆記

寫在前面

學習鏈接：資料庫 MySQL 視頻教程全集

MySQL引入

資料庫的好處

持久化數據到本地
可以實現結構化查詢，方便管理

資料庫的相關概念

DB：資料庫（database）：存儲數據的“倉庫”，它保存了一系列有組織的數據。
DBMS：資料庫管理系統（Database Management System）。資料庫是通過DBMS創建和操作的容器。
SQL：結構化查詢語言（Structure Query Language），專門用來與資料庫通信的語言。
- SQL優點：
- 不是某個特定資料庫供應商專有的語是言，幾乎所有DBMS都支持SQL
- 簡單易學
- 實際上強有力的語言，靈活使用可以進行非常複雜和高級的資料庫操作

資料庫存儲數據的特點

將數據放到表中，表再放到庫中
一個資料庫中可以有多個表，每個表都有一個的名字，用來標識自己。表名具有唯一性。
表具有一些特性，這些特性定義了數據在表中如何存儲，類似java中 “類”的設計。
表由列組成，我們也稱為欄位。所有表都是由一個或多個列
組成的，每一列類似java 中的“屬性” 。
表中的數據是按行存儲的，每一行類似於java中的“對象”。
DBMS分為兩類：
- 基於共用文件系統的DBMS（ACCESS）
- 基於客戶機——伺服器的DBMS（MySQL、Oracle、SqlServer）

MySQL服務的啟動和停止

停止服務：net stop mysql
開啟服務：net start mysql

MySQL服務端的登錄和退出

登錄：mysql 【-h localhost -P 3306】（本機可省略） -u root -p（可以直接寫密碼，不能有空格）
- -h：主機名
- -P：埠號
- -u：用戶名
- -p：密碼
退出：exit
查看mysql資料庫的版本：
- select version();（mysql命令）
- mysql –version（dos命令）

MySQL的常用命令

查看當前所有的資料庫：show databases;
打開指定的庫：use 庫名
查看當前的所有表：show tables;
查看其他庫的所有表：show tables from 庫名;
創建表：

create table 表名(

列名列類型,

列名列類型,

…

);
查看表結構：desc 表名;

MySQL語法規範

不區分大小寫，建議關鍵字大寫，表名、列名小寫
每句話用;或\g結尾
每條命令根據需要，各子句一般分行寫，關鍵字不能縮寫也不能分行
註釋
- 單行註釋：#註釋文字
- 單行註釋：-- 註釋文字（要有空格）
- 多行註釋：/* 註釋文字 */

DQL（Data Query Language）數據查詢語言

1. 基礎查詢

語法：

select 查詢列表

from 表名;
特點：
- 查詢列表可以是：表中的欄位、常量、表達式、函數
- 查詢的結果是一個虛擬的表格
註意：在進行查詢操作之前要指定所有的庫：use myemployees;
查詢表中的單個欄位：select last_name from employees;
查詢表中的多個欄位：select last_name, salary, email from employees;
查詢表中的所有欄位：select * from employees;
按F12進行格式化
著重號`用來區分是否是關鍵字或者欄位
選中語句進行執行或F9
查詢常量值：

select 100;

select ‘john’;
查詢表達式：select 100*98；
查詢函數：select version();
起別名：
- 便於理解
- 如果查詢的欄位有重名的情況，使用別名可以區分開來
方式1：

select 100%98 as 結果;

select last_name as 姓, first_name as 名 from employees;

方式2：

select last_name 姓, first_name 名 from employees;

如果別名有特殊符號要加雙引號：

select salary as "out put" from employees;
去重：

查詢員工表中涉及到的所有部門編號：select distinct department_id from employees;
+號的作用：
- 兩個操作數為數值型，則做加法運算
- 只要其中一方為字元型，試圖將字元型數值轉換成數值型，如果轉換成功，則繼續做加法運算；如果轉換失敗，則將字元型數值轉換成0
- 只要其中一方為null，則結果肯定為null
使用concat連接字元串：

查詢員工的名和姓連接成一個欄位，並顯示為姓名：select concat(last_name,first_name) as 姓名 from employees;
ifnull函數檢測是否為null，如果為null，則返回指定的值，否則返回原本的值：
```
select ifnull(commission_pct, 0) as 獎金率, commission_pct from employees;
```
isnull函數判斷某欄位或表達式是否為null，如果是，則返回1，否則返回0

2. 條件查詢

語法：select 查詢列表 from 表明 where 篩選條件;
分類：
- 按條件表達式篩選：
  - 條件運算符：> < = != <> >= <=
- 按邏輯表達式篩選：
  - 主要作用：用於連接條件表達式
  - 邏輯運算符：&& || ! and or not
- 模糊查詢
  
  like
  
  between and
  
  in
  
  is null
按條件表達式篩選：
- 查詢工資>12000的員工信息：select * from employees where salary>12000;
- 查詢部門編號不等於90號的員工名和部門編號：select last_name, department_id from employees where department_id != 90;
按邏輯表達式篩選：
- 查詢工資在10000到20000之間的員工名、工資以及獎金：select last_name, salary, commission_pct from employees where salary >= 10000 and salary <= 20000;
- 查詢部門編號不是在90到110之間，或者工資高於15000的員工信息：select * from employees where department_id < 90 or department_id > 110 or salary > 15000;
模糊查詢
- like
  - 一般和通配符搭配使用，可以判斷字元型數值或數值型
  - 通配符：
    - % 任意多個字元，包含0個字元
    - _ 任意單個字元
  - 查詢員工名中包含字元a的員工信息：
```
SELECT * FROM employees WHERE last_name LIKE '%a%';
```
  - 查詢員工名中第三個字元為e，第五個字元為a的員工名和工資：
```
SELECT last_name, salary FROM employees WHERE last_name LIKE '__n_l%';
```
  - 查詢員工名中第二個字元為_的員工名：
```
SELECT last_name FROM employees WHERE last_name LIKE '_\_ %';
```
  - 指定轉義字元：
```
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
```
- between and
  - 使用between and可以提高語句的簡潔度；
  - 包含臨界值；
  - 兩個臨界值不能替換順序；
  - 查詢員工編號在100到120之間的員工信息：
```
SELECT * FROM employees WHERE employee_id >= 100 AND employee_id <= 120;
```
```
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
```
- in
  - 含義：判斷某欄位的值是否屬於in列表中的某一項
  - 使用in提高語句簡潔度
  - in列表的值類型必須一致或相容
  - in相當於等於，所以不支持通配符（like才支持）
  - 查詢員工的工種編號是 IT_PROG、AD_VP、AD_PRES中的一個員工名和工種編號：
```
SELECT last_name, job_id FROM employees WHERE job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES';
```
```
SELECT last_name, job_id FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');
```
- is null
  - 用於判斷null值
  - =或者<>不能用於判斷null值
  - 查詢沒有獎金的員工名和獎金率：
```
SELECT
  last_name,
  commission_pct
FROM
  employees
WHERE
  commission_pct IS NULL;
```
  - 查詢有獎金的：
```
SELECT
  last_name,
  commission_pct
FROM
  employees
WHERE
  commission_pct IS NOT NULL;
```
- 安全等於 <=>
  - is null：僅僅可以判斷null值，可讀性較高
  - <=>：既可以判斷null值，又可以判斷普通的數值，可讀性較低
測試題
- 查詢沒有獎金，且工資小於18000的salary, last_name：
```
SELECT 
  salary,
  last_name 
FROM
  employees 
WHERE commission_pct IS NULL 
  AND salary < 18000;
```
- 查詢employees表中，job_id不為‘IT’或者工資為12000的員工信息：
```
SELECT 
  * 
FROM
  employees 
WHERE job_id <> 'IT' 
  OR salary = 12000 ;
```
- 查看部門表的結構：
```
DESC departments;
```
- 查詢部門表中涉及到了哪些位置編號：
```
SELECT DISTINCT 
  location_id 
FROM
  departments ;
```
- 經典面試題：select * from employees; 和 select * from employees where commission_pct like ‘%%’ and last_name like ‘%%’; 結果是否一樣？並說明原因：不一樣！如果判斷的欄位中有null值，如果查詢是select * from employees where commission_pct like ‘%%’ or last_name like ‘%%’ or ...;把所有欄位都or寫齊了就一樣了。

3. 排序查詢

語法：

select 查詢列表

from 表

【where 篩選條件】

order by 排序列表【asc|desc】
asc代表的是升序，desc代表的是降序，如果不寫，預設是升序
order by子句中可以支持單個欄位、多個欄位、表達式、函數、別名
order by子句一般是放在查詢語句的最後面，但limit子句除外
查詢員工的信息，要求工資從高到低排序：
```
SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC ;
```
從低到高是ASC（預設是ASC）

查詢部門編號>=90的員工信息，按入職時間的先後進行排序：

SELECT 
  * 
FROM
  employees 
WHERE department_id >= 90 
ORDER BY hiredate ASC ;

按年薪的高低顯示員工的信息和年薪【按表達式（別名）排序】

SELECT 
  *,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 
FROM
  employees 
ORDER BY 年薪 DESC ;

按姓名的長度顯示員工的姓名和工資【按函數排序】

SELECT 
  LENGTH(last_name) AS 位元組長度,
  last_name,
  salary 
FROM
  employees 
ORDER BY 位元組長度 DESC;

查詢員工信息，要求先按工資排序，再按員工編號排序

SELECT 
  * 
FROM
  employees 
ORDER BY salary ASC,
  employee_id DESC ;

測試題

查詢員工的姓名和部門號和年薪，按年薪降序，按姓名升序

SELECT 
  last_name,
  department_id,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 
FROM
  employees 
ORDER BY 年薪 DESC,
  last_name ASC ;

選擇工資不在8000到17000的員工的姓名和工資，按工資降序

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE salary NOT BETWEEN 8000 
  AND 17000 
ORDER BY salary DESC ;

查詢郵箱中包含e的員工信息，並先按郵箱的位元組數降序，再按部門號升序

SELECT 
  * 
FROM
  employees 
WHERE email LIKE '%e%' 
ORDER BY LENGTH(email) DESC,
  department_id ASC ;

4. 常見函數

功能：類似於java中的方法，將一組邏輯語句
好處：
- 隱藏了實現細節
- 提高代碼的重用性
調用：select 函數名(實參列表) 【from 表】;
特點：
- 叫什麼（函數名）
- 乾什麼（函數功能）
分類：
- 單行函數：如concat、length、ifnull等
- 分組函數：做統計使用，又稱為統計函數、聚合函數、組函數

單行函數

字元函數
- length：獲取參數值的位元組個數
- concat：拼接字元串
- upper/lower：將字元串變成大寫/小寫
  - 將姓變成大寫，名變成小寫，然後拼接：
```
SELECT 
  CONCAT(UPPER(last_name), LOWER(first_name)) AS 姓名 
FROM
  employees ;
```
- substr/substring：截取字元串
  - 註意：索引從1開始
  - 截取從指定索引處後面所有字元
```
SELECT 
  SUBSTR(
    '李莫愁愛上了陸展元',
    6
  ) AS output ;
```
  - 截取從指定索引處指定字元長度的字元
```
SELECT 
  SUBSTR(
    '李莫愁愛上了陸展元',
    1,
    3
  ) output ;
```
- 案例：姓名中首字母大寫，其他字元小寫，然後用_拼接，顯示出來：
```
SELECT 
  CONCAT(
    UPPER(SUBSTR(last_name, 1, 1)),
    '_',
    LOWER(SUBSTR(last_name, 2))
  ) AS output 
FROM
  employees ;
```
- instr：返回子串第一次出現的索引，如果找不到返回0
```
SELECT 
  INSTR(
    '楊不悔愛上了殷六俠',
    '殷六俠'
  ) AS output ;
```
- trim：去掉字元串前後的空格或子串
```
SELECT 
  LENGTH(TRIM('   張翠山   ')) AS output ;
```
```
SELECT 
  TRIM('a' FROM 'aaa張a翠aa山aaaaa') AS output ;
```
- lpad：用指定的字元實現左填充指定長度
- rpad：用指定的字元實現右填充指定長度
- replace：替換，替換所有的子串
數學函數
- round：四捨五入
- ceil：向上取整，返回>=該參數的最小整數
- floor：向下取整，返回<=該參數的最大整數
- truncate：截斷，小數點後截斷到幾位
- mod：取餘，被除數為正，則為正；被除數為負，則為負
- rand：獲取隨機數，返回0-1之間的小數
日期函數
- now：返回當前系統日期+時間
- curdate：返回當前系統日期，不包含時間
- curtime：返回當前時間，不包含日期
- 可以獲取指定的部分，年、月、日、小時、分鐘、秒
```
SELECT 
  YEAR(hiredate) 年 
FROM
  employees ;
```
- str_to_date：將日期格式的字元轉換成指定格式的日期
```
SELECT 
  STR_TO_DATE('1998-3-2', '%Y-%c-%d') AS output ;
```
  - 查詢入職日期為1992-4-3的員工信息
```
SELECT 
  * 
FROM
  employees 
WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y') ;
```
- date_format：將日期轉換成字元串
```
SELECT 
  DATE_FORMAT(NOW(), '%y年%m月%d日)') AS output ;
```
  - 查詢有獎金的員工名和入職日期（xx月/xx日 xx年）
```
SELECT 
  last_name,
  DATE_FORMAT(hiredate, '%m月/%d日 %y年') AS 入職日期 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;
```
- datediff：返回兩個日期相差的天數
- monthname：以英文形式返回月

其他函數

SELECT VERSION(); 當前資料庫伺服器的版本
SELECT DATABASE();    當前打開的資料庫
SELECT USER();        當前用戶
password('字元');       返回該字元的密碼形式
md5('字元');            也是加密的一種形式（MD5）

流程式控制制函數

if函數：if else的效果

SELECT 
  last_name,
  commission_pct,
  IF(
    commission_pct IS NULL,
    '沒獎金，呵呵',
    '有獎金，嘻嘻'
  ) 備註 
FROM
  employees ;

case函數的使用1：switch case的效果

語法：

case 要判斷的欄位或表達式
when 常量1 then 要顯示的值1或語句1;
when 常量2 then 要顯示的值2或語句2;
...
else 要顯示的值n或語句n;
end

查詢員工的工資，要求：

部門號=30，顯示的工資為1.1倍

部門號=40，顯示的工資為1.2倍

部門號=50，顯示的工資為1.3倍

其他部門，顯示的工資為原工資

SELECT 
  salary AS 原始工資,
  department_id,
  CASE
    department_id 
    WHEN 30 
    THEN salary * 1.1 
    WHEN 40 
    THEN salary * 1.2 
    WHEN 50 
    THEN salary * 1.3 
    ELSE salary 
  END AS 新工資 
FROM
  employees ;

case函數的使用2：類似於多重if

case
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
...
else 要顯示的值n或語句n
end

查詢員工的工資情況

如果工資>20000，顯示A級別

如果工資>15000，顯示B級別

如果工資>10000，顯示C級別

否則，顯示D級別

SELECT 
  salary,
  CASE
    WHEN salary > 20000 
    THEN 'A' 
    WHEN salary > 15000 
    THEN 'B' 
    WHEN salary > 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS 工資級別 
FROM
  employees ;

測試題

顯示系統時間（日期+時間）
```
SELECT NOW();
```

查詢員工號，姓名，工資，以及工資提高20%後的結果（new salary）

SELECT 
  employee_id,
  last_name,
  salary,
  salary * 1.2 AS "new salary" 
FROM
  employees ;

將員工的姓名按首字母排序，並寫出姓名的長度（length）

SELECT 
  last_name,
  LENGTH(last_name) 
FROM
  employees 
ORDER BY SUBSTR(last_name, 1, 1) ;

做一個查詢

SELECT 
  CONCAT(
    last_name,
    ' earns ',
    salary,
    ' monthly but wants ',
    salary * 3
  ) AS "Dream Salary" 
FROM
  employees ;

case-when訓練

SELECT 
  last_name,
  job_id AS job,
  CASE
    job_id 
    WHEN 'AD_PRES' 
    THEN 'A' 
    WHEN 'ST_MAN' 
    THEN 'B' 
    WHEN 'IT_PROG' 
    THEN 'C' 
    WHEN 'SA_PRE' 
    THEN 'D' 
    WHEN 'ST_CLERK' 
    THEN 'E' 
  END AS Grade 
FROM
  employees 
WHERE job_id = 'AD_PRES' ;

分組函數

功能：用作統計使用，又稱為聚合函數或統計函數或組函數
分類：sum 求和、avg 平均值、max 最大值、min 最小值、count 計數（非空）
```
SELECT SUM(salary) FROM employees;
```
特點
- sum、avg一般用於處理數值型數據
- max、min、count可以處理任何類型數據
- 以上分組函數都忽略null值

可以和distinct搭配實現去重的運算

SELECT 
  SUM(DISTINCT salary),
  SUM(salary) 
FROM
  employees ;

SELECT 
  COUNT(DISTINCT salary),
  COUNT(salary) 
FROM
  employees ;

count函數的單獨介紹
- 效率
  - MYISAM存儲引擎下，count(*)的效率高
  - INNODB存儲引擎下，count(*)和count(1)效率差不多，比count(欄位)要高一些
- 使用count(*) 統計一共有多少行
```
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
```
和分組函數一同查詢的欄位有限制，要求是group by後的欄位

訓練題

查詢公司員工工資的最大值，最小值，平均值，總和

SELECT 
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary) 
FROM
  employees ;

查詢員工表中的最大入職時間和最小入職時間的相差天數（difference）
```
SELECT 
  DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFERENCE 
FROM
  employees ;
```

查詢部門編號為90的員工個數

SELECT 
  COUNT(*) 
FROM
  employees 
WHERE department_id = 90 ;

5. 分組查詢

語法：

select 分組函數，列（要求出現在group by的後面）

from 表

【where 篩選條件】

group by 分組的列表

【having 分組後的篩選】

【order by 子句】
註意：查詢列表比較特殊，要求是分組函數和group by後出現的欄位
特點：
- 分組查詢中的篩選條件分為兩類：
  
  數據源位置關鍵字
  
  分組前篩選原始表 group by子句的前面 where
  
  分組後篩選分組後的結果集 group by子句的後面 having
- 分組函數做條件肯定是放在having子句中
- 能用分組前篩選的，就優先考慮使用分組前篩選
- group by子句支持單個欄位分組，多個欄位分組（多個欄位之間用逗號隔開沒有順序要求），表達式或函數（用得較少）
- 也可以添加排序（排序放在整個分組查詢最後位置）

查詢每個工種的最高工資

SELECT 
  MAX(salary),
  job_id 
FROM
  employees 
GROUP BY job_id ;

查詢每個位置上的部門個數

SELECT 
  COUNT(*),
  location_id 
FROM
  departments
GROUP BY location_id ;

查詢郵箱中包含a字元的，每個部門的平均工資

SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY department_id ;

查詢有獎金的每個領導手下員工的最高工資

SELECT 
  MAX(salary),
  manager_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id ;

查詢那個部門的員工個數>2

查詢每個部門的員工個數

SELECT 
  COUNT(*) AS 員工個數,
  department_id 
FROM
  employees 
GROUP BY department_id ;

根據上面的結果進行篩選，查詢哪個部門的員工個數＞2

SELECT 
  COUNT(*) AS 員工個數,
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING 員工個數 > 2 ;

添加分組後的篩選用having，分組前的用where

查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資

查詢每個工種有獎金的員工的最高工資

SELECT 
  MAX(salary),
  job_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL
GROUP BY job_id ;

根據上面的結果繼續篩選，最高工資>12000

SELECT 
  MAX(salary) AS 最高工資,
  job_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING 最高工資 > 12000 ;

查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，以及其最低工資

SELECT 
  MIN(salary) AS 最低工資,
  manager_id 
FROM
  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING 最低工資 > 5000 ;

按表達式或函數分組

按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的有哪些

查詢每個長度的員工個數

SELECT 
  COUNT(*) 員工個數,
  LENGTH(last_name) 姓名長度 
FROM
  employees 
GROUP BY 姓名長度 ;

添加篩選條件

SELECT 
  COUNT(*) 員工個數,
  LENGTH(last_name) 姓名長度 
FROM
  employees 
GROUP BY 姓名長度 
HAVING 員工個數 > 5 ;

按多個欄位分組

查詢每個部門每個工種的員工的平均工資

SELECT 
  AVG(salary),
  department_id,
  job_id 
FROM
  employees 
GROUP BY department_id,
  job_id ;

添加排序

查詢每個部門每個工種的員工的平均工資，並按平均工資的高低顯示

SELECT 
  AVG(salary) AS 平均工資,
  department_id,
  job_id 
FROM
  employees 
GROUP BY department_id,
  job_id 
ORDER BY 平均工資 DESC ;

練習題

查詢各job_id的員工工資的最大值、最小值、平均值，總和，並按job_id升序

SELECT 
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary),
  job_id 
FROM
  employees 
GROUP BY job_id 
ORDER BY job_id ;

查詢員工最高工資和最低工資的差距（DIFFERENCE）

SELECT 
  MAX(salary) - MIN(salary) AS DIFFERENCE 
FROM
  employees ;

查詢各個管理者手下員工的最低工資，其中最低工資不能低於6000，沒有管理者的員工不計算在內

SELECT 
  MIN(salary) AS 最低工資 
FROM
  employees 
WHERE manager_id IS NOT NULL 
GROUP BY manager_id 
HAVING 最低工資 >= 6000 ;

查詢所有部門的編號，員工數量和工資平均值，並按平均工資降序

SELECT 
  department_id,
  COUNT(*) AS 員工數量,
  AVG(salary) AS 工資平均值 
FROM
  employees 
GROUP BY department_id 
ORDER BY 工資平均值 DESC ;

查詢具有各個job_id的員工人數

SELECT 
  COUNT(*),
  job_id 
FROM
  employees 
GROUP BY job_id ;

6. 連接查詢

含義：又稱多表查詢，當查詢的欄位來自於多個表時，就會用到連接查詢
笛卡爾乘積現象：表1有m行，表2有n行，結果=m*n
- 發生原因：沒有有效的連接條件
- 如何避免：添加有效的連接條件
分類：
- 按年代分類：
  - sql92標準：僅僅支持內連接
  - sql99標準【推薦】：支持內連接+外連接（左外和右外）+交叉連接
- 按功能分類：
  - 內連接
    - 等值連接
    - 非等值連接
    - 自連接
  - 外連接
    - 左外連接
    - 右外連接
    - 全外連接（mysql不支持）
  - 交叉連接

sql92標準

等值連接
- 多表等值連接的結果為多表的交集部分
- n表連接，至少需要n-1個連接條件
- 多表的順序沒有要求
- 一般需要為表起別名
- 可以搭配前面介紹的所有子句使用，比如排序、分組、篩選
- 查詢女神名和對應的男神名：
```
SELECT 
  NAME,
  boyname 
FROM
  boys,
  beauty 
WHERE beauty.boyfriend_id = boys.id ;
```
- 查詢員工名和對應的部門名
```
SELECT 
  last_name,
  department_name 
FROM
  employees,
  departments 
WHERE employees.`department_id` = departments.`department_id` ;
```
為表起別名
- 提高語句的簡潔度
- 區分多個重名的欄位
- 註意：如果為表起了別名，則查詢的欄位就不能使用原始的表明去限定
- 查詢員工名、工種號、工種名
```
SELECT 
  last_name,
  e.`job_id`,
  job_title 
FROM
  employees e,
  jobs j 
WHERE e.`job_id` = j.`job_id` ;
```

兩個表的順序是否可以調換

查詢員工名、工種號、工種名

SELECT 
  last_name,
  e.`job_id`,
  job_title 
FROM
  jobs j ,
  employees e
WHERE e.`job_id` = j.`job_id` ;

可以加篩選

查詢有獎金的員工名、部門名

SELECT 
  last_name,
  department_name 
FROM
  employees AS e,
  departments AS d 
WHERE e.`department_id` = d.`department_id` 
  AND e.`commission_pct` IS NOT NULL ;

查詢城市名中第二個字元為o的部門名和城市名

SELECT 
  department_name,
  city 
FROM
  departments d,
  locations l 
WHERE d.`location_id` = l.`location_id` 
  AND city LIKE '_o%' ;

可以加分組

查詢每個城市的部門個數

SELECT 
  COUNT(*) 個數,
  city 
FROM
  departments d,
  locations l 
WHERE d.`location_id` = l.`location_id` 
GROUP BY city ;

查詢有將近的每個部門的部門名和部門的領導編號和該部門的最低工資

SELECT 
  department_name,
  d.manager_id,
  MIN(salary) 
FROM
  departments d,
  employees e 
WHERE d.`department_id` = e.`department_id` 
  AND commission_pct IS NOT NULL 
GROUP BY department_name,
  d.manager_id ;

可以加排序

查詢每個工種的工種名和員工的個數，並且按員工個數降序

SELECT 
  job_title,
  COUNT(*) AS 個數 
FROM
  employees e,
  jobs j 
WHERE e.`job_id` = j.`job_id` 
GROUP BY job_title 
ORDER BY 個數 DESC ;

可是實現三表連接：

查詢員工名、部門名和所在的城市

SELECT 
  last_name,
  department_name,
  city 
FROM
  employees e,
  departments d,
  locations l 
WHERE e.`department_id` = d.`department_id` 
  AND d.`location_id` = l.`location_id` ;

非等值連接

查詢員工的工資和工資級別

SELECT 
  salary,
  grade_level 
FROM
  employees e,
  job_grades g 
WHERE salary BETWEEN g.lowest_sal 
  AND g.highest_sal ;

自連接

查詢員工名和上級的名稱

SELECT 
  e.employee_id,
  e.last_name,
  m.employee_id,
  m.last_name 
FROM
  employees e,
  employees m 
WHERE e.`manager_id` = m.`employee_id` ;

測試題：

顯示員工表的最大工資，工資平均值

SELECT 
  MAX(salary),
  AVG(salary) 
FROM
  employees ;

查詢員工表的employee_id，job_id，last_name，按department_id降序，salary升序

SELECT 
  employee_id,
  job_id,
  last_name 
FROM
  employees 
ORDER BY department_id DESC,
  salary ASC ;

查詢員工表的job_id中包含a和e的，並且a在e的前面
```
SELECT 
  job_id 
FROM
  employees 
WHERE job_id LIKE '%a%e%' ;
```
顯示當前日期，以及去前後空格，截取子字元串的函數
```
select now();
select trim();
select substr(str, startIndex, [length])
```

sql99語法

語法：

select 查詢列表

from 表1 別名【連接類型】

join 表2 別名

on 連接條件

【where 篩選條件】

【group by 分組】

【having 篩選條件】

【order by 排序列表】
內連接（同上）：連接類型是inner
外連接
- 左外：left 【outer】
- 右外：right【outer】
- 全外：full 【outer】
交叉連接：cross

內連接：

語法：

select 查詢列表

from 表1 別名

inner join 表2 別名

on 連接條件

…
分類：

等值連接

非等值連接

自連接
特點：
- 添加排序、分組、篩選
- inner可以省略
- 篩選條件放在where後面，連接條件放在on後面，提高分離性，便於閱讀
- inner join連接和sql92語法中的等值連接效果是一樣的，都是查詢多表的交集

等值連接：

查詢員工名、部門名

SELECT 
  last_name,
  department_name 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` ;

查詢名字中包含e的給員工名和工種名

SELECT 
  last_name,
  job_title 
FROM
  employees e 
  INNER JOIN jobs j 
    ON e.`job_id` = j.`job_id` 
WHERE last_name LIKE "%e%" ;

查詢部門個數>3的城市名和部門個數

SELECT 
  city,
  COUNT(*) 部門個數 
FROM
  departments d 
  INNER JOIN locations l 
    ON d.`location_id` = l.`location_id` 
GROUP BY city 
HAVING 部門個數 > 3 ;

查詢哪個部門的部門員工個數>3的部門名和員工個數，並按個數降序排序

SELECT 
  department_name,
  COUNT(*) 員工個數 
FROM
  departments d 
  INNER JOIN employees e 
    ON d.`department_id` = e.`department_id` 
GROUP BY d.`department_id` 
HAVING 員工個數 > 3 
ORDER BY 員工個數 DESC ;

查詢員工名、部門名、工種名，並按部門名降序

SELECT 
  last_name,
  department_name,
  job_title 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 
  INNER JOIN jobs j 
    ON e.`job_id` = j.`job_id` 
ORDER BY d.`department_id` DESC ;

非等值連接

查詢員工的工資級別

SELECT 
  salary,
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades g 
    ON e.`salary` BETWEEN g.`lowest_sal` 
    AND g.`highest_sal` ;

查詢每個工資級別>20的個數，並且按工資級別降序

SELECT 
  COUNT(*),
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades g 
    ON e.`salary` BETWEEN g.`lowest_sal` 
    AND g.`highest_sal` 
GROUP BY grade_level 
HAVING COUNT(*) > 20 
ORDER BY grade_level DESC ;

自連接

查詢員工的名字、上級的名字

SELECT 
  e.last_name,
  m.last_name 
FROM
  employees e 
  INNER JOIN employees m 
    ON e.`manager_id` = m.`employee_id` ;

查詢姓名中包含字元k的員工的名字、上級的名字

SELECT 
  e.last_name,
  m.last_name 
FROM
  employees e 
  INNER JOIN employees m 
    ON e.`manager_id` = m.`employee_id` 
WHERE e.`last_name` LIKE "%k%" ;

外連接
- 應用場景：用於查詢一個表中有，另一個表沒有的記錄
- 特點：
  - 外連接的查詢結果為主表中的所有記錄，如果從表中有和它匹配的，則顯示匹配的值，如果從表中沒有和它匹配的，則顯示null
  - 外連接查詢結果=內連接結果+主表中有而從表中沒有的記錄
  - 左外連接：left join左邊的是主表
  - 右外連接：right join右邊的是主表
  - 左外和右外交換兩個表的順序，可以實現同樣的效果
  - 圈外鏈接=內連接的結果+表1中有但表2中沒有的+表2中有但表1中沒有的
- 查詢沒有男朋友的女神名
```
SELECT 
  b.name,
  bo.* 
FROM
  beauty b 
  LEFT JOIN boys bo 
    ON b.boyfriend_id = bo.id 
WHERE bo.`id` IS NULL ;
```
- 查詢哪個部門沒有員工
- 左外：
```
SELECT 
  d.*,
  e.employee_id 
FROM
  departments d 
  LEFT OUTER JOIN employees e 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;
```
- 右外：
```
SELECT 
  d.*,
  e.employee_id 
FROM
  employees e 
  RIGHT OUTER JOIN departments d 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;
```
- 全外連接
  - mysql不支持
  - 案例：
```
SELECT 
  b.*,
  bo.* 
FROM
  beauty b FULL 
  OUTER JOIN boys bo 
    ON b.`boyfriend_id` = bo.id ;
```
- 交叉連接（也就是笛卡爾乘積）
  - 案例：
```
SELECT 
  b.*,
  bo.* 
FROM
  beauty b 
  CROSS JOIN boys bo ;
```

sql92 和 sql99 pk
- 功能：sql99支持的較多
- 可讀性：sql99實現連接條件和篩選條件的分離，可讀性較高

練習：

查詢編號>3的女神的男朋友信息，如果有則列出詳細信息，如果沒有，則用null填充

SELECT 
  a.id,
  a.name,
  b.* 
FROM
  beauty a 
  LEFT JOIN boys b 
    ON a.`boyfriend_id` = b.`id` 
WHERE a.`id` > 3 ;

查詢哪個城市沒有部門

SELECT 
  city,
  d.* 
FROM
  departments d 
  RIGHT JOIN locations l 
    ON d.location_id = l.location_id 
WHERE d.department_id IS NULL ;

查詢部門名為SAL或IT的員工信息

SELECT 
  d.`department_name`,
  e.* 
FROM
  departments d 
  LEFT JOIN employees e 
    ON d.`department_id` = e.`department_id` 
WHERE d.`department_name` = 'SAL' 
  OR d.`department_name` = 'IT' ;

7. 子查詢

含義：出現在其他語句中的select語句，稱為子查詢或內查詢；外部的查詢語句，稱為主查詢或外查詢
嵌套在其他語句內部的select語句成為子查詢或內查詢
外面的語句可以是insert、update、delete、select等，一般select作為外面語句較多
外面如果為select語句，則此語句稱為外查詢或主查詢
分類：
- 按子查詢出現的位置：
  - select後面：僅僅支持標量子查詢
  - from後面：支持表子查詢
  - where或having後面：支持標量子查詢，列子查詢，行子查詢（較少）
  - exists後面（相關子查詢）：支持表子查詢
- 按功能、結果集的行列數不同：
  - 標量子查詢（結果集只有一行一列）
  - 列子查詢（結果集只有一列多行）
  - 行子查詢（結果集有一行多列）
  - 表子查詢（結果集一般為多行多列）

where或having後面

標量子查詢（單行子查詢）
列子查詢（多行子查詢）
行子查詢（多列多行）
特點：
- 子查詢放在小括弧內
- 子查詢一般放在條件的右側，where，having
- 標量子查詢，一般搭配著單行操作符使用（> < >= <= = <>）
- 列子查詢，一般搭配著多行操作符使用（IN、ANY/SOME、ALL）
- 子查詢的執行優選與主查詢執行，主查詢的條件用到了子查詢的結果

標量子查詢

案例1：誰的工資比Abel高？

SELECT 
  salary 
FROM
  employees 
WHERE last_name = 'Abel' ;

案例2：返回job_id與141號員工相同，salary比143員工多的員工，姓名，job_id，工資

SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id = 
  (SELECT 
    job_id 
  FROM
    employees 
  WHERE employee_id = 141) 
  AND salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE employee_id = 143) ;

案例3：返回公司工資最少的員工的last_name, job_id和salary

SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

案例4：查詢最低工資大於50號部門的最低工資的部門id和其最低工資

SELECT 
  MIN(salary),
  e.`department_id` 
FROM
  employees e 
GROUP BY e.`department_id` 
HAVING MIN(salary) > 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE department_id = 50) ;

列子查詢

（多行子查詢）

多行比較操作符：

IN/NOT IN：等於列表中的任意一個

ANY|SOME：和子查詢返回的某一個值比較，用的較少

ALL：和子查詢返回的所有值比較

案例1：返回location_id是1400或1700的部門中的所有員工姓名

SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;

案例2：返回其他工種中比job_id為‘IT_PROG’工種任一工資低的員工的員工號、姓名、job_id以及salary

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ANY 
  (SELECT DISTINCT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

或者用max代替any

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT 
    MAX(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

案例3：返回其他工種中比job_id為‘IT_PROG’工種所有工資都低的員工的員工號、姓名、job_id以及salary

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ALL 
  (SELECT DISTINCT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

或者用min代替all

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

行子查詢

結果集一行多列或多行多列

案例1：查詢員工編號最少並且工資最高的員工信息

SELECT 
  * 
FROM
  employees 
WHERE (employee_id, salary) = 
  (SELECT 
    MIN(employee_id),
    MAX(salary) 
  FROM
    employees) ;

select後面

僅僅支持標量子查詢

案例1：查詢每個部門的員工個數

SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.department_Id) 個數 
FROM
  departments d ;

案例2：查詢員工號=102的部門名

SELECT 
  (SELECT 
    department_name 
  FROM
    departments d 
    INNER JOIN employees e 
      ON d.department_id = e.department_id 
  WHERE e.employee_id = 102) 部門名 ;

from後面

將子查詢結果充當一張表，要求必須起別名

案例1：查詢每個部門的平均工資的工資等級

SELECT 
  ag_dep.*,
  g.`grade_level` 
FROM
  (SELECT 
    AVG(salary) ag,
    department_id 
  FROM
    employees 
  GROUP BY department_id) ag_dep 
  INNER JOIN job_grades g 
    ON ag_dep.ag BETWEEN g.`lowest_sal` 
    AND g.`highest_sal` ;

exists後面

相關子查詢
語法：exists（完整的查詢語句）
結果：1或0

案例1：查詢有員工的部門名

SELECT 
  department_name 
FROM
  departments d 
WHERE EXISTS 
  (SELECT 
    * 
  FROM
    employees e 
  WHERE d.`department_id` = e.`department_id`) ;

用in更簡單

SELECT 
  department_name 
FROM
  departments d 
WHERE d.`department_id` IN 
  (SELECT 
    department_id 
  FROM
    employees e) ;

習題集

查詢和zlotkey相同部門的員工姓名和工資

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees e 
  WHERE e.`last_name` = 'Zlotkey') ;

查詢工資比公司平均工資高的員工的員工號，姓名和工資

SELECT 
  employee_id,
  last_name,
  salary 
FROM
  employees e 
WHERE e.`salary` > 
  (SELECT 
    AVG(salary) 
  FROM
    employees) ;

查詢各部門中工資比本部門平均工資高的員工的員工號，姓名和工資

SELECT 
  employee_id,
  last_name,
  salary 
FROM
  employees e 
  INNER JOIN 
    (SELECT 
      AVG(salary) ag,
      department_id 
    FROM
      employees 
    GROUP BY department_id) nt 
    ON nt.department_id = e.department_id 
WHERE salary > ag ;

查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名

SELECT 
  employee_id,
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    employees 
  WHERE last_name LIKE '%u%') ;

查詢在部門的location_id為1700的部門工作的員工的員工號

SELECT 
  employee_id 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id = 1700) ;

查詢管理者是King的員工姓名和工資

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE manager_id IN 
  (SELECT 
    employee_id 
  FROM
    employees 
  WHERE last_name = 'K_ing') ;

查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列，列名為姓.名

SELECT 
  CONCAT(nt.first_name, nt.last_name) "姓.名" 
FROM
  (SELECT 
    first_name,
    last_name 
  FROM
    employees 
  WHERE salary = 
    (SELECT 
      MAX(salary) 
    FROM
      employees)) nt ;

8. 分頁查詢

應用場景：當要顯示的數據，一頁顯示不全，需要分頁提交sql請求
語法：

select 查詢列表

from 表

【join type】 join 表2

on 連接條件

where 篩選條件

group by 分組欄位

having 分組後的篩選

order by 排序的欄位】

limit offset，size；

offset：要顯示條目的起始索引（從0開始）

size：要顯示的條目個數
特點：
- limit語句放在查詢語句的最後
- 公式：
  
  要顯示的頁數page，每頁的條目數size
  
  select 查詢列表
  
  from 表
  
  limit （page - 1）* size， size；

案例1：查詢前5條員工信息

SELECT * FROM employees LIMIT 0, 5;
或者
SELECT * FROM employees LIMIT 5;

案例2：查詢第11條-第25條
```
SELECT * FROM employees LIMIT 10, 15;
```

案例3：有獎金的員工信息，並且工資較高的前10名顯示出來

SELECT 
  * 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

經典案例1：

查詢工資最低的員工信息：last_name, salary

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

查詢平均工資最低的部門信息

SELECT 
  * 
FROM
  departments 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY AVG(salary) ASC 
  LIMIT 1) ;

查詢平均工資最低的部門信息和該部門的平均工資

SELECT 
  d.*,
  dd.ag 
FROM
  departments d 
  INNER JOIN 
    (SELECT 
      AVG(salary) ag,
      department_id 
    FROM
      employees 
    GROUP BY department_id 
    ORDER BY ag 
    LIMIT 1) dd 
    ON d.`department_id` = dd.department_id ;

查詢平均工資最高的job信息

SELECT 
  * 
FROM
  jobs j 
WHERE j.`job_id` = 
  (SELECT 
    job_id 
  FROM
    employees 
  GROUP BY job_id 
  ORDER BY AVG(salary) DESC 
  LIMIT 1) ;

查詢平均工資高於公司平均工資的部門有哪些

SELECT 
  AVG(salary) ag,
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING ag > 
  (SELECT 
    AVG(salary) 
  FROM
    employees) ;

查詢出公司中所有manager的詳細信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id IN 
  (SELECT DISTINCT 
    manager_id 
  FROM
    employees 
  WHERE manager_id IS NOT NULL) ;

各個部門中，最高工資中，最低的那個部門的最低工資是多少

SELECT 
  MIN(salary) 
FROM
  employees 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY MAX(salary) ASC 
  LIMIT 1) ;

查詢平均工資最高的部門的manager的詳細信息

SELECT 
  last_name,
  department_id,
  email,
  salary 
FROM
  employees 
WHERE employee_id = 
  (SELECT DISTINCT 
    manager_id 
  FROM
    employees 
  WHERE department_id = 
    (SELECT 
      department_id 
    FROM
      employees 
    GROUP BY department_id 
    ORDER BY AVG(salary) DESC 
    LIMIT 1) 
    AND manager_id IS NOT NULL) ;

9. 聯合查詢

union：聯合，合併，將多條查詢語句的結果合併成一個結果

引入案例：查詢部門編號>90或郵箱包含a的員工信息

SELECT 
  * 
FROM
  employees 
WHERE email LIKE "%a%" 
  OR department_id > 90 ;

用聯合查詢為：

SELECT 
  * 
FROM
  employees 
WHERE email LIKE "%a%" 
UNION
SELECT 
  * 
FROM
  employees 
WHERE department_id > 90;

語法：

查詢語句1

union 【ALL】

查詢語句2

union 【ALL】

…
應用場景：要查詢的結果來自於多個表，且多個表沒有直接的連接關係，但查詢的信息一致
特點：
- 要求多條查詢語句的查詢列數是一致的
- 要求多條查詢語句的查詢的每一列的類型和順序最好是一致的
- union關鍵字預設去重，如果使用union all可以包含重覆項

10. 查詢總結

語法：

select 查詢列表 7

from 表1 別名 1

連接類型 join 表2 2

on 連接條件 3

where 篩選 4

group by 分組列表 5

having 篩選 6

order by 排序列表 8

limit 排序列表 9

DML（Data Manipulation Language）數據操作語言

涉及到數據的
- 插入：insert
- 修改：update
- 刪除：delete

1. 插入語句

方式1：
- 語法：insert into 表名（列名，…） values（值1，…）
- 示例1：插入的值的類型要與列的類型一致或相容
```
INSERT INTO beauty (
  id,
  NAME,
  sex,
  borndate,
  phone,
  photo,
  boyfriend_id
) 
VALUES
  (
    13,
    '唐藝昕',
    '女',
    '1990-4-23',
    '18934531234',
    NULL,
    2
  );
```
- 示例2：不可以為null的列必須插入值。可以為null的列如何插入值？
```
方式1：欄位的值寫null
方式2：不寫該欄位
```
- 示例3：列的順序是否可以調換
```
INSERT INTO beauty(NAME, sex, id, phone)
VALUES('蔣欣', '女', 16, '110');
```
- 示例4：列數和值的個數必須一致
- 示例5：可以省略列名，預設所有列，而且列的順序和表中列的順序一致
```
INSERT INTO beauty
VALUES(18, '李易峰', '男', NULL, '19', NULL, NULL);
```
方式2：
- 語法：insert into 表名 set 列名=值，列名=值，…
```
INSERT INTO beauty SET id = 19,
NAME = '劉濤',
phone = '999' ;
```

兩種方式大pk

方式1支持插入多行，但是方式2不支持

INSERT INTO beauty
VALUES
(20, '李易峰', '男', NULL, '19', NULL, NULL),
(21, '李易峰', '男', NULL, '19', NULL, NULL),
(22, '李易峰', '男', NULL, '19', NULL, NULL);

方式1支持子查詢，方式2不支持

INSERT INTO beauty(id, NAME, phone)
SELECT 26, '送錢', '12341234';

2. 修改語句

語法：
- 修改單表的記錄
  
  update 表名
  
  set 列=新值，列=新值…
  
  where 篩選條件；
- 修改多表的記錄
- sql92語法
  
  update 表1 別名，表2 別名
  
  set 列=值…
  
  where 篩選條件
  
  and 篩選條件；
- sql99語法：
  
  update 表1 別名
  
  inner | left | right join 表2 別名
  
  on 連接條件
  
  set 列=值，…
  
  where 篩選條件；

修改單表的記錄

案例1：修改beauty表中姓唐的女神電話為109090909

UPDATE 
  beauty 
SET
  phone = '109090909' 
WHERE NAME LIKE '唐%' ;

案例2：修改boys表中id號位2的名稱為張飛，魅力值為10

UPDATE 
  boys 
SET
  boyname = '張飛',
  usercp = 10 
WHERE id = 2 ;

修改多表的記錄

案例1：修改張無忌的女朋友的手機號為114

UPDATE
  boys b 
  INNER JOIN beauty be 
    ON b.`id` = be.`boyfriend_id` SET be.`phone` = '114' 
WHERE b.`boyName` = '張無忌' ;

案例2：修改沒有男朋友的女神的男朋友編號都為 2號

UPDATE 
  boys b 
  RIGHT JOIN beauty be 
    ON b.`id` = be.`boyfriend_id` SET be.`boyfriend_id` = 2 
WHERE be.`boyfriend_id` IS NULL ;

3. 刪除語句

方式1：delete
- 語法
  - 單表的刪除
    
    delete from 表名 where 篩選條件
  - 多表的刪除
    - sql92語法
      
      delete 別名（要刪哪個表就寫哪個表的別名，都刪就都寫）
      
      from 表1 別名，表2 別名
      
      where 連接條件
      
      and 篩選條件
      
      limit 條目數；
    - sql99語法
      
      delete 別名（要刪哪個表就寫哪個表的別名，都刪就都寫）
      
      from 表1 別名
      
      inner | left | right join 表2 別名 on 連接條件
      
      where 篩選條件
      
      limit 條目數；
- 案例1：刪除手機號以9結尾的女神信息
```
DELETE 
FROM
  beauty 
WHERE phone LIKE '%9' ;
```
- 案例2：刪除張無忌的女朋友的信息
```
DELETE 
  be 
FROM
  beauty be 
  INNER JOIN boys b 
    ON b.`id` = be.`boyfriend_id` 
WHERE b.`boyName` = '張無忌' ;
```
- 案例3：刪除黃曉明的信息以及他女朋友的信息
```
DELETE 
  b,
  be 
FROM
  beauty be 
  INNER JOIN boys b 
    ON b.`id` = be.`boyfriend_id` 
WHERE b.`boyName` = '黃曉明' ;
```
方式2：truncate
- 語法
  
  truncate table 表名；
- truncate語句中不許加where
- 一刪全刪
```
TRUNCATE TABLE boyes ;
```
delete pk truncate
- delete可以加where條件，truncate不可以
- truncate刪除效率高一些
- 假如要刪除的表中有自增長列，如果用delete刪除後，再插入數據，自增長列的值從斷點開始，而truncate刪除後，再插入數據，自增長列的值從1開始。
- truncate刪除沒有返回值，delete刪除有返回值
- truncate刪除不能回滾，delete刪除可以回滾

DDL（Data Definition Language）數據定義語言

庫和表的管理
- 庫的管理：創建、修改、刪除
- 標的管理：創建、修改、刪除
- 創建：create
- 修改：alter
- 刪除：frop

1. 庫的管理

庫的創建
- 語法：create database [if not exists] 庫名 [character set 字元集名]；
- 案例：創建庫book
```
CREATE DATABASE IF NOT EXISTS books;
```

庫的修改

修改庫名的語句【已停用】
```
RENAME DATABASE books TO new_books;
```
更改庫的字元集
```
ALTER DATABASE books CHARACTER SET gbk;
```

庫的刪除
```
DROP DATABASE IF EXISTS books;
```

2. 表的管理

表的創建
- 語法：
create table 【if not exists】表名(

列名列的類型【（長度）約束】，

列名列的類型【（長度）約束】，

…

)
- 案例1：創建表 book
```
CREATE TABLE book (
  id INT,
  bname VARCHAR (20),
  price DOUBLE,
  authorid INT,
  publishdate DATETIME
) ;
```
- 案例2：創建表author
```
CREATE TABLE author (
  id INT,
  au_name VARCHAR (20),
  nation VARCHAR (10)
) ;
```
- 案例3：查看創建的表
```
DESC author;
```
表的修改
- 語法：alter table 表名 add | drop | modify | change column 列名【列類型約束】；
- 添加列：alter table 表名 add column 列名類型【first | after 欄位名】；
- 修改列的類型或約束：alter table 表名 modify column 列名新類型【新約束】；
- 修改列名：alter table 表名 change column 舊列名新列名類型；
- 刪除列：alter table 表名 drop column 列名；
- 修改表名：alter table 表名 rename 【to】新表名；
- 修改列名
```
ALTER TABLE book 
  CHANGE COLUMN publishdate pubdate DATETIME ;
```
- 修改列的類型或約束
```
ALTER TABLE book 
  MODIFY COLUMN pubdate TIMESTAMP ;
```
- 添加新列
```
ALTER TABLE author 
  ADD COLUMN annual DOUBLE ;
```
- 刪除列
```
ALTER TABLE author 
  DROP COLUMN annual ;
```