MySQL約束
- 基本介紹
約束用于確保資料庫的資料滿足特定的商業規則
在mysql中，約束包括：not null，unique，primary key，foreign key 和check 5種
1.primary key(主鍵)
欄位名 欄位型別 primary key
用于唯一地標識表行的資料，當定義主鍵約束之后，該列不能重復
- 細節說明
- primary key不能重復而且不能為null
- 一張表最多只能有一個主鍵，但是可以是復合主鍵
- 主鍵的指定方式有兩種
- 直接在欄位名后面指定：欄位名 primary key
- 在表定義最后寫 primary key（列名）
- 使用desc 表名，可以看到primary key的情況
- 在實際開發中，每一張表往往都會設計一個主鍵
例子
-- 主鍵的使用
-- id name email
CREATE TABLE t17(
id INT PRIMARY KEY, -- 表示id列是主鍵
`name` VARCHAR(32),
email VARCHAR(32)
);
INSERT INTO t17 VALUES(1,'jack','[email protected]');
INSERT INTO t17 VALUES(2,'tom','[email protected]');
-- 1. primary key不能重復而且不能為null
INSERT INTO t17 VALUES(NULL,'jack','[email protected]');-- 插入失敗，Column 'id' cannot be null
-- 2. 一張表最多只能有一個主鍵，但是可以是復合主鍵（比如id+name）
-- 演示復合主鍵
CREATE TABLE t18(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- 這里就是復合主鍵
);
INSERT INTO t18 VALUES(1,'tom','[email protected]'); -- ok
INSERT INTO t18 VALUES(1,'jack','[email protected]');-- OK
INSERT INTO t18 VALUES(1,'tom','[email protected]'); -- false
SELECT * FROM t18;
-- 3. 主鍵的指定方式有兩種
-- 3.1直接在欄位名后面指定：欄位名 primary key
CREATE TABLE t19(
id INT PRIMARY KEY,
`name` VARCHAR(32),
email VARCHAR(32)
);
-- 3.2在表定義最后寫 primary key（列名）
CREATE TABLE t20(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id)
);
-- 4. 使用desc 表名，可以看到primary key的情況
DESC t20; -- 查看t20的情況，顯示約束的情況
2.not null(非空)
如果在列上定義了not null，那么當插入資料時，必須為列提供資料
欄位名 欄位型別 not null
3.unique(唯一)
當定義了唯一約束后，該列值是不能重復的
欄位名 欄位型別 unique
- unique細節
- 如果沒有指定not null，則unique欄位可以有多個null
- 一張表可以有多個unique欄位
-- unique的使用
CREATE TABLE t21(
id INT UNIQUE, -- 表示id列不可重復
`name` VARCHAR(32),
email VARCHAR(32)
)
INSERT INTO t21 VALUES(1,'jack','[email protected]'); -- ok
INSERT INTO t21 VALUES(1,'tom','[email protected]'); -- false，Duplicate entry '1' for key 'id'
-- unique細節
-- 1. 如果沒有指定not null，則unique欄位可以有多個null
-- 如果一個列（欄位），是 unique not null 則使用效果類似 primary key
INSERT INTO t21 VALUES(NULL,'tom','[email protected]');-- ok
SELECT * FROM t21;
-- 2. 一張表可以有多個unique欄位
CREATE TABLE t22(
id INT UNIQUE, -- 表示id列不可重復
`name` VARCHAR(32) UNIQUE, -- 表示name也不可以重復
email VARCHAR(32)
)
DESC t22;
4.foreign key(外鍵)
- foreign key(外鍵)
用于定義主表和從表之間的關系：
外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，
當定義外鍵約束后，要求外鍵列資料必須在主表的主鍵列存在或是為null
- 語法
foreign key(本表欄位名) references 主表名(主鍵名或unique欄位名)
例子
-- 外鍵演示
-- 創建 主表 my_class
CREATE TABLE my_class(
id INT PRIMARY KEY, -- 班級編號
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
-- 創建從表 my_stu
CREATE TABLE my_stu(
id INT PRIMARY KEY,-- 學生編號
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT, -- 學生所在的班級的編號
-- 下面指定外鍵關系
FOREIGN KEY(class_id) REFERENCES my_class(id)
);
-- 測驗資料
INSERT INTO my_class VALUES(100,'java'),(200,'web');
INSERT INTO my_class VALUES(300,'php');
SELECT * FROM my_class;
INSERT INTO my_stu VALUES(1,'tom',100);
INSERT INTO my_stu VALUES(2,'jack',200);
INSERT INTO my_stu VALUES(3,'hsp',300);
INSERT INTO my_stu VALUES(4,'mary',400); -- 失敗，因為400號班級不存在
SELECT * FROM my_stu;
- 外鍵細節說明
- 外鍵指向的主表的欄位，要求是primary key 或是unique
- 表的型別是innodb，這樣的表才支持外鍵
- 外鍵欄位的型別要和主鍵欄位的型別一致（長度可以不同）
- 外鍵欄位的值，必須在主鍵欄位中出現過，或者為null [前提是外鍵欄位允許為null]
- 一旦建立主外鍵的關系，資料就不能隨意洗掉了
5.check
- check
用于強制行資料必須滿足的條件，假定在sal列上定義了check約束，并要求sal列值在1000~2000之間，如果不在此范圍，就會提示出錯
提示：oracle和sql server均支持check，但是mysql5.7目前還不支持check，只做語法校驗，但不會生效
- 語法
列名 型別 check(check條件)
在mysql中實作check的功能，一般是在程式中控制或者通過觸發器完成
例子
-- 演示check的使用
-- 目前mysql5.7還不支持check，只做語法校驗但不會生效
-- 測驗
CREATE TABLE t23(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK(sex IN('man','woman')),
sal DOUBLE CHECK(sal >1000 AND sal<2000)
);
6.練習
商店售貨系統表設計案例
現有一個商店的資料庫shop_db，記錄客戶及其購物情況，由下面三個表組成：
- 商品表 goods
- 商品號 goods_id
- 商品名 goods_name
- 單價 unitprice
- 商品類別 category
- 供應商 provider
- 客戶表 customer
- 客戶號 customer_id
- 姓名 name
- 住址 address
- 電郵 email
- 性別 sex
- 身份證 card_Id
- 購買表 purchase
- 購買訂單號 order_id
- 客戶號 customer_id
- 商品號 goods_id
- 購買數量 nums
建表，在定義中要求宣告[進行合理設計]
- 每個表的主外鍵
- 客戶的姓名不能為空值
- 電郵不能夠重復
- 客戶的性別[男|女]
- 單價unitprice在1.0~9999.99之間
-- 使用約束的課堂練習
-- 1,創建資料庫
CREATE DATABASE shop_db;
-- 2,商品表 goods
CREATE TABLE goods(
goods_id INT PRIMARY KEY, -- 商品號
goods_name VARCHAR(64) NOT NULL DEFAULT '', -- 商品名
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 -- 單價
CHECK(unitprice BETWEEN 1.0 AND 9999.99),
category INT NOT NULL DEFAULT 0, -- 商品類別
provider VARCHAR(64) NOT NULL DEFAULT '' -- 供應商
);
-- 3,客戶表 customer
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY, -- 客戶號
`name` VARCHAR(64) NOT NULL DEFAULT '', -- 姓名
address VARCHAR(64) NOT NULL DEFAULT '', -- 地址
email VARCHAR(64) UNIQUE NOT NULL, -- 電郵
sex ENUM('男','女') NOT NULL, -- 使用列舉型別，性別
card_Id CHAR(18) -- 身份證
);
-- 4,購買表 purchase
CREATE TABLE purchase(
order_id INT PRIMARY KEY, -- 訂單號
customer_id CHAR(8) NOT NULL DEFAULT '', -- 客戶號
goods_id INT NOT NULL DEFAULT 0, -- 商品號
num INT NOT NULL DEFAULT 0, -- 購買數量
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
FOREIGN KEY(goods_id) REFERENCES goods(goods_id)
);
DESC goods;
DESC customer;
DESC purchase;
7.自增長
- 語法
欄位名 整型 primary key auto_increment
例子：在某張表中，存在一個id列（整數）
我們希望在添加記錄的時候，該列從1開始自動地增長，應該怎么處理？
-- 自增長演示
-- 創建表
CREATE TABLE t24(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
DESC t24;
-- 測驗自增長的使用
INSERT INTO t24 VALUES(NULL,'[email protected]','jack');
INSERT INTO t24 VALUES(NULL,'[email protected]','tom');
INSERT INTO t24(email,`name`) VALUES('[email protected]','hsp');
SELECT * FROM t24;
- 自增長使用細節
-
一般來說自增長是和primary key配合使用的
-
自增長也可以單獨使用 [但是需要配合一個unique]
-
自增長修飾的欄位為整數型（雖然小數也可以但是很少這樣使用）
-
自增長默認從1開始，也可以通過如下命令來修改
alter table 表名 auto_increment = xxx; -
如果添加資料時候，給自增長欄位（列）指定有值，則以指定的值為準，一旦指定了值，下一次自增長就會以指定的值開始增長，因此，如果指定了自增長，一般來說就按自增長的規則來添加資料
-- 修改默認的自增長開始值
CREATE TABLE t25(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
ALTER TABLE t25 AUTO_INCREMENT =100;
INSERT INTO t25 VALUES(NULL,'[email protected]','jack');
-- 指定值
INSERT INTO t25 VALUES(666,'[email protected]','hsp');
-- 一旦指定了值，下一次自增長就會以指定的值開始增長
INSERT INTO t25 VALUES(NULL,'[email protected]','marry');
SELECT * FROM t25;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/511011.html
標籤：其他
上一篇：淺談Mysql常見注意事項一
下一篇：SQL高級（事務和觸發器）