【数据库02】SELECT语句基础

---

1. SELECT语句基础

1.1 查询语句

SELECT <列名>,
	FROM <表名>
WHERE <条件表达式>；

商品表

【例子】从product表中选出商品种类product_type为衣服的商品名称

SELECT product_name
FROM product
WHERE product_type = ’衣服’;

【注意】

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

-- 给product中的一些列设定别名
SELECT product_id As id,
	   product_name As name,
	   purchase_price AS "进货单价"
FROM product;

--使用DISTINCT删除product_type列中重复的数据
SELECT DISTINCT product_type
FROM product;

---

2. 算术运算符和比较运算符

2.1 算术运算符

SQL中常用算术运算符如下

-- SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
FROM product;
-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price-purchase_price >= 500;

2.2 比较运算符

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

--对字符串使用不等号，首先创建chars并插入数据，选取出大于‘2’的SELECT语句
CREATE TABLE chars
（chr CHAR（3）NOT NULL,
PRIMARY KEY（chr））;

SELECT chr
FROM chars
WHERE chr > ’2’;

2.3 常用法则

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

-- 选取NULL的记录
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
- -- 选取不为NULL的记录
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

3. 逻辑运算符

3.1 NOT 运算符

想要表示“不是……”时，可以用<> ，也可以用NOT，后者的应用范围更为广泛
【注意】NOT不能单独使用

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

3.2 AND运算符和OR运算符

当希望同时使用多个查询条件时，可以使用 AND 或者 OR 运算符。

• AND 相当于“并且”，类似数学中的取交集；
• OR 相当于“或者”，类似数学中的取并集。

【注意】AND 运算符优先于 OR 运算符，想要优先执行 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’;
--正确方法
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = ’办公用品’
	AND ( regist_date = ’2009-09-11’
	OR regist_date = ’2009-09-20’);

3.3 真值表

NOT、AND 和 OR 称为逻辑运算符。这里所说的逻辑就是对真值进行操作的意思。真值就是值为真（TRUE）或假（FALSE）其中之一的值。

例如，对于 sale_price >= 3000 这个查询条件来说，由于 product_name 列为’ 运动 T 恤’ 的记录的
sale_price 列的值是 2800，因此会返回假（FALSE），而 product_name 列为’ 高压锅’ 的记录的 sale_price
列的值是 5000，所以返回真（TRUE）。

• AND 运算符两侧的真值都为真时返回真，除此之外都返回假。
• OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。
• NOT 运算符只是单纯的将真转换为假，将假转换为真。

【三值逻辑】含有 NULL 时的真值 NULL 的真值结果既不为真，也不为假，因为并不知道这样一个值。那该如何表示呢？
这时真值是除真假之外的第三种值——不确定（UNKNOWN）。与通常的逻辑运算被称为二值逻辑相对，只有SQL 中的逻辑运算被称为三值逻辑。三值逻辑下的 AND 和 OR 真值表为

4. 对表进行聚合查询

4.1 聚合函数

SQL 中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：

• 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函数）
ELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product;

4.2 常用法则

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

5. 对表进行分组

5.1 GROUP BY语句

聚合函数是对整个表的数据进行处理，如果想进行分组汇合（即：将现有的数据按照某列来汇总统计），可以使用GROUP BY

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

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

聚合键中包含 NULL 时，会将 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 对筛选出的数据进行处理

5.2 常见错误

在使用聚合函数及 GROUP BY 子句时，经常出现的错误有：

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

6. 为聚合结果指定条件

6.1 用 HAVING 得到特定分组

将表使用 GROUP BY 分组后，怎样才能只取出其中两组？

可以通过HAVING子句实现，HAVING作用类似于WHERE但是WHERE后不可以跟聚合函数

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

-- 取出只含有两种商品的类别
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;

7. 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;

ORDER BY 中列名可使用别名，但是GROUP BY中不可以使用，因为SELECT 的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是说，当在 ORDER
BY 中使用别名时，已经知道了 SELECT 设置的别名存在，但是在 GROUP BY 中使用别名时还不知道
别名的存在，所以不能在 ORDER BY 中可以使用别名，但是在 GROUP BY 中不能使用别名

8. 练习题

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.请说出对 product 表执行如下 3 条 SELECT 语句时的返回结果。

--一条记录也取不出来，因为不能对null使用比较运算符，只能使用is null和is not null。
SELECT *
FROM product
WHERE purchase_price = NULL;

--一条记录也取不出来，因为不能对null使用比较运算符，只能使用is null和is not null。
SELECT *
FROM product
WHERE purchase_price <> NULL;

--一条记录也取不出来，因为不能对null使用比较运算符，只能使用is null和is not null。
SELECT *
FROM product
WHERE product_name > NULL;

3.代码清单 2-22（2-2 节）中的 SELECT 语句能够从 product 表中取出“销售单价（saleprice）比进货
单价（purchase price）高出 500 日元以上”的商品。请写出两条可以得到相同结果的 SELECT 语句。执
行结果如下所示。

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

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) as profit
FROM product 
WHERE saleprice*0.9-purchase_price>100
AND (product_type = '办公用品' or product_type = '厨房用具')

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

• 在聚合函数的 SELECT 子句中写了聚合健以外的列使用 COUNT 等聚合函数时，SELECT 子句中
  如果出现列名，只能是 GROUP BY 子句中指定的列名（也就是聚合键）。product_id不符合
• where子句的书写顺序不对，应该在from后面group by前面。
• sum()函数的参数不能用字符型的product_name，要用数字型。

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

SELECT product_type, 
       SUM(sale_price) as sum_sale_price, 
       SUM(purchase_price) as sum_purchase_price
FROM Product
GROUP BY product_type
HAVING SUM(sale_price) > SUM(purchase_price) * 1.5;

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

SELECT *
FROM product
ORDER BY regist_date DESC;