面試被暴虐，含淚整理了 MySQL增删查改2.0

寫在前面

這篇部落格是基于基本的 MySQL增删查改1.0 的進階版本，這幾天面試被暴虐，畢竟好久沒用了，于是決定從新複習一遍！！！

目錄

• • 寫在前面
  • 一、查詢
  • • like
    • null
    • limit
    • offset（偏移量）
  • 二、限制
  • • not null
    • unique
    • default
    • primary key（主鍵）
    • • 自增主鍵
    • foreign key
    • • 另一中方式實作自增主鍵
  • 三、表的設計
  • 四、插入
  • 五、查詢
  • • 聚合查詢
    • group by
    • 聯合查詢
    • • 内連接配接
      • 外連接配接
      • 自連接配接
    • 子查詢
    • • 單行子查詢（=）
      • 多行子查詢（in / not in）
    • 合并查詢
    • • union（去重）
      • union all （不去重）

一、查詢

like

% 可以比對多個字元

_ 比對一個字元

null

如果想要查詢某個字段為 null，需要使用 <=> 或者 is，而不是直接使用 null。

limit

limit 查詢前幾條資料

搭配其他 select 任意功能使用

offset（偏移量）

搭配 limit 使用

也可以省略 offset，相當于把 offset 的位置放在第一個位置

• 牛客練習題

二、限制

為什麼要有限制類型？

符合一定條件的資料才能插入到資料庫中，提高了靠譜性！！！

類型	作用
not null	指定某列不能為空
unique	保證每一行必須有唯一值
default	規定給沒有指派時的預設值
primary key（主鍵）	not null 和 unique 的結合，保證兩列或者多列有唯一值
foreign key（外鍵）	保證一個表中的資料比對另一個表中的值的參照完整性
check（mysql 5 不支援）	保證列中符合指定條件

not null

• 建表，将 blogId 設定為不為 not null。

  

• 插入資料為 null 試試

• 不為 null 試試

unique

• 建表，将 blogId 設定為 unique

• 插入 blogId=1 資料

• 再次插入 blogId=1 資料

default

• 建表，将 blogId 設定為 default

• 插入資料，blogId 不為 null

• 插入資料， blogId 為 null

和預想的不一樣，實際上需要如下操作才會觸發預設值

• 隻插入 comment

primary key（主鍵）

主鍵：某一列記錄的唯一身份辨別

• 建表，将 userId 設定為 primary key

• 插入資料

• 插入重複資料

前面說過， primary key 相當于 unique + nut null

自增主鍵

主鍵不能重複，如果每次人工設定的話，那麼成本是非常大的，是以有了自增主鍵

• 建表，将 userId 設定為 primary key auto_increment

• 插入四條 userId 為 null 的資料，檢視變化

• 插入 userId 為 4 的資料

• 插入 userId 為 5 的資料

• 插入 userId 為 10 的資料，在插入一條 userId 為 null 的資料，檢視變化

• 如何把自增主鍵還原為 1？

1. 删表重建表

1. truncate 表名

差別：删表重建表多了一些指令，而 truncate 直接一條指令完成了删表重建表。

foreign key

• 建立兩張表，學生表（student）和 班級表（class），每個學生都有自己的班級； 如果學生表中某個學生的班級字段在班級表中沒有，那麼說明是錯誤的。是以可以用外鍵限制來解決這種問題。

• 在 class 表中添加資料

• 在 student 表中添加資料

• 目前來看都沒錯誤，當插入 classId 為 10，也就是 class 表中不存在 classId = 10 時，會發生什麼？

• 當想要删除 class 表中的老年大學2班 ，但是老年大學2班 中有陳大爺，那到底能不能删除呢？

• 是以想要删除班級，先要删除班級内的人，才能删除班級，如下

另一中方式實作自增主鍵

在前面說過 primary key 等價于 not null + unique，在 primary key 後加入 auto_increment 實作自增主鍵，是以也可以在 not null + unique + auto_increment 實作自增主鍵。

三、表的設計

關于表的設計，分為以下幾個步驟

1. 首先要找實體，也就是 Java 中的找對象；

   例如設計一個簡單的教務管理系統，教務管理系統中肯定需要 學生，課程，班級 這三個實體。實體找好了，那麼怎麼設計表呢？

2. 明确實體的屬性

   學生表 ： 學生id，學生姓名，性别

   課程表 ：課程id，課程名字

   班級表：班級id，班級名字

3. 找到表與表之間的關系

   學生表與課程表：一個學生能選多門課，一門課也能被多個學生選擇（一對多）

   學生表與班級表：一個學生隻能有一個班級，一個班級可以有多個學生（多對多）

   課程表與班級表：…（沒有關系）

4. 根據表與表之間的關系設定外鍵

學生表與班級表是一對多的關系，是以需要在班級表或者學生表中設定一個外鍵保證他們之間的關聯關系

班級表如下：

班級id	班名
1	老年大學1班
2	老年大學2班
3	老年大學3班

學生表如下：

學生id	姓名	班級id
1	王大爺	1
2	李大爺	1
3	陳大爺	2
4	趙大爺	3

根據學生表中的 班級id 字段就能确定學生所屬班級

學生表和課程表是多對多的關系，需要添加一張中間表來記錄他們的關系

課程表如下：

課程id	課程名
1	如何俘獲老伴芳心
2	王阿姨教你交際舞
3	我的前 60 年
4	我的餘生

學生表如下：

學生id	姓名	班級id
1	王大爺	1
2	李大爺	1
3	陳大爺	2
4	趙大爺	3

中間表如下：

學生 id	課程 id
1	1
1	2
2	3
3	3
4	4

根據中間表就能找出某位學生選了哪些課程

還有一對一的關系，就像一個學生隻能有一張床，一張床也隻能有一個學生，是以可以在學生表中添加 床号 字段

學生id	姓名	班級id	床号
1	王大爺	1	10101
2	李大爺	1	10102
3	陳大爺	2	10103
4	趙大爺	3	10104

四、插入

• 建表，插入資料；

• 建另外一張表，查詢插入

• 注意：需要保證列名、列名的類型相同

五、查詢

聚合查詢

聚合函數

函數	說明
count	傳回查詢到的資料的數量
sum	傳回查詢到的資料的和，不是數字沒有意義
avg	傳回查詢到的資料的平均值，不是數字沒有意義
max	傳回查詢到的資料的最大值，不是數字沒有意義
min	傳回查詢到的資料的最小值，不是數字沒有意義

• 建表，插入資料

• 使用 count

• 使用 sum

• 使用 avg

• 使用 max

• 使用 min

group by

• 建表插入資料

• 根據角色查詢平均工資

• 查詢最大值最小值等

• 剔除 小謝 的薪資 這個相當于先篩選再計算

• 先計算再篩選（使用 having）

聯合查詢

• 建多張表，插入多條資料

• 将 student 表和 score 表聯合起來（笛卡爾積）

内容比較多，檢視兩張表的行數

發現 聯合表的行數 = student 表的行數 * score 表的行數 聯合表的列數 = student 表的列數 +score

表的列數 如果資料太多，那麼這種查詢的速度是非常慢的！

• 查詢 黑旋風李逵 的所有課程成績

• 查詢所有同學的總成績、平均成績

聯合查詢步驟（以查詢黑旋風李逵的成績為例）：

1. 找出所需資訊都在哪些表中；（查詢同學的成績需要 student 表、score 表和 course 表）
2. 針對這些表進行笛卡爾積；（将 student 表、 score 表和 course 表進行笛卡爾積）
3. 篩選條件；（student.id = score.student_id 和 course.id = score.course_id)
4. 加上其他條件，得到需求結果。（name = ‘黑旋風李逵’）

内連接配接

上述中的 student 表和 score 表使用 where 或者 join on 都是内連接配接

外連接配接

• 重建立表，并插入資料

>

• 使用内連接配接觀察

• 使用左外連接配接和右外連接配接

• 了解外連接配接

自連接配接

一般來說 SQL 語句隻能比較列和列之間的關系進行篩選，比如 select * from score where chinese > math；

如果想通過比較行和行之間的關系來篩選，就需要 自連接配接。

也就是把該表自己對自己進行笛卡爾積。

子查詢

單行子查詢（=）

• 建表，插入資料

• 查詢 黑旋風李逵 的同班同學有哪些

多行子查詢（in / not in）

• 建表，插入資料

• 查詢國文成績或英文成績

in

not in

合并查詢

union（去重）

• 建表，插入資料

• 使用 union 查詢 id < 3 或者 name = 國文 的結果

• 使用 or 能達到同樣的效果

• 去重效果表現

union all （不去重）

• 建表，插入資料

• 使用 union 查詢 id < 3 或者 name = 國文 的結果

• 不去重表現