MySQL 学习3

一、视图

视图是一个虚拟的表,不同于直接操作数据表,视图是依据select语句来创建的,所以操作视图时会根据创建视图的select语句生成一张虚拟表,然后在这张虚拟表上做SQL操作

1. 视图和数据表的区别
   是否保存了真实的数据视图,数据库真实存储的数据表,它可以看作是一个窗口,通过这个窗口我们可以看到数据库表中真实存在的数据
   视图不是表，视图是虚表，视图依赖于表
2. 视图的优点
   通过定义视图降低数据的冗杂
   通过定义视图可以将频繁使用的select语句保存来提高效率
   通过定义视图可以使用户看到的数据更加清晰
   通过定义视图可以不公开对外的数据表全部字段,增强数据的保密性
3. 视图的创建

create view <视图名称>(<列名1>,<列名2>)  as <select语句>

select 语句中列的排列顺序和视图中列的排列顺序相同,而且视图的列名是在视图名称之后的列表中定义的,需要注意的是视图名在数据库中需要是唯一的,不能与其他视图和表重名
注意:不能用order by语句,因为视图是无序的
基于单表的视图

CREATE VIEW productsum1 (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

基于多表的视图

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;

4. 修改表视图的结构
   就和sql语句筛选差不多但是多了一行
   alter view <视图名>

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

5. 更新视图的内容
   当包含下列任意一种是都是不能被更新的
   聚合函数 sum() min() max() count() 等。
   distinct 关键字
   group by 子句
   having 子句
   union 或 union all 运算符
   from 子句中包含多个表
   视图是从表派生出来的,因此,如果原表可以更新,那么 视图中的数据也可以更新
   反之亦然,如果视图发生了改变,而原表没有进行相应更新的话,就无法保证数据的一致性了
   更新用update <视图名> set <列名>=设定值

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

注意：这里虽然修改成功了,但是并不推荐这种使用方式,而且我们在创建视图时也尽量使用限制不允许通过视图来修改表,因为这里有限制条件为‘办公用品’所以只会修改‘办公产品’的那一行或几行

6. 删除视图
   drop view <视图名1>

DROP VIEW productSum;

二、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询,在select子句中先计算子查询,使用括号括起来的sql语句先执行,再去执行外边的sql语句

SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt
          FROM students_info
          GROUP BY stu_age) AS studentSum;

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;

2. 标量子查询
   所谓标量就是单一,只能有一个返回值,就是一行的某一列

#查询出销售单价高于平均销售单价的商品
SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

由于标量子查询的特性,导致标量子查询不仅仅局限于 where 子句中,通常任何可以使用单一值的位置都可以使用,也就是说, 能够使用常数或者列名的地方,无论是 select ,group, having ,order by 子句,几乎所有的地方都可以使用

3. 关联子查询

#选取出各商品种类中高于该商品种类的平均销售单价的商品
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);

三、函数

1. 算数函数(用来进行数值计算的函数)
   abs绝对值：计算绝对值,函数参数为null时返回值也是null
   mod(除数,被除数)：是求余函数,只能对整数求余
   round(对象的数值,保留的小数位数)：函数来进行四舍五入操作

ABS(m)
MOD(n, p)
ROUND(m,1)

2. 字符串函数
   concat(str1,str2,str3)：拼接
   length(字符串)：返回字符串长度
   lower：这个函数只能针对英文字母使用,会将参数中的大写转换成小写
   upper：类似lower,将参数中的小写转换成大写
   replace(对象字符串,替换前的字符串,替换后的字符串)：将字符串替换
   substring(对象字符串,from截取的起始位置for截取的字符数)：可以截取出字符串中的一部分字符串.截取的起始位置从字符串最左侧开始计算.索引值起始为1
   
   substring_index(原始字符串,分隔符,n)：用来获取原始字符串按照分隔符分割后,第 n 个分隔符之前(或之后)的子字符串,支持正向和反向索引,索引起始值分别为 1 和 -1

SUBSTRING_INDEX('www.mysql.com', '.', 2) 
 www.mysql

3. 日期函数
   current_date获取当前日期

current_date
2021-2-2

current_time获取当前时间

current_time
15:39:20

current_timestamp获取当前时间和日期

current_timestamp
2021-2-2 15:39:20

extract(日期元素 from 日期)：截取日期元素,使用 extract 函数可以截取出日期数据中的一部分,例如“年”,“月”,或者“小时”“秒”等,该函数的返回值并不是日期类型而是数值类型

EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year
2021

4. 转换函数分为两种：一种是数据类型的转换,另一种是数据值的转换
   cast(转换前的值 as 转换后的值)----类型转换

select cast('001' as signed integer) as int_col    转换成数字类型
1
select CAST('2009-12-14' AS DATE) AS date_col	   转换成日期类型
2009-12-14

coalesce(数据1,数据2,数据3)：回可变参数 A 中左侧开始第 1个不是null的值

四、谓词

谓词就是返回值为真值的函数

1. like----用于字符串的部分一致查询
   % 是代表“零个或多个任意字符串”的特殊符号
   **_**是代表一个任意字符串的特殊符号

select *
from table1
where year like '199%' and age like '2_'

2. between and用于范围查询
   between会包含临界值,不想要临界值就用 < >

select *
from table1
where age between 12 and 23      ---12 16 17 23

select *
from table1
where age>12 and age<23          ---16 17

3. is null和not is null表示存在和不存在

select *
from table1
where project is null 

4. in和not in 取特定值时,数据较多时比between简便
   多个查询条件取并集时可以选择使用or语句

select *
from table1
where age=12 or age=23

select *
from table1
where age in(12,23)

5. 使用子查询作为in的参数

---取出天津在售商品的售价
---首先要去筛选出天津在售商品
select product_id
from product
where how='on'    ---01 02 05 06 07
---再去筛选他们的售价
select product__id,sale
from product
where product_id in (select product_id
						from product
						where how='on')    
	---01:12 02:45 05:32 06:65 07:42

6. exit(存在)：判断是否存在满足某种条件的记录
   如果存在这样的记录就返回真(true),如果不存在就返回假(false)
   注意exit前边不需要列名,直接用就可以

select product__id,sale
from product
where exit (select product_id
						from product
						where how='on')   

五、case表达式

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
ELSE <表达式>
END  

依次判断 when 表达式是否为真值,是则执行then后的语句,如果所有的when表达式均为假,则执行else后的语句,无论多么庞大的 CASE 表达式,最后也只会返回一个值

1. 根据不同分支得到不同列值

SELECT  product_name,
        CASE WHEN product_type = '衣服' THEN CONCAT('A ：',product_type)
             WHEN product_type = '办公用品'  THEN CONCAT('B ：',product_type)
             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)
             ELSE NULL
        END 
        AS abc_product_type
  FROM  product;

else子句也可以省略不写,这时会被默认为 ELSE NULL

2. 实现列方向上的聚合
   按照销售单价（ sale_price）对product（商品）表中的商品进行如下分类
   低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
   中档商品：销售单价在1001日元以上3000日元以下（菜刀）
   高档商品：销售单价在3001日元以上（运动T恤、高压锅）

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 >= 3001 then 1 else 0 end) as high_price
from product;