SQL基础查询和排序

所需数据表dataw:

2.1 SELECT 语句基础

2.1.1 SELECT语句

查询功能的实现可以使用SELECT语句来完成
语法：

#SELECT子句指定了从表中选取的数据列，FROM子句指定了选取数据的表
SELECT 列名，FROM 表名；

直接上代码：

#查询dataw表中的username和date字段
SELECT username,date FROM dataw;

2.1.2 WHERE语句从表中选取符合条件数据

有时候我们不需要将整个表中的数据全部取出来，而是选取一部分数据精确分析，这个时候WHERE语句就派上用场了。
语法：

# 条件表达式中可以使用运算符如+ - * /等
SELECT 列名，……
  FROM 表名
 WHERE 条件表达式；

代码：

SELECT username,date FROM dataw WHERE price >=6;

2.1.3 相关法则

• 星号（*）代表全部列的意思。
• SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
• 设定汉语别名时需要使用双引号（"）括起来。
• 在SELECT语句中使用DISTINCT可以删除重复行。
• 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- “和多行注释两种”/* */"。
• SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））
  
• 使用DISTINCT删除date列中重复的数据
  代码：

SELECT DISTINCT date FROM dataw;

2.2 算术运算符和比较运算符

2.2.1 算术运算符

SQL语句的算术运算符主要有+ - * / 对应加 减 乘 除。

2.2.2 比较运算符

运算符	含义
=	等于
<>	不相等
>=	大于等于
>	大于
<=	小于等于
<	小于

2.2.3 常用法则

• SELECT子句中可以使用常数或者表达式。
• 使用比较运算符时一定要注意不等号和等号的位置。
• 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
• 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

【例子】
代码：

SELECT username AS name FROM dataw WHERE id >=3;

2.3 逻辑运算符

2.3.1 NOT

表示“不是…”的含义，可以理解为取反当前指令的意思。
代码：

SELECT username,date,price FROM dataw WHERE NOT price >=9;

2.3.2 AND和OR

AND可以理解为“并且”

SELECT username FROM dataw WHERE price >=7 AND id<3;

OR理解为“或者”

SELECT username FROM dataw WHERE price >=7 OR id<3;

如果需要将运算符的优先级提升，可以使用括号()来提升优先级。

2.4 练习题

2.1
编写一条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4月28日之后”的商品，查询结果要包含product name和regist_date两列。

SELECT product_name,regist_date FROM product WHERE regist_date >"2009-4-28";

2.2

2.3
代码清单2-22（2-2节）中的SELECT语句能够从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。
product_name | sale_price | purchase_price
-------------±-----------±-----------
T恤衫 | 　 1000 | 500
运动T恤 | 4000 | 2800
高压锅 | 6800 | 5000

SELECT product_name,sale_price,purchase_price FROM product WHERE sale_price-500 >=purchase_price;
SELECT product_name,sale_price,purchase_price FROM product WHERE sale_price >=purchase_price+500;

2.4
请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。

提示：销售单价打九折，可以通过saleprice列的值乘以0.9获得，利润可以通过该值减去purchase_price列的值获得。

 SELECT product_name,product_type,
 sale_price*0.9-purchase_price>100 AS profilt FROM product;

2.5 对表进行聚合查询

2.5.1 聚合函数

SQL中用于汇总的函数称为聚合函数，常用的聚合函数：

• COUNT：计算表中的记录数（行数）
• SUM：计算表中数值列中数据的合计值
• AVG：计算表中数值列中数据的平均值
• MAX：求出表中任意列中数据的最大值
• MIN：求出表中任意列中数据的最小值

# 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
  FROM dataw;
# 计算NULL以外数据的行数
SELECT COUNT(price)
  FROM dataw;
# 计算price和prices的合计值
SELECT SUM(price), SUM(prices) 
  FROM dataw;
# 计算price和prices的平均值
SELECT AVG(price), AVG(prices)
  FROM dataw;
# MAX和MIN也可用于非数值型数据
SELECT MAX(date), MIN(date)
  FROM dataw;

• 用聚合函数删除重复值

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

2.5.2 常用法则

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

2.6 对表分组

2.6.1 GROUP BY

使用聚合函数会对整个表的数据进行处理，使用GROUP BY 可以进行分组汇总。
语法：

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

在 GROUP BY 子句中指定的列称为聚合键或者分组列。

• 聚合值中包含NULL时，会将NULL值作为一组特殊数值处理。
• GROUP BY书写位置
  1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

2.6.2 常见错误

• 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
• 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
• 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING以及ORDER BY子句中使用聚合函数。

2.7 为聚合结果指定条件

2.7.1 用HAVING得到特定分组

使用GROUP BY对数据进行分组以后，如果仅仅想取出来其中一部分分组，使用WHERE不可取，这个时候HAVING语句就要上场了。HAVING接在GROUP BY语句的后面。

2.7.2 HAVING特点

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

# 数字
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

2.8 对查询结果进行排序

2.8.1 ORDER BY

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

排序默认为升序，降序使用DESC

代码：

# 通过PRICE降序排列
SELECT USERNAME,PRICE.DATE 
FROM DATAW 
ORDER BY PRICE DESC;

• 使用多个排序键

SELECT USERNAME,PRICE,DATE,ID
FROM DATAW
ORDER BY PRICE,ID;

2.8.2 ORDER BY中列名可使用别名

SQL在使用HAVING子句SELECT语句的顺序为：
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。

2.9 练习题

2.5
请指出下述SELECT语句中所有的语法错误。

SELECT product id，SUM（product name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01'；

GROUP BY后面的字段只可以是SELECT和函数中选中的字段。

2.6
请编写一条SELECT语句，求出销售单价（sale_price列）合计值是进货单价（purchase prilce列）合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum | sum
-------------±-----±-----
衣服 | 5000 | 3300
办公用品 | 600 | 320

select product_type, sum(sale_price), sum(purchase_price)
from product
group by product_type
having sum(sale_price) > sum(purchase_price) *1.5;

2.7
此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了ORDER BY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDERBY子句的内容。

SELECT product_id,
product_name,
product_type,
sale_price,
purchase_price,
regist_date
FROM product 
ORDER BY regist_date DESC,sale_price;