MySQL學習記錄

什麼是資料庫？

資料庫Database是按照資料結構來組織、儲存和管理資料的倉庫。常見的資料庫有Oracle、DB2、SQL Server、MySQL等…

MySQL是一個關係型資料庫管理系統，開源免費，由瑞典MySQL AB公司開發，目前屬於Oracle旗下產品。

相關術語
DB(Database)：資料庫是儲存資料的集合。
DBS(Database System)：資料庫系統，由資料庫和資料庫管理軟體組成。
DBMS(Database Management System)：資料庫管理系統，是操作和管理資料庫的一個系統。
SQL(Structured Query Language)：結構化查詢語言，是資料庫的程式設計語言

MySQL相關命令列操作

配置檔案：my.cnf
登入資訊中需要注意的：
---命令列需要以`;`或者是`g`結尾，不然無法結束；
---可以通過`help`或者`h`或者`?`加上相關關鍵字來檢視手冊；
---`c`可以取消當前命令的執行；

mysql -uroot -p[密碼]            #登入MySQL
mysql -uroot -p -D db_name      #登入MySQL的同時開啟資料庫
exit;                        #退出MySQL
quit;                        #退出MySQL
q;                            #退出MySQL
ctrl+c                        #退出MySQL

#獲取當前MySQL的版本號
mysql -V;
mysql --version;

SQL語句語法規範

• 常用MySQL的關鍵字需要大寫，庫名、表名、欄位名稱等使用小寫；
• SQL語句支援折行操作，拆分的時候不能把完整單詞拆開；
• 資料庫名稱、表名稱、欄位名稱不能使用MySQL的保留字，如果必須要使用，需要用反引號；

常用SQL語句
SELECT USER(); #得到登入的使用者
SELECT VERSION(); #得到MySQL的版本資訊
SELECT NOW(); #得到當前的日期時間
SELECT DATABASE(); #得到當前開啟的資料庫

SQL的註釋

#註釋內容
--註釋內容

資料庫SQL操作

#建立資料庫
CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name;
CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name DEFAULT CHARACTER SET [=] `charset`; #指定編碼格式
--注意：資料庫名稱最好有意義，名稱不要包含特殊字元或者是MySQL關鍵字。

#修改資料庫的編碼方式
ALTER DATABASE db_name DEFAULT CHARACTER SET [=] `charset`;

#刪除資料庫
DROP DATABASE [IF EXISTS] db_name;

#開啟指定資料庫
USE db_name;    #在運算元據庫裡面的表之前需要先開啟資料庫

#SHOW方法
SHOW DATABASES|SCHEMAS;                            #檢視全部的資料庫
SHOW CREATE DATABASE db_name;                 #檢視資料庫的詳細資訊（編碼格式）
SHOW WARNINGS;                                   #檢視上一步操作產生的警告資訊


#MySQL註釋
`#`註釋內容
`--`註釋內容

資料表SQL操作
資料庫表是一系列二維陣列的集合，用來代表和儲存資料物件之間的關係，是資料庫最重要的組成部分之一。
資料儲存在表中，表名要求唯一，不要包含特殊字元，最好含義明確

• 主鍵：指的是一個列或多列的組合，其值能唯一地標識表中的每一行，一個表中只能有一個主鍵。主鍵主要是用於其他表的外來鍵關聯，以及本記錄的修改與刪除。
• 外來鍵：如果公共欄位在一個表中是主鍵，那麼這個欄位被稱為另一個表的外來鍵，一個表中可以有多個外來鍵。外來鍵保持資料一致性和完整性，主要目的是控制儲存在外來鍵表中資料，使兩張表形成關聯，外來鍵只能引用外表中的列的值或者使用空值。
• 索引：對錶中一列或者多列的值進行排序的一種結構，使用索引可快速訪問表中的特定資訊，一個表中可以有多個索引。索引的主要目的是加快檢索表中資料的方法。

表結構相關操作：

#建立表
CREATE TABLE[IF NOT EXISTS] table_name（ 
   欄位名稱1 欄位型別[完整性約束條件],
   欄位名稱2 欄位型別[完整性約束條件],
   ...
）ENGINE=儲存引擎 CHARSET=編碼方式;
    
#刪除表
DROP TABLE [IF EXISTS] table_name;
    
#檢視錶的建立資訊
SHOW CREATE TABLE table_name;
    
#檢視當前資料庫下所有的表
SHOW [FULL] TABLES [{FROM|IN} db_name];
    
#檢視錶結構
DESC table_name;                      
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
    
#新增欄位
ALTER TABLE table_name ADD 欄位名稱 欄位屬性[完整性約束條件] [FIRST|AFTER 欄位名稱]; 
    
#刪除欄位
ALTER TABLE table_name DROP 欄位名稱;
    
#新增預設值
ALTER TABLE table_name ALTER 欄位名稱 SET DEFAULT 預設值;
    
#刪除預設值
ALTER TABLE table_name ALTER 欄位名稱 DROP DEFAULT;
    
#修改欄位型別、欄位屬性
ALTER TABLE table_name MODIFY 欄位名稱 欄位型別[欄位屬性] [FIRST|AFTER 欄位名稱]
    
#修改欄位名稱、欄位型別、欄位屬性
ALTER TABLE table_name CHANGE 原欄位名稱 新欄位名稱 欄位屬性 [FIRST|AFTER 欄位名稱]
    
#新增主鍵
ALTER TABLE table_name ADD PRIMARY KEY(欄位名稱)
    
#刪除主鍵
ALTER TABLE table_name DROP PRIMARY KEY;
    
#新增唯一
ALTER TABLE table_name ADD UNIQUE KEY|INDEX [index_name](欄位名稱)  --如果不新增index_name，則索引名稱預設為欄位名稱
    
#刪除唯一
ALTER TABLE table_name DROP INDEX index_name;
    
#修改資料表名稱
ALTER TABLE table_name RENAME [TO|AS] new_table_name;
RENAME TABLE table_name TO new_table_name;

關鍵字含義
UNSIGNED：無符號，沒有負數，從0開始
ZEROFILL：零填充，當資料顯示長度不夠的時候可以使用前補0的效果填充至指定長度
NOT NULL：非空約束，也就是插入值的時候這個欄位必須要給值
DEFAULT：預設值，如果插入記錄的時候沒有給欄位賦值，則會使用預設值
PRIAMARY KEY：主鍵，標識記錄的唯一性，值不能重複，一個表只能有一個主鍵
UNIQUE KEY：唯一性索引，一個表中可以有多個欄位是唯一索引，同樣的值不能重複，但是NULL除外
AUTO_INCREASE：自動增長，只能用於數值列，而且配合索引或主鍵使用
FOREIGN KEY：外來鍵約束
COMMENT：新增註釋

#設定主鍵的兩種方式
CREATE TABLE test_primarykey(id INT UNSIGNED PRIMARY KEY, username VARCHAR(20));        #直接在欄位後面設定屬性
CREATE TABLE test_primarykey1(id INT UNSIGNED, username VARCHAR(20), PRIMARY KEY(id));     #利用函式設定欄位

MySQL資料型別

• 數值型

  • 整數型TINYINT SMALLINT MEDIUMINT INT BIGINT BOOL/BOOLEAN
  • 浮點數FLOAT DOUBLE DECIMAL
  • 定點數
• 字串型別CHAR(M) VARCHAR(M) TINYTEXT TEXT MEDIUMTEXT LONGTEXT ENUM(``, ``...) SET(``, ``...)
• 日期時間型別

CHAR和VARCHAR的比較：
CHAR是定長，VARCHAR變長；
CHAR效率高於VARCHAR，CHAR相當於拿空間換時間，VARCHAR拿時間換空間；
CHAR預設儲存資料的時候，後面會用空格填充到指定長度，而在檢索的時候會去掉後面空格，VARCHAR不會進行填充，檢索的時候尾部的空格會留下。
注意：TEXT型別的欄位不能有預設值，檢索的時候不存在大小寫轉換。

MySQL中常用函式
COUNT()語法：
COUNT(column_name)：返回指定列的值的數目
COUNT(*)：返回表中的記錄數目
COUNT(DISTINCT column_name)：返回指定列的不同值的數目

CONCAT()語法：
CONCAT()：用於將多個字串連線成一個字串。
用法：CONCAT(str1, str2, …)，返回結果為連線引數產生的字串，如果任何一個引數為NULL，則返回為NULL
CONCAT_WS()：用一個分隔符將多個字串連線成一個字串
用法：CONCAT_WS(separator, str1, str2, …)，如果分隔符為NULL，則返回為NULL

MySQL儲存引擎
MyISAM儲存引擎

• 預設MyISAM的表會在磁碟中產生三個檔案：.frm .MYD .MYI
• 可以在建立表的時候指定資料檔案和索引檔案儲存位置
• MyISAM單表最大支援的資料量2的64次方條記錄
• 每個表最多可以建立64個索引
• 如果是複合索引，每個複合索引最多包涵16個列，索引值最大長度是1000B
• MyISAM引擎的儲存格式：定長FIXED、動態DYNAMIC、壓縮COMPRESSED

InnoDB儲存引擎

• 設計遵循ACID模型Atomicity原子性、Consistency一致性、Isolation隔離性、Durability永續性，支援事務，具有從服務崩潰中恢復的能力，能夠最大限度保護使用者的資料
• 支援行級鎖，可以提升多使用者併發時的讀寫效能
• 支援外來鍵，保證資料的一致性和完整性
• InnoDB擁有自己獨立的緩衝池，常用的資料和索引都在快取中

記錄SQL操作

新增記錄

#一條記錄用VALUE，多條記錄用VALUES
INSERT [INTO] table_name[(col_name1, col_name2...)] VALUE|VALUES(value1, value2...);

#不列出欄位名稱[需要按照建表時的欄位順序給每一個欄位賦值]
INSERT [INTO] table_name VALUE(value1, value2...);

#一次新增多條記錄
INSERT [INTO] table_name[(col_name1, col_name2...)] VALUES(value1, value2...), (value1, value2...),...;

#INSERT...SET語句
INSERT [INTO] table_name SET 欄位名稱=值,...;

#INSERT...SELECT語句
INSERT [INTO] table_name SELECT 欄位名稱,... FROM table_name [WHERE條件語句];

修改記錄

UPDATE table_name SET 欄位名稱1=值1, 欄位名稱2=值2,... [WHERE條件語句];

刪除記錄

DELETE FROM table_name [WHERE條件語句];

查詢記錄

# DESC：指定列按降序排列  ASC：指定列按升序排列
# GROUP BY：分組，把值相同放到一個組裡，最終查詢出的結果只會顯示組中一條記錄，分組配合GROUP_CONCAT()檢視組中某個欄位的詳細資訊
# ORDER BY：設定記錄按照某欄位的值進行排序，預設ASC升序
# LIMIT：限制結果集的顯示條數，可以用來實現分頁
        LIMIT 數字：顯示結果集的前幾條記錄
        LIMIT offset, row_count：從offset開始[offset從0開始]，顯示幾條記錄
SELECT 欄位1, 欄位2,... FROM table_name [WHERE條件語句] [GROUP BY(col_name) Having 二次篩選] [ORDER BY(col_name) DESC|ASC] [LIMIT 限制結果集的顯示條數];

#查詢所有記錄的所有欄位
SELECT * FROM table_name;

#查詢指定欄位的資訊
SELECT 欄位名稱1, 欄位名稱2,... FROM table_name [WHERE條件語句];

#查詢某資料庫下某表的記錄[這樣可以不用開啟該資料庫就能操作該表]
SELECT 欄位名稱1, 欄位名稱2,... FROM db_name.table_name [WHERE條件語句];

#給欄位取別名[別名名稱可以使用中文]
SELECT 欄位名稱 [AS] 別名名稱,... FROM table_name [WHERE條件語句];

#給表取別名[單張表沒有太大作用，多張表才體現]
SELECT 欄位名稱1, 欄位名稱2,... FROM table_name [AS] 別名 [WHERE條件語句];

#表名.欄位名稱[單張表沒有太大作用，多張表才體現]
SELECT table_name.欄位名稱,... FROM table_name [WHERE條件語句]

#WHERE條件[篩選符合條件的記錄]
比較運算子：> < >= <= != <> <=>
邏輯運算子：AND(邏輯與) OR(邏輯或)
IS [NOT] NULL：檢測值是否為NULL或者NOT NULL
指定範圍：[NOT] BETWEEN...AND
指定集合：[NOT] IN(值1, 值2,..)
匹配字元：[NOT] LIKE
        %：任意長度的字串
        _：任意一個字元
        
#模糊查詢
在執行資料庫查詢時，分為完整查詢和模糊查詢。
格式：SELECT 欄位1, 欄位2,.. FROM table_name WHERE 某欄位 LIKE 條件;

模糊查詢包涵兩種萬用字元：
%：表示0個或多個字元，可以匹配任意型別或任意長度的字元。
LIKE `%王`：匹配的是欄位結尾為`王`的所有記錄；
LIKE `王%`：匹配的是欄位開頭為`王`的所有記錄；
LIKE `%王%`：匹配的是欄位包含`王`的所有記錄；

_：表示任何單個字元，匹配單個任意字元，它常用來限制表示式的字元長度。
LIKE `_王`：匹配的是欄位長度為2，並且結尾為`王`的所有記錄；
LIKE `王_`：匹配的是欄位長度為2，並且開頭為`王`的所有記錄；
LIKE `_王_`：匹配的是欄位長度為3，並且中間為`王`的所有記錄；

#常用聚合函式
COUNT()：統計記錄總數
SUM()：求和
MAX()：求最大值
MIN()：求最小值
AVG()：求平均值

#產生隨機數
SELECT RAND();

#實現隨機記錄[出現的記錄排序是隨機的]
SELECT * FROM table_name ORDER BY RAND();


#測試完整SELECT語句的形式
MariaDB [test004]> SELECT GROUP_CONCAT(name) AS `姓名`, COUNT(*) AS `人數`, SUM(age) AS `總和`, MAX(age) AS `最大`, MIN(age) AS `最小`, AVG(age) AS `平均` FROM user WHERE id >=1 GROUP BY address ORDER BY `總和`;

多表查詢

1. 笛卡爾積形式
笛卡爾積是多表連線組成一個新表的情況，所有的連線方式都會先生成臨時笛卡爾積表，笛卡爾積是關係代數裡的一個概念，表示兩個表中的每一行資料任意組合，新表的記錄數為多張表的記錄條數的乘積，實際應用中一般不滿足需求，只有在兩個表連線時加上限制條件，才有實際的意義。

test1                   test2
+------+--------+        +------+--------+
| id   | name   |        | id   | name   |
+------+--------+        +------+--------+
|    1 | 小紅   |           |    1 | 張三   |
|    2 | 小明   |           |    2 | 李四   |
+------+--------+        +------+--------+

MariaDB [test005]> select a.*, b.* from test1 a, test2 b;
+------+--------+------+--------+
| id   | name   | id   | name   |
+------+--------+------+--------+
|    1 | 張三   |    1 | 小紅   |
|    2 | 李四   |    1 | 小紅   |
|    1 | 張三   |    2 | 小明   |
|    2 | 李四   |    2 | 小明   |
+------+--------+------+--------+


2. 內連線形式（常用）
利用內連線可獲取兩表的公共部分的記錄
SELECT 欄位名稱,... FROM  table_name1 INNER JOIN table_name2 ON 連線條件

3. 外連線形式
左外連線：以左表為主，先顯示左表中的全部記錄，再去右表中查詢滿足複合條件的記錄，不符合的以NULL代替
SELECT 欄位名稱,... FROM table_name1 LEFT [OUTER] JOIN table_name2 ON 連線條件

右外連線：以右表為主，先顯示右表中的全部記錄，再去左表中查詢滿足複合條件的記錄，不符合的以NULL代替
SELECT 欄位名稱,... FROM table_name1 RIGHT [OUTER] JOIN table_name2 ON 連線條件

圖形化工具管理資料庫

B/S結構
phpMyAdmin

C/S結構
Sequel Pro
Navicat for MySQL
MySQL workbench