时间:2021-07-01 10:21:17 帮助过:5人阅读
他能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使的该字段不重复且不为空
- <code>create table user(
- id int primary key,
- name varchar(20)
- );
- </code>
输入 describe user;
- <code>+-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
- </code>
这里的key值下的PRI即为主键约束
我们可以插入一个记录
insert into user values(1,‘张三‘);
再插入一次相同的记录
insert into user values(1,‘张三‘);
输入select * from user;
- <code>+----+------+
- | id | name |
- +----+------+
- | 1 | 张三 |
- +----+------+
- </code>
还有一种情况,成为联合主键
- <code>> create table user2(
- -> id int,
- -> name varchar(20),
- -> password varchar(20),
- -> primary key(id,name) //表示id 和name 两个有一个不重复就可以
- -> );
- </code>
自动添加主键约束
- <code>create table user3(
- -> id int primary key auto_increment,
- -> name varchar(20)
- -> );
- </code>
添加记录:insert into user3 (name) values(‘张三‘);
- <code>mysql> select * from user3;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 张三 |
- +----+------+
- </code>
再次添加记录:insert into user3 (name) values(‘张三‘);
- <code>mysql> select * from user3;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 张三 |
- | 2 | 张三 |
- +----+------+
- </code>
如果创建表时忘记添加主键约束该怎么添加主键?
- <code>create table user4(
- -> id int,
- -> name varchar(20)
- -> );
- </code>
decs user4
- <code>mysql> desc user4
- -> ;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
我们可以 alter table user4 add primary key(id);或者输入alter table user4 modify id int primary key;
- <code>mysql> desc user4
- -> ;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
同理,可以添加主键也可以删除主键:
alter table user4 drop primary key;
约束修饰的字段的值不可以重复
- <code>create table user5(
- -> id int,
- -> name varchar(20)
- -> );
- </code>
- <code>desc user5
- -> ;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
- </code>
输入:alter table user5 add unique(name);
- <code>mysql> desc user5
- -> ;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | | NULL | |
- | name | varchar(20) | YES | UNI | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
添加记录:insert into user values(1,‘张三‘);
- <code>mysql> select * from user5;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 张三 |
- +----+------+
- </code>
再次添加记录:insert into user values(1,‘张三‘);
把名字改成李四就可以(insert into user values(1,‘李四‘);)
也可以在创建的时候直接添加约束
- <code>create table user6(
- id int,
- name varchar(20)
- unique(name)
- );
- </code>
输出为
- <code>mysql> desc user6;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | | NULL | |
- | name | varchar(20) | YES | UNI | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 2 rows in set (0.00 sec)
- </code>
此外,unique还可以改变位置
- <code> create table user7(
- -> id int,
- -> name varchar(20),unique
- -> );
- mysql> desc user7
- -> ;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | name | varchar(20) | YES | UNI | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
- <code>mysql> create table user8(
- -> id int,
- -> name varchar(20),
- -> unique(id,name)//用法同联合主键,有一个不重复的就可以
- -> );
- Query OK, 0 rows affected (0.03 sec)
- mysql> desc user8;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | MUL | NULL | |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
alter table user7 drop index name;
- <code>mysql> desc user7;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 1 row in set (0.00 sec)
- </code>
alter table user7 modify name varchar(20) unique;
修饰的字段不能为空 NULL
- <code>mysql> create table user9(
- -> id int,
- -> name varchar(20) not null
- -> );
- </code>
输出:
- <code>mysql> desc user9
- -> ;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | | NULL | |
- | name | varchar(20) | NO | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
可以看到 name值的null显示不能为空,即添加记录时name的null值不能为空,即
输入 insert into user9 values(1);会出错
输入insert into user9 values(1,’张三‘)就不会报错
当插入字段值的时候,没有传值,就会使用默认值
- <code>mysql> create table user10(
- -> id int,
- -> name varchar(20),
- -> age int default 10
- -> );
- Query OK, 0 rows affected (0.04 sec)
- mysql> desc user10;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | age | int | YES | | 10 | |
- +-------+-------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
- </code>
- <code>mysql> insert into user10 (id,name) values(1,‘zhangsan‘);//不传年龄就默认为10
- Query OK, 1 row affected (0.01 sec)
- mysql> select * from user10;
- +------+----------+------+
- | id | name | age |
- +------+----------+------+
- | 1 | zhangsan | 10 |
- +------+----------+------+
- </code>
涉及两个表:父表(主表)、子表(副表)
例如,有一个班级表(主表)
- <code>create table classes(
- -> id int primary key,
- -> name varchar(20)
- -> );
- </code>
还有一个学生表(副表)
- <code> create table students(
- -> id int primary key,
- -> name varchar(20),
- -> class_id int,
- -> foreign key(class_id) references classes(id)//指明副表里的id来自哪个表
- -> );
- </code>
分别desc
- <code> desc classes;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- </code>
- <code>desc students;
- +----------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +----------+-------------+------+-----+---------+-------+
- | id | int | NO | PRI | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | class_id | int | YES | MUL | NULL | |
- +----------+-------------+------+-----+---------+-------+
- </code>
首先往主表里插入数据:
insert into classes values(1,‘一班‘);
insert into classes values(2,‘二班‘);
insert into classes values(3,‘三班‘);
insert into classes values(4,‘四班‘);
查看数据------------select * from classes;
- <code> select * from classes;
- +----+------+
- | id | name |
- +----+------+
- | 1 | 一班 |
- | 2 | 二班 |
- | 3 | 三班 |
- | 4 | 四班 |
- +----+------+
- </code>
之后往副表里插入数据:
insert into students values(1001,‘甲‘,1);
insert into students values(1002,‘乙‘,2);
insert into students values(1003,‘丙‘,3);
insert into students values(1004,‘丁‘,4);
没有报错,但是 若insert into students values(1005,‘小明‘,5);就会报错,因为
总结自 [code158编程俱乐部]http://www.code158.com mysql编程系列教程
mysql建表约束
标签:建表 约束 联合 password 通过 查看 创建 alter 自增