mysql中约束的划分，添加和删除各种字段约束和对数据表的更名

net start sql
mysql -uroot -proot(自己电脑中MySQL密码是acmer)
USE TEST

3-1 回顾
数据类型：整型，浮点型，字符型，日期时间型
数据表操作：如何创建数据表 PIRMARY KEY(主键约束) UNIQUE KEY(唯一约束) DEFAULT(默认约束) NOT NULL(非空约 束)

记录的插入INSERT tb5(username,age) VALUES('TOM',22);  

查找的操作SHOW COLUMNS FROM tb4；

接下来这一章的小目录
         约束  按功能划为：NOT NULL(非空约束),PRIMARY KEY(主键约束),UNQUE KEY(唯一约束),DEFAULT(默认约束),FOREIGN KEY(外键约束)

                   按数据列的数目划分为：表级约束（2个和2个以上），列级约束（某1个字段）  

                   默认约束和非空约束只存在列级约束

          修改数据表：针对字段的操作：添加或者删除字段、修改列定义，修改列名称等
          针对约束的操作：添加或者删除各种约束
         针对数据表的操作：数据表更名（两种方式）



3-2约束
 1.约束保证数据的完整性和一致性
 2.约束分为表级约束和列级约束
 3.约束类型包括：NOT NULL(非空约束)  PRIMARY KEY(主键约束)  UNIQUE KEY(唯一约束)  DEFAULT(默认约束) 

FOREIGN KEY(外键约束)
FOREIGN KEY(外键约束)  ：保持数据一致性，完整性  实现一对一一对多关系。

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

mysql配置文件  default-storage-engine=INNODB

mysql-uroot -proot
USER TEST
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 SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id));
看id和pid是否已经创建了索引 SHOW INDEXES FROM provinces;
SHOW INDEXES FROM provinces\G;以网格的形式呈现
SHOW COLUMNS FROM users\G;外键列上有索引
SHOW CREATE TABLE users；

3-3外键约束的参照操作

1.CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。
2.SET NULL:从父表删除或更新行，并设置子表的外键列为NULL.如果使用该选项，必须保证子表列没有指定NOT NULL。
3.RESTRICT:拒绝对父表的删除和更新操作。
4.NO ACTION:标准sql的关键字，在mysql中与RESTRICT相同。
CREATE TABLE users1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(10) NOT NULL,pid 

SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE);
SHOW CREATE TABLE users1;
在父表中插入信息
INSERT provinces(pname) VALUES('A');
INSERT provinces(pname) VALUES('B');
INSERT provinces(pname) VALUES('C');
SELECT * FROM provinces；
INSERT users1(username,pid) VALUES('TOM',3);
INSERT users1(username,pid) VALUES('JOHN',7);系统提示错误，因为父表中并不存在这个id，因为附表中只有3个，可以将7改成1
即INSERT users1(username,pid) VALUES('JOHN',1);
INSERT users1(username,pid) VALUES('ROSE',3);
SELECT * FROM users1；
删除provinces中的第三条数据，使用DELETE命令
DELETE FROM provinces WHERE id=3；敲回车显示一行受影响
再输入SELECT * FROM provinces;可以看到第三行已经没了
再来查看子类表users中TOM ROSE记录是否存在，可以看到TOM和ROSE已经不存在了
可以知道删除父表时子类的表也被删除了
同样更新的操作也会引起同样的变化

很少使用物理的外键约束，多使用逻辑外键约束


3-4表级约束和列级约束（用的较多）
1.对一个数据列建立的约束，称为列级约束。
2.对多个数据列建立的约束，称为表级约束。
3.列级约束既可以在列定义时声明，也可以在列定义后声明。
4.表级约束只能在列定义后声明。
NOT NULL DETAULT 这两种约束不存在表级约束，只存在列级约束


3-5修改数据表--添加和删除列

添加单列   ALTER TABLE tb1_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
先看看users1的结构
SHOW COLUMNS FROM users1;
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;在最后面插入了一列
SHOW COLUMNS FROM users1;
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username；在username下面插入了password这一列
SHOW COLUMNS FROM users1;
ALTER TABLE users1 ADD truename VARCHAR(32) NOT NULL FIRST；位于第一行
SHOW COLUMNS FROM users1;

删除列
ALTER TABLE users1 DROP truename；删除truename这一列
SHOW COLUMNS FROM users1;查看数据表的结构
ALTER TABLE users1 DROP password，DROP age；删除两列

3-6修改数据表--添加约束

添加主键约束（只能有一个）
ALTER TABLE tb_1name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,```)
先创建没有用过的users2表
CREATE TABLE users2（username VARCHAR(10) NOT NULL,pid SMALLINT UNSIGNED）;
SHOW CREATE TABLE users2;可以发现这张表没有主键来约束，增加个主键
ALTER TABLE users ADD id SMALLINT UNSIGNED;新增加一行
SHOW COLUMNS FROM users2；
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id); 把id段设置为主键
SHOW COLUMNS FROM users2；可以看到id是主键

添加唯一约束（可以有多个）
这里假设username添加唯一约束
ALTER TABLE users2 ADD UNIQUE(username)；
SHOW CREATE TABLE users2;可以发现id是主键，username是唯一约束

添加外键约束，这里users的数据结构和users1基本上是完全相同的，当然除了顺序以外，现在如果仍然想要users2中的pid去参照provinces表中的id的话，就需要为users2添加一个外键约束

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces（id）；
SHOW CREATE TABLE users2;
可以看到users2中已经添加了外键约束




添加或者删除默认约束
ALTER TABLE tb1_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL; 添加一个age字段
SHOW COLUMNS FROM users2；查看数据表的结构，可以看到默认值没有，现在想赋予它默认值15，就可添加默认值
ALTER TABLE users2 ALTER age SET DEFAULT 15;
SHOW COLUMNS FROM users2；再查看数据表的结构，可以发现age的默认值是15
当然也可以删除掉默认值
ALTER TABLE users2 ALTER age DROP DEFAULT；
SHOW COLUMNS FROM users2；再来查看数据表的结构，删除了age中的默认值15


3-7删除约束


删除主键约束，上面的id是主键，假设不想要主键约束，可以这样写
ALTER TABLE users2 DROP PRIMARY KEY;这里删除主键不需要去指定名字id，因为任何一个数据表只有一个主键
SHOW COLUMNS FROM users2；


删除唯一约束，因为一张表可以有多个唯一约束，所以删除时得加上名字
首先先查看约束的名字，怎样查看呢 SHOW INDEXES FROM users2；
SHOW INDEXES FROM users2\G；这里表示以网格的形式来查看，可以看到在username上创建了一个username的索引KEY指的就是索引
ALTER TABLE users2 DROP INDEX username；注意这里删除的是约束并不是删除的是字段。
SHOW COLUMNS FROM users2；可以发现字段仍然存在，但是约束已经不存在了
SHOW INDEXES FROM users2；可以发现刚才有两个约束现在只剩下了一个约束了

删除外键约束
先查看外键约束的名称 SHOW CREATE TABLE users2；可以发现这个名字是系统赋予我们的，称为users2_ibfk_1
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
SHOW CREATE TABLE users2;可以看见外键的索引已经被删除了

3-8修改数据表 修改列定义和更改数
把id移到最上面
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;也就把id移到了最前面
SHOW COLUMNS FROM users2；可以发现id字段已经位于了所有字段的前面


修改列定义：id是SMALLINT类型的，想把它改成TINYINT形式的
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2；查看数据表的结构可以看到类型SMALLINT被修改成了TINYINT,注意由大类型改到小类型时有可能会造成数据的丢失。     在UNSIGNED下SMALLINT的范围是0到65535，TINYINT的范围是0到255，由SMALLINT到TINYINT可能会造成数据的丢失

修改列名称：
修改列定义用了MODIFY关键字，修改列名称用了CHANGE关键字，CHANGE关键字除了可以修改列名称外还可以修改列定义，功能更强大。比如现在准备将pid字段类型修改名称也做修改，类型的话把他改成TINYINT,名字的话改成p_id
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2;可以发现名称id被改成了p_id,类型SMALLINT也被修改TINYINT

数据表更名
方法1
ALTER TABLE tb1_name RENAME [TO|AS] new_tbl_name;
方法2
RENAME TABLE tb1_name TO new_tb1_name [,tb1_name2 TO new_tbl_name2]```


这里把表格名称users2给改成了users3
ALTER TABLE users2 RENAME users3；
SHOW TABLES;查看当前数据库下的所有的表，可以发现有users3而没有了users2
RENAME TABLE users3 TO users2；
SHOW TABLES;可以发现users3没有了，而有了users2，因为改名了建议不要去随意的修改表名和列名，因为一改名索引就对不上了

知识点总结：
约束  按功能划为：NOT NULL(非空约束),PRIMARY KEY(主键约束),UNQUE KEY(唯一约束),DEFAULT(默认约束),FOREIGN KEY(外键约束)
       按数据列的数目划分为：表级约束，列级约束
       修改数据表：针对字段的操作：添加或者删除字段、修改列定义，修改列名称等
       针对约束的操作：添加或者删除各种约束
       针对数据表的操作：数据表更名（两种方式）


3-9 小节

约束  

     按功能划为：NOT NULL(非空约束),PRIMARY KEY(主键约束),UNQUE KEY(唯一约束),DEFAULT(默认约束),FOREIGN KEY(外键约束)

    按数据列的数目划分为：表级约束，列级约束
     修改数据表：针对字段的操作：添加或者删除字段、修改列定义，修改列名称等
    针对约束的操作：添加或者删除各种约束
    针对数据表的操作：数据表更名（两种方式）