区块链技术博客
www.b2bchain.cn

数据库篇·第四章[2]·MYSQL求职学习资料

本文介绍了数据库篇·第四章[2]·MYSQL求职学习资料,有助于帮助完成毕业设计以及求职,是一篇很好的资料。

对技术面试,学习经验等有一些体会,在此分享。

[TOC]

1. MYSQL数据类型

MySQL 的数据类型有大概可以分为 5 种,分别是:
| 类型 | 具体 |
| ——– | ——– |
| 整数型 | TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT |
| 浮点型和定点数型 | FLOAT 和 DOUBLE,定点数类型为 DECIMAL |
| 日期和时间类型 | YEAR、TIME、DATE、DATETIME 和 TIMESTAMP |
| 字符串型 | CHAR、VARCHAR、TEXT、ENUM 和 SET 等 |
| 二进制类型 | BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB |

1.1 整数型

整数型字段可以添加 AUTO_INCREMENT 自增约束条件;
整型数据类型也可以在定义表结构时指定所需的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值;

显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。
根据实际需要选择最合适的类型,这样有利于提高查询的效率和节省存储空间。

|类型名称|说明|存储需求|有符号|无符号|
| ———|———|———|———|
|TINYINT|很小的整数|1个字节|-128~127|0~255|
|SMALLINT|小的整数|2个宇节|-32768~32767|0~65535|
|MEDIUMINT|中等大小的整数 |3个字节|-8388608~8388607|0~16777215|
|INT (INTEGHR)|普通大小的整数|4个字节 |-2147483648~2147483647|0~4294967295|
|BIGINT|大整数 |8个字节|-9223372036854775808~9223372036854775807|0~18446744073709551615|

1.2 浮点型

浮点类型有两种,分别是单精度浮点数(FLOAT)和双精度浮点数(DOUBLE);定点类型只有一种,就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。

浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

类型名称 说明 存储需求
FLOAT 单精度浮点数 4 个字节
DOUBLE 双精度浮点数 8 个字节
DECIMAL (M, D),DEC 压缩的“严格”定点数 M+2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE。DECIMAL 实际上是以字符串的形式存放的,DECIMAL 可能的最大取值范围与 DOUBLE 相同,但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D,则取值范围将随 M 的变大而变大。在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,
DECIMAL 的存储空间并不是固定的,而由精度值 M 决定,占用 M+2 个字节。
不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的范围;缺点是会引起精度问题。
另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

1.3 时间和日期类型

数据库篇·第四章[2]·MYSQL

  • YEAR 类型
    YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR,如下所示:
    以 4 位字符串或者 4 位数字格式表示的 YEAR,范围为 ‘1901’~’2155’。输入格式为 ‘YYYY’ 或者 YYYY,例如,输入 ‘2010’ 或 2010,插入数据库的值均为 2010。
    以 2 位字符串格式表示的 YEAR,范围为 ’00’ 到 ’99’。’00’~’69’ 和 ’70’~’99’ 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 YEAR 值。’0′ 与 ’00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
    以 2 位数字表示的 YEAR,范围为 1~99。1~99 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 YEAR 值。注意,在这里 0 值将被转换为 0000,而不是 2000。
    提示:两位整数范围与两位字符串范围稍有不同。例如,插入 3000 年,读者可能会使用数字格式的 0 表示 YEAR,实际上,插入数据库的值为 0000,而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ’00’,才可以被正确解释为 3000,非法 YEAR值将被转换为 0000。

  • TIME 类型
    TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。

TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于 24 小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。

可以使用各种格式指定 TIME 值,如下所示。
‘D HH:MM:SS’ 格式的字符串。还可以使用这些“非严格”的语法:’HH:MM:SS’、’HH:MM’、’D HH’ 或 ‘SS’。这里的 D 表示日,可以取 0~34 之间的值。在插入数据库时,D 被转换为小时保存,格式为 “D*24+HH”。
‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值,假定是有意义的时间。例如,’101112′ 被理解为’10:11:12’,但是 ‘106112’ 是不合法的(它有一个没有意义的分钟部分),在存储时将变为 00:00:00。
提示:为 TIME 列分配简写值时应注意:如果没有冒号,MySQL 解释值时,假定最右边的两位表示秒。(MySQL 解释 TIME 值为过去的时间而不是当前的时间)。例如,读者可能认为 ‘1112’ 和 1112 表示 11:12:00(即 11 点过 12 分钟),但MySQL 将它们解释为 00:11:12(即 11 分 12 秒)。同样 ’12’ 和 12 被解释为00:00:12。相反,TIME 值中如果使用冒号则肯定被看作当天的时间,也就是说,’11:12′ 表示 11:12:00,而不是 00:11:12。

  • DATE 类型 DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示年,MM 表示月,DD 表示日。

在给 DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可。如下所示:
以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期,取值范围为 ‘1000-01-01’~’9999-12-3’。例如,输入 ‘2015-12-31’ 或者 ‘20151231’,插入数据库的日期为2015-12-31。
以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期,在这里YY表示两位的年值。MySQL 解释两位年值的规则:’00~69′ 范围的年值转换为 ‘2000~2069’,’70~99’ 范围的年值转换为 ‘1970~1999’。例如,输入 ’15-12-31’,插入数据库的日期为 2015-12-31;输入 ‘991231’,插入数据库的日期为 1999-12-31。
以 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069,80~99 范围的年值转换为 1980~1999。例如,输入 151231,插入数据库的日期为 2015-12-31,输入 991231,插入数据库的日期为 1999-12-31。
使用 CURRENT_DATE 或者 NOW(),插入当前系统日期。
提示:MySQL 允许“不严格”语法:任何标点符号都可以用作日期部分之间的间隔符。例如,’98-11-31’、’98.11.31’、’98/11/31’和’[email protected]@31′ 是等价的,这些值也可以正确地插入数据库。

  • DATETIME 类型 DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。

在给 DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可,如下所示。
以 ‘YYYY-MM-DD HH:MM:SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期,取值范围为 ‘1000-01-01 00:00:00’~’9999-12-3 23:59:59’。例如,输入 ‘2014-12-31 05:05:05’ 或者 ‘20141231050505’,插入数据库的 DATETIME 值都为 2014-12-31 05:05:05。
以 ‘YY-MM-DD HH:MM:SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期,在这里 YY 表示两位的年值。与前面相同,’00~79′ 范围的年值转换为 ‘2000~2079’,’80~99’ 范围的年值转换为 ‘1980~1999’。例如,输入 ’14-12-31 05:05:05’,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如,输入 20141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 140505050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
提示:MySQL 允许“不严格”语法:任何标点符号都可用作日期部分或时间部分之间的间隔符。例如,’98-12-31 11:30:45’、’98.12.31 11+30+35’、’98/12/31 11*30*45′ 和 ’[email protected]@31 11^30^45′ 是等价的,这些值都可以正确地插入数据库。

  • TIMESTAMP 类型 TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 ‘1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC。在插入数据时,要保证在合法的取值范围内。 提示:协调世界时(英:Coordinated Universal Time,法:Temps Universel Coordonné)又称为世界统一时间、世界标准时间、国际协调时间。英文(CUT)和法文(TUC)的缩写不同,作为妥协,简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:
DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;
而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
提示:如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值,结果值的时间部分被设置为 ’00:00:00’,因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值,结果值的时间部分被删除,因此DATE 值未包含时间信息。

1.4 字符串类型

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
数据库篇·第四章[2]·MYSQL
VARCHAR 和 TEXT 类型是变长类型,其存储需求取决于列值的实际长度(在前面的表格中用 L 表示),而不是取决于类型的最大可能尺寸。

例如,一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串,实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”,L 是 4,而存储要求 5 个字节。

  • CHAR 和 VARCHAR 类型
    CHAR(M) 为固定长度字符串,在定义时指定字符串列长。当保存时,在右侧填充空格以达到指定的长度。M 表示列的长度,范围是 0~255 个字符。
    例如,CHAR(4) 定义了一个固定长度的字符串列,包含的字符个数最大为 4。当检索到 CHAR 值时,尾部的空格将被删除。
    VARCHAR(M) 是长度可变的字符串,M 表示最大列的长度,M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。
    例如,VARCHAR(50) 定义了一个最大长度为 50 的字符串,如果插入的字符串只有 10 个字符,则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。
    【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列,说明 CHAR 和 VARCHAR 之间的差别,如下表所示。
    数据库篇·第四章[2]·MYSQL
    对比结果可以看到,CHAR(4) 定义了固定长度为 4 的列,无论存入的数据长度为多少,所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1

  • TEXT 类型
    TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格。
    TEXT 类型分为 4 种:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。
    TINYTEXT 表示长度为 255(28-1)字符的 TEXT 列。
    TEXT 表示长度为 65535(216-1)字符的 TEXT 列。
    MEDIUMTEXT 表示长度为 16777215(224-1)字符的 TEXT 列。
    LONGTEXT 表示长度为 4294967295 或 4GB(232-1)字符的 TEXT 列。

  • ENUM类型

  • SET类型

1.5 二进制类型

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
数据库篇·第四章[2]·MYSQL

  • BIT 类型
    位字段类型。M 表示每个值的位数,范围为 1~64。如果 M 被省略,默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位,在值的左边用 0 填充。例如,为 BIT(6) 列分配一个值 b’101’,其效果与分配 b’000101′ 相同。
    BIT 数据类型用来保存位字段值,例如以二进制的形式保存数据 13,13 的二进制形式为 1101,在这里需要位数至少为 4 位的 BIT 类型,即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。
    提示:默认情况下,MySQL 不可以插入超出该列允许范围的值,因而插入数据时要确保插入的值在指定的范围内。

  • BINARY 和 VARBINARY 类型
    BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字节字符串。使用的语法格式如下:
    列名称 BINARY(M) 或者 VARBINARY(M)
    BINARY 类型的长度是固定的,指定长度后,不足最大长度的,将在它们右边填充 “” 补齐,以达到指定长度。例如,指定列数据类型为 BINARY(3),当插入 a 时,存储的内容实际为 “a0”,当插入 ab 时,实际存储的内容为“ab”,无论存储的内容是否达到指定的长度,存储空间均为指定的值 M。
    VARBINARY 类型的长度是可变的,指定好长度之后,长度可以在 0 到最大值之间。例如,指定列数据类型为 VARBINARY(20),如果插入的值长度只有 10,则实际存储空间为 10 加 1,实际占用的空间为字符串的实际长度加 1。

  • BLOB 类型
    BLOB 是一个二进制的对象,用来存储可变数量的数据。BLOB 类型分为 4 种:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB,它们可容纳值的最大长度不同,如下表所示。
    数据类型 存储范围
    TINYBLOB 最大长度为255 (28-1)字节
    BLOB 最大长度为65535 (216-1)字节
    MEDIUMBLOB 最大长度为16777215 (224-1)字节
    LONGBLOB 最大长度为4294967295或4GB (231-1)字节
    BLOB 列存储的是二进制字符串(字节字符串),TEXT 列存储的是非进制字符串(字符字符串)。BLOB 列是字符集,并且排序和比较基于列值字节的数值;TEXT 列有一个字符集,并且根据字符集对值进行排序和比较。

2. 数据类型的选择

字符串类型是通用的数据类型,任何内容都可以保存在字符串中,数字和日期都可以表示成字符串形式。

但是也不能把所有的列都定义为字符串类型。对于数值类型,如果把它们设置为字符串类型的,会使用很多的空间。并且在这种情况下使用数值类型列来存储数字,比使用字符串类型更有效率。

另外需要注意的是,由于对数字和字符串的处理方式不同,查询结果也会存在差异。例如,对数字的排序与对字符串的排序是不一样的。

例如,数字 2 小于数字 11,但字符串 ‘2’ 却比字符串 ’11’ 大。此问题可以通过把列放到数字上下文中来解决,如下面 SQL 语句:
SELECT course+ 0 as num … ORDER BY num;
让 course 列加上 0,可以强制列按数字的方式来排序,但这么做很明显是不合理的。
如果让 MySQL 把一个字符串列当作一个数字列来对待,会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换,操作效率低。而且在计算过程中使用这样的列,会导致 MySQL 不会使用这些列上的任何索引,从而进一步降低查询的速度。

所以我们在选择数据类型时要考虑存储、查询和整体性能等方面的问题。

在选择数据类型时,首先要考虑这个列存放的值是什么类型的。一般来说,用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。

  • 数值类型
    如果数值类型需要存储的数据为货币,如人民币。在计算时,使用到的值常带有元和分两个部分。它们看起来像是浮点值,但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题,因此不太适合。因为人们对自己的金钱都很敏感,所以需要一个可以提供完美精度的数据类型。
    可以把货币表示成 DECIMAL(M,2) 类型,其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差,计算是精确的。
    对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里,除非去掉其中的非数字字符。但即使去掉了其中的非数字字符,也不能把它们存储成数值类型,以避免丢失开头的“零”。

  • 日期和时间类型
    MySQL 对于不同种类的日期和时间都提供了数据类型,比如 YEAR 和 TIME。如果只需要记录年份,则使用 YEAR 类型即可;如果只记录时间,可以使用 TIME 类型。
    如果同时需要记录日期和时间,则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,因此存储较大的日期最好使用 DATETIME。
    TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下,当插入一条记录但并没有指定 TIMESTAMP 这个列值时,MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时,使用 TIMESTAMP 是方便的,另外 TIMESTAMP 在空间上比 DATETIME 更有效。
    MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分,DATETIME 必须有时间部分。如果时间部分是可选的,那么可以使用 DATE 列来记录日期,再用一个单独的 TIME 列来记录时间。然后,设置 TIME 列可以为 NULL。SQL 语句如下:
    CREATE TABLE mytb1 (
    date DATE NOT NULL, #日期是必需的
    time TIME NULL #时间可选(可能为NULL)
    );

  • 字符串类型
    字符串类型没有像数字类型列那样的“取值范围”,但它们都有长度的概念。如果需要存储的字符串短于 256 个字符,那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串,则可以选用 VARCHAR 或某种更长的 TEXT 类型。
    如果某个字符串列用于表示某种固定集合的值,那么可以考虑使用数据类型 ENUM 或 SET。
    CHAR 和 VARCHAR 之间的特点和选择
    CHAR 和 VARCHAR 的区别如下:
    CHAR 是固定长度字符,VARCHAR 是可变长度字符。
    CHAR 会自动删除插入数据的尾部空格,VARCHAR 不会删除尾部空格。
    CHAR 是固定长度,所以它的处理速度比 VARCHAR 的速度要快,但是它的缺点就是浪费存储空间。所以对存储不大,但在速度上有要求的可以使用 CHAR 类型,反之可以使用 VARCHAR类型来实现。
    存储引擎对于选择 CHAR 和 VARCHAR 的影响:
    对于 MyISAM 存储引擎,最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
    对于InnoDB存储引擎,最好使用可变长度的数据列,因为 InnoDB 数据表的存储格式不分固定长度和可变长度,因此使用 CHAR 不一定比使用 VARCHAR 更好,但由于 VARCHAR 是按照实际的长度存储,比较节省空间,所以对磁盘 I/O 和数据存储总量比较好。
    ENUM 和 SET
    ENUM 只能取单值,它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535个成员。因此,在需要从多个值中选取一个时,可以使用 ENUM。比如,性别字段适合定义,为 ENUM 类型,每次只能从‘男’或‘女’中取一个值。
    SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候,适合使用 SET 类型,比如,要存储一个人兴趣爱好,最好使用SET类型。
    ENUM 和 SET 的值是以字符串形式出现的,但在内部,MySQL 以数值的形式存储它们。

  • 二进制类型
    BLOB 是二进制字符串,TEXT 是非二进制字符串,两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等,而 TEXT 只能存储纯文本文件。

3. 索引

索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中有某一特定值的记录。使数据库应用程序可以更快地查找数据。索引是 MySQL 中十分重要的数据库对象。很多人形象的说索引就是数据的目录,便于存储引擎快速的定位数据。
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。

CREATE INDEX index_name ON table_name (column_name); CREATE UNIQUE INDEX index_name ON table_name (column_name);  ALTER TABLE table_name DROP INDEX index_name;

3.1 索引的优缺点

索引有其明显的优势,也有其不可避免的缺点。

  • 优点 索引的优点如下: 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。 可以给所有的 MySQL 列类型设置索引。 可以大大加快数据的查询速度,这是使用索引最主要的原因。 在实现数据的参考完整性方面可以加速表与表之间的连接。 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
  • 缺点 增加索引也有许多不利的方面,主要如下: 创建和维护索引组要耗费时间,并且随着数据量的增加所耗费的时间也会增加。 索引需要占磁盘空间,除了数据表占数据空间以外,每一个索引还要占一定的物理空间。如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。 当对表中的数据进行增加、删除和修改的时候,索引也要动态维护,这样就降低了数据的维护速度。 索引可以提高查询速度,但是会影响插入记录的速度。因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序,这样就降低了插入记录的速度,插入大量记录时的速度影响会更加明显。这种情况下,最好的办法是先删除表中的索引,然后插入数据,插入完成后,再创建索引。

3.2 索引的分类

我们经常从以下几个方面对索引进行分类

3.2.1 从数据结构的角度对索引进行分类

B+tree
Hash
Full-texts索引
下表是MySQL常见的存储引擎InnoDB,MyISAM和Memory分别支持的索引类型
数据库篇·第四章[2]·MYSQL
1970年,R.Bayer和E.Mccreight提出了一种适用于外查找的平衡多叉树——B-树,磁盘管理系统中的目录管理,以及数据库系统中的索引组织多数采用B-Tree这种数据结构。
B+tree是B-Tree的一个变种。(哦,对了,B-tree念B树,它不叫B减树。。。)
数据库篇·第四章[2]·MYSQL
B+tree只在叶子节点存储数据,而B-tree非叶子节点也存储数据,对此处有疑问的可以到下面的连接自己插入数据测试一番
B-TREE: https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+TREE: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
因此,B+tree单个节点的数量更小,在相同的磁盘IO下能查询更多的节点。
另外B+tree叶子节点采用单链表链接适合MySQL中常见的基于范围的顺序检索场景,而B-tree无法做到这一点。
数据库篇·第四章[2]·MYSQL
从上图中可以看出红黑树是二叉树,节点的子节点个数最多为2个,意味着其搜索复杂度为O(logN),比B+树高出不少,因此红黑树检索到目标数据所需经理的磁盘I/O次数更多。
B+tree索引与Hash表
范围查询是MySQL数据库中常见的场景,而Hash表不适合做范围查询,Hash表更适合做等值查询,另外Hash表还存在Hash函数选择和Hash值冲突等问题。
因为这些原因,B+tree索引要比Hash表索引有更广的适用场景。

3.2.2 从物理存储的角度对索引进行分类

聚簇索引
二级索引(辅助索引)

  • InnoDB InnoDB表要求必须有聚簇索引,默认在主键字段上建立聚簇索引,在没有主键字段的情况下,表的第一个NOT NULL 的唯一索引将被建立为聚簇索引,在前两者都没有的情况下,InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。 数据库篇·第四章[2]·MYSQL 数据库篇·第四章[2]·MYSQL 数据库篇·第四章[2]·MYSQL select id,name from workers where name=’吕归尘’; 这句sql只查询了id,和name,二级索引就已经包含了Query所以需要的所有字段,就无需回表查询。 explain select id,name from workers where name=’吕归尘’; 使用explain查看此条sql的执行计划 执行计划的Extra字段中出现了Using where;Using index 表明查询触发了索引index_name的索引覆盖,且对索引做了where筛选,这里不需要回表。
  • MyISAM 以MyISAM存储引擎存储的表不存在聚簇索引。 数据库篇·第四章[2]·MYSQL MyISAM表中的主键索引和非主键索引的结构是一样的,从上图中我们可以看到 他们的叶子节点是不存储表数据的,节点中存放的是表数据的地址,所以MyISAM表可以没有主键。 MyISAM表的数据和索引是分开的,是单独存放的。 MyISAM表中的主键索引和非主键索引的区别仅在于主键索引B+tree上的key必须符合主键的限制, 非主键索引B+tree上的key只要符合相应字段的特性就可以了。

3.2.3 从索引字段特性角度分类

  • 主键索引 建立在主键字段上的索引;一张表最多只有一个主键索引;索引列值不允许为null
  • 唯一索引 建立在UNIQUE字段上的索引就是唯一索引; 一张表可以有多个唯一索引,索引列值允许为null
create table persons  (      id   int(11) not null auto_increment comment '主键id',      eno  int(11) comment '工号',      eid  int(11) comment '身份证号',      veid int(11) comment '虚拟身份证号',      name varchar(16) comment '名字',      primary key (id) comment '主键索引',      UNIQUE key (eno) comment 'eno唯一索引',      UNIQUE key (eid) comment 'eid唯一索引'  ) engine = InnoDB    auto_increment = 1000    default charset = utf8;   alter table persons      add unique index index_veid (veid) comment 'veid唯一索引';
  • 普通索引
    普通索引,既不要求字段为主键也不要求字段为unique。

  • 前缀索引
    前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引,而不是在整个字段上建索引。
    例如,可以对persons表中的name(varchar(16))字段 中name的前5个字符建立索引。
    create index index_name on persons (name(5)) comment ‘前缀索引’;
    show index from persons;
    前缀索引可以建立在类型为
    char
    varchar
    binary
    varbinary
    的列上,可以大大减少索引占用的存储空间,也能提升索引的查询效率。

3.2.4 从组成索引的字段个数角度分类

单列索引
建立在单个列上的索引为单列索引
联合索引(复合索引)
建立在多列上的称为联合索引(复合索引)
数据库篇·第四章[2]·MYSQL

3.3. 索引的创建 查看 删除

在执行CREATE TABLE语句时可以创建索引
也可以单独用CREATE INDEX
或ALTER TABLE来为表增加索引。
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list); ALTER TABLE table_name ADD UNIQUE (column_list); ALTER TABLE table_name ADD PRIMARY KEY (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list); CREATE UNIQUE INDEX index_name ON table_name (column_list);

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name DROP PRIMARY KEY;

查看索引

mysql> show index from tblname; mysql> show keys from tblname;

3.4 索引什么情况下失效不被使用

3.5 怎么提升索引的使用效率,设计高效的索引

4. 约束

约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。

mysql常见约束有:
1、非空约束“not null”;
2、唯一性约束“unique”;
3、主键约束“primary key”;
4、外键约束“foreign key”;
5、默认值约束“Default”;
6、检查约束 “Check” 等等;

主要归类为列级约束和表级约束
列级约束:NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK
表级约束:PRIMARY KEY | UNIQUE | CHECK | FOREIGN KEY

4.1 分类

4.1 非空约束

用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征:所有的类型的值都可以是null,包括int、float 等数据类型

4.2 唯一性约束(unique)

唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。
唯一约束不允许出现重复的值,但是可以为多个null。
同一个表可以有多个唯一约束,多个列组合的约束。
在创建唯一约束时,如果不给唯一约束名称,就默认和列名相同。
唯一约束不仅可以在一个表内创建,而且可以同时多表创建组合唯一约束。

4.3 主键约束(primary key) PK

主键约束相当于 唯一约束 + 非空约束 的组合,主键约束列不允许重复,也不允许出现空值。
每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别创建。
当创建主键的约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。

主键分为“单字段主键”和“多字段联合主键”,并且在使用主键的时候需要注意以下几个点:
一个表只能定义一个主键;
主键值必须唯一标识表中的每一行,并且不能出现null的情况,即表中不能存在有相同主键的两行或两行以上数据,严格遵守唯一性原则;
一个字段名只能在联合主键字段表中出现一次;
联合主键不能包含不必要的多余字段,以满足最小化原则。

数据库篇·第四章[2]·MYSQL

4.4 外键约束(foreign key) FK

外键约束是用来加强两个表(主表和从表)的一列或多列数据之间的连接的,可以保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
创建外键约束的顺序是先定义主表的主键,然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用,被约束的从表中的列可以不是主键,主表限制了从表更新和插入的操作。
数据库篇·第四章[2]·MYSQL
数据库篇·第四章[2]·MYSQL

在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
用外键要适当,不能过分追求
不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库。
为何说外键有性能问题:
数据库需要维护外键的内部管理;
外键等于把数据的一致性事务实现,全部交给数据库服务器完成;
有了外键,当做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,而不得不消耗资源;
外键还会因为需要请求对其他表内部加锁而容易出现死锁情况;

4.5 默认值约束 (Default)

若在表中定义了默认值约束,用户在插入新的数据行时,如果该行没有指定数据,那么系统将默认值赋给该列,如果我们不设置默认值,系统默认为NULL。

4.6 自增约束(AUTO_INCREMENT)

自增约束(AUTO_INCREMENT)可以约束任何一个字段,该字段不一定是PRIMARY KEY字段,也就是说自增的字段并不等于主键字段。
但是PRIMARY_KEY约束的主键字段,一定是自增字段,即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。
当插入第一条记录时,自增字段没有给定一个具体值,可以写成DEFAULT/NULL,那么以后插入字段的时候,该自增字段就是从1开始,每插入一条记录,该自增字段的值增加1。当插入第一条记录时,给自增字段一个具体值,那么以后插入的记录在此自增字段上的值,就在第一条记录该自增字段的值的基础上每次增加1。
也可以在插入记录的时候,不指定自增字段,而是指定其余字段进行插入记录的操作

4.2 创建,修改和删除

建表时操作

CREATE TABLE 表名(     字段名 字段类型 列级约束,     字段名 字段类型,     表级约束   )

修改时操作
ALTER TABLE <数据表名> ADD/MODIFY/DROP 约束;

5. 函数

count, sum, avg, substr, date, concat, pad, trim, max, min, len, now, format,isnull, nvl, ifnull,
可参考 https://www.runoob.com/mysql/mysql-functions.html

5.1 字符串函数

CONCAT(s1,s2,…sn) 拼接多个字符串
CONCAT_WS(x, s1,s2…sn) 按分隔符x拼接多个字符串
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len
LTRIM(s) 去掉字符串 s 开始处的空格
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len)
REPEAT(s,n) 将字符串 s 重复 n 次
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s) 将字符串s的顺序反过来
STRCMP(s1,s2) 比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串

5.2 数字函数

一般情况下,我们需要的聚合数据(总和,平均数,最大最小值等)并不总是存储在表中。 但是,可以通过执行存储数据的计算来获取它。
数据库篇·第四章[2]·MYSQL
AVG(expression) 返回一个表达式的平均值,expression 是一个字段
COUNT(expression) 返回查询的记录总数,expression 参数是一个字段或者 * 号
CEIL(x) 返回大于或等于 x 的最小整数 
FLOOR(x) 返回小于或等于 x 的最大整数
MAX(expression) 返回字段 expression 中的最大值
MIN(expression) 返回字段 expression 中的最小值
SUM(expression) 返回指定字段的总和

5.3 日期函数

ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);->2017-06-25

ADDTIME(t,n) n 是一个时间表达式,时间 t 加上时间表达式 n

SELECT ADDTIME('2011-11-11 11:11:11', 5);->2011-11-11 11:11:16 (秒) SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26

CURRENT_DATE() 返回当前日期
CURRENT_TIME 返回当前时间
CURRENT_TIMESTAMP() 返回当前日期和时间
NOW() 返回当前日期和时间
SYSDATE() 返回当前日期和时间
DATE_ADD(d,INTERVAL expr type)

type 值可以是: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-04-15

DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 计算时间差,返回 datetime_expr2 − datetime_expr1 的时间差

SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少天-> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月-> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年-> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟-> 128885

5.4 高级函数

IF(expr,v1,v2) 如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。
IFNULL(v1,v2) 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
ISNULL(expression) 判断表达式是否为 NULL
NULLIF(expr1, expr2) 比较两个字符串,如果字符串 expr1 与 expr2 相等 返回 NULL,否则返回 expr1
USER() 返回当前用户
VERSION() 返回数据库的版本号

6. 存储过程

7. 事务

7.1 事务四大特性(ACID)

7.1.1 原子性(Atomicity)

原子性是指事务包含的一系列操作要么全部成功,要么全部回滚,不存在部分成功或者部分回滚,是一个不可分割的操作整体。

7.1.2 一致性(Consistency)

一致性是可以理解为事务对数据完整性约束的遵循,这些约束可能包括主键约束、唯一索引约束、外键约束等等。事务执行前后,数据都是合法的状态,不会违背任何的数据完整性
就拿转账来说,A和B加起来有5000块钱,不管A和B如何转账,转几次账,A和B加起来的钱永远都是5000块。
总之,可以理解为:一致性是为了保证数据的完整性。

7.1.3 隔离性(Isolation)

隔离性是指当多个用户并发操作数据库,比如操作同一张表,数据库为每一个用户开启的事务,不能被其他的事务所干扰或者影响,事务之间是彼此独立的。

7.1.4 永久性(Durability)

永久性是指一个事务一旦提交了,那么对数据库中数据的改变就是永久的,即使是在数据库发生故障时,也不会丢失事务提交的数据。

7.2 事务的隔离性

事务的隔离性。当多个线程开启事务操作数据库中的数据时,数据库要能进行隔离操作,以保证各个线程获取数据的准确性; 如果不考虑事务的隔离性,会发生以下几个问题;

7.2.1、脏读

脏读是指一个事务在处理过程中读取了另一个事务未提交的数据。比如,A向B转账
update account set money = money + 100 where name = ‘B’;
update account set money = money – 100 where name = ‘A’
当只执行第一条SQL时,A通知B查看账户,B发现确实钱已到账(此时即发生了脏读),
而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,那么当B以后再次查看账户时就会发现钱其实并没有转。

7.2.2、不可重复读

不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。
例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库,事务T1再次读取该数据就得到了不同的结果,发生了不可重复读。
不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。
在某些情况下,不可重复读并不是问题,比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题,例如对于同一个数据A和B依次查询就可能不同,A和B就可能打起来了……

7.2.3、幻读

幻读是事务非独立执行时发生的一种现象。
例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)

7.3 MySQL事务的隔离级别

四种隔离级别最高的是Serializable级别,最低的是Read uncommitted级别,当然级别越高,执行效率就越低。
像Serializable这样的级别,就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待)
所以平时选用何种隔离级别应该根据实际情况。
Serializable (串行化):可避免脏读、不可重复读、幻读的发生 >
Repeatable read (可重复读):可避免脏读、不可重复读的发生 >
Read committed (读已提交):可避免脏读的发生 >
Read uncommitted (读未提交):最低级别,任何情况都无法保证
MySQL支持以上4种隔离级别,默认的隔离级别是Repeatable read (可重复读)
Oracle只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别,默认的隔离级别是Read committed (读已提交)

7.3.1 查看和设置MYSQL的事务隔离级别

每启动一个 MySQL 程序,就会获得一个单独的数据库连接。每
个数据库连接都有一个全局变量 @@transaction_isolation,表示当前的
事务隔离级别。

老版本 MySQL 比如 5 中用的是 tx_isolation,而应该是在 5.7.20 版本之后,用的是 transaction_isolation。

select  或 selecton;

数据库篇·第四章[2]·MYSQL
设置当前 MySQL 连接的隔离级别:

set session transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

7.3.2 举例说明事务的隔离级别

7.3.2.1 Repeatable read (可重复读)

数据库篇·第四章[2]·MYSQL

7.3.2.2 Read committed (读已提交–>脏读)

现在试试将隔离级别设置为读已提交,返回的结果又是什么
设置当前 MySQL 连接的隔离级别
数据库篇·第四章[2]·MYSQL

[TOC]

1. MYSQL数据类型

MySQL 的数据类型有大概可以分为 5 种,分别是:
| 类型 | 具体 |
| ——– | ——– |
| 整数型 | TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT |
| 浮点型和定点数型 | FLOAT 和 DOUBLE,定点数类型为 DECIMAL |
| 日期和时间类型 | YEAR、TIME、DATE、DATETIME 和 TIMESTAMP |
| 字符串型 | CHAR、VARCHAR、TEXT、ENUM 和 SET 等 |
| 二进制类型 | BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB |

1.1 整数型

整数型字段可以添加 AUTO_INCREMENT 自增约束条件;
整型数据类型也可以在定义表结构时指定所需的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值;

显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。
根据实际需要选择最合适的类型,这样有利于提高查询的效率和节省存储空间。

|类型名称|说明|存储需求|有符号|无符号|
| ———|———|———|———|
|TINYINT|很小的整数|1个字节|-128~127|0~255|
|SMALLINT|小的整数|2个宇节|-32768~32767|0~65535|
|MEDIUMINT|中等大小的整数 |3个字节|-8388608~8388607|0~16777215|
|INT (INTEGHR)|普通大小的整数|4个字节 |-2147483648~2147483647|0~4294967295|
|BIGINT|大整数 |8个字节|-9223372036854775808~9223372036854775807|0~18446744073709551615|

1.2 浮点型

浮点类型有两种,分别是单精度浮点数(FLOAT)和双精度浮点数(DOUBLE);定点类型只有一种,就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。

浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

类型名称 说明 存储需求
FLOAT 单精度浮点数 4 个字节
DOUBLE 双精度浮点数 8 个字节
DECIMAL (M, D),DEC 压缩的“严格”定点数 M+2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE。DECIMAL 实际上是以字符串的形式存放的,DECIMAL 可能的最大取值范围与 DOUBLE 相同,但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D,则取值范围将随 M 的变大而变大。在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,
DECIMAL 的存储空间并不是固定的,而由精度值 M 决定,占用 M+2 个字节。
不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的范围;缺点是会引起精度问题。
另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

1.3 时间和日期类型

数据库篇·第四章[2]·MYSQL

  • YEAR 类型
    YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR,如下所示:
    以 4 位字符串或者 4 位数字格式表示的 YEAR,范围为 ‘1901’~’2155’。输入格式为 ‘YYYY’ 或者 YYYY,例如,输入 ‘2010’ 或 2010,插入数据库的值均为 2010。
    以 2 位字符串格式表示的 YEAR,范围为 ’00’ 到 ’99’。’00’~’69’ 和 ’70’~’99’ 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 YEAR 值。’0′ 与 ’00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
    以 2 位数字表示的 YEAR,范围为 1~99。1~99 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 YEAR 值。注意,在这里 0 值将被转换为 0000,而不是 2000。
    提示:两位整数范围与两位字符串范围稍有不同。例如,插入 3000 年,读者可能会使用数字格式的 0 表示 YEAR,实际上,插入数据库的值为 0000,而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ’00’,才可以被正确解释为 3000,非法 YEAR值将被转换为 0000。

  • TIME 类型
    TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。

TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于 24 小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。

可以使用各种格式指定 TIME 值,如下所示。
‘D HH:MM:SS’ 格式的字符串。还可以使用这些“非严格”的语法:’HH:MM:SS’、’HH:MM’、’D HH’ 或 ‘SS’。这里的 D 表示日,可以取 0~34 之间的值。在插入数据库时,D 被转换为小时保存,格式为 “D*24+HH”。
‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值,假定是有意义的时间。例如,’101112′ 被理解为’10:11:12’,但是 ‘106112’ 是不合法的(它有一个没有意义的分钟部分),在存储时将变为 00:00:00。
提示:为 TIME 列分配简写值时应注意:如果没有冒号,MySQL 解释值时,假定最右边的两位表示秒。(MySQL 解释 TIME 值为过去的时间而不是当前的时间)。例如,读者可能认为 ‘1112’ 和 1112 表示 11:12:00(即 11 点过 12 分钟),但MySQL 将它们解释为 00:11:12(即 11 分 12 秒)。同样 ’12’ 和 12 被解释为00:00:12。相反,TIME 值中如果使用冒号则肯定被看作当天的时间,也就是说,’11:12′ 表示 11:12:00,而不是 00:11:12。

  • DATE 类型 DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示年,MM 表示月,DD 表示日。

在给 DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可。如下所示:
以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期,取值范围为 ‘1000-01-01’~’9999-12-3’。例如,输入 ‘2015-12-31’ 或者 ‘20151231’,插入数据库的日期为2015-12-31。
以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期,在这里YY表示两位的年值。MySQL 解释两位年值的规则:’00~69′ 范围的年值转换为 ‘2000~2069’,’70~99’ 范围的年值转换为 ‘1970~1999’。例如,输入 ’15-12-31’,插入数据库的日期为 2015-12-31;输入 ‘991231’,插入数据库的日期为 1999-12-31。
以 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069,80~99 范围的年值转换为 1980~1999。例如,输入 151231,插入数据库的日期为 2015-12-31,输入 991231,插入数据库的日期为 1999-12-31。
使用 CURRENT_DATE 或者 NOW(),插入当前系统日期。
提示:MySQL 允许“不严格”语法:任何标点符号都可以用作日期部分之间的间隔符。例如,’98-11-31’、’98.11.31’、’98/11/31’和’[email protected]@31′ 是等价的,这些值也可以正确地插入数据库。

  • DATETIME 类型 DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。

在给 DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可,如下所示。
以 ‘YYYY-MM-DD HH:MM:SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期,取值范围为 ‘1000-01-01 00:00:00’~’9999-12-3 23:59:59’。例如,输入 ‘2014-12-31 05:05:05’ 或者 ‘20141231050505’,插入数据库的 DATETIME 值都为 2014-12-31 05:05:05。
以 ‘YY-MM-DD HH:MM:SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期,在这里 YY 表示两位的年值。与前面相同,’00~79′ 范围的年值转换为 ‘2000~2079’,’80~99’ 范围的年值转换为 ‘1980~1999’。例如,输入 ’14-12-31 05:05:05’,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如,输入 20141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 140505050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
提示:MySQL 允许“不严格”语法:任何标点符号都可用作日期部分或时间部分之间的间隔符。例如,’98-12-31 11:30:45’、’98.12.31 11+30+35’、’98/12/31 11*30*45′ 和 ’[email protected]@31 11^30^45′ 是等价的,这些值都可以正确地插入数据库。

  • TIMESTAMP 类型 TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 ‘1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC。在插入数据时,要保证在合法的取值范围内。 提示:协调世界时(英:Coordinated Universal Time,法:Temps Universel Coordonné)又称为世界统一时间、世界标准时间、国际协调时间。英文(CUT)和法文(TUC)的缩写不同,作为妥协,简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:
DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;
而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
提示:如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值,结果值的时间部分被设置为 ’00:00:00’,因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值,结果值的时间部分被删除,因此DATE 值未包含时间信息。

1.4 字符串类型

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
数据库篇·第四章[2]·MYSQL
VARCHAR 和 TEXT 类型是变长类型,其存储需求取决于列值的实际长度(在前面的表格中用 L 表示),而不是取决于类型的最大可能尺寸。

例如,一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串,实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”,L 是 4,而存储要求 5 个字节。

  • CHAR 和 VARCHAR 类型
    CHAR(M) 为固定长度字符串,在定义时指定字符串列长。当保存时,在右侧填充空格以达到指定的长度。M 表示列的长度,范围是 0~255 个字符。
    例如,CHAR(4) 定义了一个固定长度的字符串列,包含的字符个数最大为 4。当检索到 CHAR 值时,尾部的空格将被删除。
    VARCHAR(M) 是长度可变的字符串,M 表示最大列的长度,M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。
    例如,VARCHAR(50) 定义了一个最大长度为 50 的字符串,如果插入的字符串只有 10 个字符,则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。
    【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列,说明 CHAR 和 VARCHAR 之间的差别,如下表所示。
    数据库篇·第四章[2]·MYSQL
    对比结果可以看到,CHAR(4) 定义了固定长度为 4 的列,无论存入的数据长度为多少,所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1

  • TEXT 类型
    TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格。
    TEXT 类型分为 4 种:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。
    TINYTEXT 表示长度为 255(28-1)字符的 TEXT 列。
    TEXT 表示长度为 65535(216-1)字符的 TEXT 列。
    MEDIUMTEXT 表示长度为 16777215(224-1)字符的 TEXT 列。
    LONGTEXT 表示长度为 4294967295 或 4GB(232-1)字符的 TEXT 列。

  • ENUM类型

  • SET类型

1.5 二进制类型

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
数据库篇·第四章[2]·MYSQL

  • BIT 类型
    位字段类型。M 表示每个值的位数,范围为 1~64。如果 M 被省略,默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位,在值的左边用 0 填充。例如,为 BIT(6) 列分配一个值 b’101’,其效果与分配 b’000101′ 相同。
    BIT 数据类型用来保存位字段值,例如以二进制的形式保存数据 13,13 的二进制形式为 1101,在这里需要位数至少为 4 位的 BIT 类型,即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。
    提示:默认情况下,MySQL 不可以插入超出该列允许范围的值,因而插入数据时要确保插入的值在指定的范围内。

  • BINARY 和 VARBINARY 类型
    BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字节字符串。使用的语法格式如下:
    列名称 BINARY(M) 或者 VARBINARY(M)
    BINARY 类型的长度是固定的,指定长度后,不足最大长度的,将在它们右边填充 “” 补齐,以达到指定长度。例如,指定列数据类型为 BINARY(3),当插入 a 时,存储的内容实际为 “a0”,当插入 ab 时,实际存储的内容为“ab”,无论存储的内容是否达到指定的长度,存储空间均为指定的值 M。
    VARBINARY 类型的长度是可变的,指定好长度之后,长度可以在 0 到最大值之间。例如,指定列数据类型为 VARBINARY(20),如果插入的值长度只有 10,则实际存储空间为 10 加 1,实际占用的空间为字符串的实际长度加 1。

  • BLOB 类型
    BLOB 是一个二进制的对象,用来存储可变数量的数据。BLOB 类型分为 4 种:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB,它们可容纳值的最大长度不同,如下表所示。
    数据类型 存储范围
    TINYBLOB 最大长度为255 (28-1)字节
    BLOB 最大长度为65535 (216-1)字节
    MEDIUMBLOB 最大长度为16777215 (224-1)字节
    LONGBLOB 最大长度为4294967295或4GB (231-1)字节
    BLOB 列存储的是二进制字符串(字节字符串),TEXT 列存储的是非进制字符串(字符字符串)。BLOB 列是字符集,并且排序和比较基于列值字节的数值;TEXT 列有一个字符集,并且根据字符集对值进行排序和比较。

2. 数据类型的选择

字符串类型是通用的数据类型,任何内容都可以保存在字符串中,数字和日期都可以表示成字符串形式。

但是也不能把所有的列都定义为字符串类型。对于数值类型,如果把它们设置为字符串类型的,会使用很多的空间。并且在这种情况下使用数值类型列来存储数字,比使用字符串类型更有效率。

另外需要注意的是,由于对数字和字符串的处理方式不同,查询结果也会存在差异。例如,对数字的排序与对字符串的排序是不一样的。

例如,数字 2 小于数字 11,但字符串 ‘2’ 却比字符串 ’11’ 大。此问题可以通过把列放到数字上下文中来解决,如下面 SQL 语句:
SELECT course+ 0 as num … ORDER BY num;
让 course 列加上 0,可以强制列按数字的方式来排序,但这么做很明显是不合理的。
如果让 MySQL 把一个字符串列当作一个数字列来对待,会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换,操作效率低。而且在计算过程中使用这样的列,会导致 MySQL 不会使用这些列上的任何索引,从而进一步降低查询的速度。

所以我们在选择数据类型时要考虑存储、查询和整体性能等方面的问题。

在选择数据类型时,首先要考虑这个列存放的值是什么类型的。一般来说,用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。

  • 数值类型
    如果数值类型需要存储的数据为货币,如人民币。在计算时,使用到的值常带有元和分两个部分。它们看起来像是浮点值,但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题,因此不太适合。因为人们对自己的金钱都很敏感,所以需要一个可以提供完美精度的数据类型。
    可以把货币表示成 DECIMAL(M,2) 类型,其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差,计算是精确的。
    对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里,除非去掉其中的非数字字符。但即使去掉了其中的非数字字符,也不能把它们存储成数值类型,以避免丢失开头的“零”。

  • 日期和时间类型
    MySQL 对于不同种类的日期和时间都提供了数据类型,比如 YEAR 和 TIME。如果只需要记录年份,则使用 YEAR 类型即可;如果只记录时间,可以使用 TIME 类型。
    如果同时需要记录日期和时间,则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,因此存储较大的日期最好使用 DATETIME。
    TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下,当插入一条记录但并没有指定 TIMESTAMP 这个列值时,MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时,使用 TIMESTAMP 是方便的,另外 TIMESTAMP 在空间上比 DATETIME 更有效。
    MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分,DATETIME 必须有时间部分。如果时间部分是可选的,那么可以使用 DATE 列来记录日期,再用一个单独的 TIME 列来记录时间。然后,设置 TIME 列可以为 NULL。SQL 语句如下:
    CREATE TABLE mytb1 (
    date DATE NOT NULL, #日期是必需的
    time TIME NULL #时间可选(可能为NULL)
    );

  • 字符串类型
    字符串类型没有像数字类型列那样的“取值范围”,但它们都有长度的概念。如果需要存储的字符串短于 256 个字符,那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串,则可以选用 VARCHAR 或某种更长的 TEXT 类型。
    如果某个字符串列用于表示某种固定集合的值,那么可以考虑使用数据类型 ENUM 或 SET。
    CHAR 和 VARCHAR 之间的特点和选择
    CHAR 和 VARCHAR 的区别如下:
    CHAR 是固定长度字符,VARCHAR 是可变长度字符。
    CHAR 会自动删除插入数据的尾部空格,VARCHAR 不会删除尾部空格。
    CHAR 是固定长度,所以它的处理速度比 VARCHAR 的速度要快,但是它的缺点就是浪费存储空间。所以对存储不大,但在速度上有要求的可以使用 CHAR 类型,反之可以使用 VARCHAR类型来实现。
    存储引擎对于选择 CHAR 和 VARCHAR 的影响:
    对于 MyISAM 存储引擎,最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
    对于InnoDB存储引擎,最好使用可变长度的数据列,因为 InnoDB 数据表的存储格式不分固定长度和可变长度,因此使用 CHAR 不一定比使用 VARCHAR 更好,但由于 VARCHAR 是按照实际的长度存储,比较节省空间,所以对磁盘 I/O 和数据存储总量比较好。
    ENUM 和 SET
    ENUM 只能取单值,它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535个成员。因此,在需要从多个值中选取一个时,可以使用 ENUM。比如,性别字段适合定义,为 ENUM 类型,每次只能从‘男’或‘女’中取一个值。
    SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候,适合使用 SET 类型,比如,要存储一个人兴趣爱好,最好使用SET类型。
    ENUM 和 SET 的值是以字符串形式出现的,但在内部,MySQL 以数值的形式存储它们。

  • 二进制类型
    BLOB 是二进制字符串,TEXT 是非二进制字符串,两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等,而 TEXT 只能存储纯文本文件。

3. 索引

索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中有某一特定值的记录。使数据库应用程序可以更快地查找数据。索引是 MySQL 中十分重要的数据库对象。很多人形象的说索引就是数据的目录,便于存储引擎快速的定位数据。
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。

CREATE INDEX index_name ON table_name (column_name); CREATE UNIQUE INDEX index_name ON table_name (column_name);  ALTER TABLE table_name DROP INDEX index_name;

3.1 索引的优缺点

索引有其明显的优势,也有其不可避免的缺点。

  • 优点 索引的优点如下: 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。 可以给所有的 MySQL 列类型设置索引。 可以大大加快数据的查询速度,这是使用索引最主要的原因。 在实现数据的参考完整性方面可以加速表与表之间的连接。 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
  • 缺点 增加索引也有许多不利的方面,主要如下: 创建和维护索引组要耗费时间,并且随着数据量的增加所耗费的时间也会增加。 索引需要占磁盘空间,除了数据表占数据空间以外,每一个索引还要占一定的物理空间。如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。 当对表中的数据进行增加、删除和修改的时候,索引也要动态维护,这样就降低了数据的维护速度。 索引可以提高查询速度,但是会影响插入记录的速度。因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序,这样就降低了插入记录的速度,插入大量记录时的速度影响会更加明显。这种情况下,最好的办法是先删除表中的索引,然后插入数据,插入完成后,再创建索引。

3.2 索引的分类

我们经常从以下几个方面对索引进行分类

3.2.1 从数据结构的角度对索引进行分类

B+tree
Hash
Full-texts索引
下表是MySQL常见的存储引擎InnoDB,MyISAM和Memory分别支持的索引类型
数据库篇·第四章[2]·MYSQL
1970年,R.Bayer和E.Mccreight提出了一种适用于外查找的平衡多叉树——B-树,磁盘管理系统中的目录管理,以及数据库系统中的索引组织多数采用B-Tree这种数据结构。
B+tree是B-Tree的一个变种。(哦,对了,B-tree念B树,它不叫B减树。。。)
数据库篇·第四章[2]·MYSQL
B+tree只在叶子节点存储数据,而B-tree非叶子节点也存储数据,对此处有疑问的可以到下面的连接自己插入数据测试一番
B-TREE: https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+TREE: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
因此,B+tree单个节点的数量更小,在相同的磁盘IO下能查询更多的节点。
另外B+tree叶子节点采用单链表链接适合MySQL中常见的基于范围的顺序检索场景,而B-tree无法做到这一点。
数据库篇·第四章[2]·MYSQL
从上图中可以看出红黑树是二叉树,节点的子节点个数最多为2个,意味着其搜索复杂度为O(logN),比B+树高出不少,因此红黑树检索到目标数据所需经理的磁盘I/O次数更多。
B+tree索引与Hash表
范围查询是MySQL数据库中常见的场景,而Hash表不适合做范围查询,Hash表更适合做等值查询,另外Hash表还存在Hash函数选择和Hash值冲突等问题。
因为这些原因,B+tree索引要比Hash表索引有更广的适用场景。

3.2.2 从物理存储的角度对索引进行分类

聚簇索引
二级索引(辅助索引)

  • InnoDB InnoDB表要求必须有聚簇索引,默认在主键字段上建立聚簇索引,在没有主键字段的情况下,表的第一个NOT NULL 的唯一索引将被建立为聚簇索引,在前两者都没有的情况下,InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。 数据库篇·第四章[2]·MYSQL 数据库篇·第四章[2]·MYSQL 数据库篇·第四章[2]·MYSQL select id,name from workers where name=’吕归尘’; 这句sql只查询了id,和name,二级索引就已经包含了Query所以需要的所有字段,就无需回表查询。 explain select id,name from workers where name=’吕归尘’; 使用explain查看此条sql的执行计划 执行计划的Extra字段中出现了Using where;Using index 表明查询触发了索引index_name的索引覆盖,且对索引做了where筛选,这里不需要回表。
  • MyISAM 以MyISAM存储引擎存储的表不存在聚簇索引。 数据库篇·第四章[2]·MYSQL MyISAM表中的主键索引和非主键索引的结构是一样的,从上图中我们可以看到 他们的叶子节点是不存储表数据的,节点中存放的是表数据的地址,所以MyISAM表可以没有主键。 MyISAM表的数据和索引是分开的,是单独存放的。 MyISAM表中的主键索引和非主键索引的区别仅在于主键索引B+tree上的key必须符合主键的限制, 非主键索引B+tree上的key只要符合相应字段的特性就可以了。

3.2.3 从索引字段特性角度分类

  • 主键索引 建立在主键字段上的索引;一张表最多只有一个主键索引;索引列值不允许为null
  • 唯一索引 建立在UNIQUE字段上的索引就是唯一索引; 一张表可以有多个唯一索引,索引列值允许为null
create table persons  (      id   int(11) not null auto_increment comment '主键id',      eno  int(11) comment '工号',      eid  int(11) comment '身份证号',      veid int(11) comment '虚拟身份证号',      name varchar(16) comment '名字',      primary key (id) comment '主键索引',      UNIQUE key (eno) comment 'eno唯一索引',      UNIQUE key (eid) comment 'eid唯一索引'  ) engine = InnoDB    auto_increment = 1000    default charset = utf8;   alter table persons      add unique index index_veid (veid) comment 'veid唯一索引';
  • 普通索引
    普通索引,既不要求字段为主键也不要求字段为unique。

  • 前缀索引
    前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引,而不是在整个字段上建索引。
    例如,可以对persons表中的name(varchar(16))字段 中name的前5个字符建立索引。
    create index index_name on persons (name(5)) comment ‘前缀索引’;
    show index from persons;
    前缀索引可以建立在类型为
    char
    varchar
    binary
    varbinary
    的列上,可以大大减少索引占用的存储空间,也能提升索引的查询效率。

3.2.4 从组成索引的字段个数角度分类

单列索引
建立在单个列上的索引为单列索引
联合索引(复合索引)
建立在多列上的称为联合索引(复合索引)
数据库篇·第四章[2]·MYSQL

3.3. 索引的创建 查看 删除

在执行CREATE TABLE语句时可以创建索引
也可以单独用CREATE INDEX
或ALTER TABLE来为表增加索引。
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list); ALTER TABLE table_name ADD UNIQUE (column_list); ALTER TABLE table_name ADD PRIMARY KEY (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list); CREATE UNIQUE INDEX index_name ON table_name (column_list);

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name DROP PRIMARY KEY;

查看索引

mysql> show index from tblname; mysql> show keys from tblname;

3.4 索引什么情况下失效不被使用

3.5 怎么提升索引的使用效率,设计高效的索引

4. 约束

约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。

mysql常见约束有:
1、非空约束“not null”;
2、唯一性约束“unique”;
3、主键约束“primary key”;
4、外键约束“foreign key”;
5、默认值约束“Default”;
6、检查约束 “Check” 等等;

主要归类为列级约束和表级约束
列级约束:NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK
表级约束:PRIMARY KEY | UNIQUE | CHECK | FOREIGN KEY

4.1 分类

4.1 非空约束

用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征:所有的类型的值都可以是null,包括int、float 等数据类型

4.2 唯一性约束(unique)

唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。
唯一约束不允许出现重复的值,但是可以为多个null。
同一个表可以有多个唯一约束,多个列组合的约束。
在创建唯一约束时,如果不给唯一约束名称,就默认和列名相同。
唯一约束不仅可以在一个表内创建,而且可以同时多表创建组合唯一约束。

4.3 主键约束(primary key) PK

主键约束相当于 唯一约束 + 非空约束 的组合,主键约束列不允许重复,也不允许出现空值。
每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别创建。
当创建主键的约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。

主键分为“单字段主键”和“多字段联合主键”,并且在使用主键的时候需要注意以下几个点:
一个表只能定义一个主键;
主键值必须唯一标识表中的每一行,并且不能出现null的情况,即表中不能存在有相同主键的两行或两行以上数据,严格遵守唯一性原则;
一个字段名只能在联合主键字段表中出现一次;
联合主键不能包含不必要的多余字段,以满足最小化原则。

数据库篇·第四章[2]·MYSQL

4.4 外键约束(foreign key) FK

外键约束是用来加强两个表(主表和从表)的一列或多列数据之间的连接的,可以保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
创建外键约束的顺序是先定义主表的主键,然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用,被约束的从表中的列可以不是主键,主表限制了从表更新和插入的操作。
数据库篇·第四章[2]·MYSQL
数据库篇·第四章[2]·MYSQL

在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
用外键要适当,不能过分追求
不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库。
为何说外键有性能问题:
数据库需要维护外键的内部管理;
外键等于把数据的一致性事务实现,全部交给数据库服务器完成;
有了外键,当做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,而不得不消耗资源;
外键还会因为需要请求对其他表内部加锁而容易出现死锁情况;

4.5 默认值约束 (Default)

若在表中定义了默认值约束,用户在插入新的数据行时,如果该行没有指定数据,那么系统将默认值赋给该列,如果我们不设置默认值,系统默认为NULL。

4.6 自增约束(AUTO_INCREMENT)

自增约束(AUTO_INCREMENT)可以约束任何一个字段,该字段不一定是PRIMARY KEY字段,也就是说自增的字段并不等于主键字段。
但是PRIMARY_KEY约束的主键字段,一定是自增字段,即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。
当插入第一条记录时,自增字段没有给定一个具体值,可以写成DEFAULT/NULL,那么以后插入字段的时候,该自增字段就是从1开始,每插入一条记录,该自增字段的值增加1。当插入第一条记录时,给自增字段一个具体值,那么以后插入的记录在此自增字段上的值,就在第一条记录该自增字段的值的基础上每次增加1。
也可以在插入记录的时候,不指定自增字段,而是指定其余字段进行插入记录的操作

4.2 创建,修改和删除

建表时操作

CREATE TABLE 表名(     字段名 字段类型 列级约束,     字段名 字段类型,     表级约束   )

修改时操作
ALTER TABLE <数据表名> ADD/MODIFY/DROP 约束;

5. 函数

count, sum, avg, substr, date, concat, pad, trim, max, min, len, now, format,isnull, nvl, ifnull,
可参考 https://www.runoob.com/mysql/mysql-functions.html

5.1 字符串函数

CONCAT(s1,s2,…sn) 拼接多个字符串
CONCAT_WS(x, s1,s2…sn) 按分隔符x拼接多个字符串
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len
LTRIM(s) 去掉字符串 s 开始处的空格
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len)
REPEAT(s,n) 将字符串 s 重复 n 次
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s) 将字符串s的顺序反过来
STRCMP(s1,s2) 比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串

5.2 数字函数

一般情况下,我们需要的聚合数据(总和,平均数,最大最小值等)并不总是存储在表中。 但是,可以通过执行存储数据的计算来获取它。
数据库篇·第四章[2]·MYSQL
AVG(expression) 返回一个表达式的平均值,expression 是一个字段
COUNT(expression) 返回查询的记录总数,expression 参数是一个字段或者 * 号
CEIL(x) 返回大于或等于 x 的最小整数 
FLOOR(x) 返回小于或等于 x 的最大整数
MAX(expression) 返回字段 expression 中的最大值
MIN(expression) 返回字段 expression 中的最小值
SUM(expression) 返回指定字段的总和

5.3 日期函数

ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);->2017-06-25

ADDTIME(t,n) n 是一个时间表达式,时间 t 加上时间表达式 n

SELECT ADDTIME('2011-11-11 11:11:11', 5);->2011-11-11 11:11:16 (秒) SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26

CURRENT_DATE() 返回当前日期
CURRENT_TIME 返回当前时间
CURRENT_TIMESTAMP() 返回当前日期和时间
NOW() 返回当前日期和时间
SYSDATE() 返回当前日期和时间
DATE_ADD(d,INTERVAL expr type)

type 值可以是: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-04-15

DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 计算时间差,返回 datetime_expr2 − datetime_expr1 的时间差

SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少天-> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月-> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年-> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟-> 128885

5.4 高级函数

IF(expr,v1,v2) 如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。
IFNULL(v1,v2) 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
ISNULL(expression) 判断表达式是否为 NULL
NULLIF(expr1, expr2) 比较两个字符串,如果字符串 expr1 与 expr2 相等 返回 NULL,否则返回 expr1
USER() 返回当前用户
VERSION() 返回数据库的版本号

6. 存储过程

7. 事务

7.1 事务四大特性(ACID)

7.1.1 原子性(Atomicity)

原子性是指事务包含的一系列操作要么全部成功,要么全部回滚,不存在部分成功或者部分回滚,是一个不可分割的操作整体。

7.1.2 一致性(Consistency)

一致性是可以理解为事务对数据完整性约束的遵循,这些约束可能包括主键约束、唯一索引约束、外键约束等等。事务执行前后,数据都是合法的状态,不会违背任何的数据完整性
就拿转账来说,A和B加起来有5000块钱,不管A和B如何转账,转几次账,A和B加起来的钱永远都是5000块。
总之,可以理解为:一致性是为了保证数据的完整性。

7.1.3 隔离性(Isolation)

隔离性是指当多个用户并发操作数据库,比如操作同一张表,数据库为每一个用户开启的事务,不能被其他的事务所干扰或者影响,事务之间是彼此独立的。

7.1.4 永久性(Durability)

永久性是指一个事务一旦提交了,那么对数据库中数据的改变就是永久的,即使是在数据库发生故障时,也不会丢失事务提交的数据。

7.2 事务的隔离性

事务的隔离性。当多个线程开启事务操作数据库中的数据时,数据库要能进行隔离操作,以保证各个线程获取数据的准确性; 如果不考虑事务的隔离性,会发生以下几个问题;

7.2.1、脏读

脏读是指一个事务在处理过程中读取了另一个事务未提交的数据。比如,A向B转账
update account set money = money + 100 where name = ‘B’;
update account set money = money – 100 where name = ‘A’
当只执行第一条SQL时,A通知B查看账户,B发现确实钱已到账(此时即发生了脏读),
而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,那么当B以后再次查看账户时就会发现钱其实并没有转。

7.2.2、不可重复读

不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。
例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库,事务T1再次读取该数据就得到了不同的结果,发生了不可重复读。
不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。
在某些情况下,不可重复读并不是问题,比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题,例如对于同一个数据A和B依次查询就可能不同,A和B就可能打起来了……

7.2.3、幻读

幻读是事务非独立执行时发生的一种现象。
例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)

7.3 MySQL事务的隔离级别

四种隔离级别最高的是Serializable级别,最低的是Read uncommitted级别,当然级别越高,执行效率就越低。
像Serializable这样的级别,就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待)
所以平时选用何种隔离级别应该根据实际情况。
Serializable (串行化):可避免脏读、不可重复读、幻读的发生 >
Repeatable read (可重复读):可避免脏读、不可重复读的发生 >
Read committed (读已提交):可避免脏读的发生 >
Read uncommitted (读未提交):最低级别,任何情况都无法保证
MySQL支持以上4种隔离级别,默认的隔离级别是Repeatable read (可重复读)
Oracle只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别,默认的隔离级别是Read committed (读已提交)

7.3.1 查看和设置MYSQL的事务隔离级别

每启动一个 MySQL 程序,就会获得一个单独的数据库连接。每
个数据库连接都有一个全局变量 @@transaction_isolation,表示当前的
事务隔离级别。

老版本 MySQL 比如 5 中用的是 tx_isolation,而应该是在 5.7.20 版本之后,用的是 transaction_isolation。

select  或 selecton;

数据库篇·第四章[2]·MYSQL
设置当前 MySQL 连接的隔离级别:

set session transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

7.3.2 举例说明事务的隔离级别

7.3.2.1 Repeatable read (可重复读)

数据库篇·第四章[2]·MYSQL

7.3.2.2 Read committed (读已提交–>脏读)

现在试试将隔离级别设置为读已提交,返回的结果又是什么
设置当前 MySQL 连接的隔离级别
数据库篇·第四章[2]·MYSQL

[TOC]

1. MYSQL数据类型

MySQL 的数据类型有大概可以分为 5 种,分别是:
| 类型 | 具体 |
| ——– | ——– |
| 整数型 | TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT |
| 浮点型和定点数型 | FLOAT 和 DOUBLE,定点数类型为 DECIMAL |
| 日期和时间类型 | YEAR、TIME、DATE、DATETIME 和 TIMESTAMP |
| 字符串型 | CHAR、VARCHAR、TEXT、ENUM 和 SET 等 |
| 二进制类型 | BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB |

1.1 整数型

整数型字段可以添加 AUTO_INCREMENT 自增约束条件;
整型数据类型也可以在定义表结构时指定所需的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值;

显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。
根据实际需要选择最合适的类型,这样有利于提高查询的效率和节省存储空间。

|类型名称|说明|存储需求|有符号|无符号|
| ———|———|———|———|
|TINYINT|很小的整数|1个字节|-128~127|0~255|
|SMALLINT|小的整数|2个宇节|-32768~32767|0~65535|
|MEDIUMINT|中等大小的整数 |3个字节|-8388608~8388607|0~16777215|
|INT (INTEGHR)|普通大小的整数|4个字节 |-2147483648~2147483647|0~4294967295|
|BIGINT|大整数 |8个字节|-9223372036854775808~9223372036854775807|0~18446744073709551615|

1.2 浮点型

浮点类型有两种,分别是单精度浮点数(FLOAT)和双精度浮点数(DOUBLE);定点类型只有一种,就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。

浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

类型名称 说明 存储需求
FLOAT 单精度浮点数 4 个字节
DOUBLE 双精度浮点数 8 个字节
DECIMAL (M, D),DEC 压缩的“严格”定点数 M+2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE。DECIMAL 实际上是以字符串的形式存放的,DECIMAL 可能的最大取值范围与 DOUBLE 相同,但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D,则取值范围将随 M 的变大而变大。在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,
DECIMAL 的存储空间并不是固定的,而由精度值 M 决定,占用 M+2 个字节。
不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的范围;缺点是会引起精度问题。
另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。

1.3 时间和日期类型

数据库篇·第四章[2]·MYSQL

  • YEAR 类型
    YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR,如下所示:
    以 4 位字符串或者 4 位数字格式表示的 YEAR,范围为 ‘1901’~’2155’。输入格式为 ‘YYYY’ 或者 YYYY,例如,输入 ‘2010’ 或 2010,插入数据库的值均为 2010。
    以 2 位字符串格式表示的 YEAR,范围为 ’00’ 到 ’99’。’00’~’69’ 和 ’70’~’99’ 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 YEAR 值。’0′ 与 ’00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
    以 2 位数字表示的 YEAR,范围为 1~99。1~99 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 YEAR 值。注意,在这里 0 值将被转换为 0000,而不是 2000。
    提示:两位整数范围与两位字符串范围稍有不同。例如,插入 3000 年,读者可能会使用数字格式的 0 表示 YEAR,实际上,插入数据库的值为 0000,而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ’00’,才可以被正确解释为 3000,非法 YEAR值将被转换为 0000。

  • TIME 类型
    TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。

TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于 24 小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。

可以使用各种格式指定 TIME 值,如下所示。
‘D HH:MM:SS’ 格式的字符串。还可以使用这些“非严格”的语法:’HH:MM:SS’、’HH:MM’、’D HH’ 或 ‘SS’。这里的 D 表示日,可以取 0~34 之间的值。在插入数据库时,D 被转换为小时保存,格式为 “D*24+HH”。
‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值,假定是有意义的时间。例如,’101112′ 被理解为’10:11:12’,但是 ‘106112’ 是不合法的(它有一个没有意义的分钟部分),在存储时将变为 00:00:00。
提示:为 TIME 列分配简写值时应注意:如果没有冒号,MySQL 解释值时,假定最右边的两位表示秒。(MySQL 解释 TIME 值为过去的时间而不是当前的时间)。例如,读者可能认为 ‘1112’ 和 1112 表示 11:12:00(即 11 点过 12 分钟),但MySQL 将它们解释为 00:11:12(即 11 分 12 秒)。同样 ’12’ 和 12 被解释为00:00:12。相反,TIME 值中如果使用冒号则肯定被看作当天的时间,也就是说,’11:12′ 表示 11:12:00,而不是 00:11:12。

  • DATE 类型 DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示年,MM 表示月,DD 表示日。

在给 DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可。如下所示:
以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期,取值范围为 ‘1000-01-01’~’9999-12-3’。例如,输入 ‘2015-12-31’ 或者 ‘20151231’,插入数据库的日期为2015-12-31。
以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期,在这里YY表示两位的年值。MySQL 解释两位年值的规则:’00~69′ 范围的年值转换为 ‘2000~2069’,’70~99’ 范围的年值转换为 ‘1970~1999’。例如,输入 ’15-12-31’,插入数据库的日期为 2015-12-31;输入 ‘991231’,插入数据库的日期为 1999-12-31。
以 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069,80~99 范围的年值转换为 1980~1999。例如,输入 151231,插入数据库的日期为 2015-12-31,输入 991231,插入数据库的日期为 1999-12-31。
使用 CURRENT_DATE 或者 NOW(),插入当前系统日期。
提示:MySQL 允许“不严格”语法:任何标点符号都可以用作日期部分之间的间隔符。例如,’98-11-31’、’98.11.31’、’98/11/31’和’[email protected]@31′ 是等价的,这些值也可以正确地插入数据库。

  • DATETIME 类型 DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。

在给 DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可,如下所示。
以 ‘YYYY-MM-DD HH:MM:SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期,取值范围为 ‘1000-01-01 00:00:00’~’9999-12-3 23:59:59’。例如,输入 ‘2014-12-31 05:05:05’ 或者 ‘20141231050505’,插入数据库的 DATETIME 值都为 2014-12-31 05:05:05。
以 ‘YY-MM-DD HH:MM:SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期,在这里 YY 表示两位的年值。与前面相同,’00~79′ 范围的年值转换为 ‘2000~2079’,’80~99’ 范围的年值转换为 ‘1980~1999’。例如,输入 ’14-12-31 05:05:05’,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如,输入 20141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 140505050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
提示:MySQL 允许“不严格”语法:任何标点符号都可用作日期部分或时间部分之间的间隔符。例如,’98-12-31 11:30:45’、’98.12.31 11+30+35’、’98/12/31 11*30*45′ 和 ’[email protected]@31 11^30^45′ 是等价的,这些值都可以正确地插入数据库。

  • TIMESTAMP 类型 TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 ‘1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC。在插入数据时,要保证在合法的取值范围内。 提示:协调世界时(英:Coordinated Universal Time,法:Temps Universel Coordonné)又称为世界统一时间、世界标准时间、国际协调时间。英文(CUT)和法文(TUC)的缩写不同,作为妥协,简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:
DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;
而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
提示:如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值,结果值的时间部分被设置为 ’00:00:00’,因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值,结果值的时间部分被删除,因此DATE 值未包含时间信息。

1.4 字符串类型

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
数据库篇·第四章[2]·MYSQL
VARCHAR 和 TEXT 类型是变长类型,其存储需求取决于列值的实际长度(在前面的表格中用 L 表示),而不是取决于类型的最大可能尺寸。

例如,一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串,实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”,L 是 4,而存储要求 5 个字节。

  • CHAR 和 VARCHAR 类型
    CHAR(M) 为固定长度字符串,在定义时指定字符串列长。当保存时,在右侧填充空格以达到指定的长度。M 表示列的长度,范围是 0~255 个字符。
    例如,CHAR(4) 定义了一个固定长度的字符串列,包含的字符个数最大为 4。当检索到 CHAR 值时,尾部的空格将被删除。
    VARCHAR(M) 是长度可变的字符串,M 表示最大列的长度,M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。
    例如,VARCHAR(50) 定义了一个最大长度为 50 的字符串,如果插入的字符串只有 10 个字符,则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。
    【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列,说明 CHAR 和 VARCHAR 之间的差别,如下表所示。
    数据库篇·第四章[2]·MYSQL
    对比结果可以看到,CHAR(4) 定义了固定长度为 4 的列,无论存入的数据长度为多少,所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1

  • TEXT 类型
    TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格。
    TEXT 类型分为 4 种:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。
    TINYTEXT 表示长度为 255(28-1)字符的 TEXT 列。
    TEXT 表示长度为 65535(216-1)字符的 TEXT 列。
    MEDIUMTEXT 表示长度为 16777215(224-1)字符的 TEXT 列。
    LONGTEXT 表示长度为 4294967295 或 4GB(232-1)字符的 TEXT 列。

  • ENUM类型

  • SET类型

1.5 二进制类型

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
数据库篇·第四章[2]·MYSQL

  • BIT 类型
    位字段类型。M 表示每个值的位数,范围为 1~64。如果 M 被省略,默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位,在值的左边用 0 填充。例如,为 BIT(6) 列分配一个值 b’101’,其效果与分配 b’000101′ 相同。
    BIT 数据类型用来保存位字段值,例如以二进制的形式保存数据 13,13 的二进制形式为 1101,在这里需要位数至少为 4 位的 BIT 类型,即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。
    提示:默认情况下,MySQL 不可以插入超出该列允许范围的值,因而插入数据时要确保插入的值在指定的范围内。

  • BINARY 和 VARBINARY 类型
    BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字节字符串。使用的语法格式如下:
    列名称 BINARY(M) 或者 VARBINARY(M)
    BINARY 类型的长度是固定的,指定长度后,不足最大长度的,将在它们右边填充 “” 补齐,以达到指定长度。例如,指定列数据类型为 BINARY(3),当插入 a 时,存储的内容实际为 “a0”,当插入 ab 时,实际存储的内容为“ab”,无论存储的内容是否达到指定的长度,存储空间均为指定的值 M。
    VARBINARY 类型的长度是可变的,指定好长度之后,长度可以在 0 到最大值之间。例如,指定列数据类型为 VARBINARY(20),如果插入的值长度只有 10,则实际存储空间为 10 加 1,实际占用的空间为字符串的实际长度加 1。

  • BLOB 类型
    BLOB 是一个二进制的对象,用来存储可变数量的数据。BLOB 类型分为 4 种:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB,它们可容纳值的最大长度不同,如下表所示。
    数据类型 存储范围
    TINYBLOB 最大长度为255 (28-1)字节
    BLOB 最大长度为65535 (216-1)字节
    MEDIUMBLOB 最大长度为16777215 (224-1)字节
    LONGBLOB 最大长度为4294967295或4GB (231-1)字节
    BLOB 列存储的是二进制字符串(字节字符串),TEXT 列存储的是非进制字符串(字符字符串)。BLOB 列是字符集,并且排序和比较基于列值字节的数值;TEXT 列有一个字符集,并且根据字符集对值进行排序和比较。

2. 数据类型的选择

字符串类型是通用的数据类型,任何内容都可以保存在字符串中,数字和日期都可以表示成字符串形式。

但是也不能把所有的列都定义为字符串类型。对于数值类型,如果把它们设置为字符串类型的,会使用很多的空间。并且在这种情况下使用数值类型列来存储数字,比使用字符串类型更有效率。

另外需要注意的是,由于对数字和字符串的处理方式不同,查询结果也会存在差异。例如,对数字的排序与对字符串的排序是不一样的。

例如,数字 2 小于数字 11,但字符串 ‘2’ 却比字符串 ’11’ 大。此问题可以通过把列放到数字上下文中来解决,如下面 SQL 语句:
SELECT course+ 0 as num … ORDER BY num;
让 course 列加上 0,可以强制列按数字的方式来排序,但这么做很明显是不合理的。
如果让 MySQL 把一个字符串列当作一个数字列来对待,会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换,操作效率低。而且在计算过程中使用这样的列,会导致 MySQL 不会使用这些列上的任何索引,从而进一步降低查询的速度。

所以我们在选择数据类型时要考虑存储、查询和整体性能等方面的问题。

在选择数据类型时,首先要考虑这个列存放的值是什么类型的。一般来说,用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。

  • 数值类型
    如果数值类型需要存储的数据为货币,如人民币。在计算时,使用到的值常带有元和分两个部分。它们看起来像是浮点值,但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题,因此不太适合。因为人们对自己的金钱都很敏感,所以需要一个可以提供完美精度的数据类型。
    可以把货币表示成 DECIMAL(M,2) 类型,其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差,计算是精确的。
    对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里,除非去掉其中的非数字字符。但即使去掉了其中的非数字字符,也不能把它们存储成数值类型,以避免丢失开头的“零”。

  • 日期和时间类型
    MySQL 对于不同种类的日期和时间都提供了数据类型,比如 YEAR 和 TIME。如果只需要记录年份,则使用 YEAR 类型即可;如果只记录时间,可以使用 TIME 类型。
    如果同时需要记录日期和时间,则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,因此存储较大的日期最好使用 DATETIME。
    TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下,当插入一条记录但并没有指定 TIMESTAMP 这个列值时,MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时,使用 TIMESTAMP 是方便的,另外 TIMESTAMP 在空间上比 DATETIME 更有效。
    MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分,DATETIME 必须有时间部分。如果时间部分是可选的,那么可以使用 DATE 列来记录日期,再用一个单独的 TIME 列来记录时间。然后,设置 TIME 列可以为 NULL。SQL 语句如下:
    CREATE TABLE mytb1 (
    date DATE NOT NULL, #日期是必需的
    time TIME NULL #时间可选(可能为NULL)
    );

  • 字符串类型
    字符串类型没有像数字类型列那样的“取值范围”,但它们都有长度的概念。如果需要存储的字符串短于 256 个字符,那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串,则可以选用 VARCHAR 或某种更长的 TEXT 类型。
    如果某个字符串列用于表示某种固定集合的值,那么可以考虑使用数据类型 ENUM 或 SET。
    CHAR 和 VARCHAR 之间的特点和选择
    CHAR 和 VARCHAR 的区别如下:
    CHAR 是固定长度字符,VARCHAR 是可变长度字符。
    CHAR 会自动删除插入数据的尾部空格,VARCHAR 不会删除尾部空格。
    CHAR 是固定长度,所以它的处理速度比 VARCHAR 的速度要快,但是它的缺点就是浪费存储空间。所以对存储不大,但在速度上有要求的可以使用 CHAR 类型,反之可以使用 VARCHAR类型来实现。
    存储引擎对于选择 CHAR 和 VARCHAR 的影响:
    对于 MyISAM 存储引擎,最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
    对于InnoDB存储引擎,最好使用可变长度的数据列,因为 InnoDB 数据表的存储格式不分固定长度和可变长度,因此使用 CHAR 不一定比使用 VARCHAR 更好,但由于 VARCHAR 是按照实际的长度存储,比较节省空间,所以对磁盘 I/O 和数据存储总量比较好。
    ENUM 和 SET
    ENUM 只能取单值,它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535个成员。因此,在需要从多个值中选取一个时,可以使用 ENUM。比如,性别字段适合定义,为 ENUM 类型,每次只能从‘男’或‘女’中取一个值。
    SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候,适合使用 SET 类型,比如,要存储一个人兴趣爱好,最好使用SET类型。
    ENUM 和 SET 的值是以字符串形式出现的,但在内部,MySQL 以数值的形式存储它们。

  • 二进制类型
    BLOB 是二进制字符串,TEXT 是非二进制字符串,两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等,而 TEXT 只能存储纯文本文件。

3. 索引

索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中有某一特定值的记录。使数据库应用程序可以更快地查找数据。索引是 MySQL 中十分重要的数据库对象。很多人形象的说索引就是数据的目录,便于存储引擎快速的定位数据。
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。

CREATE INDEX index_name ON table_name (column_name); CREATE UNIQUE INDEX index_name ON table_name (column_name);  ALTER TABLE table_name DROP INDEX index_name;

3.1 索引的优缺点

索引有其明显的优势,也有其不可避免的缺点。

  • 优点 索引的优点如下: 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。 可以给所有的 MySQL 列类型设置索引。 可以大大加快数据的查询速度,这是使用索引最主要的原因。 在实现数据的参考完整性方面可以加速表与表之间的连接。 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
  • 缺点 增加索引也有许多不利的方面,主要如下: 创建和维护索引组要耗费时间,并且随着数据量的增加所耗费的时间也会增加。 索引需要占磁盘空间,除了数据表占数据空间以外,每一个索引还要占一定的物理空间。如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。 当对表中的数据进行增加、删除和修改的时候,索引也要动态维护,这样就降低了数据的维护速度。 索引可以提高查询速度,但是会影响插入记录的速度。因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序,这样就降低了插入记录的速度,插入大量记录时的速度影响会更加明显。这种情况下,最好的办法是先删除表中的索引,然后插入数据,插入完成后,再创建索引。

3.2 索引的分类

我们经常从以下几个方面对索引进行分类

3.2.1 从数据结构的角度对索引进行分类

B+tree
Hash
Full-texts索引
下表是MySQL常见的存储引擎InnoDB,MyISAM和Memory分别支持的索引类型
数据库篇·第四章[2]·MYSQL
1970年,R.Bayer和E.Mccreight提出了一种适用于外查找的平衡多叉树——B-树,磁盘管理系统中的目录管理,以及数据库系统中的索引组织多数采用B-Tree这种数据结构。
B+tree是B-Tree的一个变种。(哦,对了,B-tree念B树,它不叫B减树。。。)
数据库篇·第四章[2]·MYSQL
B+tree只在叶子节点存储数据,而B-tree非叶子节点也存储数据,对此处有疑问的可以到下面的连接自己插入数据测试一番
B-TREE: https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+TREE: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
因此,B+tree单个节点的数量更小,在相同的磁盘IO下能查询更多的节点。
另外B+tree叶子节点采用单链表链接适合MySQL中常见的基于范围的顺序检索场景,而B-tree无法做到这一点。
数据库篇·第四章[2]·MYSQL
从上图中可以看出红黑树是二叉树,节点的子节点个数最多为2个,意味着其搜索复杂度为O(logN),比B+树高出不少,因此红黑树检索到目标数据所需经理的磁盘I/O次数更多。
B+tree索引与Hash表
范围查询是MySQL数据库中常见的场景,而Hash表不适合做范围查询,Hash表更适合做等值查询,另外Hash表还存在Hash函数选择和Hash值冲突等问题。
因为这些原因,B+tree索引要比Hash表索引有更广的适用场景。

3.2.2 从物理存储的角度对索引进行分类

聚簇索引
二级索引(辅助索引)

  • InnoDB InnoDB表要求必须有聚簇索引,默认在主键字段上建立聚簇索引,在没有主键字段的情况下,表的第一个NOT NULL 的唯一索引将被建立为聚簇索引,在前两者都没有的情况下,InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。 数据库篇·第四章[2]·MYSQL 数据库篇·第四章[2]·MYSQL 数据库篇·第四章[2]·MYSQL select id,name from workers where name=’吕归尘’; 这句sql只查询了id,和name,二级索引就已经包含了Query所以需要的所有字段,就无需回表查询。 explain select id,name from workers where name=’吕归尘’; 使用explain查看此条sql的执行计划 执行计划的Extra字段中出现了Using where;Using index 表明查询触发了索引index_name的索引覆盖,且对索引做了where筛选,这里不需要回表。
  • MyISAM 以MyISAM存储引擎存储的表不存在聚簇索引。 数据库篇·第四章[2]·MYSQL MyISAM表中的主键索引和非主键索引的结构是一样的,从上图中我们可以看到 他们的叶子节点是不存储表数据的,节点中存放的是表数据的地址,所以MyISAM表可以没有主键。 MyISAM表的数据和索引是分开的,是单独存放的。 MyISAM表中的主键索引和非主键索引的区别仅在于主键索引B+tree上的key必须符合主键的限制, 非主键索引B+tree上的key只要符合相应字段的特性就可以了。

3.2.3 从索引字段特性角度分类

  • 主键索引 建立在主键字段上的索引;一张表最多只有一个主键索引;索引列值不允许为null
  • 唯一索引 建立在UNIQUE字段上的索引就是唯一索引; 一张表可以有多个唯一索引,索引列值允许为null
create table persons  (      id   int(11) not null auto_increment comment '主键id',      eno  int(11) comment '工号',      eid  int(11) comment '身份证号',      veid int(11) comment '虚拟身份证号',      name varchar(16) comment '名字',      primary key (id) comment '主键索引',      UNIQUE key (eno) comment 'eno唯一索引',      UNIQUE key (eid) comment 'eid唯一索引'  ) engine = InnoDB    auto_increment = 1000    default charset = utf8;   alter table persons      add unique index index_veid (veid) comment 'veid唯一索引';
  • 普通索引
    普通索引,既不要求字段为主键也不要求字段为unique。

  • 前缀索引
    前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引,而不是在整个字段上建索引。
    例如,可以对persons表中的name(varchar(16))字段 中name的前5个字符建立索引。
    create index index_name on persons (name(5)) comment ‘前缀索引’;
    show index from persons;
    前缀索引可以建立在类型为
    char
    varchar
    binary
    varbinary
    的列上,可以大大减少索引占用的存储空间,也能提升索引的查询效率。

3.2.4 从组成索引的字段个数角度分类

单列索引
建立在单个列上的索引为单列索引
联合索引(复合索引)
建立在多列上的称为联合索引(复合索引)
数据库篇·第四章[2]·MYSQL

3.3. 索引的创建 查看 删除

在执行CREATE TABLE语句时可以创建索引
也可以单独用CREATE INDEX
或ALTER TABLE来为表增加索引。
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list); ALTER TABLE table_name ADD UNIQUE (column_list); ALTER TABLE table_name ADD PRIMARY KEY (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list); CREATE UNIQUE INDEX index_name ON table_name (column_list);

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name DROP PRIMARY KEY;

查看索引

mysql> show index from tblname; mysql> show keys from tblname;

3.4 索引什么情况下失效不被使用

3.5 怎么提升索引的使用效率,设计高效的索引

4. 约束

约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性。

mysql常见约束有:
1、非空约束“not null”;
2、唯一性约束“unique”;
3、主键约束“primary key”;
4、外键约束“foreign key”;
5、默认值约束“Default”;
6、检查约束 “Check” 等等;

主要归类为列级约束和表级约束
列级约束:NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK
表级约束:PRIMARY KEY | UNIQUE | CHECK | FOREIGN KEY

4.1 分类

4.1 非空约束

用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征:所有的类型的值都可以是null,包括int、float 等数据类型

4.2 唯一性约束(unique)

唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。
唯一约束不允许出现重复的值,但是可以为多个null。
同一个表可以有多个唯一约束,多个列组合的约束。
在创建唯一约束时,如果不给唯一约束名称,就默认和列名相同。
唯一约束不仅可以在一个表内创建,而且可以同时多表创建组合唯一约束。

4.3 主键约束(primary key) PK

主键约束相当于 唯一约束 + 非空约束 的组合,主键约束列不允许重复,也不允许出现空值。
每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别创建。
当创建主键的约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。

主键分为“单字段主键”和“多字段联合主键”,并且在使用主键的时候需要注意以下几个点:
一个表只能定义一个主键;
主键值必须唯一标识表中的每一行,并且不能出现null的情况,即表中不能存在有相同主键的两行或两行以上数据,严格遵守唯一性原则;
一个字段名只能在联合主键字段表中出现一次;
联合主键不能包含不必要的多余字段,以满足最小化原则。

数据库篇·第四章[2]·MYSQL

4.4 外键约束(foreign key) FK

外键约束是用来加强两个表(主表和从表)的一列或多列数据之间的连接的,可以保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
创建外键约束的顺序是先定义主表的主键,然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用,被约束的从表中的列可以不是主键,主表限制了从表更新和插入的操作。
数据库篇·第四章[2]·MYSQL
数据库篇·第四章[2]·MYSQL

在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
用外键要适当,不能过分追求
不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库。
为何说外键有性能问题:
数据库需要维护外键的内部管理;
外键等于把数据的一致性事务实现,全部交给数据库服务器完成;
有了外键,当做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,而不得不消耗资源;
外键还会因为需要请求对其他表内部加锁而容易出现死锁情况;

4.5 默认值约束 (Default)

若在表中定义了默认值约束,用户在插入新的数据行时,如果该行没有指定数据,那么系统将默认值赋给该列,如果我们不设置默认值,系统默认为NULL。

4.6 自增约束(AUTO_INCREMENT)

自增约束(AUTO_INCREMENT)可以约束任何一个字段,该字段不一定是PRIMARY KEY字段,也就是说自增的字段并不等于主键字段。
但是PRIMARY_KEY约束的主键字段,一定是自增字段,即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。
当插入第一条记录时,自增字段没有给定一个具体值,可以写成DEFAULT/NULL,那么以后插入字段的时候,该自增字段就是从1开始,每插入一条记录,该自增字段的值增加1。当插入第一条记录时,给自增字段一个具体值,那么以后插入的记录在此自增字段上的值,就在第一条记录该自增字段的值的基础上每次增加1。
也可以在插入记录的时候,不指定自增字段,而是指定其余字段进行插入记录的操作

4.2 创建,修改和删除

建表时操作

CREATE TABLE 表名(     字段名 字段类型 列级约束,     字段名 字段类型,     表级约束   )

修改时操作
ALTER TABLE <数据表名> ADD/MODIFY/DROP 约束;

5. 函数

count, sum, avg, substr, date, concat, pad, trim, max, min, len, now, format,isnull, nvl, ifnull,
可参考 https://www.runoob.com/mysql/mysql-functions.html

5.1 字符串函数

CONCAT(s1,s2,…sn) 拼接多个字符串
CONCAT_WS(x, s1,s2…sn) 按分隔符x拼接多个字符串
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len
LTRIM(s) 去掉字符串 s 开始处的空格
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len)
REPEAT(s,n) 将字符串 s 重复 n 次
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s) 将字符串s的顺序反过来
STRCMP(s1,s2) 比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串

5.2 数字函数

一般情况下,我们需要的聚合数据(总和,平均数,最大最小值等)并不总是存储在表中。 但是,可以通过执行存储数据的计算来获取它。
数据库篇·第四章[2]·MYSQL
AVG(expression) 返回一个表达式的平均值,expression 是一个字段
COUNT(expression) 返回查询的记录总数,expression 参数是一个字段或者 * 号
CEIL(x) 返回大于或等于 x 的最小整数 
FLOOR(x) 返回小于或等于 x 的最大整数
MAX(expression) 返回字段 expression 中的最大值
MIN(expression) 返回字段 expression 中的最小值
SUM(expression) 返回指定字段的总和

5.3 日期函数

ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);->2017-06-25

ADDTIME(t,n) n 是一个时间表达式,时间 t 加上时间表达式 n

SELECT ADDTIME('2011-11-11 11:11:11', 5);->2011-11-11 11:11:16 (秒) SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26

CURRENT_DATE() 返回当前日期
CURRENT_TIME 返回当前时间
CURRENT_TIMESTAMP() 返回当前日期和时间
NOW() 返回当前日期和时间
SYSDATE() 返回当前日期和时间
DATE_ADD(d,INTERVAL expr type)

type 值可以是: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);-> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-04-15

DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 计算时间差,返回 datetime_expr2 − datetime_expr1 的时间差

SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01');   // 计算两个时间相隔多少天-> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月-> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年-> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟-> 128885

5.4 高级函数

IF(expr,v1,v2) 如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2。
IFNULL(v1,v2) 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
ISNULL(expression) 判断表达式是否为 NULL
NULLIF(expr1, expr2) 比较两个字符串,如果字符串 expr1 与 expr2 相等 返回 NULL,否则返回 expr1
USER() 返回当前用户
VERSION() 返回数据库的版本号

6. 存储过程

7. 事务

7.1 事务四大特性(ACID)

7.1.1 原子性(Atomicity)

原子性是指事务包含的一系列操作要么全部成功,要么全部回滚,不存在部分成功或者部分回滚,是一个不可分割的操作整体。

7.1.2 一致性(Consistency)

一致性是可以理解为事务对数据完整性约束的遵循,这些约束可能包括主键约束、唯一索引约束、外键约束等等。事务执行前后,数据都是合法的状态,不会违背任何的数据完整性
就拿转账来说,A和B加起来有5000块钱,不管A和B如何转账,转几次账,A和B加起来的钱永远都是5000块。
总之,可以理解为:一致性是为了保证数据的完整性。

7.1.3 隔离性(Isolation)

隔离性是指当多个用户并发操作数据库,比如操作同一张表,数据库为每一个用户开启的事务,不能被其他的事务所干扰或者影响,事务之间是彼此独立的。

7.1.4 永久性(Durability)

永久性是指一个事务一旦提交了,那么对数据库中数据的改变就是永久的,即使是在数据库发生故障时,也不会丢失事务提交的数据。

7.2 事务的隔离性

事务的隔离性。当多个线程开启事务操作数据库中的数据时,数据库要能进行隔离操作,以保证各个线程获取数据的准确性; 如果不考虑事务的隔离性,会发生以下几个问题;

7.2.1、脏读

脏读是指一个事务在处理过程中读取了另一个事务未提交的数据。比如,A向B转账
update account set money = money + 100 where name = ‘B’;
update account set money = money – 100 where name = ‘A’
当只执行第一条SQL时,A通知B查看账户,B发现确实钱已到账(此时即发生了脏读),
而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,那么当B以后再次查看账户时就会发现钱其实并没有转。

7.2.2、不可重复读

不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。
例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库,事务T1再次读取该数据就得到了不同的结果,发生了不可重复读。
不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。
在某些情况下,不可重复读并不是问题,比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题,例如对于同一个数据A和B依次查询就可能不同,A和B就可能打起来了……

7.2.3、幻读

幻读是事务非独立执行时发生的一种现象。
例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)

7.3 MySQL事务的隔离级别

四种隔离级别最高的是Serializable级别,最低的是Read uncommitted级别,当然级别越高,执行效率就越低。
像Serializable这样的级别,就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待)
所以平时选用何种隔离级别应该根据实际情况。
Serializable (串行化):可避免脏读、不可重复读、幻读的发生 >
Repeatable read (可重复读):可避免脏读、不可重复读的发生 >
Read committed (读已提交):可避免脏读的发生 >
Read uncommitted (读未提交):最低级别,任何情况都无法保证
MySQL支持以上4种隔离级别,默认的隔离级别是Repeatable read (可重复读)
Oracle只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别,默认的隔离级别是Read committed (读已提交)

7.3.1 查看和设置MYSQL的事务隔离级别

每启动一个 MySQL 程序,就会获得一个单独的数据库连接。每
个数据库连接都有一个全局变量 @@transaction_isolation,表示当前的
事务隔离级别。

老版本 MySQL 比如 5 中用的是 tx_isolation,而应该是在 5.7.20 版本之后,用的是 transaction_isolation。

select  或 selecton;

数据库篇·第四章[2]·MYSQL
设置当前 MySQL 连接的隔离级别:

set session transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

7.3.2 举例说明事务的隔离级别

7.3.2.1 Repeatable read (可重复读)

数据库篇·第四章[2]·MYSQL

7.3.2.2 Read committed (读已提交–>脏读)

现在试试将隔离级别设置为读已提交,返回的结果又是什么
设置当前 MySQL 连接的隔离级别
数据库篇·第四章[2]·MYSQL

部分转自互联网,侵权删除联系

赞(0) 打赏
部分文章转自网络,侵权联系删除b2bchain区块链学习技术社区 » 数据库篇·第四章[2]·MYSQL求职学习资料
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

b2b链

联系我们联系我们