Mysql基本語句
- 2020 年 3 月 10 日
- 筆記
1. 資料庫(datebase)
保存有組織的數據的容器，簡單理解為存放數據的倉庫
2. 資料庫管理系統(database manage system)
我們平常不是直接從資料庫裡面獲取數據的，而是通過使用資料庫管理系統來訪問資料庫從而獲取數據的，這些軟體稱為DBMS（由於習慣我們平時所說的資料庫指的就是資料庫管理系統，容易被誤導）
3. 表(table)
類似於文件夾，把數據分類放在同一個文件內，即放在同一個表中
4. 列和行(column and row)
表的結構類似於excel的表格，表列對應excel列，錶行對應excel行
5. 數據類型(data type)
表中的每列(欄位)都有自己的數據類型
# 常見數據類型 int float double char varchar datetime timestamp TEXT
6. 三大範式
第一範式：每個欄位都是最小的單元，不可再分 第二範式：滿足第一範式，表中的欄位必須完全依賴於全部主鍵而非部分主鍵 第三範式：滿足第二範式，非主鍵外的所有欄位必須互不依賴
7. 約束
NOT NULL: 非空 UNIQUE: 唯一性 PRIMARY KEY: 主鍵 FOREIGN KEY: 外鍵 CHECK: 控制欄位的值範圍
8. 超鍵、候選鍵、主鍵、外鍵
主鍵：唯一標識數據的單個或多個欄位
外鍵：存在他表中的主鍵
候選鍵：可以作為主鍵但沒有設為主鍵的單個或多個欄位
超鍵：候選鍵的集合
9. 表操作
# 建表 CREATE TABLE [IF NOT EXISTS] `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE = InnoDB DEFAULT CHARSET = utf8;
# 刪表 DROP TABLE [IF EXISTS] <表名> # 清空 TRUNCATE TABLE <表名>
10. SELECT語句
基本查詢
SELECT (欄位1, 欄位2, ...) FROM <表名>
條件查詢
SELECT (欄位1, 欄位2, ...) FROM <表名> WHERE <條件表達式>
排序查詢
SELECT (欄位1, 欄位2, ...) FROM <表名> ORDER BY 欄位1 DESC, 欄位2 ASC, ... # 特定記錄的排序 SELECT * FROM <表名> ORDER BY id = 9 DESC,欄位1 DESC
子查詢
# FROM 後子查詢一定要取別名 SELECT * FROM (SELECT * FROM <表名>) AS <另取的表名> WHERE <條件表達式>
分頁查詢
SELECT (欄位1, 欄位2, ...) FROM <表名> LIMIT <M> OFFSET <N> # 分頁優化，查詢第十萬條後的100條數據 SELECT * FROM <表名> LIMIT 100 OFFSET 100000; SELECT * FROM <表名> WHERE id >= (SELECT id FROM <表名> LIMIT 100000,1) limit 100;
分組查詢
SELECT (欄位1，聚合函數) FROM <表名> WHERE <條件表達式> GROUP BY 欄位1 HAVING <條件表達式> ORDER BY <條件表達式> # WHERE，用於分組前，WHERE過濾的是行 # SELECT，欄位只能加 GROUP BY 後面的欄位和聚合函數 # HAVING，用於分組後，過濾組，且條件欄位必須在前面查詢存在 # 畫重點，分組需要列出非GROUP 之後的欄位 SELECT * FROM <表名> WHERE id IN （ SELECT max(id) FROM <表名> GROUP BY id,time ）
並 / 差 / 交集查詢
SELECT (欄位1, 欄位2, ...) FROM <表名1> UNION [ALL] / EXCEPT / INTERSECT SELECT (欄位1, 欄位2, ...) FROM <表名2> # 二表欄位需一樣
聚合查詢
COUNT(欄位)，SUM(),AVG(),MAX(),MIN() # 若查詢無結果，COUNT()返回0，而SUM()、AVG()、MAX()、MIN()返回NULL
連接查詢
SELECT (表1.欄位, 表2.欄位2, ...) FROM <表1> XXX JOIN <表2> ON <表1>.column = <表2>.column XXX: INNER / RIGHT OUTER / LEFT OUTER / FULL OUTER
- 內連接，只返回同時存在於兩張表的行數據
- 外連接
- 左連接，返回右表都存在的行，左邊不存在填充NULL
- 右連接，返回左表都存在的行，右邊不存在填充NULL
- 全連接，把兩張表的所有記錄全部選擇出來，自動把對方不存在的列填充為NULL
11. INSERT語句
# 插入或替換（根據主鍵來執行） # 若存在該主鍵，刪除原記錄，插入一條新的、否則直接插入記錄 REPLACE INTO <表名> （欄位） VALUES （值）
# 插入或更新（根據主鍵來執行） # 若存在該主鍵，更新記錄、否則直接插入記錄 # 返回0，1，2 INSERT INTO <表名> (欄位1) VALUES (值) ON DUPLICATE KEY UPDATE `欄位2` = "更新值"
# 插入或忽略 INSERT IGNORE INTO <表名> （欄位） VALUES （值）
12. UPDATE
# 返回更新條數 UPDATE <表名> SET 欄位1=值1, 欄位2=值2, ... WHERE <條件表達式>
13. DELETE
# 返回條數 DELETE FROM <表名> WHERE <條件表達式>
# 跨表更新（兩個表都可更新） UPDATE <表名1> XXX JOIN <表名2> on <表名1>.a = <表名2>.b SET <表名1>.x = 0,<表名2>.x=0 WHERE <條件表達式>
14. 視圖
# 視圖是一種虛表，建立在原來的表上，其本質是查詢語句，不會增加查詢效率 # 簡化查詢 # 許可權限制，安全性 CREATE VIEW <視圖名> SELECT (欄位1, 欄位2, ...) FROM <表名> WHERE <條件表達式>
15. 名詞
- DML（data manipulation language）數據操作語言：select、update
- DDL（data definition language）數據定義語言：create、alter、drop
- DCL（data control language）數據控制語言：grant、revoke
16. 其他
- 判斷null：ISNULL
- 替換：IFNULL(
欄位，替換的值)，若為null則替換成給定值
