《MySQL必知必会》学习笔记__第11章至第15章-CSDN博客

第11章使用数据处理函数

SQL实现支持以下类型的函数：

用于处理文本串的文本函数；

用于在数据上进行算数操作的数值函数；

用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数；

返回DBMS正使用的特殊信息的系统函数。

文本处理函数：RTrim()函数去除列值右边的空格

Upper()函数将文本转换为大写：SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

常用的文本处理函数：

Left() 返回串左边的字符

Length() 返回串的长度

Locate() 找出串的一个子串

Lower() 将串转换为小写

LTrim() 去掉串左边的空格

Right() 返回串右边的字符

RTrim() 去掉串右边的空格

Soundex() 返回串的SOUNDEX值

SubString() 返回子串的字符

Upper() 将串转换为大写

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y.Lie';

SELCT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lie')

customers 表中有一个顾客的联系名为Y.Lee，但是这是一个错误的输入，其正确的联系名为Y.Lie。使用Soundex()函数进行搜索，它能匹配所有发音类似于Y.Lie的联系名。

日期和时间处理函数

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

AddDate() 增加一个日期（天、周等）

AddTime() 增加一个时间（时、分等）

CurDate() 返回当前日期

CurTime() 返回当前时间

Date() 返回日期时间的日期部分

DateDiFF() 计算两个日期之差

Date_Add() 高度灵活的日期运算函数

Date_Format() 返回一个格式化的日期或时间串

Day() 返回一个日期的天数部分

DayOfWeek() 对于一个日期，返回对应的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Month() 返回一个时间的月份部分

Now() 返回当前日期和时间

Second() 返回一个时间的秒部分

Time() 返回一个日期时间的实践部分

Year() 返回一个日期的年份部分

在MySQL中，日期格式必须为yyyy-mm-dd。

SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';

WHERE order_date = '2005-09-01' 这种做法并不可靠，因为当表中的order_date储存的值为2005-09-01 11：30：05 时，WHERE order_date = '2005-09-01'将会检索失败。

使用Date(order_date)可以提取出列的日期部分，这种做法将更为可靠：

SELECT cust_id, order_num FROM orders WHERE Date(order_data) = '2005-09-01';

检索出2005年9月的所有订单：SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数：主要用于代数、三角或集合运算。

常用数值处理函数：

Abs() 返回一个数的绝对值

Cos() 返回一个角度的虚线

Exp() 返回一个数的指数值

Mod() 返回除操作的余数

Pi() 返回圆周率

Rand() 返回一个随机数

Sin() 返回一个角度的正弦

Sqrt() 返回一个数的平方根

Tan() 返回一个角度的正切

第12章汇总数据

聚集函数(aggregate function)：运行在行组上，计算和返回单个值的函数。

SQL聚集函数：

AVG() 返回某列的平均值

COUNT（）返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

AVG()函数通过对表中行数计数并计算特定列这和，求得该列的平均值。AVG()函数可以用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

SELECT AVG(prod_price) AS avg_price FROM products;

利用AVG()函数确定SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

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

AVG()函数忽略列值为NULL的行。

COUNT()函数可以确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

SELECT COUNT(*) AS num_cust FROM customers;

SELECT COUNT(cust_email) AS num_cust FROM customers;

如果指定列名，则指定列的值为空的行被COUNT()忽略，如果COUNT()函数中用的是星号(*)，则NULL不被忽略。

MAX()函数：返回指定列中的最大值。MAX()函数要求指定列名。MAX()忽略值为NULL的行。

SELECT MAX(prod_price) AS max_price FROM products;

MIN()函数：返回指定列的最小值。MIN()忽略值为NULL的行。

SELECT MIN(prod_price) AS min_price FROM products;

在用于文本数据时，如果数据按相应的列排序，则MAX() 和 MIN() 返回最后面的和最前面的行。

SUM()函数：用来返回指定列值的和。

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

SUM(quantity) 返回订单中所有物品数量之和，忽略值为NULL的行。

SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;

此句：合计每个项目物品的item_price *quantity，得出总的订单金额。

对于以上的五个聚集函数都可以如下使用：

对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；

只包含不同的值，指定DISTINCT参数。

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

DISTINCT 必须使用列名，不能用于计算或表达式，例如DISTINCT 只能用于COUNT()，而不能用于COUNT(*)，即不允许使用COUNT(DISTINCT)。

组合聚类函数： SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;

第13章分组数据

本章将介绍如何对数据进行分组，以便汇总表内容的子集。

本章将介绍SELECT语句的两个子句，分别为GROUP BY 和HAVING子句。

返回供应商1003提供的产品数目：SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003;

创建分组GROUP BY： SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

注意：

GROUP BY 子句可以包含任意数目的列；

如果在GROUP BY 子句中嵌套了分组，数据将在最后分组上进行汇总；

GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚类函数；

在SELECT 语句中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名；

除了聚集计算语句外，SELECT语句中的每个列都必须在GROUP子句中给出；

若分组中有NULL值，则NULL将作为一个分组返回；如果列中有多行NULL，它们将分为一组；

GROUP BY 必须出现在WHERE子句之后，ORDER BY子句之前。

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别的值：SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

过滤分组HAVING：规定包括哪些分组，排除哪些分组。

事实上，目前为止所学习过的所有类型的WHERE子句都可以用HARVING来代替。唯一的差别就是WHERE过滤行，而HARVING过滤分组。

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

此句过滤了COUNT(*) >=2（两个以上的订单）的那些分组。

HAVING和WHERE的差别：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >=2;

在此句中，WHERE子句过滤了所有prod_price至少为10的行；

然后按照vend_id分组数据

HAVING子句过滤计数为2或2以上的分组。

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >=2;

分组和排序：

ORDER BY 和GROUP BY的区别：

ORDER BY GROUP BY

排序产生的输出分组行，但输出可能不是分组的序列

任意列都可以使用（甚至非只可能使用选择列或表达式列，而且必须使用每个选择列表达式选择的列也可以使用）

不一定需要如果与聚集函数一起使用列（或表达式），则必须使用

注意：一般使用GROUP BY子句时，应该也要给出ORDER BY子句，这是保证数据正确排序的唯一方法。

SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;

SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;

SELECT 子句及其顺序：

子句说明是否必须使用

SELECT 要返回的列或者表达式是

FROM 从中检索数据的表仅在从表中选择数据是使用

WHERE 行级过滤否

GROUP BY 分组说明仅在按组计算聚集时使用

HAVING 组级过滤否

ORDER BY 输出排序顺序否

LIMIT 要检索的行数否

第14章使用子查询

本章内容：介绍子查询；如何编写使用子查询。

SELECT 语句时SQL的查询，迄今为止我们学习的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。

查询(query)： 任何SQL语句都是查询，但此术语一般指SELECT语句。

SQL允许创建子查询(subquery)，即嵌套在其他查询中的查询。

需要列出订购物品TNT2的所有客户，具体检索步骤如下：

(1) 检索包含物品TNT2的所有订单的编号；

(2) 检索具有前一步骤列出的订单编号的所有客户的ID；

(3) 检索前一步骤返回的所有客户ID的客户信息。

以上步骤都可以单独作为一个查询来执行，可以把一条SELECT语句的结果用于另一条SELECT语句的WHERE子句。

第一条： SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

第二条：SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

第一条和第二条的组合查询：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

第三步：检索这些客户ID的客户信息。

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);

第一、二、三条组合：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'));

在使用WHERE子句中使用子查询，应该保证SELECT 语句具有与WHERE子句中相同数目的列。

要显示customers表中每个客户的订单总数，订单与相应的客户ID储存在orders表中：

（1）从customers表中检索客户列表；

（2）对于检索出的每个客户，统计其在orders表中的订单数目。

对客户10001的订单进行计数：SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;

对每个客户执行COUNT(*)计算：SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name; （使用了完全查询）

相关子查询(correlated subquery)： 涉及外部查询的子查询。

第15章联结表

本章内容：介绍联结；为什么使用联结；如何编写联结的SELECT语句。

外键(foreign key)：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性(scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。

创建联结： SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

这里，FROM vendors, products 联结了两个表的名字。

完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。

笛卡儿积(cartesian product)：由于没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一表中的行数乘以第二个表中的行数。

注意：应当保证所有联结都有WHERE子句，同时确保WHERE子句的正确定。

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

内部联结：目前为止所用的联结称为等值联结，它是基于两个表之间的相等测试，这种联结也称为内部联结。

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

联结多个表：

SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products. vend_id = vendors. vend_id AND orderitems. prod_id = products.prod_id AND order_num = 20005;

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';

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'));