sql-第二章：基础查询与排序

基础查询与排序

SELECT 语句

从表中选取数据时需要使用SELECT语句

基本SELECT语句包含了SELECT 和 FROM 两个字句，语法：

SELECT 列名 

FROM 表名 ;

选取符合条件的数据，使用WHERE子句，语法：

SELECT 列名1,列名2,...

FROM  表名

WHERE 条件表达式 ;

可以选取出不是查询条件的列（即条件列与输出列不同），如：

SELECT product_name

FROM product

WHERE product_type=’衣服’;

查询全部列

SELECT *

FROM 表名;

使用AS关键字为列设定别名（用中文时需要双引号””）

SELECT product_id AS id,

product_name AS name,

Purchase_price AS “进货单价”

FROM product;

使用DISTINCT删除列中重复的数据（即仅显示UNIQUE数据，不会修改原表）

SELECT DISTINCT product_type

FROM product;

常用法则：

SQL语句中也可以使用运算表达式：

SELECT product_name,sale_price,sale_price*2 AS “sale_price*2”

FROM product;

WHERE 子句的条件表达式也可以使用计算表达式

SELECT product_name, sale_price, purchase_price

FROM product

WHERE sale_price - purchase_price >= 500;

选取 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;

1. 逻辑运算符

1. NOT运算符 --可读性较差，不建议滥用

想要表示不是除了 <>运算符之外，还有一个 NOT 运算符

NOT不能单独使用，必须和其他查询条件组合使用，如 选取销售单价不大于1000的记录：

SELECT product_name,product_type,sale_price

FROM product

WHERE NOT sale_price > 1000;  ( 等价于 WHERE sale_price <= 1000 )

1. AND运算符和OR运算符 --AND优先于OR，同时使用时 记得合理通过括号来优先处理

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

AND表示 “并且”，类似于交集；

OR表示  “或者” ，类似于并集。

练习题-第一部分

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

1. 说出对product表执行如下3条SELECT语句的返回结果

SELECT *

FROM product

WHERE purchase_price = NULL;

答：输出该表中 销售价格为空 的所有记录？？？

    应该是 WHERE purchase_price IS NULL 吧 ，原语句没有输出结果

SELECT *

FROM product

WHERE purchase_price <> NULL;

答：同理，应该为 WHERE purchase_price IS NOT NULL

SELECT *

FROM product

WHERE purchase_price > NULL;

答：同理，无结果。

1. 原章节中的SELECT语句能从product表中取出“销售单价（sale_price）比进货单价（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 - 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 - 500 >= purchase_price;

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

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

SELECT product_name,product_type ,

    sale_price * 0.9 - purchase_price  AS 'profit'

FROM product

WHERE sale_price * 0.9 - purchase_price >100 

  AND (product_type ='办公用品' 

         OR product_type ='厨房用具') ;

product_name|product_type|profit|

------------+------------+------+

打孔器         |办公用品        | 130.0|

高压锅         |厨房用具        |1120.0|

1. 对表进行聚合查询

4.1 聚合函数

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

COUNT:  计算表中的记录数（行数）

SUM: 计算表中数值列中数据的合计值

AVG: 计算表中数值列中数据的平均值

MAX: 求出表中任意列中数据的最大值

MIN: 求出表中任意列中数据的最小值

计算全部数据的行数（包括NULL）:

SELECT  COUNT(*)

from product;

Out:

COUNT(*)|

--------+

       8|

计算NULL以外数据的行数：

--（统计purchase_price列非NULL的行数）

SELECT  COUNT(purchase_price) 

from product;

Out：

COUNT(purchase_price)|

---------------------+

                    6|

计算销售单价和进货单价的合计值：

SELECT  SUM(sale_price),SUM(purchase_price) 

FROM product; 

Out：

SUM(sale_price)|SUM(purchase_price)|

---------------+-------------------+

          16780|              12210|

计算销售单价和进货单价的平均值（不统计NULL值）：

SELECT  AVG(sale_price),AVG(purchase_price) 

FROM product; 

Out：

AVG(sale_price)|AVG(purchase_price)|

---------------+-------------------+

      2097.5000|          2035.0000|

MAX和MIN也可用于非数值型数据：

SELECT  MAX(regist_date),MIN(regist_date)  

FROM product; 

Out：

MAX(regist_date)|MIN(regist_date)|

----------------+----------------+

      2009-11-11|      2008-04-28|

使用聚合函数删除重复值

计算去除重复数据后的数据行数：

即 想要计算值的种类，可以在COUNT函数中的参数中使用 DISTINCT

SELECT  COUNT(DISTINCT product_type)  

FROM product; 

Out：

COUNT(DISTINCT product_type)|

----------------------------+

                            3|

是否使用DISTINCT时的动作差异（SUM函数）：

SELECT  SUM(sale_price),SUM(DISTINCT sale_price) 

FROM product;

Out：

SUM(sale_price)|SUM(DISTINCT sale_price)|

--------------------+-----------------------------+

          16780|                  16280|

4.2 常用法则

1. COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

2.聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

3.MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。

4.想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。

5.在聚合函数的参数中使用DISTINCT，可以删除重复数据。

1. 对表进行分组

5.1 GROUP BY 语句

之前使用聚合函数都是会将整个表的数据进行处理，当你想将进行分组汇总时（即：将现有的数据按照某列来汇总统计），GROUP BY可以帮助你：

GROUP BY子句中指定的列成为聚合键或分组列

看一看是否使用GROUP BY语句的差异：

按照商品种类统计数据行数：

SELECT  product_type ,COUNT(*)  

FROM product

GROUP BY product_type ;

Out：

product_type|COUNT(*)|

------------------+-----------+

衣服          |         2|

办公用品        |       2|

厨房用具        |       4|

不包含 GROUP BY

SELECT  product_type ,COUNT(*)  

FROM product;

Out：

 报错

聚合键中包含NULL时

将进货单价（purchase_price）作为聚合键举例：

SELECT purchase_price, COUNT(*) 

FROM product

GROUP BY purchase_price ;

Out：

purchase_price|COUNT(*)|

--------------+--------+

           500|       1|

           320|       1|

          2800|       2|

          5000|       1|

              |       2|

           790|       1|

此时会讲NULL作为一组特殊数据进行处理

GROUP BY 书写位置

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

其中前三项用于筛选数据，GROUP BY 对筛选出的数据进行处理

在WHERE子句中使用GROUP BY

SELECT purchase_price, COUNT(*) 

FROM product

WHERE product_type ='衣服'

GROUP BY purchase_price ;

Out：

purchase_price|COUNT(*)|

--------------+--------+

           500|       1|

          2800|       1|

5.2常见错误

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

1. 在聚合函数的SELECT子句中写了聚合键以外的列使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。

    即 SELECT 中的列 一般 和 GROUP BY 的列 相同 

1. 在GROUP BY子句中使用列的别名（SELECT子句中可以通过AS来指定别名），但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。

3.在WHERE中使用聚合函数，这个是错误的。原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

1. 为聚合结果指定条件

1.用HAVING得到特定分组

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

1. HAVING 特点

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

数字：

SELECT product_type ,COUNT(*) 

FROM product

GROUP BY product_type 

HAVING COUNT(*) =2 ; 

Out：

product_type|COUNT(*)|

------------+--------+

衣服          |       2|

办公用品        |       2|

错误形式！！！（因为product_name 不包含在GROUP BY聚合键中）

SELECT product_type ,COUNT(*) 

FROM product

GROUP BY product_type 

HAVING product_name ='圆珠笔'；

1. 对查询结果进行排序

1. ORDER BY

SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可以使用 ORDER BY子句。

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

默认升序排列：

SELECT product_id,product_name ,sale_price ,purchase_price 

FROM product 

ORDER BY sale_price ;

Out：

product_id|product_name|sale_price|purchase_price|

----------+------------+----------+--------------+

0008      |圆珠笔         |       100|              |

0002      |打孔器         |       500|           320|

0006      |叉子          |       500|              |

0007      |擦菜板         |       880|           790|

0001      |T恤衫         |      1000|           500|

0004      |菜刀          |      3000|          2800|

0003      |运动T恤        |      4000|          2800|

0005      |高压锅         |      6800|          5000|

降序排列：

SELECT product_id,product_name ,sale_price ,purchase_price 

FROM product 

ORDER BY sale_price  DESC ;

Out：

product_id|product_name|sale_price|purchase_price|

----------+------------+----------+--------------+

0005      |高压锅         |      6800|          5000|

0003      |运动T恤        |      4000|          2800|

0004      |菜刀          |      3000|          2800|

0001      |T恤衫         |      1000|           500|

0007      |擦菜板         |       880|           790|

0002      |打孔器         |       500|           320|

0006      |叉子          |       500|              |

0008      |圆珠笔         |       100|              |

多个排序键：（优先按照sale_price进行升序排列，当sale_price相同时，再按product_id进行升序排序）

SELECT product_id,product_name ,sale_price ,purchase_price 

FROM product 

ORDER BY sale_price ,product_id ;

Out：

product_id|product_name|sale_price|purchase_price|

----------+------------+----------+--------------+

0008      |圆珠笔         |       100|              |

0002      |打孔器         |       500|           320|

0006      |叉子          |       500|              |

0007      |擦菜板         |       880|           790|

0001      |T恤衫         |      1000|           500|

0004      |菜刀          |      3000|          2800|

0003      |运动T恤        |      4000|          2800|

0005      |高压锅         |      6800|          5000|

当用于排序的列中含有NULL时，NULL会在开头或者末尾进行汇总：

SELECT product_id,product_name ,sale_price ,purchase_price 

FROM product 

ORDER BY purchase_price ;

Out：

product_id|product_name|sale_price|purchase_price|

----------+------------+----------+--------------+

0006      |叉子          |       500|              |

0008      |圆珠笔         |       100|              |

0002      |打孔器         |       500|           320|

0001      |T恤衫         |      1000|           500|

0007      |擦菜板         |       880|           790|

0003      |运动T恤        |      4000|          2800|

0004      |菜刀          |      3000|          2800|

0005      |高压锅         |      6800|          5000|

1. ORDER BY中列名可使用别名，GROUP BY不可以

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

FROM -- WHERE -- GROUP BY -- HAVING -- SELECT -- ORDER BY

1. ORDER BY 排序列中存在NULL时，指定其出现在首行或末行的方式

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

如果想要指定有NULL的行出现在首行或末行，需要特殊处理。

练习题-第二部分

1. 指出下述SELECT语句中所有的语法错误：

错误：

SELECT product_id, SUM（product_name）

FROM product 

GROUP BY product_type 

WHERE regist_date > '2009-09-01';

修改：

SELECT product_type,COUNT(*)

from product 

WHERE regist_date >'2009-09-01'

GROUP BY product_type ;

1. 请编写一条SELECT语句，求出销售单价（ sale_price 列）合计值大于进货单价（ purchase_price 列）合计值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;  

7.

SELECT * 

from product 

ORDER BY regist_date IS NOT NULL,- regist_date ,sale_price ;