参考答案:task03

3.1

-- 创建视图的语句
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
  FROM product
 WHERE sale_price >= 1000
   AND regist_date = '2009-09-20';

3.2

mysql> INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
ERROR 1423 (HY000): Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value
mysql> desc product;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id     | char(4)      | NO   | PRI | NULL    |       |
| product_name   | varchar(100) | NO   |     | NULL    |       |
| product_type   | varchar(32)  | NO   |     | NULL    |       |
| sale_price     | int          | YES  |     | NULL    |       |
| purchase_price | int          | YES  |     | NULL    |       |
| regist_date    | date         | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

解析:插入时将会报错。
视图插入数据时,原表也会插入数据,而原表数据插入时不满足约束条件,所以会报错。(因为 ViewPractice5_1 的原表有三个带有 NOT NULL 约束的字段)

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

-- 创建视图的语句
CREATE VIEW AvgPriceByType 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 avg_sale_price
 FROM product p1;

-- 确认视图内容
SELECT * FROM AvgPriceByType;

3.5

正确

3.6

SELECT 查询语句 ①:

mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器       |            320 |
| 擦菜板       |            790 |
+--------------+----------------+
2 rows in set (0.01 sec)

解析:该查询语句仅仅取出了 purchase_price 不是 500、2800、5000 的商品,而不包含 purchase_priceNULL 的商品,这是因为 谓词无法与 NULL 进行比较

SELECT 查询语句 ②:

mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
Empty set (0.00 sec)

解析:代码执行之前,你可能会认为该语句会返回和查询 ① 同样的结果,实际上它却返回了零条记录,这是因为 NOT IN 的参数中不能包含 NULL,否则,查询结果通常为空。

3.7

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;