回忆一些基础操作,就当是备忘录了,为以后的骚操作做准备
使用的数据库环境:SQL Server 2017
一、IF - ELSE循环
基本的循环语句很好理解:
IF CONDITION_1 -- CONDITION_1是条件,成立则执行 IF 语句块,否则执行 ELSE 语句块
{ STATEMENT | STATEMENT_BLOCK }
[ELSE
{ STATEMENT | STATEMENT_BLOCK }]
今天主要没想起来的是如果要使用语句块该怎么做,后来发现价格 BEGIN - END 就好了:
IF condition_1
BEGIN
{ STATEMENT | STATEMENT_BLOCK }
END
[ELSE
{ STATEMENT | STATEMENT_BLOCK }]
ELSE 语句同理。
二、A表插入/更新B表
业务需求,需要对A表的数据进行排序并将排序结果永久保存。目前想到的办法是将A表的数据先放到B表中,然后清空A表,对B表排序后将B表数据插回去。
对于插入表,我们使用 INSERT 语句将B表的查询结果插到A表中,插入时注意列的顺序和类型要保持一致:
INSERT INTO TABLE_A(field_1, field_2, ...)
SELECT
B.field_1, B.field_2, ...
FROM
TABLE_B B
[WHERE { CONDITION }]
根据你的需求对B表进行条件限制。
用B表更新A表的操作需要用到UPDATE,不同的数据库采用的语法不同,这里以SQL SERVER为例:
UPDATE
A.field_1 = B.field_1
FROM
TABLE_A A, TABLE_B B
WHERE
{ CONDITION... }
一般写条件的时候至少会让A表和B表的主键相连,不然数据对不到一起去也就没什么好更新的了。
三、递归
如果不是业务上的神奇需求还真碰不到这玩意儿,在这里先感谢我的两位主管。从我个人的角度来看,递归的主要思路始终是“自己调用自己,自己分解自己”,在JAVA里是这么操作,在SQL里同样也是如此,只是需要一些不太一样的手段。
在开始解释递归之前,我们首先要了解一个东西:公用表表达式(CTE)。关于CTE的具体内容在这里不做展开,有兴趣的朋友可以自行搜索或参考这篇文章:SQL.WITH AS.公用表表达式(CTE)。简单来说,CTE出现于SQL Server 2005,旨在解决使用表变量时可能存在的IO开销和SQL后续维护问题,在这里我们重点了解下它的with as语法。
在CTE中,我们通过with as创建一个共用表,在with后调用它:
CREATE TABLE Student (
stu_id int primary key,
stu_name char(10),
stu_score int not null,
stu_sex char(7) not null
);
INSERT INTO Student VALUES(1, 'Tom', 75, 'Male');
INSERT INTO Student VALUES(2, 'Lily', 55, 'Male');
INSERT INTO Student VALUES(3, 'Sam', 85, 'Female');
INSERT INTO Student VALUES(4, 'Aden', 25, 'Female');
INSERT INTO Student VALUES(5, 'Hello', 35, 'Female');
INSERT INTO Student VALUES(6, 'World', 75, 'Male');
WITH TABLE_A AS
(
SELECT A.stu_name, A.stu_score FROM [dbo].[Student] A
WHERE stu_SEX <> 'Male'
)
SELECT A.stu_name, A.stu_score FROM [dbo].[Student] A, TABLE_A B
WHERE A.stu_name IN (SELECT B.stu_name)
ORDER BY A.stu_score DESC
DROP TABLE Student;
上述是一个简单的CTE使用案例,TABLE_A 的内容可以根据实际业务内容而变得更加复杂。
现在让我们再回顾一下递归的基本内容。递归的思想就是“分而治之”,将大问题分解成多个同类型的小问题直到其不可再分。下面是斐波那契数列的一个递归解法:
public static int Fibonacci(int n) throws NumberFormatException {
if (n < 0) {
throw new NumberFormatException("Number can not be smaller than 0");
}
if (n == 2 || n == 1) {
return 1;
} else {
return Fibonacci(n - 1) + Fibonacci(n - 2);
}
}
现在我们用斐波那契数列作练习,如果我们要用SQL语句来实现类似的效果,我们应该怎么做呢?
在T-SQL语句中,我们的计算逻辑和JAVA略有不同,JAVA通过变量将上一次的值传递到下一次运算中,但数据库中我们可以利用table来完成这一步骤,所以我们要做的事情就是计算累加和,计算前一个值和当前值的和,将其作为下一次运算的起始:
-- 创建一个变量表,用来存储数字,ID表示编号,NUM表示对应编号所代表的值
DECLARE @FIBONACCI TABLE
(
ID INT,
NUM INT
);
-- 利用WHILE循环往表格里塞20行数据,ID从1到20,NUM初始值为1
DECLARE @INDEX INT = 1;
DECLARE @LENGTH INT = 20;
WHILE(@INDEX < @LENGTH + 1)
BEGIN
INSERT INTO @FIBONACCI VALUES(@INDEX, 1);
SET @INDEX = @INDEX + 1;
END;
WITH BASE(ID, NUM) AS -- 定义递归要用到的数值
(
SELECT
A.ID, A.NUM
FROM
@FIBONACCI A
-- WHERE ... -- 如果有需要,在这里对定义域做过滤
), RESULT(ID, NUM, CALC_SUM) AS -- 定义递归函数体
(
-- 通过WHERE定义递归的起点
SELECT
A.ID, A.NUM, A.NUM
FROM
BASE A
WHERE A.ID = 1
UNION ALL
-- CALC_NUM表示累加和,我们将计算的结果返回给NUM用于下一次计算
SELECT
A.ID, B.CALC_SUM, B.CALC_SUM + B.NUM
FROM
RESULT B
JOIN
BASE A -- 使用 JOIN ON 字段将两表联立,ON后的表达式声明变化过程
ON A.ID = B.ID + 1
WHERE B.ID < @LENGTH -- WHERE后的表达式声明递归的结束条件
)
SELECT ID, NUM FROM RESULT; -- 在CTE定义后立刻使用它,否则会失效。
-- 声明变量表的好处就是不需要在结尾使用DROP TABLE,如果使用临时表,在这里要对表格进行DROP操作