mysql学习笔记二

MySQL建表约束

主键约束
它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加主键约束，就可以使得该字段不重复且不为空。
给id字段添加主键约束：

mysql> create table milk(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (1.68 sec)

mysql> describe milk;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

若该id字段重复或为空则会报错：

mysql> insert into milk values (1,'yili');
Query OK, 1 row affected (0.01 sec)

mysql> insert into milk values (1,'yili');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into milk values (null,'mengniu');
ERROR 1048 (23000): Column 'id' cannot be null

联合主键
只要联合的主键值加起来不重复就可以，且联合主键中的每一个字段都不能为空：

mysql> create table chip(
    -> id int,
    -> name varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into chip values (1,'le');
Query OK, 1 row affected (0.00 sec)

mysql> insert into chip values (2,'le');
Query OK, 1 row affected (0.01 sec)

mysql> insert into chip values (null,'le');
ERROR 1048 (23000): Column 'id' cannot be null

自增约束
自增约束可以和主键约束一起搭配使用，只能修饰 int字段。自增长列不一定要和主键搭配，但是必须和key一起搭配；一个表最多一个自增长列。

mysql> create table meat(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> num int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into meat (name,num) values ('zhangsan',60);
Query OK, 1 row affected (0.01 sec)

mysql> select * from meat;
+----+----------+------+
| id | name     | num  |
+----+----------+------+
|  1 | zhangsan |   60 |
+----+----------+------+
1 row in set (0.00 sec)

2.1 如果说我们创建表的时候，忘记创建主键了，怎么办？
使用alter修改表结构，添加约束：

mysql> create table tool(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table tool add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

2.2 使用modify修改字段，修改约束：

mysql> alter table tool modify id int auto_increment;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

2.3 删除主键
如果是自增主键则需要两步，首先删除auto_increment，然后再删除主键约束。如果只是删除单独的主键，则直接进行第二步即可。
第一步：删除auto_increment

mysql> alter table tool modify id int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

第二步：删除主键约束

mysql> alter table tool drop primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

唯一约束
约束修饰的字段的值不可以重复。unique(id,name)只要id和name组合在一起不重复就可以。

mysql> create table beer(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> alter table beer add unique(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table cola(
    -> id int,
    -> name varchar(20) unique);
Query OK, 0 rows affected (0.03 sec)

mysql> describe cola;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

mysql> create table colas(
    -> id int,
    -> name varchar(20) ,
    -> unique(name));
Query OK, 0 rows affected (0.03 sec)

删除唯一约束：

mysql> alter table cola drop index name;
Query OK, 0 rows affected (1.71 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe cola;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (1.66 sec)

添加唯一约束：

mysql> alter table cola modify name varchar(20) unique;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe cola;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

非空约束
修饰的字段的值不能为null。

mysql> create table tea(
    -> id int not null,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> describe tea;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

默认约束
当我们插入字段值的时候，如果没有传值，就会使用默认值；若该字段传值则不会使用默认值。

mysql> create table sugar(
    -> id int,
    -> name varchar(20),
    -> num int default 60);
Query OK, 0 rows affected (0.09 sec)

mysql> describe sugar;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| num   | int(11)     | YES  |     | 60      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> insert into sugar (id,name) values (1,'bang');
Query OK, 1 row affected (1.62 sec)

mysql> select * from sugar;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | bang |   60 |
+------+------+------+
1 row in set (0.00 sec)

外键约束
外键：foregin key在从表(子表)中指向主表(父表)主键的字段。
从表(子表)是含有外键的表；主表(父表)是外键指向的表。
外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

6.1 一对多或者多对一，如学生和班级的关系，此时采用在多的一端的表中添加外键的方式。
主表：

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

从表：

mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

添加数据(首先需要添加班级，再添加学生)：

mysql> insert into classes values(1,'one');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values(2,'two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1001,'zhang',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1003,'zhang',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

6.2 多对多的关系。如学生与老师的关系，这时候需要重新建立一张中间表，中间表中两字段均为外键字段，且指向两张表的主键。
6.3 一对一的关系。如班级与班主任的关系，这时候无需新建表，方式与一对多相似甚至不用设置外键。
由此可以看出：
主表classes中没有的数据值，在从表中是不可以使用的。
主表中的记录被从表引用，主表的记录是不可以被删除的。

总结：
添加约束：
7.1 建表的时候就添加约束
7.2 可以使用alter...add... 添加约束
7.3 可以使用alter...modify... 添加约束
删除约束：
7.4 可以使用alter...drop... 删除约束

最后编辑于：2020.03.11 15:06:41

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,884评论 6赞 492
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,755评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 158,369评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,799评论 1赞 285
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,910评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,096评论 1赞 291
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,159评论 3赞 411
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,917评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,360评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,673评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,814评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,509评论 4赞 334
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,156评论 3赞 317
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,882评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,123评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,641评论 2赞 362
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,728评论 2赞 351

mysql学习笔记二

MySQL建表约束

推荐阅读更多精彩内容