SQL学习笔记_Aliyun2

SQL学习笔记_Aliyun2

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

SQL的基础查询语句

SELECT… FROM…WHERE…

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

Tips

• 用 （*）搜索所有的列
• SELECT + DISTINCT 删除重复搜索的行
• 用 As 命名搜索列，来源的表（可以SELECT… AS… 或者FROM… AS…）

Examples

-- 搜索全部的列
SELECT * 
FROM product；

-- AS,DISTINCT一起用, 则DISTINCT代表后面每一个都是DISTINCT，而不是第一个是DISTINCT
SELECT DISTINCT product_id AS id,
	product_name   AS name,
	purchase_price AS "进货单价",
	product_type AS "产品种类"
FROM product;

算数运算符与比较运算符

• SELECT子句中可以使用常数或者表达式。
• 使用比较运算符时一定要注意不等号和等号的位置。
• 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
• 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

加 | 减 | 乘 | 除	+ | - | * | /
不等号, 大于号,等于号,大于等于号	<>，>，=，>=

逻辑运算符

NOT | OR | AND

真值表

AND 运算符：两侧的真值都为真时返回真，除此之外都返回假。

OR 运算符：两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。

NOT运算符：只是单纯的将真转换为假，将假转换为真。

P	Q	R	Q OR R	P AND (Q OR R)
1	1	1	1	1
1	1	0	1	1
1	0	1	1	1
1	0	0	0	0
0	1	1	0	0
0	1	1	0	0
0	0	1	0	0
0	0	0	0	0

Example

-- 通过使用括号让OR运算符先于AND运算符执行
# 注意or必须打括号，因为AND 运算符由于OR运算符
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

AND (带有NULL最后是NULL)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-frEeOBZx-1666764564783)(C:\Users\Daniel\AppData\Roaming\Typora\typora-user-images\image-20221026120631536.png)]

练习题1

#编写一条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4月28日之后”的商品，查询结果要包含product_name和regist_date两列。
SELECT product_name,regist_date
FROM product
WHERE regist_date>'2009-4-28'

练习题4

#请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。
#提示：销售单价打九折，可以通过saleprice列的值乘以0.9获得，利润可以通过该值减去purchase_price列的值获得。
SELECT product_name,product_type,(saleprice*0.9-purchase_price) as profit
FROM product
WHERE profit>100
AND (product_type = '办公用品' OR product_type = '厨房用具')

对表进行聚合

• COUNT：计算表中的记录数（行数）
• SUM：计算表中数值列中数据的合计值
• AVG：计算表中数值列中数据的平均值
• MAX：求出表中任意列中数据的最大值
• MIN：求出表中任意列中数据的最小值

Tips

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

Example

-- 计算全部数据的行数（包含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;

对表进行分组与限制

语句执行顺序 FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

语句的书写顺序 SELECT ->FROM ->WHERE->GROUP BY ->HAVING

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

Example

SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

Tips使用GROUP BY常见错误

1. 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
2. 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
3. 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING以及ORDER BY子句中使用聚合函数。

HAVING 条件限制

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

故而在GROUP BY 语句后面使用HAVING 语句

-- 以下为典型错误！！（因为HAVING 限制的attribute并不是GROUP BY 的attribute）
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔';

-- 本句中错在GROUP BY出现在WHERE之前
SELECT product_id, SUM（product_name）
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

ORDER BY 限制条件

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

• 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总
• GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却可以使用别名，因为SELECT 先于ORDER BY 执行而晚于GROUP BY 语句执行，因为在ORDER BY 使用别名的时候已经知道了SELECT设置的别名存在

练习6

--  请编写一条SELECT语句，求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

SELECT product_type,SUM(sale_pricce),SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_pricce)>1.5*SUM(purchase_price);
#这里应该先用GROUP BY聚合再用HAVING筛选出每个分类总值满足条件的，如果先用WHERE筛选应该出来的一个值把因为他把表里所有行都SUM了

练习7

--  观察可以看到regist date是按照时间有新到旧排列的(DESC)，并且同一个时间里，销售价格是由少到增的(ASCD)
SELECT *
FROM product
ORDER BY regist_date DESC, sale_price