task3复杂一点的查询

视图是一个虚拟表,不可以直接操作得用SQL语句进行创建,在虚拟表上进行SQL操作。
视图不是真实储存的表,但是可以通过视图看见数据库真实的数据,其数据来源于真实表的基础上,视图不是表但是依赖于表。
视图的作用:通过视图频繁使用SQL语句保存提高效率。是用户看到的更清晰,不对外公开数据增加数据的保密性,可以降低数据的冗余
如何创建:create view <视图名称><列名> as <select 语句>
注意:select的书写在as 关键字后面,其顺序和视图的排序一样,视图名在数据库需要唯一,不能同名。我们不仅可以在表上面创建视图也可以在视图的基础上创建视图。但是要避免这种操作因为对于多数DBMS来说会降低SQL性能。
在一般DBMS中定义视图不能使用order by语句,因为数据行没有顺序。从特定视图进行选择有自己的orderby语句会直接忽略视图定义中orderby语句。
在product表上创建视图:
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;
查询视图内容和之前的一样:
SELECT sale_price, shop_name
FROM view_shop_product
WHERE product_type = ‘衣服’;
修改视图结构:alter view <视图名> as <select语句>
视图是虚拟表,所以修改时只有满足基础的表的定义才能成功修改。
聚合函数(sum,mim, max, count,等)
distinct关键字 groupby,having,union或uiconall,from字句中的多个表不可以被更新。更新时原表更新则视图也会被更新,但是视图被更新如果表不更新可能会造成数据不一致。
更新视图语句:updateproductsum
set sale_price = ‘500’
where product_type = ‘办公用品’;
删除视图:drop view <视图1>[视图名2]
有相应的权限才能删除成功
例如:drop view productsum;
在操作会显示不存在内容
子查询:
子查询指一个查询语句嵌套在另一个查询语句内部的查询,查询基于一个或多个表。
可以直接用于from句子中,子查询是一次性的,在执行后就消失了。
虽然嵌套子查询可以查询出结果,但是随着子查询嵌套的层数的叠加,SQL语句不仅会难以理解而且执行效率也会很差,所以要尽量避免这样的使用。
标量子查询是指单一的查询,我们执行的SQL语句只能返回一个值,会返回SQL中的某一行某一列
标量查询可以单一的找出我们所需要的值
关联子查询意味着与子查询存在一定的关系,是指将通过一些标志将内外两层的查询连接起来达到过滤数据的目的。过程简要概括为:
1.首先执行不带WHERE的主查询
2.根据主查询讯结果匹配product_type,获取子查询结果
3.将子查询结果再与主查询结合执行完整的SQL语句
小结:在子查询中像标量子查询,嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。
3.3各种函数
函数分为:算术函数 (数值计算的函数)
字符串函数 (用来进行字符串操作的函数)
日期函数 (用来进行日期操作的函数)
转换函数 (用来转换数据类型和值的函数)
聚合函数 (用来进行数据聚合的函数)
常用函数有30-50个,总共两百多个
算术函数:
DDL:创建函数
DML:插入函数
ABS:绝对值函数,语法abs(数值),当ABS函数参数是null返回值也是null,abs是计算一个数的绝对值表示一个数到原点的距离。
MOD函数:语法:MOD(被除数,除数)
MOD是计算求余的函数,只能除整数,SQL不支持该函数使用%进行计算主流的DBMS都支持。
round四舍五入操作,语法round(对象数值,保留小数位数)
参数 保留小数的位数 为变量时,可能会遇到错误,请谨慎使用变量
字符串函数
concat 拼接,语法:concat(str1,str2)mysql使用该函数进行拼接
length 字符串长度
语法:length(字符串)
lower 小写转换 只针对英文字母,不适用英文字母以外的场合,不影响原本就是小写的字符
upper大写转换
replace 字符串的替换 语法:replace(对象字符串,替换前的字符串,替换后的字符串)
substring 字符串的截取,索引值起始1
substring index 字符串按索引截取
语法:SUBSTRING_INDEX (原始字符串, 分隔符,n)
支持正向和反向索引起始值是1和-1
获取第1个元素比较容易,获取第2个元素/第n个元素可以采用二次拆分的写法
日期函数:
current_date 获取当前日期select+
current_time 当前时间select+
current_timestamp 当前日期和时间select+
extract 截取日期元素select+
语法:EXTRACT(日期元素 FROM 日期),返回的是数值型而不是日期型
转换函数:在SQL中有两种意思:数据类型的转换在英语中称cast,另一个是值的转换,
cast类型转换 语法:cast(转换前的值 as 想要转换的值)
coalesce 将null转换为其他值 语法:coalesce(数据)这个函数是SQL特有的函数,函数返回可变参数从左侧开始第一个不是null的值,参数可变可以无限增加。
在SQL中将null转换为其他值时i用到转换函数。
谓词:谓词就是返回值为真值的函数,包括true、false、unknown
谓词业主要有:like,between,is null is not null, in ,exists
like用于字符串的部分一致查询,部分一致大体分为前方一致,中间一致,后方一致三种类型。例如:SELECT *
FROM samplelike
WHERE strcol LIKE ‘ddd%’;
可以表示0个或多个任意字符串的特殊符号 使用_表示任意一个字符可以代替
between代表范围查询,与其他函数不同的是使用了三个参数
如果不想包含临界值就必须使用<>。
is null ,is not null 用于判断是否为null
了选取出某些值为 NULL 的列的数据,不能使用 =,而只能使用特定的谓词IS NULL,想要选取除null以外的数据使用 is not null
in谓词是or的简便用法
多个查询条件取并集可以使用or语句
SQL语句中可以使用in谓词来替换,使语句更加简便
使用子查询作为in谓词的参数
NOT IN 同样支持子查询作为参数,用法和 in 完全一样
3.5case表达式
case分为简单和搜索两种,语法:
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
ELSE <表达式>
END
当where表达式是否为真值,是则是执行then后面的语句,是假的就执行else后的语句,无论怎样都只会返回一个值,
聚合函数+case when 可以实现聚合
总结:当转换列是数字时可以使用 sum avg max min等聚合函数
如果是文本可以使用max min 等聚合函数。

作业:
3.1:


3.2:
3.1-3.2:

3.3:

3.4:


3.5:
不一定,coalesce函数返回不一定是null,可以将null转换成其他值
3.6:
208b20822360bb748aad71928cee217
c8ee180d0f98a9e405d1e87ca582d37
3.7:

浙ICP备19012682号