SQL數(shù)據(jù)庫的基礎(chǔ)知識詳細(xì)說明

如果說前端應(yīng)用為網(wǎng)絡(luò)世界搭建起了一座座房子，那么數(shù)據(jù)庫數(shù)據(jù)就是住進(jìn)這些房子的人。

網(wǎng)絡(luò)世界（或者編程者的世界）與人類世界是相反的。編程世界的技術(shù)核心與真相，是在底層的。個人認(rèn)為，無論是用怎樣的架構(gòu)部署，用怎樣的語言編碼或者方式展示，web應(yīng)用和手機(jī)應(yīng)用的底層，就是數(shù)據(jù)庫里面的數(shù)據(jù)。在這些數(shù)據(jù)下面，有更底層的東西，值得我們?nèi)W(xué)習(xí)、去探索、去挖掘?；蛟S哪天一不小心，我們就能挖到這個世界的真相。

思維導(dǎo)圖：

1- 思維導(dǎo)圖

1、數(shù)據(jù)庫的定義

數(shù)據(jù)庫是“按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫”。是一個長期存儲在計算機(jī)內(nèi)的、有組織的、有共享的、統(tǒng)一管理的數(shù)據(jù)集合。

1、 數(shù)據(jù)庫是一個實體，它是能夠合理保管數(shù)據(jù)的“倉庫”，用戶在該“倉庫”中存放要管理的事務(wù)數(shù)據(jù)，“數(shù)據(jù)”和“庫”兩個概念結(jié)合成為數(shù)據(jù)庫。

2、 數(shù)據(jù)庫是數(shù)據(jù)管理的新方法和技術(shù)，它能更合適的組織數(shù)據(jù)、更方便的維護(hù)數(shù)據(jù)、更嚴(yán)密的控制數(shù)據(jù)和更有效的利用數(shù)據(jù)。

2、數(shù)據(jù)庫管理系統(tǒng)

數(shù)據(jù)庫管理系統(tǒng)(Database Management System)是一種操縱和管理數(shù)據(jù)庫的大型軟件，用于建立、使用和維護(hù)數(shù)據(jù)庫，簡稱DBMS。它對數(shù)據(jù)庫進(jìn)行統(tǒng)一的管理和控制，以保證數(shù)據(jù)庫的安全性和完整性。用戶通過DBMS訪問數(shù)據(jù)庫中的數(shù)據(jù)，數(shù)據(jù)庫管理員也通過DBMS進(jìn)行數(shù)據(jù)庫的維護(hù)工作。它可以支持多個應(yīng)用程序和用戶用不同的方法在同時或不同時刻去建立，修改和詢問數(shù)據(jù)庫。大部分DBMS提供數(shù)據(jù)定義語言DDL（Data Definition Language）和數(shù)據(jù)操作語言DML（Data Manipulation Language），供用戶定義數(shù)據(jù)庫的模式結(jié)構(gòu)與權(quán)限約束，實現(xiàn)對數(shù)據(jù)的追加、刪除等操作。

3、數(shù)據(jù)庫類型

3.1、關(guān)系型數(shù)據(jù)庫

關(guān)系型數(shù)據(jù)庫，存儲的格式可以直觀地反映實體間的關(guān)系。關(guān)系型數(shù)據(jù)庫和常見的表格比較相似，關(guān)系型數(shù)據(jù)庫中表與表之間是有很多復(fù)雜的關(guān)聯(lián)關(guān)系的。 常見的關(guān)系型數(shù)據(jù)庫有Mysql，SqlServer等。

雖然關(guān)系型數(shù)據(jù)庫有很多，但是大多數(shù)都遵循SQL（結(jié)構(gòu)化查詢語言，Structured Query Language）標(biāo)準(zhǔn)。 常見的操作有查詢，新增，更新，刪除，求和，排序等

3.2 非關(guān)系型數(shù)據(jù)庫(NoSQL)

NoSql數(shù)據(jù)庫如MongoDB、Redis、Memcache出于簡化數(shù)據(jù)庫結(jié)構(gòu)、避免冗余、影響性能的表連接、摒棄復(fù)雜分布式的目的被設(shè)計。

非關(guān)系型數(shù)據(jù)庫的分類：

（1）鍵值對存儲（key-value）：代表軟件Redis，它的優(yōu)點能夠進(jìn)行數(shù)據(jù)的快速查詢，而缺點是需要存儲數(shù)據(jù)之間的關(guān)系。

（2）列存儲：代表軟件Hbase，它的優(yōu)點是對數(shù)據(jù)能快速查詢，數(shù)據(jù)存儲的擴(kuò)展性強(qiáng)。而缺點是數(shù)據(jù)庫的功能有局限性。

（3）文檔數(shù)據(jù)庫存儲：代表軟件MongoDB，它的優(yōu)點是對數(shù)據(jù)結(jié)構(gòu)要求不特別的嚴(yán)格。而缺點是查詢性的性能不好，同時缺少一種統(tǒng)一查詢語言。

（4）圖形數(shù)據(jù)庫存儲：代表軟件InfoGrid，它的優(yōu)點可以方便的利用圖結(jié)構(gòu)相關(guān)算法進(jìn)行計算。而缺點是要想得到結(jié)果必須進(jìn)行整個圖的計算，而且遇到不適合的數(shù)據(jù)模型時，圖形數(shù)據(jù)庫很難使用。

3.3 NoSQL 與關(guān)系型數(shù)據(jù)庫的區(qū)別

首先一般非關(guān)系型數(shù)據(jù)庫是基于CAP模型，而傳統(tǒng)的關(guān)系型數(shù)據(jù)庫是基于ACID模型的。

其次在 數(shù)據(jù)存儲結(jié)構(gòu)、可擴(kuò)展性、數(shù)據(jù)一致性上，兩者有一定的區(qū)別。

CAP定理：在理論計算機(jī)科學(xué)中，CAP定理（CAP theorem），又被稱作布魯爾定理（Brewer's theorem），它指出對于一個分布式計算系統(tǒng)來說，不可能同時滿足以下三點：

一致性（Consistency)（所有節(jié)點在同一時間具有相同的數(shù)據(jù)）

可用性（Availability）（保證每個請求不管成功或者失敗都有響應(yīng)）

分隔容忍（Partition tolerance）（系統(tǒng)中任意信息的丟失或失敗不會影響系統(tǒng)的繼續(xù)運作）

ACID模型：ACID，是指數(shù)據(jù)庫管理系統(tǒng)（DBMS）在寫入/異動資料的過程中，為保證交易（transaction）是正確可靠的，所必須具備的四個特性：

原子性（Atomicity，或稱不可分割性）、

一致性（Consistency）

隔離性（Isolation，又稱獨立性）

持久性（Durability）。

4、分布式數(shù)據(jù)庫

所謂的分布式數(shù)據(jù)庫技術(shù)，就是結(jié)合了數(shù)據(jù)庫技術(shù)與分布式技術(shù)的一種結(jié)合。具體指的是把那些在地理意義上分散開的各個數(shù)據(jù)庫節(jié)點，但在計算機(jī)系統(tǒng)邏輯上又是屬于同一個系統(tǒng)的數(shù)據(jù)結(jié)合起來的一種數(shù)據(jù)庫技術(shù)。

5、SQL定義與語法

5.1 什么是SQL

SQL 是用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)的計算機(jī)語言

SQL 指結(jié)構(gòu)化查詢語言

SQL 使我們有能力訪問數(shù)據(jù)庫

SQL 是一種 ANSI 的標(biāo)準(zhǔn)計算機(jī)語言

5.2 語法

SQL 對大小寫不敏感。

SQL 分為兩個部分：數(shù)據(jù)操作語言 (DML) 和 數(shù)據(jù)定義語言 (DDL)。

DML部分：

SELECT - 從數(shù)據(jù)庫表中獲取數(shù)據(jù)

UPDATE - 更新數(shù)據(jù)庫表中的數(shù)據(jù)

DELETE - 從數(shù)據(jù)庫表中刪除數(shù)據(jù)

INSERT INTO - 向數(shù)據(jù)庫表中插入數(shù)據(jù)

DDL部分：

CREATE DATABASE - 創(chuàng)建新數(shù)據(jù)庫

ALTER DATABASE - 修改數(shù)據(jù)庫

CREATE TABLE - 創(chuàng)建新表

ALTER TABLE - 變更（改變）數(shù)據(jù)庫表

DROP TABLE - 刪除表

CREATE INDEX - 創(chuàng)建索引（搜索鍵）

DROP INDEX - 刪除索引

6、SQL基本語句

6.1、SELECT語句

SELECT 列名稱 FROM 表名稱 ；SELECT * FROM 表名稱。

SELECT 語句用于從表中選取數(shù)據(jù)。

結(jié)果被存儲在一個結(jié)果表中（稱為結(jié)果集）。

星號（*）是選取所有列的快捷方式。

6.2、distinct語句

關(guān)鍵詞 DISTINCT 用于返回唯一不同的值。

SELECT DISTINCT 列名稱 FROM 表名稱

6.3、where語句

WHERE 子句用于規(guī)定選擇的標(biāo)準(zhǔn)

SELECT 列名稱 FROM 表名稱 WHERE 列 運算符 值；

運算符：= <> > < >= <= BETWEEN LIKE AND OR

6.4、AND & OR語句

AND 和 OR 運算符用于基于一個以上的條件對記錄進(jìn)行過濾。

AND 和 OR 可在 WHERE 子語句中把兩個或多個條件結(jié)合起來。

如果第一個條件和第二個條件都成立，則 AND 運算符顯示一條記錄。

如果第一個條件和第二個條件中只要有一個成立，則 OR 運算符顯示一條記錄。

6.5、Order By語句

ORDER BY 語句用于根據(jù)指定的列對結(jié)果集進(jìn)行排序。

ORDER BY 語句默認(rèn)按照升序（AES） 對記錄進(jìn)行排序。

如果您希望按照降序?qū)τ涗涍M(jìn)行排序，可以使用 DESC 關(guān)鍵字。

6.6、insert 語句

INSERT INTO 語句用于向表格中插入新的行；

INSERT INTO 表名稱 VALUES (值1, 值2,....)

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

6.7、update 語句

Update 語句用于修改表中的數(shù)據(jù)。

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

6.7、delete語句

DELETE 語句用于刪除表中的行。

DELETE FROM 表名稱 WHERE 列名稱 = 值

6.8、Top子句

TOP 子句用于規(guī)定要返回的記錄的數(shù)目。

注釋：并非所有的數(shù)據(jù)庫系統(tǒng)都支持 TOP 子句。

SQL Server 中：

SELECT TOP number|percent column_name(s) FROM table_name

MySql中：

SELECT column_name(s) FROM table_name LIMIT number

Oracle中：

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number

6.9、通配符

在搜索數(shù)據(jù)庫中的數(shù)據(jù)時，SQL 通配符可以替代一個或多個字符。

SQL 通配符必須與 LIKE 運算符一起使用。

% 替代一個或多個字符

_ 僅替代一個字符

[charlist] 字符列中的任何單一字符

[^charlist] 或者 [!charlist] 不在字符列中的任何單一字符

6.10、IN操作符

IN 操作符允許我們在 WHERE 子句中規(guī)定多個值。

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

6.11、Aliases別名

通過使用 SQL，可以為列名稱和表名稱指定別名（Alias）。

SELECT column_name AS alias_name FROM table_name

7、SQL基本數(shù)據(jù)庫操作

7.1、JOIN表連接

SQL join 用于根據(jù)兩個或多個表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)。

JOIN: 如果表中有至少一個匹配，則返回行

LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行

RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行

FULL JOIN: 只要其中一個表中存在匹配，就返回行

關(guān)鍵字語法：

SELECT column_name(s)

FROM table_name1

JOIN（或者INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN） table_name2

ON table_name1.column_name=table_name2.column_name

7.2、UNION合并

UNION 操作符用于合并兩個或多個 SELECT 語句的結(jié)果集。

注意：UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條 SELECT 語句中的列的順序必須相同。

關(guān)鍵字語法：

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

注釋：默認(rèn)地，UNION 操作符選取不同的值。如果允許重復(fù)的值，請使用 UNION ALL。

7.3、表備份

SQL SELECT INTO 語句可用于創(chuàng)建表的備份復(fù)件。

SELECT INTO 語句從一個表中選取數(shù)據(jù)，然后把數(shù)據(jù)插入另一個表中。

SELECT INTO 語句常用于創(chuàng)建表的備份復(fù)件或者用于對記錄進(jìn)行存檔。

語法：

SELECT * （或者指定列column_name(s)）

INTO new_table_name [IN externaldatabase]

FROM old_tablename

注意：可以添加where子句，或者join連接等。

7.4、創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE 用于創(chuàng)建數(shù)據(jù)庫。

語法：CREATE DATABASE database_name

7.5、創(chuàng)建數(shù)據(jù)庫表

CREATE TABLE 語句用于創(chuàng)建數(shù)據(jù)庫中的表。

語法：

CREATE TABLE 表名稱

(

列名稱1 數(shù)據(jù)類型,

列名稱2 數(shù)據(jù)類型,

列名稱3 數(shù)據(jù)類型,

....

)

7.6、創(chuàng)建索引

CREATE INDEX 語句用于在表中創(chuàng)建索引。

在不讀取整個表的情況下，索引使數(shù)據(jù)庫應(yīng)用程序可以更快地查找數(shù)據(jù)。

創(chuàng)建簡單索引的語法（允許使用重復(fù)的值）：

CREATE INDEX index_name ON table_name (column_name)；

在表上創(chuàng)建一個唯一的索引。唯一的索引意味著兩個行不能擁有相同的索引值。

CREATE UNIQUE INDEX index_name ON table_name (column_name)；

7.7、DROP語句

通過使用 DROP 語句，可以輕松地刪除索引、表和數(shù)據(jù)庫。

DROP INDEX index_name 刪除索引，但是不同的數(shù)據(jù)庫有不同的用法：

例如MySQL：ALTER TABLE table_name DROP INDEX index_name

SQL Server：DROP INDEX table_name.index_name

DROP TABLE 表名稱 ：刪除表

DROP DATABASE 數(shù)據(jù)庫名稱 ：刪除數(shù)據(jù)庫

TRUNCATE TABLE 表名稱 ：除去表內(nèi)的數(shù)據(jù)，但并不刪除表本身

7.8 ALTER 語句

ALTER TABLE 語句用于在已有的表中添加、修改或刪除列。

例如：

ALTER TABLE table_name ADD column_name datatype

8、數(shù)據(jù)類型

8.1、常用的數(shù)據(jù)類型：

8-常用的數(shù)據(jù)類型圖

8.2、Microsoft Access、MySQL 以及 SQL Server 所使用的數(shù)據(jù)類型和范圍。

請參考W3school：SQL數(shù)據(jù)類型

9、約束（Constraints）

約束用于限制加入表的數(shù)據(jù)的類型。

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

主要約束：

NOT NULL 非空

NOT NULL 約束強(qiáng)制列不接受 NULL 值。

NOT NULL 約束強(qiáng)制字段始終包含值。這意味著，如果不向字段添加值，就無法插入新記錄或者更新記錄。

UNIQUE 唯一標(biāo)識

UNIQUE 約束唯一標(biāo)識數(shù)據(jù)庫表中的每條記錄。

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 擁有自動定義的 UNIQUE 約束。

請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。

PRIMARY KEY 主鍵

PRIMARY KEY 約束唯一標(biāo)識數(shù)據(jù)庫表中的每條記錄。

主鍵必須包含唯一的值。

主鍵列不能包含 NULL 值。

每個表都應(yīng)該有一個主鍵，并且每個表只能有一個主鍵。

AUTO INCREMENT 字段

我們通常希望在每次插入新記錄時，自動地創(chuàng)建主鍵字段的值。

我們可以在表中創(chuàng)建一個 auto-increment 字段。

FOREIGN KEY 外鍵

一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY。

CHECK 限制值的范圍

DEFAULT 默認(rèn)值

10、視圖View

10.1、什么是視圖

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

視圖包含行和列，就像一個真實的表。視圖中的字段就是來自一個或多個數(shù)據(jù)庫中的真實的表中的字段。我們可以向視圖添加 SQL 函數(shù)、WHERE 以及 JOIN 語句，我們也可以提交數(shù)據(jù)，就像這些來自于某個單一的表。

注釋：數(shù)據(jù)庫的設(shè)計和結(jié)構(gòu)不會受到視圖中的函數(shù)、where 或 join 語句的影響。

10.2、語法

創(chuàng)建視圖

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

注釋：視圖總是顯示最近的數(shù)據(jù)。每當(dāng)用戶查詢視圖時，數(shù)據(jù)庫引擎通過使用 SQL 語句來重建數(shù)據(jù)。

更新視圖

CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

刪除視圖

DROP VIEW view_name

10.3、視圖的作用

視圖僅支持查詢，不支持增刪改等數(shù)據(jù)操作。您可以將視圖當(dāng)作是一種臨時表。

視圖的作用：

1、 提高了sql代碼的復(fù)用性。

當(dāng)一個查詢你需要頻頻的作為子查詢使用時，視圖可以簡化代碼，直接調(diào)用而不是每次都去重復(fù)寫這個東西。

2、 提高了數(shù)據(jù)的安全性。

系統(tǒng)的數(shù)據(jù)庫管理員，需要給他人提供一張表的某兩列數(shù)據(jù)，而不希望他可以看到其他任何數(shù)據(jù)，這時可以建一個只有這兩列數(shù)據(jù)的視圖，然后把視圖公布給他。

11、索引

索引是一種特殊的查詢表，可以被數(shù)據(jù)庫搜索引擎用來加速數(shù)據(jù)的檢索。簡單說來，索引就是指向表中數(shù)據(jù)的指針。數(shù)據(jù)庫的索引同書籍后面的索引非常相像。

盡管創(chuàng)建索引的目的是提升數(shù)據(jù)庫的性能，但是還是有一些情況應(yīng)當(dāng)避免使用索引。下面幾條指導(dǎo)原則給出了何時應(yīng)當(dāng)重新考慮是否使用索引：

1、小的數(shù)據(jù)表不應(yīng)當(dāng)使用索引；

2、需要頻繁進(jìn)行大批量的更新或者插入操作的表；

3、如果列中包含大數(shù)或者 NULL 值，不宜創(chuàng)建索引；

4、頻繁操作的列不宜創(chuàng)建索引。

SQL中的索引分為兩種，一種為聚集索引和非聚集索引。

12、函數(shù)

常用的函數(shù)：

AVG () : 返回數(shù)值列的平均值。NULL 值不包括在計算中

COUNT() : 返回匹配指定條件的行數(shù)

MAX() : 返回一列中的最大值。NULL 值不包括在計算中

MIN() : 返回一列中的最小值。NULL 值不包括在計算中

SUM() : 返回數(shù)值列的總數(shù)（總額）

不常用：

FIRST() : 返回指定的字段中第一個記錄的值

LAST() : 返回指定的字段中最后一個記錄的值。

UCASE() : 把字段的值轉(zhuǎn)換為大寫

LCAS() : 把字段的值轉(zhuǎn)換為小寫

MID() : 用于從文本字段中提取字符

LEN() : 返回文本字段中值的長度

ROUND() : 用于把數(shù)值字段舍入為指定的小數(shù)位數(shù)

NOW() : 返回當(dāng)前的日期和時間

FORMA() : 用于對字段的顯示進(jìn)行格式化

REPLACE() : 字符串替換函數(shù)

CONCAT():將兩個字符串連接為一個字符串

GROUP BY 語句：

合計函數(shù) (比如 SUM) 常常需要添加 GROUP BY 語句。

語法：

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING 語句：

在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無法與合計函數(shù)一起使用。

語法：

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value

例如：

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000