mysql按分类id查询 mysql分类汇总与排序

一、分组和排序

1. 分组排序：GROUP BY 根据指定字段进行排序分组

具体说明：

(1). GROUP BY 子句可以包含任意数目的列, 这将能对分组进行嵌套, 为数据分组提供更细致的控制

(2). 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总(即指定的所有列都在一起计算)

(3). GROUP BY子句中列出的每个列都必须是检索列或有效表达式,表达式不能是聚合函数，且GROUP BY子句别名不可使用

(4). 除聚合函数外, SELECT语句中的每个列都必须在GROUP BY 子句中给出

(5). 如果分组中具有Null值, 则Null将作为一个分组返回, 如果列中有多行Null值, 它们将分为一组

(6). GROUP BY 子句必须出现在WHERE 子句之后, ORDER BY 子句之前

SELECT vend_id, COUNT(*) AS 数量 FROM products GROUP BY vend_id

2. 过滤分组: ORDER BY 根据指定字段进行排序分组

具体说明：

(1). 所有类型的WHERE子句都可以用HAVING代替

(2). 区别在于：WHERE过滤行, HAVING过滤分组；WHERE在数据分组前进行过滤, HAVING在数据分组后进行过滤

SELECT cust_id AS 编号, COUNT(*) AS 订单 FROM orders GROUP BY cust_id HAVING COUNT(*)>=2

(3). 分组和排序：ORDER BY 用于排序，GROUP BY 用于分组

示例：以按总计订单价格排序输出

SELECT order_num, SUM(quantity* item_price) AS 订单总价 FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY 订单总价

3. SELECT查询子句顺序

(1).WHERE           　　行级过滤
(2).HAVING        　　　组级过滤
(3).GROUP BY    　　　　分组说明
(4).ORDER BY    　　　　输出排序顺序
(5).LIMIT            　要检索的行数

二、聚合函数

1. 求平均值函数：AVG(X) 返回某列的平均值 以平均单价为例

SELECT AVG(prod_price) FROM products;

2. 求行数聚合函数：COUNT(X) 返回指定列的行数 (COUNT(*)表示返回所有行) 

SELECT COUNT(*) FROM products;

SELECT COUNT(prod_price) AS 平均单价 FROM products;
SELECT COUNT(cust_email) AS 电子邮件地址数量 FROM customers;

3. 求各类值的聚合函数：MAX(X)  MIN(X) SUM(X) 分别返回指定列得最大值, 最小值，总数

SELECT MAX(prod_price) AS 最大值,  MIN(prod_price) AS 最小值,  SUM(prod_price) AS 总数 FROM products;

4. 求总数聚合函数：SUM(X) 根据制定条件返回特定列得总数，但通常SUM()函数会忽略指定列值为NULL的所在行

SELECT SUM(quantity) FROM orderitems WHERE order_num = 20005

5. 针对上述5个聚合函数的相关说明：

(1). 对所有的行执行计算, 指定ALL参数或不给参数(因为ALL是默认行为)

(2). 只包含不同的值, 指定DISTINCT参数

6. 去重聚合函数：DISTINCT用于消除指定列的重复值，若指定列名则DISTINCT只能用于COUNT()，DISTINCT不能用于COUNT(*)

SELECT AVG(DISTINCT prod_price) AS 均价  FROM products WHERE vend_id=1003

7. 聚合函数综合应用：实际应用中SELECT语句更多的可根据需要包含多个聚合函数

SELECT COUNT(*) AS 行数,MIN(prod_price) AS 最低价,MAX(prod_price) AS 最高价,AVG(prod_price) AS 平均价 FROM products

 三、各类查询

1. 普通子查询语句 (以查询订单明细中指定物品的对应客户信息为例)

第一种：多条SELECT语句查询方式

/*Step1：先检索所有包含指定物品的订单编号*/
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
/*Step2：再根据订单编号检索订单编号对应客户编号*/
SELECT cust_id FROM orders WHERE order_num IN ('20005', '20007')
/*Step3：最后根据客户编号检索对应客户信息*/
SELECT * FROM customers WHERE cust_id IN ('10001', '10004')

第二种：子查询语句查询方式

SELECT * 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 c.cust_name, c.cust_state , (SELECT COUNT(*) FROM orders o WHERE o.cust_id = c.cust_id) AS count 
FROM customers c ORDER BY c.cust_name

3. 简单两表联合查询

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

4. 笛卡尔积查询方式

-- 笛卡尔积：指检索初行的数目为第一个表中的行数乘以第二个表的行数 (示例如下)
SELECT v.vend_name, p.prod_name, p.prod_price FROM vendors v, products p ORDER BY v.vend_name,  p.prod_name

5. 内部联结查询：INNER JOIN

SELECT v.vend_name, p.prod_name, p.prod_price FROM vendors v INNER JOIN products p ON v.vend_id = p.vend_id
ORDER BY v.vend_name, p.prod_name

6. 多表联合查询

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

7. 自联结查询方式 (示例：检索出现问题产品对应供应商的其他所有产品)

第一种：自联结子查询

SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')

第二种：自联结自查询

SELECT p1.prod_id, p1.prod_name FROM products p1 , products p2 WHERE 
p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

8. 外部联结 (可分为左外联：LEFT OUTER JOIN 和右外联：RIGHT OUTER JOIN )

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders 
ON customers.cust_id = orders.cust_id

SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders 
ON customers.cust_id = orders.cust_id

9. 聚合函数的左外联查询

SELECT c.cust_id AS 编号, c.cust_name AS 姓名, COUNT(o.order_num) AS 订单数 FROM customers c 
LEFT OUTER JOIN orders o ON c.cust_id = o.cust_id GROUP BY c.cust_id

10. 正则表达式

(1). 正则表达式1：.表示匹配任意一个字符，类似于通配符_

SELECT vend_id, vend_name, vend_city  FROM vendors WHERE vend_name REGEXP '.000' ORDER BY vend_name

(2). 正则表达式2：表示匹配其中之一，[123]和[1|2|3] 都是或者语句，[^123] 则表示否定匹配这些指定字符 ，类似于通配符中的OR

SELECT vend_id, vend_name, vend_city  FROM vendors WHERE vend_id REGEXP '[1|2|3]' ORDER BY vend_id

(3). 正则表达式3：表示匹配特殊字符.,需要使用转移符号\\(若需要匹配反斜杠,则需要三个转义加字符本身,即\\\\)

SELECT vend_id, vend_name, vend_city  FROM vendors WHERE vend_name REGEXP '\.' ORDER BY vend_name

(4). 正则表达式4：表示匹配任意数字，stick?表示匹配stick和sticks (s后的?可选,因为?匹配它前面的任何字符的0次或1次出现)

SELECT * FROM products WHERE prod_name REGEXP '\([0-9] sticks?\)' ORDER BY prod_name;

(5). 正则表达式5：表示匹配任意数字({4}表示为要求它前面任意数字出现4次)，类似于[0-9][0-9][0-9][0-9] ，[:digit:] 表示数字0-9

SELECT vend_id, prod_name, prod_price  FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name

(6). 正则表达式6：表示^定位符, (表示^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们

SELECT vend_id, prod_name , prod_price FROM products WHERE prod_name REGEXP '^[0-9\.]' ORDER BY prod_name;

11. 查询小结

(1). 语法：ANSI SQL 规范首选INNER JOIN 语法；尽管使用WHERE子句定义联结十分简单, 但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也会影响性能
(2). 别名的作用：
<1>. 缩短SQL语句
<2>. 允许在单条SELECT语句中多次使用相同的表
(3). 外部联结OUTER JOIN
<1>. 使用外部联结OUTER JOIN语法时, 需要使用LEFT或RIGHT关键字指定包括其所在行的表
<2>. LEFT OUTER JOIN 从FROM子句的左边表中选择所有行(无论是否存在数据都显示)