本节书摘来自华章出版社《MySQL DBA修炼之道》一书中的第3章，第3.3节，作者：陈晓勇，更多章节内容可以访问云栖社区“华章计算机”公众号查看
3.3 SQL基础
SQL是一种高级查询语言，它是声明式的，也就是说，只需要描述希望怎么获取数据，而不用考虑具体的算法实现。
3.3.1 变量
MySQL里的变量可分为用户变量和系统变量。
1.用户变量
用户变量与连接有关。也就是说，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。这点不同于在函数或存储过程中通过DECLARE语句声明的局部变量，局部变量的生存周期在它被声明的“BEGIN…END”块内。对于用户变量的值，可以先保存在用户变量中，然后以后再引用它；这样就可以将值从一个语句传递到另外一个语句。
用户变量的形式为@var_name。
设置用户变量的一个途径是执行SET语句，语法如下。SET @var_name= expr[, @var_name= expr] ...
对于SET，可以使用“=”或“:=”作为分配符。分配给每个变量的expr可以为整数、实数、字符串或NULL值。如：mysql> SET @t1=0, @t2=0, @t3=0;
或：SET @minMid=(select min(id) FROM table_name) ;
2.系统变量
MySQL服务器维护着两种系统变量：全局变量影响MySQL服务的整体运行方式；会话变量影响具体客户端连接的操作。
当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中对指定的选项进行更改。服务器启动后，通过连接服务器并执行SET GLOBAL var_name语句，可以动态更改这些全局变量。要想更改全局变量，必须具有SUPER权限。
服务器还为每个连接的客户端维护一系列的会话变量。在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。对于动态会话变量，客户端可以通过SET SESSION var_name语句更改它们。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其他客户端的会话变量。
访问全局变量的任何客户端都可以看见对全局变量所做的更改。然而，它只影响更改后连接的客户的相应会话变量，而不会影响目前已经连接的客户端的会话变量（即使客户端执行SET GLOBAL语句也不影响）。也就是说，如果你的连接是短连接，那么修改全局变量后，客户端有重连的操作，就会立刻影响到客户端。而对于长连接、连接池来说，连接可能一直在MySQL里没有被销毁，也就不会有重连的操作，所以这种情况下对全局变量的修改一般不会影响到客户端。
可以使用如下几种语法形式来设置或检索全局变量或会话变量（下面的例子使用sort_buffer_size作为示例变量名）。
要想设置一个GLOBAL变量的值，可使用下面的语法。mysql> SET GLOBAL sort_buffer_size=value;mysql> SET @@global.sort_buffer_size=value;
要想设置一个SESSION变量的值，可使用下面的语法。mysql> SET SESSION sort_buffer_size=value;mysql> SET @@session.sort_buffer_size=value;mysql> SET sort_buffer_size=value;
如果设置变量时不指定GLOBAL、SESSION或LOCAL，则默认使用SESSION。
要想检索一个GLOBAL变量的值，可使用下面的语法。mysql> SELECT @@global.sort_buffer_size;mysql> SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';
要想检索一个SESSION变量的值，可使用下面的语法。mysql> SELECT @@sort_buffer_size;mysql> SELECT @@session.sort_buffer_size;mysql> SHOW VARIABLES LIKE 'sort_buffer_size';
当用SELECT @@var_name搜索一个变量时（也就是说，不指定GLOBAL、SESSION），MySQL会返回SESSION值（如果存在SESSION变量的话），否则返回GLOBAL值。
对于SHOW VARIABLES，如果不指定GLOBAL、SESSION的话，MySQL会返回SESSION值。
3.3.2 保留字
MySQL显式保留了表3-9（摘自官方文档）中的关键字。其中大多数关键字被标准SQL用作列名和/或表名（例如GROUP）。少数被保留了，因为MySQL需要它们。在生产环境下，常犯的一个错误是，使用了MySQL保留的关键字作表名、列名，这会导致部署、升级失败或留下隐患。
3.3.3 MySQL注释
MySQL服务器支持如下3种注释风格。
从“#”字符至行尾。
从“--”序列到行尾。请注意，“--”（双破折号）注释风格要求第2个破折号的后面至少要跟一个空格符（例如空格、tab、换行符等）。之所以要求使用空格，是为了防止出现非预期结果。比如，对于语句“UPDATE account SET credit=credit--1”，则是表示credit的值减去-1，这样的语法是合格的，而不会误认为“--1”是注释。
从/序列到后面的/序列。结束序列不一定在同一行中，因此该语法允许注释跨越多行。
下面的例子显示了3种风格的注释。
mysql> SELECT 1+1; # This comment continues to the end of line
mysql> SELECT 1+1; -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;```
MySQL对标准SQL进行了扩展，如果使用了它们，将无法把代码移植到其他数据库的服务器上。可以用“/*…*/”注释掉这些扩展。如下例子中，MySQL服务器能够解析并执行注释中的代码，就像对待其他SQL语句一样，但其他数据库服务器将忽略这些扩展。
``SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...``
如果在字符“!”后添加了版本号，那么仅当MySQL的版本等于或高于指定的版本号时才会执行注释中的语法，比如下面这条语句。
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
这就意味着，如果你的版本号为3.23.02或更高，那么MySQL服务器将使用TEMPORARY关键字。
**3.3.4 数据类型**
MySQL支持常用的数据类型：数值类型、日期/时间类型和字符串（字符）类型。
**1.数值类型**
数值类型可分为两类：整型和实数。对于实数，MySQL支持确切精度的值（定点数）和近似精度的值（浮点数）。确切精度的数值类型有DECIMAL类型，近似精度的数值类型有单精度（FLOAT）或双精度（DOUBLE）两种类型。
（1）整型
整型包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，表3-10展示了各种整型的空间占用及表示的数值范围。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/1a301e610bd2872274de1d3d0b1d67067c48cd94.png" ><img src="https://yqfile.alicdn.com/a8d97b6e4d3f4c8c90fa311396873f5e93060f0f.png" >
</div>
在表3-10中，无符号（unsigned）属性可扩展一倍的最大值上限。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/57b208f048e707beb3a1fe357651b699356a9881.png" >
</div>
由于MySQL的内部类型只支持到秒级别的精度，因此可以用BIGINT来存储精度到毫秒的时间戳。
开发数据库应用的时候，需要注意的是，应保留足够的范围来满足未来的数据增长需要，对于超过数值范围的插入/修改数据，MySQL将报错失败，例如对于SMALLINT类型，值的范围为-32 768～32 767，那么在自增ID列（后文会详述）的值已经到了32 767后，还继续插入记录，就会报错“Duplicate entry '32767' for key 'PRIMARY”，而且更新的值超过最高阈值时也会报错，如“Out of range value for column 'id' at row 1”。可以设置unsigned属性来扩展数据范围。
（2）DECIMAL和NUMERIC类型（定点数）
定点数也就是DECIMAL型，指的是数据的小数点的位置是固定不变的。也就是说，小数点后面的位数是固定的。
DECIMAL和NUMERIC在MySQL中被视为相同的类型。它们用于保存必须为确切精度的值，例如货币数据。当声明该类型的列时，可以（并且通常要）指定精度和标度；比如，在DECIMAL(M,D)中，M是精度，表示数据的总长度，也就是十进制数字的位数，不包括小数点；D是标度，表示小数点后面的数字位数。在MySQL 5.1中，M的范围是1～65，D的范围是0～30且不能大于M。例如下面这条语句，5是精度，2是标度。
``salary DECIMAL(5,2)``
对于数值123 456 789.12345，可以这样定义，M = 14，D = 5。
在MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC的值。如果值太大超出了BIGINT的范围，也可以用DECIMAL存储整型。
定点数表达法的缺点在于其形式过于僵硬，固定的小数点位置决定了固定位数的整数部分和小数部分，不利于同时表达特别大的数或特别小的数。
（3）FLOAT和DOUBLE类型（浮点数）
浮点数（floating-point number）是属于有理数中某个特定子集的数的表示法，在计算机中用于近似地表示任意某个实数。具体来说，这个实数是由一个整数或定点数（即尾数）乘以某个基数（计算机中通常是2）的整数次幂（指数）得到的，这种表示方法类似于基数为10的科学记数法。比如123.45可以用十进制科学计数法表达为“1.2345×102”，其中 1.2345为尾数，10为基数，2为指数。浮点数利用指数达到了浮动小数点的效果，从而可以灵活地表达更大范围的实数。
在MySQL中，对于浮点列类型，单精度值（FLOAT）使用4个字节，双精度值（DOUBLE）使用8个字节。浮点数可以比整型、定点数表示更大的数值范围。
为了保证最大可能的可移植性，对于使用近似数值存储的代码，应使用FLOAT或DOUBLE来表示，不规定精度或位数。由于浮点数存在误差问题，如果用到浮点数，要特别注意误差的问题，并尽量避免做浮点数比较。
MySQL允许使用非标准语法：FLOAT(M,D)或DOUBLE(M,D)。这里，“(M,D)”表示该值一共显示了M位整数，其中D位整数位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时会进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。
浮点型（FLOAT/DOUBLE）对比定点类型（DECIMAL）使用的空间更少，所以为了减少存储空间，应尽量不要使用DECIMAL，除非是在保存确切精度的值时，比如货币数据。
**2.日期/时间类型**
表示时间值的日期和时间类型有DATETIME、DATE、TIMESTAMP、TIME和YEAR等。每个时间类型都有一个有效值范围，TIMESTAMP类型有其特有的自动更新特性。
如果试图插入一个不合法的日期，MySQL将给出警告或错误。可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期，例如'1999-11-31'。在这种模式下，MySQL只验证月的范围是否为从0到12，日的范围是否为从0到31。有时应用程序希望保存一个特定的不合法日期，以便将来进行处理，这时可以利用这个模式。但更常见的处理方式是设置一个不可能的特定的合法日期值，如'9999-01-01 00:00:00'。
如果是没有使用NO_ZERO_DATE的SQL模式，默认情况下，MySQL只允许在DATE或DATETIME列保存月和日是零的日期。这在应用程序中需要保存一个你不知道确切日期的生日时非常有用，在这种情况下，只需要将日期保存为'1999-00-00'或'1999-01-00'
即可。
如果不使用NO_ZERO_DATE SQL模式，MySQL还允许将'0000-00-00'保存为“伪日期”。这在某些情况下比使用NULL值更方便，并且数据和索引占用的空间更小。
MySQL以标准输出格式检索给定日期或时间类型的值，但它会尽力解释你指定的各种输入值格式。尽管MySQL在尝试解释几种格式的值时，日期总是以“年–月–日”的顺序（例如，'98-09-04'）来处理的，而不是以“月–日–年”或“日–月–年”的顺序（例如，'09-04-98'、'04-09-98'）。
包含两位年值的日期会令人产生困惑，因为不知道世纪。MySQL使用以下规则解释两位年值的日期。
70～99范围的年值均转换为1970～1999。
00～69范围的年值均转换为2000～2069。
（1）DATETIME、DATE和TIMESTAMP类型
当需要同时包含日期和时间信息的值时，建议使用DATETIME（日期时间组合）类型。MySQL以'YYYY-MM-DD HH:MM:SS'的格式检索和显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。DATETIME类型占8个字节。
当只需要日期值而不需要时间部分时，建议使用DATE（日期）类型。MySQL用'YYYY-MM-DD'格式检索和显示DATE值，但允许使用字符串或数字为DATE列分配值。支持的范围是'1000-01-01'到 '9999-12-31'。DATE类型占3个字节。
TIMESTAMP（时间戳）列用于在进行INSERT或UPDATE操作时记录日期和时间。TIMESTAMP列的显示格式与DATETIME列相同。换句话说，显示宽度固定在19个字符，并且格式为'YYYY-MM-DD HH:MM:SS'。TIMESTAMP的范围是从'1970-01-01 00:00:01'UTC 到'2038-01-09 03:14:07'UTC。TIMESTAMP类型占4个字节。
TIMESTAMP的值以UTC格式进行保存，存储时会对当前的时区进行转换，检索时再转换回当前的时区。当前时区对应的是time_zone系统变量。
控制TIMESTAMP列的初始化和更新的规则如下。
若将TIMESTAMP类型字段定义为default current_timestamp，那么插入一条记录时，该TIMESTAMP字段自动被赋值为当前时间。
若将TIMESTAMP类型字段定义为on update current_timestamp，那么修改一条记录时，该TIMESTAMP字段自动被修改为当前时间。
可以将这些类型联合使用，如default current_timestamp on update current_timestamp。
可以给TIMESTAMP字段指定一个默认值，也可以在SQL语句中指定TIMESTAMP字段的值。
可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP的值。
对于'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串，允许“不严格”语法：任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如，'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 11*30*45'和'98@12@31 11^30^45'是等价的。
对于'YYYY-MM-DD'或'YY-MM-DD'格式的字符串，也允许使用“不严格的”语法。例如，'98-12-31'、'98.12.31'、'98/12/31'和'98@12@31'是等价的。
（2）TIME（时间）类型
该时间类型的范围是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式检索和显示TIME值（或者对于大的小时值采用'HHH:MM:SS'格式），但允许使用字符串或数字为TIME列分配值。TIME类型占3个字节。
（3） YEAR（两位或四位格式的年）类型
YEAR类型表示两位或四位格式的年。MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。
默认是四位格式，在四位格式中，允许的值是1901～2155和0000。
在两位格式中，如果是两位字符串，那么范围为'00'～'99'。'00'～'69'和'70'～'99'范围的值被分别转换为2000～2069和1970～1999范围的YEAR值。
如果是两位整数，范围为1～99。1～69和70～99范围的值被分别转换为2001～2069和1970～1999范围的YEAR值。请注意，两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'，或者它被解释为0000。
YEAR类型占1个字节。
**3.字符串类型**
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
（1）CHAR和VARCHAR类型
CHAR与VARCHAR类型类似，但它们保存和检索数据的方式不同。
CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。注意，在CHAR(M)、VARCHAR(M)声明里，M是字符个数而不是字节。
如果分配给CHAR或VARCHAR列的值超过了列的最大长度，则对值进行裁剪以使其长度适合。如果被裁剪掉的字符不是空格，则会产生一条警告。
CHAR是固定长度的字符串，它的长度固定为创建表时声明的长度。长度范围为0到255个字符。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格会被删除掉，这是MySQL服务器级别控制的，和存储引擎无关。CHAR类型适合存储大部分值的长度都差不多的数据，例如MD5值。
VARCHAR列中的值为可变长度的字符串。长度可以指定为0到65 535之间的值（VARCHAR的最大有效长度由最大记录长度和使用的字符集确定。整体最大长度是65 532字节）。相对于固定长度的字符串，它需要更少的存储空间。在保存VARCHAR的值时，只保存需要的字符数，然后用1～2个字节来存储值的长度，所以如果是很短的值（如仅一个字符），那么耗费的存储空间比CHAR还会多些，所以，如果想存储很短的类型，使用CHAR会更合适。VARCHAR可选的一种场景是最长记录的长度值比平均长度的值大得多。
保存VARCHAR的值时不会进行填充。当值保存和检索时尾部的空格仍会保留，这一点符合标准SQL。
实际上，各存储引擎存取VARCHAR和CHAR的方法不尽相同。比如，内存引擎使用固定长度的行，会在内存中分配最大可能空间给VARCHAR类型，所以CHAR(5)和VARCHAR(200)在存储“hello”字符串时占据的空间大小是一样的，但VARCHAR(200)会耗费更大的内存空间。
（2）BINARY和VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是，它们包含的是二进制字符串而不是非二进制字符串。也就是说，它们包含的是字节字符串而不是字符字符串，它们的长度是字节长度而不是字符长度。这说明它们没有字符集，并且排序和比较也是基于字节的二进制值进行的。
相对来说，二进制字符串的比较比字符字符串的比较更为简单有效。
对于“随机”字符串，如 MD5()、SHA1()或UUID()生成的值会导致数据非常分散，没有明显的热点数据，还可能导致数据库缓存不能很好的工作。因此建议把MD5()、UUID()之类的值再散列下，生成整型值。
（3）BLOB和TEXT类型
BLOB是一个二进制大对象，可以容纳可变数量的数据。BLOB类型共有4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。BLOB 是SMALLBLOB的同义词。
TEXT类型也有4种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们分别对应上面的4种BLOB类型，有相同的最大长度和存储需求。TEXT是SMALLTEXT的同义词。
BLOB用于存储二进制字符串（字节字符串），而TEXT列则被视为非二进制字符串（字符字符串）的存储方式，它是有字符集和排序规则的，这两种类型都用于存储大量数据，具体的存储方式按存储引擎各有不同。
在大多数情况下，可以将BLOB列视为能够存储足够大数据的VARBINARY列。同样，也可以将TEXT列视为VARCHAR列。但是，BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR。
保存或检索BLOB和TEXT列的值时不用删除尾部的空格。
对于BLOB和TEXT列的索引，必须指定索引前缀的长度。
BLOB和TEXT列不能有默认值。
排序时只使用该列的前max_sort_length个字节。max_sort_length的默认值是1024。
BLOB或TEXT对象的最大长度由其类型来确定，但在客户端和服务器之间实际可以传递的最大数据量是由可用内存数量和通信缓存区的大小来确定的。可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端的程序。
使用BLOB、TEXT等大字段可能会导致严重的性能问题，比如导致产生磁盘临时表。
MySQL的临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎。由于MEMORY存储引擎不支持BLOB和TEXT类型，所以如果有查询使用了BLOB或TEXT列且需要隐式使用临时表（MEMORY存储引擎）来进行排序，那么将不得不使用磁盘临时表，磁盘比内存慢得多，这会导致很严重的性能问题。
（4）ENUM类型
ENUM（枚举）类型是一个字符串对象，其值通常选自一个允许值列表，该列表是在创建表时被定义的。
对于ENUM类型，需要慎重使用，如果候选值的集合可能发生改变，那么使用它就不见得是一个好主意。对于一些属性有固定数量的候选值的场景，可以使用其他更通用的方案，这样也能更方便地迁移到其他数据库，如用TINIINT类型代替ENUM类型，可以靠应用程序去维护字符串值和TINIINT的映射关系，或者增加一个表来存储映射关系，或者就直接存储更“自然”的字符串值。在现实世界中，空间大小一般已经不再是一个问题，自然、直观往往是更值得考虑的因素。
这里省略了对SET类型的介绍，感兴趣的读者可自行查阅相关图书。
**4.数据类型存储需求**
常用数值类型的存储需求见表3-11。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/72136997e69219e5efb42ff51ec526443232af0f.png" >
</div>
日期和时间类型的存储需求见表3-12。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/9d65d62bbb319687e77fb8ac84ec096731c97f10.png" >
</div>
字符串类型的存储需求见表3-13，其中的“L”代表字符串的字节长度。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/ec07ad153a63284443f54f910d7aebd343391046.png" ><img src="https://yqfile.alicdn.com/d7ed28e554891a7a032c3b504c3ef9e49d5731e4.png" >
</div>
要想计算用于保存具体CHAR、VARCHAR或TEXT列值的字节数，需要考虑该列使用的字符集。例如utf8字符集，存储汉字是3个字节，存储英文字符是1个字节。
以上VARCHAR、VARBINARY、BLOB和TEXT类型都是可变长度的类型，它们的存储需求取决于如下3个因素。
列值的实际长度。
列的最大可能长度，如行长度有65 536个字节的限制。
字符集。
例如，一个VARCHAR(255)列可以容纳一个最大长度为255个字符的字符串。如果该列使用latin1字符集（每个字符占一个字节），那么所需的实际存储为字符串字节的长度（L），再加上一个字节以记录字符串的长度。对于字符串'ABCD'，L等于4，存储需求是5个字节。如果该列使用UCS2双字节字符集，那么存储要求为10个字节：'ABCD'的长度为8个字节，再需要2个字节来存储长度，因为它的最大长度大于255个字节（此时VARCHAR(255)最多为510个字节）。
可以存储在VARCHAR或VARBINARY列的字节还受到最大行长（65 535字节）的限制。很显然，对于VARCHAR列，如果存储多字节字符，实际能够存储的字符会更少。例如，utf8字符集每个字符最多三个字节，所以使用utf8字符集的VARCHAR列可以被声明为最多21 844个字符。
**5.选择合适的数据类型**
MySQL支持许多数据类型，选择合适的数据类型可以获得更好的性能，从而更节省
空间。
以下是一些指导原则。
（1）各表使用一致的数据类型
字段在每个表中都应该使用一样的数据类型、长度，因为以后可能需要进行JOIN（连接）操作，这样做是为了避免无谓的转换或可能出现不期望的结果。我们不仅要考虑数据类型是如何存储的，也要清楚数据类型是如何计算和比较的。
（2）小往往更好
选择更短的数据类型。更短的类型意味着更少的磁盘空间、更少的内存空间、更少的CPU处理时间。例如，如果列值的范围为从1～99 999，若使用整数，则MEDIUMINT UNSIGNED是比较好的数据类型。在所有可以表示该列值的类型中，该类型使用的存储
最少。
（3）简单类型更好
简单的数据类型能够进行更快的处理。例如，整型值比字符类型运算得更快，因为字符的字符集和排序规则使字符的比较运算变得更为复杂。生产环境中经常会看到用字符或整型来存储时间，为了使数据更友好、自然，建议还是使用MySQL内建的类型来存储日志时间会更好。使用无符号整型来存储IP地址（IP本质上是一个无符号的整型，点分的形式只是为了方便我们阅读）也是常用的好办法，可用INET_ATON() 和 INET_NTOA执行
转换。
（4）尽可能避免NULL值
应尽量显式定义“not NULL”，如果查询涉及的是NULL值的字段，MySQL会很难去优化查询。可使用0、空字符串或特殊的值来代替NULL存储。当然，也不要去刻意追求“not NULL”，因为更改NULL字段为“not NULL”，对性能的提升可能没什么太大的作用，让设计更自然、更具可理解性应该更值得看重。熟悉Oracle数据库的读者需要留意，MySQL会索引NULL值，而Oracle则不会。
**3.3.5 函数**
以下介绍常用的函数和操作符。
**1.数值函数**
（1）算数操作符
可使用常见的算数操作符。例如‘+’、‘-’、‘/’、DIV（整除）。
（2）数学函数
ABS(X)：X的绝对值。
CEIL(X)：返回不小于X 的最小整数值。
FLOOR(X)：返回不大于X的最大整数值。
CRC32(X)：计算循环冗余码校验值并返回一个 32比特无符号值。
RAND()、RAND(N)：返回一个随机浮点值v，范围在0到1之间（即其范围为0≤v≤1.0）。若已指定一个整数参数N，则它被用作种子值，用来产生重复序列。
注意不要使用此函数做随机排序，如下的语句形式效率会很差，仅适合很小的表。
SELECT * FROM table_name ORDER BY RAND() LIMIT 1;
SIGN(X)：返回X的符号。
TRUNCATE(X,D)：返回被舍去至小数点后D位的数字X。若D 的值为 0，则结果不带有小数点或不带有小数部分。
ROUND(X)、ROUND(X,D)：返回参数X，其值接近于最近似的整数。在有两个参数的情况下，返回X，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要保留X值到小数点左边的D位，可将D设为负值，例如，ROUND（123.45，-1）的结果是120，ROUND（167.8，-2）的结果是200。
**2.字符类型处理函数**
CHAR_LENGTH(str)：返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含5个二字节的字符集，LENGTH()的返回值为10，而CHAR_LENGTH()的返回值为5。
LENGTH(str)：返回值为字符串str 的长度，单位为字节。
CONCAT(str1,str2,...)：返回结果为连接参数产生的字符串。如下查询将拼接'My'、
'S'、'QL'3个字符串。mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'```LEFT(str,len)：从字符串str开始，返回最左len个字符。
RIGHT(str,len)：从字符串str 开始，返回最右len个字符。
SUBSTRING(str,pos)、SUBSTRING(str,pos,len)：不带有len 参数的格式是从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式是从字符串str返回一个长度同len字符相同的子字符串，起始于位置pos。
如下查询将返回字符串Quadratically第5个字符之后的所有字符。
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'```
如下查询将返回字符串Quadratically第5个字符之后的6个字符。mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'```LOWER(str)：返回字符串str转化为小写字母的字符。
UPPER(str)：返回字符串str转化为大写字母的字符。
3.日期和时间函数
NOW()：返回当前日期和时间的值，其格式为'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS。
CURTIME()：将当前时间以'HH:MM:SS'或HHMMSS的格式返回。
CURDATE()：将当前日期按照'YYYY-MM-DD'或YYYYMMDD格式的值返回。
DATEDIFF(expr1,expr2)：是返回开始日期expr1与结束日期expr2之间相差的天数，计算中只用到这些值的日期部分。返回值为正数或负数。
DATE_ADD(date,INTERVAL expr type)、DATE_SUB(date,INTERVAL expr type)：这些函数执行日期运算。date是一个DATETIME或DATE值，用来指定起始时间。expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。type为关键词，它指示了表达式被解释的方式。type常用的值有SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR。示例代码如下所示。
``
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',INTERVAL 1 SECOND);
-> '1998-01-01 00:00:00'``
``mysql> SELECT DATE_ADD('1997-12-31 23:59:59',INTERVAL 1 DAY);
-> '1998-01-01 23:59:59'``
DATE_FORMAT(date,format)：下面的代码会根据format字符串安排date值的格式。常用的日期格式'YYYY-MM-DD HH:MM:SS'，对应的format为'%Y-%m-%d %H:%i:%S'，示例代码如下所示。
mysql>SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
'22:23:00'STR_TO_DATE(str,format)：是DATE_FORMAT() 函数的倒转。它将获取一个字符串str和一个格式字符串format。
若格式字符串包含日期和时间部分，则STR_TO_DATE()返回一个 DATETIME 值，若该字符串只包含日期部分或只包含时间部分，则返回一个 DATE 或TIME值。示例代码如下所示。
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
2004-04-313.3.6 操作符及优先级
运算符的优先级决定了不同的运算符在表达式中计算的先后顺序。一般情况下，级别高的运算符先进行计算，如果级别相同，MySQL则会按照表达式的顺序从左到右依次计算。以下是按照从低到高的优先级列出的各种运算操作符。
:=
||、OR、XOR
&&、AND
NOT
BETWEEN、CASE、WHEN、THEN、ELSE
=、<=>、>=、>、<=、<、<>、!=、IS、LIKE、REGEXP、IN
|
&
<<、>>
-、+
/（DIV）、%（MOD）
^（按位异或）
-（负号）、～（按位取反）
!
如果不能确定优先级，可以使用圆括号()来改变优先级，并且这样会使计算过程更加清晰。比如，如下的查询，我们会先计算最里层括号里面的表达式(2+3)，然后计算外层的表达式。这点类似于我们学过的算术运算。
select 1*(3-2)*(3+3+3*(2+3));3.3.7 MySQL示例employees数据库
MySQL提供了一个练习用的示范数据库employees，建议下载“employees_db-full-1.0.6”。
employees示例数据库一共有6张表，约400万条记录，包含160MB的数据。
首先是安装数据库，安装命令如下。
默认导入数据是InnoDB引擎，如果需要指定其他引擎，可以修改employees.sql文件，取消注释相应的引擎，命令如下。
使用MySQL命令将数据导入到实例中。
``
mysql -t < employees.sql``
通过以下命令验证范例数据导入是否正确。
``
time mysql -t < test_employees_sha.sql``
实体关系图3-3描述了employees示例数据库各表的结构和它们之间的关系。
图3-3中的各表通过一些字段相互关联，如dept_emp表中存储了部门职员的信息，通过dept_emp.emp_no可以到employees表中去查询职员的记录。各表之间的关系是通过连线和特殊符号标明的，钥匙标记表示这是主键，关系中属于“多”的这一边用一个类似鸟爪的图形来表示，如dept_emp表，主键是联合主键（emp_no, dept_no），employees和dept_emp表就是一对多的关系，由于职员可能在不同时期属于不同的部门，那么employees表中一条职员的记录可能在dept_emp表中存在多条对应记录。读者可自行下载此数据库，验证各表的数据和彼此之间的联系。
图3-3 employees示例数据库实体关系图
3.3.8 SQL语法
结构化查询语言（Structured Query Language，SQL）是一种高级编程语言，是数据库中的标准数据查询语言，这种语言是描述性的，很容易上手，你不需要了解数据是如何存储的也能编写出语句查询和修改数据，这项技能并非IT人士的专有领域，其他非计算机行业的人，虽然不会编程，但也可以根据自己的业务需求，用SQL在公司的数据平台上查询数据。所以，我们设计的库表，如果有其他业务部门要使用，而且是通过SQL的方式进行查询，那么表名、列名就需要考虑下自然性和易用性。
美国国家标准学会（ANSI）对SQL进行规范后，将其作为关系式数据库管理系统的标准语言，而后在国际标准组织的支持下成为了国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些改编和扩充。所以，实际上不同数据库系统之间的SQL并不能完全相互通用。一般情况下，扩展语法后功能虽有所增强，但可能会导致移植性变差，而且对于整个系统的吞吐率、性能可能也不会有明显的改善。因此本书不会对MySQL的扩展语法进行详细介绍。下面使用自带的MySQL命令行工具来演示示例。
1. SQL常见操作
使用如下命令可查看MySQL支持的选项。
shell> mysql –help
使用如下命令可连接MySQL Server。
shell> mysql -h host -P port -u user –p
Enter password: ********连接登录成功后，可以按Ctrl+D退出，或者输入QUIT退出，命令如下。
mysql> QUIT
Bye下面来运行一个简单的查询，通过以下语句可查询当前MySQL Server的版本和当前日期。
使用如下命令可创建数据库employees。mysql> CREATE DATABASE employees;
显示数据库，切换到test数据库时可使用如下命令。mysql> SHOW DATABASES;mysql> USE test
使用如下命令可显示当前的数据库。SELECT DATABASE();
使用如下命令可显示数据库下的表。mysql> SHOW TABLES
2.数据定义语句（DDL）
以下介绍常用的DDL语句。
（1）创建和删除表
可使用CREATE TABLE语句创建表。
可使用DESC语句验证创建的表结构。mysql> DESC employees_2
使用DROP TABLE语句删除表。DROP TABLE employees_2;
（2）使用ALTER TABLE语句修改表结构
首先创建表t1。mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
把表t1重新命名为t2。mysql> ALTER TABLE t1 RENAME t2;
把列a从INTERGER类型更改为TINYINT NOT NULL（名称保持不变），并把列b从CHAR(10)更改为CHAR(20)，同时把列b重新命名为列c。mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
添加一个新的TIMESTAMP列，名称为d。mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在列d和列a中添加索引。mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
删除列c。mysql> ALTER TABLE t2 DROP COLUMN c;
添加一个新的AUTO_INCREMENT整数列，名称为c。mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c);
（3）使用CREATE INDEX语句创建索引
在表lookup的列id上创建索引。CREATE INDEX id_index ON lookup (id);
在customer表的name列上创建一个索引，索引使用name列的前10个字符。CREATE INDEX part_of_name ON customer (name(10));
在tbl_name表的a、b、c列上创建一个复合索引。CREATE INDEX idx_a_b _c ON tbl_name(a,b,c);
（4）使用DROP INDEX语句删除索引
删除表tbl_name上的index_name索引时使用如下命令。DROP INDEX index_name ON tbl_name;
（5）修改字符集和排序规则
可使用如下命令更改排序字符集。ALTER TABLE test.tt1 CHANGE v2 v2 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;
可使用如下命令更改排序规则。ALTER TABLE table_name CHANGE col_a col_a VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_bin
3.数据操作语句（DML）
以下是一些查询语句常用的语法和示例。需要留意的是，我们日常所说的查询语句，不仅包括SELECT查询语句，也包括INSERT、UPDATE、DELETE等修改数据的语句。在创建表之后，就可以导入数据了，导入数据的方式有二：或采用LOAD DATA语句，或采用INSERT语句。
以下主要介绍INSERT、SELECT、UPDATE、DELETE语句，LOAD DATA语句在以后的章节中会有介绍。
（1）INSERT语句
语法如下所示。
INSERT INTO table_name (column1, column2....)
VALUES (value1, value2...);```
具体示例如下。
MySQL支持用一条INSERT语句插入多条记录，这样可以加快数据的导入，比如下面的示例。
（2）修改数据（UPDATE）
语法如下所示。
``UPDATE table_name SET column_name1 = value1,``
``column_name2 = value2,column_name3 = value3 ...
[WHERE conditions];
``
以下命令可将员工编号为10001的员工姓名修改为gary wang。
``UPDATE employees set first_name='gary',last_name='wang' where emp_no=10001;``
（3）删除（DELETE）
语法如下所示。
``DELETE FROM table_name [WHERE conditions];``
以下命令可删除员工编号为1000000的员工记录。
``DELETE from employees WHERE emp_no = 1000000;``
（4）SELECT语句
语法如下所示。
``SELECT column_names FROM table_name [WHERE ...conditions];``
可使用如下命令查询表employees的所有数据。
``SELECT * FROM employees;``
可使用如下命令查询表employees的emp_no、birth_date、first_name、last_name这几个特定列的数据。
``SELECT emp_no,birth_date,first_name,last_name FROM employees;``
查询employees表中出生日期晚于'1960-01-01'的员工，使用WHERE子句，加上比较操作符“>”即可，命令如下。
``SELECT emp_no,birth_date,first_name,last_name FROM employees WHERE birth_date > '1960-01-01';``
可使用如下命令查询employees表中出生日期早于'1960-01-01'的员工。
``SELECT emp_no,birth_date,first_name,last_name FROM employees WHERE birth_date < '1960-01-01';``
可使用如下命令查询employees表中first_name等于Divier的员工。
``SELECT * FROM employees WHERE first_name='Divier';``
SELECT查询是需要我们重点掌握的，下文将详细讨论SELECT查询。
（1）SQL模式匹配
SQL有两个通配符，“-”匹配任意单个字符，“%”匹配任意多个字符（包括0个字符）。
模式匹配默认是区分大小写的，它一般使用LIKE或NOT LIKE这些比较操作符，比如，要查询employees表中first_name列以字母D开始的员工记录，可使用如下命令。
``SELECT * FROM employees WHERE first_name LIKE 'D%';``
查询employees表中first_name列以Ang开头，一共5个字符，last_name以Con开头，一共5个字符的记录时可使用如下命令。
（2）逻辑操作符与或非（AND、OR、NOT）
可以用逻辑操作符组合成多个筛选条件，示例如下。
选择employees表first_name列等于Parto，而且last_name列等于Alpay的记录。
``SELECT emp_no,birth_date,first_name,last_name,gender,hire_date FROM employees``
`` WHERE first_name='Parto' AND last_name='Alpay';``
选择employees表中'1995-01-31'或'1996-11-21'入职的员工。
``SELECT emp_no,birth_date,first_name,last_name,gender,hire_date FROM employees``
``WHERE hire_date='1995-01-31' OR hire_date='1996-11-21';``
选择employees表中last_name列不是以字母A开头的所有记录。
``SELECT * FROM employees WHERE last_name NOT LIKE 'A%';``
（3）范围操作符IN和BETWEEN
选择employees表中分别在'1964-06-01'、'1964-06-02'和'1964-06-04'这3天出生的员工时可使用如下命令。
``SELECT * FROM employees WHERE birth_date IN ('1964-06-01','1964-06-02','1964-06-04');``
选择employees表中在'1964-06-01'至'1964-06-04'期间出生的员工时可使用如下命令。
``SELECT * FROM employees WHERE birth_date BETWEEN '1964-06-01' AND '1964-06-04';``
（4）限制获取记录数（使用LIMIT子句）
只获取employees表中的5条记录（没顺序）时可使用如下命令。
``SELECT * FROM employees LIMIT 5;``
（5）排序（ORDER BY）
查询按出生日期排序的最老的10名员工时可使用如下命令。
``SELECT * FROM employees ORDER BY birth_date ASC LIMIT 10;``
查询按出生日期排序第100至109名的员工时可使用如下命令。
``SELECT * FROM employees ORDER BY birth_date ASC LIMIT 100,10;``
（6）数据计算
MySQL提供了一些计算函数，下面给出了计算日期的示例，后续章节会专门叙述此类常用的函数。
以下命令将计算employees表中员工的年龄，并且按first_name、last_name排序，返回记录数限制在10条。
``SELECT
emp_no,
first_name,
last_name,
birth_date,
curdate(),``
`` timestampdiff(year,
birth_date,
curdate()) as age``
``FROM
employees
ORDER BY first_name , last_name
LIMIT 10;``
（7）使用DISTINCT获取不重复的唯一值
以下命令将查询employees雇员表里唯一的first_name值。
``SELECT DISTINCT first_name FROM employees ;``
如果以上语句没有关键字DISTINCT，那么返回的记录里first_name会有许多重复值。
（8）聚集函数COUNT、MIN、MAX、AVG、SUM
查询表employees的总记录数时可使用如下命令。
``SELECT COUNT(*) FROM employees;``
查询表employees的最小员工号时可使用如下命令。
``SELECT MIN(emp_no) FROM employees;``
查询表employees的最大员工号时可使用如下命令。
``SELECT MAX(emp_no) FROM employees;``
查询雇员的平均薪水时可使用如下命令。
``SELECT AVG(salary) FROM salaries WHERE to_date='9999-01-01' ;``
salaries表存储了所有员工在不同时期的薪水，where to_date='9999-01-01'可用于筛选出员工目前的薪水。
查询雇员薪水总额时可使用如下命令。
``SELECT SUM(salary) FROM salaries WHERE to_date='9999-01-01' ;``
以下查询将统计'1986-06-26 '和'1985-11-21'分别有多少人入职。
``SELECT SUM(hire_date='1986-06-26') AS sum_1986_06_26,``
``SUM(hire_date='1985-11-21') AS sum_1985_11_21 FROM employees;``
查询语句里的hire_date='1986-06-26'是一个计算表达式，满足条件时为1，不满足条件时为0。sum对表达式返回的值进行求和，这样就实现了统计。当然这种写法不常见，也不推荐使用。
（9）分组统计GROUP BY子句
一般将GROUP BY语句和聚集函数一起使用，从而实现分组统计。
查询employees表，按照first_name分组，并根据first_name出现的次数按降序排序。
``SELECT first_name,COUNT(*) cnt FROM employees GROUP BY first_name ORDER BY cnt DESC;``
也支持如下形式的对多个列同时进行聚集计算。
``SELECT MAX(a),MAX(b),MAX(c) FROM table_name WHERE ... GROUP BY d ;``
我们可以在GROUP BY语句后添加HAVING子句，并对聚集结果进行筛选。
以下命令将查询employees表，按照first_name分组，列出重复次数大于270的first_name，并按照first_name重复的次数按降序排序。
``SELECT first_name,COUNT(*) cnt FROM employees GROUP BY first_name HAVING cnt > 270 ORDER BY cnt DESC ;``
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/8b9f5cbb153110f220dd3cd74550fa8181274c10.png" >
</div>
（10）并集操作（UNION和UNION ALL）
有时我们需要对两个结果集进行合并操作。UNION和UNION ALL都是将两个结果集合并为一个，但UNION比UNION ALL更快。UNION实际上是UNION DISTINCT，在进行表连接后会筛选掉重复的记录，所以在表连接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。而UNION ALL则是不管有没有重复记录，都直接返回合并后的记录。实际应用中，两个需要合并的结果集一般不会产生重复记录，所以建议在能够使用UNION ALL的情况下尽量使用UNION ALL，否则对于很大的结果集，可能会导致查询耗时很长。
UNION ALL的使用示例如下。SELECT * FROM a
UNION ALL
SELECT * FROM b;`
UNION的使用示例如下。
SELECT * FROM a
UNION
SELECT * FROM b;```
（11）NULL值
NULL值的判断一般使用IS NULL或IS NOT NULL，不能使用以上的比较操作符=、<、>，因为NULL是一个特殊的值，表示这个值是未知的或没有定义的。
以下命令将查询employees表中first_name列以字母D开头的员工且last_name值不是NULL的记录。
``SELECT * FROM employees WHERE first_name LIKE 'D%' AND last_name IS NOT NULL;``
上述命令添加的条件“AND last_name IS NOT NULL”仅是为了演示，实际上，由于last_name列上有约束——必须是NOT NULL，所以“AND last_name IS NOT NULL”这个条件总是满足的。
对于GROUP BY子句，两个NULL值可以认为是相等的。
对于“ORDER BY … ASC”，NULL显示在前。对于“ORDER BY … DESC”，NULL值显示在后。
0或空字符串实际上都是有值的，所以在一个NOT NULL的列上插入0或空字符串是允许的。
**4. JOIN（连接）**
MySQL使用JOIN来连接多个表查询数据，主要使用的JOIN算法只有一种，那就是nested-loop join。
nested-loop join算法实现的机制很简单，就是从驱动表中选取数据作为循环基础数据，然后以这些数据作为查询条件到下一个表中进行查询，如此往复。这个实现机制类似于foreach函数的遍历。因此带来的问题就是连接的表越多，函数嵌套的层数就越多，算法复杂度呈指数级增长。
因此，设计查询要尽量减少连接的表的个数。
驱动表是指：在使用多表嵌套连接时，首先，全表扫描该驱动表，然后用驱动表返回的结果集逐行去匹配被驱动的表（可以利用索引），数据库基于成本可能会选择小表作为驱动表，而被驱动表使用索引进行连接。
JOIN语句的含义是把两张（或者多张）表的属性通过它们的值组合在一起，一般会遇到如下3种连接。
等值连接（[INNER] JOIN）
左外连接（LEFT JOIN）
右外连接（RIGHT JOIN）
示例用表见图3-4～图3-6所示。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/9fcfa5f3929c3f0d2cf4f05a165a6dafda2c07e5.png" >
</div>
图3-4 职员表
在部门职员表中，如果是在职员工，那么to_date的值为'9999-01-01'。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/01a2b9ee9cee9c5bfeee5a54e168625f5e7df0a1.png" >
</div>
图3-5 部门表
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/96c163b7ad47f9b195a3b5070c9392780a6749a6.png" >
</div>
图3-6 部门职员表
（1）内连接
内连接（INNER JOIN）是应用程序中普遍应用的“连接”操作，它一般都是默认的连接类型。内连接基于连接谓词将两张表（如A和B）的列组合在一起，从而产生新的结
果表。
内连接可以被进一步分为等值连接、自然连接和交叉连接。较常用的是等值连接。
以下是等值连接的示例。
查询目前在职的所有员工的姓名及其所在的部门时可使用如下语句。
输出结果如图3-7所示。
自然连接（natural join）是等值连接的进一步特例化。两表做自然连接时，两表中名称相同的所有列都将被比较，这是隐式的。自然连接得到的结果表中，两表中名称相同的列只出现一次。一般应该避免使用自然连接，因为我们无法指定连接列，且这种写法隐藏了我们的JOIN关系，如果以后数据模型发生了变化，可能会导致出现非预期的结果。
交叉连接（笛卡儿积） 把表视为行记录的集合，交叉连接即返回这两个集合的笛卡儿积。这其实等价于内连接的连接条件为“永真”，或者连接条件不存在的情况。
示例如下。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/c2174a9bc04954f5de170d291e3de69c4f8e4527.png" >
</div>
图3-7 在职员工的姓名和部门的等值连接
``SELECT * FROM a JOIN b;``
``SELECT * FROM a,b;``
SQL定义了两种不同的语法方式来表示“连接”。一种是“显式连接符号”，显式地使用关键字JOIN，另一种是“隐式连接符号”，它使用所谓的“隐式连接符号”。隐式连接符号把需要连接的表放到SELECT语句的FROM部分，并用逗号隔开。这样就构成了一个“交叉连接”，WHERE语句可能会放置一些过滤谓词（过滤条件）。那些过滤谓词在功能上等价于显式连接符号。
如上所述的例子中，查询目前在职的所有员工的姓名及所在部门时，可以写成如下的形式。
它等价于：
ON表达式是任何可以用于WHERE子句的条件表达式，一般来说，你应该只在ON表达式里指定如何JOIN表，而把筛选结果集的条件放到WHERE子句中。
（2）外连接（OUT JOIN）
并未要求连接的两表的每一条记录在对方的表中都必须有一条匹配的记录。连接表保留所有的记录，甚至这条记录没有匹配的记录也要保留。外连接可依据连接表保留左表、右表或全部表的行而进一步分为左外连接、右外连接和全外连接。全外连接一般没有什么意义，MySQL并不直接支持全外连接，但可以通过左右外连接的并集（UNION）来模拟实现。
1）左外连接（LEFT JOIN、LEFT OUTER JOIN）
左外连接也简称为左连接（LEFT JOIN），若A 和B 两表进行左外连接，那么结果表中将包含“左表”（即表A）的所有记录，即使那些记录在“右表”B中没有符合连接条件的匹配。这就意味着即使ON语句在表B中的匹配项是0条，连接操作也还是会返回一条记录，只不过这条记录中的来自于表B的每一列的值都为NULL。
之前的示例曾演示过插入一些记录（员工号111111、111112、111113、111114）到employees表中，但还没有指定新插入员工记录的部门。现在用如下语句联合查询下雇员表和部门–雇员表。
结果如图3-8所示，可以看到有些员工没有部门，dept_no（部门代码）显示为NULL。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/06efad51c22ff6c10c9e09f7a21676b6b6c29989.png" >
</div>
2）右外连接（RIGHT JOIN、RIGHT OUT JOIN）
右外连接也简称右连接（RIGHT JOIN），它与左外连接完全类似，只不过是连接表的顺序相反而已。如果A表右连接B表，那么“右表”B中的每一行在连接表中至少会出现一次。如果B表的记录在“左表”A中未找到匹配行，则连接表中来源于A表中的列的值将设为NULL，示例如下。SELECT *
FROM A RIGHT JOIN B
ON A.id = B.id```实际上，显式的右外连接很少使用，因为它的可读性不佳，所以总是被改写成左连接。
如果JOIN的层次比较多，则需要留意一下可读性，如果不能确定优先级，那么建议使用括号来明确优先级，以避免犯错误。例如，在以下的例子中，由于逗号的优先级比JOIN表达式低，因此可能会导致我们犯错误。
SELECT t1.id,t2.id,t3.id
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;```
上述代码实际上是：SELECT t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERE t1.id=t2.id;`
但这其实并不是我们的本意，应该写成如下的形式（用括号来提升优先级别）。
SELECT t1.id,t2.id,t3.id
FROM (t1,t2)
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;```
使用JOIN命令操作表的时候，需要留意如果表按条件筛选的记录是不确定的，可能就会导致非预期的结果。下面以图3-9和图3-10所示的数据为例来进行说明。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/9c01f6a12e8b3d1628b85d8fd2e2f760e54f5d82.png" >
</div>
对于如下的查询：
``
SELECT
t1.id as t1_id,
t1.code as t1_code,
t2.id as t2_id,
t2.code as t2_code,
t2.name``
``FROM
t1
JOIN
t2 ON t1.code = t2.code AND t2.code = 'b';``
由于code='b'在两个表中都可以唯一确定一条记录，因此查询会返回合理的结果（如图3-11所示）。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/3f701a83c8be0fd67dd1e45e6dc7e626f19ab937.png" >
</div>
图3-11 返回正确的结果
而对于如下查询：
``SELECT
t1.id as t1_id,
t1.code as t1_code,
t2.id as t2_id,
t2.code as t2_code,
t2.name``
``FROM
t1
JOIN
t2 ON t1.code = t2.code AND t2.code = 'a';``
由于t2表code='a'会返回多个值。最终的查询结果返回了3条记录（如图3-12所示），因此可能不是我们所需要的结果。
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/90bd1589996ad08dddd44aaefabc8154115aff5a.png" >
</div>
**5.子查询**
子查询是指查询语句里的SELECT语句。比如下面这条语句。
``SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);``
在这个示例中，SELECT * FROM t1是外部查询（外部语句），SELECT column1 FROM t2是子查询。
我们可以说，这个子查询是嵌套在外部查询中的，子查询嵌套的层次不宜过多，否则性能可能会很差。
许多人认为子查询的可读性更好，子查询在现实中的应用也很广泛，但MySQL对于子查询的优化不佳，由于子查询一般可以改写成JOIN语句，因此一般建议使用JOIN的方式查询数据。下面来看看示例。
查询薪水大于150 000的员工的姓名。
``SELECT
emp_no, first_name, last_name
FROM
employees``
``WHERE
emp_no IN (SELECT
emp_no
FROM
salaries``
`` WHERE
to_date = '9999-01-01'
AND salary > 150000);``
可将上述语句改写成JOIN的方式，查询语句如下。
``SELECT
employees.emp_no, first_name, last_name``
``FROM
employees
JOIN
salaries ON salaries.emp_no = employees.emp_no``
``WHERE
salaries.to_date = '9999-01-01'
AND salaries.salary > 150000；``
<div style="text-align: center">
<img src="https://yqfile.alicdn.com/6e740967da39e0954883ce8c94fed01e165ffe3e.png" >