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

標籤：MySQL

上一篇：MySQL常見面試題

下一篇：MongoDB - 聚合查詢