--mysql建表约束
－－主键约束
它能够唯一确定一张表中的内容，也就是我们通过某个字段添加约束，就可以是的该字段唯一(不重复)且不为空。
create table user(
id int primary key,
name varchar(20)
);
－－联合主键
只要联合的主键加起来不重复即可
create table user1(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);
insert into user1 values(1,'张三','123');
insert into user1 values(2,'李四','123');
insert into user1 values(3,'张三','123');
－－自增约束
--auto_increment
create table user2(
id int primary key auto_increment,
name varchar(20)
);
insert into user2 (name)values('张三')；
insert into user2 (name)values('李四')；
mysql> insert into user2 (name) values('张三');
Query OK, 1 row affected (0.03 sec)
mysql> insert into user2 (name) values('李四');
Query OK, 1 row affected (0.03 sec)
mysql> select * from user2;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
+----+--------+
会对id进行一个自动排序
2 rows in set (0.00 sec)
－－如果说在创建表时忘记创建主键约束，如何添加主键约束
create table user3(
id int,
name varchar(20)
);
desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
－－修改表结构
alter table user3 add primary key(id);
desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
－－删除表结构
alter table user3 drop primary key;
desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
可以会看到user3中没有了primary key
－－使用modify修改字段，添加约束
－－外建约束
--涉及到两个表：父表，字表
--班级
create table classes(
id int primary key,
name varchar(20)
);
--学生表
create table student(
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');
insert into student values(1,'张三',1);
insert into student values(2,'李四',2);
insert into student values(3,'王五',3);
insert into student values(4,'赵流',4);
insert into student values(5,'赵流',5);
mysql> insert into student values(5,'赵流',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
--主表没有的元素，副表不可以进行引用
--主表中记录被副表引用的元素不能被删除
delete from classes where id = 4;
mysql> delete from classes where id = 4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
mysql> select * from classes;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+----+--------+
4 rows in set (0.00 sec)
mysql> select * from student;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
| 4 | 赵流 | 4 |
+----+--------+----------+
4 rows in set (0.00 sec)
mysql>
－－唯一约束
--约束修饰的字段的值不能重复
create table user5(
id int,
name varchar(20),
primary key(id,name),:w
unique(id)
);
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
insert into user5 values(1,'张三')；
insert into user5 values(2,'李四')；
insert into user5 values(3,'王五')；
mysql> select * from user5;
+----+--------+
| id | name |/
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
3 rows in set (0.00 sec)
--如何删除唯一约束
alter table user5 drop index name;
－－非空约束
--修饰的字段不能为空
create table user6(
id int,
name varchar(20) not null
);
desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
－－默认约束
--就是当我们插入字段时，如果没有传值，就会使用默认值
create table user7(
id int,
name varchar(20),
age int Default 10
);
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
insert into user7 (id,name)values(1,'张三');
mysql> select * from user7;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | 张三 | 10 |
+------+--------+------+
1 row in set (0.00 sec)