MySQL 基礎回顧

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查詢語句

1. as 可給字段，或者給表起别名

   select s.id, s.name, s.gender from students as s;

2. 消除重複行

   select distinct gender from students;

3. 條件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
4. 排序 order by 預設升序 asc
   • asc 升序

   • desc 降序

     eg: 查詢未删除的男生資訊，按學号降序

     select * from students where gender=1 and is_delete=0 order by id desc;

5. 聚合函數
   • count
   • max
   • min
   • sum
   • avg

6. 分組 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作用: 最後新增一行，來記錄目前列裡所有記錄的總和

7. 連接配接查詢多表查詢 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

1. 自關聯

   應用場景 區域資訊，分類資訊（如淘寶分類欄，大類，小類，具體分類）

2. 子查詢

   • 标量子查詢 一行一列

     子查詢的結果是一個标量

     eg: 查詢大于平年齡的學生

   • 列子查詢 傳回的結果是一列多行

   • 行子查詢 傳回的結果是一行多列

     行元素: 将多個字段合成一個行元素,在行級子查詢中會使用到行元素

   主查詢和子查詢的關系

   · 子查詢是嵌入主查詢的

   · 子查詢要麼充當條件，要麼作為資料源

   · 子查詢也是一條完整的select語句

3. 事務

   事務是多個SQL語句操作的序列，這些操作要麼都執行，要麼都不執行，如有有一個失敗，便復原到原始狀态

   應用場景: 充話費，銀行轉賬，地鐵卡充值等

• 原子性 Atomicity
• 一緻性 Consistency
• 隔離性 Isolation

• 持久性 Durability

  1️⃣隻有針對表的insert, update, delete 才能使用事務進行管理

  2️⃣終端修改資料的指令會自動觸發事務, insert, update, delete

  3️⃣如果需要将自動開啟的事務改為手動送出 關閉自動 set autocommit=0;

其他

1️⃣ ubuntu mysql 終端無法輸入中文解決方法

export LANG=en_US.UTF-8 # 修改本地使用者的字元集即可

2️⃣ 資料庫預設字元集為utf8 隻能存儲3個位元組的資料，标準的emoji表情是4個位元組，是以要支援emoi表情的話就要修改字元集

utf8 --> utf8mb4 # 前提是mysql版本 > 5.5.3

mb4: most byte 4,專門相容四個位元組的，utf8mb4是向下相容utf8的，即使修改了字元集也不會影響線上資料。

REFER:

58到家資料庫30條軍規解讀

轉載于:https://www.cnblogs.com/panlq/p/10612430.html