Java基础27--Mysql-约束与索引

Java基础27–Mysql–约束与索引

关系型数据库设计规则

• 遵循ER模型和三范式 （Java中有时也叫O-R，Object，对象关系）

  • E entity 代表实体的意思 对应到数据库当中的一张表
  • R relationship 代表关系的意思

• 三范式：1、列不能拆分（越细冗余程度越低） 2、唯一标识 （表中不能出现两条一摸一样的记录） 3、关系引用主键（多个表之间引用）

• 具体体现

  • 将数据放到表中，表再放到库中。
  • 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
  • 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。
  • 表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名。每个字段类似java 或者python中的“实例属性”。
  • 表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。
    

约束与索引概念与区别

1、数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2、根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束
• Not NULL约束：非空约束
• Check约束：检查约束
• Default约束：默认值约束
• 自增约束

3、约束（CONSTRAINTS）与索引（INDEX）

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限在当前数据库，约束可以被当做数据库对象来处理，它们具有名称和关联模式。是逻辑约束，不会因为设置约束而额外占用空间。

索引是一个单独、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单（类似于新华字典的目录索引页）。索引的存在会增加数据库的存储空间，也会使插入、修改数据的时间开销变多(因为插入和修改数据时，索引也要随之变动），但是可以大大提高查询速度。因此应该在关键列、或其他经常要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列，值很少的列（例如性别只有男和女）等列上不应该创建索引。

会将两个一起谈的原因：因为mysql会经常将键的约束和索引放到一起处理

Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。（建立约束的同时建立索引）

其中主键删除，对应的索引也会删除

删除唯一键的方式是通过删除对应的索引来实现的

删除外键，外键列上的索引还在，如果需要删除，需要单独删除索引

约束是为了约束数据的完整性问题，约束与速度没什么关系，索引是为了提高查询速度

主键约束

1、主键约束
特点：
（1）一个表有且只能有一个主键约束
（2）主键约束意味着，唯一并且非空
（3）主键约束名PRIMARY
（4）创建主键会自动创建对应的索引，同样删除主键对应的索引也会删除

2、在建表的时候如何指定主键约束
create table 【数据库名.】表名称(
字段名1 数据类型 primary key,
字段名2 数据类型，
。。。。
);

例如：

create table test.t_stu(
	sid int primary key,
	sname varchar(20),
	gender char
);

mysql> desc t_stu;
±-------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±------+
| sid | int(11) | NO | PRI | NULL | |
| sname | varchar(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
±-------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

insert into t_stu values(1,'张三','男'),(1,'李四','女');#错误的,主键唯一

mysql> insert into t_stu values(1,'张三','男'),(1,'李四','女');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  违反了主键约束

insert into t_stu values(1,'张三','男'),(2,'李四','女');

3、建表后如何指定主键约束
alter table 【数据库名.】表名称 add primary key(字段列表);

create table test.t_stu(
	sid int,
	sname varchar(20),
	gender char
);
alter table test.t_stu add primary key(sid);

4、主键约束分为两种：
（1）单列主键约束
（2）复合主键约束

建表时如何指定复合主键约束：

create table 【数据库名.】表名称(
	字段名1 数据类型,
	字段名2 数据类型，
	。。。。,
	primary key(字段列表)//如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
);

建表后如何指定复合主键约束：

alter table 【数据库名.】表名称 add primary key(字段列表);

create table t_course(
	cid int primary key,
	cname varchar(20)
);

#现在要求sid和cid的组合是主键，是唯一的
create table xuanke(
	sid int,
	cid int,
	score int,
	primary key(sid,cid)
);

5、删除主键约束

alter table 【数据库名.】表名称 drop primary key;

唯一键约束

1、唯一键约束
特点：
（1）一个表可以有多个唯一键约束
（2）唯一键约束意味着，唯一，可以为NULL
（3）唯一键的约束名可以自己指定，也可以默认，
如果默认的话，一般如果单列唯一，默认就是列名，
如果是多列组合唯一，那么默认是该组合第1列的名称。
（4）创建唯一键约束，也会在对应列上建立索引。
而且删除唯一键约束的方式是通过删除对应索引来实现的。

2、唯一键约束也是分为两种
（1）单列的唯一键
（2）多列组合的唯一键

3、建表时如何指定唯一键

create table 【数据库名.】表名称(
	字段名1 数据类型  primary key,
	字段名2 数据类型 unique key，// #只适用于单列的唯一键
	。。。。
);

create table 【数据库名.】表名称(
	字段名1 数据类型  primary key,
	字段名2 数据类型,
	。。。。,
	unique key(字段列) #既适用于单列，也适用于多列
);

create table books(
	bid int primary key,
	bname varchar(20) unique key,
	price double
);

insert into books values
(1,'《从入门到放弃》',100),
(2,'《从入门到放弃》',88);
//报错
//ERROR 1062 (23000): Duplicate entry '《从入门到放弃》' for key 'bname' 

insert into books values
(1,'《从入门到放弃》',100),
(2,'《从入门到成功》',88);

4、建表后如何指定唯一键约束

alter table 【数据库名.】表名称 add unique key(字段列表);

create table books(
	bid int primary key,
	bname varchar(20) ,
	price double
);
alter table books add unique key(bname);

5、删除唯一键约束

alter table 【数据库名.】表名称 drop index 索引名;

alter table books drop index bname;

6、如何查看某个表格的索引名

show index from 【数据库名.】表名称;

show index from books;

非空约束

1、非空约束
特点：
（1）一个表可以有很多的非空约束
（2）非空约束只能针对某一个字段来说
（3）非空约束意味着，该字段不能存入null值

2、如何在建表时指定非空约束呢

create table 【数据库名.】表名称(
	字段名1 数据类型  primary key,
	字段名2 数据类型 【unique key】 not null,
	字段名3 数据类型 not null，
	。。。。
);

例如：
create table test.t_stu(
	sid int primary key,
	sname varchar(20) not null,
	gender char
);
insert into t_stu values(1,'张三','男');

//insert into t_stu values(2,null,'男');//错误的
//ERROR 1048 (23000): Column 'sname' cannot be null

insert into t_stu values(2,'李四',null);

3、建表后如何指定某个字段非空呢？

alter table 【数据库名.】表名称 modify 字段名 数据类型 not null;

create table test.t_stu(
	sid int primary key,
	sname varchar(20),
	gender char
);
alter table t_stu modify sname varchar(20) not null;

4、建表后，取消某个字段的非空约束

alter table 【数据库名.】表名称 modify 字段名 数据类型;

alter table t_stu modify sname varchar(20);

默认值约束

1、默认值约束
特点：
（1）一个表可以有很多的默认值约束
（2）默认值约束只能针对某一个字段来说
（3）默认值约束意味着，该字段如果没有手动赋值，会按默认值处理

2、如何在建表时指定默认值约束？

```c
create table 【数据库名.】表名称(
	字段名1 数据类型  primary key,
	字段名2 数据类型 【unique key】【not null】 default 默认值,
	字段名3 数据类型 default 默认值，
	。。。。
);

```c
```c
例如：
create table test.t_stu(
	sid int primary key,
	sname varchar(20) not null,
	gender char default '男'
);

insert into t_stu values(1,'张三');
ERROR 1136 (21S01): Column count doesn't match value count at row 1 '// 列数与值数量不匹配，不能不写

//有两种写法
insert into t_stu values(1,'张三',default);
insert into t_stu(sid,sname) values(2,'李四');

3、建表后如何指定某个字段有默认值呢？

alter table 【数据库名.】表名称 modify 字段名 数据类型 default 默认值;

create table test.t_stu(
	sid int primary key,
	sname varchar(20) not null,
	gender char 
);
alter table t_stu modify gender char default '男';

提醒：如果某个字段既要非空，又要默认值，那么alter 语句后面两个都要写。
create table test.t_stu(
	sid int primary key,
	sname varchar(20) not null,
	gender char not null
);
//增加gender还有默认值，not null 不能取消
alter table t_stu modify gender char default '男' not null;

4、如何取消某个字段的默认值约束

//将default 默认值 去掉就行
alter table 【数据库名.】表名称 modify 字段名 数据类型 【not null】; //#不写默认值约束

insert into t_stu values (1,'张三','女');
update t_stu set gender = default where sid = 1;
//修改为默认值

检查约束

1、检查约束：check
但是mysql暂不支持
在oracle或sql server中使用

create table t_stu(
	sid int primary key,
	sname varchar(20),
	gender char check('男' or '女')
);//性别只能男或者女

insert into t_stu values(1,'张三','男');
insert into t_stu values(2,'李四','妖');//在mysql中能添加进去，因为check约束在mysql中不起作用，在oracle或sql server中起作用

自增约束

1、自增约束
特点：
（1）一个表只能有一个自增约束
因为一个表只有一个维护自增值的变量。
（2）自增约束的列只能是整数列
（3）自增约束的列必须是键列（主键，唯一键，外键），
实际中一般是主键自增最多

2、如何在建表时指定某个列自增

create table 【数据库名.】表名称(
	字段名1 xxInt  primary key auto_increment,
	字段名2 数据类型 【unique key】【not null】 default 默认值,
	字段名3 数据类型 default 默认值，
	。。。。
);

create table t_stu(
	sid int auto_increment, #错误 
	sname varchar(20)
);
ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

//正确写法
create table t_stu(
	sid int primary key auto_increment,
	sname varchar(20)
);

mysql> desc t_stu;
±------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±---------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
±------±------------±-----±----±--------±---------------+
2 rows in set (0.01 sec)

3、如何给自增列添加数据呢
自增列，如果指定值，就按照指定的来，如果没有指定值就自增。
如果指定的值是0或null，就按照自增的来。

insert into t_stu values('张三');//#错误的，列不匹配
ERROR 1136 (21S01): Column count doesn't match value count at row 1' 

insert into t_stu(sname) values('张三');  //#sid=1
insert into t_stu(sname) values('李四'); // #sid=2

//若是手动写入非0或null值，按写入的值处理
insert into t_stu values(5,'王五');     // #sid=5
insert into t_stu(sname) values('赵六');  //#sid=6
insert into t_stu values(0,'钱七');    // #sid=7
insert into t_stu values(null,'王八');  // #sid = 8
insert into t_stu values(-1,'负数'); 	//#sid=-1
insert into t_stu(sname) values('测试');	//#sid=9，正常了
insert into t_stu values(3,'333');        // #sid=3
insert into t_stu(sname) values('测试顶顶顶顶');//#sid=10

建议：正常使用时，不要乱插

4、建表后增加自增

alter table 【数据库名.】表名称 modify 字段名 数据类型 auto_increment;

5、如何取消自增

alter table 【数据库名.】表名称 modify 字段名 数据类型;

外键约束

1、外键约束
特点：
（1）一个表可以有很多个外键约束
（2）外键约束是需要一个表的两个字段或两个表的两个字段之间建立外键约束
（3）外键约束一定是在从表/子表中建立的
（4）在从表中外键约束的列，与在主表中外键约束参考的列，这两个列的名称可以不同，
但是意义、数据类型必须一致。
（5）外键约束是同时约束双方的行为的
对于主表来说：修改和删除就受约束了
对于从表来说：添加和修改就受约束了
（6）主表被参考的字段/列必须是键列

建表时，先建主表，再建从表。
删表时，先删从表，再删主表。

从表、子表：参考别人的，依赖别人的
主表、父表：被参考的，被依赖的

举例：
（1）部门表和员工表
员工表中有一个字段，表示该员工所在的部门
部门表是主表
员工表是从表，说员工选择进入哪个部门
这样的话，外键建立在员工表

部门表中表示部门编号，用did表示,int类型
员工表中表示该员工所在的部门，我用编号表示，可以使用did，也可以使用deptid int类型

（2）学生表，课程表，选课表
主表：学生表和课程表
从表：选课表，这个表有两个外键，一个参考学生表，一个参考课程表

2、约束的等级（5个）

• （1）Cascade方式：级联
  主动权在主表上。
  如果主表被依赖字段修改了，从表对应的外键字段跟着修改
  如果主表被依赖字段的记录删除了，从表对应的外键字段的记录也会删除

• （2）Set null方式
  主动权在主表上。
  如果主表被依赖字段修改了，从表对应的外键字段设置为null
  如果主表被依赖字段的记录删除了，从表对应的外键字段的值设置为null
  这里要求，外键字段必须不能有非空约束。

• （3）No action方式：不作为

• （4）Restrict方式：严格

  （3）（4），如果主表的被依赖字段的值被引用了，那么主表对该字段的修改和删除就被完全限制了。就不能修改和删除。
  （3）（4）主表没有主动权。必须先处理从表对应的值，然后才能修改和删除。

• （5）Set default方式：mysql的Innodb引擎不支持。

• 
  SQLyog可视化工具里外键有个选项是空的，就是（5），但是mysql的Innodb引擎不支持。

3、如何在建表时指定外键

create table 【数据库名.】从表名称(
	字段名1 xxInt  primary key auto_increment,
	字段名2 数据类型 【unique key】【not null】 default 默认值,
	字段名3 数据类型 default 默认值，
	。。。。,
	foreign key(从表的某字段) references 主表名(被参考字段) 【on update 等级】【on delete 等级】 
);
  

例如：
create database 0513db;
use 0513db;

#主表
create table dept(
	did int ,
	dname varchar(20)
);	

#从表
create table emp(
	eid int primary key,
	ename varchar(20),
	did int,
	foreign key(did) references dept(did) on update cascade on delete set null
);

insert into emp values(1,'张三',1);//#错误的，因为主表还没有对应记录
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`did`) REFERENCES `dept` (`did`) ON DELETE SET NULL ON UPDATE CASCADE)

//正确写法
insert into dept values(1,'咨询部'),(2,'教学部')
insert into emp values(1,'张三',1);

mysql> insert into emp values(2,'李四',4);//#错误的，因为主表还没有对应记录
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`did`) REFERENCES `dept` (`did`) ON DELETE SET NULL ON UPDATE CASCADE)

//正确写法
delete from dept where did = 1;
update dept set did = 5 where did = 2;

4、建表后如何指定外键

alter table 【数据库名.】从表名称 add foreign key(从表的某字段) references 主表名(主表被引用字段) 【on update 等级】【on delete 等级】 ;

5、如何删除外键

alter table 【数据库名.】从表名称 drop foreign key 约束名;

alter table emp drop foreign key emp_ibfk_1;

6、如何查看某个表的外键约束名
是在系统库information_schema里查看表的约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

	
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';	

这个表有两个约束：主键约束和外键约束