oracle 基础语法复习记录-EW帮帮网

Oracle SQL基础

因工作需要sql能力，需要重新把sql这块知识重新盘活，特此记录学习过程。

希望有新的发现。加油！20250205

学习范围

学习SQL基础语法
- 掌握SELECT、INSERT、UPDATE、DELETE等基本操作。
- 熟悉WHERE、GROUP BY、ORDER BY、HAVING等子句。
理解表连接：
- 学习INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN等连接方式。
掌握聚合函数：
- 熟悉SUM、COUNT、AVG、MIN、MAX等函数的使用

进阶学习

子查询：
- 学习如何在SELECT、FROM、WHERE中使用子查询。
- 理解相关子查询和非相关子查询的区别。
窗口函数：
- 学习ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()等窗口函数。
- 掌握OVER子句的使用，尤其是PARTITION BY和ORDER BY。
递归查询：
- 学习使用WITH递归查询（CTE，Common Table Expressions）处理层次结构数据。
集合操作：
- 掌握UNION、UNION ALL、INTERSECT、MINUS等集合操作。

SQL基础语法

学习记录：

select

查询客户表所有列

select * from tcustinfo ;

查询客户表指定列

select t.vc_custno,t.vc_customname,t.vc_identityno from tcustinfo t;

查询有效状态得客户指定列名

select t.vc_custno,t.vc_customname,t.vc_identityno from tcustinfo t where t.c_state='0';
查询有效客户数量

select count(1) from tcustinfo t where t.c_state='0';
查询有效客户证件类型

select distinct(t. c_identitytype) from tcustinfo t where t.c_state='0';

查询2024年1月1日起，有效客户数量

select count(1) from tcustinfo t where t.vc_opendate > 20240101 and t.c_state='0';

拓展：where 过滤条件，不仅仅用于select查询，update，delete中同样适用。

以下运算符可用于WHERE条款，>, <, =, >=,>=,<>,BETWEEN,like, in等

ORDER BY关键字用于按升序asc或降序 desc。

按开户时间进行降序排序 desc
select vc_opendate from tcustinfo t order by t.vc_opendate DESC

WHERE子句可以包含一个或多个 AND运算符。AND运算符用于根据多个记录筛选记录条件 ,

筛选出2024年开户，开户类型为机构得客户

select * from tcustinfo t where t.vc_opendate between 20240101 and 20241231 and t.c_state='0' and t.vc_customname like '%机构%';

筛选出2024年开户，开户类型为：机构或者产品得客户

select * from tcustinfo t where t.vc_opendate between 20240101 and 20241231 and t.c_state='0' and （ t.vc_customname like '%机构%' or t.vc_customname like '%产品%'）;

not 不在，统计相反得运算符，可以和其他运算符搭配，比如 not in，not like，NOT BETWEEN 10 AND 60; not CustomerID > 50 也可以用！> 来表示 (not < 也可以用!<)

select * from tcustinfo t where not t.c_identitytype ='0';

select * from tcustinfo t where t.c_identitytype !='0';

select * from tcustinfo t where t.vc_opendate > 20240101 and t.c_state='0' and t.vc_customname not like '%机构%'

insert

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

也可以同时插入多条语句

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

null not null

当无法使用比较运算符例如 = 、 <或 <>，可以用 IS NULL和 IS NOT NULL来替代

查询vc_instreprname 为null得数据

select * from tcustinfo t where t.vc_instreprname is null;

查询vc_instreprname 不为null得数据

select * from tcustinfo t where t.vc_instreprname is not null;

update

UPDATE语句用于修改表中的现有记录（注意！！要加where条件，否则会变更所有记录）

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

delete

DELETE语句用于删除表中的现有记录。（注意！！要加where条件，否则会删除所有记录）

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

聚合函数

`AVG()`函数返回数值列的平均值

查询所有产品平均价格

SELECT AVG(Price) FROM Products;

`SUM()`函数返回数值列的总和

搭配GROUP BY 经常用于统计某个分组得总和

统计不同投资人个人0 机构1 产品2，得开户数量

select sum(1),t.c_custtype from tcustinfo t group by t.c_custtype

统计不同投资人类型得确认份额并且按照客户类型倒叙

select sum(t.en_confirmshare),t.c_custtype from tconfirm t group by t.c_custtype order by t.c_custtype desc

`COUNT()`函数返回与指定条件匹配的行

查询一共发行多少产品
select count(1) from tfundinfo t

并按不同类型得产品分组统计，每个类型得产品多少数量

select count(1),c_type from tfundinfo t group by t.c_type

LIKE运算符

LIKE运算符用于 WHERE子句在列中搜索指定的模式，常用于模糊查询

有两个通配符经常与 LIKE操作员:

百分号%表示零个、一个或多个字符
下划线符号_表示一个单个字符

比如查出姓名带有“王”得投资人

select * from tcustinfo t where t.vc_customname like '%王%';

再比如，查出名字中带有王X杰得投资人，中间字可以用下划线 —— 来表示。

select * from tcustinfo t where t.vc_customname like '%王_杰%';

也可以搭配 and or 运算符查询，比如查出王或者姓毛得投资人

select * from tcustinfo t where t.vc_customname like '%王%' or t.vc_customname like '%毛%';

或者查询第二个字为妃得投资人

select * from tcustinfo t where t.vc_customname like '_妃%'

用%和_ ,可与其他组合使用通配符查询我们想获得得数据

比如查询前面只有一个字，后面是明杰得投资人，比如X明杰

select * from tcustinfo t where t.vc_customname like '_明杰'

再比如查询，香XX蛋中间两个字符，hh 这是奇怪得搭配香菜煎蛋，笑不活了，谁造得啊

select * from tcustinfo t where t.vc_customname like '香__蛋'

IN运算符

IN运算符是多个 OR条件

select * from tcustinfo t where t.vc_custno in ('2638','2648','2678')

查询所有未下单得客户

SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

`BETWEEN`运算符

选择给定范围内的值。值可以是数字、文本或日期。

选择价格在10到20之间的所有产品:

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

SQL别名

SQL别名用于为表或表中的列提供临时名称。

别名通常用于使列名更可读，易懂。

别名仅在该查询的持续时间内存在。

使用创建别名AS关键字

select t.vc_acconame as 客户姓名,t.vc_tradeacco 客户交易账户 from taccoinfo t

将 "双引号" 用于带空格字符的别名: 有些客户需求别名里面含有空格

select t.vc_acconame as "A基金公司客户姓名 " ,t.vc_tradeacco 客户交易账户 from taccoinfo t

这里如果是给表 table 取别名，同样的规则也适用。用于多表关联查询。

select * from tfundacco t

JION联接

JOIN子句用于组合来自两个或多个表的行，如下是不同类型得join链接

(INNER) JOIN: 返回两个表中具有匹配值的记录
LEFT (OUTER) JOIN: 返回左表的所有记录，右表的匹配记录
RIGHT (OUTER) JOIN: 返回右表中的所有记录，并将匹配的左表中的记录
FULL (OUTER) JOIN: 当任一左有匹配项时，返回所有记录或右表

内连接：

语法：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

查询20240101之后得订单信息，关联投资人表+订单表

select * from taccoinfo t join trequest t1 on t.vc_tradeacco =t1.vc_tradeacco and t1.vc_requestdate > '20240101'

上述内连接，其实也可以这样写：

select * from taccoinfo t , trequest t1 where t.vc_tradeacco =t1.vc_tradeacco and t1.vc_requestdate > '20231119'

总结，内连接，仅返回两个表中具有匹配项的数据

左连接

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN关键字从返回所有记录左表 (客户)，即使右表中没有匹配项

select t.vc_tradeacco,t.vc_acconame,t.vc_custno,t1.vc_fundacco,t1.vc_requestdate from taccoinfo t left join trequest t1 on t.vc_tradeacco =t1.vc_tradeacco and t1.vc_requestdate > '20241014'

右连接

RIGHT JOIN关键字返回右表 (table2) 中的所有记录，而匹配左表 (table1) 中的记录。

select t.vc_tradeacco,t.vc_acconame,t.vc_custno,t1.vc_fundacco,t1.vc_requestdate from taccoinfo t right join trequest t1 on t.vc_tradeacco =t1.vc_tradeacco and t1.vc_requestdate > '20241014'

FULL JOIN

关键字返回所有记录左 (表1) 或右 (表2) 表记录，返回记录较大

select t.vc_tradeacco,t.vc_acconame,t.vc_custno,t1.vc_fundacco,t1.vc_requestdate from taccoinfo t full join trequest t1 on t.vc_tradeacco =t1.vc_tradeacco and t1.vc_requestdate > '20241014'

SQL自联接

最常用的一种连接方式，

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

select * from taccoinfo t , trequest t1 where t.vc_tradeacco =t1.vc_tradeacco and t1.vc_requestdate > '20241014'

`UNION`

运算符用于组合两个或多个结果集 SELECT 报表

语法：

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

每SELECT内的语句 UNION必须具有相同的列编号
列还必须具有相似的数据类型
中的列每SELECT语句也必须以相同的顺序

UNION默认情况下，运算符仅选择不同的值。允许重复的值，请使用UNION ALL

GROUP BY 分组

GROUP BY语句通常与聚合函数一起使用 (COUNT(), MAX(), MIN(),SUM(), AVG()) ，比如统计某个国家有多少人，统计不同年龄段基金开户数量，等等。

这里举一个生动的例子，加深理解：

从request基金申请表中统计，不同资金方式的交易数量（比如在柜台开户的多少人，通过渠道开户的多少人）。

select count(1),a.c_capitalmode from trequest a group by a.c_capitalmode;

继续~ 统计不同资金方式，不客户类型下的交易数量。

select count(1),a.c_capitalmode , b.c_custtype from trequest a ,taccoinfo b where a.vc_tradeacco=b.vc_tradeacco group by a.c_capitalmode,b.c_custtype ;

HAVING

HAVING经常出现在group by 分组语法后面替代where的功能（ WHERE关键字不能为与聚合函数一起使用。）

继续~ 统计不同资金方式，不客户类型下的交易数量。且开户数量大于50。

select count(1),a.c_capitalmode , b.c_custtype from trequest a ,taccoinfo b where a.vc_tradeacco=b.vc_tradeacco group by a.c_capitalmode,b.c_custtype having count(1) >50;

EXISTS

EXISTS运算符用于测试子查询中是否存在任何记录

听着和in有点类似，这里简述exists和in的区别：

EXISTS 用于检查子查询是否返回任何行。如果子查询返回至少一行，EXISTS 返回 TRUE，否则返回 FALSE，

IN 用于检查某个值是否存在于子查询返回的结果集中

由于 EXISTS 只需要判断子查询是否有结果，因此在大多数情况下，EXISTS 的性能优于 IN，尤其是在子查询返回大量数据时

EXISTS 还会去除重复数据。后面案例会详细说明

只关心子查询是否有结果时，使用 EXISTS，select 1 这里只是用来显示，有结果，实际通过department_id 来关联，内外层的逻辑查询

SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

当你需要检查某个值是否在子查询返回的结果集中时，使用 IN。
适用于子查询返回的结果集较小或静态列表的情况。

SELECT * FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location_id = 1700
);

EXISTS 不会受到 NULL 值的影响，因为它只关心子查询是否有结果。
EXISTS 更适合用于判断子查询是否有结果，尤其是在子查询返回大量数据时，性能较好。
IN 更适合用于检查某个值是否在子查询返回的结果集中，尤其是在结果集较小或静态列表时

比如：提供一种价格为 22 的产品的供应商的名称。

这里select ProductName 可以用 select 1表示，也可以用其他字段，仅表示存在查询结果

SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

执行结果

对于 供应商 A（SupplierID = 1）：
- 内层查询返回 产品 1（ProductName = 产品 1，Price = 22）。
- 因此，供应商 A 将被包含在外层查询的结果中。
对于 供应商 B（SupplierID = 2）：
- 内层查询返回 产品 3（ProductName = 产品 3，Price = 22）。
- 因此，供应商 B 将被包含在外层查询的结果中。
对于 供应商 C（SupplierID = 3）：
- 内层查询不返回任何产品，因为没有价格为 22 的产品。
- 因此，供应商 C 不会被包含在外层查询的结果中。

当产品表中的id重复时候，普通关联查询出来的结果会重复。如果使用普通关联查询，需要搭配groupby 进行分组去重，

GROUP BY子句将数据按指定列分组，聚合函数对每个分组应用操作，确保每个分组只返回一行结果。
即使Products表中的SupplierID有重复值，最终结果中的SupplierName也不会重复。

SELECT SupplierName, MIN(Price) AS LowestPrice
FROM Suppliers
JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE Products.Price > 10
GROUP BY SupplierName;

CASE

CASE 表达式用于根据条件返回不同的值，类似于编程语言中的 if-else 语句

语法：

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END

比如：

SELECT employee_id, first_name, last_name,
CASE department_id
WHEN 10 THEN 'Administration'
WHEN 20 THEN 'Marketing'
WHEN 30 THEN 'Purchasing'
ELSE 'Other'
END AS department_name
FROM employees;

expression 是要比较的值或表达式。
WHEN 子句用于匹配 expression 的值。
THEN 子句指定匹配成功时的返回值。
ELSE 子句是可选的，用于指定所有条件不匹配时的默认返回值。

拓展： case 可用于查询搜素中 select 后面，

SELECT employee_id, first_name, last_name, salary,
       CASE
           WHEN salary > 10000 THEN 'High'
           WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

实例：

select count(1) as 开户数量, t.c_custtype from tcustinfo t group by t.c_custtype

查询出来的数据如下，如果客户要求翻译出数据中的1 0 2 分别代表的中文描述。

我们可以通过case来实现，

select count(1) as 开户数量,

case

when t.c_custtype =1 then '个人'
when t.c_custtype =2 then '机构'
when t.c_custtype =0 then '产品'
end as 类型

from tcustinfo t group by t.c_custtype

也可以嵌套，处理复杂逻辑，

SELECT employee_id, first_name, last_name, salary,
       CASE
           WHEN salary > 10000 THEN 'High'
           WHEN salary BETWEEN 5000 AND 10000 THEN 
               CASE
                   WHEN commission_pct IS NOT NULL THEN 'Medium with Commission'
                   ELSE 'Medium'
               END
           ELSE 'Low'
       END AS salary_level
FROM employees;

可以用在update语句，

UPDATE employees
SET salary = CASE
WHEN department_id = 10 THEN salary * 1.1
WHEN department_id = 20 THEN salary * 1.05
ELSE salary
END;

用于分组，

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY CASE
             WHEN salary > 10000 THEN 1
             WHEN salary BETWEEN 5000 AND 10000 THEN 2
             ELSE 3
         END;

搭配having ，实现自定义过滤

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > CASE
                         WHEN department_id = 10 THEN 8000
                         WHEN department_id = 20 THEN 7000
                         ELSE 5000
                     END;

Oracle 的 CASE 表达式功能强大，适用于 SELECT、UPDATE、ORDER BY、GROUP BY 和 HAVING 等场景，能够处理复杂的条件逻辑

NVL 处理NULL函数

NVL()函数是一个非常常用的函数，主要用于处理NULL值

NVL(expr1, expr2)

expr1：这是需要检查是否为NULL的表达式。
expr2：如果expr1为NULL，则返回expr2的值，如果expr1的值不为NULL，则返回expr1的值。

SELECT employee_id, NVL(email, '未知') AS email
FROM employees;

如果email为NULL，则返回'未知'。

SELECT employee_id, NVL(salary, 0) AS salary
FROM employees;

如果salary为NULL，则返回0。

nvl常用于计算中，

SELECT product_id,
price * NVL(discount, 0) AS total_price
FROM sales;

如果discount为NULL，则NVL(discount, 0)会返回0，从而避免计算错误。

NVL()函数只能处理两个参数，不支持多个参数。

PROCEDURE 存储过程

存储过程是可以保存的准备好的SQL代码，因此代码可以重复使用，还可以将参数传递给存储过程，以便存储过程可以根据参数值执行操作

语法：

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

执行存储过程

EXEC procedure_name;

SQL约束

SQL约束用于为表中的数据指定规则。

约束可以是列级或表级。列级约束应用于列，表级约束应用于整个表。

SQL中常用的约束如下:

NOT NULL-确保列不能具有NULL值
UNIQUE-确保列中的所有值都是不同的
PRIMARY KEY-一个组合的 NOT NULL和UNIQUE。唯一标识表中的每一行
FOREIGN KEY -防止破坏表之间链接的操作
CHECK-确保列中的值满足特定条件
DEFAULT-设置列的默认值如果没有值已指定
CREATE INDEX-用于从数据库中创建和检索数据非常快

未完待续 20250208

进阶sql

从 taccoinfo 表中筛选出 vc_address 包含 || 但不包含 境外|| 的记录，然后将这些记录的 vc_tradeacco 和 vc_address 拼接成一个字符串，并统计符合条件的记录数

SELECT LISTAGG('交易账号：' || vc_tradeacco || ' 地址：' || vc_address, ', ') WITHIN GROUP (ORDER BY vc_tradeacco) AS RETURNMSG,
       COUNT(1) AS count_num
FROM taccoinfo t
WHERE t.vc_address LIKE '%||%'
  AND t.vc_address NOT LIKE '%境外||%';

oracle 基础语法复习记录

Oracle SQL基础

学习范围

学习SQL基础语法

进阶学习

SQL基础语法

select

insert

null not null

update

delete

聚合函数

`AVG()`函数返回数值列的平均值

`SUM()`函数返回数值列的总和

`COUNT()`函数返回与指定条件匹配的行

LIKE运算符

IN运算符

`BETWEEN`运算符

SQL别名

JION联接

内连接：

左连接

FULL JOIN

SQL自联接

`UNION`

GROUP BY 分组

HAVING

EXISTS

CASE

NVL 处理NULL函数

PROCEDURE 存储过程

SQL约束

网站公告

今日签到

热门文章

最新发布

oracle 基础语法复习记录

Oracle SQL基础

学习范围

学习SQL基础语法

进阶学习

SQL基础语法

select

insert

null not null

update

delete

聚合函数

AVG()函数返回数值列的平均值

SUM()函数返回数值列的总和

COUNT()函数返回 与指定条件匹配的行

LIKE运算符

IN运算符

BETWEEN运算符

SQL别名

JION联接

内连接：

左连接

FULL JOIN

SQL自联接

UNION

GROUP BY 分组

HAVING

EXISTS

CASE

NVL 处理NULL函数

PROCEDURE 存储过程

SQL约束

网站公告

今日签到

热门文章

最新发布

`AVG()`函数返回数值列的平均值

`SUM()`函数返回数值列的总和

`COUNT()`函数返回与指定条件匹配的行

`BETWEEN`运算符

`UNION`