# 數據庫小結
## 數據庫相關概念
? 1、DB：數據庫，保存一組有組織的數據的容器
2、DBMS：數據庫管理系統，又稱為數據庫軟件（產品），用于管理DB中的數據
3、SQL:結構化查詢語言，用于和DBMS通信的語言
## 數據庫存儲數據的特點
? 1、將數據放到表中，表再放到庫中
2、一個數據庫中可以有多個表，每個表都有一個的名字，用來標識自己。表名具有唯一性。
3、表具有一些特性，這些特性定義了數據在表中如何存儲，類似java中 “類”的設計。
4、表由列組成，我們也稱為字段。所有表都是由一個或多個列組成的，每一列類似java 中的”屬性”
5、表中的數據是按行存儲的，每一行類似于java中的“對象”。
## MySQL服務的啟動和停止
? 方式一：計算機——右擊管理——服務
方式二：通過管理員身份運行
net start 服務名（啟動服務）
net stop 服務名（停止服務）
## MySQL服務的登錄和退出
```mysql
方式一：通過mysql自帶的客戶端
只限于root用戶
方式二：通過windows自帶的客戶端
登錄：
mysql 【-h主機名 -P端口號 】-u用戶名 -p密碼
mysql -u root -p 以root用戶登錄
退出：
exit或ctrl+C
```
## 在端口中MySQL的常見命令
```mysql
1.查看當前所有的數據庫
show databases;
2.打開指定的庫
use 庫名;
3.查看當前庫的所有表
show tables;
4.查看其它庫的所有表
show tables from 庫名;
5.創建表
create table 表名(
列名 列類型,
列名 列類型，
。。。
);
6.查看表結構
desc 表名;
解決中文亂碼問題:set names gbk;
7.刪除信息
delete from 表名 where 條件
8.插入信息
insert into 表名 (字段1，字段2，...) values()
9.更新/修改信息
update 表名 set 條件
10.查詢信息
select * from 表名 ；* 表示所有信息，也可以只查幾個信息
```
```mysql
11.查看服務器的版本
方式一：登錄到mysql服務端
select version();
方式二：沒有登錄到mysql服務端
mysql --version
或
mysql --V
```
## MySQL的語法規范
? 1.不區分大小寫,但建議關鍵字大寫，表名、列名小寫
2.每條命令最好用分號結尾
3.每條命令根據需要，可以進行縮進 或換行
4.注釋
單行注釋：#注釋文字
單行注釋：-- 注釋文字
多行注釋：/* 注釋文字 */
## 數據庫查詢語句學習
### 1 基礎查詢
語法：
SELECT 要查詢的東西
【FROM 表名】;
特點：
1、查詢列表可以是：表中的字段、常量值、表達式、函數
2、查詢的結果是一個虛擬的表格
```mysql
1.查詢表中的單個字段
查詢員工的姓
SELECT last_name
FROM employees
2.查詢表中的多個字段
查詢員工的姓，工資，email
SELECT last_name,salary,email
FROM employees
3.查詢表中的所有字段
查詢員工所有信息
select *
from employees
```
### 2 條件查詢
條件查詢：根據條件過濾原始表的數據，查詢到想要的數據
語法：
select 要查詢的字段|表達式|常量值|函數
from 表
where 條件 ;
```mysql
1.等值條件 = 等于 <>不等于
查詢員工號為125的員工名與工資
select first_name,salary
from employees
where employee_id=125
2.< > <= >=條件
查詢部門id大于50的員工信息
select *
from employees
where department_id>50
3.邏輯連接條件
查詢部門號為50的并且名字中含有u的員工信息
select *
from employees
where department_id=50 and first_name like '%u%'
邏輯運算符有三種：
and（&&）:兩個條件如果同時成立，結果為true，否則為false
or(||)：兩個條件只要有一個成立，結果為true，否則為false
not(!)：如果條件成立，則not后為false，否則為true
4. 1 0 條件
在SQL中， 1表示 true， 0表示 false
select *
from employees
where 1
該語句會執行
5.模糊查詢條件，四個關鍵字
a like：特點：一般和通配符搭配使用
通配符：
% 任意多個字符,包含0個字符
_ 任意單個字符
案例1：查詢員工名中包含字符a的員工信息
select *
from employees
where last_name like '%a%'
b between and
特點：使用between and 可以提高語句的簡潔度
包含臨界值
兩個臨界值不要調換順序
案例：查詢員工編號在100到120之間的員工信息
SELECT *
FROM employees
WHERE employee_id >= 120 AND employee_id<=100
c in
含義：判斷某字段的值是否屬于in列表中的某一項
特點：使用in提高語句簡潔度
in列表的值類型必須一致或兼容
in列表中不支持通配符
案例：查詢員工的工種編號是 IT_PROG、AD_VP、AD_PRES中的一個員工名和工種編號
方法一
SELECT last_name,job_id
FROM employees
WHERE job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES'
方法二
SELECT last_name,job_id
FROM employees
WHERE job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES')
d is null
=或<>不能用于判斷null值
is null或is not null 可以判斷null值
案例：查詢沒有獎金的員工名和獎金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL
IS NULL:僅僅可以判斷NULL值，可讀性較高，建議使用
<=> :既可以判斷NULL值，又可以判斷普通的數值，可讀性較低
```
### 3 排序查詢
**關鍵字段：order by**
語法：
select 要查詢的東西
from 表
where 條件
order by 排序的字段|表達式|函數|別名 【asc|desc】
**ASC 表示升序，默認，可以不寫**
**DESC 表示降序**
**order by子句在查詢語句的最后面，除了limit子句**
```mysql
1、按單個字段排序
#按員工工資降序排序
SELECT *
FROM employees
ORDER BY salary DESC
2、添加篩選條件再排序
#查詢部門編號>=90的員工信息，并按員工編號降序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC
3、按表達式排序
#查詢員工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC
4、按別名排序
#查詢員工信息 按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC
5、按函數排序
#查詢員工名，并且按名字的長度降序
SELECT LENGTH(last_name),last_name
FROM employees
ORDER BY LENGTH(last_name) DESC
6、按多個字段排序
#查詢員工信息，要求先按工資降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC
-- 多字段排序，誰寫在前面就一誰為主排序，再以后面字段排序
```
### 4 分組函數
**特點：**
1、sum、avg一般用于處理數值型
max、min、count可以處理任何類型
2、以上**分組函數都忽略null值** **（除了count（*））** 3、可以和distinct搭配實現去重的運算 4、count函數的單獨介紹 一般使用count(*)用作統計行數 5、**和分組函數一同查詢的字段要求是group by后的字段**
**功能：用作統計使用，又稱為聚合函數或統計函數或組函數**
**分類：**
**sum 求和、avg 平均值、max 最大值 、min 最小值 、count 計算個數**
```mysql
1、簡單的使用
SELECT SUM(salary)
FROM employees;
SELECT AVG(salary)
FROM employees;
SELECT MIN(salary)
FROM employees;
SELECT MAX(salary)
FROM employees;
SELECT COUNT(salary)
FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 個數
FROM employees;
2、忽略 null 值
-- 使用分組函數時會忽略表中的 null 值的信息
3、 distinct 搭配
# 1000 1000 2000 2000 2000 5000 5000
# 1000 2000 5000 8000
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees
4、 count函數的介紹
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
#在每一條信息前面加一個*，計算*的總數
```
### 5 常見函數
概念：類似于java的方法，將一組邏輯語句封裝在方法體中，對外暴露方法名
**好處：1、隱藏了實現細節 2、提高代碼的重用性**
**調用：select 函數名(實參列表) 【from 表】**
#### 5類常見函數
##### 1、字符函數
```mysql
concat 拼接
#concat(字段1，字段2，..)
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees
substr截取子串
select substr('你傷害了我，還一笑而過',5)
#表示從第五個索引值開始截取，下標從1開始
select substr('你傷害了我，還一笑而過',1,5)
#表示從第一個索引值開始截取，截取長度為3
upper轉換成大寫
lower轉換成小寫
#案例：姓名中首字符大寫，其他字符小寫然后用_拼接，顯示出來
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
FROM employees
trim去前后指定的空格和字符
SELECT LENGTH(TRIM(' 張翠山 ')) ;
#去掉前后空格
SELECT TRIM('aa' FROM 'aaaaaaaaa張aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') ;
#去掉前后 aa,奇數就留一個，偶數全去掉
replace替換
SELECT REPLACE('來吧來吧相約98，來吧來吧，相約98','98','酒吧')
#將文字中的98替換為酒吧，傳3個參數
lpad左填充
rpad右填充
SELECT LPAD('變形金剛',2,'*') -- 變形
SELECT RPAD('變形金剛',2,'*') -- 變形
#也能實現截取，但不管lpad還是rpad都是從左邊開始截取
instr返回子串第一次出現的索引
length 獲取字節個數(utf-8一個漢字代表3個字節,gbk為2個字節)
#length 獲取參數值的字節個數
SELECT LENGTH('john');-- 4
SELECT LENGTH('張三豐hahaha') -- 15
```
##### 2、數學函數
```mysql
round 四舍五入
SELECT ROUND(-1.55);-- -2
SELECT ROUND(1.567,2);-- 1.57
rand 隨機數
floor向下取整
#返回<=該參數的最大整數
SELECT FLOOR(-9.99) -- -10
ceil向上取整
#返回>=該參數的最小整數
SELECT CEIL(-1.02) -- -1
mod取余
#mod(a,b)
SELECT mod(-10,3) -- -1
#除數有誤負號都會被忽略，當被除數有負號時，余數也有負號，
truncate截斷
#截取小數點個數
SELECT TRUNCATE(1.69999,1); -- 1.6
```
##### 3、日期函數
```mysql
now當前系統日期+時間
SELECT NOW();
curdate當前系統日期
SELECT CURDATE();
curtime當前系統時間
SELECT CURTIME();
str_to_date 將字符轉換成日期
#str_to_date 將字符通過指定的格式轉換成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
date_format將日期轉換成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
```
##### 4、流程控制函數
```mysql
1 if 處理雙分支
SELECT IF(10<5,'大','小');
#如果判斷為 true，輸出前面的參數，false輸出后面的參數
2 case語句 處理多分支
情況1：處理等值判斷
情況2：處理條件判斷
/*案例：查詢員工的工資，要求
部門號=30，顯示的工資為1.1倍
部門號=40，顯示的工資為1.2倍
部門號=50，顯示的工資為1.3倍
其他部門，顯示的工資為原工資
*/
SELECT salary 原始工資,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;
#條件結束后，要用 end 結尾 再起個別名
```
##### 5、其他函數
```mysql
SELECT VERSION(); -- 查看版本
SELECT DATABASE(); -- 查看數據庫
SELECT USER(); -- 查看用戶
```
### 6 分組查詢
**關鍵字段：GROUP BY**
語法：
select 查詢列表
from 表
【where 篩選條件】
group by 分組的字段
【order by 排序的字段】
特點：
1、和分組函數一同查詢的字段必須是group by后出現的字段
2、篩選分為兩類：分組前篩選和分組后篩選
連接的關鍵字
分組前篩選 原始表 group by前 where 分組后篩選 group by后的結果集 group by后 having
```mysql
-- where having 有什么區別 ？
-- where 主要用來對原始表中 有的字段 進行篩選 。
-- having 主要對原始表中不存在的字段 進行篩選 。
-- where 放在 group by 前面 -- having 放在 group by 后面 。 基本上只用在分組函數。
#案例 查詢每個工種的員工平均工資
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#案例 查詢郵箱中包含a字符的 每個部門的最高工資
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例：查詢哪個部門的員工個數>5
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
#按多個字段分組
#案例：查詢每個工種每個部門的最低工資,并按最低工資降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
```
### 7 多表連接查詢
```mysql
-- 笛卡爾乘積：如果連接條件省略或無效則會出現
-- 解決辦法：添加上連接條件
```
#### 一、傳統模式下的連接 ：等值連接——非等值連接
1.等值連接的結果 = 多個表的交集
2.n表連接，至少需要n-1個連接條件
3.多個表不分主次，沒有順序要求
4.一般為表起別名，提高閱讀性和性能
#### 二、sql99語法：通過join關鍵字實現連接
含義：1999年推出的sql語法
支持：
等值連接、非等值連接 （內連接）
外連接
交叉連接
語法：
select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on 連接條件
【inner|left outer|right outer|cross】join 表3 on 連接條件
【where 篩選條件】
【group by 分組字段】
【having 分組后的篩選條件】
【order by 排序的字段或表達式】
好處：語句上，連接條件和篩選條件實現了分離，簡潔明了！
#### 三、自連接
案例：查詢員工名和直接上級的名稱
sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
### 8 子查詢
含義：一條查詢語句中又嵌套了另一條完整的select語句，其中被嵌套的select語句，稱為子查詢或內查詢
在外面的查詢語句，稱為主查詢或外查詢
特點：
1、子查詢都放在小括號內
2、子查詢可以放在from后面、select后面、where后面、having后面，但一般放在條件的右側
3、子查詢優先于主查詢執行，主查詢使用了子查詢的執行結果
4、子查詢根據查詢結果的行數不同分為以下兩類：
① 單行子查詢
結果集只有一行
一般搭配單行操作符使用：> < = <> >= <=
非法使用子查詢的情況：
a、子查詢的結果為一組值
b、子查詢的結果為空
② 多行子查詢
結果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 屬于子查詢結果中的任意一個就行
any和all往往可以用其他查詢代替
```sql
分類：
按子查詢出現的位置：
select后面：
僅僅支持標量子查詢
from后面：
支持表子查詢 一般用完之后都要給子查詢起一個別名，把這個別名當做一張表來看。
where或 having 后面：★
標量子查詢（單行） √
列子查詢 （多行） √
行子查詢
SQL ...
exists 后面（相關子查詢）
表子查詢
按結果集的行列數不同：
標量子查詢（結果集只有一行一列）
列子查詢（結果集只有一列多行）
行子查詢（結果集有一行多列）
表子查詢（結果集一般為多行多列）
```
```mysql
where或 having后面
/*
1、標量子查詢（單行子查詢）
2、列子查詢（多行子查詢）
3、行子查詢（多列多行）
特點：
①子查詢放在小括號內 ()
②子查詢一般放在條件的右側 工資>(子查詢) king 所有員工的名字，部門id...
③標量子查詢，一般搭配著單行操作符使用
> < >= <= = <>
列子查詢，一般搭配著多行操作符使用
in、any/some、all
in ( )
any ( ) 匹配任意一個 ， some 匹配任意一個 salary>any (5000,6000,8000)
all () 匹配所有 。 */
#1.標量子查詢
#案例 誰的工資比 Abel 高?
-- 查詢Abel的工資
#①查詢Abel的工資
SELECT salary
FROM employees
WHERE last_name = 'Abel'
#②查詢員工的信息，滿足 salary>①結果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#2.列子查詢（多行子查詢）★ 只有一列 但是可以有多行
#案例2：返回其它工種中比job_id為‘IT_PROG’工種任一工資低的 員工的員工號、姓名、job_id 以及salary
(10,20,30)
#①查詢job_id為‘IT_PROG’部門任一工資
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查詢員工號、姓名、job_id 以及salary，salary<(①)的任意一個
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#3、行子查詢（結果集一行多列或多行多列）
#案例：查詢員工編號最小并且工資最高的員工信息
-- 第一種方法
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
-- 第二種方法
#①查詢最小的員工編號
SELECT MIN(employee_id)
FROM employees
#②查詢最高工資
SELECT MAX(salary)
FROM employees
#③查詢員工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
#二、select后面 select (里面查詢結果 只有一行一列。)
/*
僅僅支持標量子查詢
*/
#案例：查詢每個部門的員工個數
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 個數
FROM departments d;
#三、from后面
/*
將子查詢結果充當一張表，要求必須起別名 ...
A 2000-4999
...
*/
#四、exists后面（相關子查詢）
/*
語法：
exists(完整的查詢語句)
結果：
1或0
0 表示 子查詢的結果不存在 。
1 表示 子查詢的結果存在。
*/
-- java 0 1
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary>3000);
#案例 查詢有員工的部門名
#in
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT distinct department_id
FROM employees
-- 都有員工的部門 。。。
)
```
### 9 分頁查詢
應用場景：實際的web項目中需要根據用戶的需求提交對應的分頁查詢的sql語句
語法：
```mysql
select 字段|表達式,...
from 表
【where 條件】
【group by 分組字段】
【having 條件】
【order by 排序的字段】
limit 【起始的條目索引，】條目數;
```
特點：
```mysql
1.起始條目索引從0開始
2.limit子句放在查詢語句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每頁顯示條目數sizePerPage
要顯示的頁數 page
示例：
#案例1：查詢第11條——第25條
SELECT * FROM employees LIMIT 10,15;
#案例2：有獎金的員工信息，并且工資較高的前10名顯示出來
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;
```
### 10 聯合查詢
**mysql不支持全外連接，但可以通過union實現這樣的功能。**
引入：union 聯合、合并
語法：
```mysql
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
.....
select 字段|常量|表達式|函數 【from 表】 【where 條件】
```
特點：
1、多條查詢語句的查詢的列數必須是一致的
2、多條查詢語句的查詢的列的類型幾乎相同
3、union代表去重，union all代表不去重
```mysql
#引入的案例：查詢部門編號>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;
```
## DML語言
### 插入
語法：
insert into 表名(字段名，...)
values(值1，...);
特點：
1、字段類型和值類型一致或兼容，而且一一對應
2、可以為空的字段，可以不用插入值，或用null填充
3、不可以為空的字段，必須插入值
4、字段個數和值的個數必須一致
5、字段可以省略，但默認所有字段，并且順序和表中的存儲順序一致
### 修改
修改單表語法：
update 表名 set 字段=新值,字段=新值
【where 條件】
修改多表語法：
update 表1 別名1,表2 別名2
set 字段=新值，字段=新值
where 連接條件
and 篩選條件
### 刪除
方式1：delete語句
單表的刪除： ★
delete from 表名 【where 篩選條件】
多表的刪除：
delete 別名1，別名2
from 表1 別名1，表2 別名2
where 連接條件
and 篩選條件;
方式2：truncate語句
truncate table 表名
兩種方式的區別【面試題】
```mysql
#1.truncate不能加where條件，而delete可以加where條件
#2.truncate的效率高一丟丟
#3.truncate 刪除帶自增長的列的表后，如果再插入數據，數據從1開始
#delete 刪除帶自增長列的表后，如果再插入數據，數據從上一次的斷點處開始
#4.truncate刪除不能回滾，delete刪除可以回滾
```
## DDL語句
### 庫和表的管理
#### 庫的管理：
一、創建庫
create database 庫名
二、刪除庫
drop database 庫名
#### 表的管理：
```mysql
#1.創建表 create
CREATE TABLE IF NOT EXISTS stuinfo(
stuId INT,
stuName VARCHAR(20),
gender CHAR,
bornDate DATETIME);
```
```mysql
DESC studentinfo; //查看表結構 desc 表名
#2.修改表 alter
語法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段類型】;
#①修改字段名
ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;
#②修改表名
ALTER TABLE stuinfo RENAME [TO] studentinfo;
#③修改字段類型和列級約束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
#④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
#⑤刪除字段
ALTER TABLE studentinfo DROP COLUMN email;
```
```mysql
#3.刪除表
DROP TABLE [IF EXISTS] studentinfo;
```
### 常見數據類型
```mysql
整型：
/*
分類：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8 字節
特點：
① 如果不設置無符號還是有符號，默認是有符號，如果想設置無符號，需要添加unsigned關鍵字
② 如果插入的數值超出了整型的范圍,會報out of range異常，并且插入臨界值
③ 如果不設置長度，會有默認的長度
長度代表了顯示的最大寬度，如果不夠會用0在左邊填充，但必須搭配zerofill使用！
*/
小數：
浮點型
定點型
/*
分類：
1.浮點型
float(M,D)
double(M,D)
2.定點型
dec(M，D)
decimal(M,D)
特點：
1、
M：整數部位+小數部位
D：小數部位
如果超過范圍，則插入臨界值
2、
M和D都可以省略
如果是decimal，則M默認為10，D默認為0
如果是float和double，則會根據插入的數值的精度來決定精度
3、
定點型的精確度較高，如果要求插入數值的精度較高如貨幣運算等則考慮使用
*/
字符型：
char
varchar 可變字符串
日期型：
/*
分類：
date只保存日期
time 只保存時間
year只保存年
datetime保存日期+時間
timestamp保存日期+時間
特點：
字節 范圍 時區等的影響
datetime 8 1000——9999 不受
timestamp 4 1970-2038 受
*/
```
### 常見約束
```mysql
NOT NULL -- 非空，用于保證該字段的值不能為空
DEFAULT -- 默認，用于保證該字段有默認值
UNIQUE -- 唯一，用于保證該字段的值具有唯一性，可以為空
CHECK -- 檢查約束【mysql中不支持】
PRIMARY KEY -- 主鍵，用于保證該字段的值具有唯一性，并且非空
FOREIGN KEY -- 外鍵，用于限制兩個表的關系，用于保證該字段的值必須來自于主表的關聯列的值
```
```mysql
#一、創建表時添加約束
#1.添加列級約束
/*
語法：
直接在字段名和類型后面追加 約束類型即可。
只支持：默認、非空、主鍵、唯一*/
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主鍵、外鍵、唯一
SHOW INDEX FROM stuinfo;
#2.添加表級約束
/*
語法：在各個字段的最下面
【constraint 約束名】 約束類型(字段名)
*/
#二、修改表時添加約束
/*
1、添加列級約束
alter table 表名 modify column 字段名 字段類型 新約束;
2、添加表級約束
alter table 表名 add 【constraint 約束名】 約束類型(字段名) 【外鍵的引用】;
```
### 標識列
```mysql
auto_increment 自動增長
/*
又稱為自增長列
含義：可以不用手動的插入值，系統提供默認的序列值
特點：
1、標識列必須和主鍵搭配嗎？不一定，但要求是一個key
2、一個表可以有幾個標識列？至多一個！
3、標識列的類型只能是數值型
4、標識列可以通過 SET auto_increment_increment=3;設置步長
可以通過 手動插入值，設置起始值
```
### 數據庫事務
**事務：現實生活中的一組邏輯操作單元，這組操作要么全部成功，要么全部失敗。**
含義:通過一組邏輯操作單元（一組DML——sql語句），將數據從一種狀態切換到另外一種狀態
特點:（ACID）
原子性：要么都執行，要么都回滾
一致性：保證數據的狀態操作前和操作后保持一致
隔離性：多個事務同時操作相同數據庫的同一個數據時，一個事務的執行不受另外一個事務的干擾
持久性：一個事務一旦提交，則數據將持久化到本地，除非其他事務對其進行修改
相關步驟：
1、開啟事務
2、編寫事務的一組邏輯操作單元（多條sql語句）
3、提交事務或回滾事務
#### 事務的分類
隱式事務，沒有明顯的開啟和結束事務的標志
```mysql
比如
insert、 update、 delete語句本身就是一個事務
```
顯式事務，具有明顯的開啟和結束事務的標志
```mysql
1、開啟事務
取消自動提交事務的功能
set autocommit=0;
start transaction;
2、編寫事務的一組邏輯操作單元（多條sql語句）
insert
update
delete
3、提交事務或回滾事務
commit; -- 提交
rollback; -- 回滾
使用到的關鍵字
savepoint 斷點-- 可以設置一個斷點
commit to 斷點
rollback to 斷點 -- 回滾到斷點處，之間的SQL語句無效
```
#### 事務的隔離級別
事務并發問題如何發生？
當多個事務同時操作同一個數據庫的相同數據時
事務的并發問題有哪些？
臟讀：一個事務讀取到了另外一個事務未提交的數據
不可重復讀：同一個事務中，多次讀取到的數據不一致
幻讀：一個事務讀取數據時，另外一個事務進行更新，導致第一個事務讀取到了沒有更新的數據
如何避免事務的并發問題？
通過設置事務的隔離級別
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免臟讀
3、REPEATABLE READ 可以避免臟讀、不可重復讀和一部分幻讀
4、SERIALIZABLE可以避免臟讀、不可重復讀和幻讀
設置隔離級別：
```mysql
set session|global transaction isolation level 隔離級別名;
```
查看隔離級別：
```mysql
select @@tx_isolation;
```
```mysql
#演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=1;
SAVEPOINT a;#設置保存點
DELETE FROM account WHERE id=2;
ROLLBACK TO a;#回滾到保存點
commit;
```
### 視圖
含義：理解成一張虛擬的表
視圖和表的區別：
```mysql
使用方式 占用物理空間
視圖 完全相同 不占用，僅僅保存的是sql邏輯
表 完全相同 占用
```
視圖的好處：
1、sql語句提高重用性，效率高
2、和表實現了分離，提高了安全性
#### 視圖的創建
? 語法：
CREATE VIEW 視圖名
AS
查詢語句;
#### 視圖的增刪改查
```mysql
-- 視圖最好不要做增刪改操作
1、查看視圖的數據
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';
2、插入視圖的數據
INSERT INTO my_v4(last_name,department_id) VALUES('虛竹',90);
3、修改視圖的數據
UPDATE my_v4 SET last_name ='夢姑' WHERE last_name='虛竹';
4、刪除視圖的數據
DELETE FROM my_v4;
```
#### 某些視圖不能更新
? 包含以下關鍵字的sql語句：分組函數、distinct、group by、having、union或者union all
常量視圖
Select中包含子查詢
join
from一個不能更新的視圖
where子句的子查詢引用了from子句中的表
#### 視圖邏輯的更新
```mysql
#方式一： create or replace
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;
#方式二: alter
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;
SELECT * FROM test_v7;
```
#### 視圖的刪除
```mysql
DROP VIEW test_v1,test_v2,test_v3;
```
#### 視圖結構的查看
```mysql
DESC test_v7;
SHOW CREATE VIEW test_v7;
```
### 存儲過程
含義：一組經過預先編譯的sql語句的集合
```mysql
-- 好處：
1、提高了sql語句的重用性，減少了開發程序員的壓力
2、提高了效率
3、減少了傳輸次數
4、安全性
-- 缺點
1、調試困難 -- mysql中不支持調試
2、CPU、內存耗費大
3、維護與開發困難
```
分類：
```mysql
1、無返回無參
2、僅僅帶in類型，無返回有參
3、僅僅帶out類型，有返回無參
4、既帶in又帶out，有返回有參
5、帶inout，有返回有參
注意：in、out、inout都可以在一個存儲過程中帶多個
```
#### 創建存儲過程
語法：
```mysql
create procedure 存儲過程名(in|out|inout 參數名 參數類型,...)
begin
存儲過程體
end
```
類似于方法：
```mysql
修飾符 返回類型 方法名(參數類型 參數名,...){
方法體;
}
```
```mysql
-- 注意
1、需要設置新的結束標記
delimiter 新的結束標記
示例：
delimiter $
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名 參數類型,...)
BEGIN
sql語句1;
sql語句2;
END $
2、存儲過程體中可以有多條sql語句，如果僅僅一條sql語句，則可以省略begin end
3、參數前面的符號的意思
in:該參數只能作為輸入 （該參數不能做返回值）
out：該參數只能作為輸出（該參數只能做返回值）
inout：既能做輸入又能做輸出
```
##### 調用存儲過程
```mysql
call 存儲過程名(實參列表)
#案例1：根據輸入的女神名，返回對應的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END
-- 又返回參數時，需要設置變量代替返回參數，然后查詢變量即可得到返回的值
#調用
CALL myp7('小昭',@name,@cp)
SELECT @name,@cp
#案例2 ：創建存儲過程實現，用戶是否登錄成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD 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 myp3('張飛','8888')
```
```mysql
#刪除存儲過程
#語法：drop procedure 存儲過程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#× -- 不行
#查看存儲過程的信息
DESC myp2;× -- 不行
SHOW CREATE PROCEDURE myp2;
```
### 函數
#### 創建函數
學過的函數：LENGTH、SUBSTR、CONCAT等
語法：
```mysql
CREATE FUNCTION 函數名(參數名 參數類型,...) RETURNS 返回類型
BEGIN
函數體
END
#調用函數
SELECT 函數名（實參列表）
```
#### 函數和存儲過程的區別
關鍵字 調用語法 返回值 應用場景
函數 FUNCTION SELECT 函數() 只能是一個 一般用于查詢結果為一個值并返回時，當有返回值而且僅僅一個
存儲過程 PROCEDURE CALL 存儲過程() 可以有0個或多個 一般用于更新
```mysql
#查看函數
SHOW CREATE FUNCTION myf3;
#刪除函數
DROP FUNCTION myf3;
#案例
#創建函數，實現傳入兩個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)
```
### 變量
#### 系統變量
##### 1、全局變量
作用域：針對于所有會話（連接）有效，但不能跨重啟
```mysql
查看所有全局變量
SHOW GLOBAL VARIABLES;
查看滿足條件的部分系統變量
SHOW GLOBAL VARIABLES LIKE '%char%';
查看指定的系統變量的值
SELECT @@global.autocommit;
為某個系統變量賦值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
```
##### 2、會話變量
作用域：針對于當前會話（連接）有效
```mysql
查看所有會話變量
SHOW SESSION VARIABLES;
查看滿足條件的部分會話變量
SHOW SESSION VARIABLES LIKE '%char%';
查看指定的會話變量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
為某個會話變量賦值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
```
#### 自定義變量
##### 1、用戶變量
聲明并初始化：
```mysql
SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;
```
賦值：
```mysql
方式一：一般用于賦簡單的值
SET 變量名=值;
SET 變量名:=值;
SELECT 變量名:=值;
方式二：一般用于賦表 中的字段值
SELECT 字段名或表達式 INTO 變量
FROM 表;
使用：
select @變量名;
```
##### 2、局部變量
聲明：
```mysql
declare 變量名 類型 【default 值】;
```
賦值：
```mysql
-- 方式一：一般用于賦簡單的值
SET 變量名=值;
SET 變量名:=值;
SELECT 變量名:=值;
-- 方式二：一般用于賦表 中的字段值
SELECT 字段名或表達式 INTO 變量
FROM 表;
-- 使用：
select 變量名
```
二者的區別：
```mysql
作用域 定義位置 語法
用戶變量 當前會話 會話的任何地方 加@符號，不用指定類型
局部變量 定義它的BEGIN END中 BEGIN END的第一句話 一般不用加@,需要指定類型
#案例：聲明兩個變量，求和并打印
#用戶變量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#局部變量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
```
### 流程控制結構
#### 分支
##### 1、if函數
? 語法：if(條件，值1，值2)
特點：可以用在任何位置
##### 2、case語句
```mysql
語法：
情況一：類似于switch
case 表達式
when 值1 then 結果1或語句1(如果是語句，需要加分號)
when 值2 then 結果2或語句2(如果是語句，需要加分號)
...
else 結果n或語句n(如果是語句，需要加分號)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）
情況二：類似于多重if
case
when 條件1 then 結果1或語句1(如果是語句，需要加分號)
when 條件2 then 結果2或語句2(如果是語句，需要加分號)
...
else 結果n或語句n(如果是語句，需要加分號)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）
特點：
可以用在任何位置
#案例：創建函數，實現傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回C，否則返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END
SELECT test_if(87)
```
##### 3、if elseif語句
```mysql
語法：
if 情況1 then 語句1;
elseif 情況2 then 語句2;
...
else 語句n;
end if;
特點：
只能用在begin end中！！！
```
三者比較：
應用場合
if函數 簡單雙分支
case結構 等值判斷 的多分支
if結構 區間判斷 的多分支
#### 循環
```mysql
/*
分類：
while、loop、repeat
循環控制：
iterate類似于 continue，繼續，結束本次循環，繼續下一次
leave 類似于 break，跳出，結束當前所在的循環
*/
#1.while
語法：
【標簽:】while 循環條件 do
循環體;
end while【 標簽】;
聯想：
while(循環條件){
循環體;
}
#2.loop
語法：
【標簽:】loop
循環體;
end loop 【標簽】;
可以用來模擬簡單的死循環
#3.repeat
語法：
【標簽：】repeat
循環體;
until 結束循環的條件
end repeat 【標簽】;
```
```mysql
語法：
【標簽：】WHILE 循環條件 DO
循環體
END WHILE 【標簽】;
#案例：批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
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),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END
CALL test_while1(100)
#添加iterate語句
#案例：批量插入，根據次數插入到admin表中多條記錄，只插入偶數次
TRUNCATE TABLE admin
DROP PROCEDURE test_while1
CREATE PROCEDURE test_while1(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),'0000');
END WHILE a;
END
CALL test_while1(100)
```
特點：
```mysql
只能放在BEGIN END里面
如果要搭配leave跳轉語句，需要使用標簽，否則可以不用標簽
leave類似于java中的break語句，跳出所在循環！！！
```
