數(shù)據(jù)庫

一、數(shù)據(jù)庫基礎(chǔ)

1、一些概念

• 數(shù)據(jù)庫：數(shù)據(jù)庫（DataBase ，簡稱DB），就是信息的集合。數(shù)據(jù)庫是由數(shù)據(jù)庫管理系統(tǒng)管理的數(shù)據(jù)的集合；
• 數(shù)據(jù)庫管理系統(tǒng)：簡稱DBMS 。是一種操縱和管理數(shù)據(jù)庫的大型軟件，通常用于建立、使用和維護(hù)數(shù)據(jù)庫；
• 數(shù)據(jù)庫系統(tǒng)：DBS。通常由軟件、數(shù)據(jù)庫和數(shù)據(jù)管理員（DBA）組成；
• 數(shù)據(jù)庫管理員：DBA。負(fù)責(zé)全面管理和控制數(shù)據(jù)庫系統(tǒng)。

---

• 元組：是關(guān)系數(shù)據(jù)庫中的基本概念，關(guān)系是一張表，表中的每行（即數(shù)據(jù)庫中的每條記錄）就是一個元組，每列就是一個屬性。
• 碼：能唯一標(biāo)識實體的屬性，對應(yīng)表中的列
• 候選碼：若關(guān)系中的某一屬性或?qū)傩越M的值能唯一的標(biāo)識一個元組 ，則稱該屬性組為候選碼。例如，學(xué)生實體中，“學(xué)號”能唯一的區(qū)分學(xué)生實體，同時“班級”和“姓名”組合也可以唯一的區(qū)分學(xué)生實體，所以{學(xué)號}、{班級，姓名}都是候選碼
• 主碼：也叫主鍵。主碼是從候選碼中選出來，一個實體只能有一個主碼，但可以有多個候選碼。主碼是最小的候選碼，是根據(jù)業(yè)務(wù)需求來選取的 ，是數(shù)據(jù)庫設(shè)計者認(rèn)為最適合唯一標(biāo)識表的屬性或?qū)傩越M合
• 外碼：也叫外鍵。如果一個關(guān)系中的一個屬性是另一個關(guān)系的主鍵 ，則這個屬性為外鍵。一個表只能有一個主鍵，可以有多個外鍵
• 主屬性：候選碼中出現(xiàn)過的屬性稱為主屬性
• 非主屬性：不包含在任何一個候選碼中的屬性稱為非主屬性

2、ER圖

• 提供了表示實體類型、屬性和聯(lián)系的方法
  • 實體：通常是現(xiàn)實世界的業(yè)務(wù)對象。用矩形框表示
  • 屬性：某個實體擁有的屬性?？梢岳斫鉃樽侄巍Ｓ脵E圓形表示
  • 聯(lián)系：實體與實體之間的的關(guān)系?？梢酝ㄟ^數(shù)字表示實體之間的數(shù)量對照關(guān)系（1:1 、1：N 、M:N）

3、數(shù)據(jù)庫范式

• 1NF：屬性（對應(yīng)表中的字段）不可再分，也就是這個字段只能是一個值，不能再分為其他字段。是所有關(guān)系型數(shù)據(jù)庫的最基本要求 。強(qiáng)調(diào)的是列的原子性，即數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項

• 2NF：1NF的基礎(chǔ)上，消除了非主屬性對于碼的部分函數(shù)依賴：添加主鍵，非主屬性依賴主鍵

將表拆分出來，每個表設(shè)單獨的主鍵，非主屬性依賴主鍵。要求所有實體的屬性完全依賴于主關(guān)鍵字

• 3NF：在2NF的基礎(chǔ)之上，消除了非主屬性對于碼的傳遞函數(shù)依賴：消除傳遞函數(shù)依賴

傳遞函數(shù)依賴：在關(guān)系模式R（U）中，設(shè) X、Y、Z是U的不同的屬性子集，如果X確定Y、Y確定Z，則稱Z傳遞函數(shù)依賴于X。傳遞函數(shù)會導(dǎo)致數(shù)據(jù)冗余和異常。任何非主屬性不依賴于其他非主屬性

4、為什么不推薦使用外鍵與級聯(lián)？

在阿里巴巴開發(fā)手冊中這樣說到：

【強(qiáng)制】不得使用外鍵與級聯(lián)，一切外鍵概念必須在應(yīng)用層解決。

說明: 以學(xué)生和成績的關(guān)系為例，學(xué)生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學(xué)生表中的 student_id，同時觸發(fā)成績表中的student_id 更新，即為級聯(lián)更新 。外鍵與級聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群 ; 級聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng) 險; 外鍵影響數(shù)據(jù)庫的插入速

為什么不使用外鍵？

• 增加了復(fù)雜性：每次做修改操作（刪除、更新）都必須考慮外鍵約束，會導(dǎo)致開發(fā)的時候很痛苦，測試數(shù)據(jù)極為不方便；外鍵的主從關(guān)系是固定的，假如需求有變化，數(shù)據(jù)庫中的這個字段如果不需要和其他表有關(guān)聯(lián)的話就會增加很多麻煩
• 增加了額外工作：數(shù)據(jù)庫需要增加維護(hù)外鍵的工作，當(dāng)我們設(shè)計外鍵字段的修改操作，要出發(fā)相關(guān)操作去檢查，保證數(shù)據(jù)的一致性和正確性
• 對分庫分表不友好：分庫分表下外鍵是無法生效的

5、drop、delete與truncate區(qū)別？

1）用法不同

• drop：drop table 表名 ，直接將表都刪除掉，在刪除表的時候使用
• truncate：truncate table 表名 ，只刪除表中的數(shù)據(jù)，再插入數(shù)據(jù)時自增長id又從1開始，在清空表中數(shù)據(jù)時使用
• delete：delete from 表名 where 列名 = 值 ，刪除某一行的數(shù)據(jù)，如果不加where子句和truncate table 表名 作用相同

2）屬于不同數(shù)據(jù)庫語言

• truncate 和drop屬于DDL（數(shù)據(jù)定義語言）語句，操作立即生效，原數(shù)據(jù)不放到rollback segment中，不能回滾。而delete是DML（數(shù)據(jù)庫操作語言）語句，會放到rollback segment中，事務(wù)提交后才生效

3）執(zhí)行速度不同

一般來說 drop>truncate >delete

6、數(shù)據(jù)庫設(shè)計通常分為？

• 需求分析：分析用戶的需求，包括數(shù)據(jù)、功能和性能需求
• 概念結(jié)構(gòu)設(shè)計：主要采用E-R模型進(jìn)行設(shè)計，包括畫E-R圖
• 邏輯結(jié)構(gòu)設(shè)計：通過將E-R圖轉(zhuǎn)換成表，實現(xiàn)從E-R模型到關(guān)系模型的轉(zhuǎn)換
• 物理結(jié)構(gòu)設(shè)計：主要是為所設(shè)計的數(shù)據(jù)庫選擇合適的存儲結(jié)構(gòu)和存儲路徑
• 數(shù)據(jù)庫實施：包括編程、測試和試運行
• 數(shù)據(jù)庫的運行和維護(hù)：系統(tǒng)的運行與數(shù)據(jù)庫的日常維護(hù)

二、NoSQL基礎(chǔ)

1、NoSQL是什么？

Not Only SQL 。泛指非關(guān)系型數(shù)據(jù)庫，主要針對的是鍵值、文檔以及圖形類型數(shù)據(jù)存儲 。并且，NoSQL數(shù)據(jù)庫天生支持分布式，數(shù)據(jù)冗余和數(shù)據(jù)分片等特性，旨在提供可擴(kuò)展的高可用高性能數(shù)據(jù)存儲解決方案

NoSQL數(shù)據(jù)庫代表：HBase、Cassandra、MongoDB、Redis

2、SQL和NoSQL的區(qū)別

• SQL數(shù)據(jù)庫是結(jié)構(gòu)化存儲，具有固定行和列的表格；NoSQL數(shù)據(jù)庫是非結(jié)構(gòu)化存儲，文檔：JSON文檔，鍵值：鍵值對，寬列：包含行和動態(tài)列的表 ，圖：節(jié)點和邊
• SQL數(shù)據(jù)庫提供ACID屬性；NoSQL通常不支持ACID事務(wù)，為了可擴(kuò)展、高性能進(jìn)行了權(quán)衡，少部分支持比如MongoDB。
• SQL數(shù)據(jù)庫性能決定于磁盤子系統(tǒng)，要獲得最佳性能，通常需要優(yōu)化查詢、索引和表結(jié)構(gòu)；NoSQL性能通常由底層硬件集群大小、網(wǎng)絡(luò)延遲以及調(diào)用應(yīng)用程序來決定

2、NoSQL數(shù)據(jù)庫有什么優(yōu)勢

• 靈活性：NoSQL數(shù)據(jù)庫通常提供靈活的架構(gòu)，以實現(xiàn)更快速、更多的迭代開發(fā) 。靈活的數(shù)據(jù)模型使NoSQL數(shù)據(jù)庫成為半結(jié)構(gòu)化和非結(jié)構(gòu)化數(shù)據(jù)的理想之選
• 可擴(kuò)展性：NoSQL數(shù)據(jù)庫通常被設(shè)計為通過使用分布式硬件集群來橫向擴(kuò)展，而不是通過添加昂貴和強(qiáng)大的服務(wù)器來縱向擴(kuò)展
• 高性能：NoSQL數(shù)據(jù)庫針對特定的數(shù)據(jù)模型和訪問模型進(jìn)行了優(yōu)化 ，這與嘗試使用關(guān)系數(shù)據(jù)庫完成完成類似功能相比可實現(xiàn)更高的性能
• 強(qiáng)大的功能：NoSQL數(shù)據(jù)庫提供功能強(qiáng)大的API和數(shù)據(jù)類型 ，專門針對其各自的數(shù)據(jù)模型而構(gòu)建

3、NoSQL數(shù)據(jù)庫有哪些類型？

• 鍵值：鍵值數(shù)據(jù)庫是一種比較簡單的數(shù)據(jù)庫，其中每個項目都包含鍵和值。應(yīng)用可以完全控制value字段中存儲的內(nèi)容，沒有任何限制。代表的鍵值數(shù)據(jù)庫：Redis和DynanoDB

• 文檔：文檔數(shù)據(jù)庫中的數(shù)據(jù)被存儲在類似于JSON對象的文檔中，非常清晰直觀。每個文檔包含成對的字段和值，這些值通?？梢允歉鞣N類型。代表的文檔數(shù)據(jù)庫：MongoDB

• 圖形：輕松構(gòu)建和運行與高度連接的數(shù)據(jù)集一起使用的應(yīng)用程序。圖形數(shù)據(jù)庫的典型使用案例包括社交網(wǎng)絡(luò)、推薦引擎、欺詐檢測和知識圖形。代表的圖形數(shù)據(jù)庫：Neo4j、Giraph

• 寬列：寬列存儲數(shù)據(jù)庫非常適合需要存儲大量的數(shù)據(jù)，代表的寬列數(shù)據(jù)庫有 Cassandra、HBase

三、字符集

1、什么是字符集

字符集就是乙烯類字符的集合。字符集的種類較多，每個字符集可以表示的字符范圍通常不同，比如說有些字符集是無法表示漢字的

計算機(jī)只能存儲二進(jìn)制的數(shù)據(jù)，對于英文、漢字、表情等字符需要轉(zhuǎn)換為對應(yīng)的二進(jìn)制數(shù)據(jù)。我們將字符對應(yīng)二進(jìn)制數(shù)據(jù)的過程稱為“字符編碼”，反之二進(jìn)制數(shù)據(jù)解析成字符的過程稱為“字符解碼”

2、常見的字符集

ASCII , GB2321 , GBK , UTF-8

Unicode&UTF-8

不同的字符集可以表示的字符范圍以及編碼規(guī)則存在差異，這就導(dǎo)致了使用錯誤的編碼方式查看一個包含字符的文件就會產(chǎn)生亂碼現(xiàn)象 。亂碼的本質(zhì)就是：編碼和解碼時用了不同或者不兼容的字符集。為了解決這個問題，Unicode誕生了。

Unicode字符集中包含了世界上幾乎所有已知的字符，不過Unicode字符集并沒有規(guī)定如何存儲這些字符（使用二進(jìn)制數(shù)據(jù)表示這些字符），于是就有了UTF-8 ,。

UTF-8使用1到4個字節(jié)為每個字符編碼，可以根據(jù)不同的符號自動選擇編碼的長短。UTF-8是目前使用最廣的一種字符編碼。

3、MySQL字符編碼集中有兩套UTF-8編碼實現(xiàn)：

• utf8
• utf8mb4

utf8mb4是UTF-8的完整實現(xiàn) ，可以用來存儲emoji符號。因此如果你需要存儲emoji類型的數(shù)據(jù)或者一些比較復(fù)雜的文字、繁體字到MySQL數(shù)據(jù)庫的話，數(shù)據(jù)庫的編碼一定要指定為utf8mb4而不是utf8，否則存儲的時候就會報錯了。

四、SQL語言基礎(chǔ)

1、SQL語法要點

• SQL語句不區(qū)分大小寫
• SQL語句以;結(jié)尾
• 處理SQL語句時，所有空格都被忽略

2、SQL分類

• DDL：數(shù)據(jù)定義語言。對數(shù)據(jù)庫內(nèi)部對象 進(jìn)行創(chuàng)建、刪除、修改的操作語言。更多的被數(shù)據(jù)庫管理員使用

是SQL語言集中負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫對象定義的語言

DDL的核心指令是 CREATE、ALTER、DROP

• DML：數(shù)據(jù)庫操縱語言，對數(shù)據(jù)庫中表記錄的操作。包括表記錄的插入、更新、刪除和查詢，是開發(fā)人員日常使用最頻繁的操作。只對表內(nèi)部數(shù)據(jù)的操作，不涉及表結(jié)構(gòu)等

主要功能是訪問數(shù)據(jù)，其語法都是以讀寫數(shù)據(jù)庫 為主

DML的核心指令是 INSERT、UPDATE、DELETE、SELECT

• TCL：事務(wù)控制語言，用于管理數(shù)據(jù)庫中的事務(wù) 。核心指令是：COMMIT、ROLLBACK

• DCL：數(shù)據(jù)控制語言，是一種可對數(shù)據(jù)訪問權(quán)進(jìn)行控制的指令，DCL以控制用戶的訪問權(quán)限為主 ，可利用DCL控制的權(quán)限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES

3、DML數(shù)據(jù)庫操縱語言

DML語言的主要功能就是讀寫數(shù)據(jù)庫實現(xiàn)增刪改查

插入數(shù)據(jù)

字段名和值一一對應(yīng)?？梢圆迦胍恍幸部梢酝瑫r插入多行

-- INSERT INTO 表名（[字段名1，字段名2，字段名3]）values(`值1`，`值2`,`值3`) 插入一行
--INSERT INTO 表名（`字段名1`）values(`值1`)，(`值2`),(`值3`)  插入多行
--插入一行
INSERT INTO student(name,pwd)VALUES('張三','123456');
--插入多行
INSERT INTO student(name,pwd)VALUES('張三','123456'),('張三','123456');
-- 插入完整行，可以忽略字段名
INSERT INTO student VALUES('張三','123456','aaaa');

更新數(shù)據(jù)

update user 
set username = 'root',password='root'
where username='root';

刪除數(shù)據(jù)

delete from user
where username='root';

清空表中的數(shù)據(jù)

truncate table user;

查詢

-- 查詢單列
select prod_name from department;
--查詢多列
select prod_name,prod_id from department;
--查詢所有列
select * from department;--查詢不同的值  用于返回唯一不同的值，作用于所有列，所有列的值都相同才算相同
select distinct ven_id from products;-- 限制查詢結(jié)果，分頁
select * from mytable limit 5 ; -- 返回前五行
select * from mytable limit 0,5;-- 返回前五行，第1~5行
select * from mytable limit 2,3; -- 從第三行開始，返回三條數(shù)據(jù)，第3~5行

排序

order by 用于對結(jié)果集按照一個列或者多個列進(jìn)行排序。默認(rèn)按照升序（ASC）對記錄進(jìn)行排序，如果需要按照降序?qū)τ涗涍M(jìn)行排序，可以使用desc關(guān)鍵字

-- 默認(rèn)是升序?？梢詫Χ鄠€列進(jìn)行排列如下，不同列可以有不同的排序規(guī)則。按照先排序的列進(jìn)行排序，相等再按后面的列的排序規(guī)則
select * from products
order by prod_price DESC,prod_name ASC -- 先按成績降序，如果成績一樣，按名字排序

分組

-- 查詢不同課程的  平均分、最高分、最低分
-- 分組為不同的課程SELECT `subjectname`,AVG(`studentresult`)AS 平均分,MAX(`studentresult`) AS 最高分 ,MIN(`studentresult`) AS 最低分
FROM result r
GROUP BY r.subjectno   -- 通過什么字段來分組
HAVING 平均分 >80    -- having:過濾條件

having和where：

• where：過濾指定的行，后面不能加聚合函數(shù)（分組函數(shù)），where在group by 前面
• having：過濾分組，一般都是和group by 連用 ，不能單獨使用。having 在group by 之后

子查詢

內(nèi)部查詢在其父查詢之前執(zhí)行，以便可以將內(nèi)部查詢的結(jié)果傳遞給外部查詢。

-- 查詢數(shù)據(jù)庫結(jié)構(gòu)的所有考試結(jié)果（學(xué)號，科目編號，成績），降序排列
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno =(
SELECT subjectno FROM `subject`
WHERE subjectname = '數(shù)據(jù)庫結(jié)構(gòu)'
)
ORDER BY studentresult DESC-- 分?jǐn)?shù)不小于80分的學(xué)生的學(xué)號和姓名
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno`=ANY (
SELECT `studentno`FROM `result`
WHERE `studentresult`>80
)
ORDER BY `studentno`DESC-- 子查詢的子查詢
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_idFROM ordersWHERE order_num IN (SELECT order_numFROM orderitemsWHERE prod_id = 'RGAN01'));

IN 和 BETWEEN

• IN操作符在WHERE子句中使用，作用是在指定的幾個特定值中任選一個值
• Between 操作符在WHERE子句中使用，作用是選區(qū)介于某個范圍內(nèi)的值

-- IN
SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');-- BETWEEN ，between一般和and連用，在某個區(qū)間范圍內(nèi)
SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;

模糊查詢

LIKE操作符在WHERE子句中使用，作用是確定字符串是夠匹配模式

只有字段是文本值時才使用LIKE

LIKE支持兩個通配符匹配選項：% 和 _

不要濫用通配符，通配符位于開頭處匹配會非常慢

% 表示任何字符出現(xiàn)任意次數(shù)

_表示任何字符出現(xiàn)一次

-- 從商品中查找名稱為%bean bag%的商品id、商品名、商品價格
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';

聯(lián)表查詢

join

-- 從table1中查找table1.common_column1 = table2.common_column2的table1的cplumn1和table2的column2
select table1.column1, table2.column2...
from table1
join table2
on table1.common_column1 = table2.common_column2;

連接表時需要在每個表中選擇一個字段，并對這些字段的值進(jìn)行比較，值相同的兩條記錄將合并為一條。連接表的本質(zhì)就是將不同表的記錄合并起來，形成一張新表。當(dāng)然，這張新表只是臨時的，它僅存在于本次查詢期間。

當(dāng)兩個表中有同名的字段時，為了幫助數(shù)據(jù)庫引擎區(qū)分是哪個表的字段，在書寫同名字段名時需要加上表名，當(dāng)然，如果書寫的字段名在兩個表中是唯一的，則不用寫表名；

如果兩張表的關(guān)聯(lián)字段名相同，也可以使用USING子句來代替ON

-- join....on
select c.cust_name, o.order_num
from Customers c
inner join Orders o
on c.cust_id = o.cust_id
order by c.cust_name;-- 如果兩張表的關(guān)聯(lián)字段名相同，也可以使用USING子句：join....using()
select c.cust_name, o.order_num
from Customers c
inner join Orders o
using(cust_id)
order by c.cust_name;

• inner join： （默認(rèn)連接方式）只有當(dāng)兩個表都存在滿足條件的記錄時才會返回行。

  滿足s.studentno=r.studentno這個條件的行，才返回

• left join：返回左表中的所有行，即使右表中沒有滿足條件的行也是如此。

先把左表的行全部抄下來，然后看右表有沒有匹配條件的行 ，有的話就把字段合并，沒有就不合并。

• right join：從右表中返回所有的值，即使左表中沒有匹配（右表全集） 返回右表全集加交集

先把右表的行全部抄下來，然后看左表有沒有匹配條件的行 ，有的話就把字段合并，沒有就不合并。

-- inner join  
SELECT studentname,s.studentno,subjectno,studentresult 
FROM student AS s INNER JOIN result AS r
ON s.studentno=r.studentno-- right join
SELECT studentname,s.studentno,subjectno,studentresult 
FROM student AS s RIGHT JOIN result AS r
ON s.studentno=r.studentno-- left join
SELECT studentname,s.studentno,subjectno,studentresult 
FROM student AS s LEFT JOIN result AS r
ON s.studentno=r.studentno

組合

union運算符將兩個或更多查詢的結(jié)果組合起來，并生成一個結(jié)果集 ，其中包含來自union中參與查詢的提取行。

基本規(guī)則：

• 所有查詢的列數(shù)和列順序必須相同
• 每個查詢中涉及表的列的數(shù)據(jù)類型必須相同或兼容
• 通常返回的列名取自第一個查詢

-- 如果允許重復(fù)的值，請使用 UNION ALL。SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

函數(shù)

• 文本處理

LEFT() / RIGHT() :左邊或者右邊的字符

LOWER() / UPPER() ：轉(zhuǎn)換為小寫或者大寫

LTRIM() / RTRIM() : 去除左邊或者右邊的空格

LENGTH() : 長度，以字節(jié)為單位

SOUNDEX() : 轉(zhuǎn)換為語音值

• 匯總

AVG() ： 返回某列的平均值

COUNT() : 返回某列的行數(shù)

MAX（） ： 返回某列的最大值

MIN() : 返回某列的最小值

SUM（）：返回某列值之和

使用DISTINCT（唯一）可以讓匯總函數(shù)值匯總不同的值

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

匯總col1字段中不同的值的和

4、DDL數(shù)據(jù)定義語言

DDL的主要功能是定義數(shù)據(jù)庫對象（如：數(shù)據(jù)庫、數(shù)據(jù)表、視圖、索引等）

• 對于數(shù)據(jù)庫

create database test;
drop database test;
use test;

• 對于數(shù)據(jù)表

-- 普通創(chuàng)建
CREATE TABLE user (id int(10) unsigned NOT NULL COMMENT 'Id',username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用戶名',password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密碼',email varchar(64) NOT NULL DEFAULT 'default' COMMENT '郵箱'
) COMMENT='用戶表';-- 根據(jù)已有的表創(chuàng)建新表
create table vip_user as select * from user;-- 刪除數(shù)據(jù)表
drop table user;-- 添加列
alter table user add age int (3);-- 刪除列
alter table user drop column age;-- 添加主鍵
alter table user add primary key (id);-- 刪除主鍵
alter table user drop primary key;

• 視圖

定義：

視圖是基于SQL語句的結(jié)果集的可視化的表

視圖是虛擬的表，本身不包含數(shù)據(jù)，也就不能對其進(jìn)行索引操作。對視圖的操作和對普通標(biāo)的操作一樣

作用：

簡化復(fù)雜的SQL操作，比如復(fù)雜的聯(lián)結(jié)；

只使用實際表的一部分?jǐn)?shù)據(jù)；

通過只給用戶訪問視圖的權(quán)限，保證數(shù)據(jù)的安全性；

更改數(shù)據(jù)格式和表示

創(chuàng)建視圖

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

刪除視圖

DROP VIEW top_10_user_view;

• 索引

索引是一種用于快速查詢和檢索數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，其本質(zhì)可以看成是一種排序好的數(shù)據(jù)結(jié)構(gòu)。索引的作用就相當(dāng)于書的目錄

優(yōu)點：

使用索引可以大大加快數(shù)據(jù)的檢索速度（大大減少檢索的數(shù)據(jù)量），這也是創(chuàng)建索引的最主要的原因

通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性

缺點：

創(chuàng)建索引和維護(hù)索引需要耗費許多時間，但對表中的數(shù)據(jù)進(jìn)行增刪改的時候，如果有數(shù)據(jù)有索引，那么索引也需要動態(tài)的修改，會降低SQL執(zhí)行效率

索引需要物理文件存儲，也會耗費一定空間

創(chuàng)建索引：在數(shù)據(jù)庫中執(zhí)行一個操作，在表中建立索引

CREATE INDEX user_index
ON user(id);

添加索引：在已存在的表中增加新的索引

ALTER table user ADD INDEX user_index(id);

創(chuàng)建唯一索引

CREATE UNIQUE INDEX user_index
ON user (id);

刪除索引

ALTER TABLE user
DROP INDEX user_index;

• 約束

SQL約束用于規(guī)定表中的數(shù)據(jù)規(guī)則，如果存在違反約束的數(shù)據(jù)行為，行為會被約束終止。

約束可以在創(chuàng)建表時規(guī)定（通過 CREATE TABLE語句），或者在表創(chuàng)建之后規(guī)定（通過 ALTER TABLE語句）

約束類型：

NOT NULL ：指示某列不能存儲NULL值

UNIQUE：保證某列的某行必須由唯一的值

PRIMARY KEY：確保某列有唯一標(biāo)識，主鍵

CHECK:保證列中的值符合指定的條件

DEFAULT：規(guī)定沒有給列賦值時的默認(rèn)值

創(chuàng)建表時使用約束條件：

CREATE TABLE Users (Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用戶名',Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密碼',Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '郵箱地址',Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

5、TCL事務(wù)處理

TCL的主要功能是管理數(shù)據(jù)庫中的事務(wù)

• 不能回退select語句，回退select語句也沒意義；也不能回退create和drop語句
• MySQL默認(rèn)是隱式提交，每執(zhí)行一條語句就把這條語句當(dāng)成一個事務(wù)然后進(jìn)行提交。
• 當(dāng)出現(xiàn) START TRANSACTION 語句時，會關(guān)閉隱式提交；當(dāng) COMMIT 或 ROLLBACK 語句執(zhí)行后，事務(wù)會自動關(guān)閉，重新恢復(fù)隱式提交。
• 通過 set autocommit = 0 可以取消自動提交，直到set autocommit=1 才會提交。autocommit標(biāo)記是針對每個連接而不是針對服務(wù)器

-- 開始事務(wù)
START TRANSACTION;-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');-- 創(chuàng)建保留點 updateA
SAVEPOINT updateA;-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');-- 回滾到保留點 updateA
ROLLBACK TO updateA;-- 提交事務(wù)，只有操作 A 生效.回滾到了保留點A，所以后面B的插入操作不會被執(zhí)行
COMMIT;

6、權(quán)限控制 DCL

DCL的主要功能是控制用戶的訪問權(quán)限

要授予用戶賬戶權(quán)限，可以用GRANT命令；要撤銷用戶的權(quán)限，可以用REVOKE命令。

GRANT privilege,[privilege],.. ON privilege_level
TO user [IDENTIFIED BY password]
[REQUIRE tsl_option]
[WITH [GRANT_OPTION | resource_option]];

• 在GRANT關(guān)鍵字后指定一個或多個權(quán)限，如果授予用戶多個權(quán)限，則每個權(quán)限由逗號分隔
• ON privilege_level 確定權(quán)限應(yīng)用級別。MySQL支持global（* .*） ， database，table和列級別
• user 是要授予權(quán)限的用戶

使用示例：

-- 創(chuàng)建賬戶
CREATE USER myuser IDENTIFIED BY 'mypassword';-- 修改賬戶名
UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;-- 刪除賬戶
DROP USER myuser;-- 查看權(quán)限
SHOW GRANTS FOR myuser;-- 授予權(quán)限
GRANT SELECT, INSERT ON *.* TO myuser;-- 刪除權(quán)限
REVOKE SELECT, INSERT ON *.* FROM myuser;-- 更改密碼
SET PASSWORD FOR myuser = 'mypass';

7、存儲過程

• 存儲過程可以看成是對一系列SQL操作的批處理。存儲過程可以由觸發(fā)器，其他存儲過程以及Java等應(yīng)用程序調(diào)用

• 使用存儲過程的好處

代碼封裝：保證了一定的安全性；

代碼復(fù)用；

由于是預(yù)先編譯，因此具有很高的性能

• 創(chuàng)建存儲過程：

命令行中創(chuàng)建存儲過程需要自定義分隔符

包含in 、out 、 inout 三種參數(shù)

給變量賦值都需要用select into 語句

每次只能給一個變量賦值，不支持集合的操作

• 阿里巴巴Java開發(fā)手冊中強(qiáng)制禁止使用存儲過程。因為存儲過程難以調(diào)試和擴(kuò)展，更沒有移植性。

-- 創(chuàng)建存儲過程
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGINDECLARE c int;if a is null then set a = 0;end if;if b is null then set b = 0;end if;set sum  = a + b;
END
;;
DELIMITER ;-- 使用存儲過程
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

8、觸發(fā)器

觸發(fā)器是一種與表操作有關(guān)的數(shù)據(jù)庫對象，當(dāng)觸發(fā)器所在表上出現(xiàn)指定事件時，將調(diào)用該對象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行

使用觸發(fā)器的優(yōu)點

SQL觸發(fā)器提供了另一種檢查數(shù)據(jù)完整性的方法；

SQL觸發(fā)器可以捕獲數(shù)據(jù)庫層中業(yè)務(wù)邏輯中的錯誤；

SQL觸發(fā)器提供了另一種執(zhí)行計劃任務(wù)的方法。通過使用SQL觸發(fā)器，不必等待運行計劃任務(wù)，因為在對表中的數(shù)據(jù)進(jìn)行更改之前或之后會自動調(diào)用觸發(fā)器；

SQL觸發(fā)器對于審計表中數(shù)據(jù)的更改非常有用

使用觸發(fā)器的缺點

SQL觸發(fā)器只能提供擴(kuò)展驗證，并且不能替換所有驗證。必須在應(yīng)用程序?qū)又型瓿梢恍┖唵蔚尿炞C；

從客戶端應(yīng)用程序調(diào)用和執(zhí)行SQL觸發(fā)器是不可見的，因此很難弄清楚數(shù)據(jù)庫層中發(fā)生了什么

SQL觸發(fā)器可能會增加數(shù)據(jù)庫服務(wù)器的開銷

創(chuàng)建觸發(fā)器

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGINtrigger_statements
END;

當(dāng)觸發(fā)器的觸發(fā)條件滿足時，將會執(zhí)行BEGIN和END之間的觸發(fā)器執(zhí)行動作

查看觸發(fā)器

SHOW TRIGGERS;

刪除觸發(fā)器

DROP TRIGGER IF EXISTS trigger_insert_user;