第三章 待整理

1 视图

• 视图与表的区别：“是否保存了实际的数据”
• “视图不是表，视图是虚表，视图依赖于表”。
  

1.1 创建视图

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

-- 基于多表的创建视图，不用join
-- create的列名可省
CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;

-- 基于上述视图的查询
SELECT sale_price, shop_name
  FROM view_shop_product
 WHERE product_type = '衣服';

• ELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列
• 视图的列名是在视图名称之后的列表中定义
• 视图名在数据库中需要是唯一的，不能与其他视图和表重名
• 视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图，但对多数 DBMS 来说多重视图会降低 SQL 的性能
• 在一般的DBMS中定义视图时不能使用ORDER BY语句，因为视图和表一样，数据行都是没有顺序的
• 在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略

1.2 修改视图结构

ALTER VIEW <视图名> AS <SELECT语句>

-- exam1
ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

1.3 更新视图内容

-- 上述修改后的productsum不含以上限制
UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

• 结果中只有一条数据被更新，因为视图只是原表的一个窗口，所以它只能修改透过窗口能看到的内容
• 不推荐用这种方式修改原表

1.4 删除视图

DROP VIEW <视图名1> [ , <视图名2> …]

-- exam
DROP VIEW productSum;

2 子查询

2.1 嵌套子查询

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;

• AS后可接子查询得到的表名
• 子查询是一次性的，不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了
• 随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用

2.2 标量子查询

• 标量子查询：执行的select语句只能返回一个值，也就是要返回表中具体的某一行的某一列
• 标量子查询在任何可以使用单一值的位置都可以使用

-- 多了一个avg_price列，值都一样
SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

2.3 关联子查询

• 意味着查询与子查询之间存在着联系
• 下例中将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询

-- 查出每个类别中，sale价格高于对应类别均价的物品
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);

• 由sql执行顺序可知：
  • 首先from得到待筛选的数据
  • 执行子查询，其中p1.producty_type是父查询当前数据所对应的type

3 函数

3.1 算术函数

example：abs、mod、round

-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);

-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务

-- 查询表内容
SELECT * FROM samplemath;
+----------+------+------+
| m        | n    | p    |
+----------+------+------+
|  500.000 |    0 | NULL |
| -180.000 |    0 | NULL |
|     NULL | NULL | NULL |
|     NULL |    7 |    3 |
|     NULL |    5 |    2 |
|     NULL |    4 | NULL |
|    8.000 | NULL |    3 |
|    2.270 |    1 | NULL |
|    5.555 |    2 | NULL |
|     NULL |    1 | NULL |
|    8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)

3.2 字符串函数

example：concat、length、lower、upper、replace、substring、substring_index

-- DDL ：创建表
USE  shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',	'月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM samplestr;
+-----------+------+------+
| str1      | str2 | str3 |
+-----------+------+------+
| opx       | rt   | NULL |
| abc       | def  | NULL |
| 太阳      | 月亮 | 火星 |
| aaa       | NULL | NULL |
| NULL      | xyz  | NULL |
| @!#$%     | NULL | NULL |
| ABC       | NULL | NULL |
| aBC       | NULL | NULL |
| abc哈哈   | abc  | ABC  |
| abcdefabc | abc  | ABC  |
| micmic    | i    | I    |
+-----------+------+------+
11 rows in set (0.00 sec)

3.3 日期函数

介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可

-- 获取当前日期
SELECT CURRENT_DATE;

-- 获取当前时间
SELECT CURRENT_TIME;

-- 当前日期和时间
SELECT CURRENT_TIMESTAMP;

-- 截取日期元素
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;

3.4 转换函数

CAST(转换器的值/列 AS 想要转换的数据类型)
COALESCE(数据1，数据2，数据3……)