day 18 MySQL

1. 数据库·表的操作

1.1 表的介绍

数据库中的表相当于一个文件，表中的一条记录就是一行数据，一行数据是由N多个列组成，每列的列名称被称之为字段。

1.2 创建表

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件],
...
);

注意：

1. 在同一张表中，字段名是不能相同的。
2. 宽度和约束条件可选、非必须，宽度指的就是字段长度约束，例如：char(10)里面的10
3. 字段名和类型是必须的。

代码示例：

简单的插入数据

产看所有的表

show tables;

查看指定的表结构；

desc 表名;

代码示例：

查看表中所有数据

select * from 表名;

1.3 MySQL的基础数据类型

1.3.1 数值类型

整数类型：TINYINT, SMALLINT , MEDIUMINT, INT ,BIGINT

作用：id，学号，年龄，身份证号，电话号等等。

注意：

​ 对于整型来说，数据类型后面的宽度并不是存储长度限制，而是显示限制，假如：int(8),那么显示不够8位时则用0来填充，够8位则正常显示，通过zerofill来测试，存储长度还是int的4个字节长度。默认的显示宽度就是能够存储的最大的数据的长度，比如：int无符号类型，那么默认的显示宽度就是int(10)，有符号的就是int(11)，因为多了一个符号，所以我们没有必要指定整数类型的数据，没有必要指定宽度，因为默认的就能够将你存的原始数据完全显示。

• 测试宽度问题

小结：整型类型的宽度限制不是取值范围的限制，而是显示宽度的限制，（并且超出了也不影响），所以我们用整型类型一般不设置宽度范围。

• 有无符号问题

  对于整型类型来说，一般默认都是有符号的。（我的MySQL现在是非严格模式，如果设置的是严格模式，插入的超出范围的数据则报错。）

• 无符号设置

  

1.3.2 浮点型

浮点型：float、double、decimal

**作用：**身高、体重、钱、圆周率等等。

语法：

1. float[(M,D)] [UNSIGNED] [ZEROFILL]

   定义：

   ​ 单精度浮点型（非准确小数值），M是整数部分+小数部分的总个数，D是小数点后个数。M最大值为255，D最大值为30，例如：

   float(255,30)

   ​ 有符号：

   ​ -3.402823466E+38 to -1.175494351E-38,
   ​ 1.175494351E-38 to 3.402823466E+38

   ​ 无符号：

   ​ 1.175494351E-38 to 3.402823466E+38

   ​ **精确度：**随着小数的增多，精度变得不准确

2. 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要高，但也会变得不准确

3. decimal [(m[,d])] [unsigned] [zerofill]

   定义：

   ​ 准确的小数值，m是整数部分+小数部分的总个数（负号不算），d是小数点后个数。m最大值为65，d最大值为30，比float和double的整数个数少，但是小数位数都是30位。

   精确度：

   ​ 随着小数的增多，精度始终准确

   ​ 对于精确数值计算时需要用此类型

   ​ decimal能够存储精确值的原因在于其内部按照字符串存储。

   精确度由高到低：decimal、double、float、

   ​ decimal精度高，但是整数位少

   ​ float和double精度低，但是整数位数多

   float已经满足绝大多数的场景了，但是什么导弹、航线等要求精度非常高，所以还是需要按照业务场景自行选择，如果又要精度高又要整数位数多，那么你可以直接使用字符串来存。

   • 测试

 小结：float、double、decimal区别？

 随着小数的位数增多，他们的精准度越来越高。

1.3.3 日期时间类型

类型：date、time、datetime、timestamp、year

作用：注册登录时间，生日，出生年月，入学入职时间等等。

• 语法

注意：

1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入

2. 插入年份时，尽量使用4位值、

3. 插入两位年份时，<=69，以20开头，比如50，结果2050

   ​ <=70，以19开头，比如71，结果1970

1.3.4 字符串类型

类型：char varchar

作用：姓名 各种信息等等。

• 语法

  char类型：定长，简单粗暴，浪费空间，存取速度快

  ​ 字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）

  ​ 存储：

  ​ 存储char类型的值时，会往右填充空格来满足长度。

  ​ 例如：指定长度为10，存>10个字符则报错（严格模式下），存<10个字符则用空格 填充直到凑够10个字符存储。

  varchar类型：变长，精准，节省空间，存取速度慢

  ​ 字符长度范围：

  ​ 0-65535（如果大于21845会提示用其他类型。mysql行（读hang）最大限 制为65535字节，字符编码为utf8

  ​ 官网解释：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=8bit2**8最大波啊是 数字为255）

  ​ 如果真实数据>255bytes则需要2bytes的前缀（2bytes=16bit2**16最大表示 数字为65535

char和varchar的性能对比：

以char(5)和varchar(5)来比较，加入我要存的三个人名：sb1，sb22，sb333

char：

​ **优点：**简单粗暴，不管你是多长的数据，我就按照规定的长度来存，5个5个的存，三个人就会类似这种存储：sb1、sb22、sb333，中间是空格补全，取数据的时候5个5个的取，简单粗暴速度快

​ **缺点：**貌似浪费空间，并且我们将来存储的数据的长度可能会参差不齐

varchar：

​ varchar类型不定长存储数据，更为精简和节省空间

​ 例如存上面三个人名的时候类似于是这样的：sb1sb22sb333

​ 所以，varchar在存数据的时候，会在每一个数据前面加上一个头，这个头是1-2bytes的数据，这个数据指的是后面跟着的这个数据的长度，1 bytes能表示2 8=256，两个bytes表示2 16=65536，能表示0-65536的数字，所以varchar在存储的时候是这样的：1bytes+sb1+1bytes+sb22+1bytes+sb333,所以varchar在存储的时候是这样的：1bytes+sb1+bytes+sb22+1bytes+sb333,所以存的时候会比较麻烦，导致效率比char慢，取的时候也慢，先拿长度，再取数据。

**优点：**节省了一些硬盘空间，一个ASCII码的字符用一个bytes长度就能表示，但是也并不一定比char省，看一下官网给出的一个表格对比数据，当你存的数据正好是你规定的字段长度的时候，varchar反而占用的空间比char要多。

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

**缺点：**存取速度都慢

• 小结

  总结：

  ​ 需要根据业务需求来选择中那种类型来存

  其实在多数的用户量少的工作场景中char和varchar效率差别不是很大，最起码给用户的感知不是很大，并且其实软件级别的慢远比不上硬件级别的慢，所以你们公司的运维发现项目慢的时候会加内存、换更牛批的硬盘，项目的效率会提升很多，但是我们作为专业人士，我们应该提出来这样的技术点来提高效率。

  ​ 但是对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度（所有数据行都使用指向数据列值的头指针），因此本质上，使用固定长度的char列不一定比使用可变长度varchar列性能更好。因此，主要的性能因素是数据行使用的存储总量。由于char平均占用的空间多于varchar，因此使用varchar来最小化需要处理的数据行的存储总量和磁盘IO是比较好的。

1.3.5 枚举集合类型

字段的值只能在给定范围中选择，如单选框，多选框，如果你在应用程序或者前端不做选项限制，在MySQL的字段里面也能做限制

​ enum单选，只能在给定的范围内选一个值，如性别sex男 male/女 female

​ set多选 在给定的范围内可以选择一个或一个以上的值（爱好1，爱好2…）

• 测试

1.4 MySQL表的约束

1.4.1 总体介绍

介绍

​ 约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

主要分为

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

说明：

1. 是否允许为空，默认null，可设置not null，字段不允许为空，必须赋值

2. 字段是否有默认值，缺省的默认值是null，如果插入记录时不给字段赋值，此字段使用默认值。

   sex enum(‘male’,‘female’) not null default ‘male’

   age int unsigned not null default 20 必须为正值（无符号）不允许为空 默认是20

3. 是否是key

   主键 primary key

   外键 foreign key

   索引 （index，unique…）

1.4.2 not null default

not null 该字段不能为空；default设置该字段的默认值

• 测试

1.4.3 unique

独一无二，唯一的属性

身份证号，学号，手机号等等。

• 测试

  • 常见唯一字段

• 也可以创建联合唯一。