SQL-进阶-上

最新推荐文章于 2023-07-30 15:34:16 发布

breadmakerQQQ

最新推荐文章于 2023-07-30 15:34:16 发布

阅读量153

点赞数

本文链接：https://blog.csdn.net/breadmakerQQQ/article/details/104573877

版权

SQL-进阶-上

今天开始看进阶版，没想到竟然只有英文字幕，爆哭，努力学下去叭小胡~

1.1 Aggregate Functions（聚合函数？）
聚合函数是一系列以集合为输入，以单个数据为输出的内置函数，常见有MAX()、MIN()、AVG()、SUM()、COUNT()，格式为：[SELECT] AG_FUNC( column or expression ) [AS alias FROM table WHERE condition]
AVG是求平均数而COUNT是求长度。值得注意的是聚合函数会自动忽略NULL值，因此例如COUNT( column )很大情况下并不能真正反映记录的真正长度，如果想要做到的话可以试着这两种思路：

SELECT COUNT(*) AS total_records
SELECT COUNT(primary_key) AS total_records

缺省情况下聚合函数不会无视相同的值，即相同的值会被多次计算，若想避免此情况发生，即每个不同的值只被函数计算一次，需添加DISTINCT关键字：AG_FUNC( DISTINCT column )，而DISTINCT关键字也可以用在很多其他地方以筛除相同项，例如SELECT DISTINCT

1.2 GROUP BY子句
GROUP BY：用于将具有某种字段相同值的记录归为一类，格式：ORDER BY column，对于复合型的criteria，将产生复合型的组合，并依据组合分类。
clause（子句顺序）以及GROUP BY的一个实例用法：

SELECT client_id, SUM(invoice_total) AS total_sales 
FROM invoices i
JOIN clients c USING (client_id)
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC

1.3 HAVING子句
HAVING：作为GROUP BY子句的补充，若要根据分组后的新字段进行筛选，这里并不能使用WHERE子句（因为WHERE子句顺序在GROUP BY之前），故HAVING子句在此作出这一补充，紧跟于GROUP BY子句后面。

HAVING和WHERE一样的格式：HAVING condition，也可通过逻辑连接词将多个criteria连接起来
WHERE和HAVING一样都可以进行筛选，不同的是WHERE在分组之前，对记录进行筛选，一定程度上他决定了哪些记录可以进行后续操作。而HAVING则在分组之后对新产生的记录进行筛选，它只决定了哪些新记录会被挑选出来。
在WHERE的condition中，无论被审查的column是否在SELECT中被挑选，均可以作为它的判断条件，而HAVING中的condition，其审查的column则必须要被SELECT语句所挑选。但是如果column仅作为某一函数的参数，则无论是否被SELECT挑选则不要紧

SELECT CountryCode FROM city GROUP BY CountryCode HAVING Population > 100000 --报错，Population是未被识别的列
SELECT CountryCode FROM city GROUP BY CountryCode HAVING SUM(Population) > 100000 --通过并可得到正确结果

1.4 ROLLUP运算符（MySQL限定）
WITH ROLLUP：紧跟在（比HAVING还紧）GROUP BY子句后面，GROUP BY column_1, column_2, ... WITH ROLLUP，用于计算分组们的总值，不太好解释所以上图：

GROUP BY state, city WITH ROLLUP --对于复合型分组条件，ROLLUP将对前n-1个分组条件进行总和运算

在这里插入图片描述

2.2 Subquery（子查询）：嵌套于其他查询或其他语句的查询

2.3 IN可以用在子查询返回一系列值的判断中，一个简单的实例：

SELECT * FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id FROM order_items)

2.5 ALL关键字
ALL：在逻辑上对元组的每个元素进行与操作：price > ALL (150, 193, 137, ...) == price > 150 AND price > 193 AND price > 137 AND ...，此关键字也可用在子查询的前缀以等效于使用MAX()或MIN()之类的函数
2.6 ANY关键字
ANY：在逻辑上对元组的每个元素进行或操作：price > ALL (150, 193, 137, ...) == price > 150 OR price > 193 OR price > 137 OR ...，此关键字有点等效于IN啥的

2.7 具有相关性的子查询（Correlated Subquery）

SELECT * FROM employees e
WHERE salary > (
	SELECT AVG(salary) FROM employees
	WHERE office_id = e.office_id
) --查询在每个不同的办公室中（office_id），薪资（salary）高于平均水平的职员（employee）

对于普通的嵌套查询，它嵌套于其中的子查询与它并没有关联，也就是说，子查询是静态的，对于这种普通情况，sql只需执行一遍子查询并返回数值即可。而在具有关联性的嵌套查询中，如实例所示，子查询中的条件与父查询息息相关。因此，每次父查询执行一条记录时，子查询都要执行一遍，也就是说子查询是动态的，可以根据父查询正在处理的记录进行改变，其返回值不是唯一的。这种相关性耗时无疑是更多的。

2.8 EXISTS运算符

SELECT * FROM clients WHERE client_id IN (SELECT DISTINCT client_id FROM invoices)
SELECT * FROM clients c WHERE EXISTS (SELECT client_id FROM invoices WHERE client_id = c.client_id)

这两种写法所要达到的效果一样，都是查找存在于invoices这张表中的客户并查找其在clients表中的信息。对于第一种已经认识了的写法，其思路是首先通过子查询查找并返回invoices中所有client_id的集合，然后通过In运算符判别，这种写法简单明了也便于理解，但如果invoices中的client_id非常多，那么子查询将返回一个长度非常大的集合，往往是不利的，因此可以利用EXISTS运算符。在第二种写法中，首先明确一点子查询是动态的，其运行过程是这样的，父查询每次处理一条记录时，便进入子查询，EXISTS运算符使得子查询并不会真正的执行SELECT语句，反之，它只会对子查询中的WHERE条件进行一次又一次的判别，最终返回布尔值给父查询中的WHERE以决定该条记录是否需要被挑选。在本例中也是如此，clients中的每条记录都会进入子查询中进行invoices中是否有相同client_id的判别并返回true或者false

2.9 SELECT子句中的子查询
在这里插入图片描述

第一个子查询的原因：AVG是聚合函数只返回单一的一个值，而结果需要的是一列的效果，故使用子查询返回一列
第二个SELECT的原因：在同一个SELECT下的各个参数是同步的，也就是说地位是平等的，所以虽然看似第四个参数在第三个参数后面，但实际上他们是同时被计算的，所以如果只写Population - avg_pop此时编译器还把avg_pop作为unknown column，因此通过把他嵌套在子查询中使其与父查询并异步被计算即可解决此问题，相同写法也可以是(SELECT (Population -avg_pop)) AS differences

2.10 FROM子句中的子查询：查询的结果也是一张表，也可作为FROM的对象，但此种情况下必须赋予该表别名：FROM (SELECT ...) alias 或者 FROM (SELECT ...) AS alias

3.1 Numeric Functions（数值函数）
ROUND(value, precision = 0)：返回value保留precision位小数，precision缺省为0
TRUNCATE(value, precision)：返回value截去后保留precision位的小数，precision不能缺省
CEILING(value)：返回大于value的最小整数、FLOOR(value)：返回小于value的最大整数
ABS(value)：返回value的绝对值、RAND()：返回0~1之间的随机浮点数
Numeric Functions官方文档

3.2 String Functions（字符串函数）
LENGTH(string)：返回string长度、UPPER(string)：返回string的全大写形式、LOWER(string)：返回string的全小写形式
LTRIM(string)：清除string最左侧所有空格、RTRIM(string)：清除string最右侧所有空格、TRIM(string)：清除string所有空格
LEFT(string, num)：返回string最左侧num个字符、RIGHT(string, num)：返回string最右侧num个字符
SUBSTRING(string, start, length)：返回string从第start个字符开始，长度为length的子串。（在MySQL中，字符串的字符下标从1开始）
LOCATE(char, string)：返回string中第一次出现char的位置，大小写不敏感，若未出现，返回0
REPLACE(string, substring, new_string)：将string中的所有substring子串替换成new_string
CONCAT(string1, string2, ...)：将string1、string2、…所有字符串拼接
String Functions官方文档

3.3 Date Functions（日期时间函数）
NOW()：返回当前日期和时间、CURDATE()：返回当前日期、CURTIME()：返回当前时间
YEAR(date)：返回date的年、MONTH(date)：返回date的月、DAY(date)：返回date的日
HOUR(date)：返回date的时、MINUTE(date)：返回date的分、SECOND(date)：返回date的秒
DAYNAME(date)：返回date的星期名、MONTHNAME(date)：返回date的月份名
EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM date)：返回date的年/月/日/时/分/秒，该函数是sql标准的一部分，适用于跨库编译。

3.4 DATE_FORMAT函数和TIME_FORMAT函数：将日期和时间转化成任意形式的输出函数
DATE_FORMAT函数详细用法，TIME_FORMAT函数占位符相同

3.5 日期时间的计算
单个日期的相对运算：DATA_ADD(date, INTERVAL int_num YEAR/MONTH/DAY/HOUR/MINUTE/SECOND)，返回date加上int_num年/月/日/时/分/秒，DATA_SUB同理，减去。
两个日期的相对计算：DATEDIFF(date1, date2)：计算date1和daye2的差异，量纲为天（也就是说不能计算时间的差异，在计算时date1和date2的时间会被忽略）
两个时间的相对计算：TIME_TO_SEC(time1) - TIME_TO_SEC(time2)：TIME_TO_SEC函数返回从输入时间到午夜零点的秒数

3.6 一些函数
IFNULL(column, value)：如果该记录column项不为空，则返回column。若为空，则返回value值
COALESCE(column1, column2, column3, ..., value)：如果该记录column1为空则返回column2，若column2也为空，则返回column3，…，一直到返回固定值value
IF(expression, value1, value2)：如果expression值为True，则返回value1，值为假则返回value2

3.7 CASE运算符：依次检查expression1、expression2、expression3、…，如果为真，则返回紧跟其后面THEN后的值，如果全部都不为真，返回ELSE后的值

CASE 
	WHEN expression1 THEN value1
	WHEN expression2 THEN value2
	WHEN expression3 THEN value3
	...
	ELSE value_default
END

4.1 视图（View）
使用CREATE VIEW关键字，视图与表格几乎完全一样，具体来说，视图就是对多张表进行的SELECT及各种子句操作创造出来的“表”，为了方便在多次调用时的麻烦。如赋予其名字，使之可以像表格一样通过名字来被使用。唯一不同的是，表格是真实存在的表格，是数据库中的物理存储，而视图虽有名字，但是说到底只是一堆待编译的select语句，并不在数据库中物理存在，是virtual的。在MySQL中，左侧导航栏可以找到Views

CREATE VIEW view_name AS
	SELECT ...

4.2 修改视图

DROP：在导航栏中删除（drop）视图或者使用语句DROP VIEW view_name，之后便可再次执行创造语句进行修改
添加关键字REPLACE达到创造或替换效果：CREATE OR REPLACE VIEW view_name AS ...
改变视图（Altering View）：左侧导航栏右击视图或左击视图名右侧第二个小图标打开MySQL专门的视图编辑栏，修改完成后点击右下角Apply即可
p.s.在视图编辑栏中可以看到所有表格名、字段都被此符号’ ` '（键盘左上角ESC下面）包围，当要使用sql关键字做字段、表格名等等时，即可使用这种方法哟

4.3 Updatable Views（可更新的视图）
如果一个视图的构造中没有以下关键字：DISTINCT、UNION、GROUP BY/HAVING以及Aggregate Functions，则可称该视图为可更新的视图，可以对该视图进行插入、修改和删除操作，就像一张正常的表一样。

4.4 WITH CHECK OPTION：加在构造视图语句的最后
在with check option的选项下，可以总结为
1、update，要保证数据update之后能被视图查询出来，也就是要符合where的条件
2、insert,保证insert的数据能被视图查询出来
3、delete,有无 with check option都一样
4、对于没有where字句的视图，使用with check option是多余的

breadmakerQQQ

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL-进阶-上

SQL-进阶今天开始看进阶版，没想到竟然只有英文字幕，爆哭，努力学下去叭小胡~1.1 Aggregate Functions（聚合函数？）聚合函数是一系列以集合为输入，以单个数据为输出的内置函数，常见有MAX()、MIN()、AVG()、SUM()、COUNT()AVG是求平均数而COUNT是求长度。值得注意的是聚合函数会自动忽略NULL值，因此例如COUNT()...
复制链接

扫一扫