本文学习自DATAWHALE SQL教程
SELECT & WHERE
SELECT:从表中选出必要数据
query:匹配查询/查询,通过SELECT选出必要数据的过程
SELECT "栏位名" FROM "表格名";
WHERE:选择性地提取必要数据
SELECT "栏位名"
FROM "表格名"
WHERE "条件";
例如:
SELECT sale_price
FROM product
WHERE product_name='裤子';
一些SQL规则
一、星号(*)代表全部列
SELECT * FROM <表名>;
二、SQL中可以随意使用换行符,不影响语句执行(但不可插入空行)
三、SELECT语句中可使用DISTINCT可以删除重复行
SELECT DISTINCT product_type FROM product;
四、单行注释:–;多行注释:/* */
五、选取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;
六、SQL语句可以使用AS关键字为列设定别名(用中文时需要双引号)
SELECT product_id As id,
product_name As name,
purchase_price AS "进货单价"
FROM product;
七、SQL语句可以使用算术表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
FROM product;
SELECT product_name, sale_price, purchase_price FROM product
WHERE sale_price-purchase_price >= 500;
八、可以使用AND、OR、NOT运算符
-- 通过使用括号让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');
九、注意SQL因为NULL的存在,其逻辑运算称为三值逻辑,总之就是不管AND还是OR,但凡有一个不确定就不确定。
练习题
一、编写一条SQL语句,从product(商品)表中选取出“登记日期(regist在2009年4月28日之后”的商品,查询结果要包含product name和regist_date两列。
SELECT product_name, regist_date From product
WHERE regist_date >= '2009-4-28';
说明:SQL中可以使用>, <, >=, <=来比较和选择日期,
二、请说出对product 表执行如下3条SELECT语句时的返回结果。
-- 1
SELECT *
FROM product
WHERE purchase_price = NULL;
-- 2
SELECT *
FROM product
WHERE purchase_price <> NULL;
-- 3
SELECT *
FROM product
WHERE product_name > NULL;
- 返回product表中purchase_price一列为NULL的全部列;
- 返回product表中purchase_price一列不为NULL的全部列;
- 报错,NULL不能比较;
三、请写出一条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 sale_price * 0.9 > 100 AND (product_type='办公用品' OR product_type='厨房用具');
SQL中的聚合函数
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;
一些常用法则
- COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数。
- 聚合函数会将NULL排除在外。但COUNT(*)例外,并不会排除NULL。
- MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
分组汇总与排序
GROUP BY:用于分组汇总
举例:我们知道一个商场所有店铺的销售额(用SUM求),若想了解每家店铺单独的销售额,就需要用到GROUP BY,如下
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name;
这样返回的结果就是各个店铺的总销售额。
GROUP BY的书写位置:
- SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
其中前三项用于筛选数据,GROUP BY对筛选出的数据进行处理,例如:
SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;
上述代码是在统计不同购买价格的衣服的数量,我理解该统计结果会包含NULL.
在使用聚合函数及GROUP BY子句时,经常出现的错误有:
-
在聚合函数的SELECT子句中写了聚合健以外的列
使用COUNT等聚合函数时,SELECT子句中如果出现列名,只能是GROUP BY子句中指定的列名(也就是聚合键)。
-
在GROUP BY子句中使用列的别名
SELECT子句中可以通过AS来指定别名,但在GROUP BY中不能使用别名。因为在DBMS中,SELECT子句在GROUP BY子句后执行。
-
在WHERE中使用聚合函数
原因是聚合函数的使用前提是结果集已经确定,而WHERE还处于确定结果集的过程中,所以相互矛盾会引发错误。 如果想指定条件,可以在SELECT,HAVING以及ORDER BY子句中使用聚合函数。
HAVING:对分组进行过滤
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:顺序排列结果
-- 降序排列为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会在开头或末尾进行汇总。
当使用了过多的SQL命令时,命令的执行顺序变得特别重要,SQL在使用 HAVING 子句时 SELECT 语句的顺序为:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
该顺序符合逻辑,不难理解
练习题
一、指出语法错误
SELECT product_id, SUM(product_name)
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01';
在聚合函数的SELECT子句中写了聚合健以外的列,product_type不是SELECT子句里面的列;
此外,GROUP BY和WHERE的顺序写错了。
二、请编写一条SELECT语句,求出销售单价( sale_price
列)合计值大于进货单价( purchase_price
列)合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum | sum
-------------+------+------
衣服 | 5000 | 3300
办公用品 | 600 | 320
SELECT product_type, SUM(sale_price), SUM(purchace_price)
FROM product
WHERE sale_price > purchace_price*1.5;
三、此前我们曾经使用SELECT语句选取出了product(商品)表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序,但现在已经无法记起当时如何指定的了。请根据下列执行结果,思考ORDERBY子句的内容。
答:ORDER BY regist_date;