wonderful-sql-第二章：MySQL的基础查询和排序

2.1SELECT 语句基础

2.1.1从表中选出数据

SELECT name FROM addressbook;

2.1.2从表中选取符合条件的数据

where语句
where在指代后面的条件的雨具

-- 用来选取product type列为衣服的记录的SELECT语句
SELECT product_name, product_type
  FROM product
 WHERE product_type = '衣服';
-- 也可以选取出不是查询条件的列（条件列与输出列不同）
SELECT product_name
  FROM product
 WHERE product_type = '衣服';

2.1.3相关法则

• 星号*代表全部列
• 可以随意使用换行符
• 设定的中文的使用需要使用双引号括起来
• 在select语句中使用diatinct可以删除重复行
• 注释1行注释-- 和多行注释 /* */

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
SELECT *
  FROM <表名>；
-- SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））。
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算术运算符和比较运算符

2.2.1算术运算符

SQL中算术运算符有四种
加、减、乘、初
+、-、*、/

2.2.2比较运算符

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

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

2.2.3常用法则

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

相关代码：

-- 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;
/* 对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句*/
-- DDL：创建表
CREATE TABLE chars
（chr CHAR（3）NOT NULL, 
PRIMARY KEY（chr））;
-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
  FROM chars
 WHERE chr > '2';
-- 选取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;

2.3逻辑运算符

2.3.1NOT运算符

可以使用NOT,做出相反的选择，NOT必选和其他的条件配合使用起来，不能单独的使用

--选择出售价大于500的商品，现实它的名字、类别、和价格
SELECT product_name,product_type,sale_price FROM product_lw WHERE sale_price>=500;
--配合NOT可以选择出
SELECT product_name,product_type,sale_price FROM product_lw WHERE NOT sale_price>=500;

MOT sale_price>=500与sale_price <500是等价的

2.3.2AND运算符和OR运算符

AND相当于数学中的交集
OR相当于数学中的并集

通过括号优点处理

运算符具有优先级别的处理

-- 将查询条件原封不动地写入条件表达式，会得到错误结果
SELECT product_name, product_type, regist_date
  FROM product
 WHERE product_type = '办公用品'
   AND regist_date = '2009-09-11'
    OR regist_date = '2009-09-20';
   --这里的where的运算条件，and的优先级比or的优先级更高

2.3.3真值表

真值就是值为真（true）和假（false）期中的之一的值
AND 运算符两侧的真值都为真时返回真，除此之外都返回假。

OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。

NOT运算符只是单纯的将真转换为假，将假转换为真。

含NULL时的真值

NULL不是真也不是假
一般在sql之外的编程中，基本只有真假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有sql中的逻辑运算被称为三值逻辑。

练习题-第一部分

2.1

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

SELECT product_name,regist_date FROM product WHERE regist_date>='2009-04-28';

2.2

SELECT *
  FROM product
 WHERE purchase_price = NULL;
 --选出购买价是空的商品，并对满足条件的行显示出所有的列

SELECT *
  FROM product
 WHERE purchase_price <> NULL;
 --选出购买价不是空值的商品，并展示出所有的列

SELECT *
  FROM product
 WHERE product_name > NULL;
 --没法比较，返回空

2.3

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

--第一种
 SELECT product_name,sale_price,purchase_price FROM product WHERE NOT sale_price-purchase_price < 500 ;

--第二种
SELECT product_name,sale_price,purchase_price FROM product WHERE sale_price >= 4000 OR product_type='衣服';

2.4

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

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

2.4对表进行聚合查询

2.4.1聚合函数

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

• SUM：计算表中某数值列中的合计值
• AVG：计算表中某数值列中的平均值
• MAX：计算表中任意列中数据的最大值，包括文本类型和数字类型
• MIN：计算表中任意列中数据的最小值，包括文本类型和数字类型
• COUNT：计算表中的记录条数（行数）

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

使用DISTINCT进行删除重复值的聚合运算

-- 计算产品的种类数量
SELECT COUNT(DISTINCT product_type)
  FROM product;

2.4.2聚合函数应用法则

• COUNT 聚合函数运算结果与参数有关，COUNT(*) / COUNT(1) 得到包含 NULL 值的所有行，COUNT(<列名>) 得到不包含 NULL 值的所有行。
• 聚合函数不处理包含 NULL 值的行，但是 COUNT(*) 除外。
• MAX / MIN 函数适用于文本类型和数字类型的列，而 SUM / AVG 函数仅适用于数字类型的列。
• 在聚合函数的参数中使用 DISTINCT 关键字，可以得到删除重复值的聚合结果

2.5对表进行分组

2.5.1GROUP BY语句

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

这样，GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列

聚合键中包含NULL时

此时会将NULL作为一组特殊数据进行聚合运算

GROUP BY书写位置

2.5.2常见错误

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

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

2.6为聚合结果指定条件

2.6.1用HAVING得到特定分组

前面学习了如何得到分组聚合结果，现在大家思考一下，如何得到分组聚合结果的部分结果呢？

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

这里 WHERE 不可行，因为，WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为 2 行”或者“平均值为 500”等）。

可以在 GROUP BY 后使用 HAVING 子句。

HAVING 的用法类似 WHERE。

值得注意的是：HAVING 子句必须与 GROUP BY 子句配合使用，且限定的是分组聚合结果，WHERE 子句是限定数据行（包括分组列），二者各司其职，不要混淆。

2.6.2HAVING特点

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 = '圆珠笔';

2.7对查询结果进行排序

2.7.1ORDER BY

使用ORDER BY 进行拍讯

## 排序语法的结构
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1> [ASC, DESC], <排序基准列2> [ASC, DESC], ……

-- 降序排列，默认是升序排列，ASC为升序排列，可以缺省ASC
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;

2.7.2ORDER BY 可以使用别名

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

FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY

其中 SELECT 的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。

当在 ORDER BY 子句中使用别名时，已经知道了 SELECT 子句设置的别名，但是在 GROUP BY 子句执行时还不知道别名的存在，所以在 ORDER BY 子句中可以使用别名，但是在GROUP BY中不能使用别名。

2.7.3ORDER BY 遇上 NULL

在MySQL中，NULL 值被认为比任何 非NULL 值低，因此，当顺序为 ASC（升序）时，NULL 值出现在第一位，而当顺序为 DESC（降序）时，则排序在最后。

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    date_login DATE,
    PRIMARY KEY (id)
);

INSERT INTO user(name, date_login) VALUES
(NULL,    '2017-03-12'), 
('john',   NULL), 
('david', '2016-12-24'), 
('zayne', '2017-03-02');

NULL的值比非NULL值低（0或者-OO），在拍讯时就要对这个默认情况进行特殊处理以达到理想的效果

• 将NULL值排在末行，同时将所有非NULL值按升序排列。
  对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来得到反向排序（-1、-2、-3…）

SELECT * FROM user 
 ORDER BY -date_login ASC;

对于字符型或者字符型数字，可以试用IS NOT NULL比较运算符。另外！ISNULL()函数等同于使用 IS NOT NULL比较运算符。


上述语句先使用 !ISNULL(name) 字段进行升序排列，而只有当 name 列值不为 NULL 时，!ISNULL(name) 才为真，所以其排到说行，而 name DESC 则实现了 非NULL 值降序排列。

使用 COALESCE 函数实现需求

练习题-第二部分

2.5

指出下面的语句的语法错误

SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

错误的地方：
1.where语句应该在group by 之前
2.sum没法计算字符的合计值

2.6

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

product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

2.7

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

SELECT *
FROM product
ORDER BY -regist_date,product_id DESC ;