MySQL数据库学习之约束

第六章

第一节 约束

            --非空约束	 not null
		mysql> drop table if exists test;
		mysql> create table test(
			-> id int(10),
			-> name varchar(10) not null,
			-> tel varchar(20)
			-> );
		mysql> desc test;
		+-------+-------------+------+-----+---------+-------+
		| Field | Type        | Null | Key | Default | Extra |
		+-------+-------------+------+-----+---------+-------+
		| id    | int(10)     | YES  |     | NULL    |       |
		| name  | varchar(10) | NO   |     | NULL    |       |
		| tel   | varchar(20) | YES  |     | NULL    |       |
		+-------+-------------+------+-----+---------+-------+
	--唯一性约束 unique
		*列级约束
			... unique
			mysql> drop table if exists test;
			mysql> create table test(
				-> id int(10),
				-> name varchar(10) unique,
				-> tel varchar(20)
				-> );
			mysql> desc test;
			+-------+-------------+------+-----+---------+-------+
			| Field | Type        | Null | Key | Default | Extra |
			+-------+-------------+------+-----+---------+-------+
			| id    | int(10)     | YES  |     | NULL    |       |
			| name  | varchar(10) | YES  | UNI | NULL    |       |
			| tel   | varchar(20) | YES  |     | NULL    |       |
			+-------+-------------+------+-----+---------+-------+
		*表级约束
			unique(...,...)联合起来的字段不能重复
			mysql> drop table if exists test;
			mysql> create table test(
				-> id int(10),
				-> name varchar(10),
				-> tel varchar(20),primary key(id,name)
				-> );
			mysql> desc test;
			+-------+-------------+------+-----+---------+-------+
			| Field | Type        | Null | Key | Default | Extra |
			+-------+-------------+------+-----+---------+-------+
			| id    | int(10)     | NO   | PRI | NULL    |       |
			| name  | varchar(10) | NO   | PRI | NULL    |       |
			| tel   | varchar(20) | YES  |     | NULL    |       |
			+-------+-------------+------+-----+---------+-------+
			
	--给约束添加名字
		*constraint .... unique(...,...)TABLE_CONSTRAINTS
	--主键约束	primaty key
		*给多个字段添加主键约束，称为复合主键
			primary key(
		*一张表主键约束只能有一个
		*自然主键和业务主键
			主键值若是一个自然数，这个自然数和业务没有什么关系
	--外键约束	foreign key
	--检查约束【MySQL不支持，Oracle支持】

--外键约束
--外键字段
--外键值
--某个字段或某些字段添加外键约束之后，该字段称为外键字段
--一张表可以有多个外键约束

分析场景：
请设计数据库来存储学生和班级信息，要求给出两种设计方案
方案一，学生信息和班级信息存储在一张表
sno(pk)		sname 		cno 		cname
--------------------------------------------------------
1 			jack 		1001		湖南省耒阳市一中1班
2 			hellen 		1001		湖南省耒阳市一中1班
3 			mark 		1002		湖南省耒阳市一中2班
4 			zhangsan    1002		湖南省耒阳市一中2班

以上设计缺点：数据多余
方案二，学生信息和班级信息分别存储到一张表上
学生信息表
sno(pk)		sname		sex		cno(fk)
1 			jack 		m		1001
1 			hellen 		w		1001
1 			mark 		m		1002
1 			zhangsan 	m		1002
班级信息表
cno(pk)		cname
1001		湖南省耒阳市一中1班
1002		湖南省耒阳市一中2班



外键值可以为空
外键字段应用另外一张表的字段时候，被引用的字段必须具有unique约束

有了外键引用之后，表为父表和子表，引用外键字段的表被称为子表，被引用的表称为父表，创建表是应该
先创建父表再创建子表，在表中插入数据时，应该在父表中先插入数据，然后在子表中插入数据，删除表时，
应该先删除子表



--级联更新
	*on update cascade
	alter table tstudent add constraint tstudent_cno_fk foreign key(cno) references tclass(cno) on update cascade
--级联删除
	*on delete cascade
	alter table tstudent add constraint tstudent_cno_fk foreign key(cno) references tclass(cno) on delete cascade
--添加和删除一个约束
	*如果给约束命名的话(在创建完test表之后添加外键指向test_2表)
	alter table test add constraint test_no_fk foreign key(no) references test_2(no);
	删除如下：
	aalter table test drop foreign key test_no_fk;
	
	*如果没有给约束命名的话(在创建完test表之后添加外键指向test_2表)
	alter table test add foreign key(no) references test_2(no)
	则系统会自动给该约束命名，通过以下语句可以看到
	show create table test;
	可以看到constraint后面系统给约束命名的名字，向前面一样删除即可

--case when then when then else end
select
	eno,sal,(case deptno when 10 then sal*1.5 when 20 then sal*2 else sal ) newsal
from emp;