前言
本博客为Datawhale第39期《SQL编程语言》打卡学习笔记。
开源教程地址:https://github.com/datawhalechina/wonderful-sql。
task01:初识数据库
1.1 编写一条 CREATE TABLE 语句,用来创建一个如下表所列项的 Addressbook (地址簿)表,并为 regist_no (注册编号)列设置主键约束
CREATE TABLE Addressbook
(regist_no INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10) ,
mail_address CHAR(20) ,
PRIMARY KEY (regist_no));
知识点:
建表(CREATE TABLE)语法:
CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
< 列名 2> < 数据类型 > < 该列所需约束 > ,
< 列名 3> < 数据类型 > < 该列所需约束 > ,
< 列名 4> < 数据类型 > < 该列所需约束 > ,
.
.
.
< 该表的约束 1> , < 该表的约束 2> ,……);
1.2 向 Addressbook 中加入 postal_code列
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
知识点:
表中添加列(ALTER TABLE)的语法:
ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
1.2 向 Addressbook 中加入 postal_code列
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
知识点:
表中添加列(ALTER TABLE + ADD COLUMN)的语法:
ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
表中删除列( ALTER TABLE + DROP COLUMN)的语法:
ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
1.3 删除 Addressbook 表
DROP table Addressbook;
知识点:
删表语法:
DROP TABLE < 表名 > ;
1.4 是否是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表
答:不可以,所以备份很重要。
task02:基础查询与排序
用到的表 product :
2.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';
知识点:
查表语法(最最最最基础的知识了):
SELECT <列名>, ……
FROM <表名>
WHERE <条件表达式>;
2.2 如下3条SELECT语句时的返回结果
①
SELECT * FROM product
WHERE purchase_price = NULL;
②
SELECT * FROM product
WHERE purchase_price <> NULL;
③
SELECT * FROM product
WHERE purchase_price > NULL;
知识点:
(1) SELECT 如果接星号(*)代表选择全部列;
(2) SQL语句支持四则运算和比较运算,<> 代表“和 ~ 不相等”,类似于NOT。
2.3 从 product 表中取出“销售单价(sale_price)比进货单价(purchase_price)高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示:
--第一种:
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 sale_price >= purchase_price + 500;
2.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 = '厨房用具' );
知识点:
AND 运算符优先于 OR 运算符 ,想要优先执行OR运算,需要使用 括号 。
2.5 请指出下述SELECT语句中所有的语法错误。
SELECT product_id, SUM(product_name)
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01';
错误 ①:字符型字段 product_name 不可以进行 SUM 聚合。
错误 ②:WHERE 语句应该书写在 GROUP BY 语句之前( FROM 语句之后),或者使用HAVING。
错误 ③:GROUP BY 字段(product_type)与 SELECT 字段不同(product_id)。
知识点:
(1) MAX / MIN 函数适用于文本类型和数字类型的列,而 SUM / AVG 函数仅适用于数字类型的列;
(2) SQL语句的执行顺序:FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY;
(3) 在聚合函数的SELECT子句中写了聚合键以外的列使用COUNT等聚合函数时,SELECT子句中如果出现列名,只能是GROUP BY子句中指定的列名(也就是聚合键)。
2.6 请编写一条SELECT语句,求出销售单价( sale_price 列)合计值大于进货单价( purchase_price 列)合计值1.5倍的商品种类。
SELECT product_type , SUM(sale_price) , SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_price) > 1.5*SUM(purchase_price);
知识点:
(1)HAVING子句用于对分组进行过滤,可以使用常数、聚合函数和GROUP BY中指定的列名(聚合键)。
(2)HAVING 子句必须与 GROUP BY 子句配合使用,且限定的是分组聚合结果.
2.7 请编写一条SELECT语句,输出如下所示的表。
观察数据可知,该表格是先根据regist_date排序(降序),再对同一regist_date中的purchase_price排序(升序)。
SELECT * FROM product
ORDER BY - regist_date ASC,purchase_price ASC;
知识点:
(1)ASC:升序;DESC:降序。
(2)在MySQL中,NULL 值被认为比任何 非NULL 值低,因此在降序的条件下想让它放到第一行,需要进行特殊处理,这里就是在排序字段前添加一个负号(minus)来得到反向排序(NULL位置不受影响)。
(3)由于SQL语句的执行顺序(2.5(2)),ORDER BY语句可以使用SELECT子句中定义的别名。
(4)现阶段所有关键词的书写顺序:SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY。
task03:复杂一(亿)点的查询
这节知识点比较重要,推荐把官方文档好好过一遍。
3.1 使用 product(商品)表作为参照表,创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。
- 条件 1:销售单价大于等于 1000 日元。
- 条件 2:登记日期是 2009 年 9 月 20 日。
- 条件 3:包含商品名称、销售单价和登记日期三列。
CREATE VIEW ViewPractice5_1 (product_name, sale_price,regist_date)
AS
(SELECT product_name, sale_price,regist_date
FROM product
WHERE sale_price>=1000 AND regist_date='2009-09-20');
3.2 向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果?
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
报错:Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value。
原因分析:视图归根结底还是从表派生出来的窗口,当向视图中插入数据时,同时也会向原表(product)插入数据插入数据 ,而原表存在多个字段不允许为空,所以无法插入。
3.3 请根据如下结果编写 SELECT 语句,其中 sale_price_avg 列为全部商品的平均销售单价。
SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_avg
FROM product;
第一次做的时候,本能的把 AVG(sale_price) 同另外几列放到了一起,但是执行时候会报“In aggregated query without GROUP BY…”的错误,网上其实给了一个解决方案:修改下sql_mode。只是这种写法确实不太规范,这里还是用一下子查询,将两个结果合并起来即可解决。
3.4 请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)
顺着3.3的思路写下来的:
CREATE VIEW AvgPriceByType (product_id, product_name, product_type, sale_price, sale_price_avg_type)
AS
(
SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price)
FROM product p2
WHERE p1.product_type = p2.product_type
GROUP BY p1.product_type) AS sale_price_avg_type
FROM product p1
);
自己跟自己连接,然后再进行GROUP操作,我自己确实想不到,跪了。
3.5 四则运算中含有 NULL 时(不进行特殊处理的情况下),运算结果是否必然会变为NULL ?
是的。
3.6 对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?
①
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
②
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
知识点:
(1) 谓词无法与 NULL 进行比较
(2) NOT IN 的参数中不能包含 NULL,否则,查询结果通常为空。
3.7 按照销售单价( sale_price )对 product(商品)表中的商品进行如下分类。
- 低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
- 中档商品:销售单价在1001日元以上3000日元以下(菜刀)
- 高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句:
SELECT
SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END) AS high_price
FROM product;
知识点: CASE 表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
ELSE <表达式>
END