mysql表操作

约束操作

一 介绍

约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性

主要分为：

primary key (pk) 标识该字段为该表的主键，可以唯一的标识记录foreign key (fk) 标识该字段为该表的外键not null 标识该字段不能为空unique key (uk) 标识该字段的下的记录是唯一的值auto_increment 标识该字段的值自动增长(整数类型，而且为主键)default 为该字段设置默认值unsigned 无符号zerofill 使用0填充

说明：

1. 是否允许为空，默认null,可设置not null,字段不允许为空，必须赋值2. 字段是否有默认值，默认值是null,如果插入记录不给字段赋值，此字段使用默认值create table t1(sex  not null default'male');create table t2(age int unsigned not null defult 20) 必须为正值(无符号) 不允许为空 默认是203.是否是key主键 primary key外键 foreign key索引 (index,unique...)

二 not null与default

是否可空，null表示空，非字符串
not null - 不可空
null - 可空

默认值，创建列时可以指定默认值，当插入数据时如果为主动设置，则自动添加默认值

=============not null==============mysql> create table t1(id int); #此时id默认可以插入空mysql> decs t1; #查看表结构+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | YES  | | NULL||+-------+---------+------+-----+---------+-------+mysql> insert t1 values(); #插入空成功Query OK, 1 row affected (0.00 sec)mysql> create table t2(id int not null); #设置字段id不能为空mysql> desc t2;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+mysql> insert t2 values(); #插入没成功，显示需要默认值ERROR 1364 (HY000): Field 'id' doesn't have a default value=============defualt==============mysql> create table t3(id int default 1);mysql> insert t3 values();Query OK, 1 row affected (0.00 sec)mysql> alter table t3 modify id int not null default 1;mysql> insert t3 values();Query OK, 1 row affected (0.00 sec)#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

三 unique

==========设置唯一约束 unique==========方法一(单列唯一)：create table t1(id int,name varchar(10) unique,work varchar(10));mysql> insert t1 values(1,'egon','aaaa');Query OK, 1 row affected (0.00 sec)mysql> insert t1 values(2,'egon','bbbb');ERROR 1062 (23000): Duplicate entry 'egon' for key 'name'#设置了单列唯一指在该字段的记录不能重复必须都唯一。方法二(联合唯一)：create table server(id int,name varchar(10),inip varchar(10),port int,unique(ip,port),unique(name));mysql> insert server values(1,'egon','11.11.11',8080);Query OK, 1 row affected (0.01 sec)mysql> insert server values(1,'arther','11.11.11',8181);Query OK, 1 row affected (0.00 sec)mysql> insert server values(1,'arther','11.11.11',8181);ERROR 1062 (23000): Duplicate entry '11.11.11-8181' for key 'ip'#设置联合的两个字段，其下新写入的记录如果是完全一样则报错，最多有一个字段下的记录是重复的能成功写入。ps:# not null 和unique的化学反应=>会被识别成表的主键create table t4(id int,name varchar(10) not null unique);create table t5(id int,name varchar(10) unique);

四 primary key

主键primary key
特点
1、主键的约束效果是not null+unique
2、innodb表有且只有一个主键，但是该主键可以联合主键

了解：
create table t7(
id int,
name varchar(5),
primary key(id,name)
);

主键做为架构整个表的主要参数，一般默认使用id作为字段表示，并且配上传入空时则传入默认值以及自动增值的方法。

五 auto_increment

约束字段为自动增长，被约束的字段必须同时被Key约束。

#不指定id,则自动增长create table student(id int primary key auto_increment,name varchar(10),sex enum('male','female') default 'male');mysql> desc student+-------+-----------------------+------+-----+---------+----------------+| Field | Type  | Null | Key | Default | Extra  |+-------+-----------------------+------+-----+---------+----------------+| id| int(11)| NO| PRI | NULL|auto_increment |  | name  | varchar(10)| YES  | | NULL||| sex| enum('male','female') | YES  | | male||+-------+-----------------------+------+-----+---------+----------------+mysql> insert student(name) values ('egon');Query OK, 1 row affected (0.00 sec)#直接传入非默认的name,其他标题自动以默认值插入#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长mysql> delete from student;Query OK, 4 rows affected (0.00 sec)mysql> select * from student;Empty set (0.00 sec)mysql> insert into student(name) values('ysb');mysql> select * from student;+----+------+------+| id | name | sex  |+----+------+------+|  8 | ysb  | male |+----+------+------+#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它

六 foreign key

1. 快速理解foreign key

员工信息表有三个字段：工号 姓名 部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：

我们完全可以定义一个部门表

然后让员工信息表关联该表，如何关联，即foreign key

#表类型必须是innodb存储的(由于我在配置文件已经指定所以不必重复写)create table dep(id int primary key auto_increment,name varchar(20) not null);#由于部门是被关联，所以需要先建立，等着员工去关联#d_id外键，关联父表(dep主键id)，同步更新，同步删除create table emp(id int primary key auto_increment,name varchar(20) not null,dep_id int,foreign key(dep_id) references dep(id) on delete cascade on update cascade);#先往父表dep中插入记录mysql>insert dep(name) values('sell'),('tech');#再往子表emp中插入记录mysql> insert emp values-> (1,'egon',1),-> (2,'alex1',2),-> (3,'alex2',2),-> (4,'alex3',2);#删除父表dep,子表同步更新mysql> delete from dep where id=1;mysql> select * from emp;+----+-------+--------+| id | name  | dep_id |+----+-------+--------+|  2 | alex1 |  2 ||  3 | alex2 |  2 ||  4 | alex3 |  2 |+----+-------+--------+#更新父表dep,子表中的内容也跟着改mysql> update dep set id=222 where id=2;mysql> select * from emp;+----+-------+--------+| id | name  | dep_id |+----+-------+--------+|  2 | alex1 |222 ||  3 | alex2 |222 ||  4 | alex3 |222 |+----+-------+--------+

2.如何找出两张表之间的关系

分析步骤：1.先站在左表的角度去找是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段(通常是id)，由此建立左表与右表的关联。2.再站在右表的角度去找是否右表的多条记录可以对应左表的一条记录如果是，则证明右表的一个字段foreign key 左表一个字段(通常是id)，由此建立右表与左表的关联。3.总结#多对一:如果只有步骤1成立，则是左表多对一右表如果只有步骤2成立，则是右表多对一左表#多对多如果步骤1与步骤2同时成立，则证明这两张表互相多对一，即多对多，需要定义一个第三张表专门放这两张表的关联关系。#一对一如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可。

3.建立表之间的关系

#一对多两张表：出版社， 书一对多：一个出版社可以出很多书关联方式：foreign key+一张新的表#先创建被关联者create table press(id int primary key auto_increment,name varchar(20));#再创建主动关联者create table book(id int primary key auto_increment,name varchar(20),pid int not null,foreign key(pid) references press(id) on delete cascade on update cascade);insert into press(name) values('beijing press'),('people press'),('wuhan press');insert into book(name,pid) values('best life',1),('dream true',2),('dog die',2),('snow white',3),('destory life',3);mysql> select * from book;+----+--------------+-----+| id | name | pid |+----+--------------+-----+|  1 | best life|1 ||  2 | dream true|2 ||  3 | dog die  |2 ||  4 | snow white|3 ||  5 | destory life |3 |+----+--------------+-----+#严格意义上在字段name应该设置一个唯一约束unique,保证不能重复创建书本名确保一对多的可读性，不然将会出现如下的多对多的情况，造成阅读的混乱。mysql> insert book values(6,'dog die',1);mysql> select * from book;+----+--------------+-----+| id | name | pid |+----+--------------+-----+|  1 | best life|1 ||  2 | dream true|2 ||  3 | dog die  |2 ||  4 | snow white|3 ||  5 | destory life |3 ||  6 | dog die  |1 |+----+--------------+-----+

#多对多两张表：作者，书多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多关联方式：foreign key + 一张新的表create table author(id int primary key auto_increment,name varchar(20));create table book(id int primary key auto_increment,name varchar(20));#最后创建新表去主动关联create table author2book(id int primary key auto_increment,aid int not null,bid int not null,foreign key(aid) references author(id) on delete cascade on update cascade,foreign key(bid) references book(id) on delete cascade on update cascade,evaluate varchar(20));#可以在新表建立两者关联的东西，如对这本书以及作者的评价insert into book(name) values('best life'),('dream true'),('dog die'),('snow white'),('destory life');insert into author(name) values('arther'),('egon'),('tank');#建立通过ID作者与书的关系1.arther:	1.('best life'),	2.('dream true'),	2.('egon'):	3.('dog die'),	4.('snow white'),	5.('destory life');3.('tank'):	2.('dream true'),	3.('dog die'),	4.('snow white'),	insert into author2book(aid,bid,evaluate) values(1,1,'good'),(1,2,'perfect'),(2,3,'bad'),(2,4,'not bad'),(2,5,'so so'),(3,2,'good'),(3,3,'bad'),(3,4,'good');mysql> select * from author2book;+----+-----+-----+----------+| id | aid | bid | evaluate |+----+-----+-----+----------+|  1 |1 |1 | good ||  2 |1 |2 | perfect  ||  3 |2 |3 | bad  ||  4 |2 |4 | not bad  ||  5 |2 |5 | so so||  6 |3 |2 | good ||  7 |3 |3 | bad  ||  8 |3 |4 | good |+----+-----+-----+----------+

#一对一两张表：顾客表和会员表一对一：一个顾客是一个客户，一个客户有可能成为会员，即一对一的关系。因身份的转换，所用的表也不同。关联方式：foreign key+unique#现有顾客再有会员，所以先创建顾客，然后由会员去关联顾客create table customer(id int primary key auto_increment,name varchar(20));create  table member(id int primary key auto_increment,name varchar(20),cid int unique,foreign key(cid) references customer(id) on delete cascade on update cascade); #设置了unique代表此字段下的记录唯一，就不会出现一对多的情况insert customer(name) values('arther'),('egon'),('tank');insert member(name,cid) values('arther',1),('egon',2),('tank',3);mysql> select * from member;+----+--------+------+| id | name| cid  |+----+--------+------+|  1 | arther |1 ||  2 | egon|2 ||  3 | tank|3 |+----+--------+------+

创建表

#语法：create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]);#注意：1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选3. 字段名和类型是必须的

查看表结构

MariaDB [db1]> describe t1; #查看表结构，可简写为desc 表名+-------+-----------------------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id| int(11)| YES  | | NULL||| name  | varchar(50)| YES  | | NULL||| sex| enum('male','female') | YES  | | NULL||| age| int(3)| YES  | | NULL||+-------+-----------------------+------+-----+---------+-------+MariaDB [db1]> show create table t1\G; #查看表详细结构，可加\G

修改表

语法：1. 修改表名  ALTER TABLE 表名RENAME 新表名;2. 增加字段  ALTER TABLE 表名  ADD 字段名  数据类型 [完整性约束条件…],  ADD 字段名  数据类型 [完整性约束条件…];  ALTER TABLE 表名  ADD 字段名  数据类型 [完整性约束条件…]  FIRST;  ALTER TABLE 表名  ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;3. 删除字段  ALTER TABLE 表名DROP 字段名;4. 修改字段  ALTER TABLE 表名MODIFY  字段名 数据类型 [完整性约束条件…];  ALTER TABLE 表名CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];  ALTER TABLE 表名CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）mysql> create table new_service select * from service;只复制表结构mysql> select * from service where 1=2;//条件为假，查不到任何记录Empty set (0.00 sec)mysql> create table new1_service select * from service where 1=2;  Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Waings: 0mysql> create table t4 like employees;

删除表

DROP TABLE 表名;

数据类型

一介绍

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

详细参考：

• http://www.runoob.com/mysql/mysql-data-types.html
• http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

mysql常用数据类型概览

#1. 数字：整型：tinyinit  int  bigint小数：float ：在位数比较短的情况下不精准double ：在位数比较长的情况下不精准0.000001230123123123存成：0.000001230000decimal：（如果用小数，则用推荐使用decimal）精准内部原理是以字符串形式去存#2. 字符串：char（10）：简单粗暴，浪费空间，存取速度快root存成root000000varchar：精准，节省空间，存取速度慢sql优化：创建表时，定长的类型往前放，变长的往后放比如性别比如地址或描述信息>255个字符，超了就把文件路径存放到数据库中。比如图片，视频等找一个文件服务器，数据库中只存路径或url。#3. 时间类型：最常用：datetime#4. 枚举类型与集合类型

二 数值类型

1 整数类型

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：存储年龄，等级，id，各种号码等

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了

默认的显示宽度，都是在最大值的基础上加1

nt的存储宽度是4个Bytes，即32个bit，即2**32

无符号最大值为：4294967296-1

有符号最大值：2147483648-1

有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的

最后：整形类型，其实没有必要指定显示宽度，使用默认的就ok

2 浮点型

定点数类型 DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE

作用：存储薪资、身高、体重、体质参数等

======================================#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]定义：单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30有符号：-3.402823466E+38 to -1.175494351E-38,1.175494351E-38 to 3.402823466E+38无符号：1.175494351E-38 to 3.402823466E+38精确度：**** 随着小数的增多，精度变得不准确 ****======================================#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]定义：双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30有符号：-1.7976931348623157E+308 to -2.2250738585072014E-3082.2250738585072014E-308 to 1.7976931348623157E+308无符号：2.2250738585072014E-308 to 1.7976931348623157E+308精确度：****随着小数的增多，精度比float要高，但也会变得不准确 ****======================================decimal[(m[,d])] [unsigned] [zerofill]定义：  准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。精确度：**** 随着小数的增多，精度始终准确 ****对于精确数值计算时需要用此类型decaimal能够存储精确值的原因在于其内部按照字符串存储。

三 日期类型

DATE TIME DATETIME TIMESTAMP YEAR

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

datetime与timestamo的差别

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。datetime与timestamp的区别

mysql> create table t1(x datetime not null default now()); # 需要指定传入空值时默认取当前时间Query OK, 0 rows affected (0.01 sec)mysql> create table t2(x timestamp); # 无需任何设置，在传空值的情况下自动传入当前时间Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 values();Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values();Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+---------------------+| x|+---------------------+| 2018-07-07 01:26:14 |+---------------------+1 row in set (0.00 sec)mysql> select * from t2;+---------------------+| x|+---------------------+| 2018-07-07 01:26:17 |+---------------------+1 row in set (0.00 sec)

！！！注意：针对datetime或者timestamp如果是用作注册时间，那么指定not null default now()自动填充时间即可，如果是用作更新时间那么需要额外指定on update now()，该配置timestamp自带

四 字符串类型

#官网：https://dev.mysql.com/doc/refman/5.7/en/char.html#注意：char和varchar括号内的参数指的都是字符的长度#char类型：定长，简单粗暴，浪费空间，存取速度快字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）存储：存储char类型的值时，会往右填充空格来满足长度例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储检索：在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）#varchar类型：变长，精准，节省空间，存取速度慢字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）存储：varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

总结

#InnoDB存储引擎：建议使用VARCHAR类型单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。但对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。#其他字符串系列（效率：char>varchar>text）TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXTBLOB 系列TINYBLOB BLOB MEDIUMBLOB LONGBLOB BINARY系列 BINARY VARBINARYtext：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.