MySQL建表约束条件及键值

约束条件
Null 允许为空
Not NULL 不允许为空
Key 索引类型
Default 设置默认值，缺省为NULL
例：

mysql> use db2; 
mysql> create table t1( 
    -> name char(10) not null, 
    -> age tinyint not null default 25, 
    -> likes set("eat","sleep","play") default "eat,sleep" 
    -> ); 
mysql> desc db2.t1; 
mysql> insert into db2.t1(name) values( 
    -> "bob" 
    -> ); 
mysql> select * from db2.t1; 

如果要修改字段为非空，该字段的值里面不能有空值null，不然会冲突

修改表结构
语法结构
基本用法：alter table 表名 执行动作
Add 添加字段
Modify 修改字段类型
Change 修改字段名
Drop 删除字段
Rename 修改表名

1.添加新字段
Alter table 表名 add 字段名 类型（宽度） 约束条件；
可以after 字段名; 或者 first;
例：

mysql> desc t1; 
mysql> alter table t1 
    -> add email varchar(30) default "stu@qqq.cn", 
    -> add tel char(11); 
mysql> select * from t1; 
mysql> alter table t1 
    -> add stu_num char(9) first; 
mysql> select * from t1;   
mysql> alter table t1 
    -> add class char(7) default "test1" after name; 
mysql> select * from t1; 

修改字段类型
Alter table 表名 modify 字段名 类型（宽度） 约束条件；
可加after 字段名; 或 first;
例：

mysql> desc t1; 
mysql> alter table t1  
    -> modify stu_num varchar(10); 
mysql> desc t1; 
mysql> alter table t1 
    -> modify age tinyint default 25; 
mysql> desc t1; 
mysql> alter table t1 modify age tinyint default 25 after name; 
mysql> desc t1; 
mysql> select * from t1; 

不能与原有表约束条件发生冲突，否则会报错
枚举类型的修改：如果已经保存的数据里包含原有的举例，则不能修改原有的例子，但是可以添加新的例子。如果修改default，不会对以保存的数据有影响
例：

mysql> desc t1; 
mysql> select * from t1; 
mysql> alter table t1 
    -> modify likes set("eat","sleep","play","it","book") null default "it,book"; 
mysql> desc t1; 
mysql> select * from t1; 

3.修改字段名
Alter table 表名 change 原字段名 新字段名 类型（宽度） 约束条件；
例：

mysql> alter table t1 
    -> change email mail varchar(30) default "stu@qqq.cn"; 
mysql> desc t1; 

当跟新类型和约束条件时，也可修改字段类型

4.删除字段
Alter table 表名 drop 字段名；
例：

mysql> alter table t1 
    -> drop mail, 
    -> drop class; 
mysql> desc t1; 
mysql> select * from t1; 

表中有多少条记录时，所有列的此字段的值都会被删除

修改表名
Alter table 表名 rename 新表名； （表对应的文件名也会改变）
例：

# ls /var/lib/mysql/ 
mysql> show tables; 
mysql> alter tabel t1 
    -> rename stutab; 
mysql> show tables; 
# ls /var/lib/mysql/ 

MySQL键值
MySQL索引：对记录集的多个字段进行排序的方法
（Btree（默认，二叉树算法）、B+tree、hash、FULLTEXT）
加快数据的检索速度
索引需要占物理空间
键值类型：INDEX（普通索引）
UNIQUE（唯一索引）
FULLTEXT（全文索引）
PRIMARY KEY（主键）
FOREIGN KEY（外键）

INDEX普通索引使用说明
一个表中可以有多个INDEX字段；字段的值允许有重复，且可以赋NULL值；经常把做查询条件的字段设置为INDEX字段；INDEX字段的KEY标志是MUL
建表的时候指定索引字段
index（字段1），index（字段2）…
在已有表中设置索引字段
Create index 索引名 on 表名（字段名）;
删除指定表的索引字段
Drop index 索引名 on 表名;
例：

mysql> create index name on t1(name); 
mysql> desc t1;                                           //已有表 
mysql> show index from t1\G; 
 
mysql> create table t2( 
    -> name char(10) not null, 
    -> age tinyint not null default 25, 
    -> likes set("eat","sleep","game","zhang")             //建表时 
    -> default "eat,sleep", 
    -> index(name),index(age) 
    -> ); 
mysql> desc t2; 
mysql> show index from t2\G; 

PRIMARY KEY（主键）
一个表中只能有一个primary key字段
对应的字段值不允许有重复，且不允许赋NULL值
如果有多个字段都作为primary key，称为复合主键，必须一起创建
主键字段的KEY标志是PRI
通常与AUTO_INCREMENT连用
经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]
建表的时候指定主键字段
Primary key（字段名）;
例（两种写法）：

mysql> create table t3( 
    -> name char(5), 
    -> class char(7), 
    -> stu_num char(9), 
    -> primary key(stu_num) 
-> ); 
mysql> desc t3;    
 
mysql> create table t3( 
    -> name char(5), 
    -> class char(7), 
    -> stu_num char(9) primary key 
    -> ); 
mysql> desc t3;    

复合主键（只有一种写法）
例：

mysql> create table t4( 
    -> clientip char(15), 
    -> serport smallint unsigned, 
    -> status enum("allow","deny"), 
    -> primary key(clientip,serport) 
    -> ); 
mysql> desc t4; 
mysql> insert into t4 values("1.1.1.1",22,"deny"); 
mysql> insert into t4 values("1.1.1.1",21,"allow"); 
mysql> insert into t4 values("1.1.1.1",21,"allow");   (x)同时重复就不可以 

在已有表中设置主键字段
Alter table 表名 add primary key（字段名）;
例：假设alter table t4 drop primary key;

mysql> alter table t4  
    -> add primary key(clientip,serport); 

移除表中主键字段
Alter table 表名 drop primary key;
例：

mysql> alter table t3 drop primary key; 
mysql> desc t3; 
复合主键中不能选择只删除某一个主键 

AUTO_INCREMENT：让字段的值自动增长+1
条件：数值类型，primary key
例：

mysql> create table t5( 
    -> id int primary key auto_increment, 
    -> name char(10), 
    -> age tinyint 
    -> ); 
mysql> desc t5; 
mysql> insert into t5(name,age) values("bob",19); 
mysql> insert into t5(name,age) values("tom",19); 
mysql> insert into t5(name,age) values("lucy",19); 
mysql> select * from t5; 

删除包含自增长的主键：

mysql> alter table t5  
    -> modify id int not null; 
mysql> desc t5; 
mysql> alter table t5 drop primary key; 
mysql> desc t5; 

Foreign key（外键）
让当前表字段的值在另一个表中字段值的范围内选择
条件：表的存储引擎必须是innodb
字段类型要一致
被参照的字段必须是索引类型的一种（primary key）
基本用法
Foreign key(表A的字段名)
Reference(表B字段名)
On update cascade //同步更新
On delete cascade //同步删除
例：
创建外键

mysql> create table yg( 
    -> yg_id int primary key auto_increment, 
    -> name char(10) 
mysql> insert table yg(name) values("bob"); 
mysql> insert into yg(name) values("tom"); 
mysql> select * from yg; 
mysql> create table gz( 
    -> gz_id int, 
    -> pay float(7,2), 
    -> foreign key(gz_id) references yg(yg_id) 
    -> on update cascade on delete cascade 
    -> )engine=innodb; 
mysql> desc gz; 
mysql> show create table gz\G; 

测试外键

mysql> insert into gz values(1,45000); 
mysql> insert into gz values(2,15000); 
mysql> select * from gz; 
mysql> insert into gz values(3,25000);            //(x)报错 
mysql> insert into yg(name) values("jack"); 
mysql> select * from yg; 
mysql> insert into gz values(3,25000); 
mysql> select * from gz; 

同步更新、同步删除

mysql> delete from yg where yg_id=1; 
mysql> select * from yg; 
mysql> select * from gz; 
mysql> update yg set yg_id=8 where yg_id=2; 
mysql> select * from yg; 
mysql> select * from gz; 
 
mysql> select * from gz; 
mysql> insert into gz values(8,30000); 
mysql> insert into gz values(8,30000); 
mysql> insert into gz values(8,30000); 
mysql> select * from gz; 
mysql> delete from gz; 
mysql> select * from gz; 
mysql> alter table gz  
    -> add primary key(gz_id); 
mysql> desc gz; 
mysql> insert into gz values(8,30000); 
mysql> insert into gz values(8,30000);     //重复的不能写，空的不能写，yg表没有的不能写 

删除外键字段

Alter table 表名 drop foreign key 约束名; 
mysql> show create table gz; 
mysql> alter table gz drop foreign key gz_ibfk_1; 
mysql> show create table gz; 
外键约束名称在FOREIGN KEY前面