学习笔记TASK3

视图：

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

2.视图与表的区别在于“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。

3.创建视图

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

其中SELECT 语句需要书写在 AS 关键字之后。

注：需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。

数据行都是没有顺序的。在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

4.修改视图结构

修改视图结构的基本语法如下：

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

5.更新视图

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

DISTINCT 关键字。

GROUP BY 子句。

HAVING 子句。

UNION 或 UNION ALL 运算符。

FROM 子句中包含多个表。

更新视图

update productsum

   set sale_price = '5000'

 where product_type = '办公用品';

6.删除视图

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

子查询：

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。

标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询。

由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的

关联子查询与子查询的联系:

1.首先执行不带WHERE的主查询

2.根据主查询讯结果匹配product_type，获取子查询结果

3.将子查询结果再与主查询结合执行完整的SQL语句

4.在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。

练习题part1：

1.

create view ViewPractice5_1 as

SELECT

product_name,

sale_price,

regist_date

FROM

product

where

sale_price>=1000

and

regist_date='2009-09-20';

select * from ViewPractice5_1;

2.报错

3.

select

product_id,

product_name,

product_type,

sale_price,

(select avg(sale_price) from product) as sale_price_all

from

product

ORDER BY

product_id;

4.

create view AvgPriceByType as

select

product_id,

product_name,

product_type,

sale_price,

(select avg(sale_price)

from product as p2

where p1.product_type = p2.product_type

GROUP BY p1.product_type ) as avg_sale_price

from

product as p1;

函数：

1.算数函数

ABS -- 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

当 ABS 函数的参数为NULL时，返回值也是NULL。

MOD -- 求余数

语法：MOD( 被除数，除数 )

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

ROUND -- 四舍五入

语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。

注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

2.字符串函数

CONCAT -- 拼接

语法：CONCAT(str1, str2, str3)

MySQL中使用 CONCAT 函数进行拼接。

LENGTH -- 字符串长度

语法：LENGTH( 字符串 )

LOWER -- 小写转换

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

类似的， UPPER 函数用于大写转换。

REPLACE -- 字符串的替换

语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING -- 字符串的截取

语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

3.转换函数

CAST -- 类型转换

语法：CAST（转换前的值 AS 想要转换的数据类型）

COALESCE -- 将NULL转换为其他值

语法：COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。

谓词：

1.谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

谓词主要有以下几个：

LIKE

BETWEEN

IS NULL、IS NOT NULL

IN

EXISTS

2.当需要进行字符串的部分一致查询时需要使用该谓词。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

前方一致：选取出“dddabc”

中间一致：选取出“abcddd”,“dddabc”,“abdddc”

后方一致：选取出“abcddd“

综合如上三种类型的查询可以看出，查询条件最宽松，也就是能够取得最多记录的是中间一致。这是因为它同时包含前方一致和后方一致的查询结果。

3.BETWEEN谓词 -- 用于范围查询

S NULL、 IS NOT NULL -- 用于判断是否为NULL

4.使用子查询作为IN谓词的参数时：

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。

5.NOT IN和子查询

NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

6.EXIST 谓词

谓词的作用就是 “判断是否存在满足某种条件的记录”。

如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

注：就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。

CASE表达式：

CASE表达式的语法分为简单CASE表达式（重点）和搜索CASE表达式两种。

注：执行语法时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 无论多么庞大的 CASE 表达式，最后也只会返回一个值。

练习题part2：

1.错。OR函数两边只要有一方为真，则返回真，真 or null 返回 真，假 or null 返回 null

2.返回售价不为500，2800，5000的商品 ，不包含售价为NULL的商品

返回零条

3.select

count(case when sale_price <= 1000 then sale_price else null end) as low_price,

count(case when sale_price BETWEEN 1001 and 3000 then sale_price else null end) as mid_price,

count(case when sale_price >= 3001 then sale_price else null end) as high_price

from product;