MySQL必知必会笔记 11-15

使用数据处理函数

函数

SQL 支持利用函数来处理数据, 比如使用 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 是将对串的发音进行比较, 而不是字母

例如: 需要查找顾客名读音为 Y.Lee, 实际上是Y.Lie, 按正确的搜索是不会返回数据的, 但使用 SOUNDEX 就可以得到数据

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

使用 Soundex() 函数匹配发音相似Y.Lie的联系名

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

日期和时间处理函数

使用日期和时间处理函数, 快速有效地排序过滤数据

函数	说明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

对 yyyy-mm-dd 格式的日期进行查找

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

如果 date 数据类型为 datetime 这种存储日期和时间值的数据, 使用上面的匹配方法则会失败

使用 Date函数, 对 yyyy-mm-dd hh:mm:ss 格式的日期进行查找(忽略时间值)

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

使用 Date + BETWEEN + AND 检索某个月份的所有订单

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

这个方法需要记住每个月有多少天, 可以直接用月份来查找

使用 Year + AND + Month 检索某个月份的所有订单

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

Year() 函数从日期中返回年份, Month() 函数从日期这个返回月份

数值处理函数

处理数值数据, 主要用于代数, 三角, 几何运算

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

汇总数据

汇总表的数据

聚集函数

需要汇总函数, 而不是把它们实际检索出来, 如:

确定表中行数(或者满足某个条件或包含某个特定值的行数)
获得表中行数的和
找出表列(或所有行或某些特定的行)的最大值, 最小值和平均值

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG() 函数

返回列/行的平均值(忽略值为 NULL 的行)

使用 AVG() 返回 products 表中所有产品的平均价格

SELECT AVG(prod_price) AS avg_price
FROM products;

通过 WHERE 限定行, 返回特定供应商 1003 提供产品的平均价格

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

COUNT() 函数

返回行的数目

使用 COUNT(*) 对表中行的数目进行计数, 包括空值NULL 和非空值
使用 COUNT(column) 对特定的列中具有值的行计数

使用 COUNT(*) 计数客户总数

SELECT COUNT(*) AS num_cust
FROM customers;

使用 COUNT(column) 计数具有电子邮件地址的客户总数

SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX() 函数

返回指定列中的最大值, 要求指定列名(忽略 NULL 的行)

使用 MAX() 返回产品表中最贵的物品价格

SELECT MAX(prod_price) AS max_price
FROM products;

MIN() 函数

跟 MAX() 函数功能相反, 返回指定列的最小值

使用 MIN() 返回产品表中最便宜的物品价格

SELECT MIN(prod_price) AS min_price
FROM products;

SUM() 函数

返回指定列值的和(总计)

使用 SUM() 返回表中某个产品订单的物品总数

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

聚集不同值

以上五个聚集函数可以使用:

对所有的行执行计算, 默认指定ALL参数
只计算不同的值, 需要指定 DISTINCT 参数

使用 AVG()+ DISTINCT 参数返回 products 表中所有产品不同价格的平均价格

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

组合聚集函数

SELECT 语句可以选择多个聚集函数, 如:

计算 products 表中物品的数目，产品价格的最高、最低以及平均值

SELECT COUNT(*) AS num_item, 
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

分组数据

数据分组

到目前为止所有计算都在表的所有数据或匹配特定的 WHERE 子句的数据上进行的

比如返回供应商 1003 提供的产品数目

SELECT COUNT(*) AS num_prods
FROM prducts
WHERE vend_id = 1003;

如果我需要返回每个供应商提供的产品数目, 或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商

这时候需要用到分组, 分组允许把数据分为多个逻辑组, 以便对每个组进行聚集计算

创建分组 GROUP BY

使用 GROUP BY 计算每个产品商(忽略为 NULL 的产品商)的产品数

SELECT COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

GROUP BY 子句需要注意的事项:

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。

使用 ROLLUP 可以把产品商为 NULL 的进行归档

使用 GROUP BY + WITH ROLLUP 计算每个产品商, 包括统计产品商为NULL 的产品数

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

过滤分组, HAVING子句

GROUP 创建分组, 可以在这个基础上再进行过滤分组

WHERE 过滤指定的行, 而不是分组, 需要用到 HAVING 子句

使用 GROUP 创建分组, 并使用 HAVING 对分组进行过滤

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

使用 WHERE + HAVING 查找prod_price >= 10, 且根据供应商进行分组并筛选产品数大于等于2,

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

分组和排序, GROUP BY + ORDER BY

GROUP BY数据是通过分组顺序输出的, 可以使用 ORDER BY 自定义排序

使用 GROUP BY 检索总计订单价格大于等于 50 的订单的订单号和总计订单价格

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

在上面的基础上, 使用 ORDER BY 进行价格排序输出

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

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

使用子查询

利用子查询进行过滤

表	主要的列
vendors 供应商	vend_id 供应商 ID
products 产品	prod_id 产品 ID, vend_id 供应商 ID
customers 顾客信息	cust_id 顾客 ID
orders 顾客订单日期	order_num 订单号, cust_id 顾客 ID
orderitems 订单物品	order_item 订单物品号, order_num 订单号, prod_id 产品 ID
productnotes 产品注释	note_id 注释 ID, prod_id 产品 ID

列出订购物品 TNT2 所有客户:

检索包含物品 TNT2 所有订单编号 orderitems.prod_id > orderitems.order_num
通过订单编号列出所有客户 ID orderitems.order_num > orders.order_num > orders.cust_id
通过客户 ID 返回所有客户信息 orders.cust_id > customers.cust_id

检索出物品 TNT2 所有订单编号

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

返回订单编号: 20005 和 20007, 通过订单编号返回客户 ID

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);

返回客户 ID : 10001 和 10004, 通过客户 ID 返回客户信息 cust_name, cust_contact

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

1 和 2 结合为子查询组合查询

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

1、2 和 3 结合为子查询组合查询

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

作为计算字段使用子查询

显示 customers 表中每个客户的订单总数

从customers 表中检索客户列表 customers
对于检索出来的每个客户, 统计其在 orders 表中的订单数目COUNT(*) orders.cust_id = customers.cusst_id

SELECT cust_name, cust_state, (SELECT COUNT(*)
                                FROM orders
                                WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

这个有点难理解, 因为通过 SELECT 进行计算的, 不过想起来也合理, 显示客户信息的同时也通过客户信息的 id 在 orders 表中进行查询, 并把查询的结果返回, 每检索出一个客户则计算一次

orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

联结表

联结表就是把每个表的信息独立, 每个表都有个唯一的主键, 而彼此之间使用外键(另一个表的主键)连接

创建联结

例子: 查找出产品的名字、产品的价格及产品对应的厂商名称

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

分析: SELECT 检索出来的 3 个列分别在 vendors, products 两个表中, 而这两个表用 WHERE 子句正确联结, 致使 MySQL 匹配 vendors 表中的 vend_id 和 products 的 vend_id

笛卡尔积(没有联结条件的表关系)

检索出的行的数目将是第一个表中的行数乘于第二个表中的行数

SELECT vend_name, prod_name, prod_price
FROM vendors, products
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;

这里使用 INNER JOIN(首选语法, 能确保不会忘记联结条件) 指定, 联结条件是特定的 ON 子句, 而不是 WHERE 子句, 实际条件是相同的

联结多个表

SQL 可以联结的表数目没有限制

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;

显示订单编号 20005 订单中的物品，与其对应的物品（产品）名、物品（产品）价格、产品供应商名、产品数量。类似超市发票

                        订单编号 20005
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+

关联流程图：

graph LR
A(订单 orderitems) -->|order_num = 20005|B
B(订单 orderitems) -->|prod.id| C(产品表 products)
C -->|vend_id| D(供应商 vendors)

graph TB

B(订单 orderitems) --> H(商品数量 quantity)
C(产品表 products) --> F(产品名 prod_name)
C --> G(产品价格 prod_price)
D(供应商 vendors) --> E(供应商名 vend_name)

之所以感觉之前的例子有点生涩, 是因为我们的阅读顺序不一致, 我们是按顺序来的, 使用上面的关联流程图应该是这样:

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

之前的例子：

使用子查询进行 SELECT操作, 返回订购产品 "TNT2" 的客户列表

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_nim 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 orders.order_num = orderitems.order_num
  AND prod_id = 'TNT2';

posted @ 2020-06-18 14:41 to人间值得阅读(167) 评论(0) 收藏举报

刷新页面返回顶部

人间值得

MySQL必知必会 笔记 11-15