MySQL 基礎回顧_ZenDei技術網路在線

mysql 回顧資料庫的設計必須滿足三範式 1NF: 強調列的原子性，列不可拆分 eg: 一張表（聯繫人）有（姓名，性別，電話）三列，但是現實中電話又可分為家庭電話和公司電話，這種表結構設計就不符合第一範式了，正確的應該是繼續拆分(姓名，性別，家庭電話，公司電話) 2NF: 首先滿足1NF，另 ...

mysql 回顧

資料庫的設計必須滿足三範式

1NF: 強調列的原子性，列不可拆分
eg: 一張表（聯繫人）有（姓名，性別，電話）三列，但是現實中電話又可分為家庭電話和公司電話，這種表結構設計就不符合第一範式了，

正確的應該是繼續拆分(姓名，性別，家庭電話，公司電話)
2NF: 首先滿足1NF，另外包含兩點：
- 表必須有一個主鍵
- 非主鍵列必須完全依賴於主鍵，而不能只依賴與主鍵的一部分
  eg: 有這樣一張表
  OrderDetail:（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
  我們知道在一個訂單中可以訂購多種產品，所以單單一個 OrderID 是不足以成為主鍵的，主鍵應該是（OrderID，ProductID）。
  顯而易見 Discount（折扣），Quantity（數量）完全依賴（取決）於主鍵（OderID，ProductID），而 UnitPrice，ProductName
  只依賴於 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的設計容易產生冗餘數據
正確的做法應該是進行分表:

【OrderDetail】表拆分為【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）來消除原訂單表中UnitPrice，ProductName多次重覆的情況。
3NF 首先要滿足2NF，另外非主鍵列必須直接依賴於主鍵，不能存在傳遞關係，即：非主鍵列A 依賴於非主鍵列B, 非主鍵列B依賴於主鍵的情況
eg: 訂單表
Order（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主鍵是（OrderID）
其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主鍵列都完全依賴於主鍵
（OrderID），所以符合 2NF。不過問題是 CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID（非主鍵列），而不是直接依賴於主鍵，它是通過傳遞才依賴於主鍵，所以不符合 3NF。
正確的方式：

Order 拆分為【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）從而達到 3NF

範式小結

第二範式和第三範式容易混淆，關鍵在於， 2NF: 非主鍵列是否完全依賴於主鍵，還是依賴於主鍵的一部分； 3NF: 非主鍵列是直接依賴於主鍵，還是直接依賴於非主鍵列。

資料庫的 CURD

數據源:

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

添加數據：

-- 向students表中插入數據
# 主鍵id 是自動增長的，使用全列插入時需要占位，通常用0，default、null 來占位
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'彭於晏',29,185.00,1,1,0),
(0,'劉德華',59,175.00,1,2,1),
(0,'黃蓉',38,160.00,2,1,0),
(0,'鳳姐',28,150.00,4,2,1),
(0,'王祖賢',18,172.00,2,1,1),
(0,'周傑倫',36,NULL,1,1,0),
(0,'靜香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周傑',34,176.00,2,5,0);

-- 向classes表中插入數據
insert into classes values (0, "一班"), (0, "二班");

基本命令

    create databse db_name charset=utf8;  # 創建資料庫
    show databses; # 顯示所有資料庫
    show create database db_name; # 查看資料庫的基本信息
    use db_name;  # 切換資料庫
    select database();  # 查看當前所用資料庫
    select now();       # 查看當前時間

MySQL查詢語句

as 可給欄位，或者給表起別名

select s.id, s.name, s.gender from students as s;
消除重覆行

select distinct gender from students;
條件where 子句
優先順序（由高到低）: 小括弧，not, 比較運算符，邏輯運算符，and比or先運算
註意: 不推薦使用 a) 負向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃描 b) %開頭的模糊查詢，會導致全表掃描
- 比較運算符
- 邏輯運算符
- 模糊查詢
  - like
    eg: select * from students where name like; '黃%' # 查詢姓黃的學生
  - % 表示任意多個任意字元
    eg: select * from students where name like; '黃_' # 查詢姓黃且名字是一個字的學生
  - _ 表示一個任意字元
- 範圍查詢
  - in 表示在一個非連續的範圍內
    eg: select * from students where id in (1, 3, 8);
  - between...and... 表示在一個連續的範圍內
    eg: select * from students where id between 3 and 8;
- 空判斷
  - null （與''不同）
  - is not null
排序 order by 預設升序 asc
- asc 升序
- desc 降序
  eg: 查詢未刪除的男生信息，按學號降序
  
  select * from students where gender=1 and is_delete=0 order by id desc;
聚合函數
- count
- max
- min
- sum
- avg
分組 group by 一般結合聚合函數使用
將查詢結果按照1個或多個欄位進行分組，欄位值相同的為一組
- group by + group_concat(欄位名) 將分組結果根據欄位名輸出對應欄位值的集合
  select gender, group_concat(name) from students group by gender;
- group by + 聚合函數
  eg: 按性別分別統計年齡的平均值
  select gender, avg(age) from students group by gender;
- group by + having
  用來分組查詢後指定一些條件來輸出查詢結果, 作用和where一樣，但是只能用於group by
  select gender,count() from students group by gender having count()>2;
- gounp by + with rollup
  with rollup作用: 最後新增一行，來記錄當前列里所有記錄的總和
連接查詢多表查詢 join 表連接原理: 笛卡爾積
- 內連接
- 右連接在內連接的基礎上添加右表數據，右表中沒有的數據欄位使用null填充
- 左連接在內連接的基礎上添加左表數據，右表中沒有的數據欄位使用null填充

    # 笛卡爾積 在其他資料庫中內連接和笛卡爾積石油區別的，在mysql中 join 和 inner join 是一樣的
    select * from table1 [inner] join table2;

條件查詢(on子句過濾笛卡爾積)

語法: select * from table1 inner|left|right join table2 on table1.field = table2.field

自關聯
應用場景區域信息，分類信息（如淘寶分類欄，大類，小類，具體分類）
子查詢
- 標量子查詢一行一列
  子查詢的結果是一個標量
  eg: 查詢大於平年齡的學生
```
select * from students where age > (select avg(age) from students);
```
- 列子查詢返回的結果是一列多行
- 行子查詢返回的結果是一行多列
  行元素: 將多個欄位合成一個行元素,在行級子查詢中會使用到行元素
```
select * from students where (height, age) = (select max(height),max(age) from students);
```
主查詢和子查詢的關係
· 子查詢是嵌入主查詢的
· 子查詢要麼充當條件，要麼作為數據源
· 子查詢也是一條完整的select語句
事務
事務是多個SQL語句操作的序列，這些操作要麼都執行，要麼都不執行，如有有一個失敗，便回滾到原始狀態
應用場景: 充話費，銀行轉賬，地鐵卡充值等

原子性 Atomicity
一致性 Consistency
隔離性 Isolation
持久性 Durability

1️⃣只有針對錶的insert, update, delete 才能使用事務進行管理
2️⃣終端修改數據的命令會自動觸發事務, insert, update, delete
3️⃣如果需要將自動開啟的事務改為手動提交關閉自動 set autocommit=0;