My_SQL天池训练打卡3_视图是存储数据库中的查询的sql语句,字段取别名可以隐藏真实字段名,加强了安-CSDN博客

本文链接：https://blog.csdn.net/sailhang/article/details/125094019

MySQL_Task_3

1.视图

视图是存储在数据库中的查询的sql 语句本质是查询语句的结果，存入一个临时空间
是一个虚拟表，其内容由查询定义视图在数据库中没有原本的物理存储，只是相当于临时表
是一个虚拟表，其内容由查询定义

视图在数据库中没有原本的物理存储，只是相当于临时表
简单化，数据所见即所得
安全性，用户只能查询或修改他们所能见到得到的数据逻辑独立性，可以屏蔽真实表结构变化带来的影响

2.视图的作用

1：提高重用性需要频繁查询的复杂语句，使用视图可以相当于将语句存为临时表，调用时，只需要从视图的位置查询即可
2：对外接口稳定当数据库底层表发生改变时，原本的表可能不存在，使用视图则不存在此问题
3：提高安全性只开放特定字段给外部接口

3.视图和表的区别

1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，试图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。
9、不能对视图进行update或者insert into操作。

4.视图和表的联系

视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

5.创建视图

 create view productsum (product_type, product_name)
 as
 select product_type, count(*)
 from product
 group by product_type ; 
 #SELECT 语句中列的排列顺序和视图中列的排列顺序相同
 #注意在一般的DBMS中定义视图时不能使用order by语句

多表示图

 create view_shop(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;

修改视图

 alter view view_shop #视图名
 as
 select shop_id,sale_price,purchase_price #选择显示列名
 from product,shop_product               #选择表
 where purchase_price > 2000             #限制条件

更新视图
- 限制
  - 因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
  - 聚合函数 sum()、min()、max()、count() 等。
  - distinct关键字。
  - group by子句。
  - having子句。
  - union
  - from 子句中包含多个表。
- 例子
- ```
 update view_shop
 set sale_price = 5000
 where purchase_price = 2800
```
删除视图
- ```
 drop view view_shop
```

6.子查询

什么是子查询
- 子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
子查询和视图的关系
- 子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。概括来说，就是一张一次性视图。

嵌套子查询

 select product_type,cnt_product
 from (select *
      from (select product_type,count(*) 
            as cnt_product
           from product
           group by product_type) as view_shop
           where cnt_type = 4) as view_shop2
           #嵌套会增加查询时间 减少这样使用

标准子查询

 select product_id,product_name,sale_price
 from product
 where sale_price > (select avg(sale_price) from product)
 #首先执行select avg from 这条语句 然后在执行括号外面的语句

关联子查询

 #选取出 各商品种类中 高于 该商品种类的平均销售单价 的商品
 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)
  /*这里的p1.product_type = p2.product_type 是为了比较商品a和商品a的平均销售单价
  没有这句的话 那么商品就会和所有商品的平均售价单价相比*/

7.函数

函数分类

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）

算数函数
- 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 – 字符串的截取
- 日期函数
  - CURRENT_DATE – 获取当前日期
  - CURRENT_TIME – 当前时间
  - CURRENT_TIMESTAMP – 当前日期和时间
  - EXTRACT – 截取日期元素
    
    语法：EXTRACT(日期元素 FROM 日期)
    
    使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”
    
    “月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
- 谓词
  - LIKE
  - BETWEEN
  - IS NULL、IS NOT NULL
  - IN
  - EXISTS
- CASE表达式
- 语法
```
CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END 
```
  例:
```
-- CASE WHEN 实现数字列 score 行转列
SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+
```