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

摘要： MySQL資料庫的基本操作 建立資料庫CREATE DATABASE database_name database_name為要建立的資料庫的名稱 刪除資料庫DROP DATABASE database_name ...

MySQL資料庫的基本操作

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. 單欄位主鍵

      欄位名 資料型別 PRIMARY KEY [預設值]
      [CONSTRAINT <約束名>] PRIMARY KEY [欄位名]
      

   2. 多欄位聯合主鍵
      主鍵由多個欄位聯合組合而成。PRIMARY KEY [欄位1、欄位2...]
      其位置放置在定義完所有的主鍵之後
3. 使用外來鍵約束
   外來鍵用來在兩個表的資料之間建立連結，它可以是一列或者多列。一個表可以有一個或多個外來鍵。
   [CONSTRAINT<外來鍵名>] FOREIGN KEY 欄位1[,欄位2...] REFERENCES<主表名> 主鍵列1[,主鍵列2...]
4. 使用非空約束
   非空約束指欄位的值不能為空。對於使用了非空約束的欄位，如果使用者在新增資料時沒有指定值，資料庫系統會報錯。欄位名 資料型別 NOT NULL
5. 使用唯一性約束
   唯一性約束要求該列唯一，允許為空，但只能出現一個空值。
   唯一約束可以確保一列或者幾列不出現重複值。

   欄位名 資料型別 UNIQUE
   [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 [位置偏移量,] 行數

使用集合函式查詢

函式	作用
AVG()	返回某列的平均值
COUNT()	返回某列的行數
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

連線查詢

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次|

Linux公社的RSS地址 ：https://www.linuxidc.com/rssFeed.aspx

本文永久更新連結地址：https://www.linuxidc.com/Linux/2019-03/157880.htm