1:主键约束
能够确定一张表中的一条记录,通过给某一字段添加约束,就可以使得该字段不重复且不为空。
create table user(
-> id int primary key,
-> name varchar(20)
-> );
我们使用了主键元素;看下这个表信息。
MariaDB [helloworld]> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
我们可以看到这个Key值是PRI,我们这时候插入一个元素。
nsert into user value(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
继续插入相同元素就会出现报错
insert into user value(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
我们添加的时候得更改ID才能进行添加;因为ID有了主键约束;
insert into user value(2,'zhangsan');
Query OK, 1 row affected (0.00 sec)
查看哈:
select *from user;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
+----+----------+
注意ID不能为NULL
联合主键:只要联合的主键值加起来不出重复就可以
create table user2( id int,
-> name vaechar(20).
-> password varchar(20),
-> primary key(id,name)
-> );
insert into user2 values(1,'zhangsan','610722200101555546');
insert into user2 values(2,'zhangsan','610722200101555546');
可以插入的
select *from user2;
+----+----------+--------------------+
| id | name | password |
+----+----------+--------------------+
| 1 | zhangsan | 610722200101555546 |
| 2 | zhangsan | 610722200101555546 |
+----+----------+--------------------+
2:外键约束
一个是父表,一个是字表
例子:
create table classes( id int primary key, name varchar(20));
create table students( id int primary key, name varchar(20), class_id int, foreign key(class_id) references classes(id));
desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
desc students;
+----------+-------------+------+-----+---------+-------+
| 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:唯一约束
约束该字段的值不可以重复
create table user5( id int, name varchar(20) );
alter table user5 add unique(name);
desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
唯一约束修饰的字段不可以重复
如何删除唯一约束?
alter table user7 drop index name;
desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4:非空约束
create table user7( id int, name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)
MariaDB [helloworld]> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
不传id是可以的
insert into user7(name) values('lisi');
desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
5:自增约束
create table user3(
-> id int primary key auto_increment,
-> name varchar(20));
自增约束和主键约束搭配在一起使用
insert into user3 (name) values('libai');
我们可以看到,我们插入时并没有写ID号,我们来看结果
select *from user3;
+----+-------+
| id | name |
+----+-------+
| 1 | libai |
+----+-------+
1 row in set (0.00 sec)
创建过程中忘了加主键怎么办
添加主键:
create table user4( id int, name varchar(20));
desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除主键:
alter table user4 drop primary key;
desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
修改主键约束:
alter table user4 modify id int primary key;
6:默认约束
当我们插入字段值的时候,如果没有传值就会使用默认值。
create table test1(
-> id int,
-> name varchar(20),
-> age int default 10);
desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
insert into test1(id,name) values(1,'zhangsan');
select *from test1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
+------+----------+------+
===========================================================
总结:
添加约束:
1:创建表的时候添加约束,
2:使用 alter添加…add…,
3:使用 alter…modif…,
4: alter…drop…进行删除。