資料庫 加強篇（1）

標籤：cat   聲明   tab   設計   add   into   column   varchar   cas   

資料約束2.1什麼資料約束

對使用者動作表的資料進行約束

2.2 預設值

作用： 當使用者對使用預設值的欄位不插入值的時候，就使用預設值。

注意：

1）對預設值欄位插入null是可以的。

2）對預設值欄位可以插入非null

-- 1.1 預設值 CREATE TABLE student( id INT, NAME VARCHAR(20), address VARCHAR(20) DEFAULT ‘廣州天河‘  -- 預設值 )   DROP TABLE student; -- 當欄位沒有插入值的時候，mysql自動給該欄位分配預設值 INSERT INTO student(id,NAME) VALUES(1,‘張三‘);   -- 注意：預設值的欄位允許為null INSERT INTO student(id,NAME,address) VALUE(2,‘李四‘,NULL); INSERT INTO student(id,NAME,address) VALUE(3,‘王五‘,‘廣州番禺‘);

 

2.3 非空

作用： 限制欄位必須賦值

注意：

1）非Null 字元必須賦值

2）非Null 字元不能賦null

-- 1.2 非空 -- 需求： gender欄位必須有值（不為null） CREATE TABLE student( id INT, NAME VARCHAR(20), gender VARCHAR(2) NOT NULL -- 非空 )   -- 非空欄位必須賦值 INSERT INTO student(id,NAME) VALUES(1,‘李四‘); -- 非Null 字元不能插入null INSERT INTO student(id,NAME,gender) VALUES(1,‘李四‘,NULL);

 

2.4 唯一

作用： 對欄位的值不能重複

注意：

1）唯一欄位可以插入null

2）唯一欄位可以插入多個null

 

-- 1.3 唯一 CREATE TABLE student( id INT UNIQUE, -- 唯一 NAME VARCHAR(20) )   INSERT INTO student(id,NAME) VALUES(1,‘zs‘); INSERT INTO student(id,NAME) VALUES(1,‘lisi‘); -- ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘id‘   INSERT INTO student(id,NAME) VALUES(2,‘lisi‘);

 

2.5 主鍵

作用： 非空+唯一

注意：

1）通常情況下，每張表都會設定一個主鍵欄位。用於標記表中的每條記錄的唯一性。

2）建議不要選擇表的包含業務含義的欄位作為主鍵，建議給每張表獨立設計一個非業務含義的 id欄位。

-- 1.4 主鍵（非空+唯一） DROP TABLE student;   CREATE TABLE student( id INT PRIMARY KEY, -- 主鍵 NAME VARCHAR(20) )   INSERT INTO student(id,NAME) VALUES(1,‘張三‘); INSERT INTO student(id,NAME) VALUES(2,‘張三‘); -- INSERT INTO student(id,NAME) VALUES(1,‘李四‘); -- 違反唯一約束： Duplicate entry ‘1‘ for key ‘PRIMARY‘   -- insert into student(name) value(‘李四‘); -- 違反非空約束： ERROR 1048 (23000): Column ‘id‘ cannot be null

 

2.6 自增長

作用： 自動遞增

 

-- 1.5 自增長 CREATE TABLE student( id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增長，從0開始  ZEROFILL 零填充 NAME VARCHAR(20) )   -- 自增長欄位可以不賦值，自動遞增 INSERT INTO student(NAME) VALUES(‘張三‘); INSERT INTO student(NAME) VALUES(‘李四‘); INSERT INTO student(NAME) VALUES(‘王五‘);   SELECT * FROM student; -- 不能影響自增長約束 DELETE FROM student; -- 可以影響自增長約束 TRUNCATE TABLE student;

 

2.7 外鍵

作用：約束兩種表的資料

 

出現兩種表的情況：

解決資料冗餘高問題： 獨立出一張表

例如： 員工表  和  部門表

問題出現：在插入員工表資料的時候，員工表的部門ID欄位可以隨便插入！！！！！

 

使用外鍵約束：約束插入員工表的部門ID欄位值

 

解決辦法： 在員工表的部門ID欄位添加一個外鍵約束

 

-- 部門表（主表） CREATE TABLE dept( id INT PRIMARY KEY, deptName VARCHAR(20) )   -- 修改員工表（副表/從表） CREATE TABLE employee( id INT PRIMARY KEY, empName VARCHAR(20), deptId INT,-- 把部門名稱改為部門ID -- 聲明一個外鍵約束 CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) --           外鍵名稱                  外鍵               參考表(參考欄位) )

 

    注意：

1）被約束的表稱為副表，約束別人的表稱為主表，外鍵設定在副表上的！！！

2）主表的參考欄位通用為主鍵！

3）添加資料： 先添加主表，再添加副表

4）修改資料： 先修改副表，再修改主表

5）刪除資料： 先刪除副表，再刪除主表

 

-- 1.6 外鍵約束 -- 員工表 CREATE TABLE employee( id INT PRIMARY KEY, empName VARCHAR(20), deptName VARCHAR(20) -- 部門名稱 )   INSERT INTO employee VALUES(1,‘張三‘,‘軟體開發部‘); INSERT INTO employee VALUES(2,‘李四‘,‘軟體開發部‘); INSERT INTO employee VALUES(3,‘王五‘,‘應用維護部‘);   SELECT * FROM employee;   -- 添加員工，部門名稱的資料冗餘高 INSERT INTO employee VALUES(4,‘陳六‘,‘軟體開發部‘);   -- 解決資料冗餘高的問題：給冗餘的欄位放到一張獨立表中 -- 獨立設計一張部門表 CREATE TABLE dept( id INT PRIMARY KEY, deptName VARCHAR(20) )   DROP TABLE employee;   -- 修改員工表 CREATE TABLE employee( id INT PRIMARY KEY, empName VARCHAR(20), deptId INT,-- 把部門名稱改為部門ID -- 聲明一個外鍵約束 CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：級聯修改 --           外鍵名稱                  外鍵               參考表(參考欄位) )   INSERT INTO dept(id,deptName) VALUES(1,‘軟體開發部‘); INSERT INTO dept(id,deptName) VALUES(2,‘應用維護部‘); INSERT INTO dept(id,deptName) VALUES(3,‘秘書部‘);   INSERT INTO employee VALUES(1,‘張三‘,1); INSERT INTO employee VALUES(2,‘李四‘,1); INSERT INTO employee VALUES(3,‘王五‘,2); INSERT INTO employee VALUES(4,‘陳六‘,3);   -- 問題: 該記錄業務上不合法，員工插入了一個不存在的部門資料 INSERT INTO employee VALUES(5,‘陳六‘,4); -- 違反外鍵約束： Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))   -- 1）當有了外鍵約束，添加資料的順序： 先添加主表，再添加副表資料 -- 2）當有了外鍵約束，修改資料的順序： 先修改副表，再修改主表資料 -- 3）當有了外鍵約束，刪除資料的順序： 先刪除副表，再刪除主表資料 -- 修改部門(不能直接修改主表) UPDATE dept SET id=4 WHERE id=3; -- 先修改員工表 UPDATE employee SET deptId=2 WHERE id=4;   -- 刪除部門 DELETE FROM dept WHERE id=2;   -- 先刪除員工表 DELETE FROM employee WHERE deptId=2;   SELECT * FROM dept; SELECT * FROM employee;

 

  2.8 級聯操作

問題： 當有了外鍵約束的時候，必須先修改或刪除副表中的所有關聯資料，才能修改或刪除主表！但是，我們希望直接修改或刪除主表資料，從而影響副表資料。可以使用級聯操作實現！！！

 

級聯修改： ON UPDATE CASCADE

串聯刪除： ON DELETE CASCADE

 

CREATE TABLE employee( id INT PRIMARY KEY, empName VARCHAR(20), deptId INT,-- 把部門名稱改為部門ID -- 聲明一個外鍵約束 CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：級聯修改 --           外鍵名稱                  外鍵               參考表(參考欄位) ) 注意： 級聯操作必須在外鍵基礎上使用

 

-- 級聯修改（修改） -- 直接修改部門 UPDATE dept SET id=5 WHERE id=4;   -- 串聯刪除 -- 直接刪除部門 DELETE FROM dept WHERE id=1;

資料庫 加強篇（1）