《MySQL資料庫應用從入門到精通(第二版)》學習筆記

顔色解釋：綠色标注的字型，即為sql語句的文法格式

• MySQL資料庫基本操作

建立資料庫：CREATE DATABASE database_name;

檢視資料庫：SHOW  DATABASES;

選擇資料庫：USE database_name;

删除資料庫：DROP DATABASE database_name;

• MySQL資料庫中存儲引擎和資料類型

存儲引擎指定了表的類型，即如何存儲和索引資料、是否支援事務等，同時存儲引擎也決定了表在計算機中的存儲方式。

資料類型：整數類型、浮點數類型、定點數類型、位類型、日期和時間類型、字元串類型

檢視MySQL支援的存儲引擎：SHOW  ENGINES;

檢視預設存儲引擎：SHOW VARIABLES LIKE  ‘storage_engine%’;

檢視MySQL幫助文檔支援的目錄清單：HELP CONTENTS;

• 表的操作

表中的資料庫對象包含列、索引和觸發器。其中：

• 列（Columns）:也稱屬性列，在具體建立表時，必須指定列的名字和資料類型；
• 索引（Indexes）：指根據指定的資料庫表列建立起來的順序，提供了快速通路資料的途徑且可監督表的資料，使其索引所指向的列中的資料不重複；
• 觸發器（Triggers）：指使用者定義的事物指令的集合，當對一個表中的資料進行插入、更新或删除時這組指令就會自動執行，可以用來確定資料的完整性和安全性。

建立表文法形式：

CREATE TABLE table_name(

    屬性名 資料類型，

    屬性名 資料類型，

    ……

    屬性名 資料類型

)

檢視表定義：DESCRIBE table_name;

檢視表詳細定義：SHOW CREATE TABLE table_name;

1. 表的增删改查操作

删除表：DROP TABLE table_name;

修改表名：ALTER TABLE old_table_name RENAME [TO] new_table_name;

在表的最後一個位置增加字段：ALTER TABLE table_name ADD 屬性名 屬性類型；

在表的第一個位置增加字段：ALTER TABLE table_name ADD 屬性名 屬性類型  FIRST；

在表的指定字段後增加字段：

ALTER TABLE table_name ADD 屬性名 屬性類型 AFTER 屬性名；

删除字段：ALTER TABLE table_name DROP 屬性名；

修改字段的資料類型：ALTER TABLE table_name MODIFY 屬性名 資料類型；

修改字段的名字：ALTER TABLE table_name CHANGE 舊屬性名 新屬性名 舊資料類型；

同時修改字段的名字和屬性：

ALTER TABLE table_name CHANGE 舊屬性名 新屬性名 新資料類型；

修改字段的順序：

              ALTER TABLE table_name MODIFY 屬性名1 資料類型 FIRST|AFTER 屬性名2；

1. 操作表的限制

    所謂完整性是指資料的準确性和一緻性，而完整性檢查指檢查資料的準确性和一緻性。

完整性限制

完整性限制關鍵字	含義
NOT NULL	限制字段的值不能為空
DEFAULT	設定字段的預設值
UNIQUE KEY(UK)	限制字段的值是惟一的
PRIMARY KEY(PK)	限制字段為表的主鍵，可以作為該表記錄的唯一辨別
AUTO_INCREMENT	限制字段的值為自動增加
FOREIGN KEY(FK)	限制字段為表的外鍵

設定非空限制（NOT NULL,NK）:

CREATE TABLE table_name(

      屬性名 資料類型 NOT NULL,

      …….

);

設定字段的預設值（DEFAULT）:

CREATE TABLE table_name(

屬性名 資料類型 DEFAULT 預設值,

……

);

設定唯一限制(UNIQUE,UK)：

CREATE TABLE table_name(

屬性名 資料類型 UNIQUE,

……

);

設定主鍵限制（PRIMARY KEY,PK）——單字段主鍵

CREATE TABLE table_name(

屬性名 資料類型 PRIMARY KEY,

……

);

設定主鍵限制（PRIMARY KEY,PK）——多字段主鍵

CREATE TABLE table_name(

屬性名 資料類型 PRIMARY KEY,

……

CONSTRAINT 限制名 PRIMARY KEY(屬性名，屬性名……)

);

設定字段值自動增加（AUTO_INCREMENT）

CREATE TABLE table_name(

屬性名 資料類型 AUTO_INCREMENT,

……

);

設定外鍵限制（FOREIGN KEY,FK）

CREATE TABLE table_name(

屬性名 資料類型 ,

屬性名 資料類型 ,

……

CONSTRAINT 外鍵限制名 FOREIGN  KEY(屬性名1)

    REFERENCES 表名(屬性名2)

……

);

• 資料的操作

插入完整資料記錄：

INSERT INTO table_name(field1,field2,……,fieldn)

      VALUES(value1,value2,……,valuen);

插入多條完整資料記錄：

INSERT INTO table_name(field1,field2,……,fieldn)

      VALUES(value11,value12,……,value1n),

(value21,value22,……,value2n),

……

(valuen1,valuen2,……,valuenn);

更新資料記錄：

UPDATE  table_name

    SET field1 = value1,

       Field2 = value2

    WHERE  條件;

删除資料記錄：

    DELETE FROM table_name WHERE 條件；

• 單表資料記錄查詢

簡單資料記錄查詢：

SELECT field1,field2,…,fieldn

     FROM table_name;

避免重複資料查詢——DISTINCT

SELECT  DISTINCT field1,field2,…,fieldn

     FROM table_name;

修改查詢出來的資料字段名——AS：

SELECT field1 AS otherfield1,,field2 AS otherfield2,…,fieldn AS otherfieldn

     FROM table_name;

1．條件資料記錄查詢

條件資料記錄查詢：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  條件;

補充：關于條件查詢語句可以包含如下功能：

• 帶關系運算符和邏輯運算符的條件資料查詢
• 帶BETWEEN  AND 關鍵字、NOT  BETWEEN  AND的條件資料查詢
• 帶IS NULL關鍵字的條件資料查詢
• 帶IN關鍵字的條件資料查詢
• 帶LIKE關鍵字的條件資料查詢

帶BETWEEN AND 關鍵字的範圍查詢(符合範圍)：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field BETWEEN values1 AND value2;

帶BETWEEN AND 關鍵字的範圍查詢（不符合範圍）：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field  NOT BETWEEN values1 AND value2;

帶IS NULL關鍵字的空值查詢：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field IS NULL;

帶IS NOT NULL關鍵字的不是空值查詢：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field IS NOT NULL;

帶IN關鍵字的集合查詢——在集合中：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field IN (value1,value2,…,valuen);

帶IN關鍵字的集合查詢——不在集合中：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field NOT  IN (value1,value2,…,valuen);

帶LIKE關鍵字的模糊查詢——模糊比對(通配符：%（多個），_（單個），%%（全部）

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field LIKE value;

帶LIKE關鍵字的模糊查詢——模糊不比對

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field  NOT LIKE value;

2．排序資料記錄查詢

    ASC(升序，預設)，DESC(降序)

排序資料記錄查詢文法：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  條件

ORDER BY fileldm1 [ASC|DESC] [,fileldm2 [ASC|DESC];

3．限制資料記錄查詢數量

限制資料記錄查詢數量文法形式：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  條件

LIMIT  offset_start,row_count;

4．統計函數和分組資料記錄查詢

統計函數：

• COUNT()函數：實作統計表中記錄的條數；
• AVG()函數：實作計算字段值的平均值；
• SUM()函數：實作計算字段值的總和；
• MAX()函數：實作查詢字段值的最大值
• MIN()函數：實作查詢字段值的最小值。

對于MySQL所支援的統計函數，如果所操作的表中沒有任何資料記錄，則COUNT()函數傳回資料0，而其他函數傳回NULL。

分組資料查詢：關鍵字GROUP BY

注意：GROUP BY單獨使用時，預設查詢出每個分組中随機一條記錄，具有很大的不确定性。如果想顯示每個分組中的字段，可以通過函數GROUP_CONCAT()函數來實作，該函數可以實作顯示每個分組中的指定字段值。

WHERE關鍵字主要用來實作條件限制資料記錄；

HIVING關鍵字來實作條件限制分組資料記錄。

分組資料查詢——實作統計功能分組查詢：

SELECT GROUP_CONCAT(field)

     FROM table_name

WHERE  條件

GROUP BY field2;

分組資料查詢——實作HAVING子句限定分組查詢：

SELECT  function (field)

     FROM table_name

WHERE  條件

GROUP BY field1,field2,…,fieldn

HAVING 條件;

• 多表資料記錄查詢

1. 關系資料操作簡介

關系資料操作中傳統的運算——并（UNION）、笛卡爾積（CARTESIAN PRODUCT）和專門針對資料庫操作的運算——連接配接（JOIN）。

并（UNION）就是把具有相同字段數目和字段類型的表合并到一起。

笛卡爾積（CARTESIAN PRODUCT）就是沒有連接配接條件表關系傳回的結果。

連接配接（JOIN）就是在表關系的笛卡爾積資料記錄中，按照相應字段值的比較條件進行選擇生成一個新的關系。連接配接分為内連接配接（INNER JOIN）、外連接配接（OUTER JOIN）和交叉連接配接（CROSS JOIN）。

• 内連接配接（INNER JOIN）就是在表關系的笛卡爾積資料記錄中，保留表關系中所有比對的資料記錄，舍棄不比對的資料記錄。按照比對的條件可以分為自然連接配接、等值連接配接和不等連接配接。

1. 自然連接配接（NATURAL JOIN）:就是表關系的笛卡爾積中，首先根據表關系中相同名稱的字段自動進行記錄比對，然後去掉重複的字段。
2. 等值連接配接：就是表關系的笛卡爾積中，選擇所比對字段值相等的資料記錄。
3. 不等連接配接：就是表關系的笛卡爾積中，選擇所比對字段值不相等的資料記錄。

• 外連接配接（OUTER JOIN）就是在表關系的笛卡爾積資料記錄中，不僅保留表關系中所有比對的資料記錄，而且還會保留部分不比對的資料記錄。按照保留不比對條件資料記錄來源可以分為左外連接配接（LEFT OUTER JOIN）、右外連接配接（RIGHT OUTER JOIN）和全外連接配接（FULL OUTER JOIN）

1. 左外連接配接：就是表關系的笛卡爾積中，除了選擇相比對的資料記錄，還包含關聯左邊表中不比對的資料記錄。
2. 右外連接配接：就是表關系的笛卡爾積中，除了選擇相比對的資料記錄，還包含關聯右邊表中不比對的資料記錄。
3. 全外連接配接：就是表關系的笛卡爾積中，除了選擇相比對的資料記錄，還包含關聯左右兩邊表中不比對的資料記錄。

1. 連接配接查詢SQL語句

MySQL中内連接配接資料查詢通過SQL語句“INNER JOIN … ON ”來實作，文法形式：

SELECT field1,field2,…,fieldn

     FROM join_tablename1 INNER JOIN join_tablename2 【INNER JOIN join_tablenamen】  ON  join_condition;

MySQL中外連接配接資料查詢通過SQL語句“OUTER JOIN … ON ”來實作，文法形式：

SELECT field1,field2,…,fieldn

          FROM join_tablename1  LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2

          ON  join_condition;

1. 合并查詢資料記錄SQL語句

在MySQL軟體中實作查詢資料記錄合并通過SQL語句UNION來實作，文法如下：

SELECT field1,field2,…,fieldn

    FROM tablename1

UNION | UNION ALL

SELECT field1,field2,…,fieldn

    FROM tablename2

UNION | UNION ALL

SELECT field1,field2,…,fieldn

    FROM tablename3

……;

補充：UNION 和UNION ALL的差別：前者會去掉重複資料記錄。

1. 子查詢

連接配接查詢實作多表資料查詢的性能很差，是以出現了連接配接查詢的替代者子查詢。

所謂子查詢，就是指在一個查詢中嵌套了其他的若幹查詢，即在一個SELECT語句中的WHERE 或FROM子句中包含另一個SELECT查詢語句。在查詢語句中，外層SELECT查詢語句稱為主查詢，WHERE子句中的SELECT查詢語句被稱為子查詢，也被稱為嵌套查詢。子查詢語句可以包含IN、ANY、ALL和EXISTS等關鍵字，除此之外，還可能包含比較運算符。

（1）理論上子查詢可以出現在查詢語句的任意位置，但實際中，子查詢常出現在WHERE和FROM子句中。

• WHERE子句中的子查詢：該位置處的子查詢一般傳回單行單列、多行單列、單行多列資料記錄
• FROM子句中的子查詢；該位置處的子查詢一般傳回多行多列資料記錄，可以當作一張臨時表

（2）關鍵字ANY用來表示主查詢的條件為滿足子查詢傳回查詢結果中任意一條資料記錄，該關鍵字有三種比對方式，分别如下：

• =ANY：其功能與關鍵字IN一樣
• >ANY(>=ANY)：比子查詢中傳回資料記錄中最小的還要大于（大于等于）資料記錄；
• <ANY(<=ANY)：比子查詢中傳回資料記錄中最大的還要小于（小于等于）資料記錄

（3）關鍵字ALL用來表示主查詢的條件為滿足子查詢傳回查詢結果中所有資料記錄，該關鍵字有兩種比對方式，分别如下：

• >ALL(>=ALL)：比子查詢中傳回資料記錄中最大的還要大于（大于等于）資料記錄；
• <ALL(<=ALL)：比子查詢中傳回資料記錄中最小的還要小于（小于等于）資料記錄

    （4）關鍵字EXISTS是一個布爾類型，當傳回結果集時為TRUE，不能傳回結果集時為FALSE。查詢時EXISTS對外表采用周遊方式逐條查詢，每次查詢都會比較EXISTS的條件語句。

• 使用MySQL運算符

MySQL軟體提供的運算符包含4種：

• 算術運算符：算術運算符包含：加（+）、減（-）、乘（*）、除（/）、求模（%、mod ）運算5種。
• 比較運算符：大于（>）、小于（<）、等于（=、<=>）、不等于（!=、<>）、大于等于（>=）、小于等于（<=）、存在于指定範圍（BETWEEN AND）、為空（IS NULL）、存在于指定集合（IN）、通配符比對（LIKE）、正規表達式比對（REGEXP）
• 邏輯運算符：與（AND 、&&）、或（OR、||）、非（NOT、！）、異或（XOR）
• 位運算符：按位與（&）、按位或（|）、按位取反（~）、按位異或（^）、按位左移（<<）、按位右移（>>）

• 使用MySQL常用函數

字元串函數、數值函數、日期函數、系統資訊函數。

1. 字元串函數

函數	功能
CANCAT(str1,str2,…strn)	連接配接字元串str1,str2,…,strn為一個完整字元串
INSERT(str,x,y,instr)	将字元串str從第x位置開始，y個字元長的子串替換為字元串instr
LOWER(str)	将字元串str中所有字元變為小寫
UPPER(str)	将字元串str中所有字元變為大寫
LEFT(str,x)	傳回字元串str中最左邊的x個字元
RIGHT(str,x)	傳回字元串str中最右邊的x個字元
LPAD(str,n,pad)	使用字元串pad對字元串str最左邊進行填充，直到長度為n個字元長度
RPAD(str,n,pad)	使用字元串pad對字元串str最右邊進行填充，直到長度為n個字元長度
LTRIM(str)	去掉字元串str左邊的空格
RTRIM(str)	去掉字元串str右邊的空格
REPEAT(str,x)	傳回字元串str重複x次的結果
REPLACE(str,a,b)	使用字元串b替換字元串str中所有出現的字元串a
STRCMP(str1,str2)	比較字元串str1和str2
TRIM(str)	去掉字元串str行頭和行尾的空格
SUBSTRING(str,x,y)	傳回字元串str中從x位置起y個字元長度的字元串

1. 數值函數

函數	功能
ABS(x)	傳回x的絕對值
CEIL(x)	傳回大于x的最大整數值
FLOOR(x)	傳回小于x的最大整數值
MOD(x,y)	傳回x模y的值
RAND()	傳回0~1内的随機數
ROUND(x,y)	傳回數值x的四舍五入後有y位小數的數值
TRUNCATE(x,y)	傳回數值x截斷為y位的數值

1.  日期和時間函數

函數	功能
CURDATE()	擷取目前日期
CURTIME()	擷取目前時間
NOW()	擷取目前的日期和時間
UNIX_TIMESTAMP(date)	擷取UNIX時間戳的日期值
FROM_UNIXTIME()	擷取UNIX時間戳的日期值
WEEK(date)	傳回日期date為一年中的第幾周
YEAR(date)	傳回日期date的年份
HOUR(time)	傳回時間time的小時值
MINUTE(time)	傳回時間time的分鐘值
MONTHNAME(date)	傳回時間time的月份值

1. 系統資訊函數

函數	功能
VERSION()	傳回資料庫的版本号
DATABASE()	傳回目前資料庫名
USER()	傳回目前使用者
LAST_INSERT_ID()	傳回最近生成的AUTO_INCREMENT值

enjoy it!