當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

尚硅谷MySQL基础学习笔记

發布時間：2023/12/18 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了尚硅谷MySQL基础学习笔记小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

數據庫MySQL學習筆記

文章目錄

- 寫在前面
- MySQL引入
- - 數據庫的好處
  - 數據庫的相關概念
  - 數據庫存儲數據的特點
  - MySQL服務的啟動和停止
  - MySQL服務端的登錄和退出
  - MySQL的常用命令
  - MySQL語法規范
- DQL（Data Query Language）數據查詢語言
- - 1. 基礎查詢
  - 2. 條件查詢
  - 3. 排序查詢
  - 4. 常見函數
  - - 單行函數
    - 分組函數
  - 5. 分組查詢
  - 6. 連接查詢
  - 7. 子查詢
  - - where或having后面
    - - 標量子查詢
      - 列子查詢
      - 行子查詢
    - select后面
    - from后面
    - exists后面
  - 8. 分頁查詢
  - 9. 聯合查詢
  - 10. 查詢總結
- DML（Data Manipulation Language）數據操作語言
- - 1. 插入語句
  - 2. 修改語句
  - - 修改單表的記錄
    - 修改多表的記錄
  - 3. 刪除語句
- DDL（Data Definition Language）數據定義語言
- - 1. 庫的管理
  - 2. 表的管理
  - 3. 常見的數據類型
  - - 整型
    - 小數
    - 字符型
    - 日期型
  - 4. 常見約束
  - - 創建表時添加約束
    - 修改表時添加約束
    - 修改表時刪除約束
  - 5. 標識列
- TCL（Transaction Control Language）事務控制語言
- 視圖
- 變量
- 存儲過程和函數
- - 1. 存儲過程
  - 2. 函數
- 流程控制結構

寫在前面

學習鏈接：數據庫 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值
  - 查詢沒有獎金的員工名和獎金率：
    SELECTlast_name,commission_pct FROMemployees WHEREcommission_pct IS NULL;
  - 查詢有獎金的：
    SELECTlast_name,commission_pct FROMemployees WHEREcommission_pct IS NOT NULL;
- 安全等于 <=>
  - is null：僅僅可以判斷null值，可讀性較高
  - <=>：既可以判斷null值，又可以判斷普通的數值，可讀性較低
測試題
- 查詢沒有獎金，且工資小于18000的salary, last_name：
  SELECT salary,last_name FROMemployees WHERE commission_pct IS NULL AND salary < 18000;
- 查詢employees表中，job_id不為‘IT’或者工資為12000的員工信息：
  SELECT * FROMemployees WHERE job_id <> 'IT' OR salary = 12000 ;
- 查看部門表的結構：
  DESC departments;
- 查詢部門表中涉及到了哪些位置編號：
  SELECT DISTINCT location_id FROMdepartments ;
- 經典面試題：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 * FROMemployees ORDER BY salary DESC ;
從低到高是ASC（默認是ASC）
查詢部門編號>=90的員工信息，按入職時間的先后進行排序：
SELECT * FROMemployees WHERE department_id >= 90 ORDER BY hiredate ASC ;
按年薪的高低顯示員工的信息和年薪【按表達式（別名）排序】
SELECT *,salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 FROMemployees ORDER BY 年薪 DESC ;
按姓名的長度顯示員工的姓名和工資【按函數排序】
SELECT LENGTH(last_name) AS 字節長度,last_name,salary FROMemployees ORDER BY 字節長度 DESC;
查詢員工信息，要求先按工資排序，再按員工編號排序
SELECT * FROMemployees ORDER BY salary ASC,employee_id DESC ;
測試題
- 查詢員工的姓名和部門號和年薪，按年薪降序，按姓名升序
  SELECT last_name,department_id,salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪 FROMemployees ORDER BY 年薪 DESC,last_name ASC ;
- 選擇工資不在8000到17000的員工的姓名和工資，按工資降序
  SELECT last_name,salary FROMemployees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC ;
- 查詢郵箱中包含e的員工信息，并先按郵箱的字節數降序，再按部門號升序
  SELECT * FROMemployees 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 姓名 FROMemployees ;
- 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 FROMemployees ;
- 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) 年 FROMemployees ;
- str_to_date：將日期格式的字符轉換成指定格式的日期
  SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d') AS output ;
  - 查詢入職日期為1992-4-3的員工信息
    SELECT * FROMemployees 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 入職日期 FROMemployees 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,'沒獎金，呵呵','有獎金，嘻嘻') 備注 FROMemployees ;
- 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,CASEdepartment_id WHEN 30 THEN salary * 1.1 WHEN 40 THEN salary * 1.2 WHEN 50 THEN salary * 1.3 ELSE salary END AS 新工資 FROMemployees ;
- 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,CASEWHEN salary > 20000 THEN 'A' WHEN salary > 15000 THEN 'B' WHEN salary > 10000 THEN 'C' ELSE 'D' END AS 工資級別 FROMemployees ;
測試題
- 顯示系統時間（日期+時間）
  SELECT NOW();
- 查詢員工號，姓名，工資，以及工資提高20%后的結果（new salary）
  SELECT employee_id,last_name,salary,salary * 1.2 AS "new salary" FROMemployees ;
- 將員工的姓名按首字母排序，并寫出姓名的長度（length）
  SELECT last_name,LENGTH(last_name) FROMemployees ORDER BY SUBSTR(last_name, 1, 1) ;
- 做一個查詢
  SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary * 3) AS "Dream Salary" FROMemployees ;
- case-when訓練
  SELECT last_name,job_id AS job,CASEjob_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 FROMemployees 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) FROMemployees ; SELECT COUNT(DISTINCT salary),COUNT(salary) FROMemployees ;
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) FROMemployees ;
- 查詢員工表中的最大入職時間和最小入職時間的相差天數（difference）
  SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFERENCE FROMemployees ;
- 查詢部門編號為90的員工個數
  SELECT COUNT(*) FROMemployees 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 FROMemployees GROUP BY job_id ;
查詢每個位置上的部門個數
SELECT COUNT(*),location_id FROMdepartments GROUP BY location_id ;
查詢郵箱中包含a字符的，每個部門的平均工資
SELECT AVG(salary),department_id FROMemployees WHERE email LIKE '%a%' GROUP BY department_id ;
查詢有獎金的每個領導手下員工的最高工資
SELECT MAX(salary),manager_id FROMemployees WHERE commission_pct IS NOT NULL GROUP BY manager_id ;
查詢那個部門的員工個數>2
- 查詢每個部門的員工個數
  SELECT COUNT(*) AS 員工個數,department_id FROMemployees GROUP BY department_id ;
- 根據上面的結果進行篩選，查詢哪個部門的員工個數＞2
  SELECT COUNT(*) AS 員工個數,department_id FROMemployees GROUP BY department_id HAVING 員工個數 > 2 ;
添加分組后的篩選用having，分組前的用where
查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資
- 查詢每個工種有獎金的員工的最高工資
  SELECT MAX(salary),job_id FROMemployees WHERE commission_pct IS NOT NULL GROUP BY job_id ;
- 根據上面的結果繼續篩選，最高工資>12000
  SELECT MAX(salary) AS 最高工資,job_id FROMemployees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING 最高工資 > 12000 ;
- 查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，以及其最低工資
  SELECT MIN(salary) AS 最低工資,manager_id FROMemployees WHERE manager_id > 102 GROUP BY manager_id HAVING 最低工資 > 5000 ;
- 按表達式或函數分組
  - 按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的有哪些
    - 查詢每個長度的員工個數
      SELECT COUNT(*) 員工個數,LENGTH(last_name) 姓名長度 FROMemployees GROUP BY 姓名長度 ;
    - 添加篩選條件
      SELECT COUNT(*) 員工個數,LENGTH(last_name) 姓名長度 FROMemployees GROUP BY 姓名長度 HAVING 員工個數 > 5 ;
- 按多個字段分組
  - 查詢每個部門每個工種的員工的平均工資
    SELECT AVG(salary),department_id,job_id FROMemployees GROUP BY department_id,job_id ;
- 添加排序
  - 查詢每個部門每個工種的員工的平均工資，并按平均工資的高低顯示
    SELECT AVG(salary) AS 平均工資,department_id,job_id FROMemployees GROUP BY department_id,job_id ORDER BY 平均工資 DESC ;
練習題
- 查詢各job_id的員工工資的最大值、最小值、平均值，總和，并按job_id升序
  SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROMemployees GROUP BY job_id ORDER BY job_id ;
- 查詢員工最高工資和最低工資的差距（DIFFERENCE）
  SELECT MAX(salary) - MIN(salary) AS DIFFERENCE FROMemployees ;
- 查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在內
  SELECT MIN(salary) AS 最低工資 FROMemployees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING 最低工資 >= 6000 ;
- 查詢所有部門的編號，員工數量和工資平均值，并按平均工資降序
  SELECT department_id,COUNT(*) AS 員工數量,AVG(salary) AS 工資平均值 FROMemployees GROUP BY department_id ORDER BY 工資平均值 DESC ;
- 查詢具有各個job_id的員工人數
  SELECT COUNT(*),job_id FROMemployees GROUP BY job_id ;

6. 連接查詢

含義：又稱多表查詢，當查詢的字段來自于多個表時，就會用到連接查詢
笛卡爾乘積現象：表1有m行，表2有n行，結果=m*n
- 發生原因：沒有有效的連接條件
- 如何避免：添加有效的連接條件
分類：
- 按年代分類：
  - sql92標準：僅僅支持內連接
  - sql99標準【推薦】：支持內連接+外連接（左外和右外）+交叉連接
- 按功能分類：
  - 內連接
    - 等值連接
    - 非等值連接
    - 自連接
  - 外連接
    - 左外連接
    - 右外連接
    - 全外連接（mysql不支持）
  - 交叉連接
sql92標準
- 等值連接
  - 多表等值連接的結果為多表的交集部分
  - n表連接，至少需要n-1個連接條件
  - 多表的順序沒有要求
  - 一般需要為表起別名
  - 可以搭配前面介紹的所有子句使用，比如排序、分組、篩選
  - 查詢女神名和對應的男神名：
    SELECT NAME,boyname FROMboys,beauty WHERE beauty.boyfriend_id = boys.id ;
  - 查詢員工名和對應的部門名
    SELECT last_name,department_name FROMemployees,departments WHERE employees.`department_id` = departments.`department_id` ;
- 為表起別名
  - 提高語句的簡潔度
  - 區分多個重名的字段
  - 注意：如果為表起了別名，則查詢的字段就不能使用原始的表明去限定
  - 查詢員工名、工種號、工種名
    SELECT last_name,e.`job_id`,job_title FROMemployees e,jobs j WHERE e.`job_id` = j.`job_id` ;
- 兩個表的順序是否可以調換
  - 查詢員工名、工種號、工種名
    SELECT last_name,e.`job_id`,job_title FROMjobs j ,employees e WHERE e.`job_id` = j.`job_id` ;
- 可以加篩選
  - 查詢有獎金的員工名、部門名
    SELECT last_name,department_name FROMemployees AS e,departments AS d WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL ;
  - 查詢城市名中第二個字符為o的部門名和城市名
    SELECT department_name,city FROMdepartments d,locations l WHERE d.`location_id` = l.`location_id` AND city LIKE '_o%' ;
- 可以加分組
  - 查詢每個城市的部門個數
    SELECT COUNT(*) 個數,city FROMdepartments d,locations l WHERE d.`location_id` = l.`location_id` GROUP BY city ;
  - 查詢有將近的每個部門的部門名和部門的領導編號和該部門的最低工資
    SELECT department_name,d.manager_id,MIN(salary) FROMdepartments 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 個數 FROMemployees e,jobs j WHERE e.`job_id` = j.`job_id` GROUP BY job_title ORDER BY 個數 DESC ;
- 可是實現三表連接：
  - 查詢員工名、部門名和所在的城市
    SELECT last_name,department_name,city FROMemployees e,departments d,locations l WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id` ;
- 非等值連接
  - 查詢員工的工資和工資級別
    SELECT salary,grade_level FROMemployees 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 FROMemployees e,employees m WHERE e.`manager_id` = m.`employee_id` ;
- 測試題：
  - 顯示員工表的最大工資，工資平均值
    SELECT MAX(salary),AVG(salary) FROMemployees ;
  - 查詢員工表的employee_id，job_id，last_name，按department_id降序，salary升序
    SELECT employee_id,job_id,last_name FROMemployees ORDER BY department_id DESC,salary ASC ;
  - 查詢員工表的job_id中包含a和e的，并且a在e的前面
    SELECT job_id FROMemployees 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 FROMemployees e INNER JOIN departments d ON e.`department_id` = d.`department_id` ;
    - 查詢名字中包含e的給員工名和工種名
      SELECT last_name,job_title FROMemployees e INNER JOIN jobs j ON e.`job_id` = j.`job_id` WHERE last_name LIKE "%e%" ;
    - 查詢部門個數>3的城市名和部門個數
      SELECT city,COUNT(*) 部門個數 FROMdepartments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY city HAVING 部門個數 > 3 ;
    - 查詢哪個部門的部門員工個數>3的部門名和員工個數，并按個數降序排序
    SELECT department_name,COUNT(*) 員工個數 FROMdepartments 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 FROMemployees 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 FROMemployees e INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
    - 查詢每個工資級別>20的個數，并且按工資級別降序
      SELECT COUNT(*),grade_level FROMemployees 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 FROMemployees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;
    - 查詢姓名中包含字符k的員工的名字、上級的名字
      SELECT e.last_name,m.last_name FROMemployees 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.* FROMbeauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.`id` IS NULL ;
  - 查詢哪個部門沒有員工
  - 左外：
    SELECT d.*,e.employee_id FROMdepartments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL ;
  - 右外：
    SELECT d.*,e.employee_id FROMemployees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL ;
  - 全外連接
    - mysql不支持
    - 案例：
      SELECT b.*,bo.* FROMbeauty b FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id ;
  - 交叉連接（也就是笛卡爾乘積）
    - 案例：
      SELECT b.*,bo.* FROMbeauty b CROSS JOIN boys bo ;
sql92 和 sql99 pk
- 功能：sql99支持的較多
- 可讀性：sql99實現連接條件和篩選條件的分離，可讀性較高
練習：
- 查詢編號>3的女神的男朋友信息，如果有則列出詳細信息，如果沒有，則用null填充
  SELECT a.id,a.name,b.* FROMbeauty a LEFT JOIN boys b ON a.`boyfriend_id` = b.`id` WHERE a.`id` > 3 ;
- 查詢哪個城市沒有部門
  SELECT city,d.* FROMdepartments 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.* FROMdepartments 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 FROMemployees WHERE last_name = 'Abel' ;
案例2：返回job_id與141號員工相同，salary比143員工多的員工，姓名，job_id，工資
SELECT last_name,job_id,salary FROMemployees WHERE job_id = (SELECT job_id FROMemployees WHERE employee_id = 141) AND salary > (SELECT salary FROMemployees WHERE employee_id = 143) ;
案例3：返回公司工資最少的員工的last_name, job_id和salary
SELECT last_name,job_id,salary FROMemployees WHERE salary = (SELECT MIN(salary) FROMemployees) ;
案例4：查詢最低工資大于50號部門的最低工資的部門id和其最低工資
SELECT MIN(salary),e.`department_id` FROMemployees e GROUP BY e.`department_id` HAVING MIN(salary) > (SELECT MIN(salary) FROMemployees WHERE department_id = 50) ;

列子查詢

（多行子查詢）
多行比較操作符：

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

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

ALL：和子查詢返回的所有值比較
- 案例1：返回location_id是1400或1700的部門中的所有員工姓名
  SELECT last_name FROMemployees WHERE department_id IN (SELECT DISTINCT department_id FROMdepartments WHERE location_id IN (1400, 1700)) ;
- 案例2：返回其他工種中比job_id為‘IT_PROG’工種任一工資低的員工的員工號、姓名、job_id以及salary
  SELECT employee_id,last_name,job_id,salary FROMemployees WHERE salary < ANY (SELECT DISTINCT salary FROMemployees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG' ;
  或者用max代替any
  SELECT employee_id,last_name,job_id,salary FROMemployees WHERE salary < (SELECT MAX(salary) FROMemployees 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 FROMemployees WHERE salary < ALL (SELECT DISTINCT salary FROMemployees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG' ;
  或者用min代替all
  SELECT employee_id,last_name,job_id,salary FROMemployees WHERE salary < (SELECT MIN(salary) FROMemployees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG' ;

行子查詢

結果集一行多列或多行多列
案例1：查詢員工編號最少并且工資最高的員工信息
SELECT * FROMemployees WHERE (employee_id, salary) = (SELECT MIN(employee_id),MAX(salary) FROMemployees) ;

select后面

僅僅支持標量子查詢
案例1：查詢每個部門的員工個數
SELECT d.*,(SELECT COUNT(*) FROMemployees e WHERE e.department_id = d.department_Id) 個數 FROMdepartments d ;
案例2：查詢員工號=102的部門名
SELECT (SELECT department_name FROMdepartments 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 FROMemployees 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 FROMdepartments d WHERE EXISTS (SELECT * FROMemployees e WHERE d.`department_id` = e.`department_id`) ;
用in更簡單
SELECT department_name FROMdepartments d WHERE d.`department_id` IN (SELECT department_id FROMemployees e) ;
習題集
查詢和zlotkey相同部門的員工姓名和工資
SELECT last_name,salary FROMemployees WHERE department_id = (SELECT department_id FROMemployees e WHERE e.`last_name` = 'Zlotkey') ;
查詢工資比公司平均工資高的員工的員工號，姓名和工資
SELECT employee_id,last_name,salary FROMemployees e WHERE e.`salary` > (SELECT AVG(salary) FROMemployees) ;
查詢各部門中工資比本部門平均工資高的員工的員工號，姓名和工資
SELECT employee_id,last_name,salary FROMemployees e INNER JOIN (SELECT AVG(salary) ag,department_id FROMemployees GROUP BY department_id) nt ON nt.department_id = e.department_id WHERE salary > ag ;
查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
SELECT employee_id,last_name FROMemployees WHERE department_id IN (SELECT DISTINCT department_id FROMemployees WHERE last_name LIKE '%u%') ;
查詢在部門的location_id為1700的部門工作的員工的員工號
SELECT employee_id FROMemployees WHERE department_id IN (SELECT DISTINCT department_id FROMdepartments WHERE location_id = 1700) ;
查詢管理者是King的員工姓名和工資
SELECT last_name,salary FROMemployees WHERE manager_id IN (SELECT employee_id FROMemployees WHERE last_name = 'K_ing') ;
查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列，列名為姓.名
SELECT CONCAT(nt.first_name, nt.last_name) "姓.名" FROM(SELECT first_name,last_name FROMemployees WHERE salary = (SELECT MAX(salary) FROMemployees)) 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 * FROMemployees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10 ;
經典案例1：
查詢工資最低的員工信息：last_name, salary
SELECT last_name,salary FROMemployees WHERE salary = (SELECT MIN(salary) FROMemployees) ;
查詢平均工資最低的部門信息
SELECT * FROMdepartments WHERE department_id = (SELECT department_id FROMemployees GROUP BY department_id ORDER BY AVG(salary) ASC LIMIT 1) ;
查詢平均工資最低的部門信息和該部門的平均工資
SELECT d.*,dd.ag FROMdepartments d INNER JOIN (SELECT AVG(salary) ag,department_id FROMemployees GROUP BY department_id ORDER BY ag LIMIT 1) dd ON d.`department_id` = dd.department_id ;
查詢平均工資最高的job信息
SELECT * FROMjobs j WHERE j.`job_id` = (SELECT job_id FROMemployees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1) ;
查詢平均工資高于公司平均工資的部門有哪些
SELECT AVG(salary) ag,department_id FROMemployees GROUP BY department_id HAVING ag > (SELECT AVG(salary) FROMemployees) ;
查詢出公司中所有manager的詳細信息
SELECT * FROMemployees WHERE employee_id IN (SELECT DISTINCT manager_id FROMemployees WHERE manager_id IS NOT NULL) ;
各個部門中，最高工資中，最低的那個部門的最低工資是多少
SELECT MIN(salary) FROMemployees WHERE department_id = (SELECT department_id FROMemployees GROUP BY department_id ORDER BY MAX(salary) ASC LIMIT 1) ;
查詢平均工資最高的部門的manager的詳細信息
SELECT last_name,department_id,email,salary FROMemployees WHERE employee_id = (SELECT DISTINCT manager_id FROMemployees WHERE department_id = (SELECT department_id FROMemployees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1) AND manager_id IS NOT NULL) ;

9. 聯合查詢

union：聯合，合并，將多條查詢語句的結果合并成一個結果
引入案例：查詢部門編號>90或郵箱包含a的員工信息
SELECT * FROMemployees WHERE email LIKE "%a%" OR department_id > 90 ;
用聯合查詢為：
SELECT * FROMemployees WHERE email LIKE "%a%" UNION SELECT * FROMemployees 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的列必須插入值?？梢詾閚ull的列如何插入值？
  方式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 SETphone = '109090909' WHERE NAME LIKE '唐%' ;
案例2：修改boys表中id號位2的名稱為張飛，魅力值為10
UPDATE boys SETboyname = '張飛',usercp = 10 WHERE id = 2 ;

修改多表的記錄

案例1：修改張無忌的女朋友的手機號為114
UPDATEboys 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 FROMbeauty WHERE phone LIKE '%9' ;
- 案例2：刪除張無忌的女朋友的信息
  DELETE be FROMbeauty be INNER JOIN boys b ON b.`id` = be.`boyfriend_id` WHERE b.`boyName` = '張無忌' ;
- 案例3：刪除黃曉明的信息以及他女朋友的信息
  DELETE b,be FROMbeauty 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 ;
- 修改表名
  ALTER TABLE author RENAME TO book_author ;
表的刪除
- 語法：drop table if exists 表名；
- 查看有哪些表：show tables；
- if exists 只能在庫，表的創建和刪除的時候使用，列的操作不能使用。
- 通用的寫法：
  DROP DATABASE IF EXISTS 舊庫名; CREATE DATABASE 新庫名;DROP TABLE IF EXISTS 舊表名; CREATE TABLE 表名();
表的復制
- 僅僅復制表的結構
  CREATE TABLE copy LIKE book_author ;
- 復制表的結構+數據
  CREATE TABLE copy2 SELECT * FROMbook_author ;
- 只復制部分數據
  CREATE TABLE copy3 SELECT id,au_name FROMbook_author WHERE nation = '中國' ;
- 僅僅復制某些字段（部分結構）：設置where不滿足，那么就沒有數據
  CREATE TABLE copy4 SELECT id,au_name FROMbook_author WHERE 0 ;
習題集
- 創建表dept1
  USE myemployees; CREATE TABLE dept1 (id INT (7), NAME VARCHAR (25)) ;
- 將表departments中的數據插入新表dept2中
  CREATE TABLE dept2 SELECT department_id,department_name FROMdepartments ;
- 創建表emp5
  CREATE TABLE emp5 (id INT (7),first_name VARCHAR (25),last_name VARCHAR (25),dept_id INT (7) ) ;
- 將last_name的長度修改為50
  ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);
- 根據表employees創建employee2
  CREATE TABLE employee2 LIKE employees ;
- 刪除表emp5
  DROP TABLE IF EXISTS emp5;
- 將表empoyees2重命名為emp5
  ALTER TABLE employee2 RENAME TO emp5 ;
- 在表dept和emp5中添加新列test_column，并檢查所做的操作
  ALTER TABLE emp5 ADD COLUMN test_column INT ; DESC emp5;
- 直接刪除表emp5中的列dept_id
  ALTER TABLE emp5 DROP COLUMN dept_id ;

3. 常見的數據類型

數值型：
- 整型
- 浮點型
  - 定點數
  - 浮點數
字符型：
- 較短的文本：char、varchar
- 較長的文本：text、blob（較長的二進制數據）
日期型：

整型

分類：Tinyint、Smallint、Mediumint、int/integer、bigint
特點
- 如果不設置無符號還是有符號，默認是有符號，如果想設置無符號，需要添加unsigned關鍵字
- 如果插入的數值超出了整型的范圍，會報out of range異常，并且插入臨界值
- 如果不設置長度，會有默認的長度，長度代表了顯示的最大寬度，如果不夠會用0在左邊填充，但必須搭配zerofill使用，并且默認變為無符號的整型！

小數

分類
- 浮點型
  - float（M，D）
  - double（M，D）
- 定點型
  - dec（M，D）
  - decimal（M，D）
特點
- M：整數部位+小數部位
- D：小數部位
- 如果超過范圍，則插入臨界值
- M和D都可以省略，如果是decimal，則M默認為10，D默認為0
- 如果是float和double，則會根據插入的數值的精度來決定精度
- 定點型的精確度較高，如果要求插入數值的精度較高如貨幣運算等則考慮使用定點型
原則：所選擇的類型越簡單越好，能保存數值的類型越小越好

字符型

較短的文本：char、varchar
較長的文本：text，blob
特點：
- 寫法 M的意思特點空間耗費效率
- char char(M) 最大的字符數，可以省略，默認為1 固定長度的字符比較耗費高
- varchar varchar(M) 最大的字符數，不可以省略可變長度的字符比較節省低

其他類型
- binary和varbinary用于保存較短的二進制
- enum：枚舉，eg. enum('男', '女')
- set：集合，eg. set('a', 'b', 'c', 'd')

日期型

分類：
- date：只保存日期
- time：值保存時間
- year：值保存年
- datetime：保存日期+時間
- timestamp：保存日期+時間，更能反映真實時間
特點：
- ? 字節范圍時區等的影響
- datetime 8 1000-9999 不受
- timestamp 4 1970-2038 受

4. 常見約束

含義：一種限制，用于限制表中的數據，為了保證表中的數據的準確和可靠性
分類：六大約束
- not null：非空，用于保證該字段的值不能為空。比如姓名、學號等。
- default：默認，用于保證該字段有默認值。比如性別。
- primary key：主鍵，用于保證該字段的值具有唯一性，并且非空。比如學號、員工編號等。
- unique：唯一，用于保證該字段的值具有唯一性，可以為空。比如座位號。
- check：檢查約束【mysql中不支持】。不日年齡、性別。
- foreign key：外鍵，用于限制兩個表的關系，用于保證該字段的值必須來自于主表的關聯列的值。在從表添加外鍵約束，用于應用主表中某列的值。比如學生表的專業編號，員工表的部門編號，員工表的工種編號。
添加約束的時機：
- 創建表時
- 修改表時
約束添加的分類：
- 列級約束：六大約束語法上都支持，但外鍵約束沒有效果
- 表級約束：除了非空、默認，其他的都支持
  位置支持的約束類型是否可以起約束名
  列級約束列的后面語法都支持，但外鍵沒有效果不可以
  表級約束所有列的下面默認和非空不支持，其他支持可以（主鍵沒有效果）

創建表時添加約束

添加列級約束
CREATE DATABASE students; USE students;CREATE TABLE stuinfo (id INT PRIMARY KEY,stuname VARCHAR (20) NOT NULL, # 非空gender CHAR(1) CHECK (gender = '男' OR gender = '女'),seat INT UNIQUE, # 唯一age INT DEFAULT 18, # 默認majorID INT REFERENCES major (id) ) ;CREATE TABLE major (id INT PRIMARY KEY,majorName VARCHAR (20) ) ;DESC stuinfo; SHOW INDEX FROM stuinfo;
- 語法：直接在字段名和類型后面追加約束類型即可。
- 只支持：默認、非空、主鍵、唯一（除了外鍵都支持）
添加表級約束
DROP TABLE IF EXISTS stuinfo ;CREATE TABLE stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT,CONSTRAINT pk PRIMARY KEY(id), CONSTRAINT uq UNIQUE(seat), CONSTRAINT ck CHECK(gender='男' OR gender='女'), CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) );SHOW INDEX FROM stuinfo;
- 語法：在各個字段的最下面
  
  【constraint 約束名】約束類型（字段名）
- 除了非空、默認，其他的都支持
通用的寫法：
CREATE TABLE IF NOT EXISTS stuinfo (id INT PRIMARY KEY,stuname VARCHAR (20) NOT NULL,gender CHAR(1),seat INT UNIQUE,age INT DEFAULT 18,majorID INT,CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id) ) ;
表級約束pk列級約束
支持類型可以起約束名與否
列級約束除了外鍵不可以
表級約束除了非空和默認可以，但對主鍵無效
列級約束可以在一個字段上追加多個，中間用空格隔開，沒有順序要求
主鍵和唯一的大對比
- 保證唯一性是否允許為空一個表中可以有多少個是否允許組合
- 主鍵 √ × 至多有1個 √，但不推薦
- 唯一 √ √ 可以有多個 √，但不推薦
外鍵特點
- 要求在從表設置外鍵關系
- 從表的外鍵列的類型和主表的關聯列的類型要求一致或兼容，名稱無要求
- 主表的關聯列必須是一個key（一般是主鍵或唯一）
- 插入數據時，先插入主表，再插入從表
- 刪除數據時，先刪除從表，再刪除主表
  - 方式1：級聯刪除
    ALTER TABLE stuinfo ad CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
    刪除的時候，主表和從表對應的行都刪了。
  - 方式2：級聯置空
    ALTER TABLE stuinfo ad CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
    刪除的時候，主表對應的行被刪除了，從表引入的地方變為空值null。

修改表時添加約束

添加列級約束：alter table 表名 modify column 字段名字段類型新約束；
添加表級約束：alter table 表名 add 【constraint 約束名】約束類型（字段名）【外鍵的引用】；
添加非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
添加默認約束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
添加主鍵
# 列級約束的寫法 ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY; # 表級約束的寫法 ALTER TABLE stuinfo ADD PRIMARY KEY(id);
添加唯一
# 列級約束的寫法 ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE; # 表級約束的寫法 ALTER TABLE stuinfo ADD UNIQUE(seat);
添加外鍵
ALTER TABLE (CONSTRAINT fk_stuinfo_major) stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

修改表時刪除約束

刪除非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
刪除默認約束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
刪除主鍵
ALTER TABLE stuinfo DROP PRIMARY KEY;
刪除唯一
ALTER TABLE stuinfo DROP INDEX seat;
刪除外鍵
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
總結
非空

添加非空：alter table 表名 modify column 字段名字段類型 not null；

刪除非空：alter table 表名 modify column 字段名字段類型；
默認

添加默認：alter table 表名 modify column 字段名字段類型 default；

刪除默認：alter table 表名 modify column 字段名字段類型；
主鍵

添加主鍵：alter table 表名 add 【constraint 約束名】 primary key（字段名）；

刪除主鍵：alter table 表名 drop primary key；
唯一

添加唯一：alter table 表名 add 【constraint 約束名】unique（字段名）；

刪除唯一：alter table 表名 drop index 索引名；
外鍵

添加外鍵：alter table 表名 add 【constraint 約束名】foreign key（字段名） references 主表（被引用列）；

刪除唯一：alter table 表名 drop foreign key 約束名；

5. 標識列

又稱為自增長列
含義：可以不用手動的插入值，系統提供默認的序列值
特點：
- 標識列必須和主鍵搭配嗎？不一定，但要求是一個key。
- 一個表可以有幾個標識列？至多一個。
- 標識列的類型？只能是數值型（int（一般是int），float，double）
- 標識列可以通過**SET auto_increment_increment = 1;**設置步長；可以通過手動插入值設置起始值。
創建表時設置表時列

create table 表（

? 字段名字段類型約束 auto_increment

）
CREATE TABLE tab_identity (id INT PRIMARY KEY AUTO_INCREMENT,NAME varcahr (20) ) ;
設置表時列的步長
SHOW VARIABLES LIKE '%auto_increment%'; SET auto_increment_increment = 3;
設置表時列的起始值：想在什么地方更改自增長列的值，則手動插入值（不手動的時候，值是null）。
修改表時設置標識列

alter table 表 modify column 字段名字段類型約束 auto_increment
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
修改表時刪除標識列

alter table 表 modify column 字段名字段類型約束
ALTER TABLE tab_identity MODIFY COLUMN id INT;

TCL（Transaction Control Language）事務控制語言

事務的含義：一個或一組sql語句組成一個執行單元，這個執行單元要么全部執行，要么全部不執行。
存儲引擎：在mysql中的數據用各種不同的技術存儲在文件（或內存）中。
- 通過show engines來查看mysql支持的存儲引擎。
- 在mysql中用的最多的存儲引擎有：innodb、myisam、memory等。其中innodb支持事務，而myisam、memory等不支持事務。
事務的ACID屬性
原子性（Atomicity）：原子性是指事務是一個不可分割的工作單位，事務中的操作要么都發生，要么都不發生。
一致性（Consistency）：事務必須使數據庫從一個一致性狀態變換到另外一個一致性狀態。
隔離性（Isolation）：事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的數據對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾。
持久性（Durability）：持久性是指一個事務一旦被提交，它對數據庫中數據的改變就是永久性的，接下來的其他操作和數據庫故障不應該對其有任何影響。
事務的創建
- 隱式事務：事務沒有明顯的開啟和結束的標記。比如insert、update、delete語句
- 顯式事務：事務具有明顯的開啟和結束的標記
  - 前提：必須先設置自動提交功能為禁用
    SET autocommit=0;
  - 步驟1：開啟事務
    SET autocommit=0; START TRANSACTION;（可選）
  - 步驟2：編寫事務中的sql語句(select insert update delete，只有增刪改查，不包括DDL語言)
    
    語句1；
    
    語句2；
    
    …
  - 步驟3：結束事務
    
    commit；提交事務
    
    rollback；回滾事務
- savepoint 結點名：設置保存點
  SET autocommit = 0 ;START TRANSACTION; DELETE FROM account WHERE id=25; SAVEPOINT a; DELETE FROM accound WHERE id=28; ROLLBACK TO a;
- delete和truncate在事務使用時的區別
  - delete刪除后支持回滾
  SET autocommit = 0 ; START TRANSACTION; DELETE FROM account; ROLLBACK;
  - truncate刪除后不支持回滾
  SET autocommit = 0 ; START TRANSACTION; TRUNCATE TABLE account; ROLLBACK;
數據庫的隔離級別
- 對于同時運行的多個事務，當這些事務訪問數據庫中相同的數據時，如果沒有采取必要的隔離機制，就會導致各種并發問題：
  - 臟讀：對于兩個事務T1，T2。T1讀取了已經被T2更新但還沒有被提交的字段之后，若T2回滾，T1讀取的內容就是臨時且無效的。主要是其他事務更新的數據
  - 不可重復讀：對于兩個事務T1，T2。T1讀取了一個字段，然后T2更新了該字段之后，T1再次讀取同一個字段，值就不同了。
  - 幻讀：對于兩個事務T1，T2。T1從一個表中讀取了一個字段，然后T2在該表中插入了一些新的行之后，如果T1再次讀取同一個表，就會多出幾行。主要是其他事務插入的數據
- 數據庫事務的隔離性：數據庫系統必須具有隔離并發運行各個事務的能力，使他們不會相互影響，避免各種并發問題。
- **一個事務與其他事務隔離的程度稱為隔離級別。**數據庫規定了多種事務隔離級別，不同隔離級別對應不同的干擾程度，隔離級別越高，數據一致性就越好，但并發性弱。
- 數據庫提供的4種事務隔離級別：
- Oracle支持2種事務隔離級別：READ COMMITED，SERIALIZABLE。Oracle默認的事務隔離級別是：READ COMMITED。
- Mysql支持4種事務隔離級別。Mysql默認的事務隔離級別為：REPEATABLE READ。
- 每啟動一個mysql程序，就會獲得一個單獨的數據庫連接，每個數據庫連接都有一個全局變量@@tx_isolation，表示當前事務隔離級別。
- 查看當前的隔離級別：select @@tx_isolation;
- 設置當前mysql連接的隔離級別：set transaction isolation level read committed;
- 設置數據庫系統的全局的隔離級別：set global transaction isolation level read committed;

視圖

含義：虛擬表，和普通表一樣使用。并且使用視圖時動態生成的，值保存了sql邏輯，不保存成查詢結果。
mysql5.1版本出現的新特性，是通過表動態生成的數據
應用場景：
- 多個地方用到同樣的查詢結果
- 該查詢結果使用的sql語句較復雜
案例1：查詢姓張的學生名和專業名
# 普通寫法 SELECT stuname,majorname FROMsuinfo s INNER JOIN major m ON s.majorid = m.`id` WHERE s.stuname LIKE '張%' ;# 視圖寫法 CREATE VIEW v1 AS SELECT stuname,majorname FROMsuinfo s INNER JOIN major m ON s.majorid = m.`id` ;SELECT * FROMv1 WHERE stuname LIKE '張%' ;
創建視圖
- 語法：
  
  create view 視圖名 as 查詢語句；
視圖的好處：
- 重用sql語句
- 簡化復雜的sql操作，不必知道它的查詢細節
- 保護數據，提高安全性
練習題：
- 習題1：查詢姓名中包含a字符的員工名、部門名和工種信息
  CREATE VIEW myv1 AS SELECT last_name,department_name,job_title FROMemployees e JOIN departments d ON e.`department_id` = d.`department_id` JOIN jobs j ON j.`job_id` = e.`job_id` ;SELECT * FROMmyv1 WHERE last_name LIKE '%a%' ;
- 習題2：查詢各部門的平均工資級別
  # 創建視圖查看每個部門的平均工資 CREATE VIEW myv2 AS SELECT AVG(salary) ag,department_id FROMemployees GROUP BY department_id ;# 使用 SELECT myv2.`ag`,g.grade_level FROMmyv2 JOIN job_grades g ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
- 習題3：查詢平均工資最低的部門信息
  # 用習題2的視圖myv2 SELECT * FROMmyv2 ORDER BY ag LIMIT 1 ;
- 習題4：查詢平均工資最低的部門名和工資
  CREATE VIEW myv3 AS SELECT * FROMmyv2 ORDER BY ag LIMIT 1 ;SELECT d.*,m.`ag` FROMmyv3 m JOIN departments d ON m.`department_id` = d.`department_id` ;
視圖的修改
- 方式1：create or replace view 視圖名 as 查詢語句；
- 方式2：alter view 視圖名 as 查詢語句；
刪除視圖
- 語法：drop view 視圖名，視圖名，…；
查看視圖
- 語法：
  
  show create view 視圖名；
  
  desc 視圖名；
案例1：創建一個視圖emp_v1，要求查詢電話號碼以‘011’開頭的員工姓名和工資、郵箱
CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,salary,email FROMemployees WHERE phone_number LIKE '011%' ;
案例2：創建視圖emp_v2，要求查詢部門的最高工資高于12000的部門信息
CREATE OR REPLACE VIEW emp_v2 AS SELECT MAX(salary) mx,department_id FROMemployees GROUP BY department_id HAVING MAX(salary) > 12000 ;SELECT * FROMemp_v2 ;SELECT d.*,m.mx FROMdepartments d JOIN emp_v2 m ON m.department_id = d.`department_id` ;
視圖的更新
- 增、刪、改（視圖基于的表也會發生更改）
  CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email FROMemployees;SELECT * FROM myv1;# 插入 INSERT INTO myv1 VALUES('張飛', 'zhangfei');# 修改 UPDATE myv1 SET last_name='張無忌' WHERE last_name = '張飛';# 刪除 DELETE FROM myv1 WHERE last_name='張無忌';
- 視圖的可更新性和視圖中查詢的定義有關系，以下類型的視圖是不能更新的。（注意：視圖一般用于查詢，而不是更新。）
  - 包含以下關鍵字的sql語句：分組函數、distinct、group by、having、union或union all
  - 常量視圖
  - select中包含子查詢
  - join
  - from 一個不能更新的視圖
  - where子句的子查詢應用了from子句中的表
視圖和表的對比
創建語法的關鍵字是否實際占用物理空間使用
視圖 create view 只是保存了sql邏輯增刪改查，一般不能增刪改
表 create table 保存了實際的數據增刪改查
測試題
- 題1：創建表
  CREATE TABLE book (bid INT PRIMARY KEY,bname VARCHAR (20) UNIQUE nut NULL,price FLOAT DEFAULT 10,btypeid INT,FOREIGN KEY (btypeid) REFERENCES booktype (id) ) ;
- 題2：開啟事務，向表中插入1行數據，并結束
  SET autocommit = 0 ; INSERT INTO book(bid, bname, price, btypeid) VALUES(1, '小李飛刀', 100, 1); COMMIT;
- 題3：創建視圖，實現查詢價格大于100的書名和類型名
  CREATE VIEW myv1 AS SELECT bname,NAME FROMbook b INNER JOIN booktype t ON b.btypeid = t.id WHERE price > 100 ;
- 題4：修改視圖，實現查詢價格在90-120之間的書名和價格
  CREATE OR REPLACE VIEW myv1 AS SELECT bname,price FROMbook WHERE price BETWEEN 90 AND 120 ;
- 題5：刪除剛才創建的視圖
  DROP VIEW myv1;

變量

系統變量
- 說明：變量由系統提供，不是用戶定義，屬于服務器層面
- 注意：如果是全局級別，則需要加global；如果是會話級別，則需要加session；如果不寫，則默認session
- 使用的語法：
  - 查看所有的系統變量
    SHOW GLOBAL|【SESSION】 VARIABLES;
  - 查看滿足條件的部分系統變量
    SHOW GLOBAL|【SESSION】 VARIABLES LIKE '%char%';
  - 查看指定的某個系統變量的值
    SELECT @@GLOBAL|【SESSION】.系統變量名;
  - 為某個系統變量賦值
    - 方式一
      set GLOBAL|【SESSION】系統變量名 = 值;
    - 方式二
      set @@GLOBAL|【SESSION】.系統變量名 = 值;
- 分類：
  - 全局變量
    - 服務器層面上的，必須擁有super權限才能為系統變量賦值。
    - 作用域：服務器每次啟動將為所有的全局變量賦初始值，針對于所有的會話（連接）有效，但不能跨重啟。
    - 查看所有的全局變量
    SHOW GLOBAL VARIABLES;
    - 查看部分的全局變量
    SHOW GLOBAL VARIABLES LIKE ‘%char%’;
    - 查看指定的全局變量的值
    SELECT @@global.autocommit;
    SELECT @@global.tx_isolation;
    - 為某個指定的全局變量賦值- 方式1：``` set global autocommit=0;```- 方式2：``` SET @@global.autocommit=0;```
  - 會話變量
    - 服務器為每一個連接的客戶端都提供了系統變量。
    - 作用域：僅僅針對于當前會話（連接）有效。
    - 查看所有的會話變量
  SHOW 【SESSION】 VARIABLES;
```
- 查看部分的會話變量
SHOW 【SESSION】 VARIABLES LIKE ‘%char%’;
```
  - 查看指定的某個會話變量
  SELECT @@【SESSION.】autocommit;
```
- 為某個會話變量賦值
  - 方式1：
set session autocommit=0;
```
  - 方式2：```SET @@【session.】autocommit=0;```
自定義變量
- 變量是用戶自定義的，不是由系統定義的
- 使用步驟：聲明賦值使用（查看、比較、運算等）
- 分類
  - 用戶變量
    - 作用域：針對于當前會話（連接）有效，等同于會話變量的作用域
    - 應用在任何地方，也就是begin end里面或begin end的外面
  - 聲明并初始化（三種方式）
    set @用戶變量名=值； set @用戶變量名:=值；（推薦） select @用戶變量名:=值；
  - 賦值（更新用戶變量的值）
    - 方式1：通過set或select（同上）
      set @用戶變量名=值； set @用戶變量名:=值；（推薦） select @用戶變量名:=值；
      - 案例1：
        SET @name='John'; SET @name=100;
    - 方式2：通過select into
      select 字段 into 變量名 from 表；
      - 案例1：
        SELECT COUNT(*) INTO @count FROMemployees ;
  - 使用（查看用戶變量的值）
    
    select @用戶變量名；
  - 局部變量
    - 作用域：僅僅在定義它的begin end中有效
    - 應用在begin end中的第一句話
  - 聲明
    
    declare 變量名類型；
    
    declare 變量名類型 default 值；
  - 賦值
    - 方式1：通過set或select（同上）
      set 局部變量名=值； set 局部變量名:=值；（推薦） select @局部變量名:=值；
    - 方式2：通過select into
      select 字段 into 局部變量名 from 表；
  - 使用
    
    select 局部變量名；
  - 對比用戶變量和局部變量：
    作用域定義和使用的位置語法
    用戶變量當前會話會話中的任何地方必須加@符號，不用限定類型
    局部變量 begin end中只能在begin end中，且為第一句話一般不用加@符號，需要限定類型
  - 案例1：聲明兩個變量并賦初始值，求和，并打印
    - 用戶變量
      SET @m=1; SET @n=2; SET @sum=@m+@n; SELECT @sum;
    - 局部變量
      # 報錯 DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 2; DECLARE SUM INT; SET SUM=m+n; SELECT SUM;

存儲過程和函數

類似于java中的方法
好處：
- 提高代碼的重用性
- 簡化操作

1. 存儲過程

含義：一組預先編譯好的sql語句的集合，理解成批處理語句
好處：
- 提高代碼的重用性
- 簡化操作
- 減少了編譯次數并且減少了和數據庫服務器的連接次數，提高了效率
語法：
- 創建語法
  CREATE PROCEDURE 存儲過程名(參數列表) BEGIN存儲過程體(一組合法的SQL語句) END
- 注意
  - 參數列表包含三部分：參數模式，參數名，參數類型
    - 舉例：in stuname varchar(20)
    - 參數模式：
      - in：該參數可以作為輸入，也就是該參數需要調用方傳入值
      - out：該參數可以作為輸出，也就是該參數可以作為返回值
      - inout：該參數既可以作為輸入又可以作為輸出，也就是該參數既需要傳入值，又需要返回值
  - 如果存儲過程體僅僅只有一句話，begin end可以省略
  - 存儲過程體中的每條SQL語句的結尾要求必須加分號
  - 存儲過程的結尾可以使用 DELIMITER 重新設置
    - 語法：DELIMITER 結束標記
    - 案例：
      DELIMITER $
- 調用語法
  - call 存儲過程名(實參列表)；
空參列表
- 案例1：插入到admin表中五條記錄
  SELECT * FROM admin; DELIMITER $CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(username, `password`) VALUES ('john1', '0000'), ('asd', '0000'), ('joqqhn1', '0000'), ('qa', '0000'), ('ww', '0000'); END $CALL myp1()$
創建帶in模式參數的存儲過程
- 案例1：創建存儲過程實現：根據女神名，查詢對應的男神信息
  CREATE PROCEDURE myp2(IN beautyname VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyname; END $CALL myp2('柳巖')$ CALL myp2('王語嫣')$
- 案例2：創建存儲過程實現，用戶是否登錄成功
  CREATE PROCEDURE myp4(IN username VARCHAR(20), IN passward VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0; # 聲明并初始化SELECT COUNT(*) INTO result # 賦值 FROM admin WHERE admin.username = username AND admin.password = PASSWORD;SELECT IF(result>0, '成功！', '失敗！'); # 使用 END $CALL myp4('張飛', '8888')$
創建帶out模式的存儲過程
- 案例1：根據女神名，返回對應的男神名
  CREATE PROCEDURE myp5(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20)) BEGIN SELECT bo.boyname INTO boyname FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyname; END $CALL myp5('王語嫣', @bname)$ SELECT @bname$
- 案例2：根據女神名，返回對應的男神名和男神魅力值
  CREATE PROCEDURE myp6(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20), OUT usercp INT) BEGIN SELECT bo.boyname, bo.usercp INTO boyname, usercp FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name=beautyname; END $CALL myp6('王語嫣', @bname, @usercp)$ SELECT @bname, @usercp$
創建帶inout模式參數的存儲過程
- 案例1：傳入a和b兩個值，最終a和b都翻倍并返回
  CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT) BEGIN SET a=a*2; SET b=b*2; END $SET @m=10$ SET @n=20$ CALL myp8(@m,@n)$ SELECT @m,@n$
練習題
- 習題1：創建存儲過程實現傳入用戶名和密碼，插入到admin表中
  CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginpwd VARCHAR(20)) BEGIN INSERT INTO admin(admin.`username`, PASSWORD) VALUES(username, loginpwd); END $CALL test_pro1('litian', '1234')$ SELECT * FROM admin$
- 習題2：創建存儲過程實現傳入女神編號，返回女神名稱和女神電話
  CREATE PROCEDURE test_pro2(IN id INT, OUT NAME VARCHAR(20), OUT phone VARCHAR(20)) BEGIN SELECT b.name, b.phone INTO NAME, phone FROM beauty b WHERE b.id=id; END $SET @n=''$ SET @m=''$ CALL test_pro2(1, @n, @m)$ SELECT @m,@n$
- 習題3：創建存儲過程來實現傳入兩個女神生日，返回大小
  CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT) BEGIN SELECT DATEDIFF(birth1, birth2) INTO result; END $CALL test_pro3('1990-2-3', NOW(), @result)$ SELECT @result$
存儲過程的刪除
- 語法：drop procedure 存儲過程名
  DROP PROCEDURE myp1;
- 不能同時刪除多個存儲過程
查看存儲過程的信息
- 語法：show create procedure 存儲過程名
  SHOW CREATE PROCEDURE myp2;
- 不能修改存儲過程中的語句，需要修改的話，就刪了重寫。
練習題
- 練習題1：創建存儲過程實現傳入一個日期，格式化成xx年xx月xx日并返回
  CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strdate VARCHAR(20)) BEGIN SELECT DATE_FORMAT(mydate, '%y年%m月%d天') INTO strdate; END $CALL test_pro4(NOW(), @str)$ SELECT @str $
- 練習題2：創建存儲過程實現傳入女神名稱，返回：女神 and 男神格式的字符串
  CREATE PROCEDURE test_pro5(IN beautyname VARCHAR(20), OUT str VARCHAR(50)) BEGIN SELECT CONCAT(beautyname, ' and ', IFNULL(boyname, 'null')) INTO str FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id WHERE b.name=beautyname; END $CALL test_pro5('王語嫣', @result)$ SELECT @result$
- 練習題3：創建存儲過程，根據傳入的起始索引和條目數，查詢beauty表的記錄
  CREATE PROCEDURE test_pro6(IN startindex INT, IN size INT) BEGIN SELECT * FROM beauty LIMIT startindex, size; END $CALL test_pro6(3, 3)$

2. 函數

好處：
- 提高代碼的重用性
- 簡化操作
- 減少了編譯次數并且減少了和數據庫服務器的連接次數，提高了效率
與存儲過程的區別：
- 存儲過程：可以有0個返回，也可以有多個返回（適合做批量插入、更新）
- 函數：有且僅有1個返回（適合處理數據后返回一個結果）
創建語法：

create function 函數名（參數列表） returns 返回類型

begin

函數體

end
- 注意事項：
  - 參數列表：包含兩部分：參數名參數類型，注意：沒有in，out，inout這種模式了
  - 函數體：肯定會有return語句，如果沒有會報錯
  - 如果return語句沒有放在函數體的最后也不報錯，但不建議
  - 函數體中僅有一句話，則可以省略begin end
  - 使用delimiter語句設置結束標記
調用語法：select 函數名(參數列表)
- 無參有返回
  - 案例1：返回公司的員工個數
    CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0;# 定義變量 SELECT COUNT(*) INTO c# 賦值 FROM employees; RETURN c; END $SELECT myf1()$
- 有參有返回
  - 案例1：根據員工名，返回他的工資
    CREATE FUNCTION myf2(empname VARCHAR(20)) RETURNS DOUBLE BEGIN SET @sal=0;# 定義用戶變量 SELECT salary INTO @sal# 賦值 FROM employees WHERE last_name = empname; RETURN @sal; END $SELECT myf2('kochhar')$
  - 案例2：根據部門名，返回該部門的平均工資
    CREATE FUNCTION myf3(deptname VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE sal DOUBLE; SELECT AVG(salary) INTO sal FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name=deptname;RETURN sal; END $SELECT myf3('IT')$
查看函數：
SHOW CREATE FUNCTION myf3;
刪除函數：
DROP FUNCTION myf3;
案例1：創建函數，實現傳入兩個float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT BEGIN DECLARE SUM FLOAT DEFAULT 0; SET SUM=num1+num2; RETURN SUM; END $SELECT test_fun1(1,2)$

流程控制結構

分類
- 順序結構：結構從上往下依次執行
- 分支結構：程序從兩條或多條路徑中選擇一條去執行
- 循環結構：程序在滿足一定條件的基礎上，重復執行一段代碼
分支結構
- if函數
  - 功能：實現簡單的雙分支
  - 語法：
    
    select if(表達式1，表達式2，表達式3)
  - 執行順序：
    
    如果表達式1成立，則if函數返回表達式2的值，否則返回表達式3的值
  - 應用環境：任何地方
- case結構
  - 情況1：類似于java中的switch語句，一般用于實現等值判斷
    - 語法：
      
      case 變量|表達式|字段
      
      when 要判斷的值 then 返回的值1或語句1；
      
      when 要判斷的值 then 返回的值2或語句2；
      
      …
      
      else 要返回的值n或語句n；
      
      end case；
  - 情況2：類似于java中的多重if語句，一般用于實現區間判斷
    - 語法：
      
      case
      
      when 要判斷的條件1 then 返回的值1或語句1；
      
      when 要判斷的條件2 then 返回的值2或語句2；
      
      …
      
      else 要返回的值n或語句n；
      
      end case；
  - 特點：
    - 可以作為表達式，嵌套在其他語句中使用，可以放在任何地方，begin end 中，或 begin end 的外面
    - 也可以作為獨立的語句去使用，只能放在begin end 中
    - 如果when中的值滿足或條件成立，則執行對應的then后面的語句，并且結束case
    - 如果都不滿足，則執行else中的語句或值
    - else可以省略，如果else省略了，并且所有的when條件都不滿足，則返回null
- 案例1：創建存儲過程，根據傳入的成績，來顯示等級，比如傳入的成績：90-100，顯示A；80-90：顯示B；60-80：顯示C；否則顯示D
  CREATE PROCEDURE test_case(IN score INT) BEGIN CASE WHEN score >= 90 AND score <= 100 THEN SELECT 'A'; WHEN score >=80 THEN SELECT 'B'; WHEN score >= 60 THEN SELECT 'C'; ELSE SELECT 'D'; END CASE; END $CALL test_case(95)$
- if結構
  - 功能：實現多重分支
  - 語法：
    
    if 條件1 then 語句1；
    
    elseif 條件2 then 語句2；
    
    …
    
    【else 語句n；】
    
    end if；
  - 應用場合：應用在begin end中
- 案例2：創建存儲過程，根據傳入的成績，來返回等級，比如傳入的成績：90-100，返回A；80-90：返回B；60-80：返回C；否則返回D
  CREATE FUNCTION test_if(score INT) RETURNS CHAR BEGIN IF score >= 90 AND score <= 100 THEN RETURN 'A'; ELSEIF score >=80 THEN RETURN 'B'; ELSEIF score >= 60 THEN RETURN 'C'; ELSE RETURN 'D'; END IF; END $SELECT test_if(86)$
循環結構
- 分類：while、loop、repeat
- 循環控制：
  - iterate：類似于continue，繼續；結束本次循環，繼續下一次
  - leave：類似于break，跳出；結束當前所在循環
- while
  - 語法：
    
    [標簽：】while 循環條件 do
    
    循環體；
    
    end while 【標簽】；
- loop
  - 語法：
    
    【標簽：】loop
    
    循環體；
    
    end loop 【標簽】；
  - 可以用來模擬簡單的死循環
- repeat
  - 語法：
    
    【標簽：】repeat
    
    循環體；
    
    until 結束循環的條件
    
    end repeat【標簽】；
- 對比：
  - 這人三種循環都可以省略名稱，但如果循環中添加了循環控制語句（leave或iterate）則必須添加名稱
  - loop：一般用于實現簡單的死循環
  - while：先判斷后執行
  - repeat：先執行后判斷，無條件至少執行一次
案例1：沒有添加循環控制語句
批量插入，根據次數插入到admin表中多條記錄
CREATE PROCEDURE pro_while1(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= insertcount DO INSERT INTO admin(username, `password`) VALUES(CONCAT('Rose', i), '666'); SET i=i+1; END WHILE; END $CALL pro_while1(100)$ SELECT * FROM admin;
案例2：添加leave語句
批量插入，根據次數插入到admin表中20條記錄
CREATE PROCEDURE test_while1(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i <= insertcount DO INSERT INTO admin(username, `password`) VALUES(CONCAT('xiaohua', i), '000'); IF i>=20 THEN LEAVE a; END IF; SET i=i+1; END WHILE a; END $CALL test_while1(100)$ SELECT * FROM admin;
案例3：添加iterate語句
批量插入，根據次數插入到admin表中多條記錄，只插入偶數次
CREATE PROCEDURE test_while2(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i <= insertcount DO SET i=i+1; IF MOD(i,2) != 0 THEN ITERATE a; END IF; INSERT INTO admin(username, `password`) VALUES(CONCAT('xiaohua', i), '000'); END WHILE a; END $CALL test_while2(100)$ SELECT * FROM admin;

案例4：已知表stringcontent，其中字段id 自增長；content varchar(20)，向該表中插入指定個數的隨機的字符串
USE test; DROP TABLE IF EXISTS stringcontent; CREATE TABLE stringcontent( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(20) );DELIMITER $ CREATE PROCEDURE test_randstr_insert(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 1;# 定義一個循環變量i，表示插入次數 DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'; DECLARE startindex INT DEFAULT 1;# 代表起始索引 DECLARE len INT DEFAULT 1;# 代表截取的字符的長度 WHILE i<=insertcount DO SET len=FLOOR(RAND()*(20-startindex+1)+1);# 產生一個隨機的整數，代表截取長度，1-(26-startindex+1) SET startindex=FLOOR(RAND()*26+1);# 產生一個隨機的整數，代表起始索引1-26 INSERT INTO stringcontent(content) VALUES(SUBSTR(str, startindex, len)); SET i=i+1; END WHILE; END $CALL test_randstr_insert(10)$ SELECT * FROM stringcontent; CALL test_randstr_insert(10)$ SELECT * FROM stringcontent;

我的CSDN：https://blog.csdn.net/qq_21579045

我的博客園：https://www.cnblogs.com/lyjun/

我的Github：https://github.com/TinyHandsome

紙上得來終覺淺，絕知此事要躬行~

歡迎大家過來OB~

by 李英俊小朋友

總結

以上是生活随笔為你收集整理的尚硅谷MySQL基础学习笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： AtCoder Beginner Con
下一篇： JarvisOJ Basic 握手包