資料庫、數據表的基本操作及查詢數據

資料庫的基本操作

1. 創建資料庫 CREATE DATABASE database_name
   database_name為要創建的資料庫的名稱
2. 刪除資料庫 DROP DATABASE database_name
   database_name為要刪除的資料庫的名稱

3. 資料庫存儲引擎
   資料庫存儲引擎是資料庫底層軟體組成，資料庫管理系統(DBMS)使用數據引擎進行創建、查詢、更新和刪除數據操作。
   MySQL的核心就是存儲引擎。

   ---

   存儲引擎比較
   |功能|MyISAM|Memory|InnoDB|Archive|
   |---|---|---|---|---|
   |存儲限制|256TB|RAM|64TB|None|
   |支持事務|No|No|Yes|No|
   |支持全文索引|Yes|No|No|No|
   |支持數索引|Yes|Yes|Yes|No|
   |支持哈希索引|No|Yes|No|No|
   |支持數據緩存|No|N/A|Yes|No|
   |支持外鍵|No|No|Yes|No|

數據表的基本操作

創建數據表

1. 創建表的語法形式
   SQL CREATE TABLE<表名> ( 欄位名1 數據類型 [列級別約束條件] [預設值], 欄位名2 數據類型 [列級別約束條件] [預設值], …… [表級別約束條件] );

2. 使用主鍵約束

   主鍵約束要求主鍵列的數據唯一，並且不允許為空。他能唯一地標識表中的一條記錄，可以結合外鍵來定義不同數據表之間的關係，並且可以加快資料庫查詢的速度。

   1. 單欄位主鍵
      1. 在定義列的同時指定主鍵。 欄位名 數據類型 PRIMARY KEY [預設值]
      2. 在定義完所有列之後指定主鍵。 [CONSTRAINT <約束名>] PRIMARY KEY [欄位名]
   2. 多欄位聯合主鍵
      主鍵由多個欄位聯合組合而成。 PRIMARY KEY [欄位1、欄位2...]
      其位置放置在定義完所有的主鍵之後
3. 使用外鍵約束
   外鍵用來在兩個表的數據之間建立鏈接，它可以是一列或者多列。一個表可以有一個或多個外鍵。
   [CONSTRAINT<外鍵名>] FOREIGN KEY 欄位1[,欄位2...] REFERENCES<主表名> 主鍵列1[,主鍵列2...]
4. 使用非空約束
   非空約束指欄位的值不能為空。對於使用了非空約束的欄位，如果用戶在添加數據時沒有指定值，資料庫系統會報錯。 欄位名 數據類型 NOT NULL
5. 使用唯一性約束
   唯一性約束要求該列唯一，允許為空，但只能出現一個空值。
   唯一約束可以確保一列或者幾列不出現重覆值。
   1. 在定義完列之後直接指定唯一約束。欄位名 數據類型 UNIQUE
   2. 在定義完所有列之後指定唯一約束。[CONSTRAINT<約束名>] UNIQUE(<欄位名>)
6. 使用預設約束
   預設約束指定某列的預設值。 欄位名 數據類型 DEFAULT 預設值

7. 設置表的屬性值自動增加
   在資料庫應用中，可以通過為表的主鍵添加 AUTO_INCREMENT關鍵字來實現：當每新增加一條記錄，使該主鍵自動加一。一個表只能有一個欄位使用 AUTO_INCREMENT約束，且該欄位必須為主鍵的一部分。其約束的欄位可以是任何整數類型。
   欄位名 數據類型 AUTO_INCREMENT

查看數據表結構

1. 查看表基本結構語句
   DESCRIBE 表名; 或者簡寫為 DESC 表名;
2. 查看表詳細結構語句
   SHOW CREATE TABLE <表名/G>;
   如果不加 /G參數，顯示的結果可能非常混亂，加上參數 /G後，可使顯示結果更加直觀，易於查看。

修改數據表

1. 修改表名
   ALTER TABLE <舊表名> RENAME [TO] <新表名>;
2. 修改欄位的數據類型
   ALTER TABLE <表名> MODIFY <欄位名> <數據類型>;
3. 修改欄位名
   ALTER TABLE <表名> CHANGE <舊欄位名> <新欄位名> <新數據類型>;
4. 添加欄位
   ALTER TABLE <表名> ADD <新欄位名> <新數據類型> [約束條件] [FIRST|AFTER 已存在欄位名];
5. 刪除欄位
   ALTER TABLE <表名> DROP <欄位名>;
6. 修改欄位的排列位置
   ALTER TABLE <表名> MODIFY <欄位1> <數據類型> FIRST|AFTER <欄位2>;
7. 更改表的存儲引擎
   ALTER TABLE <表名> ENGINE=<更改後的存儲引擎名>;
8. 刪除表的外鍵約束
   ALTER TABLE <表名> DROP FOREIGN KEY <外鍵約束名>;

刪除數據表

DROP TABLE [IF EXISTS] 表1,表2...表n;

查詢數據

基本查詢語句

SELECT
    {*|<欄位列表>}
    [
        FROM <表1>,<表2>...
        [WHERE <表達式>]
        [GROUP BY <group by definition>]
        [HAVING <expression>[{<operator><expression>}...]]
        [ORDER BY <order by definition>]
        [LIMIT [offset,] <row count>]
    ]
    
 SELECT [欄位1,欄位2,...,欄位n]
 FROM [表或視圖]
 WHERE [查詢條件];

單表查詢

1. 查詢所有欄位
   SELECT * FROM 表名;
2. 查詢指定欄位
   SELECT 欄位名1[,欄位名2,...,欄位名n] FROM 表名;
3. 查詢指定記錄
   在 SELECT語句中，通過 WHERE子句可以對數據進行過濾。
   SELECT 欄位名1[,欄位名2,...,欄位名n] FROM 表名 WHERE 查詢條件;
4. 帶 IN關鍵字的查詢
   IN操作符用來查詢滿足指定範圍內的條件的記錄，使用 IN操作符，將所有檢索條件用括弧括起來，檢索條件之間用逗號隔開，只要滿足條件範圍內的一個值即為匹配項。
   在 IN關鍵字前面加上 NOT即可使得查詢的結果正好相反。
5. 帶 BETWEEN AND的範圍查詢
   BETWEEN AND用來查詢某個範圍內的值，該操作符需要有兩個參數，即範圍的開始值和結束。如果欄位值滿足指定的範圍查詢條件，則這些記錄被返回。
   同樣，在BETWEEN AND關鍵字前面加上 NOT即可使得查詢的結果正好相反。
6. 帶 LIKE的字元匹配查詢
   LIKE關鍵字即是使用通配符來進行匹配查找。通配符是一種在SQL的 WHERE條件子句中擁有特殊意思的字元，可以和 LIKE一起使用的通配符有 %和 _。
   百分號通配符 %，匹配任意長度的字元，甚至包括零字元。
   下劃線通配符 _ ，一次只能匹配任意一個字元。
7. 查詢空值
   空值不同於0，也不同於空字元串。空值一般表示數據未知、不適用或將在以後添加數據。
   在 SELECT語句中使用 IS NULL子句，可以查詢某欄位內容為空記錄。
8. 帶 AND的多條件查詢
   AND主要用於 WHERE子句中，用來鏈接兩個甚至多個查詢條件，表示所有的條件都需要滿足才會返回值。
9. 帶 OR的多條件查詢
   OR也主要用於 WHERE子句中，用來鏈接兩個甚至多個查詢條件，表示所有的條件僅需滿足其中之一項便會返回值。
10. 查詢結果不重覆
    在 SELECT語句中，使用 DISTINCT關鍵字來指示MySQL消除重覆的記錄。
    SELECT DISTINCT 欄位名 FROM 表名;
11. 對查詢結果排序
    用 ORDER BY語句來對查詢的結果進行排序。
    在後面添加 DESC表示降序排序
    在後面添加 ASC或預設，表示升序排序
12. 分組查詢
    在MySQL中使用 GROUP BY來對數據進行分組
    [GROUP BY 欄位] [HAVING <條件表達式>] [WITH ROLLUP]
HAVING 關鍵字用來過濾數據，因為 WHERE不能和 GROUP BY混用
    WITH ROLLUP關鍵字是在所有查詢出的記錄之後增加一條記錄，該記錄計算查詢出的所有記錄的總和，即統計記錄數量。但是ROLLUP和能夠與 GROUP BY同時使用的ORDER BY不能同時使用。
13. 使用 LIMIT限制查詢結果的數量
    LIMIT [位置偏移量,] 行數

使用集合函數查詢

連接查詢

1. 內連接查詢
   在內連接查詢中，只有滿足條件的記錄才能出現在結果關係中。兩個表之間的關係通過 (INNER) JOIN指定。 使用這種語法的時候，連接的條件使用 ON子句給出，而不是用 WHERE。
2. 外連接查詢
   1. LEFT JOIN左連接
      返回包括左表中的所有記錄和右表中連接欄位相等的記錄。
   2. RIGHT JOIN右連接
      返回包括右表中的所有記錄和左表中連接欄位相等的記錄。
3. 複合條件連接查詢
   複合條件連接查詢是在連接查詢的過程中，通過添加過濾條件，限制查詢的結果，使查詢的結果更加準確。

子查詢

子查詢指一個查詢語句嵌套在另一個查詢語句內部的查詢。

1. 帶 ANY、SOME關鍵字的子查詢
   ANY和 SOME關鍵字是同義詞，表示滿足其中任一條件，它們允許創建一個表達式對子查詢的返回值列表進行比較，只要滿足內層子查詢中的任何一個比較條件，就返回一個結果作為外層查詢的條件。
2. 帶 ALL關鍵字的子查詢
   使用 ALL時，需要同時滿足所有內層查詢的條件。
3. 帶 EXISTS關鍵字的子查詢
   EXISTS關鍵字後面的參數是一個任一的子查詢，系統對子查詢進行運算以判斷它是否返回行，如果至少返回一行，那麼 EXISTS的結果為 true，此時外層查詢語句將進行查詢；如果子查詢沒有返回任何行，那麼 EXISTS的結果為 false，此時外層語句不進行查詢。
4. 帶 IN關鍵字的子查詢
   使用 IN關鍵字進行子查詢時，內層查詢語句僅僅返回一個數據列，這個數據列里的值將提供給外層查詢語句進行比較操作。當外層查詢語句內只要有一個內層查詢語句返回的數據列中的數據時，則判斷為滿足條件，外層查詢語句將進行查詢。
5. 帶比較運算符的子查詢
   子查詢可以使用如 '<','<=','=','>','>=','!='等比較運算符。

合併查詢結果

利用 UNION關鍵字，可以給出多條 SELECT語句，並將他們的結果組合成單個結果集。合併時，兩個表對應的列數和數據類型必須相同。各個 SELECT語句之間使用 UNION或 UNION ALL關鍵字分割。 UNION不適用關鍵字 ALL，執行的時候刪除重覆的記錄，所有返回的行都是唯一的；使用關鍵字 ALL的作用時不刪除重覆行也不對結果進行自動排序。

為表和欄位取別名

1. 為表取別名
   表名 [AS] 表別名
2. 為欄位取別名
   列名 [AS] 列別名

使用正則表達式查詢

MYSQL中使用 REGEXP關鍵字指定正則表達式的字元匹配模式。

正則表達式常用字元匹配列表
|選項|說明|
|:---:|---|
|^|匹配文本的開始字元|
|$|匹配文本的結束字元|
|.|匹配任何單子符|
|*|匹配零個或多個在他前面的字元|
|+|匹配前面的字元1次或多次|
|<字元串>|匹配保護指定的字元串的文本|
|[字元集合]|匹配字元集合中的任何一個字元|
|[^]|匹配不在括弧中的任何字元|
|字元串{n,}|匹配前面的字元串至少n次|
|字元串{n,m}|匹配前面的字元串至少n次，至多m次|