mysql约束和索引

• 约束

1、作用
	保证数据的完整性、一致性、有效性
2、约束分类
	1、默认约束（default）
		插入记录，不给该字段赋值，则使用默认值
	1、非空约束（not NULL）
		不允许该字段的值有NULL记录
3、示例：
	create table t2(
	id int not null,
	name varchar(15),
	sex enum("M","F","S") default "S"
	)				#创建完使用desc查询

• 索引

1、定义
	对数据库表的一列或多列的值进行排序的一种结构（Btree方式）
2、优点
	加快数据检索速度
3、缺点
	1、需要占用物理存储空间
	2、当对表中数据更新时，索引需要动态维护，降低数据维护速度
4、索引示例
	1、开启运行时间检测：set profiling = 1
	2、执行查询语句
		select name from t1 where name = "lucy99999"
	3、查询执行时间
		show profiles;
	4、在name字段创建索引
		create index name on t1(name);
	5、关闭profiling
		set profiling = 0;

• 分类
• 普通索引(index)

1、使用规则
	1、可设置多个字段
	2、字段值无约束
	3、key标志：MUL
2、创建index
	1、创建表时
		create table 表名 (...
			index(字段名),index(字段名));
	2、已有表
		create index 索引名 on 表名(字段名);
		eg:create index name on t1(name);
	3、查看索引
		1、desc 表名;			   -->KEY标志为：MUL
		2、show index from 表名\G;	(KEY值为索引名)
	4、删除索引
		1、drop index 索引名 on 表名;
		eg:drop

• 唯一索引(unique)

1、使用规则
	1、可设置多个字段
	2、约束：字段值不允许重复，但可为NULL
	3、KEY标志：UNI
2、创建
	1、创建表时创建
		create table 表名(
		....
		unique(字段名),unique(字段名)...);
	2、已有表
		create unique index 索引名 on 表名(字段名);
	3、查看、删除同普通索引
		show index from t1\G;  #若是唯一索引Non_unique为0
		drop index 索引名 on 表名;

• 主键索引(primary key)
  自增长属性(auto_increment,配合主键一起使用)
  作用：能够唯一的标识一条字段

1、使用规则
	1、只能有一个主键字段
	2、约束：不允许重复，且不能为NULL
	3、KEY标志：PRI
	4、通常设置记录编号字段id，能唯一锁定一条记录
2、创建主键
	1、创建表时
		create table t1(
		id int primary key auto_increment,
		name varchar(15)
		) auto_increment = n;		#设置自增长起始值为n
		
		已有表添加自增长属性：
		alter table 表名 modify id int auto_increment;
		
		已有表重新指定起始值：
		alter table 表名 auto_increment = n;
		注意：n必须大于当前表内最大主键值，否则按原顺序插入
	2、已有表添加主键(但要保证id字段无空值无重复)
		alter table 表名 add primary key(id);
	3、查看: show index from 表名\G;
	4、删除
		1、先删除自增长属性（modify）
			alter table 表名 modify id int;	#id为主键
		2、删除主键索引
			alter table 表名 drop primary key;

eg:主键为空插入时，自增长属性会自动递增
注意：但若删除该记录重新插入时会在原来的基础上继续递增

• 外键索引(foreign key)

1、定义：
	让当前表字段的值在另一个表的范围内选择
2、语法：
	foreign key(参考字段名)
		references 主表(被参考字段名)
		on delete 级联动作
		on update 级联动作
3、使用规则
	1、主表、从表字段数据类型一致
	2、主表被参考字段：主键
4、示例：
	表1、缴费信息表（财务）
	id		姓名		班级		缴费金额
	1		唐伯虎		AID06		300
	2		点秋香		AID06		260
	表2、学生信息表（班主任）
	id		姓名		缴费金额

	create table jftab(
		id int primary key,
		name varchar(15),
		class char(6),
		money int);
	
	create table bjtab(
		stu_id int,
		name varchar(15),
		money int,
		foreign key(stu_id) references jftab(id)
		on delete cascade
		on update cascade
		);
5、删除外键
	alter table 表名 drop foreign key 外键名;
	1、查询外键名：show create table 表名;
	2、删除：alter table bjtab drop foreign key 
				bjtab_ibfk_1;
6、级联动作
	1、cascade 级联更新
		数据级联删除、更新（参考字段）
	2、restrict(默认)
		从表有相关联记录，不允许主表操作
	3、set null
		主表删除、更新，从表相关联记录字段值为NULL
7、已有表添加外键
	alter table 表名 add
		foreign key(被参考字段名) references 主表(参考字段)
		on delete ...
		on update ...

注意：当从表某个字段的外键参照同一个主表字段的不同级联属性，只会表现一种（这里是set null）