03. 视图，子查询，函数

03. 视图，子查询，函数

一、视图

目的：

1.临时创建的一张表，方便查询，用完就丢

2.对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图

区别：视图不是表，视图是虚表，视图依赖于表

语法：

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

【例子】

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

1.创建视图

#在product表和shop_product表的基础上,通过id合并在一起
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;

2.修改视图结构

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

【例子】

alter view productsum as
	select product_type,sale_price
	from product
	where regist_date>'2009-09-01';

3.更新视图

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';
 #原表数据也更新了（不推荐）

4. 删除视图

DROP VIEW productSum;

二、子查询（套娃）

2.1 含义

出现在其他语句的select语句，称为子查询或内查询，外部的叫主查询

【示例】

select name from employees where
department_id in(
	select department_id from departments
    where location_id=1700
)

2. 分类

   按结果集的行列数不同

   • 标量子查询（一列一行）
   • 列子查询（一列多行）
   • 行子查询（一行多列）
   • 表子查询（结果集一般为多行多列）

   按子查询出现的位置

   • select 后面：标量子查询
   • from 后面：表子查询
   • where/having后面：标量子查询 √，列子查询√，行子查询
   • exists后面：表子查询

2.2 标量子查询（单行）

特点：搭配单行操作符使用 > < >= <= = <>

#谁的工资比abel高
select * from empployees
	where salary>(select salary from employees 
	where last_name='Abel');  #（）里面是abel的工资

#查询最低工资大于50号部门最低工资的部门id和其最低工资
select min(salary),departmrnt_id
from employees
group by department_id
having min(salary)>
(select min(salary) from employees where department_id=50)

2.3 多行子查询

特点：搭配多行操作符使用 in/ not in any/ some all

#返回location_id是1400或1700的部门中所有员工的姓名
select last_name from employees
where department_id in          #in
	(select distinct department_id
    from departments
    where location_id in(1400,1700)
    );

2.4 关联子查询

定义：通过一些标志将内外两层的查询连接起来起到过滤数据的目的。

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);

三、常见函数

select 函数名（实参列表）

3.1 算数函数

• select abs(): 返回绝对值。
• select mod(N,M): 返回N被M除的余数%。
• select floor(x): 返回不大于x的最大整数值。
• select ceiling(x): 返回不小于x的最小整数值。
• select round(x): 返回x四舍五入的整数。

3.2 字符串函数

• select ASCII(str): 返回字符串str的最左面字符的ASCII代码值。

  select ASCII('2');   #50
  select ASCII(2);   #50
  select ASCII('dx')  #100
  

• concat(str1,str2…): 返回来自于参数连结的字符串,如果任何参数是NULL，返回NULL。超过2个参数，一个数字参数被变换为等价的字符串形式。

  select CONCAT('My', 'S', 'QL');   #MySQL
  select CONCAT('My', NULL, 'QL');   #NULL
  select CONCAT(14.3);  #14.3
  

• length(str): 返回字符串str的长度。

• locate(substr,str): 返回子串substr在字符串str第一个出现的位置。

  select LOCATE('bar', 'foobarbar');   #4
  select LOCATE('xbar', 'foobar');  #0
  

• INSTR(str,substr): 返回子串substr在字符串str中的第一个出现的位置。

• LOWER – 小写转换

• REPLACE – 字符串的替换

• SUBSTRING – 字符串的截取

3.3 日期函数

• select CURRENT_DATE – 获取当前日期

• ​ CURRENT_TIME – 当前时间

• CURRENT_TIMESTAMP – 当前日期和时间

• EXTRACT – 截取日期元素

  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;
  +---------------------+------+-------+------+------+--------+--------+
  | now                 | year | month | day  | hour | MINute | second |
  +---------------------+------+-------+------+------+--------+--------+
  | 2020-08-08 17:34:38 | 2020 |     8 |    8 |   17 |     34 |     38 |
  +---------------------+------+-------+------+------+--------+--------+
  

3.4 转换函数

• CAST – 类型转换

  #将字符串类型转换为数值类型
  SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
  #将字符串类型转换为日期类型
  SELECT CAST('2009-12-14' AS DATE) AS date_col;
  

• COALESCE – 将NULL转换为其他值

四、谓词

定义：返回值为真值的函数：like, between, is null, is not null, in, exists

1.like谓词

用于字符串的部分一致查询。

FROM samplelike      
WHERE strcol LIKE 'ddd%';  #samplelike 表; strcol 数据; 'dddabc'
WHERE strcol LIKE '%ddd%';  #中间是ddd abcddd,abdddc,dddabc
WHERE strcol LIKE 'abc__';  #abcdd

2. between谓词

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。

SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;  #闭区间

3. IS NULL、 IS NOT NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

WHERE purchase_price IS NULL;

4. IN谓词 – OR的简便用法

多个查询条件取并集时可以选择使用or语句。

WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

WHERE purchase_price IN (320, 500, 5000);
WHERE purchase_price NOT IN (320, 500, 5000);  #不是320,500,5000 不能包含NULL

5. EXIST 谓词

可用IN，NOT IN代替。

五、CASE表达式

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

依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。

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 |
+------+---------+------+---------+