编程一五八俱乐部SQL学习笔记-2-mysql建表中的约束

于 2021-02-03 14:01:00 发布
阅读量102 收藏 1
点赞数
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38582314/article/details/113548811
版权

回顾上一笔记：
update xxx set ...改
delete from xxx where ...删
select * from xxx查
insert into xxx value()增

一、主键约束
1、主键约束

使某个字段不重复且不得为空，确保表内所有数据的唯一性。

mysql>create table user(
			id int primary key,
			name varchar(20)
			);
mysql> insert into user values(1,"zhangsan");
//mysql> insert into user values(1,"zhangsan");
//ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY' //主键是序号，再加一个1重复了，报错
//mysql> insert into user values(NULL,"zhangsan");
//ERROR 1048 (23000): Column 'id' cannot be null //主键为空了报错

mysql> insert into user values(2,"zhangsan");
mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+
2、联合主键

多个主键，这里面每个主键都不能为空，并且加起来不重复即可

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name) //在最后声明主键
    -> );
mysql> insert into user2 values(1,'zhangsan','123');

//mysql> insert into user2 values(1,'zhangsan','123');
//ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user2.PRIMARY' 不能所有主键都重复
mysql> insert into user2 values(2,'zhangsan','123');
//Query OK, 1 row affected (0.00 sec) 有个别主键不重复的即可插入
3、自增约束

主键由系统自动递增分配

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
//mysql> insert into user3 values('zhangsan'); //错误写法
mysql> insert into user3 (name) values('zhangsan'); //正确写法，多了(自增主键)

mysql> insert into user3 (name) values('zhangsan');
mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+
3、后期添加&删除主键约束

如果忘记设置主键，还可以通过sql语句设置（两种方式）

alter table user  add primary key(id);
alter table user  modify id int primary key;

alter table user  drop primary key;

二、唯一约束

1、唯一约束

该字段的值不可以重复。与主键约束的区别：

  • 主键约束只能有一个，并且不重复不为空；
  • 唯一约束的字段仅仅是数值上的不重复，可以为空，可以多个。
mysql> create table user5(
    -> id int,
    -> name varchar(20)
    -> );
mysql> alter table user5 add unique(name); //后设置name为唯一约束
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into user5 values(1,'zhangsan');
//mysql> insert into user5 values(1,'zhangsan');
//ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'张三重复，报错
mysql> insert into user5 values(1,'lisi');
mysql> insert into user5 values(1,NULL);

mysql> select * from user5;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    1 | lisi     |
|    1 | NULL     |
+------+----------+

//上述间接创建unique约束，下面直接unique约束
mysql> create table user5(
    -> id int,
    -> name varchar(20),
    -> unique(name)
    -> );
mysql> create table user5(
    -> id int,
    -> name varchar(20) unique
    -> );
2、多个唯一约束

有点像联合主键。

mysql> create table user8( id int, name varchar(20),unique(id,name) );
mysql> insert into user8 values(1,'zhangsan');

//mysql> insert into user8 values(1,'zhangsan'); //多个字段都一样才算重复
//ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user8.id'
mysql> insert into user8 values(2,'zhangsan'); //其中有字段不一样就没关系
mysql> insert into user8 values(1,'lisi');
mysql> select * from user8;
+------+----------+
| id   | name     |
+------+----------+
|    1 | lisi     |
|    1 | zhangsan |
|    2 | zhangsan |
+------+----------+
3、添加&删除唯一主键
alter table user  add unique(name);
alter table user  modify name varchar(20) unique;

alter table user  drop index name;

三、非空约束

修饰的字段不能为空

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
    -> );
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       | //这里null默认值为no，表明不能为空
+-------+-------------+------+-----+---------+-------+

//mysql> insert into user9 (id) values(1); //注意这里只对id赋值，所以user9后面有(id)
//ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into user9 values(1,'zhangsan');
mysql> insert into user9 (name) values('lisi'); // 只对李四赋值，依然ok，name非空即可
mysql> select * from user9;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
| NULL | lisi     |
+------+----------+

移除非空约束

alter table user  modify name varchcar(20);

四、默认约束

约定某个字段的默认值

mysql> create table user10( 
    -> id int,
    -> name varchar(20),
    -> age int default 10
    -> );
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 10      |       | //默认为10
+-------+-------------+------+-----+---------+-------+

mysql> insert into user10 (id,name) values(1,'zhangsan');
mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+

mysql> insert into user10 values(1,'zhangsan',19);
mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   19 |
+------+----------+------+

移除默认约束

alter table user  modify age int;

五、外键约束

涉及到父表，子表（主表副表），主表的数据被副表引用。

//classes主表
mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
//students副表
mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id) 
    -> ); //foreign的意思是上方的class_id的字段值参考classes表中的id，注意references加s
mysql> insert into classes values(1,"yiban");
mysql> insert into classes values(2,"erban");
mysql> insert into classes values(3,"sanban");
mysql> insert into classes values(4,"siban");
mysql> insert into students values(1001,'zhangsan',1);
mysql> insert into students values(1002,'zhangsan',2);
mysql> insert into students values(1003,'zhangsan',3);
mysql> insert into students values(1004,'zhangsan',4);

mysql> insert into students values(1005,'zhangsan',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
//主表中不存在的记录5，不可以在附表中添加

mysql> delete from classes where id = 4; //注意删的是主表中的
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
//当前主表中的记录正被别人引用着，不可删

要想比别人优秀，就要比别人多付出两倍的努力。

Jackson Cortex
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值