MySQL必知必会1-20章读书笔记

mysql备忘

---

目录

使用mysql

• mysql -u<usr> -p -h<host> -p <port> 分别指明用户名，主机名，端口号

• show columns from <table> 对每个字段返回一行，其中的信息分别是字段名，数据类型，是否允许为空，键信息，默认值及其他信息

  describe <table> 与上同

• show status 显示广泛的服务器状态信息
• show create database <database>和show create table <table>，分别用来显示创建特定数据库或表的mysql语句
• show grants，用来显示授予用户（所有用户或特定用户）的安全权限
• show errors和show warnings用来显示服务器错误或警告消息

检索数据

​ 通过select * from somewhere选择所有的列时，列的顺序一般情况下是列在表定义中出现的顺序，但有时也不是，表的模式的变化（如添加或删除列）可能会导致顺序的变化。

一般情况不要使用通配符，除了的确需要，使用通配符会降低检索和应用程序的性能

• select distinct <variable> from <table> 来指示mysql只返回不同的值

注意：

不能部分使用distinct distinct关键字应用于所有列而不仅是前置它的列。如果给出select distinct vend_id, prod_price，除非指定的两个列都相同，否则所有行都将被检索出来。也就是说select distinct a, b, c from table相当于select a, b, c from table group by a, b, c会选出所有a, b, c的不同组合。

• select <variable> from <table> limit <n> 使用limit子句来返回结果的第一行或前几行

​ 限制返回结果不多于5行，此外，还可以指定哟检索的开始行和行数。

行0 检索出来的第一行为行0而不是行1。因此，limit 1, 1将检索出第二行而不是第一行。

在行数不够时 limit中指定要检索的行数为检索的最大行数。如果没有足够的行（例如，给出limit 10, 5，但只有13行），mysql将只返回它能返回的那么多行。

mysql 5的limit语法 limit 3, 4的含义是从行4开始的3行还是从行3开始的4行？如前所述，它的意思是从行3开始的4行，这容易把人搞糊涂。由于这个原因，mysql 5支持limit的另一种替代语法。limit 4 offset 3意为从行3开始取4行，就像limit 3, 4一样

排序检索数据

​ 检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到mysql重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

子句（clause） sql语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有select语句的from子句

• select <variables> from <table> order by <variable> [desc] 为了明确地排序用select语句检索出的数据，可以使用order by子句。 order by 子句取一个或者多个列的名字，据此对数据的数据进行排序。

通过非选择列进行排序 通常，order by子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

​ 按列降序排列：

desc关键字只应用到直接位于其前面的列名。

在多个列上降序排序 如果想在多个列上进行降序排序，必须对每个列指定desc关键字。

区分大小写和排序顺序 在对文本性的数据进行排序时，a与a相同吗？a位于b之前还是位于z之后？这些问题不是理论问题，其答案取决于数据库如何设置。在字典（dictionary）排序顺序中，a被视为与a相同，这是mysql（和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。这里，关键的问题是，如果确实需要改变这种排序顺序，用简单的order by子句做不到。你必须请求数据库管理员的帮助。

​ 这里通过order by 和limit的组合使用来得到价格最高的商品。

order by子句的位置 在给出order by子句时，应该保证它位于from子句之后。如果使用limit，它必须位于order by之后。使用子句的次序不对将产生错误消息。请注意，order by子句必须是select语句中的最后一个子句。否则将产生错误信息。

过滤数据

​ 在select语句中，数据根据where子句中指定的搜索条件进行过滤。where子句在表名（from子句）之后给出

sql过滤与应用过滤 数据也可以在应用层过滤。为此目的，sql的select语句为客户机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。通常，这种实现并不令人满意。因此，对数据库进行了优化，以便快速有效地对数据进行过滤。让客户机应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

where子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	在指定的两个值之间

​ 接着看一个特例：

检查where prod_name=‘fuses’语句，它返回prod_name的值为fuses的一行。mysql在执行匹配时默认不区分大小写，所以fuses与fuses匹配。

何时使用引号 如果仔细观察上述where子句中使用的条件，会看到有的值括在单引号内（如前面使用的'fuses'），而有的值未括起来。单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

​ 为了检查某个范围的值，可使用between操作符。其语法与其他where子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。例如，between操作符可用来检索价格在5美元和10美元之间或日期在指
定的开始日期和结束日期之间的所有产品。

从这个例子中可以看到，在使用between时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用and关键字分隔。between匹配范围中所有的值，包括指定的开始值和结束值。

​ 在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值null。

null 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

​ 通过is null子句过滤含空值的行：

null与不匹配 在通过过滤选择出不具有特定值的行时，你可能希望返回具有null值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有null的行。

​ 也就是说，当我们需要某一行时，尤其是哪一行某个属性为空值，需要特别注意。

数据过滤

​ mysql中的逻辑操作符有and，or, not。可以通过组合使用与或非和where子句来获得更复杂的条件。与绝大多数语言一样，有优先级

优先级顺序	运算符
1	！(按位非)
2	-(负号)，~(按位反)
3	^(按位异或)
4	*, /, %, div, mod
5	-, +
6	<<, >>(移位运算符)
7	&(按位与)
8	|(按位或)
9	=, <=>, >=, >, <=, <, <>, !=, is, like, regexp, in
10	between,case, when, then, else
11	not
12	&&, and
13	||, or, xor
14	:=

参考：[菜鸟教程](https://www.runoob.com/mysql/mysql-operator.html)

备注：<=>用以比较和null是否相等，比如null <=> null返回1，而只当一个操作码为null的时候，返回0。注意，不用<=>时，null表现出传染性。

​ 同样的，类似于绝大多数语言，在条件表达式含糊不清时，使用( )来显示的指明运算优先次序。

• select <variables> from <table> where <variable> in <tuple> order by <variable> 通过使用in (...)操作符来指定条件范围。

​ 为什么要使用in操作符呢？

1. 在使用长的合法选项清单时，in操作符的语法更清楚且更直观。
2. 在使用in时，计算的次序更容易管理（因为使用的操作符更少）。
3. in操作符一般比or操作符清单执行更快。(in操作符是o(log(n))级别的复杂度，而or是o(n)级别的复杂度)
4. in的最大优点是可以包含其他select语句

​ where子句中的not操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

mysql中的not mysql支持使用not 对in 、between 和exists子句取反，这与多数其他dbms允许使用not对各种条件取反有很大的差别。

用通配符进行过滤

• select <variables> from <table> where <variable> like <pattern> 而pattern中的%通配符表示任何字符出现任意次数。

区分大小写 根据mysql的配置方式，搜索可以是区分大小写的。如果区分大小写，'jet%'与jetpack 1000将不匹配。

​ 这个时候可以在like后加binary来表示区分大小写。

注意：

1. 这里的binary不是操作符，而是类型转换运算符，将后面的字符串转换成ascii码，以此来区分大小写。
2. 如果%使用在查询单词的开头，这个sql语句将不走索引，查询效率会降低。

注意尾空格 尾空格可能会干扰通配符匹配。例如，在保存词anvil 时， 如果它后面有一个或多个空格， 则子句where prod_name like '%anvil'将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。

注意null 虽然似乎%通配符可以匹配任何东西，但有一个例外，即null。即使是where prod_name like '%'也不能匹配用值null作为产品名的行。(可以理解成na的传染性)

• select prod_id, prod_name from products where prod_name like '_ton anvil' 这里出现的_通配符用途与%一致，但下划线只匹配单个字符而不是多个字符。

​ mysql的通配符很有用，但是这种有用是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索要花的时间更长。

一些使用通配符的技巧：

1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

用正则表达式进行搜索

​ 正则表达式是用来匹配文本的特殊的串（字符集合）。mysql用where子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤select检索出的数据。

• select <variables> from <table> where <variable> rlike/regexp <pattern> 其中rlike或者regexp后所跟的模式即为正则表达式。

​ 例如select prod_name from products where prod_name regexp '1000' order by prod_name筛选出所有prod_name包含1000的行。

like与regexp 在like和regexp之间有一个重要的差别。请
看以下两条语句：

如果执行上述两条语句，会发现第一条语句不返回数据，而第二条语句返回一行。为什么？正如第8章所述，like匹配整个列。如果被匹配的文本在列值中出现，like将不会找到它，相应的行也不被返回（除非使用通配符）。而regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回。这是一个非常重要的差别。那么，regexp能不能用来匹配整个列值（从而起与like相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可

匹配不区分大小写 mysql中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用binary关键字，如where prod_name regexp binary 'jetpack .000'。

匹配\ 为了匹配反斜杠（\）字符本身，需要使用\\。

\或\? 多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但mysql要求两个反斜杠（mysql自己解释一个，正则表达式库解释另一个）。

匹配字符类

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类（character class）。

字符类

定位符

定位元字符

使regexp起类似like的作用 利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使regexp的作用与like一样。

简单的正则表达式测试 可以在不使用数据库表的情况下用select来测试正则表达式。regexp检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的regexp来测试表达式，并试
验它们。相应的语法如下：

select 'help' regexp '[0-9]'

这个例子显然将返回0（因为文本hello中没有数字）。

创建计算字段

​ 存储在表中的数据往往都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。计算字段是在select语句内创建的。

客户机与服务器的格式 可在sql语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为dbms是设计来快速有效地完成这种处理的。

• select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') from vendors order by vend_name 通过concat()函数连接字符串与变量，通过trim()函数去除字符创两端的空格。

• select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as vend_title from vendors order by vend_name 使用as关键字来指定别名。

  此外，还可以直接在select语句中创建计算字段。如：

  

使用数据处理函数

函数没有sql的可移植性强 能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数sql语句是可移植的，在sql实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的dbms的实现都支持其他实现不支持的函数，而且有时差异还很大。为了代码的可移植，许多sql程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利
用其他方法来实现dbms非常有效地完成的工作。如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写sql代码的含义。

大多数sql实现支持以下类型的函数：

• 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
• 返回dbms正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

一些常用的文本处理函数：

函数	说明
left()	返回串左边的字符
length()	返回串的长度
locate()	找出串的一个子串
lower()	将串转化为小写
ltrim()	去掉串左边的空格
right()	返回串右边的字符
rtrim()	去穿串右边的空格
soundex()	返回串的soundex值[1]
substring()	返回子串的字符
upper()	将串转化为大写
reverse()	翻转字符串

[1] soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然soundex不是sql概念，但mysql（就像多数dbms一样）都提供对soundex的支持

一个使用soundex()的例子：

日期和时间常用的处理函数：

函数名称	函数功能说明
adddate()	添加日期
addtime()	添加时间
convert_tz()	转换不同时区
curdate()	返回当前日期
current_date() 与 current_date	等同于 curdate()
current_time() 与 current_time	等同于 curtime()
current_timestamp() 与 current_timestamp	等同于 now()
curtime()	返回当前时间
date_add()	添加两个日期
date_format()	按指定方式格式化日期
date_sub()	求解两个日期的间隔
date()	提取日期或日期时间表达式中的日期部分
datediff()	求解两个日期的间隔
day()	等同于 dayofmonth()
dayname()	返回星期中某天的名称
dayofmonth()	返回一月中某天的序号（1-31）
dayofweek()	返回参数所定影的一周中某天的索引值
dayofyear()	返回一年中某天的序号（1-366）
extract	提取日期中的相应部分
from_days()	将一个天数序号转变为日期值
from_unixtime()	将日期格式化为 unix 的时间戳
hour()	提取时间
last_day	根据参数，返回月中最后一天
localtime() 和 localtime	等同于 now()
localtimestamp 和 localtimestamp()	等同于 now()
makedate()	基于给定参数年份和所在年中的天数序号，返回一个日期
maketime	maketime()
microsecond()	返回参数所对应的毫秒数
minute()	返回参数对应的分钟数
month()	返回传入日期所对应的月序数
monthname()	返回月的名称
now()	返回当前日期与时间
period_add()	为年-月组合日期添加一个时段
period_diff()	返回两个时段之间的月份差值
quarter()	返回日期参数所对应的季度序号
sec_to_time()	将描述转变成 'hh:mm:ss' 的格式
second()	返回秒序号（0-59）
str_to_date()	将字符串转变为日期
subdate()	三个参数的版本相当于 date_sub()
subtime()	计算时间差值
sysdate()	返回函数执行时的时间
time_format()	提取参数中的时间部分
time_to_sec()	将参数转化为秒数
time()	提取传入表达式的时间部分
timediff()	计算时间差值
timestamp()	单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和
timestampadd()	为日期时间表达式添加一个间隔 interval
timestampdiff()	从日期时间表达式中减去一个间隔 interval
to_days()	返回转换成天数的日期参数
unix_timestamp()	返回一个 unix 时间戳
utc_date()	返回当前的 utc 日期
utc_time()	返回当前的 utc 时间
utc_timestamp()	返回当前的 utc 时间与日期
week()	返回周序号
weekday()	返回某天在星期中的索引值
weekofyear()	返回日期所对应的星期在一年当中的序号（1-53）
year()	返回年份
yearweek()	返回年份及星期序号

具体用法参照：

应该总是使用4位数字的年份 支持2位数字的年份，mysql处理00-69为2000-2069，处理70-99为1970-1999。虽然它们可能是打算要的年份，但使用完整的4位数字年份更可靠，因为
mysql不必做出任何假定。

数值处理函数

函数名称	函数说明
abs()	返回数值表达式的绝对值
acos()	返回数值表达式的反余弦值。如果参数未在[-1, 1]区间内，则返回 null
asin()	返回数值表达式的反正弦值。如果参数未在[-1, 1]区间内，则返回 null
atan()	返回数值表达式的反正切值
atan2()	返回两个参数的反正切值
bit_and()	返回表达式参数中的所有二进制位的按位与运算结果
bit_count()	返回传入的二进制值的字符串形式
bit_or()	返回表达式参数中的所有二进制位的按位或运算结果
ceil()	返回值为不小于传入数值表达式的最小整数值
ceiling()	同ceil()返回值为不小于传入数值表达式的最小整数值
conv()	转换数值表达式的进制
cos()	返回所传入数值表达式（以弧度计）的余弦值
cot()	返回所传入数值表达式的余切值
degrees()	将数值表达式参数从弧度值转变为角度值
exp()	返回以e（自然对数的底数）为底，以所传入的数值表达式为指数的幂
floor()	返回不大于所传入数值表达式的最大整数
format()	将数值表达式参数四舍五入到一定的小数位
greatest()	返回传入参数的最大值
interval()	比较所传入的多个表达式：expr1、expr2、expr3……，如果 expr1 < expr2，则返回0；如果 expr1 < expr3，则返回1……以此类推
least()	返回传入参数中的最小值
log()	返回传入数值表达式的自然对数
log10()	返回传入数值表达式的常用对数（以10为底的对数）
mod()	返回参数相除的余数
oct()	返回传入数值表达式的八进制数值的字符串表现形式。如果传入值为 null，则返回 null
pi()	返回 π 值
pow()	返回两个参数的幂运算结果，其中一个参数为底，另一个参数为它的指数。
power()	返回两个参数的幂运算结果，其中一个参数为底，另一个参数为它的指数。
radians()	将参数由角度值转换成弧度值
round()	将所传入数值表达式四舍五入为整数。也可以用来将参数四舍五入到一定的小数位
sin()	返回参数（以弧度计）的正弦值
sqrt()	返回参数的非负平方根
std()	返回参数的标准方差值
stddev()	返回参数的标准方差值
tan()	返回参数（以弧度计）的正切值
truncate()	将数值参数 expr1 的小数位截取到 expr2 位如果 expr2 为0，则结果没有小数位。

具体用法：

汇总数据

​ 我们经常需要汇总数据而不用把它们实际检索出来，为此mysql提供了专门的函数。使用这些函数，mysql查询可用于检索数据，以便分析和报表生成。

以下是mysql的五个聚集函数：

函数	说明
avg()	返回某列的平均值
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列值之和

标准偏差 mysql还支持一系列的标准偏差聚集函数。详情戳：mysql 5.7 reference manual

因为上面五个函数都是语义清晰的，因此只讲一些注意事项：

avg()函数：

只用于单个列 avg()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个avg()函数。

null值 avg()函数忽略列值为null的行。

count()函数的两种使用方法：

• 使用count(*)对表中行的数目进行计数，不管表列中包含的是空值（null）还是非空值。
• 使用count(column)对特定列中具有值的行进行计数，忽略null值。

max()函数：

对非数值数据使用max() 虽然max()一般用来找出最大的数值或日期值，但mysql允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则max()返回最后一行。

min()函数：

对非数值数据使用min() min()函数与max()函数类似，mysql允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则min()返回最前面的行。

sum()函数：

​ 除了统计某一列的变量的和，还可以用来合计计算值。如：

在多个列上进行计算 如上所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

聚集不同值：

对以上的5个聚集函数，都有如下选项：

• 对所有的行执行计算，指定all参数或不给参数（因为all是默认行为）；
• 只包含不同的值，指定distinct参数。

all为默认 all参数不需要指定，因为它是默认行为。如果不指定distinct，则假定为all。

重要：

注意 如果指定列名，则distinct只能用于count()。distinct不能用于count(*)，因此不允许使用count（distinct），否则会产生错误。类似地，distinct必须使用列名，不能用
于计算或表达式。

​ 理由想想就知道：count(*)的目的是统计表中有多少行，而distinct选项会忽略掉一些null值，是冲突的。同样对于计算或表达式，可能不同的计算式或表达式会产生相同的结果，如果不加考虑的直接去重，会丢失很多数据。

将distinct用于min()和max() 虽然distinct从技术上可用于min()和max()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

重要：

取别名 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的sql更易于理解和使用（以及将来容易排除故障）。

​ 聚集函数用来汇总数据。mysql支持一系列聚集函数，可以用多种方法使用它们以返回所需的结果。这些函数是高效设计的，它们返回结果一般比你在自己的客户机应用程序中计算要快得多。

分组数据

​ mysql通过select语句中的group by子句创建分组。如：

select <variables> from <table> group by <flag> 其中是某一列名。

一些规定：

• group by子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。例如经常使用的：group by year, month, day按天分组
• 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。i.e.: group by year, month, day按天使用聚集函数
• group by子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在group by子句中指定相同的表达式。 不能使用别名。（应该是说group by子句中不允许使用列的别名，但是select语句中是可以指定列的别名的）。
• 除聚集计算语句外，select语句中的每个列都必须在group by子句中给出。
• 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，它们将分为一组。
• group by子句必须出现在where子句之后，order by子句之前。

也就是说，根据我们已经学的mysql表达式，一套比较完整的流程是：

select var1, var2, count(*) as n
from table
where condition(var)
group by var1, var2
order by var1, var2 desc

i.e.：我们必须先筛选，再分组，最后对分组进行排序。

此外：

使用rollup 使用with rollup关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;

​ 可以看到with rollup选项实际上做的是一个超分组的操作，即对分组的数据再次进行汇总。最后一行的null实际上代表为空，效果是这样滴：

​ 那问题就来了，如果本来的分组值里面有个null怎么办呢？

​ 注意，同所有数据分析的工具一样，如果不加with rollup选项，na值会附加在末尾，而一旦加了with rollup选项，空值会自动调整到第一行。

​ 除了能用group by分组数据外，mysql还允许过滤分组，规定包括哪些分组，排除哪些分组。我们已经看到了where子句的作用。

​ 但是，在这个需求下where不能完成任务，因为where过滤指定的是行而不是分组。事实上，where没有分组的概念。那么，不使用where使用什么呢？mysql为此目的提供了另外的子句，那就是having子句。having非常类似于where。事实上，目前为止所学过的所有类型的where子句都可以用having来替代。唯一的差别是where过滤行，而having过滤分组。

having支持所有where操作符 之前提到过的所有可以在where子句中使用过的条件都可以在having子句中使用。

having和where的差别 这里有另一种理解方法，where在数据分组前进行过滤，having在数据分组后进行过滤。这是一个重要的区别，where排除的行不包括在分组中。这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。

分组与排序

order by与group by

​ 我们经常发现用group by分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是sql规范所要求的。此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的order by子句，即使其效果等同于group by子句也是如此。

不要忘记order by 一般在使用group by子句时，应该也给出order by子句。这是保证数据正确排序的唯一方法。千万不要仅依赖group by排序数据。

select子句顺序

select var1, var2...
from <table>
where <conditions>
group by var
having <conditions>
order by var [desc]
limit [start,] num

说明：

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅从表中选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

使用子查询

​ 子查询，即嵌套在其他查询中的查询。

例如：现在需要列出订购物品tnt2的所有客户：

格式化sql 包含子查询的select语句难以阅读和调试，特别是它们较为复杂时更是如此。如上所示把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。

​ 尤其要注意这个格式化的过程，因为外部查询实际上需要的是内部查询返回一个,分隔的tuple。

​ 可见，在where子句中使用子查询能够编写出功能很强并且很灵活的sql语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

​ 虽然子查询一般与in操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

子查询和性能 这里给出的代码有效并获得所需的结果。但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。

作为计算字段使用子查询

​ 计算字段，即跟着select语句的一些字段，是使用子查询的另外一种方式。

​ 例：假如需要显示customers表中每个客户的订单总数，为此，我们必须执行两个步骤：

• 从customers表中检索客户列表。
• 对于检索出的每个客户，统计其在orders表中的订单数目。

​ 有：

select cust_name,
	   cust_state,
	   (select count(*)
        from orders
        where orders.cust_id = customers.cust_id) as orders
 from customers
 order by cust_name;

​ 注意这里被嵌套的查询where子句需要使用完全限定的列名。

相关子查询（correlated subquery） 涉及外部查询的子查询。

此外，如上给出的sql语句并不是最高效的办法。后续会进行改进。

逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与mysql处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性。

​ 如上提供了sql的调试办法。

联结表

​ 将不同表中的数据合并就是联结。

维护引用完整性 重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由mysql根据需要建立，它存在于查询的执行当中。在使用关系表时，仅在关系列中插入合法的数据非常重要。回到这里的例子，如果在products表中插入拥有非法供应商id（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。为防止这种情况发生，可指示mysql只允许在products表的供应商id列中出现合法值（即出现在vendors表中的供应商）。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。

内连接(自然连接)的两种使用方法：

​ 如果我们要查询供应商所有的商品及其价格，我们可以：

select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

​ 还可以：

select vend_name, prod_name, prod_price 
from vendors inner join products
on vendors.vend_id = products.vend_id
order by vend_name, prod_name;

​ 其中前者是通常的where子句写法，后者明确的表示这是个内连接。通过on子句传递条件。

特别要注意是：where子句和on子句传递的条件必须不能忘记，否则直接返回一个笛卡尔积。

不要忘了where子句 应该保证所有联结都有where子句，否则mysql将返回比想要的数据多得多的数据。同理，应该保证where子句的正确性。不正确的过滤条件将导致mysql返回
不正确的数据。

叉联结 有时我们会听到返回称为叉联结（cross join）的笛卡儿积(cartesian product)的联结类型。

反过来看一下子查询中查询订购产品tnt2的客户那个例子：

# 子查询
select cust_name, cust_contact
from customers
where cust_id in (select cust_id
				  from orders
				  where order_num in (select order_num
				  					  from orderitems
				  					  where prod_id = 'tnt2'))
				  					  
# 内连接
select cust_name, cust_contact
from customers, orders, orderitems
where customers, cust_id = orders.cust_id
	and orderitems.order_num = orders.order_num
	and prod_id = 'tnt2';

多做实验 正如所见，为执行任一给定的sql操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

创建高级联结

​ mysql不仅支持给计算字段取别名，同时也支持给表取别名。这样做主要有以下两个理由：

• 缩短sql语句；
• 允许单条select语句中多次使用相同的表。

​ 很容易想到的一点是：表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机。

不同类型的连接：

​ 之前谈到过内连接，这里介绍其他的三种连接: 自连接，自然连接，外连接。其中外连接包括左外连接，右外连接，但是mysql并没有提供全连接的语法支持。不过是可以通过某些方法模拟出来全连接的。

自连接：

自连接通过给同一张表取别名的方式，多次连接同一张表。并且进行条件运算。如：

注意： mysql中进行连接时，如果要选择的计算字段语义不明，必须给定表名限定的字段名。如上所示。同时，既可以使用from <inner/left/right> join on...也可以使用from ... where ...。

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然连接：

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（笛卡尔积）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（select *），对所有其他表的列使用明确的子集来完成的。下面举一个例子：

select c.*, o.order_num, o.order_date,
						oi.prod_id, oi.quantity, oi.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
	and oi.order_num = o.order_num
	and prod_id = 'fb';

(其实我有点没看太懂）

外部连接：

select customers.cust_id, orders.order_num
from customers left join orders
	on customers.cust_id = orders.cust_id;

mysql左外连接，右外连接的语法支持分别是 left join和right join。

没有=操作符 mysql不支持简化字符=和=*的使用，这两种操作符在其他dbms中是很流行的。

外部联结的类型 存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒from或where子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定

此外，聚集函数也可以在各种连接中使用。

使用连接和链接条件：

• 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

组合查询

组合查询/复合查询：或者说，不同查询结果的并（union）。

两种情况：

• 在单个查询中从不同的表返回类似结构的数据；
• 对单个表执行多个查询，按单个查询返回数据。

组合查询和多个where条件 多数情况下，组合相同表的两个查询完成的工作与具有多where子句条件的单条查询完成的工作相同。换句话说，任何具有多个where子句的select语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

​ 可用union操作符来组合数条sql查询。利用union，可给出多条select语句，将它们的结果组合成单个结果集。例如：筛选价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。虽然可以很简单的用where子句完成，但这里也可以选用union操作符：

​

​ 使用union操作符的一些规则：

• union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔（因此，如果组合4条select语句，将要使用3个union关键字）。
• union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是dbms可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

此外，union的默认行为会去重，如果想要覆盖掉这种默认行为，可以：

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union all
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002)

使用union all，mysql不取消重复的行。

union与where union几乎总是完成与多个where条件相同的工作。union all为union的一种形式，它完成where子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用union all而不是where。

最后，如果想要对union的结果排序，只能在最后一行加一行order by对整个表进行排序。不能以一种方式对一部分排序，以另一种方式对另一部分排序。并且，前面提到过的，可以对不同的表应用组合查询，而不仅限于例子中的单表。

之前在创建高级连接的时候提到过，mysql并没有语法层面的对full join的支持，学完union后，我们看看怎样实现一个full join:

可能第一次会是这样：

select * from t1
left join t2 on t1.id = t2.id
union
select * from t1
right join t2 on t1.id = t2.id

然后这就没问题了吗？不对，一个比较通用的full join是不会自动去重的，所以第二次你可能会写出这样的code：

(the query above works for special cases where a full outer join operation would not produce any duplicate rows. the query above depends on the union set operator to remove duplicate rows introduced by the query pattern. we can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a union all set operator to combine the two sets. in the more general case, where a full outer join would return duplicate rows, we can do this:)

select * from t1
left join t2 on t1.id = t2.id
union all
select * from t1
right join t2 on t1.id = t2.id
where t1.id is null

但是这样就完了吗？不对，可以看到最后一行做了判断t1.id is null，对问题来了，怎么保证t1.id是允许为空的，上面那个查询一定会是语法正确的吗？

如对本文有疑问， 点击进行留言回复！！