阿里天池龙珠计划SQL训练营Task02笔记

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为https://tianchi.aliyun.com/specials/promotion/aicampsqlhttps://tianchi.aliyun.com/specials/promotion/aicampsql?accounttraceid=4e19dfae8cea4dc38558fe0c392b659dtkkuSELECT

通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询（query）

SELECT <列名> FROM <表名>;

SELECT 语句通过WHERE子句来指定查询数据的条件。在WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件

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

想要查询出全部列时，可以使用代表所有列的星号（*）

SELECT * FROM <表名>;

SQL语句可以使用AS关键字为列设定别名，用中文时需要双引号“”

SELECT product_id     AS id,
       product_name   AS name,
       purchase_price AS "进货单价"
FROM product;

使用DISTINCT可以删除重复行

SELECT DISTINCT product_type FROM product;

注释分为1行注释"-- "和多行注释两种"/* */"

--一行注释
/*
多行
注释
*/

算术运算符和比较运算符

基本加减乘除

含义	运算符
加法	+
减法	-
乘法	*
除法	/

 比较

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

例 运算表达式  可以在select后也可以在where后

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;

 例  选取NULL记录或非NULL记录

SELECT product_name，purchase_price FROM product WHERE purchase_price IS NULL;

SELECT product_name，purchase_price FROM product WHERE purchase_price IS NOT NULL;

逻辑运算符

NOT

想要表示“不是……”时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT

-- 选取出销售单价大于等于1000日元的记录
-- 两句意思相等
SELECT product_name, product_type, sale_price FROM product WHERE sale_price >= 1000;

SELECT product_name, product_type, sale_price FROM product WHERE NOT sale_price <= 1000;

AND运算符和OR运算符

AND 相当于“并且”，类似数学中的取交集。

OR 相当于“或者”，类似数学中的取并集。

SELECT * FROM account_info WHERE total_amount >296800 AND is_delete = 1
SELECT * FROM account_info WHERE total_amount >296800 OR company_id = 1105

括号优先处理

“商品种类为办公用品”并且“登记日期是 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');

真值表仅用于语句复杂时数理逻辑关系使用

 含NULL真值表

 第一部分练习题答案

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

略

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

SELECT produce_name,product_type,(sale_price * 0.9) - purchase_price AS profit 
FROM product 
WHERE (sale_price *0.9) - purchase_price > 100
AND (profuct_type = '办公用品' OR product_type = '厨房用具')

聚合函数

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

注：

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

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

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

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

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

分组 GROUP BY

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

例：

SELECT COUNT(*),company_id FROM taskinfo GROUP BY company_id

表 

结果 

 

注:

聚合键中包含NULL时会将NULL作为一组特殊数据进行处理

SQL书写顺序为SELECT ..FROM ..WHERE ..GROUPT BY ..

前三个为筛选数据，group by用于分组数据

用HAVING得到特定分组

如果查出三组数据而我只想要两组该怎么办

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

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

排序 ORDER BY

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

默认为升序排列ASC，降序排列为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;

第二部分练习题答案

字符型字段不可以使用SUM

WHERE应该写在GROUP BY 之前

GROUP BY 字段与SELECT 字段不一致

SELECT product_type,SUM(sale_price),SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_price) > SUM(purchase_price) * 1.5

SELECT *
FROM product
ORDER BY regist_date DESC,sale_price ASC