文章目录
1 视图
1.1 知识点总结
1.2 语句
--创建视图
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
--修改视图
ALTER VIEW <视图名> AS <SELECT语句>
--更新视图
UPDATE 视图名
SET 更新后的值
WHERE 选择更新的列;
--只能修改透过窗口能看到的内容,创建视图时尽量使用限制不允许通过视图来修改表。
--删除视图
DROP VIEW 视图名;
2 子查询
2.1 知识点总结
2 语句
--嵌套子查询 (product例子)
SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type,
COUNT(*) AS cnt_product
FROM product
GROUP BY product_type) AS productsum
WHERE cnt_product = 4) AS productsum2;
--标量子查询
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
--或者
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
--关联子查询
SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
PS:对于一个SQL语句来说都不应该设计的层数非常深且特别复杂,不仅可读性差而且执行效率也难以保证,所以尽量有简洁的语句来完成需要的功能。
关联查询那块还是有点晕,先存下教程关联查询执行过程
3 练习题-第一部分
3.1
创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。
条件 1:销售单价大于等于 1000 日元。
条件 2:登记日期是 2009 年 9 月 20 日。
条件 3:包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
--创建视图
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.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
会报错。因为原表中的product_id 不允许为空。
3.3
请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
SELECT product_id, product_name, product_type, sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;
3.4
请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
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
4 各种各样的函数
4.1 知识点总结
4.2 语句
4.2.1 数值函数
--ABS绝对值(参数为NULL时,返回值也是NULL)
ABS(数值)
--MOD余数,只有整数列求余
--主流 DBMS 都支持 MOD 函数,只有SQL Server 不支持,其使用%符号求余。
MOD(被除数,除数)
--ROUND四舍五入
ROUND(对象数值)
4.2.2 字符串函数
--CONCAT 拼接
CONCAT(str1, str2, str3)
--LENGTH字符串长度
LENGTH( 字符串 )
--LOWER 小写转换,只能针对英文字母使用
LOWER(字符串)
--REPLACE字符串替换
REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
--SUBSTRING 字符串的截取
SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
--截取的起始位置从字符串最左侧开始计算,索引值起始为1。
4.2.3 日期函数
--CURRENT_DATE获取当前日期
SELECT CURRENT_DATE;
--CURRENT_TIME – 当前时间
SELECT CURRENT_TIME;
--CURRENT_TIMESTAMP – 当前日期和时间
SELECT CURRENT_TIMESTAMP;
--EXTRACT – 截取日期元素
--截取出日期数据中的一部分
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
4.2.4 转换函数
--CAST 类型转换
CAST(转换前的值 AS 想要转换的数据类型)
--COALESCE将NULL转换为其他值
COALESCE(数据1,数据2,数据3……)
5 谓词
5.1知识点总结
5.2 语句
--like
--前方一致:作为查询条件的字符串与查询对象字符串起始部分相同
SELECT *
FROM 表名
WHERE strcol LIKE '一致的字符串部分%';
--中间一致:查询对象字符串中含有作为查询条件的字符串
--字符串出现在对象字符串的最后或中间都可以
SELECT *
FROM 表名
WHERE strcol LIKE '%一致的字符串部分%';
--后方一致:作为查询条件的字符串与查询对象字符串的末尾部分相同。
SELECT *
FROM 表名
WHERE strcol LIKE '%一致的字符串部分';
--_下划线匹配任意 1 个字符
--使用 _(下划线)代替 %,它代表“任意 1 个字符”。
SELECT *
FROM 表名
WHERE strcol LIKE 'abc__';
--BETWEEN(闭区间),开区间只能用大于/小于
SELECT 列1, 列2
FROM product
WHERE 列 BETWEEN 条件1 AND 条件2;
--NULL/NOT NULL
SELECT 列1, 列2
FROM 表名
WHERE 列 IS NULL/NOT NULL;
--IN(OR的简便用法)
SELECT 列1, 列2
FROM 表名
WHERE列 IN (条件1, 条件2, 条件3);(满足/不满足其一即可)
--不满足改成NOT IN即可
--使用子查询作为IN谓词的参数
-- DDL :创建一张新表shopproduct显示出哪些商店销售的商品
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
( shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id) -- 指定主键
);
-- DML :插入数据
START TRANSACTION; -- 开始事务
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT; -- 提交事务
SELECT * FROM shopproduct;
--假设我么需要取出大阪在售商品的销售单价
--第一步,取出大阪门店的在售商品 `product_id ;
SELECT product_id
FROM shopproduct
WHERE shop_id = '000C';
--第二步,取出大阪门店在售商品的销售单价 `sale_price
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '000C');
--NOT IN和子查询
-- NOT IN 使用子查询作为参数,取出未在大阪门店销售的商品的销售单价
SELECT product_name, sale_price
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '000A');
--EXISTS:存在这样的记录就返回真(TRUE),不存在就返回假(FALSE)
--上面的表,用 EXIST 选取出大阪门店在售商品的销售单价
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT *
FROM shopproduct AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p.product_id);
--EXIST 是只有 1 个参数的谓词,左侧没有参数,右侧有一个,通常是子查询
--EXIST 是只有 1 个参数的谓词
--EXIST 只关心记录是否存在,返回哪些列都没有关系
EXIST 的子查询中用 SELECT * 就行
--NOT EXIST 与 EXIST 相反,当“不存在”满足子查询中指定条件的记录时返回真(TRUE)
6 CASE表达式
6.1知识点总结
6.2 语句
ASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
--ELSE 子句可以省略,此时默认为 ELSE NULL
-- CASE 表达式最后的“END”不能省略
--应用1:根据不同分支得到不同结果
SELECT product_name,
CASE WHEN product_type = '衣服' THEN CONCAT('A : ',product_type)
WHEN product_type = '办公用品' THEN CONCAT('B : ',product_type)
WHEN product_type = '厨房用具' THEN CONCAT('C : ',product_type)
ELSE NULL
END AS abc_product_type
FROM product;
--实现列方向上的聚合
SELECT product_type,
SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;
--列方向不同种类聚合值
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
FROM product;
--当待转换列为数字时,可以使用SUM AVG MAX MIN等聚合函数;
--当待转换列为文本时,可以使用MAX MIN等聚合函数
7 练习题-第二部分
7.1
运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)
是的
7.2
对本章中使用的 product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?
运行结果1:返回purchase_price结果不为500,2800,5000,且不为NULL 的行。
运行结果2:返回为空。就是上题提到的运算或函数中含有 NULL 时,结果全都会变为NULL 。筛选含/不含NULL的项,用IS NULL或IS NOT NULL.
7.3
按照销售单价( sale_price)对 product(商品)表中的商品进行如下分类。
-
低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
-
中档商品:销售单价在1001日元以上3000日元以下(菜刀)
-
高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
执行结果
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;