Python基礎之MySQL資料庫

一、約束概述

1、為什麼要約束

為了防止資料庫中存在不符合語意規定的資料和防止錯誤資訊的輸入、輸出造成無效的操作而提出的

為了保證資料的完整性，SQL規範以約束的方式對錶資料進行額外的條件限制，從以下四個方面考慮

實體完整性：例如一個表中不能存在兩條相同的，無法區分的記錄
域完整性：例如年齡範圍，性別範圍
參照完整性：例如學生所在的班級、學校，在學校中要能找到班級、在班級能找到學生
使用者自定義完整性：例如使用者名稱唯一，密碼不能不空

2、什麼是約束

約束是指，對欄位名下資料的強制約束

約束的方式：

建立表時約束：create table

create table 表名（欄位名 型別（數位） 約束條件）;

3、約束的分類

根據約束資料列的限制，約束可分為
- 單列約束：每個約束只能約束一列
- 多列約束：每個約束可約束多列資料
根據約束的作用範圍，約束可分為
- 列級約束：只能作用在一個列上，跟在列的定義後面
- 表級約束：可以作用在多個列上，單獨定義
根據約束的作用，約束可分為
- 非空約束：規定某個欄位不能為空
- 唯一約束：規定某個欄位的資料不能重複
- 主鍵約束：非空且唯一，一個表只能擁有一個主鍵欄位
- 外來鍵約束：主要體現在父子表中關係資料的約束操作
- 檢查約束：控制特定列中的值的完整性約束
- 預設值約束：在不特別新增資料的情況下，該欄位名下資料使用預設資料

4、檢視當前表已有的約束

方式一：

show create table 表名；

方式二：

describe 表名；/desc 表名；

二、約束語法及用法

1、無符號

關鍵詞：unsigned
作用：在SQL中，整型和浮點型別都自帶正負號，而很多資料並用不到負數，例如人的年齡，手機號碼，這時就可以通過約束條件解除正負號

mysql> create table t2(id tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

2、零填充

關鍵詞：zerofill
作用：在建立表時，整型和浮點型後一般不用輸入數位，系統會預設生成，在新增資料後，系統指揮預設顯示該數值的大小及位數，通過零填充加數值，可固定資料值現實位數，不足部分用零填充

mysql> create table t4(id tinyint(2) zerofill);
Query OK, 0 rows affected (0.01 sec)

3、非空

關鍵詞：not null
作用：所有欄位型別，在不做非空約束的情況下，插入的資料值都可以為空（null），在建立表階段可以約束該欄位下資料為非空，約束後在往表內新增資料時，該欄位下不能為空，空字元除外

mysql> create table t5(id int(3) zerofill, name varchar(16) not null);
Query OK, 0 rows affected (0.02 sec)

4、預設值

關鍵詞：default
作用：在建立表階段，在型別後約束預設值，可在預設值後方填入預設值，新增該欄位下的資料時，不進行特殊定義的話會採用預設值

mysql> create table t(id int primary key auto_increment,
    -> name varchar(32) not null,
    -> gender varchar(8) default 'mela');
Query OK, 0 rows affected (0.02 sec)

5、唯一值

5、1.單列唯一（列級）

關鍵詞：unique
作用：唯一值是指，在建立表階段，可通過約束唯一值的條件，使在往該表內約束的欄位下新增的資料不能重複出現，例如往序號欄位下新增‘序號1’後，該序號將不能在重複出現在其他列

mysql> create table t6(id int unique, name varchar(16) not null);
Query OK, 0 rows affected (0.04 sec)

5、2.聯合唯一（表級）

關鍵詞：unique(欄位名1，欄位名2，...)
作用：聯合唯一不在單個欄位名下定義，而是單獨定義，在關鍵詞後方括號內新增需要進行聯合唯一的欄位名，那麼被新增的多個欄位名的資料值將不能共同出現，單個欄位名下的資料值可以重複

mysql> create table t7(id int, name varchar(16), unique(id, name));
Query OK, 0 rows affected (0.02 sec)

6、主鍵

關鍵詞：primary key
作用：單從約束層面上而言的話，主鍵就相當於‘非空且唯一’，InnoDB儲存引擎規定了所有的表都必須擁有且只能有一個主鍵（在建立表的時候，可以不用手動約束，系統會將其他非空且唯一的欄位升級為主鍵，如果都沒有約束的話，後臺會自動生成一個隱藏的主鍵），主鍵可以加快資料的搜尋速度（如果沒有主鍵的話，查詢資料就需要一行一行的進行），在我們建立表的時候應該主動約束一個欄位為主鍵，用來標識資料的唯一性，通常使用‘id（編號）’欄位

mysql> create table t8(id int primary key, name varchar(16));
Query OK, 0 rows affected (0.02 sec)

7、自增

關鍵詞：auto_increment
作用：通常在儲存多個資料的時候，我們都會給資料進行編號，方便查詢，自增的意思就是幫助我們生成資料的編號，不需要在手動填寫，這樣的好處就是省時、省力，還不用專門的去記憶當前的需要是多少。
特性：自增不會因資料刪除而退回，只會往前，如果自己設定了更大的數，那麼自增就會基於這個數往更大的數去自增,，自增的欄位只能是主鍵
格式化表格：
- truncate 表名; (可以用來個格式化表格並重置自增)

mysql> insert into t9(name, age) values('jason', 18),('kang',19);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

8、外來鍵

8、1.外來鍵的定義

外來鍵是某個表中某一列，它包含在另一個表的主鍵中

外來鍵也是索引的一種，是通過一張表指向另一張表中的主鍵，來對兩張表進行關聯

一張表可以有一個外來鍵，也可以存在多個外來鍵，與多張表進行關聯

8、2.外來鍵的作用

主要用於表與表之間的資料關聯，減少資料冗餘，保證資料的一致性，完整性，主要體現在以下兩個方面：

阻止執行：
- 從表插入新行，其外來鍵值不是主表的主鍵值便阻止插入
- 從表修改外來鍵值，新表不是主表的主鍵值便阻止修改
- 主表刪除行，其主鍵值在從表裡存在則阻止刪除（想要刪除必須刪除從表的相關行）
- 主表修改主鍵值，舊值在從表裡存在則阻止修改（要想修改，必須先刪除從表的相關行）
級聯執行：
- 主表刪除行，連帶刪除從表關聯行
- 主表修改值，連帶修改從表關聯行的值

8、3.外來鍵的建立

主表：

create table 主表名(
        id 型別 主鍵 ，
        欄位名 型別 約束條件)

從表：

create table 從表名(
    id 型別 主鍵 ,
    欄位名 型別(數位) 約束條件，
    繫結外來鍵的欄位名  int，
    foreign key(主表名) references 主表名(主表的主鍵欄位名)
)

在建立主表和從表時，主表必須已經存在於資料庫中
錄入資料的時候一定要先錄入主表的資料
修改資料的時候外來鍵欄位無法刪除和修改

級聯建立的方式：

需要在建立表階段，在從表外來鍵關鍵詞後加入：

級聯更新：on update cascade
級聯刪除：on delete cascade

create table 從表名(
   id 型別 主鍵 ,
   欄位名 型別(數位) 約束條件，
   繫結外來鍵的欄位名  int，
   foreign key(主表名) references 主表名(主表的主鍵欄位名)
	on update cascade
	on delete cascade)

8、4.外來鍵的多種關係

1.一對多：

以員工表為例，通常，一個員工只可以對應一個部門，而一個部門可以對應多個員工，這就是一對多的關係

對於一對多的關係，外來鍵欄位應該建立在‘多’的一方

create table dep(id int primary key auto_increment,
	dep_name varchar(32)
	);

create table emp(id int primary key auto_increment,
	emp_name varchar(32) not null,
 	dep_id int, foreign key(dep_id) references dep(id)
 	on update cascade
  	on delete cascade
  	);

2.多對多：

以書籍表與作者表為例

先站在書籍表的角度
問:一本書能否對應多個作者
答:可以
再站在作者表的角度
問:一個作者能否對應多本書
答:可以
結論:兩個都可以關係就是'多對多'
針對'多對多'不能在表中直接建立需要新建第三張關係表

create table book(
	id int primary key auto_increment,
 	title varchar(32),
  	price float(5,2)
);

create table author(
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);

create table book2author(
	id int primary key auto_increment,
   author_id int,
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);

3.一對一：

以使用者表與使用者詳情表為例

先站在使用者表的角度
問:一個使用者能否對應多個使用者詳情
答:不可以
再站在使用者詳情表的角度
問:一個使用者詳情能否對應多個使用者
答:不可以
結論:兩個都可以關係就是'一對一'或者沒有關係
針對'一對一'外來鍵欄位建在任何一方都可以但是推薦建在查詢頻率較高的表中

create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

MySQL資料庫：6、約束的概述及語法