MySQL 4.表操作与列操作

约束：

约束是为了保证数据的完整性和一致性。
约束分为表级约束和列级约束

约束只针对某一个字段使用，称之为列级约束

约束针对于两个或两个以上的字段，称之为表级约束

约束类型（按功能划分）：

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

外键约束：(FOREIGN KEY)

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

外键约束的要求：

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

Demo：

 1 # 1.首先创建一个数据库
 2 CREATE DATABASE test1;
 3 #2.创建一张省份信息表，用于保存省份,只有id和省份的名称
 4 CREATE TABLE province(
 5 id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 6 name VARCHAR(20) NOT NULL
 7 );
 8 #3.创建第二张表，拥有id和name以及省份信息pid
 9 CREATE TABLE tb1(
10 id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11 username VARCHAR(20) NOT NULL,
12 pid SMALLINT UNSIGNED,
13 # 将本表的pid设为外键，其关联的是province表的id
14 FOREIGN KEY (pid) REFERENCES province (id)
15 );

View Code

外键约束的参照操作：

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

Demo：

 1 # 1.创建数据库
 2 CREATE DATABASE gzcsql;
 3 # 2.创建父表
 4 CREATE TABLE province(
 5 id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 6 name VARCHAR(20) NOT NULL
 7 );
 8 # 3.创建子表将其pid设为外键，并设置父表删除时自动更新
 9 CREATE TABLE usertable(
10 id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11 username VARCHAR(20) NOT NULL;
12 pid SMALLINT UNSIGNED,
13 FOREIGN KEY (pid) REFERENCES province(id) ON DELETE CASCADE
14 );
15 # 4.往父表中添加记录
16 INSERT province (name) VALUES('A');
17 INSERT province(name) VALUES('B');
18 INSERT province(name) VALUES('C');
19 # 5.往子表中添加记录
20 INSERT usertable(username,pid) VALUES('Join',3);
21 INSERT usertable(username,pid) VALUES('XiaoMing',1);
22 INSERT usertable(username,pid) VALUES('Xiaohong',1);
23 INSERT usertable(username,pid) VALUES('Xiaojia',1);

View Code

在外键约束分为物理外键和逻辑外键，实际上在实习开发过程中很少使用物理的外键约束很多都去使用逻辑的外键约束，因为物理的外键约束只有innoDB这种引擎才支持，别的引擎不支持。

表级约束与列级约束

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

添加列与删除列

添加一列（默认位置）：ALTER TABLE 表名 ADD 列名列属性...;

添加一列（指定到最开始的位置 '使用FIRST关键字'）：ALTER TABLE 表名 ADD 列名列属性...FIRST;

添加一列（指定固定的位置'通过AFTER关键字后面写要在哪一列后面'）：ALTER TABLE 表名 ADD 列名列属性...AFTER id;

添加多列(多列只能在最后的位置)：ALTER TABLE 表名 ADD(列名1 列属性... ,列名2 列属性...);

删除一列：ALTER TABLE 表名 DROP 列名;

删除多列：ALTER TABLE 表名 DROP 列名1,DROP 列名2,DROP 列名3....;

综合操作Demo：

1 1 # Demo 1 添加一列删除多列（多个操作间用逗号','分隔）
2 2 ALTER TABLE usertable ADD age TINYINT UNSIGNED NOT NULL FIRST,DROP truename,DROP mytext;

View Code

1 # 添加多列删除多列,多个操作之间用逗号隔开
2 ALTER TABLE usertable ADD(aaa TINYINT UNSIGNED NOT NULL,bbb TINYINT UNSIGNED NOT NULL),DROP truename,DROP password;

View Code

添加约束

添加主键：ALTER TABLE 表名 ADD PRIMARY KEY (要添加约束的列名);

添加主键（同时命名）：ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (要添加约束的列名);

删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;

添加唯一约束：ALTER TABLE 表名 ADD UNIQUE (要添加约束的列名);

删除唯一约束：ALTER TABLE 表名 DROP INDEX 唯一约束列的列名;

添加外键约束：ALTER TABLE 表名 ADD FOREIGN KEY (要添加约束的列名) REFERENCES 要关联的表名 (要关联表的列名);

删除外键约束：分两步：1. 查看外键约束名称(该指令是查看表创建时的信息) SHOW CREATE TABLE 要删除外键的表名;

　　　　　　　　　　　　2.删除　　　　　　　　　　　　　　　　　　　　ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

添加默认约束：ALTER TABLE 表名 ALTER 要设置的列名 SET DEFAULT 设置的值;

删除默认约束：ALTER TABLE 表名 ALTER 要删除的列名 DROP DEFAULT;

修改列

修改列定义（如名称、位置、数据类型等）：ALTER TABLE 表名 MODIFY 要修改的列名列属性（如数据类型、位置、是否为空等）;

PS：修改列定义时若大类型改到小类型的话有可能会出现数据的丢失。

修改列名称（同时还能修改列的数据类型等属性，其功能比MODIFY强大）：ALTER TABLE 表名 CHANGE 老的列名新的列名列属性...;

修改数据表名称：ALTER TABLE 表名 RENAME 新表名;

　　　　　　　　RENAME TABLE 表名 TO 新表名;

　　　　　　　　修改多张表名： RENAME TABLE 旧表名 TO 新表名, 旧表名 TO 新表名...;

PS:尽量少修改或者不修改表名和列名，因为表名和列名被引用的情况下修改会导致功能无法运行