【SQL笔记】Task2

SQL Task2

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

-- 一、SELECT语句基础
SELECT product_id     AS id,
       product_name   AS name,
       purchase_price AS "进货单价" #中文别名""，字符串、日期时间类型变量''
  FROM product;
	
SELECT DISTINCT product_type FROM product; #去重

-- 二、算术运算符和比较运算符
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;
 
SELECT product_name，purchase_price
  FROM product
WHERE purchase_price IS NOT NULL; #取非空 is null取空

-- 三、逻辑运算符
-- 3.1 NOT运算符
SELECT product_name, product_type, sale_price
  FROM product
WHERE NOT sale_price >= 1000;
-- 3.2 AND运算符和OR运算符
#“商品种类为办公用品”并且“登记日期是2009年9月11日或者2009年9月20日”
select * from product 
where product_type='办公用品' 
and (regist_date='2009-09-11' or regist_date='2009-09-20'); 
-- 3.3 真值(只有SQL中的逻辑运算被称为三值逻辑:TRUE FALSE NULL)

-- 练习题-第一部分
-- 练习题1
select product_name, regist_date 
	from product
where regist_date>='2009-04-28';
-- 练习题2--空值参与运算，结果为空！
SELECT *
  FROM product
 WHERE purchase_price = NULL;
 
SELECT *
  FROM product
 WHERE purchase_price <> NULL;
 
SELECT *
  FROM product
 WHERE product_name > NULL;
-- 练习题3：从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品
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;
-- 练习题4:从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。
-- 查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）
select product_name, product_type, sale_price*0.9 as profit
from product
where product_type in ('办公用品', '厨房用具')
and sale_price*0.9 - purchase_price>100;

-- 四、对表进行聚合查询
-- 4.1 聚合函数
select count(1),count(*),count(product_id),count(regist_date) 
from product; #计算全部数据行数（使用字段时注意空值）：8 8 8 7

select sum(sale_price), sum(purchase_price) from product; #合计

select avg(sale_price), avg(purchase_price) from product; #平均值

select max(sale_price), min(regist_date) from product; #max min也可用于非数值型

-- 4.2 使用聚合函数删除重复值
-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM product;
 
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product;

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

-- 五、对表进行分组
-- 5.1 GROUP BY语句
select product_type, count(*)
from product
group by product_type; #按照商品种类统计数据行数

-- 5.2 聚合键中包含NULL时--NULL作为一组特殊数据进行处理
SELECT purchase_price, COUNT(*)
FROM product
GROUP BY purchase_price;

-- 5.3 在WHERE子句中使用GROUP BY
SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;

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

-- 六、为聚合结果指定条件
-- 6.1 用HAVING得到特定分组
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
-- 6.2 HAVING特点:HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）

-- 七、对查询结果进行排序
-- 7.1 ORDER BY
-- 降序排列desc 升序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;

-- 7.2 ORDER BY中列名可使用别名
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

-- 练习题-第二部分
-- 练习题5
SELECT product_id, SUM（product_name）
FROM product 
GROUP BY product_type 
WHERE regist_date > '2009-09-01'; #错误：select字段非聚合字段

-- 练习题6：求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值1.5倍的商品种类
select product_type, sum(sale_price) as sum1, sum(purchase_price) as sum2 
from product
group by product_type
having sum1>sum2*1.5; 

-- 练习题7
select * 
from product 
order by regist_date desc, sale_price;