学习笔记-sql 篇
sql必知必会
2.5 DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。例 如，你指定 SELECT DISTINCT vend_id, prod_price，除非指定的 两列完全相同，否则所有的行都会被检索出来。
2.6 SELECT 时，可以使用 TOP 关键字来限制最多返回多少行 (语法不一定一样,比如LIMIT, LIMIT 带的 OFFSET 指定从哪儿开始, 简化版 LIMIT 3,4
3.1 ORDER BY 子句的位置
在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一 条子句。如果它不是最后的子句，将会出现错误消息。
提示:通过非选择列进行排序
通常，ORDER BY 子句中使用的列将是为显示而选择的列。但是，实 际上并不一定要这样，用非检索的列排序数据是完全合法的
3.4
在多个列上降序排序
如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字
4.1
注意:WHERE 子句的位置
在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误(关于 ORDER BY 的使用，请参阅 第 3 课 )。
4.2
BETWEEN 匹配 范围中所有的值，包括指定的开始值和结束值。
NULL无值(no value)，它与字段包含 0、空字符串或仅仅包含空格不同。
NULL 和非匹配 通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值 的行。但是这做不到。因为未知(unknown)有特殊的含义，数据库 不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返 回这些结果。
过滤数据时，一定要验证被过滤列中含 NULL 的行确实出现在返回的 数据中。
5.1
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
AND prod_price >= 10;
返回的行中有 4 行价格小于 10 美元，显然，返回的行 未按预期的进行过滤。为什么会这样呢?原因在于求值的顺序。SQL(像 多数语言一样)在处理 OR 操作符前，优先处理 AND 操作符。当 SQL 看到 上述 WHERE 子句时，它理解为:由供应商 BRS01 制造的价格为 10 美元以 上的所有产品，以及由供应商 DLL01 制造的所有产品，而不管其价格如何。 换句话说，由于 AND 在求值过程中优先级更高，操作符被错误地组合了
5.2
IN 的最大优点是可以包含其他 SELECT 语句，能够更动态地建立
WHERE 子句。第 11 课会对此进行详细介绍
5.3
WHERE NOT vend_id = 'DLL01'
6.x
%表示任何字符出现任意次数, 不匹配NULL
搜索可能可以是区分大小写的
注意空格:
prod_name LIKE 'F%y'只匹配以F开头、以y结尾的prod_name。 如果值后面跟空格，则不是以 y 结尾，所以 Fish bean bag toy 就 不会检索出来。简单的解决办法是给搜索模式再增加一个%号:'F%y%' 还匹配 y 之后的字符(或空格)。更好的解决办法是用函数去掉空格。 请参阅第 8 课
_只匹配 单个字符，而不是多个字符, 可以连用
方括号([])通配符用来指定一个字符集，它必须匹配指定位置(通配 符的位置)的一个字符, 可以用前缀字符^(脱字号)来否定
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始 处。把通配符置于开始处，搜索起来是最慢的
7.x 创建计算字段
拼接字段 SELECT vend_name + ' (' + vend_country + ')'
去掉空格 RTRIM()函数去掉值右边的所有空格, LTRIM()(去掉字符串左边的空格)以及 TRIM()(去掉字符 串左右两边的空格)
别名用 AS 关键字赋予
虽然 SELECT 通常用于从表中检索数据，但是省略了 FROM 子句后就是简单地访问和 处理表达式，例如 SELECT 3 * 2;将返回 6，SELECT Trim(' abc '); 将返回 abc，SELECT Now();使用 Now()函数返回当前日期和时间。 现在你明白了，可以根据需要使用 SELECT 语句进行检验。
8.x 使用函数处理数据
UPPER()将文本转换为大写
LEFT()(或使用子字符串函数) 返回字符串左边的字符
DATEPART() 函数，顾名思义，此函数返回日期的某一部分
Oracle 没有 DATEPART()函数，不过有几个可用来完成相同检索的日期处 理函数, WHERE to_number(to_char(order_date, 'YYYY')) = 2012;, to_char()函数用来提取日期的成分，to_number()用来将提取出的成分转换为数值
Oracle 的 to_date()函数用来将两个字符串转换为日期
MySQL 和 MariaDB 用户可使用名为 YEAR()的函数从日期中提取年份:
9.x 汇总数据
使用 COUNT()对表中行的数目进行计数，不管表列中包含的是空值 (NULL)还是非空值
使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。
SUM()函数忽略列值为 NULL 的行。
以上 5 个聚集函数都可以如下使用:
对所有行执行计算，指定 ALL 参数或不指定参数(因为 ALL 是默认行为
只包含不同的值，指定 DISTINCT 参数。
DISTINCT 不能用于 COUNT()
如果指定列名，则 DISTINCT 只能用于 COUNT()。DISTINCT 不能用 于 COUNT(*)。类似地，DISTINCT 必须使用列名，不能用于计算或表 达式。
10.x 分组数据
在使用GROUP BY子句前，需要知道一些重要的规定。
GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套， 更细致地进行数据分组。
如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进 行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以 不能从个别的列取回数据)。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但 不能是聚集函数)。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名
大多数 SQL 实现不允许 GROUP BY 列带有长度可变的数据类型(如文 本或备注型字段)。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句 中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。 如果列中有多行 NULL 值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
10.3 过滤分组
在这个例子 中 WHERE 不能完成任务，因为 WHERE 过滤指定的是行而不是分组。事实 上，WHERE 没有分组的概念。
SQL 为此提供了另一个子句，就是 HAVING 子句。HAVING 非常类似于 WHERE。事实上，目前为止所学过的 所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是，WHERE 过滤行，而 HAVING 过滤分组。
例子
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
HAVING 和 WHERE 的差别
这里有另一种理解方法，WHERE 在数据分组前进行过滤，HAVING 在数 据分组后进行过滤。这是一个重要的区别，WHERE 排除的行不包括在 分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值 过滤掉的分组。
使用 HAVING 和 WHERE
HAVING 与 WHERE 非常类似，如果不指定 GROUP BY，则大多数 DBMS 会同等对待它们。不过，你自己要能区分这一点。使用 HAVING 时应 该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
11.x 使用子查询
子查询常用于 WHERE 子句的 IN 操作符中，以及用来填充计算列
对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性 能的限制，不能嵌套太多的子查询
只能是单列
作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回 错误。
子查询和性能
这里给出的代码有效，并且获得了所需的结果。但是，使用子查询并 不总是执行这类数据检索的最有效方法。更多的论述，请参阅第 12 课，其中将再次给出这个例子
11.3 作为计算字段使用子查询
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
不止一种解决方案
正如这一课前面所述，虽然这里给出的样例代码运行良好，但它并不 是解决这种数据检索的最有效方法。在后面两课学习 JOIN 时，我们 还会遇到这个例子
12.x 联结表
性能考虑
DBMS 在运行时关联指定的每个表，以处理联结。这种处理可能非常 耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性 能下降越厉害。
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
如第 11 课所述，子查询并不总是执行复杂 SELECT 操作的最有效方法， 下面是使用联结的相同查询:
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01';
目前为止使用的联结称为等值联结(equijoin)，它基于两个表之间的相 等测试。这种联结也称为内联结(inner join)。
下面的 SELECT 语句返回与 前面例子完全相同的数据:
输入▼
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
“正确的”语法
ANSI SQL 规范首选 INNER JOIN 语法，之前使用的是简单的等值语 法。其实，SQL 语言纯正论者是用鄙视的眼光看待简单语法的。这就 是说，DBMS 的确支持简单格式和标准格式，我建议你要理解这两种 格式，具体使用就看你用哪个更顺手了。
13.x 创建高级联结
14.x 组合查询
UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键 字 UNION 分隔(因此，如果组合四条 SELECT 语句，将要使用三个 UNION 关键字)。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过， 各个列不需要以相同的次序列出)。
列数据类型必须兼容:类型不必完全相同，但必须是 DBMS 可以隐含 转换的类型(例如，不同的数值类型或不同的日期类型)。
使用 UNION 时，重复 的行会被自动取消。
这是 UNION 的默认行为
事实上，如果想返回 所有的匹配行，可使用UNION ALL而不是UNION
这条 UNION 在最后一条 SELECT 语句后使用了 ORDER BY 子句。虽然 ORDER BY 子句似乎只是最后一条 SELECT 语句的组成部分，但实际上 DBMS 将 用它来排序所有 SELECT 语句返回的所有结果
15.x 插入数据
虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的 SQL 语 句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息
省略列
如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列 必须满足以下某个条件。
该列定义为允许 NULL 值(无值或空值)。
在表定义中给出默认值。这表示如果不给出值，将使用默认值
插入检索出的数据
insert select
还有个select into
16.x 更新和删除数据
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
在 UPDATE 语句中使用子查询
UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据 更新列数据
FROM 关键字
有的 SQL 实现支持在 UPDATE 语句中使用 FROM 子句，用一个表的数 据更新另一个表的行
DELETE FROM Customers
WHERE cust_id = '1000000006';
更快的删除
如果想从表中删除所有行，不要使用 DELETE。可使用 TRUNCATE TABLE 语句，它完成相同的工作，而速度更快(因为不记录数据的变动)。
更新和删除的指导原则
前两节使用的 UPDATE 和 DELETE 语句都有 WHERE 子句，这样做的理由 很充分。如果省略了 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表 中所有的行。换句话说，如果执行 UPDATE 而不带 WHERE 子句，则表每一行都将用新值更新。类似地，如果执行 DELETE 语句而不带 WHERE子句，表的所有数据都将被删除。
下面是许多 SQL 程序员使用 UPDATE 或 DELETE 时所遵循的重要原则。
除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句 的 UPDATE 或 DELETE 语句。
保证每个表都有主键(如果忘记这个内容，请参阅第 12 课)，尽可能 像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。
在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进 行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
使用强制实施引用完整性的数据库(关于这个内容，请参阅第 12 课)，
这样 DBMS 将不允许删除其数据与其他表相关联的行。
有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句 的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应
该使用它。
17.x 创建与操作表
替换现有的表
在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆 盖已有的表，SQL 要求首先手工删除该表(请参阅后面的内容)，然 后再重建它，而不是简单地用创建表语句覆盖它
使用 DEFAULT 而不是 NULL 值
许多数据库开发人员喜欢使用 DEFAULT 值而不是 NULL 列，对于用于 计算或数据分组的列更是如此。
以下是使用 ALTER TABLE 时需要考虑的事情。
理想情况下，不要在表中包含数据时对其进行更新。应该在表的设 计过程中充分考虑未来可能的需求，避免今后对表的结构做大 改动。
所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型 (以及 NULL 和 DEFAULT 的使用)有所限制。
许多 DBMS 不允许删除或更改表中的列。
多数 DBMS 允许重新命名表中的列。
许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几
乎没有限制。
18.x 使用视图
视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。 视图提供了一种封装 SELECT 语句的层次，可用来简化数据处理，重新 格式化或保护基础数据。
19.x 使用存储过程
简单来说，存储过程就是为以后使用而保存的一条 或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。
20.x 管理事务处理
事务是必须完整执行的 SQL 语句块
21.x 使用游标
需要在检索出来的行中前进或后退一行或多行，这就是游标的用 途所在。游标(cursor)是一个存储在 DBMS 服务器上的数据库查询， 它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了 游标之后，应用程序可以根据需要滚动或浏览其中的数据
使用游标涉及几个明确的步骤。
21.2 使用游标 | 185
在使用游标前，必须声明(定义)它。这个过程实际上没有检索数据， 它只是定义要使用的 SELECT 语句和游标选项。
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
对于填有数据的游标，根据需要取出(检索)各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标(有赖于具
体的 DBMS)。
22.x 高级sql特性
约束(constraint)
管理如何插入或处理数据库数据的规则
表中任意列只要满足以下条件，都可以用于主键。
任意两行的主键值都不相同。
每行都具有一个主键值(即列中不允许 NULL 值)。
包含主键值的列从不修改或更新。(大多数 DBMS 不允许这么做，但如果你使用的 DBMS 允许这样做，好吧，千万别!)
主键值不能重用。如果从表中删除某一行，其主键值不分配给新行
外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完
整性的极其重要部分。
外键有助防止意外删除
如第 6 课所述，除帮助保证引用完整性外，外键还有另一个重要作用。 在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。例 如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相 关的订单(这表示还要删除相关的订单项)。由于需要一系列的删除， 因而利用外键可以防止意外删除数据。
有的 DBMS 支持称为级联删除(cascading delete)的特性。如果启用， 该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用 级联删除并且从 Customers 表中删除某个顾客，则任何关联的订单行 也会被自动删除。
唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主
键，但存在以下重要区别。
表可包含多个唯一约束，但每个表只允许一个主键。 唯一约束列可包含 NULL 值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。
检查约束
CREATE TABLE OrderItems
(
..... NOT NULL CHECK (quantity > 0),
);
索引
在开始创建索引前，应该记住以下内容。
索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。 在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据(如州)不如具有更多可能值的数据(如姓或名)，能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列(例如，州加上城市)。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。 因此，按主键检索特定行总是一种快速有效的操作。
CREATE INDEX prod_name_ind
ON Products (prod_name);
触发器
下面是触发器的一些常见用途。TRIGGER
保证数据一致。例如，在 INSERT 或 UPDATE 操作中将所有州名转换 为大写。
基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行 时将审计跟踪记录写入某个日志表。
进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资 金不超限定，如果已经超出，则阻塞插入。
计算计算列的值或更新时间戳。
ODBC（Open Database Connectivity，开放数据库互连）提供了一种标准的API（应用程序编程接口）方法来访问数据库管理系统（DBMS）。
group by
SELECT BIGINT(self_gds_id % 10) item_group
,CASE WHEN BIGINT(self_gds_id % 10) IN (0,3,4,5,6,9) THEN '人工定价' ELSE '算法定价' END type --error
FROM jiuwu_sc.ads_spc_service_recycle_process_ds
WHERE self_platform_on_shelf_time >= '2023-05-19 00:00:00' and dt = MAX_PT("jiuwu_sc.ads_spc_service_recycle_process_ds")
GROUP BY (self_gds_id % 10)
LIMIT 5;
不会报错
但是
SELECT BIGINT(self_gds_id % 10) item_group
,CASE WHEN BIGINT(self_gds_id) % 10 IN (0,3,4,5,6,9) THEN '人工定价' ELSE '算法定价' END type --error
FROM jiuwu_sc.ads_spc_service_recycle_process_ds
WHERE self_platform_on_shelf_time >= '2023-05-19 00:00:00' and dt = MAX_PT("jiuwu_sc.ads_spc_service_recycle_process_ds")
GROUP BY (self_gds_id % 10)
LIMIT 5;
会报错
FAILED: ODPS-0130071:[2,26] Semantic analysis exception - column reference ads_spc_service_recycle_process_ds.self_gds_id should appear in GROUP BY key
https://blog.csdn.net/tanqingfu1/article/details/124267435
1、order by后面的列必须是在select后面存在的。
2、 select、having或order by后面存在的非聚合列必须全部在group by中存在。
SQL JOIN 中 on 与 where 的区别
https://www.runoob.com/w3cnote/sql-join-the-different-of-on-and-where.html
聚合函数
SELECT TOUPPER(TRIM(gds_code)) gds_code
,gds_level
,gds_size
,sale_price
,DENSE_RANK() OVER (PARTITION BY TOUPPER(TRIM(gds_code)),gds_size,gds_level ORDER BY sale_price DESC ) price_desc
,DENSE_RANK() OVER (PARTITION BY TOUPPER(TRIM(gds_code)),gds_size,gds_level ORDER BY sale_price ) price_asc
,COUNT(DISTINCT sale_price) OVER (PARTITION BY TOUPPER(TRIM(gds_code)),gds_size,gds_level) same_nums
FROM jiuwu_sc.dm_goods_ds
WHERE dt = ${bdp.system.bizdate}
AND sale_type IN (0,3)
AND l1_cat_id = 1
AND l2_cat_id <> 227
AND spu_id > 0
AND title NOT LIKE '%闷包%'
AND gds_type = 0
AND is_on_sale = 1
AND gds_code = 'DD1503-101';
-- GROUP BY gds_code, gds_size, gds_level, sale_price;
最后一句要不要 结果不一样,但是不会报错
select 临时查询
select * from values ('你好。'),('nihao！') t(d)
d是列
select 除了dt之外的字段
select (dt)?+.+
(dt)?+.+
有三类：dt，dtxxx，xxx
(dt)?非贪婪，有dt就只匹配一个dt，无dt不匹配，代表匹配0或者1个dt
(dt)?+ 一次或多次，假如有dt，那就是一个或多个dt
.+限制了后面必须有东西，淘汰了只有dt
种一棵树最好的时间是十年前，其次是现在。
