创建表加入约束(constraint)
/*约束在创建表的时候 可以给表的字段添加响应的约束 添加约束的目的是为了保证表中的数据的合法性 有效性 完整性
常见的约束有哪些呢？
非空约束（not null）约束的字段不能为NULL
唯一约束 （unique）约束的字段不能重复
主键约束 （ primary key）约束的字段既不能为NULL也不能重复
外键约束 （foreign key）
检查约束 （check）
注意oracal数据有check约束 目前mysql不支持
*/
- not null(只有列级约束)
mysql> drop if exists t_mem;
mysql> create table t_mem(
id int,
memname varchar(255) not null,
password varchar(255)
);
mysql> desc t_mem;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| memname | varchar(255) | NO | | NULL | |
| password | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
//非空字段少值
mysql> insert into t_mem (id,password) values (1,'123');
结果打印：
ERROR 1364 (HY000): Field 'memname' doesn't have a default value
mysql> insert into t_mem (id,memname,password) values (1,'lisi','123');
mysql> select *from t_mem;
+------+---------+----------+
| id | memname | password |
+------+---------+----------+
| 1 | lisi | 123 |
+------+---------+----------+
2.唯一性约束(unique)(列级 表级约束)
唯一性约束修饰的字段具有唯一性 不能重复 但可以为NULL
//给一个字段添加unique
drop table if exists t_member;
create table t_member(
id int,
username varchar(255) unique
);
//插入数据
insert into t_member values (1,'zhangsan');
//插入相同的username
insert into t_member values (1,'zhangsan'),(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_member.username'
//可以同时为NULL
insert into t_member (id) values (2);
insert into t_member (id) values (3);
mysql> select *from t_member;
+------+----------+
| id | username |
+------+----------+
| 2 | NULL |
| 3 | NULL |
| 1 | zhangsan |
+------+----------+
//给多个字段联合添加unique
//usercode,username联合(拼接)起来不能重复
create table t_member(
id int unique,
usercode varchar(255),
username varchar(255),
unique(usercode,username),
);
//给单独字段添加unique
// 单独字段内容不能重复
create table t_member(
id int unique,
usercode varchar(255) unique,
username varchar(255) unique,
);
3.主键约束
根据主键字段分类
1.单一主键
2.复合主键(多个字段联合起来添加主键约束)（复合主键不建议使用 因为复合主键违背了三范式）
3.业务主键 主键和系统的业务挂钩 例如:拿着银行卡的卡号做主键 拿着身份证号作为主键.(不推荐使用)
4.自然主键：主键值最好就是一个和业务没有关系的自然数(推荐)
//使用符合主键 不需要掌握
drop table if exists t_mem;
create table t_mem(
id int primary key,
username varchar(255),
email varchar(255),
primary key(id,username)
);
//使用表级约束定义主键
drop table if exists t_mem;
create table t_mem(
id int primary key,
username varchar(255),
email varchar(255),
primary key(id)
);
//使用列级约束定义主键
drop table if exists t_mem;
create table t_mem(
id int primary key,
username varchar(255),
email varchar(255)
);
insert into t_mem (id,username,email) values (1,'zs','zs@123');
insert into t_mem (id,username,email) values (2,'ls','ls@123');
insert into t_mem (id,username,email) values (3,'ww','ww@123');
mysql> select *from t_mem;
+----+----------+--------+
| id | username | email |
+----+----------+--------+
| 1 | zs | zs@123 |
| 2 | ls | ls@123 |
| 3 | ww | ww@123 |
+----+----------+--------+
//id为主键不能重复
mysql> insert into t_mem (id,username,email) values (1,'md','md@123');
ERROR 1062 (23000): Duplicate entry '1' for key 't_mem.PRIMARY'
//id为主键 不能为NULL
mysql> insert into t_mem (username,email) values ('md','md@123');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
//mysql提供主键值自增 (auto_increment)
drop table if exists t_mem;
create table t_mem(
id int primary key auto_increment,
username varchar(255)
);
insert into t_mem (username) values ('a');
insert into t_mem (username) values ('b');
insert into t_mem (username) values ('c');
insert into t_mem (username) values ('d');
mysql> select *from t_mem;
+----+----------+
| id | username |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+----------+
4 rows in set (0.00 sec)
4.外键约束 foreign key
//班级表和学生表
t_class 班级表（父表）
cno(pk) cname
---------------------------------------------
101 北京宜黄二中高三1班
102 北京宜黄二中高三2班
t_student 学生表（子表）
sno(pk) sname classno(该字段添加外键约束fk)
----------------------------------------------------------
1 zs1 101
2 zs2 102
3 zs3 101
4 zs4 102
//将以上表的建表语句写出来
//foreign key(字段) references 父表(字段),
drop table if exists t_student;
create table t_class(
cno int primary key,
cname varchar(255)
);
create table t_student(
sno int primary key,
sname varchar(255),
classno int,
foreign key(classno) references t_class(cno)
);
insert into t_class values (101,'xxxxxxxxxxxx');
insert into t_class values (102,'yyyyyyyyyyyyy');
insert into t_student values(1,'zs',101);
insert into t_student values(2,'ff',102);
insert into t_student values(3,'ww',102);
insert into t_student values(4,'cc',101);
mysql> select *from t_class;
+-----+---------------+
| cno | cname |
+-----+---------------+
| 101 | xxxxxxxxxxxx |
| 102 | yyyyyyyyyyyyy |
+-----+---------------+
2 rows in set (0.00 sec)
mysql> select *from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
| 1 | zs | 101 |
| 2 | ff | 102 |
| 3 | ww | 102 |
| 4 | cc | 101 |
+-----+-------+---------+
4 rows in set (0.00 sec)
insert into t_student values(5,'cc',105);
Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
网友评论