MySQL了--- 复杂查询

1. 视图

   1）视图

    	视图是一个虚拟的表，不同于直接操作数据表
    	视图是依据select语句创建的，会生成一张虚拟表
    	然后再表上做操作
   

   2）视图与表的区别

    	主要区别---是否保存了实际的数据
    	表中存储真实数据
    	视图 可看作一个窗口，显示表中的真实数据是一张虚拟的表
   

   3）视图的用途

    	显示数据信息
    	提高频繁使用的select 语句保存效率
    	降低数据冗余
    	不公开数据全部字段，增强保密性
   

   4）创建视图

    	CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
    	
    	注：DBMS 中定义视图时不能使用 ORDER BY 语句
    	
    	MySQL 中视图的定义允许使用 GROUP BY语句
    	但是若从特定视图选择，而该视图使用自己的 ORDER BY 语句
    	则该试图定义中的 GROUP BY 将被忽略
   

CREATE VIEW productsum (pro_type, cnt_product)
AS 
SELECT pro_type, COUNT(*)
FROM product
GROUP BY pro_type;


-- 在product 和 shop_product 基础上 创建 视图 

CREATE VIEW view_shop_product (pro_type, sale_price, shop_name)
AS 
SELECT  pro_type, sale_price, shop_name 
FROM product,
shop_product
WHERE product.pro_id = shop_product.product_id;

5）修改视图
		
		ALTER VIEW <视图名> AS <SELECT语句>
		视图名在数据库是唯一的
		
6）更新视图内容

		对于视图而言，包含以下结构 不可被更新
			聚合函数
			DISTINCT关键字
			GROUP BY 子句
			HAVING子句
			UNION子句
			UNION 或 UNION ALL运算符
			FROM 子句包含多个表
		更新视图
			UPDATE productsum
			SET sale_price = '5000'
			WHERE product_type = '办公用品';

ALTER VIEW productsum 
AS 
SELECT pro_type, sale_price
FROM product
WHERE regist_date > '2009-09-11';

7）删除视图
		
		DROP VIEW <视图名1> [ , <视图名2> …]
		删除需要权限

DROP VIEW productsum;

2. 子查询

   demo:

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

() 里的语句先执行， 执行成功后再执行 外面的 语句

1）子查询
	一个查询语句嵌套在另一个查询语句内部的查询寻
2）子查询和视图的关系
	子查询将 用来定义 视图的 SELECT 语句直接用于 from
	子句
	
	子查询是一次性的
	
3）嵌套子查询
	
	层层嵌套，效率不高
	
4）标量子查询
	也叫单一子查询
	单一子查询：要求执行的 SQL 语句 只能返回一个值
	即返回表中具体的某一行某一列
5）标量子查询的作用
	对于特定的需求 处理方便
	如:
	查询出注册日期最晚的那个商品
	查询出销售单价高于平均销售单价的商品
	SELECT pro_id, pro_name, sale_price
	FROM product
	WHERE sale_price > (SELECT AVG(sale_price) 
	FROM product);
	
	标量子查询不局限于 WHERE 子句中， SELECT, GROUP BY
	, HAVING, ORDER BY 都可
	
6）关联子查询
	关联子查询： 查询与子查询之间存在联系
	
	关联子查询与子查询的联系
	demo：

SELECT pro_type, pro_name, sale_price
		FROM product AS p1
		WHERE sale_price > (SELECT AVG(sale_price) 
		FROM product AS p2 WHERE p1.pro_type = p2.pro_type
		GROUP BY pro_type);

	首先执行 WHERE 之前主查询的 语句
	根据主查询结果 得到 子查询的结果
	结合子查询 与 主查询的结果 执行完整的结果

4. 函数

   1）算术函数

    ABS(X) 绝对值
    MOD(N,M) 取模求余数
    ROUND(X) 四舍五入
   

SELECT m, ABS(m) AS mod_abs,
n, p, MOD(n, p) AS mod_val, 
ROUND(m, 1) AS rnd_val
FROM sampleMath;

2）字符串函数

	CONCAT(str1,str2,...)  拼接
	LENGTH(str)  字符串长度
	LOWER(str)  小写转化
	REPLACE(str,from_str,to_str) 
	SUBSTRING(str FROM pos FOR len) 字符串截取
	SUBSTRING(str,pos,len) 字符串按索引截取
	REPEAT(str,count) 字符串按需重复多次

SELECT * FROM samplestr;

SELECT str1, str2, str3,
CONCAT(str1, str2, str3) as con_str,
LENGTH(str1) AS length_str,
LOWER(str1) AS low_str,
REPLACE(str1,str2,str3) AS rep_str,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM samplestr;

-- 按分隔符截取， 
-- 按 . 截取 两个子串
SELECT SUBSTRING_INDEX('www.mysql.com','.',2);
-- 按 . 逆序截取 两个子串
SELECT SUBSTRING_INDEX('www.mysql.com','.',-2);
-- 拆分截取 单个元素
SELECT SUBSTRING_INDEX('www.mysql.com','.',1);
-- 二次拆分
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com','.',2),'.',-1);

3）日期函数

	CURRENT_DATE 获取当前日期
	CURRENT_TIME  获取当前时间
	CURRENT_TIMESTAMP 获取当前日期和时间
	EXTRACT(unit FROM date) 截取日期元素

-- 当前日期
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;

4）转换函数

	CAST(expr AS type) 类型转换
	COALESCE(value,...) 将NULL 转换为 其他值

SELECT CAST('001' as SIGNED INTEGER) AS int_col;
SELECT CAST('2009-12-14' AS DATE) AS date;
-- 将NULL 转换为其他类型
SELECT COALESCE(NULL,11) AS col_11,
COALESCE(NULL,'hello_world', NULL) AS col_22,
COALESCE(NULL,NULL, '2020-12-10') AS col_33;

5）聚合函数

5. 谓词

   谓词：返回值为真值的函数

   1)LIKE 字符串的部分一致查询寻

-- 前方一致，选取 'dddac'
SELECT * FROM samplelike
WHERE strcol 
LIKE 'ddd%';
-- 中间一致， '%ddd%'
SELECT * FROM samplelike
WHERE strcol 
LIKE '%ddd%';
-- 后方一致， '%ddd'
SELECT * FROM samplelike
WHERE strcol 
LIKE '%ddd';
-- _ 匹配任一一个字符
SELECT * FROM samplelike
WHERE strcol 
LIKE 'abc__';

2)BETWEEN 范围查询

	包含端点值， 是闭区间
	不包含端点值，使用<>

SELECT pro_name, sale_price
FROM product
WHERE sale_price < 1000 AND sale_price > 100;

SELECT pro_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

-- 不包含端点值
SELECT pro_name, sale_price
FROM product
WHERE sale_price < 1000 AND sale_price > 100;

-- IS NULL, IS NOT NULL
SELECT pro_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

SELECT pro_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

3）IS NULL ， IS NOT NULL 判断是否为空

SELECT pro_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

SELECT pro_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

4）IN， OR简便用法

	多个条件查询取并集使用 OR
	IN 范围查询， 增强可读性
	IN , NOT IN 都无法取出 NULL数据

-- 多个条件
SELECT pro_name, purchase_price
FROM product
WHERE purchase_price = 450
OR purchase_price = 550
OR purchase_price = 2850;
-- IN
SELECT pro_name, purchase_price
FROM product
WHERE purchase_price 
IN (450, 550);
SELECT pro_name, purchase_price
FROM product
WHERE purchase_price 
NOT IN (450, 550);

5）子查询使用IN 做参数

	IN 和子查询
	使用子查询而不使用 IN：
	IN 需要经常更新sql 语句降低效率，提高了维护成本
	使用子查询可以保持SQL语句不变，提了程序可维护性
	
	NOT IN 同样也支持子查询作为参数

- 子查询作为 IN 的参数
-- 第一步, 子查询

SELECT product_id
FROM shop_product
WHERE shop_id = '000C';

-- 第二步
SELECT pro_name, sale_price
FROM product
WHERE pro_id IN (SELECT product_id
FROM shop_product
WHERE shop_id = '000C');

6）EXISTS 

	作用：判断是否存在满足某种条件的记录
	EXISTS 谓词的主语 是记录
	只有一个参数，往往是 一个子查询， 通常是关联子查询
	
	EXIST只关心记录是否存在，因此返回哪些列都没有关系
	SELECT 后可以跟常数
	一般可以写 SELECT *

SELECT pro_name, sale_price
FROM product AS p1
WHERE EXISTS (SELECT * 
FROM shop_product AS sp
WHERE sp.shop_id = '000c'
AND sp.product_id = p1.pro_id);

--  EXIST 只关心记录是否存在，因此返回哪些列都没有关系
-- SELECT 后可以跟常数
-- 一般习惯写 SELECT *
SELECT pro_name, sale_price
FROM product AS p1
WHERE EXISTS (SELECT 1
FROM shop_product AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p1.pro_id);

SELECT pro_name, sale_price
FROM product AS p1
WHERE EXISTS (SELECT *
FROM shop_product AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p1.pro_id);

6. CASE 表达式

   1. CASE表达式：
      CASE 同常称为 条件分支
      分为 CASE 表达式 和 搜索CASE表达式
      CASE WHEN <求值表达式> THEN <表达式>
      WHEN <求值表达式> THEN <表达式>
      WHEN <求值表达式> THEN <表达式>
      .
      .
      .
      ELSE <表达式>
      END

   2. CASE 表达式的使用

SELECT pro_name,
CASE WHEN pro_type = '衣服' THEN
		CONCAT('A: ', pro_type)
	WHEN pro_type = '办公用具' THEN
		CONCAT('B: ', pro_type)
	WHEN pro_type = '厨房用具' THEN
		CONCAT('C: ', pro_type)
	ELSE
		NULL
END AS abc_pro_type
FROM product;

SELECT pro_type, SUM(sale_price) AS sum_prcie
FROM product
GROUP BY pro_type;