第三章约束和修改数据表

作者: 齐天大圣李圣杰 | 来源:发表于2016-09-14 19:47 被阅读0次

回顾和概述

数据类型

整型
浮点型
字符型
日期时间型

数据表操作

如何创建数据表

PRIMARY KEY（主键约束）
UNIQUE KEY （唯一约束）
DEFAULT（默认约束）
NOT NULL（非空约束）

记录插入
查找记录

外键约束的要求解析

约束

1.约束保证数据的完整性和一致性
2.约束分为表级约束和列级约束
3.约束类型包括

NOT NULL（非空约束）
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

根据约束的字段多少定义列级约束和表级约束
列级约束：约束只针对于某一个字段使用
表级约束：约束针对于两个或两个以上的字段使用

外键约束：FOREIGN KEYp

保持数据一致性，完整性
实现一对多或一对一的关系

外键约束的要求：

1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2.数据表的存储引擎只能为InoDB。
3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度可以不同。
4.外键列和参照列必须创建索引。如果外键列不存在索引，MySQL将自动创建索引。

父表：子表所参照的表
子表：具有外键列的表
外键列：曾经加过外键关键词的列
参照列：外键所参照的列
参照列如果没有索引会自动创建，外键列没有索引不会自动创建

编辑数据表的默认存储引擎

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

修改完成后需要重启mysql服务

外键约束例子：
创建省份表

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

使用SHOW CREATE TABLE provinces;查看一下存储引擎
创建用户表：

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

同样报错，因为原来是无符号的，数字类型的类型必须一样，包括符号

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
主键会自动创建索引
显示proviences的索引：SHOW INDEXES FROM proviences;
以网格形式显示：SHOW INDEXES FROM proviences\G;
显示id已经创建索引
以网格形式显示users的索引：SHOW INDEXES FROM proviences\G;
有两个索引，一个是id字段是主键，自动加上了索引，系统为pid字段自动创建了索引
此时查看users表：
SHOW CREATE TABLE users;
看到系统加了一个KEY `pid` (`pid`)

外键约束的参照操作

1.CASCADE：从父表删除或更新且自动删除或跟新子表中匹配的行
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3.RESTRICT：拒绝对父表的删除或更新操作
4.NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同

CASCADE例：
创建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
);

在省份表中插入三条数据：

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

在users1表中插入一条数据

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;查看表记录，发现插入的id为134而不是123，因为第二条虽然没有插入成功，但是id还是会自动增长
现在，把provinces表中的id=3的记录删除，此时再查看proviences表和ueses1表发现provinces表中的id=3的记录被删除，users1表中的pid=3的值也一同被删除了，因为刚才选择的外键约束参照是ON DELETE CASCADE，此处只演示删除操作，更新操作同样也会更新子表中的数据

*在实际开发中，很少使用物理外键约束，通常使用逻辑外键约束，因为物理外键约束只有innodb存储引擎支持，在其他存储引擎如：MyISAM上是不支持的，所以如果想使用MyISAM存储引擎，通常不会定义外键约束，所谓逻辑约束是指在创建表时不会使用FOREIGN关键词，而是在定义两张表的时候按照存在着某种结构的方式去定义

表级约束与列级约束

按照参照数目的多少，可以分为表级约束和列级约束

表级约束与列级约束

对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束
列级约束既可以在列定义时声明，也可以在列定以后声明
表级约束只能在列定义后声明

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 #列定义后声明约束，放在同一行为定义时同时声明
);

实际开发中，列级约束用的较多，表级约束很少用到。并不是所有约束都存在列级约束和表级约束之分，NOT NULL 和 DEFAULT 这两个就只有列级约束，主键，唯一，外键约束才存在列级约束和表级约束，还有一个CHEACK约束，但是不起作用，所以没有提到，了解一下就行了

修改数据表-添加删除列

修改数据表

添加单列

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

COLUMN可以省略，FIRST 插入的列在所有列的最前方，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字段同时被删除

也可以删除的同时新增一列

修改数据表-添加约束

添加主键约束

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

CONSTRAINT 可以不写，如果写可以为主键起名字

index_type索引类型，两种：HASH索引，BTREE索引。默认为BTREE。

例：
创建无用的表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字段的默认约束

修改数据表-删除约束#

删除主键约束##

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;#以网格形式查看索引看到username的索引被删除了
不是删除字段，只删除索引约束

删除外键约束##

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

使用SHOW CREATE TABLE users2;查看外键约束名称
可以看到系统设置的外键名称users2_ibfk_1
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;#删除外键约束

查看表结构看到外键已经被删除，还存在一个索引
使用ALTER TABLE users2 DROP INDEX pid;#删除索引
再查看表结构，看到索引也被删除了

修改数据表-修改列定义和更名数据表#

修改列定义：数据列名字没问题，但是类型和位置可能有问题
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已经放到了第一个位置

ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;#把id变成TINYINT类型
由大类型改成小类型有可能造成数据丢失

修改列名称##

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

数据表更名##

方法一：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可以为多张数据表更名

ALTER TABLE users2 RENAME users3;#把数据表users2的表名改成users3
使用SHOW TABLES查看所有数据表看到users2被改成了users3

RENAME TABLE users3 TO users2;#把数据表users3的表名改成users2

尽量少使用数据列和数据表的更名，因为当我们以后创建了索引或者创建了视图或存储过程表名和列名被引用了如果更名会导致存储过程或视图无法使用

本节知识点##

约束
- 按功能划分：NOT NULL,PRIMARY KEY,UNIQUE KEY,DEFAULT,FOREIGN KEY
- 按数据列的数目划分：表级约束，列级约束
修改数据表
- 针对字段的操作：添加/删除字段、修改列定义，修改列名称等
- 针对约束的操作：添加/删除各种约束
- 针对数据表的操作：数据表更名（两种方式）

小结#

Paste_Image.png

第三章约束和修改数据表

回顾和概述

数据类型

数据表操作

外键约束的要求解析

约束

外键约束的要求：

编辑数据表的默认存储引擎

外键约束的参照操作

表级约束与列级约束

表级约束与列级约束

修改数据表-添加删除列

修改数据表

添加单列

添加多列

删除列

修改数据表-添加约束

添加主键约束

添加唯一约束

添加外键约束

添加/删除默认约束

修改数据表-删除约束#

删除主键约束##

删除唯一约束##

删除外键约束##

修改数据表-修改列定义和更名数据表#

修改列名称##

数据表更名##

本节知识点##

小结#

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第三章 约束和修改数据表

回顾和概述

数据类型

数据表操作

外键约束的要求解析

约束

外键约束的要求：

编辑数据表的默认存储引擎

外键约束的参照操作

表级约束与列级约束

表级约束与列级约束

修改数据表-添加删除列

修改数据表

添加单列

添加多列

删除列

修改数据表-添加约束

添加主键约束

添加唯一约束

添加外键约束

添加/删除默认约束

修改数据表-删除约束#

删除主键约束##

删除唯一约束##

删除外键约束##

修改数据表-修改列定义和更名数据表#

修改列名称##

数据表更名##

本节知识点##

小结#

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第三章约束和修改数据表