MySQL 《必知必会》 源码 二

0## 5. 计算字段, 函数, 分组

---------------------计算字段-------------------------

## 拼接字段: Concat() + 别名
## Concat可以把多个指定的串拼接成一个较长的串
SELECT Concat( vend_name, '(', vend_country, ')') 
AS vend_title 
FROM vendors 
ORDER BY vend_name;

----------------------处理函数-------------------------

## https://blog.csdn.net/sinat_38899493/article/details/78710482

----------------------汇总数据-------------------------
## 聚集函数: 运行在行组上, 计算和返回单个值的函数
## 1. AVG(): 返回平均值
SELECT AVG(prod_price) AS '平均值' 
FROM products;

## 2. COUNT(): 确定符合条件的行的数目
## 用法1: COUNT(*): 对表中行的数目进行统计, 包含NULL值行
SELECT COUNT(*)  
FROM products; ## 统计表中记录总数
## 用法2: COUNT(column): 对特定表进行统计行数
SELECT COUNT(prod_price)  
FROM products; ## 统计prod_price的记录数 

## 3. MAX(): 求最大值; MIN(): 求最小值; SUM(): 求总和

---------------------分组数据---------------------------

## 使用GROUP BY 和 HAVING进行数据分组
## 1. 根据vend_id分组, 查询出每个制造商的商品数, with rollup表示最后再加个总记录数
SELECT vend_id, COUNT(*) AS num_prod 
FROM products 
GROUP BY vend_id 
WITH ROLLUP;

## 2. 查询出订单数>=2的客户, HAVING过滤条件
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>1;

## 3. 检索出商品数 >= 2, 价格>= 10 De 商品信息, HAVING + WHERE的组合过滤
SELECT vend_id, prod_price, COUNT(*) AS num_prods 
FROM products 
WHERE prod_price>=10 
GROUP BY vend_id 
HAVING COUNT(*) >= 2;

6. 子查询和联合查询

------------------------子查询----------------------------------

## 1. 最后一个字句返回产品id = TNT2的订单
##    倒数第二个 返回这两个订单的客户id
##    第一个 返回这个客户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 = 'TNT2'));

## 2. 计算字段作为子查询
SELECT cust_name, 
cust_state, 
( SELECT COUNT(*) 
FROM orders 
WHERE orders.cust_id = customers.cust_id ) AS orders 
FROM customers
ORDER BY cust_name;

## 3. 使用联结进行查询
## 外键: 是某个表中包含另一个表主键值的一列, 定义了两个表的关系

## 建议使用 INNER JOIN来联结左右两个表, 因为ON确保不会遗忘连接条件 
SELECT vend_name, prod_name, prod_price 
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

## 内部联结: INNER JOIN ... ON
## 外部联结: OUTER JOIN ... ON, 外部联结需要 LEFT 或 RIGHT 来制定左联结还是右联结
## LEFT表示选取左边表的所有行, RIGHT表示选取右边表的所有行

## 使用AND联结多个表: 
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语句
SELECT cust_name, cust_contact
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 = 'TNT2';

## 不同的连接: 
## COUNT()函数的联结: 检索所有客户以及每个客户的订单数
SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS num_ord
FROM customers AS c INNER JOIN orders AS o 
ON c.cust_id = o.cust_id
GROUP BY c.cust_id; 

7. 组合查询

## 使用UNION来将多条SELECT组合成一个结果

## 语句1 : 检索出价格小于等于5的记录
SELECT * 
FROM products 
WHERE prod_price <=5;

## 语句2 : 检索出1001和1002生产的商品
SELECT * 
FROM products 
WHERE vend_id IN(1001,1002);

## 使用UNION整合: 
SELECT * 
FROM products 
WHERE prod_price <=5
UNION
SELECT * 
FROM products 
WHERE vend_id IN(1001,1002);

## UNION规则: UNION会自动取消重复记录, 如果需要, 使用UNION ALL来包含所有