手机搜索和网站搜索的排名,推广型网站制作公司,青岛网站排名方案,电脑可以做网站吗在MySQL中#xff0c;我们需要存储的数据在特定的场景中需要不同的约束。当新插入的数据违背了该字段的约束字段#xff0c;MySQL会直接禁止插入。
数据类型也是一种约束#xff0c;但数据类型这个约束太过单一#xff1b;比如我需要存储的是一个序号#xff0c;那就不可…在MySQL中我们需要存储的数据在特定的场景中需要不同的约束。当新插入的数据违背了该字段的约束字段MySQL会直接禁止插入。
数据类型也是一种约束但数据类型这个约束太过单一比如我需要存储的是一个序号那就不可能会有负数这时候就要用无符号来对整形进行约束如果我要存储的是一个日期且这个日期每个字段都必须要有那就需要用NOT NULL不为空来进行约束如果我要存储的是一个用户ID在整个用户系统中这个值肯定是唯一的就可以使用UNIQUE来约束唯一性
本文主要介绍下面几种约束的类型这依旧是MySQL中ddl类型的语句操作。
null/not null
default
comment
zerofill
primary key
auto_increment
unique key顺带一提在MySQL中每一列的值可以称之为列或者字段一般不称之为键值。 而表中的每一行被称作一条记录 1.空属性
1.1 说明
空属性包含两个值NULL/NOT NULL分别对应为空和不为空
在数据库中如果我们在插入一行时没有指定某一列的值那么数据库就会把这个值设置为NULL 这里要注意区分NULL和空字符串空字符串并不是NULL 但实际使用数据的时候假设这是一个整形的数据我们需要取出来后对其进行运算。此时NULL取出来的结果就不是一个数字没有办法进行运算。所以在很多时候我们都会把一个字段的约束设置为NOT NULL并添加上一个默认值比如0或者空字符串
MariaDB [hello] select null;
------
| NULL |
------
| NULL |
------
1 row in set (0.000 sec)MariaDB [hello] select not null;
----------
| not null |
----------
| NULL |
----------
1 row in set (0.001 sec)由下可见空属性是没办法参与运算的不管如何运算其结果都是NULL
在Python中NULL直接对应的就是None当你尝试用None和int类型进行运算的时候Python就会报错了。CPP中也是如此。
MariaDB [hello] select 1null;
--------
| 1null |
--------
| NULL |
--------
1 row in set (0.001 sec)1.2 案例
假设我们有一个楼层中的班级和教室对应的表其中包含班级编号和教室编号这两个字段
如果班级编号为空那就不知道在这间教室上课的是那个班级如果教室编号为空那就不知道某个班级到底是在哪里上课
由实际场景可见这两个字段都不可以为空所以在建表的时候就需要考虑到这一点
create table if not exists myclass(class_name varchar(30) not null,class_room varchar(30) not null
)default charsetutf8;创建了表之后当我们尝试将一个NULL的字段插入会出现如下的提示标识某一列不能为空
MariaDB [hello] insert into myclass values (510,NULL);
ERROR 1048 (23000): Column class_room cannot be null而空字符串是可以被插入的这里又一次说明了我们认为的空和NULL并不相同空字符串不是NULL
MariaDB [hello] insert into myclass values (510,);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] select * from myclass;
------------------------
| class_name | class_room |
------------------------
| 510 | |
------------------------
1 row in set (0.000 sec)2.默认值default
当我们注册某些网站的时候一些信息不填就会被系统设置为默认值。
比如你不选择年龄的时候系统可能就会显示你为0岁其他用户看到你的个人主页上显示的0岁就知道你并没有填写自己的真实年龄。而前端开发的时候也可以将0认作没有填写显示成”隐藏年龄“
再比如我们的网站上有一个用户积分的数值当用户注册的时候积分肯定是0暂时不考虑新人送积分什么的操作这时候就可以把积分那一列的默认值设置成0在插入的时候就可以不显式插入这列的数据
在MySQL中某一列设置了默认值后。在insert时候如果没有指定这一列的数据那就会采用默认值。
create table if not exists web_user(name varchar(30) not null default 默认用户名,age tinyint not null default 0,gender char(2) not null default 男
);创建完毕这个表当我们查看表结构的时候就能看到是否为空以及默认直的相关属性
MariaDB [hello] desc web_user;
--------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------
| name | varchar(30) | NO | | 默认用户名 | |
| age | tinyint(4) | NO | | 0 | |
| gender | char(2) | NO | | 男 | |
--------------------------------------------------------
3 rows in set (0.004 sec)由于这个表里面的3个字段我们都设置了初始值你甚至可以直接啥都不指定地插入一个数据下方可以看到所有列都被设置成了该列的初始值。
MariaDB [hello] insert into web_user values ();
Query OK, 1 row affected (0.001 sec)MariaDB [hello] select * from web_user;
------------------------------
| name | age | gender |
------------------------------
| 默认用户名 | 0 | 男 |
------------------------------
1 row in set (0.000 sec)当我们想不设置某一列的值的时候默认值就能帮上忙。
这里先给一个错误的演示我们只在values里面设置了两个值目的是让新插入的这个用户的性别采用默认值。但MySQL报错了报错的信息提示是value的个数和列的数量不一致
MariaDB [hello] insert into web_user values (李华,16);
ERROR 1136 (21S01): Column count doesnt match value count at row 1这是因为我们在插入的时候没有显示的告诉MySQL我们这两个值到底是哪两列的值。李华是给name列还是给gender列MySQL没有办法自主决定所以干脆拒绝插入。
所以当我们想让某一列使用缺省值的时候就需要告诉MySQL我们当前指定的values到底是哪几列的数据
insert into web_user (name,age) values (李华,16);这样才能插入成功
MariaDB [hello] insert into web_user (name,age) values (李华,16);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] select * from web_user;
------------------------------
| name | age | gender |
------------------------------
| 默认用户名 | 0 | 男 |
| 李华 | 16 | 男 |
------------------------------
2 rows in set (0.001 sec)因为这里做了对列名的显示指定所以顺序并不一定需要依照表中列名的顺序比如下方我们反过来也是可以插入的。但并不建议这么做在插入的时候的列名顺序应该和表中列顺序保持一致
MariaDB [hello] insert into web_user (age,name) values (18,小李);
Query OK, 1 row affected (0.001 sec)MariaDB [hello] select * from web_user;
------------------------------
| name | age | gender |
------------------------------
| 默认用户名 | 0 | 男 |
| 李华 | 16 | 男 |
| 小李 | 18 | 男 |
------------------------------
3 rows in set (0.001 sec)如果想让age列采用初始值那就是如下的插入
MariaDB [hello] insert into web_user (name,gender) values (菲菲公主,女);
Query OK, 1 row affected (0.005 sec)2.1 默认值和NULL
需要注意的是默认值和NOT NULL并不是必须一起使用的
当我们设置了默认值但是没有设置NOT NULL我们可以显式地插入NULL默认值也可以设置成NULL
create table if not exists test_user(name varchar(30) not null default 默认用户名,age tinyint not null default 0,gender char(2) default null
);使用如上sql创建表数据库没有报错即代表我们的语法是被支持的。因为性别并不需要参与运算所以我们可以认为当性别列为空的时候就是未选择性别的选项。不过也可以通过空字符串作为默认值来解决这一问题相比之下用空字符串更好因为这样能保证这个字段的值始终是个字符串而不需要对null进行特殊处理 default null 和 not null不能一起使用这是肯定的
3.列描述comment
需要注意在sqlite中是不支持comment的不同的数据库对sql字段的支持会有些许的差距请根据你使用的数据库为准。本文所述基于MySQL和MariaDB。
所谓的列描述就是对这列到底是干嘛的一个说明信息相当于代码的注释。其本身没有任何含义
列注释的主要作用就是让所有使用这个数据库使用这张表的人都能理解这个字段的作用。其中还可以添加额外的注释说明来让程序员统一在不同模块的上传代码中进行额外的处理。
比如我们将第二点中出现过的用户表改成如下形式每个字段都添加上注释
create table if not exists web_user(name varchar(30) not null default 默认用户名 comment 用户名,age tinyint not null default 0 comment 用户年龄,gender char(2) not null default 男 comment 用户性别
);当我们使用这个sql创建了这个表后如果想查询字段的注释可以用如下命令查看创建表时使用的命令其中就包含了表的注释
SHOW CREATE TABLE web_user;显示如下
MariaDB [hello] SHOW CREATE TABLE web_user;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| web_user | CREATE TABLE web_user (name varchar(30) NOT NULL DEFAULT 默认用户名 COMMENT 用户名,age tinyint(4) NOT NULL DEFAULT 0 COMMENT 用户年龄,gender char(2) NOT NULL DEFAULT 男 COMMENT 用户性别
) ENGINEInnoDB DEFAULT CHARSETutf8 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)
也可以用如下命令来展示所有列和列的属性其中包括注释
SHOW FULL COLUMNS FROM web_user;MariaDB [hello] SHOW FULL COLUMNS FROM web_user;
------------------------------------------------------------------------------------------------------------------------
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
------------------------------------------------------------------------------------------------------------------------
| name | varchar(30) | utf8_general_ci | NO | | 默认用户名 | | select,insert,update,references | 用户名 |
| age | tinyint(4) | NULL | NO | | 0 | | select,insert,update,references | 用户年龄 |
| gender | char(2) | utf8_general_ci | NO | | 男 | | select,insert,update,references | 用户性别 |
------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.002 sec)
需要注意desc命令显示的结果中是不包含列注释的
MariaDB [hello] desc web_user;
--------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------
| name | varchar(30) | NO | | 默认用户名 | |
| age | tinyint(4) | NO | | 0 | |
| gender | char(2) | NO | | 男 | |
--------------------------------------------------------
3 rows in set (0.001 sec)4.zerofill
4.1 测试结果
先来用如下命令创建一个表
create table if not exists test_int(a int not null,b int unsigned not null
);创建完成后我们查看创建这个表时使用的语句会发现在int之后多了一个括号里面跟了一个数字。
我们知道在char和varchar里面这个括号是用来限制字符串字符长度的那么在整形这里的括号是干嘛的呢
MariaDB [hello] show create table test_int;
----------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
----------------------------------------------------------------------------------------------------------------------------------
| test_int | CREATE TABLE test_int (a int(11) NOT NULL,b int(10) unsigned NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8 |
----------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)
先往这个表内插入一个数据并查询显示出来
MariaDB [hello] insert into test_int values (3,1);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] select * from test_int;
------
| a | b |
------
| 3 | 1 |
------
1 row in set (0.000 sec)如果我们把a列的属性进行修改为如下的字段类型
alter table test_int change a a int(5) unsigned zerofill;再去查看创建表的命令此时结果如下a列已经被修改成了信的属性
MariaDB [hello] alter table test_int change a a int(5) unsigned zerofill;
Query OK, 1 row affected (0.005 sec)
Records: 1 Duplicates: 0 Warnings: 0MariaDB [hello] show create table test_int;
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| test_int | CREATE TABLE test_int (a int(5) unsigned zerofill DEFAULT NULL,b int(10) unsigned NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8 |
-------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)那么这个括号里面的数字和zerofill有什么作用呢
再次查询此表会发现刚刚插入的数据a列的3变成了00003
MariaDB [hello] select * from test_int;
----------
| a | b |
----------
| 00003 | 1 |
----------
1 row in set (0.000 sec)4.2 前补0
此时这个属性的作用就很明确了其用于给数字进行前补0而5就是规定的前补0的长度而不是数字的长度当数字的位数小于5位就会触发前补0
可以看到即便表结构中出现了int(5)我们依旧可以往这个表里面插入长度大于5位的数字
MariaDB [hello] insert into test_int values (12345678,1);
Query OK, 1 row affected (0.005 sec)所以这个括号并不是用来限制int的长度的而是当一个数字小于5位的时候会给这个数字前补0
MariaDB [hello] insert into test_int values (18,3);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] select * from test_int;
-------------
| a | b |
-------------
| 00003 | 1 |
| 12345678 | 1 |
| 00018 | 3 |
-------------
3 rows in set (0.001 sec)如果再把int(5) zerofill改成更长的数值前补0的长度就会变化
alter table test_int change a a int(7) unsigned zerofill;MariaDB [hello] alter table test_int change a a int(7) unsigned zerofill;
Query OK, 0 rows affected (0.007 sec)
Records: 0 Duplicates: 0 Warnings: 0MariaDB [hello] select * from test_int;
-------------
| a | b |
-------------
| 0000003 | 1 |
| 12345678 | 1 |
| 0000018 | 3 |
-------------
3 rows in set (0.001 sec)需要注意int(n)的属性只有和zerofill一起使用才会触发前补0的操作。这便能解释为何最初创建的表里面是int(11)但数字并没有被前补0
而这里的前补0只是一个在MySQL内部显示的优化实际上存储的依旧是数字本身比如我们在上表中查询3是可以直接查出来的。
MariaDB [hello] select * from test_int where a 3;
------------
| a | b |
------------
| 0000003 | 1 |
------------
1 row in set (0.001 sec)比如我要存储的数字都是5位的设置了前补0查询整表时看到的格式化输出的结果会比没有前补0的结果看上去舒服很多。
4.3 为什么int是11无符号是10
在前面查询默认的创建表i语句的时候会发现MySQL系统默认给int了11位无符号int是10位 这是因为10位的长度已经能标识int范围内的所有值了而有符号整数多了一位是用来显示正负号的。
5.主键primary key
主键是用于约束字段里面的数据不能重复不能为空一张表只有一个主键或者没有一般都是用整形作为主键。
主键是用于确定表中每一条记录的唯一性的其告知了使用者要想往这个表中插入数据就必须保证主键的值不冲突。
5.1 主键的设计类型
以一个用户系统为例
我们可以把用户名设置为主键当用户选择了一个已经存在的用户名时拒绝此用户名并告知用户我们可以使用另外一个无关的数字作为主键比如QQ中就使用了QQ号来标识用户唯一性并不要求用户的用户名不能相相同现在绝大部分聊天软件都使用了这种方式比如QQ和KOOK微信虽然没有QQ那样的唯一标识但后台肯定也是有主键作为唯一性标识的、我们可以将多列组成复合主键
5.2 删除和添加主键
在创建表的时候可以用两种方式来指定主键
-- 方法1在字段后指明
create table test_pri_1(id int unsigned not null primary key,name varchar(30) not null
);
-- 方法2在表的最后指明
create table test_pri_2(id int unsigned not null,name varchar(30) not null,primary key(id)
);二者都能执行成功。
另外主键本身就是不能为空的所以我们定义主键列的时候可以不用写not null
MariaDB [hello] create table test_pri_1(- id int unsigned not null primary key,- name varchar(30) not null- );
Query OK, 0 rows affected (0.017 sec)MariaDB [hello] create table test_pri_2(- id int unsigned not null,- name varchar(30) not null,- primary key(id)- );
Query OK, 0 rows affected (0.019 sec)而且表结构相同在id列的Key处可以看到PRI就是Primary的缩写代表id列是主键
MariaDB [hello] desc test_pri_1;
----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
----------------------------------------------------
2 rows in set (0.001 sec)MariaDB [hello] desc test_pri_2;
----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
----------------------------------------------------
2 rows in set (0.001 sec)如果是一个已经存在的表我们也可以往里面追加主键或者删除主键
alter table 表名 drop primary key;
-- 用于删除主键列的主键属性因为主键列只能有一个
-- 注意这个语句不会删除该列
alter table 表名 add primary key(id);
-- 给id列加上主键属性但是ID列里面不能有重复值测试一下可以看到id列的PRI属性没有了
MariaDB [hello] alter table test_pri_1 drop primary key;
Query OK, 0 rows affected (0.010 sec)
Records: 0 Duplicates: 0 Warnings: 0MariaDB [hello] desc test_pri_1;
----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------
| id | int(10) unsigned | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
----------------------------------------------------
2 rows in set (0.002 sec)当我们往表里面插入数据的时候如果想往主键列插入一个相同的记录MySQL会拒绝插入
MariaDB [hello] insert into test_pri_2 values (1,李华);
Query OK, 1 row affected (0.006 sec)MariaDB [hello] insert into test_pri_2 values (1,李明);
ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY5.3 复合主键
一张表只有一个主键但是主键可以不止一列
以我自己写的活跃度统计机器人为例机器人处在不同服务器中会收到不同的服务器ID和不同用户的操作为了记录不同服务器的不同用户的活跃度情况在用户统计表中需要同时有服务器ID和用户ID此时就会出现一个用户加入了两个服务器而这两个服务器都使用了我这个机器人的情况。反馈到表中就是一个用户ID出现了两次但对应的服务器ID不同
在这种情形下肯定是不能把用户ID或服务器ID单独设置成主键的。我们就可以把用户ID和服务器ID统一设置成符合主键
设置了复合主键后我们可以出现相同的服务器ID和相同的用户ID。但只能是某个服务器的某个用户不能存在两条服务器ID和用户ID都相同的记录。这便是复合主键的作用
create table user(guild_id int unsigned comment 服务器ID,user_id int unsigned not null comment 用户ID,score tinyint unsigned not null default 0 comment 用户积分,primary key(guild_id, user_id) -- guild_id user_id 为复合主键
);此时查看表结构会发现服务器id和用户id的两个键值在Key里面都有PRI属性即他们都是主键
而且即便我们的guild_id没有指定not null其的NULL属性依旧是NO。因为主键是不允许为NULL的
MariaDB [hello] create table user(- guild_id int unsigned comment 服务器ID,- user_id int unsigned not null comment 用户ID,- score tinyint unsigned not null default 0 comment 用户积分,- primary key(guild_id, user_id) -- guild_id user_id 为复合主键- );
Query OK, 0 rows affected (0.011 sec)MariaDB [hello] desc user;
----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------
| guild_id | int(10) unsigned | NO | PRI | NULL | |
| user_id | int(10) unsigned | NO | PRI | NULL | |
| score | tinyint(3) unsigned | NO | | 0 | |
----------------------------------------------------------
3 rows in set (0.003 sec)当我们插入时服务器id和用户id可以在各自列中重复。
MariaDB [hello] insert into user values (1,1,0);
Query OK, 1 row affected (0.008 sec)MariaDB [hello] insert into user values (1,2,0);
Query OK, 1 row affected (0.008 sec)MariaDB [hello] insert into user values (2,1,0);
Query OK, 1 row affected (0.001 sec)但如果你想在已经有服务器id为1用户id为1的记录的基础上再插入一条这样的记录那就会报错拒绝插入
MariaDB [hello] select * from user;
--------------------------
| guild_id | user_id | score |
--------------------------
| 1 | 1 | 0 |
| 1 | 2 | 0 |
| 2 | 1 | 0 |
--------------------------
3 rows in set (0.000 sec)MariaDB [hello] insert into user values (1,1,10);
ERROR 1062 (23000): Duplicate entry 1-1 for key PRIMARY6.自增auto_increment
自增人如其名就是MySQL会自动帮我们往这个列添加数据。比如一个序号新增一条记录就会将序号加一自增属性只能添加给整形
自增的列必须是主键
6.1 使用
自增的使用办法是在创建表的键值后添加这个约束
create table test_pri_3(id int unsigned auto_increment,name varchar(30) not null,primary key(id)
);查看表结构可以看到id列是主键非空且具有自增属性
MariaDB [hello] desc test_pri_3;
-------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
-------------------------------------------------------------
2 rows in set (0.003 sec)由于自增的列必须是主键所以我们不能将其和主键分开来使用
如果在设置自增的时候没有将这列同时设置为主键那么创建表的时候就会报错
MariaDB [hello] create table test_pri_4(- id int unsigned auto_increment,- name varchar(30) not null- );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key设置了自增之后我们同样可以显示的指定该列的值也可以不指定直接插入其他列的数据。MySQL会自动帮我们把当前记录1。
MariaDB [hello] insert into test_pri_3 values (1,李华);
Query OK, 1 row affected (0.007 sec)MariaDB [hello] select * from test_pri_3;
------------
| id | name |
------------
| 1 | 李华 |
------------
1 row in set (0.001 sec)MariaDB [hello] insert into test_pri_3 (name) values (小明);
Query OK, 1 row affected (0.001 sec)MariaDB [hello] select * from test_pri_3;
------------
| id | name |
------------
| 1 | 李华 |
| 2 | 小明 |
------------
2 rows in set (0.000 sec)多插入几条数据可以看到id列都成功自增了
MariaDB [hello] insert into test_pri_3 (name) values (小明3);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] insert into test_pri_3 (name) values (小明5);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] select * from test_pri_3;
-------------
| id | name |
-------------
| 1 | 李华 |
| 2 | 小明 |
| 3 | 小明3 |
| 4 | 小明5 |
-------------
4 rows in set (0.000 sec)6.2 自增是怎么判断当前所处序号位置的
自增的长度是按最大的那个数字开始自增的还是说有其他处理流程
先尝试往表里面主动插入一个1000为id的键值然后再不指定id的情况下再插入两行记录
MariaDB [hello] insert into test_pri_3 values (1000,test);
Query OK, 1 row affected (0.006 sec)MariaDB [hello] insert into test_pri_3 (name) values (test1);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] insert into test_pri_3 (name) values (test2);
Query OK, 1 row affected (0.001 sec)查询列表会发现在这1000之后的的记录全都是从1000开始增加的。
MariaDB [hello] select * from test_pri_3;
---------------
| id | name |
---------------
| 1 | 李华 |
| 2 | 小明 |
| 3 | 小明3 |
| 4 | 小明5 |
| 1000 | test |
| 1001 | test1 |
| 1002 | test2 |
---------------
7 rows in set (0.000 sec)莫非是依照最大的id来进行自增的吗我们再来试试。
先把最大id的记录删除再插入一个新数据
MariaDB [hello] delete from test_pri_3 where id 1002;
Query OK, 1 row affected (0.007 sec)MariaDB [hello] insert into test_pri_3 (name) values (test3);
Query OK, 1 row affected (0.006 sec)再次查询会发现自增的id是1003。可见其内部记录过一个id的最大值是按内部一个额外的记录来进行自增的而不是判断表中id列的最大值
MariaDB [hello] select * from test_pri_3;
---------------
| id | name |
---------------
| 1 | 李华 |
| 2 | 小明 |
| 3 | 小明3 |
| 4 | 小明5 |
| 1000 | test |
| 1001 | test1 |
| 1003 | test3 |
---------------
7 rows in set (0.001 sec)那这个额外的记录在哪里呢
show create table test_pri_3;使用如上命令查看创建表的sql语句你会发现紧跟在表之后的就有一个自增的字段AUTO_INCREMENT1004
MariaDB [hello] show create table test_pri_3;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| test_pri_3 | CREATE TABLE test_pri_3 (id int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(30) NOT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT1004 DEFAULT CHARSETutf8 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)这就是MySQL中对自增字段当前值的定义这里存放的就是下一个插入的记录其id的自增值。每次插入一个信的记录这里的自增值就会对应变换为下一个记录应该是多少的数值
举个例子如果我们想让一个系统的ID从10001开始自增那么我们就可以在创建了表之后直接往表里面插入一个id为10000的记录。在这之后创建的其他记录id就会从10001开始自增了 6.3 索引
讲到这里顺带一提MySQL中索引的概念
索引 在关系数据库中索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针然后根据您指定的排序顺序对这些指针排序。 数据库使用索引以找到特定值然后顺指针找到包含该值的行。
这样可以使对应于表的SQL语句执行得更快可快速访问数据库表中的特定信息。
索引本质上就是一个用空间换时间的套路。在当前多并发的业务中执行速度远比占用内存、磁盘多少来的更重要
7.唯一键unique
唯一键对字段的约束那就是这一列的数据都不能出现相同的。
看起来和主键有点相似但实际上其是独立于主键之外的一种唯一性的约束。和主键的区别在于唯一键可以为NULL
要知道一个表里面的主键只能设置一个。复合主键在某些时候并不能满足我们的需求。于是MySQL就在主键之外额外提供了唯一键的约束让我们可以给其他列设置唯一性。
至于为什么要这么做就好比一个免责声明我这列的数据设置了唯一那么就不可能接受两个相同的记录比如用户表中两个人却有相同手机号是不应该的如果你的业务中出现了拒绝插入的报错那么就应该去看业务处理代码中是哪里有BUG而不应该怪罪MySQL没有维护唯一性或者拒绝记录的插入。
7.1 单独唯一键
假设我有一个平台类似于qq一样使用了一个qq号作为用户的主键但为了实名认证我们又要求一个身份证只能注册一个账户。此时就无法用复合主键来解决这个问题因为复合主键是允许其中某一列有重复的而我们需要的是用户账户编号和用户身份证号都不能重复 同时在用户的联系方式中两个用户的电话号码、微信号也不应该出现相同如果要添加电话号码的键值也可以将其设置为unique 此时就可以将用户编号作为主键用户身分证号设置unique作为唯一键
create table test_unique_1(no int unsigned not null primary key,name varchar(30) not null,id_card varchar(30) not null unique
);查看表结构唯一键的列Key的约束是UNI即unique的缩写
MariaDB [hello] desc test_unique_1;
------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------
| no | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| id_card | varchar(30) | NO | UNI | NULL | |
------------------------------------------------------
3 rows in set (0.001 sec)当我们往这个表中的主键列或者id_card列插入相同记录时MySQL都会拒绝插入
MariaDB [hello] insert into test_unique_1 values (1,李华,123456);
Query OK, 1 row affected (0.006 sec)MariaDB [hello] insert into test_unique_1 values (1,李华,1234567);
ERROR 1062 (23000): Duplicate entry 1 for key PRIMARYMariaDB [hello] insert into test_unique_1 values (3,小明,123456);
ERROR 1062 (23000): Duplicate entry 123456 for key id_card7.2 复合唯一键
唯一键也能设置多列效果和复合主键相同这里不再说明
create table user(user_no int unsigned primary key auto_increment comment 用户编号 主键,guild_id int unsigned comment 服务器ID,user_id int unsigned not null comment 用户ID,score tinyint unsigned not null default 0 comment 用户积分,unique(guild_id, user_id) -- guild_id user_id 为复合唯一键
);表中对guild_id和user_id的约束就变成了MUL如下图所示
其中能观察到guild_id因为没有设置not null其NULL一栏为YES代表这列可以为NULL唯一键可以为NULL主键不能
而MUL则代表目前允许多行在此列具有相同的值但guild_id和user_id都相同的两行是不允许存在的
MariaDB [hello] desc user;
-------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------------
| user_no | int(10) unsigned | NO | PRI | NULL | auto_increment |
| guild_id | int(10) unsigned | YES | MUL | NULL | |
| user_id | int(10) unsigned | NO | | NULL | |
| score | tinyint(3) unsigned | NO | | 0 | |
-------------------------------------------------------------------
4 rows in set (0.001 sec)如下最后的插入和第一次的插入中的guild_id和user_id相同于是就出现了拒绝插入的报错
MariaDB [hello] insert into user values (1,1,1,20);
Query OK, 1 row affected (0.006 sec)MariaDB [hello] insert into user values (2,1,2,21);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] insert into user values (3,2,3,22);
Query OK, 1 row affected (0.006 sec)MariaDB [hello] insert into user values (4,1,1,23);
ERROR 1062 (23000): Duplicate entry 1-1 for key guild_id7.3 在MySQL中MUL、PRI和UNI是什么
从Mysql 5.7官网文档可知
如果键是PRI则列是主键或多列主键中的列之一如果键是UNI则该列是唯一索引的第一列(唯一索引允许多个空值但可以通过检查NULL字段来判断该列是否允许空)如果键为MUL则该列是非唯一索引的第一列其中允许在列中多次出现给定值
8.外键
8.1 说明
外键是用来定义两张表中某些字段的关系并来约束记录的
基础语法如下在创建表的时候使用。设置外键的表是从表
foreign key (字段名) references 主表(列)比如下图中学生表中每个学生的班级编号都对应了班级表中班级的id此时我们就可以将班级表的id设置为学生表中class_id的外键
虽然我们可以将class表中的数据直接插入到学生表里面但是这样并不合理。如果我们针对一个班级或者针对一个学生的字段有非常多的话将这两张表合起来是非常不方便的。因为同一个班级会有很多学生合并表之后就相当于同一个班级的学生他们的班级列的信息全是相等的这就存在了无意义的资源占用冗余。
相比之下分表了之后再采用外键的方式来绑定两个字段是更好的选择 在上图的情况中myclass是主表stu是一个从表
-- 主表 班级
create table myclass (id int primary key comment 班级号,name varchar(30) not null comment 班级名
);
-- 从表 学生
create table stu (id int primary key,name varchar(30) not null comment 学生名,class_id int,foreign key (class_id) references myclass(id) -- 设置从表外键约束
);8.2 测试
创建好表之后先来看看学生表i的属性可以看到class_id表的key是一个MUL和前面设置复合唯一键的时候相同。
MariaDB [hello] desc stu;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
--------------------------------------------------
3 rows in set (0.001 sec)当我们尝试往学生表里面插入一个数据的时候会报错
MariaDB [hello] insert into stu values (1,李华,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (hello.stu, CONSTRAINT stu_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id))这是因为你设置的这个记录中学生的班级编号2压根在班级表里面不存在。一个不存在的班级怎么可以有学生呢所以自然就拒绝了你的插入。
所以要想插入学生我们需要保证这个学生的记录所在班级是存在于班级表里面的这样就实现了学生和班级N对1的绑定。
MariaDB [hello] insert into myclass values (1,少华班);
Query OK, 1 row affected (0.002 sec)MariaDB [hello] insert into stu values (1,李华,1);
Query OK, 1 row affected (0.005 sec)MariaDB [hello] select * from myclass;
---------------
| id | name |
---------------
| 1 | 少华班 |
---------------
1 row in set (0.001 sec)MariaDB [hello] select * from stu;
----------------------
| id | name | class_id |
----------------------
| 1 | 李华 | 1 |
----------------------
1 row in set (0.000 sec)这里我又多插入了几个数据
MariaDB [hello] select * from stu;
-------------------------
| id | name | class_id |
-------------------------
| 1 | 李华 | 1 |
| 2 | 小明 | 1 |
| 3 | 小流 | 1 |
| 4 | 小流2 | 2 |
| 5 | 猪猪侠 | 2 |
| 6 | 苗条俊 | 2 |
-------------------------
6 rows in set (0.000 sec)MariaDB [hello] select * from myclass;
---------------
| id | name |
---------------
| 1 | 少华班 |
| 2 | 你好班 |
---------------
2 rows in set (0.000 sec)此时还有另外一个问题如果这个班级有学生我们可以把这个班级删掉吗
考虑看来肯定是不行的既然没有这个班级你不能插入对应的学生。那么这个班级有学生的时候你也不应该把班级删除。二者是相互的逻辑在MySQL里面也是如此当一个班级有对应的学生的时候是不允许删除的。
MariaDB [hello] delete from myclass where id 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (hello.stu, CONSTRAINT stu_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id))只有当这个班级没有学生了才能从班级表中被删除
MariaDB [hello] delete from stu where class_id 2;
Query OK, 3 rows affected (0.005 sec)MariaDB [hello] delete from myclass where id 2;
Query OK, 1 row affected (0.006 sec)MariaDB [hello] 更新班级id同样是不允许的因为在学生表中有学生绑定了这个班级。MySQL并不能做到帮我们直接更新所有学生的班级号。
MariaDB [hello] update myclass set id 3 where name 你好班;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (hello.stu, CONSTRAINT stu_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id))8.3 外键约束
在上面的情况中我们完全可以建立两个没有外键关系的独立表在代码层进行两者关系的维护。
但是这样这两个表的操作依旧是独立的MySQL是不知道这两个表之间有毛线关联的此时你就可以往不存在的班级里面插学生把还有学生的班级删掉最终就乱了套了
自己维护两个表的信息有关联加上外键MySQL的约束
两者合一才是外键的完全体
所以我们才需要在MySQL中将这两个表之间定义外键的约束让MySQL协助我们维护两张表中的数据关系。
注意虽然此时MySQL会约束我们的操作但实际上的业务流程还是需要程序袁在代码中处理。比如不要往MySQL中插入班级不存在的学生MySQL只会拒绝插入并不能帮你把班级给修正
The end
基础的约束操作就是这些了有其他会用到的日后再新增
