回顧和概述

數(shù)據(jù)類型

• 整型
• 浮點型
• 字符型
• 日期時間型

數(shù)據(jù)表操作

• 如何創(chuàng)建數(shù)據(jù)表

• PRIMARY KEY（主鍵約束）
• UNIQUE KEY （唯一約束）
• DEFAULT（默認約束）
• NOT NULL（非空約束）

• 記錄插入
• 查找記錄

外鍵約束的要求解析

約束

1.約束保證數(shù)據(jù)的完整性和一致性
2.約束分為表級約束和列級約束
3.約束類型包括

• NOT NULL（非空約束）
• PRIMARY KEY（主鍵約束）
• UNIQUE KEY（唯一約束）
• DEFAULT（默認約束）
• FOREIGN KEY（外鍵約束）

根據(jù)約束的字段多少定義列級約束和表級約束
列級約束：約束只針對于某一個字段使用
表級約束：約束針對于兩個或兩個以上的字段使用

外鍵約束：FOREIGN KEYp

• 保持數(shù)據(jù)一致性，完整性
• 實現(xiàn)一對多或一對一的關系

外鍵約束的要求：

1.父表和子表必須使用相同的存儲引擎绰咽，而且禁止使用臨時表闲礼。
2.數(shù)據(jù)表的存儲引擎只能為InoDB。
3.外鍵列和參照列必須具有相似的數(shù)據(jù)類型痴腌。其中數(shù)字的長度或是否有符號位必須相同现拒；而字符的長度可以不同蔓腐。
4.外鍵列和參照列必須創(chuàng)建索引。如果外鍵列不存在索引刁品，MySQL將自動創(chuàng)建索引泣特。

父表：子表所參照的表
子表：具有外鍵列的表
外鍵列：曾經(jīng)加過外鍵關鍵詞的列
參照列：外鍵所參照的列
參照列如果沒有索引會自動創(chuàng)建，外鍵列沒有索引不會自動創(chuàng)建

編輯數(shù)據(jù)表的默認存儲引擎

找到配置文件my.ini挑随，打開修改成如下：
default-storage-engine=INNODB

修改完成后需要重啟mysql服務

外鍵約束例子：
創(chuàng)建省份表

CREATE TABLE provinces（
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);

使用SHOW CREATE TABLE provinces;查看一下存儲引擎
創(chuàng)建用戶表：

CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid BIGINT,
FOREIGN KEY (pid) PEFERENCES provinces (id)
);

會報錯状您，因為pid的類型和provinces表的id不一樣

CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT,
FOREIGN KEY (pid) PEFERENCES provinces (id)
);

同樣報錯，因為原來是無符號的兜挨，數(shù)字類型的類型必須一樣膏孟，包括符號

CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) PEFERENCES provinces (id)
);

父表：provinces
子表：users
主鍵會自動創(chuàng)建索引
顯示proviences的索引：SHOW INDEXES FROM proviences;
以網(wǎng)格形式顯示：SHOW INDEXES FROM proviences\G;
顯示id已經(jīng)創(chuàng)建索引
以網(wǎng)格形式顯示users的索引：SHOW INDEXES FROM proviences\G;
有兩個索引，一個是id字段是主鍵拌汇，自動加上了索引柒桑，系統(tǒng)為pid字段自動創(chuàng)建了索引
此時查看users表：
SHOW CREATE TABLE users;
看到系統(tǒng)加了一個KEY `pid` (`pid`)

外鍵約束的參照操作

1.CASCADE：從父表刪除或更新且自動刪除或跟新子表中匹配的行
2.SET NULL：從父表刪除或更新行，并設置子表中的外鍵列為NULL噪舀。如果使用該選項魁淳，必須保證子表列沒有指定NOT NULL
3.RESTRICT：拒絕對父表的刪除或更新操作
4.NO ACTION：標準SQL的關鍵字，在MySQL中與RESTRICT相同

CASCADE例：
創(chuàng)建users1表

CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) PEFERENCES provinces (id) ON DELETE CASCADE
);

在省份表中插入三條數(shù)據(jù)：

INSERT provinces(pname) VALUES('A');
INSERT provinces(pname) VALUES('B');
INSERT provinces(pname) VALUES('C');

在users1表中插入一條數(shù)據(jù)

INSERT users1(username,pid) VALUES('Tom',3);#正確
INSERT users1(username,pid) VALUES('John',7);#錯誤与倡，因為provinces表中沒有id為7的值
INSERT users1(username,pid) VALUES('John',1);#正確
INSERT users1(username,pid) VALUES('Rose',3);#正確

此時用SELECT * FROM users1;查看表記錄界逛，發(fā)現(xiàn)插入的id為134而不是123，因為第二條雖然沒有插入成功纺座，但是id還是會自動增長
現(xiàn)在息拜，把provinces表中的id=3的記錄刪除，此時再查看proviences表和ueses1表發(fā)現(xiàn)provinces表中的id=3的記錄被刪除，users1表中的pid=3的值也一同被刪除了少欺，因為剛才選擇的外鍵約束參照是ON DELETE CASCADE喳瓣，此處只演示刪除操作，更新操作同樣也會更新子表中的數(shù)據(jù)

*在實際開發(fā)中赞别，很少使用物理外鍵約束夫椭，通常使用邏輯外鍵約束，因為物理外鍵約束只有innodb存儲引擎支持氯庆，在其他存儲引擎如：MyISAM上是不支持的，所以如果想使用MyISAM存儲引擎扰付，通常不會定義外鍵約束堤撵，所謂邏輯約束是指在創(chuàng)建表時不會使用FOREIGN關鍵詞，而是在定義兩張表的時候按照存在著某種結構的方式去定義

表級約束與列級約束

按照參照數(shù)目的多少羽莺，可以分為表級約束和列級約束

表級約束與列級約束

• 對一個數(shù)據(jù)列建立的約束实昨，稱為列級約束
• 對多個數(shù)據(jù)列建立的約束，稱為表級約束
• 列級約束既可以在列定義時聲明盐固，也可以在列定以后聲明
• 表級約束只能在列定義后聲明

CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) PEFERENCES provinces (id) ON DELETE CASCADE #列定義后聲明約束荒给，放在同一行為定義時同時聲明
);

實際開發(fā)中，列級約束用的較多刁卜，表級約束很少用到志电。并不是所有約束都存在列級約束和表級約束之分，NOT NULL 和 DEFAULT 這兩個就只有列級約束蛔趴，主鍵挑辆，唯一，外鍵約束才存在列級約束和表級約束孝情，還有一個CHEACK約束鱼蝉，但是不起作用，所以沒有提到箫荡，了解一下就行了

修改數(shù)據(jù)表-添加刪除列

修改數(shù)據(jù)表

添加單列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

COLUMN可以省略魁亦，F(xiàn)IRST 插入的列在所有列的最前方，AFTER clo_name插入的列在某一列的后邊羔挡，如果不寫則默認在所有列的最后邊

添加列：

ALTER TABLE user1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;#添加的列在所有列的最后邊

ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;#添加的列在username列的后邊

ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST;#添加的列在所有列的最前邊

添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_defination,...)

添加單列時列不需要加小括號洁奈，可以指定添加列的位置，添加多列時不能指定添加位置绞灼，只能默認放到所有列的后邊

刪除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

刪除列：

ALTER TABLE users1 DROP truename;#刪除truename字段
ALTER TABLE users2 DROP password,DROP age;#password字段和age字段同時被刪除

也可以刪除的同時新增一列

修改數(shù)據(jù)表-添加約束

添加主鍵約束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

CONSTRAINT 可以不寫睬魂，如果寫可以為主鍵起名字

index_type索引類型，兩種：HASH索引镀赌，BTREE索引氯哮。默認為BTREE。

例：
創(chuàng)建無用的表users2

CREATE TABLE users2(
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED
);

增加主鍵

ALTER TABLE users2 ADD id SMALLINT UNSIGNED;#增加一列
ALTER TABLE users2 ADD C ONSTRAINT PK_users2_id PRIMARY KEY (id);#將id字段設置為主鍵

使用SHOW COLUMNS FROM users2;查看表結構可以看到id被設置成了主鍵

添加唯一約束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_naem,...)

和主鍵約束不同的是唯一約束可以有多個，而主鍵約束只能有一個

為username添加唯一約束例：

ALTER TABLE users2 ADD UNIQUE (username);

使用SHOW COLUMNS FROM users2;查看表結構可以看到username被設置了唯一約束

添加外鍵約束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

要求users2的pid參照provinces表中的id
例：

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);#為pid添加provinces表中的id外鍵約束喉钢，

使用SHOW COLUMNS FROM users2;查看表結構可以看到為pid添加了外鍵約束

添加/刪除默認約束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

添加/刪除默認約束：

ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;#添加一列
ALTER TABLE users2 ALTER age SET DEFAULT 15;#為age字段添加默認約束
ALTER TABLE users2 ALTER age DROP DEFAULT;#刪除age字段的默認約束

修改數(shù)據(jù)表-刪除約束#

刪除主鍵約束##

ALTER TABLE tbl_name DROP PRIMARY KEY

刪除主鍵約束

ALTER TABLE users2 DROP PRIMARY KEY;#刪除主鍵約束

不用加主鍵的列名姆打，因為一張表就一個主鍵

刪除唯一約束##

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

ALTER COLUMNS FROM users2 DROP INDEX username;刪除users2表中的username索引
使用SHOW INDEX FROM users2\G;#以網(wǎng)格形式查看索引看到username的索引被刪除了
不是刪除字段，只刪除索引約束

刪除外鍵約束##

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

使用SHOW CREATE TABLE users2;查看外鍵約束名稱
可以看到系統(tǒng)設置的外鍵名稱users2_ibfk_1
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;#刪除外鍵約束

查看表結構看到外鍵已經(jīng)被刪除肠虽，還存在一個索引
使用ALTER TABLE users2 DROP INDEX pid;#刪除索引
再查看表結構幔戏，看到索引也被刪除了

修改數(shù)據(jù)表-修改列定義和更名數(shù)據(jù)表#

修改列定義：數(shù)據(jù)列名字沒問題，但是類型和位置可能有問題
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

例：剛才的users2表税课，id不在第一個位置（在哪個位置無所謂闲延，但是習慣上把id放到第一個位置）
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;#把id字段放到第一個位置
使用SHOW COLUMNS FROM users2;看到id已經(jīng)放到了第一個位置

ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;#把id變成TINYINT類型
由大類型改成小類型有可能造成數(shù)據(jù)丟失

修改列名稱##

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

同時修改類型和名稱：
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;#把pid的名稱改成p_id同時把類型改成TINYINT

數(shù)據(jù)表更名##

• 方法一：ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
• 方法二：RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...

使用方法2可以為多張數(shù)據(jù)表更名

ALTER TABLE users2 RENAME users3;#把數(shù)據(jù)表users2的表名改成users3
使用SHOW TABLES查看所有數(shù)據(jù)表看到users2被改成了users3

RENAME TABLE users3 TO users2;#把數(shù)據(jù)表users3的表名改成users2

盡量少使用數(shù)據(jù)列和數(shù)據(jù)表的更名，因為當我們以后創(chuàng)建了索引或者創(chuàng)建了視圖或存儲過程表名和列名被引用了如果更名會導致存儲過程或視圖無法使用

本節(jié)知識點##

• 約束
  • 按功能劃分：NOT NULL,PRIMARY KEY,UNIQUE KEY,DEFAULT,FOREIGN KEY
  • 按數(shù)據(jù)列的數(shù)目劃分：表級約束韩玩，列級約束
• 修改數(shù)據(jù)表
  • 針對字段的操作：添加/刪除字段垒玲、修改列定義，修改列名稱等
  • 針對約束的操作：添加/刪除各種約束
  • 針對數(shù)據(jù)表的操作：數(shù)據(jù)表更名（兩種方式）

小結#

Paste_Image.png