oracle 基础语法复习记录

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 dWHERE d.department_id = e.department_idAND 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;

进阶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 '%境外||%';