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 | +------+---------+------+---------+
-
练习截图