Python基礎之MySQL資料庫

• Python基礎之MySQL資料庫
• 一、約束概述
  • 1、為什麼要約束
  • 2、什麼是約束
  • 3、約束的分類
  • 4、查看當前表已有的約束
• 二、約束語法及用法
  • 1、無符號
  • 2、零填充
  • 3、非空
  • 4、預設值
  • 5、唯一值
    • 5、1.單列唯一（列級）
    • 5、2.聯合唯一（表級）
  • 6、主鍵
  • 7、自增
  • 8、外鍵
    • 8、1.外鍵的定義
    • 8、2.外鍵的作用
    • 8、3.外鍵的建立
    • 8、4.外鍵的多種關係
      • 1.一對多：
      • 2.多對多：
      • 3.一對一：

一、約束概述

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
);