sql打卡记录三 复杂一点的查询

目录

一、视图

1、视图的含义

2、视图的优势

3、创建视图

4、修改视图

5、删除视图

二、子查询

1、子查询的含义

2、子查询与视图

3、嵌套子查询

4、标量子查询

5、关联子查询

三、函数

1、算数函数

2、字符串函数

3、日期函数

4、转换函数

四、谓词

1、谓词的含义

2、LIKE谓词

3、BETWEEN谓词

4、IS NULL、IS NO TNULL谓词

5、IN谓词

6、使用子查询作为IN谓词的参数

7、EXIST谓词

五、CASE表达式

1、CASE表达式含义

---

一、视图

1、视图的含义

简单来说，视图是通过过select语句查询出来的数据表，可以进行查询相关的操作，一般是对原表中的数据无法修改，但是有些情况下可以。表存的是实际数据，视图存的是查询语句。不过视图虽然不是真是的表，但是我们依旧可以在视图的基础上在创建视图。

2、视图的优势

1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
2. 通过定义视图可以使用户看到的数据更加清晰。
3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
4. 通过定义视图可以降低数据的冗余。

3、创建视图

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

注意：一般的DBMS中定义视图时不能使用ORDER BY语句，但是MySQL中是可以使用的。 

4、修改视图

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

视图是原表的一个窗口，对视图的更新也是对原表的更新，因此必须要符合原表定义的才能修改成功。

例如：

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

我通过该条语句，修改原有视图，得到一下结果：

我再使用：

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

修改视图的内容，将办公用品的价格由100改为5000，此时我们查看视图数据是：

原表数据是：

根据数据能看出来，对于办公用品价格，原表只修改“圆珠笔”价格，因为视图中查询出的是“圆珠笔”的价格。

以上例子可以看出，视图只能修改通过他查询出来的信息。

5、删除视图

DROP VIEW <视图名1> [ , <视图名2> …]

二、子查询

1、子查询的含义

所谓子查询就是嵌套查询，就像是嵌套循环一样，在查询语句中包含查询语句。子查询会作为外层的查询条件，而这个子查询是一个完整的查询，可以单独拿出来作为一个查询语句。

2、子查询与视图

子查询就是将视图中的select语句放到from后面，但是子查询是一次性的，语句执行之后就消失了，不像是视图可以保存下来

3、嵌套子查询

子查询并没有具体限制，可以无限嵌套下去。但是嵌套查询就像是嵌套循环，层次过多会导致效率降低，因此要慎用

4、标量子查询

标量子查询就是返回值只能有一个值，因此在写sql时，需要确定到某一行某一列。根据其特点也能知道，一般是用于select或者where条件后面，作为输出的某一个字段或者某一个查询条件

这种查询恰巧我在最近写试图中经常使用，例如根据职能是否有总结记录，判断该职能是否提交，然后展示在主表的视图中，很好用

5、关联子查询

关联子查询就是，子查询语句中，需要和外层语句中某些信息关联起来获取对应的信息，例如最近工作中使用的，主表的id关联总结表，查询该id下是否总结

三、函数

1、算数函数

用来计算数值

（1）ABS函数（绝对值）         ABS(数值）

（2）MOD函数（求余）           MOD(被除数，除数)

（3）ROUND函数（四舍五入）ROUND(数据，小数位数)

2、字符串函数

（1）LENGTH函数（字符串长度）           LENGTH(字符串)

（2）REPLAC函数（字符串替换）           REPLAC(对象字符串，替换前字符串，替换后字符串)

（3）SUBSTRING函数（字符串截取）           SUBSTRING(字符串 FROM 起始位置 FOR 字符数)

（4）LOWER函数（大小写替换）

3、日期函数

（1）CURRENT_DATE  获取当前日期

（2）CURRENT_TIME  获取当前时间

（3）CURRENT_TIMESTAMP  获取当前日期和时间

（4）EXTRACT  截取日期元素   例：EXTRACT(YEAR FROM CURRENT_TIMESTAMP) as year

4、转换函数

（1）CAST 类型转换                                      CAST(转换前的值 as 转换后的数据类型)

（2）COALESCE  将NULL转为其他值          COALESCE(数据1，数据2...)

四、谓词

1、谓词的含义

谓词就是返回值为真值的函数。主要的谓词有：LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS

2、LIKE谓词

用于部分查询，也就是我们常说的模糊查询，根据某个字段的部分信息获取所需要的数据。

例如：

要获取开头为ddd的信息，则在where条件后：字段名 LIKE 'ddd%'

要获取结尾为ddd的信息，则在where条件后：字段名 LIKE '%ddd'

要获取包含ddd的信息，则在where条件后：字段名 LIKE '%ddd%'

也可以用'_'(下划线)替换'%'，'_'代表任意一个字符

3、BETWEEN谓词

用于范围查询，查询某个字段数值范围内的数据

4、IS NULL、IS NO TNULL谓词

查询为null、不为null的数据，不能用'='

5、IN谓词

IN谓词与OR含义类似，指的是查询IN内的所有数据，可用OR代替

6、使用子查询作为IN谓词的参数

IN谓词和NOT IN谓词，都可以将子查询作为条件，也就是说可以将视图作为查询条件，需要注意的是，IN后面是某一个字段的不同值，因此子查询的select语句只能查询一个字段信息

7、EXIST谓词

EXIST谓词是判断是否存在满足某种条件的记录，如果存在就返回真，不存在就是返回假

五、CASE表达式

1、CASE表达式含义

该表达式就是我们所说的条件分支

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

符合WHEN 后面，就执行后面的THEN，不符合就执行ELSE