不會吧！不會吧！不會真有人不熟悉資料庫吧？這一篇讓你重新認識！！！

2021 年 3 月 4 日
筆記

資料庫「真經」

資料庫「真經」

基本概念

mysql是一個處理文件的軟體，可以將資料庫看作是一個文件夾，一個表看作是excel表或者是一個文件
結構化查詢語言 (Structured Query Language)，具有定義、查詢、更新和控制等多種功能，是關係資料庫的標準語言
sql分類：
1. 數據操縱語言DML Data Manipulation Language，操作的對象主要是數據 :
  
  SELECT INSERT UPDATE DELETE
2. 數據定義語言DDL Data Definition language，操作的對象主要是表 :
CREATE ALTER DROP RENAME TRUNCATE
1. 數據控制語言DCL Data Control Language ，操作的對象主要是角色:
  GRANT REVOKE
2. Transaction，操作的對象主要是一次事務:
  
  COMMIT ROLLBACK SAVEPOINT
關係型資料庫：sql、mysql、sql server、sqlite、orcle、db2等，非關係型資料庫：mongodb、redis等

基礎操作

資料庫的操作：

創建用戶

create user name identified by password;
更改密碼

update user set password=password("1123") where user="user";
授予權力
grant powers on tablename/databasename to username;

grant all privileges on userInfo to user0;授予用戶對於表的所有許可權
撤銷權力
revoke powers on tablename/databasename from username;
創建

create database test0 character set utf8;`
刪除

drop database name;

表的操作

增

create table name{
......
}engine=innodb charactor set utf8mb4;

刪
1. 刪表
  
  drop table name;
2. 刪數據
  1. 全部
    
    delete from table;
    
    truncate table name;
  2. 單條
    
    delete from table where ...刪除後再次插入的話，若有自增的欄位則會接著刪除欄位的值
查

select * from tablename;`
改

update name set columns = values;
查看錶得相關屬性：

desc tbname; 查看錶得屬性
表重命名

rename old_name to new _name要求是表的所有者

數據的操作

增

insert into tablename(columns...) values(..),(...),...;
刪

delete from tablename where xxx;
改

update tablename set column = xxx where xxx;
查

select * from tablename;

欄位的操作

增

alter table name add column type;
刪

alter table name drop column;
改

alter table name modify(column type);

數據約束

整數
1. int
2. tinyint
3. largeint
浮點數
1. float
2. double
3. decimal
字元串
1. char
2. varchar
3. text
4. 關於char和varchar：在儲存效率方面varchar更高，在查找的情況下char效率更高
日期
1. time
2. date
3. datatime
集合（表示欄位可以取其中到的元素的組合）
```
create table name(
columns set(...)
)
```
枚舉（表示只能用其中的一個值）
```
create table name(
columns enum(...)
)
```

外鍵

create table tb1(
constraint foreign_key_name foreign key(column_name) references other_table_name(column_name
)

聯合主鍵，主鍵只有一個，但是可以由多個欄位共同組成。
```
create table name(
id int...,
name ...;
primery key(id, name)
)
```

聯合外鍵

create table name(
id ...,
name  ...,
primery key (id, name),
condtraint fk_name foreign key(id,name) references other(id, name)
)

唯一索引
1. 單欄位索引
  
  unique unique_name (column);
2. 聯合索引，表示索引的組合是唯一的
  
  unique unique_name (column1, column2);
索引與主鍵
1. 都可以加快查找速度，且都是唯一的，都可以是聯合的
2. 索引可以為空，主鍵不能

添加註釋

為表添加註釋
comment on table emp is "僱員表";
為列添加註釋
comment on column emp.Empno is "僱員工號";

查詢結果去重distinct

distinct必須放在開頭
多欄位
每個欄位不一樣才去重
```
select distinct name from userinfo;
```

條件比較

＝，!＝ ,<>， <,>,<=,>=,any,some,all

-- any大於只查詢的最小值
-- all大於子查詢的最大值
-- some大於子查詢中介於最大值最小值之間的值
select * from class0 where class0.age > some (select age from class1);
select * from class0 where class0.age > any (select age from class1);
select * from class0 where class0.age > all (select age from class1);

is null,is not null：是否為空

select * from class1 where name is null;
select * from class1 where name is not null;

between x and y：篩選查詢結果的某個欄位值是否存在範圍內
```
select * from class1 where age between 18 and 20;
```

in（list）， not in（list）：篩選查詢結果的某個欄位值是否存在範圍內

select * from class1 where class1.age in (select age from class0);
select * from class1 where class1.age not in (select age from class0);

exists（sub－ query）：查詢的結果是否存在在子查詢中

select distinct age from class0 where exists(select age from class1);

like _ ,%,escape 『\『 _% escape 『\』

邏輯複合條件

not，（and， or） and優先順序最高

select * from class1 where name like "老%" and age > 21;
select * from class1 where name like "老%" or age > 21;

sql優化問題
1. AND: 把檢索結果較少的條件放到後面
2. OR: 把檢索結果較多的條件放到後面

like

條件表達式中字元串匹配操作符是‚like
1. %通配符表示任意字元出現任意次數
2. _通配符表示任意字元出現一次
技巧和注意事項：
不能過度使用通配符。如果其他操作符能達到目的，就不要使用通配符。
確實需要使用通配符時，除非絕對必要，否則不要把通配符用到搜索模式最開始處，因為這樣搜索起來是最慢的

order by

按照單個列排序：order by col
```
select * from class1 order by age;
```

降序和升序：order by col desc (asc)

select * from class1 order by age desc;

按多個列排序（優先順序）：order by col1 desc(asc), col2 desc(asc)

select * from class1 order by age desc, id asc;

並集，全集，交集，差集

並集（union）：去重

select age from class1 union select age from class0;

全集（union all）

select age from class1 union all select age from class0;

交集

select age from class1 where age in (select age from class0);

差集

select age from class1 where age not in (select age from class0);

SQL 函數

函數一般是在數據上執行的，它給數據的轉換和處理提供了方便。只是將取出的數據進行處理，不會改變資料庫中的值

Sql函數可以分為組函數和單行函數。

組函數又被稱作聚合函數，用於對多行數據進行操作，並返回一個單一的結果，組函數僅可用亍選擇列表戒查詢的having子句
單行函數對單個數值進行操作，並返回一個值

單行函數的分類

字元函數
1. concat（string1， string2）連接兩個字元串
2. initcap（string） string中每個單詞首字母大寫
3. Lower(string) 以小寫形式返回string
4. lpad,rpad 填充字元型數據
5. ltrim/rtrim （string1,string2）
6. trim(A from B)
7. Substr（）提取字元串的一部分substr（string， 1， 2）
8. upper(string）以大寫形式返回string
9. Instr（）字元串出現的位置, instr（ string ，』A『）
10. Length（）字元串長度，一個漢字3個長度
數字函數

數字函數以NUMBER類型為參數返回NUMBER值
1. round（number， n）返回四捨五入後的值
2. trunc（number， n）
3. mod（x， y）求餘數
4. ceil()上取整
5. floor()下取整
日期和時間函數
1. Add_months(d,x) 返回加上x月後的日期d的值
2. LAST_DAY(d) 返回的所在月份的最後一天
3. Months_between(date1,date2) 返回date1和date2之間月的數目
轉換函數
1. to_char
2. to_number
3. to_date

單行函數嵌套

單行函數可被嵌入到任何層
嵌套函數從最深層到最低層求值

組函數

組函數基亍多行數據返回單個值

avg()：返回某列的平均值
min()：返回某列的最小值
max()：返回某列的最大值
sum()：返回某列值的和
count()：返回某列的行數
組函數僅在選擇列表和Having子句中有效

數據分組

創建分組
1. group by 子句
  Group by 子句可以包含任意數目的列
2. 除組函數語句外， select語句中的每個列都必須在group by 子句中給出
3. 如果分組列中具有null值，則null將作為一個分組返回。如果列中有多行null值，他們將
  分為一組
4. Group by 子句必須出現在where子句之後， order by 子句之前
過濾分組（having子句)
1. Where過濾行， having過濾分組
2. Having支援所有where操作符
分組排序
1. 一般在使用group by 子句時，應該也給出order by子句
出現在SELECT列表中的欄位，如果出現的位置不是在組函數中，那麼必須出現在GROUP BY子句中
不能在 WHERE 子句中使用組函數.不能在 WHERE 子句中限制組. 使用Having對分組進行限制

Select子句順序

Sql語句執行過程：
1. 讀取from子句中的基本表、視圖的數據， [執行笛卡爾積操作]
2. 選取滿足where子句中給出的條件表達式的元組
3. 按group子句中指定列的值分組，同時提取滿足Having子句中組條件表達式的那些組
4. 按select子句中給出的列名戒列表達式求值輸出
5. Order by子句對輸出的目標表進行排序

多表查詢

交叉連接

CROSS JOIN產生了一個笛卡爾積，就象是在連接兩個表格時忘記加入一個WHERE子句一樣
```
select emp.empno, emp.ename, emp.deptno, dept.loc
from emp ， dept;
```
可以使用CROSS JOIN 來達到相同的結果

select emp.empno, emp.ename, emp.deptno, dept.loc
from emp cross join dept;

自然連接

NATURAL JOIN子句基亍兩個表中列名完全相同的列產生連接
1. 兩個表有相同名字的列
2. 數據類型相同
3. 從兩個表中選出連接列的值相等的所有行
```
select *
from emp natural join dept
Where deptno = 10;
```
自然連接的結果不保留重複的屬性

using創建連接

select e.ename,e.ename,e.sal,deptno,d.loc
from emp e join dept d using(deptno)
where deptno=20;

using子句引用的列在sql任何地方丌能使用表名戒者別名做前綴,同樣適合natural子句

使用on創建連接

自然連接的條件是基亍表中所有同名列的等值連接
為了設置任意的連接條件戒者指定連接的列，需要使用ON子句
連接條件不其它的查詢條件分開書寫

使用ON 子句使查詢語句更容易理解

select ename,dname
from emp join dept on emp.deptno=dept.deptno
where emp.deptno=30;

左外連接

在LEFT OUTER JOIN中，會返回所有左邊表中的行，即使在右邊的表中沒有可對應的列值

select e.ename,d.deptno,d.dname
from dept d
left outer join emp e
on e.deptno=d.deptno;

右外連接

RIGHT OUTER JOIN中會返回所有右邊表中的行，即使在左邊的表中沒有可對應的列值

select e.ename,d.deptno,d.dname
from emp e
right outer join dept d
on e.deptno=d.deptno;

分頁

一般情況

select *
from (select rownum rn, t2.ename, t2.sal
from (select e.ename, e.sal from emp e order by e.sal desc) t2
) t1
where t1.rn >= 6
and t1.rn <= 10;
--select * from t_user limit 0,10; limitstartRow,pageSize

性能優化

在使用select * from tablenem limit start, step; 當查詢時，會遍歷資料庫，找到start，當數據太大時就會很慢，解決方案：

索引表中掃：

select * from tablename where id > ? in ( select id from tablename limit start, step) ;--也會很慢

最優方案：

select * from tablename where id > ? limit step;--通過在主鍵中掃描，達到最快

頁面只有上一頁，下一頁max_id min_id

select * from tablename where id > max_id limit 10;

select * from tablename where id < min_id order by id desc limit 10;

當有多頁時，上一頁 192 193 196 197 198 199 下一頁

select * from tablename where id in (select id from tablename where id > max_id limit 30) as N order by N.id desc limit 10;

between…and…只適用於連續的id索引

表設計

視圖

基礎概念

視圖(view)，也稱虛表, 不佔用物理空間，這個也是相對概念，因為視圖本身的定義語句還是要存儲在數據字典里的。視圖只有邏輯定義。每次使用的時候, 只是重新執行SQL
視圖是從一個或多個實際表中獲得的，這些表的數據存放在資料庫中。那些用於產生視圖的表叫做該視圖的基表。一個視圖也可以從另一個視圖中產生
視圖的定義存在資料庫中，與此定義相關的數據並沒有再存一份於資料庫中。通過視圖看到的數據存放在基表中
視圖看上去非常象資料庫的物理表，對它的操作同任何其它的表一樣。當通過視圖修改數據時，實際上是在改變基表中的數據；相反地，基表數據的改變也會自動反映在由基表產生的視圖中

創建視圖

在CREATE VIEW語句後加入子查詢

CREATE [OR REPLACE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];

create or replace view v$_emp_dept
as
select emp.deptno,ename,dname from emp
join dept on emp.deptno=dept.deptno
with read only

使用視圖

在查詢時，不需要再寫完全的Select查詢語句，只需要簡單的寫上從視圖中查詢的語句就可以了

當視圖不再需要的時候，用「drop view」撤銷。刪掉視圖不會導致數據的丟失，因為視圖是基於資料庫的表之上的一個查詢定義

事務處理

基本概念

事務處理

事務（Transaction）是一個操作序列。這些操作要麼都做，要麼都不做，是一個不可分割的工作單位，是資料庫環境中的邏輯工作單位。事務是為了保證資料庫的完整性
事務不能嵌套
一個Transaction起始於一條DML(Insert、 Update和Delete )語句，結束於以下的幾種情況：
1. 用戶顯式執行Commit語句提交操作或Rollback語句回退
2. 當執行DDL(Create、 Alter、 Drop)語句事務自動提交
3. 用戶正常斷開連接時， Transaction自動提交
4. 系統崩潰或斷電時事務自動回退

Commit & Rollback

Commit表示事務成功地結束，此時告訴系統，資料庫要進入一個新的正確狀態，該事務對資料庫的所有更新都以交付實施。每個Commit語句都可以看成是一個事務成功的結束，同時也是另一個事務的開始
Rollback表示事務不成功的結束，此時告訴系統，已發生錯誤，資料庫可能處在不正確的狀態，該事務對資料庫的更新必須被撤銷，資料庫應恢復該事務到初始狀態。每個Rollback語句同時也是另一個事務的開始
一旦執行了commit語句，將目前對資料庫的操作提交給資料庫（實際寫入DB），以後就不能用rollback進行撤銷
執行一個 DDL ， dcl語句或從 SQL*Plus正常退出，都會自動執行commit命令
savepoint：保存點，即相當於一個執行截斷點

delete from userInfo where name="xxx";
savepoint sp;
delete from userInfo where name="xxxx";
rollback to sp;
commit;
-- 表示sp前面的成功執行。後面的不執行

事務的四個特性：ACID

事務四大特徵：原子性，一致性，隔離性和持久性

原子性（Atomicity）
一個原子事務要麼完整執行，要麼乾脆不執行。這意味著，工作單元中的每項任務都必須正確執行。如果有任一任務執行失敗，則整個工作單元或事務就會被終止。即此前對數據所作的任何修改都將被撤銷。如果所有任務都被成功執行，事務就會被提交，即對數據所作的修改將會是永久性的
一致性（Consistency）
一致性代表了底層數據存儲的完整性。它必須由事務系統和應用開發人員共同來保證。事務系統通過保證事務的原子性，隔離性和持久性來滿足這一要求; 應用開發人員則需要保證資料庫有適當的約束(主鍵，引用完整性等)，並且工作單元中所實現的業務邏輯不會導致數據的不一致(即，數據預期所表達的現實業務情況不相一致)。例如，在一次轉賬過程中，從某一賬戶中扣除的金額必須與另一賬戶中存入的金額相等。支付寶帳號100 你讀到餘額要取，有人向你轉100 但是事物沒提交（這時候你讀到的餘額應該是100，而不是200）這種就是一致性
隔離性（Isolation）
隔離性意味著事務必須在不干擾其他進程或事務的前提下獨立執行。換言之，在事務或工作單元執行完畢之前，其所訪問的數據不能受系統其他部分的影響。
持久性（Durability）
持久性表示在某個事務的執行過程中，對數據所作的所有改動都必須在事務成功結束前保存至某種物理存儲設備。這樣可以保證，所作的修改在任何系統癱瘓時不至於丟失
所有的這些都是為了保證數據的一致性

提交或回滾前數據的狀態

以前的數據可恢復
當前的用戶可以看到DML操作的結果
其他用戶不能看到DML操作的結果
被操作的數據被鎖住,其他用戶不能修改這些數據

提交後數據的狀態

數據的修改被永久寫在資料庫中.
數據以前的狀態永久性丟失.
所有的用戶都能看到操作後的結果
記錄鎖被釋放,其他用戶可操作這些記錄

回滾後數據的狀態

語句將放棄所有的數據修改

修改的數據被回退
恢複數據以前的狀態
行級鎖被釋放

事務測試

打開mysql的命令行，將自動提交事務給關閉

--查看是否是自動提交 1表示開啟，0表示關閉
select @@autocommit;
--設置關閉
set autocommit = 0;

數據準備

--創建資料庫
create database tran;
--切換資料庫 兩個窗口都執行
use tran;
--準備數據
 create table psn(id int primary key,name varchar(10)) engine=innodb;
--插入數據
insert into psn values(1,'zhangsan');
insert into psn values(2,'lisi');
insert into psn values(3,'wangwu');
commit;

測試事務

--事務包含四個隔離級別：從上往下，隔離級別越來越高，意味著數據越來越安全
read uncommitted; 	--讀未提交
read commited;		--讀已提交
repeatable read;	--可重複讀
serialize		--序列化執行，串列執行
--產生數據不一致的情況：
臟讀
不可重複讀
幻讀

隔離級別	異常情況		異常情況
讀未提交	臟讀	不可重複讀	幻讀
讀已提交		不可重複讀	幻讀
可重複讀			幻讀
序列化

測試1：臟讀 read uncommitted，當一個事務在更新數據時，另一個事務讀取到了該事務還未commit的事務

set session transaction isolation level read uncommitted;
A:start transaction;
A:select * from psn;
B:start transaction;
B:select * from psn;
A:update psn set name='msb';
A:selecet * from psn
B:select * from psn;  --讀取的結果msb。產生臟讀，因為A事務並沒有commit，讀取到了不存在的數據
A:commit;
B:select * from psn; --讀取的數據是msb,因為A事務已經commit，數據永久的被修改

更新數據：

插入：

測試2：當使用read committed的時候，就不會出現臟讀的情況了，當時會出現不可重複讀的問題，即在同一次事務中讀到不同的值，就好比取錢的時候，銀行卡插進去是1000元，等了幾分鐘就只有幾百元了

set session transaction isolation level read committed;
A:start transaction;
A:select * from psn;
B:start transaction;
B:select * from psn;
--執行到此處的時候發現，兩個窗口讀取的數據是一致的
A:update psn set name ='zhangsan' where id = 1;
A:select * from psn;
B:select * from psn;
--執行到此處發現兩個窗口讀取的數據不一致，B窗口中讀取不到更新的數據
A:commit;
A:select * from psn;--讀取到更新的數據
B:select * from psn;--也讀取到更新的數據
--發現同一個事務中多次讀取數據出現不一致的情況

測試3：當使用repeatable read的時候(按照上面的步驟操作)，就不會出現不可重複讀的問題，但是會出現幻讀的問題，也就是一個事務完成添加數據的操作時，另一個事務感知到了，但是並不會展示，僅僅感知

set session transaction isolation level repeatable read;
A:start transaction;
A:select * from psn;
B:start transaction;
B:select * from psn;
--此時兩個窗口讀取的數據是一致的
A:insert into psn values(4,'sisi');
A:commit;
A:select * from psn;--讀取到添加的數據
B:select * from psn;--讀取不到添加的數據
B:insert into psn values(4,'sisi');--報錯，無法插入數據
--此時發現讀取不到數據，但是在插入的時候不允許插入，出現了幻讀，設置更高級別的隔離級別即可解決

總結：以上問題都沒有保證數據的一致性

約束 constraint

基本概念

當我們創建表的時候，同時可以指定所插入數據的一些規則，比如說某個欄位不能為空值，某個欄位的值（比如年齡）不能小於零等等，這些規則稱為約束。約束是在表上強制執行的數據校驗規則
支援下面五類完整性約束
1. NOT NULL非空
2. UNIQUE Key 唯一鍵
3. PRIMARY KEY 主鍵
4. FOREIGN KEY 外鍵
5. CHECK 自定義檢查約束

創建約束的時機

在建表的同時創建
建表後創建

分類

約束從作用上分類，可以分成兩大類：

表級約束：可以約束表中的任意一列或多列。可以定義除了Not Null以外的任何約束
列級約束：只能約束其所在的某一列。可以定義任何約束

列級約束

列級約束: 從形式上看，在每列定義完後馬上定義的約束，在逗號之前就定義好了

carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));

表級約束

create table child( c number , c2 number , primary key (c2), foreign key(c2) references parent(c1));

具體分類

主鍵約束（ PRIMARY KEY）

主鍵約束是資料庫中最重要的一種約束。在關係中，主鍵值不可為空，也不允許出現重複，即關係要滿足實體完整性規則
注意：
1. 主鍵從功能上看相當於非空且唯一
2. 一個表中只允許一個主鍵
3. 主鍵是表中能夠唯一確定一個行數據的欄位
4. 主鍵欄位可以是單欄位或者是多欄位的組合
5. Oracle為主鍵創建對應的唯一性索引
建議命名：
1. 約束_表名_欄位
主鍵可用下列兩種形式之一定義
1. 主鍵子句：在表的定義中加上如下子句 primary key(列)
2. 主鍵短語：在主屬性的定義之後加上primary key字樣
```
create table t3(
id number(4),
constraint t3_pk primary key(id)
)
```

非空約束（NOT NULL）

確保欄位值不允許為空

只能在欄位級定義

CREATE TABLE employees(
employee_id NUMBER(6),
name VARCHAR2(25) NOT NULL,
salary NUMBER(8,2),
hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
)

唯一性約束（UNIQUE）

唯一性約束條件確保所在的欄位或者欄位組合不出現重複值

唯一性約束條件的欄位允許出現空值

CREATE TABLE employees(
id NUMBER(6),
name VARCHAR2(25) NOT NULL UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
hire_date DATE NOT NULL,
CONSTRAINT emp_email_uk UNIQUE(email)
);

CHECK 約束

Check約束用於對一個屬性的值加以限制

在check中定義檢查的條件表達式，數據需要符合設置的條件

create table emp3
( id number(4) primary key,
age number(2) check(age > 0 and age < 100),
salary number(7,2),
sex char(1),
constraint salary_check check(salary > 0)
)

關係模型的三類完整性規則

為了維護資料庫中的數據與現實世界的一致性，關係資料庫的數據與更新操作必須遵循下列三類完整性規則：

實體完整性規則：這條規則要求關係中在組成主鍵的屬性上不能有空值
參照完整性規則：這條規則要求「不引用不存在的實體」。例如： deptno是dept表的主鍵，而相應的屬性也在表emp中出現，此時deptno是表emp的外鍵。在emp表中， deptno的取值要麼為空，要麼等於dept中的某個主鍵值
用戶定義的完整性規則：用戶定義的完整性規則反應了某一具體的應用涉及的數據必須滿足的語義要求

外鍵約束（ FOREIGN KEY）

外鍵是表中的一個列，其值必須在另一表的主鍵或者唯一鍵中列出
作為主鍵的表稱為「主表」，作為外鍵的關係稱為「依賴表」
外鍵參照的是主表的主鍵或者唯一鍵
對於主表的刪除和修改主鍵值的操作，會對依賴關係產生影響，以刪除為例：當要刪除主表的某個記錄（即刪除一個主鍵值，那麼對依賴的影響可採取下列3種做法：
1. RESTRICT方式：只有當依賴表中沒有一個外鍵值與要刪除的主表中主鍵值相對應時，才可執行刪除操作
2. CASCADE方式：將依賴表中所有外鍵值與主表中要刪除的主鍵值相對應的記錄一起刪除
3. SET NULL方式：將依賴表中所有與主表中被刪除的主鍵值相對應的外鍵值設為空值
```
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);
```
約束的添加和撤銷

約束的添加和撤銷
1. 添加:
  
  alter table tablename add constraint con_name unique(col) ;
2. 刪除
  
  alter table tablename drop constraint com_name [cascade] ;
索引

基本概念
1. 索引是為了加快對數據的搜索速度而設立的。索引是方案（schema）中的一個資料庫對象,與表獨立存放
2. 索引的作用：在資料庫中用來加速對錶的查詢,通過使用快速路徑訪問方法快速定位數據,減少了磁碟的I/O
3. Sql中的索引是非顯示索引，也就是在索引創建以後，在用戶撤銷它之前不會在用到該索引的名字，但是索引在用戶查詢時會自動起作用
4. 索引的創建有兩種情況：
  1. 自動: 當在表上定義一個PRIMARY KEY 或者UNIQUE 約束條件時
  2. 手動: 用戶可以創建索引以加速查詢
5. 開發中使用索引的要點：
  1. 索引改善檢索操作的性能，但降低數據插入、修改和刪除的性能。在執行這些操作時， DBMS必須動態地更新索引
  2. 索引數據可能要佔用大量的存儲空間
  3. 並非所有的數據都適合於索引。唯一性不好的數據（如省）從索引的到的好處不比具有更多可能值的數據（如姓名）從索引得到的好處多
  4. 索引用於數據過濾和數據排序。如果你經常以某種特定的順序排序數據，則該數據可能是索引的備選
  5. 可以在索引中定義多個列（如省加城市），這樣的索引只在以省加城市的順序排序時有用。如果想按城市排序，則這種索引沒有用處
使用

創建

CREATE INDEX index ON table (column[, column]...);

刪除

DROP INDEX upper_last_name_idx;

進階

不能命中索引的幾種方式
1. like
2. 調用函數
3. or
```
select * from tablename where index_column = ? or not_column_name=?--不會走索引

select * from tablename where index_column1 = ? or not_column_name=? and index_column2=?--會直接屏蔽or...and之間的走
```
4. 索引!=(主鍵除外)
5. > (主鍵除外)
6. 滿足的條件的值的類型與聲明索引時的類型不一致（主鍵除外）
7. order by(主鍵除外)

注意事項：

避免使用select
count(1)或count(列)代替count()
創建表時盡量使用char代替varchar
表得欄位順序固定長度的欄位優先
組合索引代替多個單列索引（經常使用多個條件查詢時）
盡量使用短索引
使用連接(join)代替子查詢
連表注意類型需要一致
索引散列值（重複少）不適合建立索引。例如：性別。

觸發器

首先：delimiter 設置sql語句的結束標誌，sql語句默認是「；」結束也就是：delimiter ;，可以修改為：delimiter //表示當遇到「//」，語句才算結束，可以恢復設置：delimiter ;

trigger用於在增、刪、改的前後觸發一系列動作

create trigger t1 on  tablename before  insert for each row;
create trigger t1 on tablename after insert for each row;

new/old用於表示插入、刪除、更新操作的行的數據

new表示新插入的數據行

delimiter //
        create trigger t1 befor insert on tablename for each row
        begin
            insert into t2(name) values(new.name);
        end //
        delimiter ;

old表示即將被刪除的數據行

delimiter //
        create trigger t1  befor delete on tablename for each row
        begin
            insert into t2(name) values(old.name);
        end //
        delimiter ;

存儲過程

用一個別名來描述多個sql語句的執行過程

最簡單的使用

delimiter //
    create PROCEDURE p1()
    begin
    select * from userinfo;
    end //
    delimiter ;

    call p1();

傳參（in， out， inout）

in 表示傳入的參數，可以傳入數值或者變數，即使傳入變數，並不會更改變數的值，可以內部更改，僅僅作用在函數範圍內

delimiter //
        create procedure p2(
            in v1 int
            )
            begin
            set v1 = 2 * v1;
            select v1;
            end //

        delimiter ;

        call p2(19);

        ----------------
        delimiter //
        create procedure p6(
            in v int
            )
            begin 
            set v = 10;
            select v;
            end //
        delimiter ;

        call p6(10);

        set @v4 = 0;
        call p6(@v4);
        select @v4;  -- 此時@v4依然為0

out 表示存儲執行存儲過程的返回結果，且參數只能是一個變數，且只能對其賦值（函數執行完以後也生效），不能在函數內獲取其值

delimiter //
        create procedure p3(
            out v int
            )
            begin
            set v = 10;
            end //
        delimiter ;

        set @v1 = 0; # @varible_name 類似於定義一個局部變數，跟session一樣
        call p3(@v1); 
        select @v1;

        -----------------------------
        delimiter //
        create procedure p4(
            out v int
            )
            begin 
            set v = v + 5;  -- 這兒有問題，不能這樣，只能直接賦值
            select v;
            end //
        delimiter ;
        -- 這段程式碼是有問題的。
        delimiter //
        create procedure p5(
            out v int
            )
            begin 
            set v = 5; -- 對的
            select v;
            end //
        delimiter ;

        set @v3 = 0;
        call p5(@v3);
        
create procedure name(
            in v1 int,
            out v2 int
        )
        begin
        set v2 = v1;
        select v2;
        end

        call(num, num)

inout 表示從外部傳入的參數經過修改後可以返回的變數，既可以使用傳入變數的值也可以修改變數的值（即使函數執行完）

delimiter //
        create procedure p7(
            inout v int
            )
            begin
            set v = 9999;
            select v;
            end //
        delimiter ;

        call p7(10);

        set @v5 = 0;
        call p7(@v5);
        select @v5;

        ------------------------------------

        delimiter //
        create procedure p8(
            inout v int
            )
            begin
            set v = v + 9999;
            select v;
            end //
        delimiter ;

        set @v6 = 1;
        call p8(@v6);
        select @v6;

總結：
1. in只可以讀取值/變數，不能更改
2. out不能讀，可以更改
3. inout既可以讀又可以更改

不會吧！不會吧！不會真有人不熟悉資料庫吧？這一篇讓你重新認識！！！

資料庫「真經」

基本概念

基礎操作

資料庫的操作：

表的操作

數據的操作

欄位的操作

數據約束

添加註釋

查詢結果去重distinct

條件比較

邏輯複合條件

like

order by

並集，全集，交集，差集

SQL 函數

單行函數的分類

單行函數嵌套

組函數

數據分組

Select子句順序

多表查詢

交叉連接

自然連接

using創建連接

使用on創建連接

左外連接

右外連接

分頁

一般情況

性能優化

表設計

視圖

基礎概念

創建視圖

使用視圖

事務處理

基本概念

事務處理

Commit & Rollback

事務的四個特性：ACID

提交或回滾前數據的狀態

提交後數據的狀態

回滾後數據的狀態

事務測試

約束 constraint

基本概念

創建約束的時機

分類

列級約束

表級約束

具體分類

主鍵約束（ PRIMARY KEY）

非空約束（NOT NULL）

唯一性約束（UNIQUE）

CHECK 約束

關係模型的三類完整性規則

外鍵約束（ FOREIGN KEY）

約束的添加和撤銷

索引

基本概念

使用

創建

刪除

進階

不能命中索引的幾種方式

注意事項：

觸發器

存儲過程

往期精彩文章

分享此文：

Related Posts

【ASP.NET Core】設置 Web API 響應數據的格式——FormatFilter特性篇

Dimple在左耳聽風ARTS打卡（十六）

世界首富的星際飛船慘烈爆炸：火光一片、現場極慘烈

Kubernetes Container lifecycle hooks

VirMach 便宜 VPS

QNews

熱門搜尋