MySQL入门：基础查询与排序

SELECT语句基础

这里创建一张表并插入数据以便后续使用

USE shop;
CREATE TABLE product (
product_id CHAR ( 4 ) NOT NULL,
product_name VARCHAR ( 100 ) NOT NULL,
product_type VARCHAR ( 32 ) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY ( product_id ) 
);
INSERT INTO product
VALUES
	( '0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20' );
INSERT INTO product
VALUES
	( '0002', '打孔器', '办公用品', 500, 320, '2009-09-11' );
INSERT INTO product
VALUES
	( '0003', '运动T恤', '衣服', 4000, 2800, NULL );
INSERT INTO product
VALUES
	( '0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20' );
INSERT INTO product
VALUES
	( '0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15' );
INSERT INTO product
VALUES
	( '0006', '叉子', '厨房用具', 500, NULL, '2009-09-20' );
INSERT INTO product
VALUES
	( '0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28' );
INSERT INTO product
VALUES
	( '0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11' );

基本语法：

SELECT 字段名1，字段名2，……，字段名n
FROM 表名
WHERE 条件表达式;

样例：

SELECT product_name，product_type
FROM product
WHERE product_type = '衣服'

说明：执行SELECT语句后得到的结果都是一张表
查询表中的所有字段，可用*号代替：

SELECT *
FROM 表名;

LIMIT子句可限定查询返回的行数:

SELECT *|{字段名1，字段名2，……}
FROM 表名
LIMIT n;

算数运算符与比较运算符

常用的算术运算符如下

常用的比较运算符如下

样例：

-- 选取出sale_price列为500的记录
SELECT product_name，product_type
FROM product
WHERE sale_price = 500；

逻辑运算符

NOT表示否定，以下两个查询等价：

-- 选取出销售单价大于等于1000日元的记录
SELECT product_name,product_type,sale_price
FROM product
WHERE sale_price >= 1000;
-- 向上述查询的查询条件中添加NOT运算符
SELECT product_name,product_type,sale_price
FROM product
WHERE NOT sale_price >= 1000;

AND和OR运算符，AND相当于集合运算中的取交集，OR相当于集合运算中的并集。使用AND和OR运算符时，应与（）连用，以便得到你想要的结果。比如，想要得到以下结果：“商品种类为办公用品”并且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日”。

-- 将查询条件原封不动地写入条件表达式，会得到错误结果
SELECT product_name,product_type,regist_date
FROM product
WHERE product_type = '办公用品'
		AND regist_date = '2009-09-11'
		OR regist_date = '2009-09-20';
-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name,product_type,regist_date
FROM product
WHERE product_type = '办公用品'
		AND ( regist_date = '2009-09-11'
      	OR regist_date = '2009-09-20');

对表进行聚合函数

以下为常见的聚合函数：
COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值
样例：

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
FROM product;

使用聚合函数删除重复值：

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM product;
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product;

常用法则
COUNT函数的结果根据参数的不同而不同。COUNT()会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
聚合函数会将NULL排除在外。但COUNT()例外，并不会排除NULL。
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT，可以删除重复数据。

对表进行分组

GROUP BY语句：分组汇总，即将现有的数据按照某列来汇总统计。基本语法如下：

SELECT <列名1>,<列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;

样例：

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;
-- 不含GROUP BY
SELECT product_type, COUNT(*)
FROM product;

聚合键中包含NULL时：

SELECT purchase_price, COUNT(*)
FROM product
GROUP BY purchase_price;

GROUP BY书写位置：
GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：

1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理。
在WHERE子句中使用GROUP BY：

SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;

为聚合结果指定条件

可以在GROUP BY后使用HAVING子句，HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）：

-- 数字
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔';

对查询结果进行排序

ORDER BY字句的作用是对查询结果进行排序，基本语法如下：

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……

默认为升序排列，降序排列为DESC

-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC;
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price, product_id;
-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY purchase_price;

因为SQL在使用 HAVING 子句时 SELECT 语句的顺序为：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
所以，ORDER BY语句中的列可以使用别名。

练习题

请编写一条SELECT语句，求出销售单价（sale_price 列）合计值大于进货单价（purchase_price 列）合计值1.5倍的商品种类。执行结果如下所示：

SELECT	product_type,SUM(sale_price) AS sum1,SUM(purchase_price) AS sum2
FROM	product
GROUP BY	product_type
HAVING	sum1 > 1.5*sum2;

结果如下：

设计ORDER BY子句中的内容，输出以下结果：

SELECT *
FROM product
ORDER BY if(isnull(regist_date),0,1),regist_date DESC;