<MySQL必知必会> 笔记
MySQL5.7安装 #仅供参考
脚本下载 #下载MySQL(and Mariadb) SQL scripts
下载下来脚本后进行解压并导入进数据库，先导入create.txt，再导入populate.txt,库自定义（这里我的库名为opop）
导入成功后进行查看
这里我从第4章<检索数据>开始做笔记，前3章都是了解知识，so...
** 只查看一列数据
格式：SELECT col_name FROM tab_name;
示例： SELECT prod_name FROM Products;
解析：
SELECT语句从 products 表中检索一个名 为 prod_name 的列。
所需的列名在 SELECT 关键字之后给出, FROM关键字指出从其中检索数据的表名
SQL语句不区分大小写,select PROD_name from Products;和上条语句一样效果(表名一定要一样)
4.3 检索多个列
SELECT 关键字后给出多个列名,列名之间必须以逗号分隔(最后一个列名后不加)即可
解析：SQL语句一般返回原始的、无格式的数据。数据的格式化是一个表示问题,而不是一个检索问题
4.4 检索所有列
在实际列名的位置使用星号( * )通配符即可
除非你确实需要表中的每个列,否则最好别使用 * 通配符。虽然使用通配符可能会使你自己省事,不用明确列出所需列,但检索不需要的列通常会降低检索和应用程序的性能
使用通配符有一个大优点。由于不明确指定列名(因为星号检索每个列),所以能检索出名字未知的列。
4.5 去重
解析：
SELECT DISTINCT vend_id 告诉MySQL只返回不同(唯一)的vend_id 行,因此只返回3行，DISTINCT 关键字必须直接放在列名的前面
注：DISTINCT 关键字应用于所有列而不仅是前置它的列。如下图所示，除非指定的两个列都不同,否则所有行都将被检索出来。
4.6 限制结果
SELECT 语句返回所有匹配的行,它们可能是指定表中的每个行。为了返回第一行或前几行,可使用 LIMIT 子句
解析：limit指定返回几行。
为得出下一个3行,可指定要检索的开始行和行数
解析：
LIMIT 3,3指示MySQL返回从第3行开始后的3行数据。第一个数为开始位置,第二个数为要检索的行数
带一个值的 LIMIT 总是从第一行开始,给出的数为返回的行数。
带两个值的 LIMIT 可以指定从行号为第一个值的位置开始
行数不够时MySQL将只返回它能返回的那么多行
4.7 使用完全限定的表名
解析：这里指定了一个完全限定的列名
表名也可以是完全限定的,如下所示:
格式：select 表名.列名 from 库名.表名;
mysql> select Products.prod_name from opop.Products;
第5章、排序检索数据
5.1 排序数据
如果不排序,数据一般将以它在底层表中出现的顺序显示，可以是数据最初添加到表中的顺序，但是,如果数据后来进行过更新或删除,则此顺序将会受到MySQL重用回收存储空间的影响
关系数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义
SQL语句由子句构成,有些子句是必需的,有些是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有 SELECT 语句的 FROM 子句
为了明确地排序用 SELECT 语句检索出的数据,可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字,据此对输出进行排序
注1：这里排序的规则为从返回的列中的第一位开始，数字优先，如前两行开头都为1，而后排序第2位，以此类推，前2行排完后第三行的第1位为8显然比1大。
数字排完后则从字母a开始，顺序为abcdefghi........
注2：通常, ORDER BY 子句中使用的列将是为显示所选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的
如上图所示，我要查询的列名为prod_name,但是筛选出来的结果会有prod_id、prod_price等列，此时在order by后面也可以使用prod_id进行排序
5.2 按多个列排序
为了按多个列排序,只要指定列名,列名之间用逗号分开即可
下面的代码检索3个列,并按其中两个列对结果进行排序——首先按价格,然后再按名称排序。
如果写成 order by prod_name,prod_price; 则首先按名称，然后再按价格排序
5.3 指定排序方向(升序、降序)
默认为升序
降序：desc
升序：asc
降序示例：
DESC 关键字只应用到直接位于其前面的列名,如下所示，只对prod_price列指定DESC,对prod_name列不指定 。
因 此,prod_price 列以降序排序,而 prod_name 列(在每个价格内)仍然按标准的升序排序
如果想在多个列上进行降序排序,必须对每个列指定 DESC 关键字
升序(ASC)没有多大用处,因为升序是默认的,不指定升降序默认则为升序
使用 ORDER BY 和 LIMIT 的组合,能够找出一个列中最高或最低的值
下图为找出最昂贵的物品，如需找出最便宜的物品则将下图中desc去掉即可，因为默认为升序
注：order by 位于 FROM 子句之后。LIMIT 必须位于 ORDER BY之后
第6章、过 滤 数 据
6.1 使用 WHERE 子句
在 SELECT 语句中,数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名( FROM 子句)之后给出
注1：因为只输出了2列，原因为在上图中的SQL语句中只指定了2列(select后面)
注2：ORDER BY 必须在 WHERE 之后，否则将报错
6.2 WHERE 子句操作符
| 操作符 | 说明 |
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between | 在指定的两个值之间 |
注：如使用除=号之外的操作符，都将输出一个范围，而非精确查找
示例：
select prod_name,prod_price from Products where prod_price<10;
select prod_name,prod_price from Products where prod_price<=10;
select prod_name,prod_price from Products where prod_price<>5.99;
select prod_name,prod_price from Products where prod_price != 5.99;
select prod_name,prod_price from Products where prod_price between 3 and 5;
空值检查
检查具有 NULL 值的列，null表示无值(no value),它与字段包含 0 、空字符串或仅仅包含空格不同
select prod_name,prod_price from Products where prod_price is null;
第7章、数 据 过 滤
7.1 组合 WHERE 子句
MySQL允许给出多个 WHERE 子句。这些子句可以两种方式使用:以 AND 子句的方式或 OR 子句的方式使用
and 和 or 称为逻辑操作符，用来联结或改变 WHERE 子句中的子句的关键字,and两端条件必须都满足
or两端条件有一个满足即可
圆括号具有较 AND 或 OR 操作符高的计算次序，也就是说如果有元括号和and或or时先算括号内的表达式，而后算and或or表达式，如下所示：
7.2 IN 操作符
圆括号在 WHERE 子句中还有另外一种用法。 IN 操作符用来指定条件范围,范围中的每个条件都可以进行匹配。 IN 取合法值的由逗号分隔的清单,全都括在圆括号中
注：上图和 select prod_name,prod_price from Products where prod_price = 8.99 or prod_price = 9.49 order by prod_name; 相同
7.3 NOT 操作符
WHERE 子句中的 NOT 操作符有且只有一个功能,那就是否定它之后所跟的任何条件，也为WHERE 子句中用来否定后跟条件的关键字
第8章、用通配符进行过滤
8.1 LIKE 操作符
通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern)由字面值、通配符或两者组合构成的搜索条件。
为在搜索子句中使用通配符,必须使用 LIKE 操作符。 LIKE 指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
8.1.1 百分号( % )通配符
% 表示任何字符出现任意次数
注：bea%表示开头是bea后面无论是什么字符，有多少个字符都找出来，%bea%表示子药prod_name中有bea这三个字符的都找出来
通配符也可以出现在搜索模式的中间,如下语句找出以b开头中间任意字符且以y结尾的所有名字
select prod_id,prod_name from Products where prod_name like 'b%y';
即使%通佩符可以匹配到所有但是要匹配的字符后面有空格时将匹配不到，其次也匹配不到null的行
8.1.2 下划线( _ )通配符
下划线只匹配单个字符而不是多个字符
第9章、用正则表达式进行搜索
9.1 正则表达式介绍
正则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较
9.2.1 基本字符匹配
在 LIKE 和 REGEXP 之间有一个重要的差别
LIKE 匹配整个列。如果被匹配的文本在列值中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用通配符)。
而 REGEXP 在列值内进行匹配,如果被匹配的文本在列值中出现, REGEXP 将会找到它,相应的行将被返回。
这是一个非常重要的差别,如果想使用regexp进行匹配整个列则使用^ 和 $ 定位符即可
正则表达式中不区分大小写
9.2.2 进行OR匹配
为搜索两个串之一(或者为这个串,或者为另一个串),使用 | ,可使用多个 |
9.2.3 匹配几个字符之一
如果只想匹配特定的字符可通过指定一组用 [ ] 括起来的字符来完成
解析：使用了正则表达式 [128] inch 。 [128] 定义一组字符,它的意思是匹配 1 或 2 或 8
和 select prod_name from Products where prod_name regexp '1|2|8 inch' order by prod_name; 同等意思
^[128]表示开头是1或2或8的字符串，[^128]表示取开头为1或2或8的字符串之外的值
9.2.4 匹配范围
select prod_name from Products where prod_name regexp '[0-9] inch' order by prod_name;
select prod_name from Products where prod_name regexp '[a-z] inch' order by prod_name;
9.2.5 匹配特殊字符
如何找出包含 . 字符的值
为了匹配特殊字符,必须用 \\ 为前导。 \\- 表示查找 - , \\. 表示查找
示例：
select vend_name from Vendors where vend_name regexp '\\.' order by vend_name;
注：这种处理就是所谓的转义(escaping),正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括 . 、 | 、 [] 以及迄今为止使用过的其他特殊字符
\\ 也用来引用元字符(具有特殊含义的字符)
| 元字符 | 说明 |
| \\f | 换页 |
| \\n | 换行 |
| \\r | 回车 |
| \\t | 制表 |
| \\v | 纵向制表 |
为了匹配反斜杠( \ )字符本身,需要使用 \\\，多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)
9.2.6 匹配字符类
| 类 | 说明 |
| [:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
| [:alpha:] | 任意字符(同[a-zA-Z]) |
| [:blank:] | 空格和制表(同[\\t]) |
| [:cntrl:] | ASCII控制字符(ASCII 0到31和127) |
| [:digit:] | 任意数字(同[0-9]) |
| [:graph:] | 与[:print:]相同,但不包括空格 |
| [:lower:] | 任意小写字母(同[a-z]) |
| [:print:] | 任意可打印字符 |
| [:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v]) |
| [:upper:] | 任意大写字母(同[A-Z]) |
| [:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) |
9.2.7 匹配多个实例
| 元 字 符 | 说明 |
| * | 0个或多个匹配 |
| + | 1个或多个匹配(等于{1,}) |
| ？ | 0个或1个匹配(等于{0,1}) |
| {n} | 指定数目的匹配 |
| {n,} | 不少于指定数目的匹配 |
| {n,m} | 匹配数目的范围(m不超过255) |
9.2.7 匹配多个实例
select prod_name from Products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;
解析：只匹配prod_name中有括号、以数字开头且后面跟的sticks或sticks后有一个字符
9.2.7 匹配多个实例
select prod_name from Products where prod_name regexp '[[:digit:]]{4}' order by prod_name;
解析：[:digit:] 匹配任意数字,因而它为数字的一个集合。 {4} 确切地要求它前面的字符(任意数字)出现4次,所以[[:digit:]]{4} 匹配连在一起的任意4位数字,有点疑惑
9.2.8 定位符
目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本,需要使用定位
| 元字符 | 说明 |
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
9.2.8 定位符
目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本,需要使用定位符
解析：匹配prod_name列中开头是数字且后面是任意字符（包括回车和新行）的行
LIKE 和 REGEXP的不同在于, LIKE 匹配整个串而 REGEXP 匹配子串。利用定位符,通过用 ^ 开始每个表达式,用 $ 结束每个表达式,可以使REGEXP 的作用与 LIKE 一样。
第 10 章、创建计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式，需要直接从数据库中检索出转换、计算或格式化过的数据;
而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。这就是计算字段发挥作用的所在了，计算字段是运行时在 SELECT 语句
内创建的。
只有数据库知道 SELECT 语句中哪些列是实际的表列,哪些列是计算字段。从客户机(如应用程序)的角度来看,计算字段的数据是以与其他列的数据相同的方式返回的。
10.2 拼接字段
把两个列拼接起来。在MySQL的 SELECT 语句中,可使用Concat() 函数来拼接两个列。
select concat(vend_name, '(', vend_country, ')') from Vendors order by vend_name;
也可以起别名
select concat(vend_name, '(',vend_country,')') as opop from Vendors order by vend_name;
解析：
Concat() 拼接串,即把多个串连接起来形成一个较长的串。
Concat() 需要一个或多个指定的串,各个串之间用逗号分隔。
上图也可以通过删除数据右侧多余的空格来整理数据
解析：
RTrim() 函数去掉值右边的所有空格。通过使用 RTrim() ,各个列都进行了整理。
LTrim() 函数去掉值左边的所有空格
Trim() 函数去掉值左右两边的空格
10.3 执行算术计算
如下汇总物品的价格(单价乘以订购数量)并使用了别名
SELECT 提供了测试和试验函数与计算的一个很好的办法。
虽然 SELECT 通常用来从表中检索数据,但可以省略 FROM 子句以便简单地访问和处理表达式。
例如, SELECT 3*2; 将返回 6 ,
SELECT Trim('abc'); 将返回 abc ,
SELECT Now() 利用 Now() 函数返回当前日期和时间。(相当于oracle中的select sysdate from dual)
通过这些例子可以明白如何根据需要使用 SELECT 进行试验。
第 11 章、使用数据处理函数
与其他大多数计算机语言一样, SQL支持利用函数来处理数据。
函数一般是在数据上执行的,它给数据的转换和处理提供了方便。
11.2、使用函数
大多数SQL实现支持以下类型的函数。
1、用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数。
2、用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
3、用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数。
4、返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数。
11.2.1 文本处理函数
RTrim()函数去除列值右边的空格
Upper() 函数将文本转换为大写
常用的文本处理函数
| 函数 | 说明 |
| Left() | 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转换为小写 |
| LTrim() | 去掉串左边的空格 |
| Right() | 返回串右边的字符 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为大写 |
注：SOUNDEX 是一个将任何文
本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。
虽然SOUNDEX 不是SQL概念,但MySQL(就像多数DBMS一样)都提供对SOUNDEX 的支持
示例：
使用 Soundex() 函数进行搜索,它匹配所有发音类似于 kim Hard的联系名:
解析：WHERE 子句使用 Soundex() 函数来转换 cust_contact 列 值 和 搜 索 串 为 它 们 的 SOUNDEX 值 。
因 为 Kim Hard 和Kim Howard 发音相似,所以它们的 SOUNDEX 值匹配,因此 WHERE 子句正确地过滤出了所需的数据。
11.2.2 日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。
| 函数 | 说明 |
| AddDate() | 增加一个日期(天、周等) |
| AddTime() | 增加一个时间(时、分等) |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format() | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期,返回对应的星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份部分 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
MySQL使用日期格式不管是插入或更新表值还是用 WHERE 子句进行过滤,日期必须为格式 yyyy-mm-dd
因此,2005年9月1日,给出为2005-09-01。
虽然其他的日期格式可能也行,但这是首选的日期格式,因为它排除了多义性(如,04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或......)。
select cust_id, order_num from Orders where order_date='2012-01-12';
select cust_id, order_num from Orders where date(order_date)='2012-01-12';
注：区别是第一句没有将order_date列中的日期格式化，如果order_date列不三yyyy-mm-dd这种格式，那就会报错或筛选为空
如果你想要的仅是日期,则使用 date() 是一个良好的习惯,即使你知道相应的列只包含日期也是如此。这样,如果由于某种原因表中以后有日期和时间值,你的SQL代码也不用改变。
当然,也存在一个 time()函数,在你只想要时间时应该使用它。
检索出2005年9月的所有订单
select cust_id, order_num from Orders where date(order_date) between '2012-01-01' and '2012-1-31';
或者
select cust_id,order_num from Orders where year(order_date)=2012 and month(order_date)=1;
注：这种不需要记住每个月中有多少天或不需要操心闰年2月
解析：Year() 是一个从日期(或日期时间)中返回年份的函数。类似 Month() 从日期中返回月份。
因此, WHERE Year(order_date)= 2012 AND Month(order_date) = 9 检索出 order_date 为2012年9月的所有行。
11.2.3 数值处理函数
数值处理函数仅处理数值数据。
这些函数一般主要用于代数、三角或几何运算,因此没有串或日期—时间处理函数的使用那么频繁。
在主要DBMS的函数中,数值函数是最一致最统一的函数
常用数值处理函数:
| 函数 | 说明 |
| abs() | 返回一个数的绝对值 |
| cos() | 返回一个角度的余弦 |
| exp() | 返回一个数的指数值 |
| mod() | 返回除操作的余数 |
| pi() | 返回圆周率 |
| rand() | 返回一个随机数 |
| sin() | 返回一个角度的正弦 |
| sqrt() | 返回一个数的平方根 |
| tan() | 返回一个角度的正切 |
第 12 章、汇 总 数 据
我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数。
使用这些函数,MySQL查询可用于检索数据,以便分析和报表生成，MySQL给出了5个聚集函数
| 函数 | 说明 |
| avg() | 返回某列的平均值 |
| count() | 返回某列的行数 |
| max() | 返回某列的最大值 |
| min() | 返回某列的最小值 |
| sum() | 返回某列值之和 |
12.1.1、AVG()函数
AVG() 通过对表中行数计数并计算特定列值之和,求得该列的平均值。
AVG() 可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
avg() 也可以用来确定特定列或行的平均值
AVG() 只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。
为了获得多个列的平均值,必须使用多个 AVG() 函数。
AVG() 函数忽略列值为 NULL 的行。
12.1.2、COUNT()函数
COUNT() 函数进行计数。可利用 COUNT() 确定表中行的数目或符合特定条件的行的数目。
COUNT() 函数有两种使用方式。
1、使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空值( NULL )还是非空值。
2、使用 COUNT(column) 对特定列中具有值的行进行计数,忽略 NULL 值。
12.1.3、MAX()函数
max() 返回指定列中的最大值。 max() 要求指定列名且忽略列值为 NULL 的行。
12.1.4、MIN()函数
min()返回指定列中的最小值。min() 要求指定列名且忽略列值为 NULL 的行。
12.1.5、SUM()函数
sum() 用来返回指定列值的和。利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算且该函数忽略列值为 NULL 的行
12.2、DISTINCT 函数。
聚集函数 DISTINCT
以上5个聚集函数都可以如下使用:
1、对所有的行执行计算,指定 ALL 参数或不给参数(因为 ALL 是默认行为);
2、只包含不同的值,指定 DISTINCT 参数。
ALL 参数不需要指定,因为它是默认行为。如果不指定 DISTINCT ,则假定为 ALL 。
12.3、组合聚集函数
实际上 SELECT 语句可根据需要包含多个聚集函数
第 13 章、分 组 数 据
13.2 创建分组
分组是在 SELECT 语句的 GROUP BY 子句中建立的。理解分组的最好办法是看一个例子:
解析：上面的 SELECT 语句指定了两个列, vend_id 包含产品供应商的ID,num_prods 为计算字段(用 COUNT(*) 函数建立)。
GROUP BY 子句指示MySQL按 vend_id 排序并分组数据。这导致对每个 vend_id 而不是整个表计算 num_prods 一次。
从输出中可以看到,供应商 BRS01 有 3 个产品,供应商DLL01有 4个产品,供应商 FNG01 有 2 个产品。
因为使用了 GROUP BY ,就不必指定要计算和估值的每个组了。系统会自动完成。 GROUP BY 子句指示MySQL分组数据,然后对每个组而不是整个结果集进行聚集。
在具体使用 GROUP BY 子句前,需要知道一些重要的规定。
1、GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
2、如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
3、GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。
4、除聚集计算语句外, SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
5、如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL 值,它们将分为一组。
6、GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前。
使用 WITH ROLLUP 关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值,如下图所示:
13.3 过滤分组
having和where唯一的差别是 WHERE 过滤行,而 HAVING 过滤分组
注：最后一的 HAVING 子句,它过滤 COUNT(*) >=2 (两个以上的订单)的那些分组，这里 WHERE 子句不起作用,因为过滤是基于分组聚集值而不是特定行值的
13.4、分组和排序
GROUP BY 和 ORDER BY 的不同
| order by | group by |
| 排序产生的输出 | 分组行。但输出可能不是分组的顺序 |
|
任意列都可以使用 (甚至非选择的列也可以使用) |
只可能使用选择列或表达式列,而且必须使用每个选择 列表达式 |
| 不一定需要 | 如果与聚集函数一起使用列(或表达式),则必须使用 |
一般在使用 GROUP BY 子句时,应该也给出 ORDER BY 子句。
这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。
为说明 GROUP BY 和 ORDER BY 的使用方法,还得通过例子还说明：
为按总计订单价格排序输出,需要添加 ORDER BY 子句
上例中, GROUP BY 子句用来按订单号( order_num 列)分组数据,以便 SUM(*) 函数能够返回总计订单价格。
HAVING 子句过滤数据,使得只返回总计订单价格大于等于 50 的订单。最后,用 ORDERBY 子句排序输出。
13.5 SELECT子句顺序
| 子句 | 说明 | 是否必须使用 |
| SELECT | 要查看的列或表达式 | 是 |
| FROM | 从哪个表检索数据 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
第 14 章、使用子查询
SQL允许创建子查询(subquery),即嵌套在其他查询中的查询。一条 SELECT 语句返回的结果用于另一条 SELECT 语句的 WHERE 子句
14.2 利用子查询进行过滤
现在把上一个查询(返回订单号的那一个)变为子查询组合两个查询：
解析:
在 SELECT 语句中,子查询总是从内向外处理。在处理上面的SELECT 语句时,MySQL实际上执行了两个操作。
首先,它执行下面的查询:
select order_num from OrderItems where prod_id='BR01'
此查询返回两个订单号: 20005 和 20007 。然后,这两个值以 IN 操作符要求的逗号分隔的格式传递给外部查询的 WHERE 子句。
外部查询变成:
select cust_id from Orders where order_num in (20005,20007);
可以看到,输出是正确的并且与前面硬编码 WHERE 子句所返回的值相同。
现在得到了订购物品 BR01 的所有客户的ID。下一步是检索这些客户ID的客户信息。检索两列的SQL语句为:
包含子查询的 SELECT 语句难以阅读和调试,特别是它们较为复杂时更是如此。
所以有时候把子查询分解为多行并且适当地进行缩进,可以极大地简化子查询的使用
可以把上图中的 WHERE 子句转换为子查询而不是硬编码这些客户ID
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='BNBG03'));
解析：
MySQL实际上必须执行3条 SELECT语句。
最里边的子查询返回订单号列表,此列表用于其外面的子查询的 WHERE 子句。
外面的子查询返回客户ID列表,此客户ID列表用于最外层查询的 WHERE 子句。
最外层查询确实返回所需的数据
14.3 作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。假如需要显示 customers表中每个客户的订单总数。订单与相应的客户ID存储在 orders 表中
操作步骤：
(1) 从 customers 表中检索客户列表。
(2) 对于检索出的每个客户,统计其在 orders 表中的订单数目
使用 SELECT COUNT ( *) 对表中的行进行计数,并且通过提供一条 WHERE 子句来过滤某个特定的客户ID,可仅对该客户的订单进行计数
为了对每个客户执行 COUNT(*) 计算,应该将 COUNT(*) 作为一个子查询
解析：
这条SELECT语句对customers表中每个客户返回3列 :
cust_name 、 cust_state 和 orders 。
orders 是一个计算字段,它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次
子查询中的 WHERE 子句与前面使用的 WHERE 子句稍有不同,因为它使用了完全限定列名
语句告诉SQL比较orders 表中的 cust_id 与当前正从 customers 表中检索的 cust_id
第 15 章、联 结 表（重点！！！）
SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。
联结是利用SQL的 SELECT 能执行的最重要的操作,很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。
在能够有效地使用联结前,必须了解关系表以及关系数据库设计的一些基础知识。
下面的介绍并不是这个内容的全部知识,但作为入门已经足够了
15.1.1 关系表
理解关系表的最好方法是来看一个现实世界中的例子。
假如有一个包含产品目录的数据库表,其中每种类别的物品占一行。
对于每种物品要存储的信息包括产品描述和价格,以及生产该产品的供应商信息。
现在,假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如,供应商名、地址、联系方法等)呢?将这些数据与产品信息分开存储的理由如下。
1、因为同一供应商生产的每个产品的供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空间。
2、如果供应商信息改变(例如,供应商搬家或电话号码变动),只需改动一次即可。
3、如果有重复数据(即每种产品都存储供应商信息),很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。
关系表的设计就是要保证把信息分解成多个表,一类数据一个表。各表通过某些常用的值(即关系设计中的关系(relational))互相关联。
在这个例子中,可建立两个表,一个存储供应商信息,另一个存储产品信息。
vendors 表包含所有供应商信息,每个供应商占一行,每个供应商具有唯一的标识。此标识称为主键(primary key),可以是供应商ID或任何其他唯一值。
products 表只存储产品信息,它除了存储供应商ID( vendors 表的主键)外不存储其他供应商信息。
vendors 表的主键又叫作 products 的外键,它将 vendors 表与 products 表关联,利用供应商ID能从 vendors 表中找出相应供应商的详细信息。
外键是某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
这样做的好处如下:
1、供应商信息不重复,从而不浪费时间和空间;
2、如果供应商信息变动,可以只更新 vendors 表中的单个记录,相关表中的数据不用改动;
3、由于数据无重复,显然数据是一致的,这使得处理数据更简单。
总之,关系数据可以有效地存储和方便地处理。因此,关系数据库的可伸缩性远比非关系数据库要好
包含子查询的 SELECT 语句难以阅读和调试,特别是它们较为复杂时更是如此。
所以有时候把子查询分解为多行并且适当地进行缩进,可以极大地简化子查询的使用
15.1.2 为什么要使用联结
分解数据为多个表能更有效地存储,更方便地处理,并且具有更大的可伸缩性。但这些好处是有代价的。
如果数据存储在多个表中,怎样用单条 SELECT 语句检索出数据?
答案是使用联结。简单地说,联结是一种机制,用来在一条 SELECT语句中关联表,因此称之为联结。
使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
一定要理解联结不是物理实体，它在实际的数据库表中不存在。联结由MySQL根据需要建立,它存在于查询的执行当中。
15.2 创建联结
规定要联结的所有表以及它们如何关联即可
解析：
SELECT 语句与前面所有语句一样指定要检索的列。
这里,最大的差别是所指定的两个列( prod_name和 prod_price )在一个表中,而另一个列( vend_name )在另一个表中。
分析现在来看 FROM 子句。与以前的 SELECT 语句不一样,这条语句的 FROM子句列出了两个表,分别是 vendors 和 products 。
它们就是这条 SELECT语句联结的两个表的名字。这两个表用 WHERE 子句正确联结, WHERE 子句指示MySQL匹配 vendors 表中的 vend_id 和 products 表中的 vend_id 。
可 以 看 到 要 匹 配 的 两 个 列 以 vendors.vend_id 和 products.vend_id 指定。这里需要这种完全限定列名,因为如果只给出 vend_id ,则MySQL不知道指的是哪一个(它们有两个,每个表中一个)。
15.2.1 WHERE 子句的重要性
利用 WHERE 子句建立联结关系似乎有点奇怪,但实际上,有一个很充分的理由。请记住,在一条 SELECT 语句中联结几个表时,相应的关系是在运行中构造的。
在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时,你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。
WHERE 子句作为过滤条件,它只包含那些匹配给定条件(这里是联结条件)的行。没有WHERE 子句,第一个表中的每个行将与第二个表中的每个行配对,而不管它们逻辑上是否可以配在一起
