一、数据库的约束
mysql提供的约束
1.not null
指示某列不能存储null值。
2.nuique
保证某列的每行必须有唯一的值。
3.default
规定没有给列赋值时的默认值。
4.primary key
not null和 unique 的结合。确保某列(或两个列多个列的结合)有唯一标识,
5.foreign key
保证一个表中的数据匹配另一个表中的值的参照完整性。
6.check
保证列中的值符合指定的条件。对于MySQL数据库,对check子句进行分析,但是忽略check子句.
注:约束可以连写
二、约束的效果
1unique 约束
create table 表名(列名 类型 not null, 列名 类型 ...);
不使用效果
mysql> insert into student values(null,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| NULL | NULL |
+------+------+
create table student1(id int not null, name varchar(20)); 让id不能为空
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student1 values(null,null);
ERROR 1048 (23000): Column 'id' cannot be null 插入id为空则报错
2.nuique 约束
create table 表名(列名 类型 unique, 列名 类型 ...);
未使用时
update student set id = null where name ='xiaoming';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student1 values (1 , 'xiaoming');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student1 values (1 , 'xiaoming'); 可以多行插入
Query OK, 1 row affected (0.00 sec)
mysql> select * from student1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
| 1 | xiaoming |
| 1 | xiaoming | 表项中出现多个相同的值
+----+----------+
使用 unique 约束
mysql> create table student ( id int unique, name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into student values(1,'xiaoming');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'xiaoming');
ERROR 1062 (23000): Duplicate entry '1' for key 'id' 这边进行了多次插入
mysql> select * from student
-> ;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+ 表项只有一次插入的值,无多次值,说明unique约束成功了
1 row in set (0.00 sec)
3.Default 约束
create table 列表名 (列名 类型,列名,类型 default '关键字');
未使用如下:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
当我们使用了default 进行约束后,再插入时,就会在没有显示指定插入的值的时候生效
mysql> create table student (id int , name varchar(20) default '未命名');
Query OK, 0 rows affected (0.02 sec)
mysql> desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-----------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | 未命名 | |
+-------+-------------+------+-----+-----------+-------+
mysql> insert into student (id) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 未命名 |
+------+-----------+
1 row in set (0.00 sec)
4.primary key(最重要的约束,一行记录 身份标识)
create table 列表名 (列名 类型 primary key,列名,类型 ');
【一个表里只能有一个身份标识,即只能有一个 primary key】
【虽然只有一个主键但也可以多个列,构成一个联合主键】
mysql> create table student (id int primary key ,name varchar(20));
Query OK, 0 rows affected (0.03 sec) 使用这个 primary kay 作为主键
mysql> insert into student values (1,'小明');
Query OK, 1 row affected (0.01 sec) 使用这个也可以正常插入
mysql> insert into student values (1,'小明');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
包含了 nuique 约束 所以说也不能重复插入
mysql> insert into student values (null,'小明');
ERROR 1048 (23000): Column 'id' cannot be null
同意包含了nuique 约束 也不能插入为空值(null)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
对于带有主键的表来说, 他每次插入修改/修改 数据时,都会先进行查询操作。
5.foreign key 外键
描述了两个表之间的关联关系
父表约束子表;
子表受父表约束;
为什么要使用外键:
————————创建了两个表,一个班级,一个学生——————
1——————插入一个学生——————
mysql> insert into student values (1,'张三',100);
Query OK, 1 row affected (0.01 sec)
mysql> select * from class ;
+---------+--------+
| classid | name |
+---------+--------+
| 1 | 小班 |
| 2 | 中班 |
| 3 | 大班 |
+---------+--------+
————————————我们可以看到我们插入的 张三班级为 100 ,而我们创建的班级无100
————————————所以我们认为这个插入是不合法的
mysql> select *from student;
+----+--------+---------+
| id | name | classid |
+----+--------+---------+
| 1 | 张三 | 100 |
+----+--------+---------+
怎么使用外键
create table 表名 (列名 类型 primary key,列名 类型 , 表名 类型 ,foreign key(当前表被约束的列) re
ferences 从那个表约束的(那个表的列名 ));
指定外键约束时,父表中关联的这一列必须带 主键 或 unique
————————引用外键 创建了一个student 表 ,受到 class 表中 classid列约束——————
create table student (id int primary key,name varchar(20) , classid int ,foreign key(classid) references class(classid));
——————————给学生表插入一个——————
mysql> insert into student values (1,'张三',1);
Query OK, 1 row affected (0.01 sec)
——————————插入李四 班级 并不在我们创建的class表中——————
mysql> insert into student values (2,'李四',4);
————————报错——————
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dome1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`))
————同样我们也不能直接修改子类已经引用的父表中的值——————
mysql> update student set classid = 4 where id = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dome1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`))
mysql> select * from class;
+---------+--------+
| classid | name |
+---------+--------+
| 1 | 小班 |
| 2 | 中班 |
| 3 | 大班 |
+---------+--------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+--------+---------+
| id | name | classid |
+----+--------+---------+
| 1 | 张三 | 1 |
+----+--------+---------+
1 row in set (0.00 sec)
—————————父类中没有引用的值可以进行修改删除——————
mysql> delete from class where classid = 2;
Query OK, 1 row affected (0.01 sec)
——————一旦引用了就无法再修改即删除——————
mysql> delete from class where classid = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dome1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`))
6.check(了解)
mysql 使用时不会报错吗但会忽略改约束
drop table if exists test_user;
create table test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女')
);
三、如何确保主键唯一
mysql 提供了 ”自增主键’ 的一种机制
主键 常会使用 int / bigint
在插入数据时,不必手动指定 主键 值 ~~ 有数据库服务器自己给你分配一个主键
它会从 1 开始 ,进行递增的分配主键的值
1.自动分配
create table 表名(列名 类型 primary key auto_increment, 表名 类型);
缺点:
自动分配又一定局限性
如果是单个mysql服务器,没问题,
如果是有一个分布式系统,有多个 MySQL服务器构成的集群 ,这个时候依靠 自增主键就不行了
他不会保证跟其他数据库中的 id 不重复
分布式系统 id 算法 :http://t.csdnimg.cn/wTv1dhttp://t.csdnimg.cn/wTv1d
mysql> create table student(id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
可以发现 extra 中出现了这个 auto——increment 表示他会自动给我们分配这个值。
————————————————————当我们插入一个值——————————————————
mysql> insert into student values (null,'小明');
-----这里的 null 不是表示为空,而是交给这个关键字自动处理
————————————————————就会出现以下结果:————————————————
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
+----+--------+
1 row in set (0.00 sec)
——————————这边又插入一个数据————————————
mysql> insert into student values (null,'王强');
Query OK, 1 row affected (0.01 sec)
——————————可以看到 id 一列自动进行了添加
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
| 2 | 王强 |
+----+--------+
2 rows in set (0.00 sec)
2.手动添加
也可以手动添加这个值,后续再使用关键字添加就会以新添加的值来递增
相当于使用一个变量来保存当前表id的一个最大值,
后续通过这个变量的值来进行分配
如果再指定了一个id 如果比他大会自动更新
【其中空余的空间会自动浪费】
——————————————-此处我们手动添加了一个 id 为 10.
mysql> insert into student values (10,'李四');
——————————————可以看到 同样我们可以手动添加————————————
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
| 2 | 王强 |
| 3 | 张三 |
| 10 | 李四 |
+----+--------+
——————————————再自动添加一个 名为 王五 的。
mysql> insert into student values (null,'王五');
——————————————此时可以看到 它的自增数为最 新 添加的一个开始自增——————————————
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
| 2 | 王强 |
| 3 | 张三 |
| 10 | 李四 |
| 11 | 王五 |
+----+--------+
5 rows in set (0.00 sec)