SQL编程-组队学习打卡task02-基础查询与排序

SELECt 语句基础

select 列名 as xx
from 表名
where 条件表达式

注意：
在 SELECt 语句中使用 DISTINCT 可以删除重复行
设定别名为中文时要用 “ ”

运算符

算数运算符：=, <>, >=, >, <=, <, 对null 要用 is null 或 is not null
逻辑运算符：not, and, or, （and优先级比or高，必要时对or用括号

聚合函数

select 聚合函数(列名)
常用：count, sum, avg, max, min

-- COUNT(*) 会得到包含 NULL 的数据行数，而 COUNT(<列名>) 会得到不包含 NULL 的数据行数。
select count(*) from product;
select count(purchase_price) from product;

-- MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。
select sum(sale_price),sum(purchase_price) from product;
select avg(sale_price),avg(purchase_price) from product;
select max(regist_date),min(regist_date) from product;

-- distinct 用于去重。
select count(distinct product_type)from product;
select sum(sale_price), sum(distinct sale_price) from product;# 后者只对不同的价格相加，所以少了500

group by 对表进行分组

select 列名
from 表名
where 条件表达式
group by 列名

-- 按照商品种类统计数据行数
select product_type,count(*)
from product
group by product_type;


select product_type,count(*)
from product;


--  GROUP BY 子句中指定的列称为 聚合键 或者 分组列。null也可以作为聚合键。

--  GROUP BY 书写位置 1. SELECt 2. FROM 3. WHERe 4. GROUP BY
select purchase_price
from product
where product_type='厨房用具'
group by purchase_price ;

having 过滤分组

-- WHERe 子句只能指定记录（行）的条件,having 可以实现组的过滤。如 group by 分了3组，用 having 取出其中两组。 
select product_type,count(*),sum(sale_price)
from product
group by product_type;


select product_type,count(*),sum(sale_price)
from product
group by product_type
having count(*)=2;

order by 对查询结果排序

注意：
可以设置多个排序键。排序列名有 NULL 时， NULL 会在开头或末尾进行汇总。 group by 不能使用 select 定义的别名，
order by 可以使用。

-- order by 对查询结果进行排序 .默认为升序排列，降序排列为 DESC
select * from product order by sale_price;

select * from product order by sale_price desc;

记住：SQL 在使用 HAVINg 子句时 SELECt 语句的顺序为： FROM, WHERe, GROUP BY, HAVINg, SELECt, ORDER BY。 练习题 3.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';

3.2

select *
from product
where purchase_price = NULL; # 无

select *
from product
where purchase_price <> NULL; # 无

select *
from product
where purchase_price is NULL; # 正确语法应为 is null

select *
from product
where product_name > NULL; # 无

3.3 从 product 表中取出“销售单价（sale_price）比进货单价（purchase_price）高出 500 日元以上”的商品。

select product_name, sale_price,purchase_price
from product
where sale_price -purchase_price  >= 500;

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

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 ='厨房用具');

3.5

错误：
1.where在group by前面
2.不能对varchar进行sum，但是这里没报错，log里说
Truncated incorrect DOUBLE value: ‘打孔器’
Truncated incorrect DOUBLE value: ‘菜刀’
Truncated incorrect DOUBLE value: ‘叉子’
Truncated incorrect DOUBLE value: ‘圆珠笔’
应该使用 select sum(cast(列名as 你想要的数据类型)) 。
参考 mysql - varchar类型与数字的比较和转换
PS：除了语法错误，对product_name 进行sum本就没有意义（狗头

select product_id, sum(product_name)
from product
where regist_date >'2009-09-01'
group by product_type;

3.6 请编写一条 SELECt 语句，求出销售单价（‘sale_price‘ 列）合计值大于进货单价（‘purchase_price‘ 列）合计值 1.5 倍的商品种类。

select product_type,sum(sale_price) as 'sum',sum(purchase_price) as 'sum'
from product
group by product_type
having sum(sale_price) > sum(purchase_price)*1.5;

3.7

select * 
from product
order by regist_date is not null,regist_date desc,sale_price;