目录
学习计划
主要教程：Docs (feishu.cn)
0、环境搭建
1、认识数据库和SQL
1.1 基础语法
1.2 基础操作
-- 创建本课程用到的数据库
CREATE DATABASE shop;
-- 创建本课程用到的商品表
CREATE TABLE product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
-- 本课程用到的数据SQL
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
-- 更新数据
-- 修改所有的注册时间
UPDATE product
SET regist_date = '2009-10-10';
-- 仅修改部分商品的单价
UPDATE product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
-- 将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL
UPDATE product
SET regist_date = NULL
WHERE product_id = '0008';
-- 将多列作为更新对象，合并后的写法
UPDATE product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
-- DML ：本课程插入数据
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
-- 创建视图
CREATE VIEW ViewPractice5_1
AS
SELECT product_name, sale_price,regist_date
FROM product
WHERE regist_date = '2009-9-20'AND sale_price >= 1000;1.3 实践
【课后题（10.11）】
-- 1.1 创建表
CREATE DATABASE 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) );
-- 1.2 添加一列
ALTER TABLE Addressbook ADD COLUMN pstal_code CHAR(8) NOT NULL;
-- 1.3 填空题:编写 SQL 语句来删除 Addressbook 表
DROP TABLE Addressbook;
-- 1.4 判断题：不可以编写 SQL 语句来恢复删除掉的 Addressbook 表2、SELECT有关的语句
本次学习了与SELECT有关的语句和语法，包括：
1）从表中查询/选取/删除数据；
SELECT <列名>，<列名>
FROM <表名>
WHERE <条件表达式>;
-- 星号（*）代表所有列
SELECT *
FROM <表名>;
-- AS为列设定别名（用中文时需要双引号（“”））。
SELECT product_id AS id,
product_name AS name,
purchase_price AS "进货单价"
FROM product;
-- 使用DISTINCT删除product_type列中重复的数据
SELECT DISTINCT product_type
FROM product;2）算术/逻辑运算符：+-*/ NULL NOT AND OR
3）真值表：涉及到NOT AND OR运算会用到
4）聚合函数：汇总查询：SUM AVG MAX MIN COUNT
-- 汇总商品种类
SELECT COUNT(DISTINCT product_type)
FROM product;5）分组：GROUP BY…HAVING…
6）排序：ORDER BY
*执行顺序：FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY
*存在NULL的情况
【作业】
-- 2.1
SELECT product_name, regist_date
FROM product
WHERE regist_date>'2009-4-28'
-- 2.2 执行结果：均为空，因为NULL的的真值既不是“真”也不是“假”，而是第三种情况“不确定”
-- 2.3 两种写法
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
-- 2.4
SELECT product_name, product_type, (sale_price*0.9-purchase_price) AS profit
FROM product
WHERE profit>100 AND (product_type='办公用品'OR'厨房用具');
-- 2.5 语句错误1）SUM只适用于数值型；2）GROUP BY 应在WHERE后面
SELECT product_id, SUM(product_name)
-- 2.6
SELECT product_type, SUM(sale_price) AS sum, SUM(purchase_price) AS sum
FROM product
WHERE (SUM(sale_price)-1.5*SUM(purchase_price))>=0
-- 2.7 ORDER BY 观察到regist_date降序排列；但是regist_date的第一行是空值，相同日期的按销售价格升序排列
SELECT *
FROM product
ORDER BY IF(ISNULL(regist_date),0,1), regist_date DESC, sale_price;
3、复杂查询
3.1 提出视图得概念--创建视图--多表视图-在视图得基础上进行查询/修改/更新/删除
注意点：1视图上创建视图；2定义视图时不能用order by语句；3视图的修改时基于原表的
3.2 子查询
嵌套子查询--标量子查询--关联子查询
3.3 各种函数
算数函数--字符串函数--日期函数--转换函数--聚合函数
3.4 谓词
返回值为真值的函数：LIKE/BETWEEN/IS NULL/IS NOT NULL/IN/EXISTS
3.5 CASE表达式
【作业】
-- 3.1 创建满足条件的视图
--创建视图
CREATE VIEW ViewPractice5_1
AS
SELECT product_name, sale_price,regist_date
FROM product
WHERE regist_date = '2009-9-20'AND sale_price >= 1000;
--执行题中的语句
SELECT *
FROM ViewPractice5_1
-- 3.3 计算所有商品均价
SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;
-- 3.4 计算各类商品均价，考虑使用关联子查询
SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type=p2.product_type
GROUP BY product_type) AS sale_price_all
FROM product AS P1
-- 3.6
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);
-- 3.7
SELECT
SUM(CASE WHEN sale_price BETWEEN 0 AND 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>3000 THEN 1 ELSE 0 END) AS high_price
FROM product;
3.1 创建视图
3.2 向ViewPractice5_1插入一条数据，会报错，因为原表中的product_id 不允许为空。
3.3 计算所有商品均价
3.4 计算各类商品均价
3.5 Yes
3.6 ②的结果为空，原因是上题提到的运算或函数中含有 NULL 时，结果全都会变为NULL 。
3.7 代码及结果如下：
4、集合计算
4.1 表的加减法
用集合的概念来理解就是并、交、补、差集
UNION&OR INTERSECT&AND EXCEPT&NOT
4.2 内连结
来自群友分析的图帮助理解
【作业】
4.1找出 product 和 product2 中售价高于 500 的商品的基本信息。
4.2 借助对称差的实现方式, 求product和product2的交集。
4.3 每类商品中售价最高的商品都在哪些商店有售 ？
4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。
4.5 用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高
CREATE TABLE product2
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
-- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),
('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 4.1找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT *
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;
-- 4.2 借助对称差的实现方式, 求product和product2的交集。
SELECT *
FROM product
-- product和product2 的并集
WHERE product_id IN (SELECT product_id FROM product
UNION SELECT product_id FROM product2)
-- 减去A-B和B-A的并集
AND product_id NOT IN( SELECT product_id FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION SELECT product_id FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product));
-- 4.3 每类商品中售价最高的商品都在哪些商店有售 ？
-- 4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price,p2.max_sale_price
FROM product AS p1
INNER JOIN
(SELECT product_type, MAX(sale_price) AS max_sale_price
FROM product
GROUP BY product_type) AS p2
ON p1.product_type=p2.product_type
AND p1.sale_price=p2.max_sale_price;
-- 关联子查询
SELECT product_type,product_name,sale_price
FROM product AS p1
WHERE sale_price = (SELECT MAX(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
-- 4.5 用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高5、SQL高级处理
【作业】
5.1请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。
5.2继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）
5.3思考题
① 窗口函数不指定PARTITION BY的效果是什么？
② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。
回答：1）会将当前表格看成一个大窗口进行计算。
2）窗口函数一般对where或者group by子句处理后的数据进行操作
5.4使用存储过程创建20个与 shop.product 表结构相同的表
-- 5.1请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product;
-- 5.2计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）
SELECT product_name
,product_id,sale_price,regist_date
,sum(sale_price) OVER (PARTITION BY regist_date)
FROM product;
6、决战秋招
练习一：各部门工资最高的员工
创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
创建Department 表，包含公司所有部门的信息。
编写一个 SQL 查询，找出每个部门工资最高的员工
-- 创建Employee表
CREATE TABLE Employee
( Id varchar(4) NOT NULL ,
Name varchar(15) NOT NULL,
Salary integer NOT NULL,
DepartmentId varchar(4) NOT NULL,
primary key (Id)
);
insert into Employee
values
('1', 'Joe', 70000, '1'),
('2', 'Henry', 80000, '2'),
('3', 'Sam', 60000, '2'),
('4', 'Max', 90000, '1');
-- 创建department表
CREATE TABLE Department (
Id VARCHAR(1) NOT NULL,
Name VARCHAR(225) NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO Department VALUES
('1','IT'),
('2','Sales');
-- 找出每个部门工资最高的员工
SELECT Department.Name AS Department,
Employee.Name AS Employee,
Employee.Salary AS Salary
FROM Employee
INNER JOIN Department
ON Employee.DepartmentId =Department.Id
WHERE Employee.Salary IN
(SELECT MAX(Employee.Salary) FROM Employee
GROUP BY DepartmentId)
ORDER BY Employee.Salary DESC
练习二: 换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。
CREATE TABLE seat (
Id VARCHAR(4) NOT NULL,
Student VARCHAR(15) NOT NULL,
PRIMARY KEY(Id)
);
INSERT INTO seat VALUES
('1','Abbot'),
('2','Doris'),
('3','Emerson'),
('4','Green'),
('5','Jeames');
SELECT
(CASE id % 2
WHEN 0 THEN id - 1
WHEN 1 AND id != (SELECT MAX(id) FROM seat) THEN id + 1
ELSE id END) AS id, student
FROM seat
ORDER BY id;
练习三: 分数排名（难度：中等）
假设在某次期末考试中，二年级四个班的平均成绩分别是 93、93、93、91。根据查询结果书写出查询
CREATE TABLE score_avg (
class VARCHAR(4) NOT NULL,
score_avg VARCHAR(15) NOT NULL,
PRIMARY KEY(class)
);
INSERT INTO score_avg VALUES
('1','93'),
('2','93'),
('3','93'),
('4','91');
SELECT score_avg, (ORDER BY score_avg DESC) AS RANK
FROM score_avg;
练习四：连续出现的数字（难度：中等）
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
练习五：树节点 （难度：中等）
对于tree表，id是树节点的标识，p_id是其父节点的id。
写一条查询语句打印节点id及对应的节点类型。按照节点id排序。
练习六：至少有五名直接下属的经理 （难度：中等）
Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。针对Employee表，写一条SQL语句找出有5个下属的主管。
练习七：查询回答率最高的问题 （难度：中等）
求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。
uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。
写一条sql语句找出回答率（show 出现次数 / answer 出现次数）最高的 question_id。
练习八：各部门前3高工资的员工（难度：中等）
将练习一中的 employee 表清空，重新插入数据。编写一个 SQL 查询，找出每个部门工资前三高的员工。
练习九：平面上最近距离 (难度: 困难）
point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。
写一条查询语句求出这些点中的最短距离并保留2位小数。
练习十：行程和用户（难度：困难）
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
458
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
