练习题-第一部分
3.1
创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。
- 条件 1:销售单价大于等于 1000 日元。
- 条件 2:登记日期是 2009 年 9 月 20 日。
- 条件 3:包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
SELECT * FROM ViewPractice5_1;
执行结果
product_name | sale_price | regist_date
--------------+------------+------------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20
答:
CREATE VIEW ViewPractice5_1 (
product_name, sale_price, regist_date
) AS
SELECT
product_name,sale_price,regist_date
FROM
product
WHERE regist_date = '2009-09-20' AND sale_price >= 1000;
3.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据,会得到什么样的结果呢?
INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
答:SQL 执行会引发报错,试图插入数据,原表也会插入,但是原表还有三个NOT NULL
约束的字段,所以引发table doesn't have a default value
错误。
3.3
请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000
答:SELECT product_id, product_name, product_type, sale_price, (SELECT avg( sale_price ) FROM product) as sale_price_all FROM product;
3.4
请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)。
product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
提示:其中的关键是 avg_sale_price 列。与习题三不同,这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说,该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
答:
CREATE view AvgPriceByType(
product_id,product_name,product_type,sale_price,sale_price_all
) as
SELECT
p.product_id,p.product_name,p.product_type,p.sale_price,avgpt.tasp AS sale_price_all
FROM
product as p RIGHT JOIN (
SELECT product_type, AVG( sale_price ) AS tasp FROM product GROUP BY product_type
) AS avgpt ON p.product_type = avgpt.product_type;
SQL 函数
算数函数
- ABS – 绝对值
语法:ABS( 数值 )
ABS 函数用于计算一个数字的绝对值,表示一个数到原点的距离。
当 ABS 函数的参数为NULL时,返回值也是NULL。
- MOD – 求余数
语法:MOD( 被除数,除数 )
MOD 是计算除法余数(求余)的函数,是 modulo 的缩写。小数没有余数的概念,只能对整数列求余数。
注意:主流的 DBMS 都支持 MOD 函数,只有SQL Server 不支持该函数,其使用%符号来计算余数。
- ROUND – 四舍五入
语法:ROUND( 对象数值,保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意:当参数 保留小数的位数 为变量时,可能会遇到错误,请谨慎使用变量。
字符串函数
- CONCAT – 拼接
语法:CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。
- LENGTH – 字符串长度
语法:LENGTH( 字符串 )
- LOWER – 小写转换
LOWER 函数只能针对英文字母使用,它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合,不影响原本就是小写的字符。
类似的, UPPER 函数用于大写转换。
- REPLACE – 字符串的替换
语法:REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )
- SUBSTRING – 字符串的截取
语法:SUBSTRING (对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算,索引值起始为1。
- (扩展内容)SUBSTRING_INDEX – 字符串按索引截取
语法:SUBSTRING_INDEX (原始字符串, 分隔符,n)
该函数用来获取原始字符串按照分隔符分割后,第 n 个分隔符之前(或之后)的子字符串,支持正向和反向索引,索引起始值分别为 1 和 -1。
日期函数
不同DBMS的日期函数语法各有不同,本课程介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可。
- CURRENT_DATE – 获取当前日期
- CURRENT_TIME – 当前时间
- CURRENT_TIMESTAMP – 当前日期和时间
- EXTRACT – 截取日期元素
语法:EXTRACT(日期元素 FROM 日期)
使用 EXTRACT 函数可以截取出日期数据中的一部分,例如“年”
“月”,或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
转换函数
“转换”这个词的含义非常广泛,在 SQL 中主要有两层意思:一是数据类型的转换,简称为类型转换,在英语中称为cast;另一层意思是值的转换。
- CAST – 类型转换
语法:
CAST(转换前的值 AS 想要转换的数据类型)
- COALESCE – 将NULL转换为其他值
语法:
COALESCE(数据1,数据2,数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的,因此可以根据需要无限增加。
在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
练习题-第二部分
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);
答:
SELECT product_name, purchase_price FROM product WHERE purchase_price NOT IN (500, 2800, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器 | 320 |
| 擦菜板 | 790 |
+--------------+----------------+
SELECT product_name, purchase_price FROM product WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
结果为空
3.7
按照销售单价( sale_price)对练习 6.1 中的 product(商品)表中的商品进行如下分类。
- 低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
- 中档商品:销售单价在1001日元以上3000日元以下(菜刀)
- 高档商品:销售单价在3001日元以上(运动T恤、高压锅
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
答:
SELECT
SUM(
CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END
) as low_price,
SUM(
CASE WHEN sale_price > 1000 AND sale_price <= 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;