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) 必须为正值(无符号) 不允许为空 默认是20
3.是否是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 Warnings: 0
mysql> 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.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存
#2. 字符串：
char（10）：简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
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-308
2.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 LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
text：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.
