1. not null 非空约束
// name 不能为空，如果数据插入的时候name为空会报错
create table t_user(
id int(10),
name varchar(32) not null, // 列级约束
email varchar(128)
// not null(name) 加到这里是表级约束
);
insert into t_user(id,name,email) values (1,'张三','123@jd.com');
+------+--------+------------+
| id | name | email |
+------+--------+------------+
| 1 | 张三 | 123@jd.com |
+------+--------+------------+
// name设置不能为空，如果插入的是时候name为空会报这种错误
insert into t_user(1,email)values(1,'135@jd.com');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,email)values(1,'135@jd.com')' at line 1
2. unique 唯一约束
create table t_user(
id int(10),
name varchar(32) not null, // 列级约束
email varchar(128) unique // 列级约束
// unique(email) 表级约束
);
// 给邮箱加上unique 表示在插入数据的时候邮箱不能重复
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(32) | NO | | NULL | |
| email | varchar(128) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+-------+
3. 主建约束 primary key 简称pk
主建涉及的术语
主建约束
主建字段
主建值
给字段添加主建约束后,不能为空,并且不能重复
一张表中应该有主建,如果没有主建表示这张表是无效的
主建值：是当前行数据的唯一标示
/// 添加主建
drop table if exists t_user;
create table t_user(
id int(10) primary key,
name varchar(32) ,
email varchar(128)
);
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
| email | varchar(128) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
mysql 中提供了一个自增数字，主建的值是自动增长，不需要用户维护
auto_increment ， 自动增长的值只能使用一次
drop table if exists t_user;
create table t_user(
id int(10) primary key auto_increment,
name varchar(32) ,
email varchar(128)
);
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| email | varchar(128) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4. foreign key 外建约束 简称fk
主建涉及的术语
外建约束
外建字段
外建值
foreign key 可以把两张表联系起来
两张表设置外建
drop table if exists t_student;
drop table if exists t_calss;
create table t_calss(
cno int(3) primary key,
cname varchar(120) not null unique
);
create table t_student(
sno int(3) primary key,
sname varchar(32) not null,
calssno int(3),
foreign key(calssno) references t_calss(cno)
);
insert into t_calss (cno,cname) values(100,'1班');
insert into t_calss (cno,cname) values(200,'2班');
insert into t_calss (cno,cname) values(300,'3班');
insert into t_student(sno,sname,calssno) values(1,'李四',100);
insert into t_student(sno,sname,calssno) values(2,'王五',200);
insert into t_student(sno,sname,calssno) values(3,'赵六',300);
select * from t_student;
+-----+--------+---------+
| sno | sname | calssno |
+-----+--------+---------+
| 1 | 李四 | 100 |
| 2 | 王五 | 200 |
| 3 | 赵六 | 300 |
+-----+--------+---------+
select * from t_calss;
+-----+-------+
| cno | cname |
+-----+-------+
| 100 | 1班 |
| 200 | 2班 |
| 300 | 3班 |
+-----+-------+
// 下面这行添加失败t_calss cno 里面 500 不存在
insert into t_student(sno,sname,calssno) values(6,'李四',500);
