(3)Mysql约束以及修改数据表

回顾上节的内容：
1. 数据类型包括整型, 浮点型, 字符型, 日期时间型；
2. 数据表操作:
(1) 创建数据表 CREATE TABLE t1；
(2) PRIMARY KEY, UNIQUE KEY, DEFAULT, NOT NULL
(3) 记录的插入与查找 INSERT SELECT
(3)Mysql约束以及修改数据表
这里我们来具体说明约束：

约束是为了保证数据的完整性和一致性
约束分为表级约束和列级约束。之所以分为表级约束和列级约束，是根据约束所针对的字段的数目的多少来决定的，如果约束只针对于某一个字段来约束，那么这种约束我们称之为列级约束，如果针对两个或者两个以上的字段进行使用的话，我们则称之为表级约束。
约束类型包括：
NOT NULL 非空约束
PRIMARY KEY 主键约束
UNIQUE KEY 唯一约束
DEFAULT 默认约束
FORGIGN KEY 外键约束

前四个之前已经讲过，外键约束其作用在于保证数据的一致性和完整性，实现数据表的一对一或者一对多的关系。这也是为什么我们把很多的数据库称为关系型数据库的一个根本原因。FOREIGN KEY，在mysql当中，如果是要去创建外键约束，需要有一些前置的要求。
创建FOREIGN KEY之前需要满足：

父表和子表必须使用相同的存储引擎InnoDB，而且禁止使用临时表；（具有外键列的表我们称之为子表，而子表所参照的表我们称之为父表）
数据表的存储引擎只能为InnoDB;
外键列和参照列必须具有相似的数据类型（外键列就是我们加载了foreign key的列，而参照列就是外键参照的列），其中如果是数字的话，数字的长度以及是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，MYSQL将自动创建索引。如果（父表）字段没有索引，创建外键会失败。父表关联字段必须显式创建索引（单字段索引或位于最左位置的组合索引）；子表外键字段也是索引字段（如果不是索引字段建立外键关系的时候MySQL会隐式地为该字段创建一个普通索引）。即外键相关字段最后一定会是索引字段。

MYSQL配置文件，my.ini配置文本中设置存储引擎： default-storage-engine=INNODB
如果参照的列是主键，那么mysql会自动为其创建索引，所以以主键为参照列时索引要求就是满足的。下面是创建外键约束的例子

CREATE TABLE a1（
id smallInt（5） unsigned not null auto_increment，
pname varchar（20） not null
）;
CREATE TABLE a2（
id smallInt（5） unsigned not null auto_increment，
username varchar（10） not null，
pid smallInt(5) unsigned，//注意unsigned符号位也不能有
FOREIGN KEYp（pid） references a1（id））;

此时有外键的子表 a2，a1则称之为父表。外键列是pid，参照列是父表的id，在数字的情况下必须相同。除此之外外键列必须创建索引。那么父表主键id列是否会自动创建索引呢？主键在其被创建的同时会自动创建索引。
查看索引位置 show indexes from a1\G \G加上它会以网格的形式呈现
外键约束的参照操作：

CASCADE: cascade从父表删除或更新且自动删除或更新子表中匹配的行;
SET NULL:set null 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证字表列没有指定NOT NULL
RESTRICT:restrict，拒绝对父表的删除或更新操作
NO ACTION:no action标准SQL的关键字，在MYSQL中与restrict相同，拒绝对父表的删除或更新操作

在进行了外键约束以后，父表更新子表是否也进行相应的操作，做个例子展示下，在创建表时，最后一句加外键的时候设置删除更新操作:
FOREIGN KEYp（pid） references a1（id）） ON DELETE CASCADE；
如果要插入记录，必须先在父表中插入记录，才能在子表中插入记录，原因很简单，因为子表参照的是父表中的信息，如果父表中不存在信息，子表也就无法参照，必须先在a1表中插入记录。
删除a1中的某条记录 DELETE from a1 where id =3
在实际的开发当中很少使用物理的外键约束，而多的使用逻辑上的外键约束，因为物理的外键约束只有innoDB这种引擎支持，像我们的另外的一种引擎myisam的引擎则不支持，反过来说如果我想创建的数据表假设存储引擎myisam，又想使用外键约束的话，其实是不可能实现的。所以我们经常在实际的开发中我们不去定义物理的外键，所谓的逻辑外键指的就是我们在定义两张表的结构的时候，我们是按照存在的某种结构的方式去定义，但是不去使用FOREIGN KEY 这个关键字去定义。

Mysql表级约束与列级约束的两点区别：

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

在实际的开发中列级约束用的多，表级约束用的少。向notnull，default只存在列级约束，不存在表级约束。而像其他的三种，主键唯一外键，他们都可以存在表级和列级约束。另外有些资料中也有一个检测约束，这个是不起作用的，所以在这儿就不讲了。

修改数据表的操作：

添加单列：
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST |AFTER col_name]
ADD后面这个column是列的单词，可以省略。之后写列的名称定义
FIRST这些不写代表所添加的这一列将在所有列的下方，也就是在最后面补一列。而FIRST，单独写则代表再最上面一列，after name，则是在后面这个名字的列下方补一列。
添加多列:
ALTER TABLE tbl_name ADD [column] (col_name column_definition,....)
添加单列的时候不需要加小括号，添加单列的时候可以指定位置关系。但是多列需要括号，而且不能指定位置，只能在原来数据表列的下方
删除单列的操作：
`ALTER TABLE tbl_name DROP [COLUMN] col_name
删除多列:
ALTER TABLE a3 DORP truename,DORP password
关于约束,添加主键约束:
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name)
添加唯一约束：
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX| KEY] [index_name] [index_type](index_col_name，...)
添加外键约束：
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name](index_col_name) reference_definition
添加删除默认约束：
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
删除主键约束：
ALTER TABLE tbl_name DORP PRIMARY KEY
删除唯一约束：
`ALTER TABLE tbl_name DORP {INDEX|KEY} index_name
查看数据表中的唯一约束：
Show INDEXES FROM tbl_name\G
删除外键约束：
ALTER TABLE tbl_name DORP FOREIGN KEY fk_symbol
修改列定义：
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
ALTER TABLE 表名 MODIFY 列名数据类型其他属性，如果更改属性由大类型更改到小类型可能会造成数据的溢出。下面是一个修改列位置的例子：ALTER TABLE users2 MODIFY id SMALLINT UNSIDNED FIRST;
修改列名称列定义：
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; //修改列名称
change column除了可以改列定义还可以更改列名称.
修改数据表名字的两种方法（注意上一条是修改列名称）：
方法1：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
例子1：ALTER TABLE users2 RENAME (to|as) users3;
方法2：RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
例子2：RENAME TABLE users5 TO users2;
查看数据表 SHOW TABLES;
这里注意：尽量少使用数据列的更名及数据表的更名，如果之前有引用，更名后可能导致某些存储过程失效。所以不要去随意的更改数据列以及数据表的名字。

秒客网

(3)Mysql约束以及修改数据表

相关文章