Oracle SQL精妙SQL语句讲解

最新推荐文章于 2024-09-21 22:02:19 发布
最新推荐文章于 2024-09-21 22:02:19 发布
阅读量579 收藏
点赞数
文章标签： sql oracle table insert user 算法
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hailang99/article/details/1789751
版权

 

 

 

 

-- 行列转换 行转列
DROP   TABLE  t_change_lc;
 CREATE   TABLE  t_change_lc (card_code  VARCHAR2 ( 3 ), q  NUMBER , bal  NUMBER );

 INSERT   INTO  t_change_lc 
 SELECT   ' 001 '  card_code, ROWNUM q, trunc(dbms_random.VALUE  *   100 ) bal  FROM  dual CONNECT  BY  ROWNUM  <=   4
UNION  
 SELECT   ' 002 '  card_code, ROWNUM q, trunc(dbms_random.VALUE  *   100 ) bal  FROM  dual CONNECT  BY  ROWNUM  <=   4 ;

 SELECT   *   FROM  t_change_lc;

 SELECT  a.card_code,
 SUM (decode(a.q,  1 , a.bal,  0 )) q1,
 SUM (decode(a.q,  2 , a.bal,  0 )) q2,
 SUM (decode(a.q,  3 , a.bal,  0 )) q3,
 SUM (decode(a.q,  4 , a.bal,  0 )) q4
 FROM  t_change_lc a
 GROUP   BY  a.card_code
 ORDER   BY   1 ;

 -- 行列转换 列转行
DROP   TABLE  t_change_cl;
 CREATE   TABLE  t_change_cl  AS  
 SELECT  a.card_code,
 SUM (decode(a.q,  1 , a.bal,  0 )) q1,
 SUM (decode(a.q,  2 , a.bal,  0 )) q2,
 SUM (decode(a.q,  3 , a.bal,  0 )) q3,
 SUM (decode(a.q,  4 , a.bal,  0 )) q4
 FROM  t_change_lc a
 GROUP   BY  a.card_code
 ORDER   BY   1 ;

 SELECT   *   FROM  t_change_cl;

 SELECT  t.card_code,
t.rn q,
decode(t.rn,  1 , t.q1,  2 , t.q2,  3 , t.q3,  4 , t.q4) bal
 FROM  ( SELECT  a. * , b.rn
 FROM  t_change_cl a,
( SELECT  ROWNUM rn  FROM  dual CONNECT  BY  ROWNUM  <=   4 ) b) t
 ORDER   BY   1 ,  2 ;

 -- 行列转换 行转列 合并
DROP   TABLE  t_change_lc_comma;
 CREATE   TABLE  t_change_lc_comma  AS   SELECT  card_code, ' quarter_ ' || q  AS  q  FROM  t_change_lc; 

 SELECT   *   FROM  t_change_lc_comma;

 SELECT  t1.card_code, substr( MAX (sys_connect_by_path(t1.q,  ' ; ' )),  2 ) q
 FROM  ( SELECT  a.card_code,
a.q,
row_number()  over (PARTITION  BY  a.card_code  ORDER   BY  a.q) rn
 FROM  t_change_lc_comma a) t1
START  WITH  t1.rn  =   1
CONNECT  BY  t1.card_code  =  PRIOR t1.card_code
 AND  t1.rn  -   1   =  PRIOR t1.rn
 GROUP   BY  t1.card_code;

 -- 行列转换 列转行 分割
DROP   TABLE  t_change_cl_comma;
 CREATE   TABLE  t_change_cl_comma  AS
SELECT  t1.card_code, substr( MAX (sys_connect_by_path(t1.q,  ' ; ' )),  2 ) q
 FROM  ( SELECT  a.card_code,
a.q,
row_number()  over (PARTITION  BY  a.card_code  ORDER   BY  a.q) rn
 FROM  t_change_lc_comma a) t1
START  WITH  t1.rn  =   1
CONNECT  BY  t1.card_code  =  PRIOR t1.card_code
 AND  t1.rn  -   1   =  PRIOR t1.rn
 GROUP   BY  t1.card_code;

 SELECT   *   FROM  t_change_cl_comma;

 SELECT  t.card_code,
substr(t.q,
instr( ' ; '   ||  t.q,  ' ; ' ,  1 , rn),
instr(t.q  ||   ' ; ' ,  ' ; ' ,  1 , rn)  -  instr( ' ; '   ||  t.q,  ' ; ' ,  1 , rn)) q
 FROM  ( SELECT  a.card_code, a.q, b.rn
 FROM  t_change_cl_comma a,
( SELECT  ROWNUM rn  FROM  dual CONNECT  BY  ROWNUM  <=   100 ) b
 WHERE  instr( ' ; '   ||  a.q,  ' ; ' ,  1 , rn)  >   0 ) t
 ORDER   BY   1 ,  2 ;


 --  实现一条记录根据条件多表插入
DROP   TABLE  t_ia_src;
 CREATE   TABLE  t_ia_src  AS   SELECT   ' a ' || ROWNUM c1,  ' b ' || ROWNUM c2  FROM  dual CONNECT  BY  ROWNUM <= 5 ;
 DROP   TABLE  t_ia_dest_1;
 CREATE   TABLE  t_ia_dest_1(flag  VARCHAR2 ( 10 ) , c  VARCHAR2 ( 10 ));
 DROP   TABLE  t_ia_dest_2;
 CREATE   TABLE  t_ia_dest_2(flag  VARCHAR2 ( 10 ) , c  VARCHAR2 ( 10 ));
 DROP   TABLE  t_ia_dest_3;
 CREATE   TABLE  t_ia_dest_3(flag  VARCHAR2 ( 10 ) , c  VARCHAR2 ( 10 ));

 SELECT   *   FROM  t_ia_src; 
 SELECT   *   FROM  t_ia_dest_1;
 SELECT   *   FROM  t_ia_dest_2;
 SELECT   *   FROM  t_ia_dest_3;

 INSERT   ALL
WHEN  (c1  IN  ( ' a1 ' , ' a3 ' ))  THEN  
 INTO  t_ia_dest_1(flag,c)  VALUES (flag1,c2)
 WHEN  (c1  IN  ( ' a2 ' , ' a4 ' ))  THEN  
 INTO  t_ia_dest_2(flag,c)  VALUES (flag2,c2)
 ELSE
INTO  t_ia_dest_3(flag,c)  VALUES (flag1 || flag2,c1 || c2)
 SELECT  c1,c2,  ' f1 '  flag1,  ' f2 '  flag2  FROM  t_ia_src;

 --  如果存在就更新，不存在就插入用一个语句实现
DROP   TABLE  t_mg;
 CREATE   TABLE  t_mg(code  VARCHAR2 ( 10 ), NAME  VARCHAR2 ( 10 ));

 SELECT   *   FROM  t_mg;

MERGE  INTO  t_mg a
USING ( SELECT   ' the code '  code,  ' the name '  NAME  FROM  dual) b
 ON  (a.code  =  b.code)
 WHEN  MATCHED  THEN
UPDATE   SET  a.NAME  =  b.NAME
 WHEN   NOT  MATCHED  THEN
INSERT  (code, NAME)  VALUES  (b.code, b.NAME);

 --  抽取/删除重复记录 
DROP   TABLE  t_dup;
 CREATE   TABLE  t_dup  AS   SELECT   ' code_ ' || ROWNUM code, dbms_random.string( ' z ' , 5 ) NAME  FROM  dual CONNECT  BY  ROWNUM <= 10 ; 
 INSERT   INTO  t_dup  SELECT   ' code_ ' || ROWNUM code, dbms_random.string( ' z ' , 5 ) NAME  FROM  dual CONNECT  BY  ROWNUM <= 2 ; 

 SELECT   *   FROM  t_dup;

 SELECT   *   FROM  t_dup a  WHERE  a.ROWID  <>  ( SELECT   MIN (b.ROWID)  FROM  t_dup b  WHERE  a.code = b.code);

 SELECT  b.code, b.NAME
 FROM  ( SELECT  a.code,
a.NAME,
row_number()  over (PARTITION  BY  a.code  ORDER   BY  a.ROWID) rn
 FROM  t_dup a) b
 WHERE  b.rn  >   1 ;

 --  IN/EXISTS的不同适用环境
--  t_orders.customer_id有索引
SELECT  a. *
FROM  t_employees a
 WHERE  a.employee_id  IN
( SELECT  b.sales_rep_id  FROM  t_orders b  WHERE  b.customer_id  =   12 );

 SELECT  a. *
FROM  t_employees a
 WHERE   EXISTS  ( SELECT   1
FROM  t_orders b
 WHERE  b.customer_id  =   12
AND  a.employee_id  =  b.sales_rep_id);

 --  t_employees.department_id有索引
SELECT  a. *
FROM  t_employees a
 WHERE  a.department_id  =   10
AND   EXISTS
( SELECT   1   FROM  t_orders b  WHERE  a.employee_id  =  b.sales_rep_id);

 SELECT  a. *
FROM  t_employees a
 WHERE  a.department_id  =   10
AND  a.employee_id  IN  ( SELECT  b.sales_rep_id  FROM  t_orders b);

 --  FBI
DROP   TABLE  t_fbi;
 CREATE   TABLE  t_fbi  AS
SELECT  ROWNUM rn, dbms_random.STRING( ' z ' , 10 ) NAME , SYSDATE  +  dbms_random.VALUE  *   10  dt  FROM  dual 
CONNECT  BY  ROWNUM  <= 10 ; 

 CREATE   INDEX  idx_nonfbi  ON  t_fbi(dt);

 DROP   INDEX  idx_fbi_1;
 CREATE   INDEX  idx_fbi_1  ON  t_fbi(trunc(dt));

 SELECT   *   FROM  t_fbi  WHERE  trunc(dt)  =  to_date( ' 2006-09-21 ' , ' yyyy-mm-dd ' ) ;

 --  不建议使用
SELECT   *   FROM  t_fbi  WHERE  to_char(dt,  ' yyyy-mm-dd ' )  =   ' 2006-09-21 ' ;

 --  LOOP中的COMMIT/ROLLBACK
DROP   TABLE  t_loop PURGE;
 create   TABLE  t_loop  AS   SELECT   *   FROM  user_objects  WHERE   1 = 2 ;

 SELECT   *   FROM  t_loop;

 --  逐行提交
DECLARE
BEGIN
FOR  cur  IN  ( SELECT   *   FROM  user_objects) LOOP
 INSERT   INTO  t_loop  VALUES  cur;
 COMMIT ;
 END  LOOP;
 END ;

 --  模拟批量提交
DECLARE
v_count  NUMBER ;
 BEGIN
FOR  cur  IN  ( SELECT   *   FROM  user_objects) LOOP
 INSERT   INTO  t_loop  VALUES  cur;
v_count : =  v_count  +   1 ;
 IF  v_count  >=   100   THEN
COMMIT ;
 END   IF ;
 END  LOOP;
 COMMIT ;
 END ;

 --  真正的批量提交
DECLARE
CURSOR  cur  IS
SELECT   *   FROM  user_objects;
TYPE rec  IS   TABLE   OF  user_objects % ROWTYPE;
recs rec;
 BEGIN
OPEN  cur;
 WHILE  (TRUE) LOOP
 FETCH  cur  BULK  COLLECT
 INTO  recs LIMIT  100 ;
 --  forall 实现批量
FORALL i  IN   1  .. recs. COUNT
INSERT   INTO  t_loop  VALUES  recs (i);
 COMMIT ;
 EXIT   WHEN  cur % NOTFOUND;
 END  LOOP;
 CLOSE  cur;
 END ;

 --  悲观锁定/乐观锁定 
DROP   TABLE  t_lock PURGE;
 CREATE   TABLE  t_lock  AS   SELECT   1  ID  FROM  dual;

 SELECT   *   FROM  t_lock;

 --  常见的实现逻辑，隐含bug
DECLARE
v_cnt  NUMBER ;
 BEGIN
--  这里有并发性的bug
SELECT   MAX (ID)  INTO  v_cnt  FROM  t_lock;

 --  here for other operation
v_cnt : =  v_cnt  +   1 ;
 INSERT   INTO  t_lock (ID)  VALUES  (v_cnt);
 COMMIT ;
 END ;

 --  高并发环境下，安全的实现逻辑
DECLARE
v_cnt  NUMBER ;
 BEGIN
--  对指定的行取得lock
SELECT  ID  INTO  v_cnt  FROM  t_lock  WHERE  ID = 1   FOR   UPDATE ;
 --  在有lock的情况下继续下面的操作
SELECT   MAX (ID)  INTO  v_cnt  FROM  t_lock;

 --  here for other operation
v_cnt : =  v_cnt  +   1 ;
 INSERT   INTO  t_lock (ID)  VALUES  (v_cnt);
 COMMIT ;  -- 提交并且释放lock
END ;

 --  硬解析/软解析
DROP   TABLE  t_hard PURGE;
 CREATE   TABLE  t_hard (ID  INT );

 SELECT   *   FROM  t_hard;

 DECLARE
sql_1  VARCHAR2 ( 200 );
 BEGIN
--  hard parse
--  java中的同等语句是 Statement.execute()
FOR  i  IN   1  ..  1000  LOOP
sql_1 : =   ' insert into t_hard(id) values( '   ||  i  ||   ' ) ' ;
 EXECUTE  IMMEDIATE sql_1;
 END  LOOP;
 COMMIT ;

 --  soft parse
-- java中的同等语句是 PreparedStatement.execute()
sql_1 : =   ' insert into t_hard(id) values(:id) ' ;
 FOR  i  IN   1  ..  1000  LOOP
 EXECUTE  IMMEDIATE sql_1
USING i;
 END  LOOP;
 COMMIT ;
 END ;



 --  正确的分页算法 
SELECT   *
FROM  ( SELECT  a. * , ROWNUM rn
 FROM  ( SELECT   *   FROM  t_employees  ORDER   BY  first_name) a
 WHERE  ROWNUM  <=   500 )
 WHERE  rn  >   480  ;

 --  分页算法(why not this one)
SELECT  a. * , ROWNUM rn
 FROM  ( SELECT   *   FROM  t_employees  ORDER   BY  first_name) a
 WHERE  ROWNUM  <=   500   AND  ROWNUM  >   480 ;

 --  分页算法(why not this one)
SELECT  b. *
FROM  ( SELECT  a. * , ROWNUM rn
 FROM  t_employees a
 WHERE  ROWNUM  <   =   500
ORDER   BY  first_name) b
 WHERE  b.rn  >   480 ;

 --  OLAP
--  小计合计
SELECT   CASE
WHEN  a.deptno  IS   NULL   THEN
' 合计 '
WHEN  a.deptno  IS   NOT   NULL   AND  a.empno  IS   NULL   THEN
' 小计 '
ELSE
''   ||  a.deptno
 END  deptno,
a.empno,
a.ename,
 SUM (a.sal) total_sal
 FROM  scott.emp a
 GROUP   BY   GROUPING  SETS((a.deptno),(a.deptno, a.empno, a.ename),());

 --  分组排序
SELECT  a.deptno,
a.empno,
a.ename,
a.sal,
 --  可跳跃的rank
rank()  over (PARTITION  BY  a.deptno  ORDER   BY  a.sal  DESC ) r1,
 --  密集型rank
dense_rank()  over (PARTITION  BY  a.deptno  ORDER   BY  a.sal  DESC ) r2,
 --  不分组排序
rank()  over ( ORDER   BY  sal  DESC ) r3
 FROM  scott.emp a
 ORDER   BY  a.deptno,a.sal  DESC ;

 --  当前行数据和前/后n行的数据比较
SELECT  a.empno,
a.ename,
a.sal,
 --  上面一行
lag(a.sal)  over ( ORDER   BY  a.sal  DESC ) lag_1,
 --  下面三行
lead(a.sal,  3 )  over ( ORDER   BY  a.sal  DESC ) lead_3
 FROM  scott.emp a
 ORDER   BY  a.sal  DESC ;
 
hailang99
关注
OracleSQL精妙SQL语句讲解[定义].pdf
10-11
子查询是嵌套在其他SQL语句中的查询，可以作为查询的一部分提供临时结果。 Oracle支持多种数据类型，如： - `NUMBER`：用于数值存储，可以存储整数和小数。 - `VARCHAR2`：可变长度的字符串，最大长度为2000字符。 ...
Oracle+SQL精妙SQL语句讲解.txt
03-21
根据提供的文件信息，我们可以归纳出以下几个关键的知识点： ### 1. 获取当前月份的开始日期与结束...以上是对给定文档中几个主要知识点的总结与解释，希望能帮助你更好地理解和掌握这些Oracle SQL语句的使用方法。
SQL精妙语句讲解(Oracle)
12-02
在Oracle SQL中，获取当前日期所在月份的第一天和最后一天是常见的需求之一。这里提供了两种方法来实现这一功能。 #### 方法一：获取下个月初与下个月末 ```sql SELECT TRUNC(TRUNC(SYSDATE, 'DAY') + 1, 'MONTH') ...
精妙SQL语句.txt
10-10
### SQL语句的高级应用 #### 1. 条件筛选与逻辑判断 在SQL中，`1<>1`这种写法实际上永远不会返回任何结果，因为`1<>1`（即`1!=1`）总是假的。这通常被用作一种技巧，在`WHERE`子句中排除所有行，从而使查询不返回...
Oracle精妙的sql语句
01-16
以下是对各部分提到的SQL语句及其功能的详细解析： ### 1. 假数据插入：`SELECT * INTO b FROM a WHERE 1<>1` 这一语句看似简单，实则巧妙地用于创建一个空表结构而不插入任何数据。由于`WHERE 1<>1`条件永远不...
SQL - 进阶语法（一）
qq_51222843的博客
09-21 344
关于SQL的进阶语法教程，包括简单合并数据库，修改数据库内容等操作
web基础—dvwa靶场（八）SQL Injection(Blind)
m0_74080781的博客
09-18 1218
SQL Injection(Blind)，SQL盲注，相比于常规的SQL注入，他不会将返回具体的数据信息或语法信息，只会将服务器包装后的信息返回到页面中。
text2sql(NL2Sql)综述《The Dawn of Natural Language to SQL: Are We Fully Ready?》
beingstrong的博客
09-15 1067
text2sql(NL2Sql)综述《The Dawn of Natural Language to SQL: Are We Fully Ready?》详细笔记
mysql学习教程，从入门到精通，SQL 删除数据（DELETE 语句）（18）
qq_45746668的博客
09-18 1022
在编写SQL中的`DELETE`语句时，需要非常小心，因为一旦执行，被删除的数据就无法恢复了（除非你有备份）。`DELETE`语句用于从数据库表中移除一条或多条记录。这里，我将提供一个基本的`DELETE`语句的示例，并解释如何安全地使用它。
SpringBlade dict-biz/list 接口 SQL 注入漏洞
为了低调的博客
09-15 495
在 SpringBlade 框架中，如果接口的后台处理逻辑没有正确地对用户输入进行过滤或参数化查询（Prepared Statements），那么可能会存在 SQL 注入的漏洞。SQL 注入是一种安全漏洞，攻击者可以利用它向应用程序的数据库查询中插入或“注入”恶意的 SQL 代码片段。
SqlDb_.cs 与 csharp_SqlDb.h
weixin_42944928的博客
09-16 524
【代码】SqlDb_.cs 与 csharp_SqlDb.h。
快速理解sql数据库（一）（sql关键字优先级执行顺序详解）
hs_1024的博客
09-21 630
了解SQL关键字的执行顺序对于编写高效、准确的SQL语句至关重要。通过掌握这些关键字的执行顺序，我们可以更好地理解SQL查询的逻辑，优化查询性能，并避免一些常见的错误。希望本文能帮助读者更好地理解和使用SQL语言。
技术选型对SQL与NoSQL以及Mysql,Hbase,Hive使用特性差别
Climbman的博客
09-16 1081
数据结构明确且需要严格一致性时。处理大规模非结构化数据时。快速开发和简单应用场景。
mysql学习教程，从入门到精通，SQL 更新数据（UPDATE 语句）（17）
qq_45746668的博客
09-17 1242
SQL `UPDATE` 需要指定要更新的表、要修改的列以及新值，并且通常会通过`WHERE`子句来指定哪些行需要被更新。下面是一个简单的示例，说明如何使用`UPDATE`语句。
关于ClickHouse建表 集群表 SQL
最新发布
托塔天王的博客
09-21 361
如果当前集群节点为2个时,那么我们查询test_all表时,总数量为10000, 在任意一台服务器上查询test本地表,则数量为5000, 在另一台节点查询test本地表数量也为5000,则说明ClickHouse正常.上面的sql 是 生成10000条数据 我们分别去查询一下 test_all集群表的数据 和test表的数据。我这里以test 表 test_all 集群表 为演示 可以执行下面的SQL。坐标 {"x”:222,”y”:33,”w”:44,”h”:44}任务id 关联表主键。
SQL 语法学习指南
qq_42568323的博客
09-21 1111
SQL 是一种用于管理和操作关系数据库的标准语言。它允许用户通过特定的查询从数据库中提取、更新、删除数据，或者对数据库结构进行修改。学习 SQL 不仅仅是掌握一些简单的语法，还要理解其背后的逻辑和原理。在实际工作中，SQL 是非常强大的工具，能够处理大量数据并进行复杂的查询。掌握好基础语法后，你可以进一步探索索引优化、存储过程、事务处理等进阶话题。希望通过这篇文章，你对 SQL 的基础语法有了更清晰的认识，并且能够在日常工作中灵活应用 SQL 进行数据操作。
经典sql题（九）SQL 查询详细指南总结二
m0_58076578的博客
09-17 923
这些步骤展示了 SQL 查询如何逐步处理数据，应用过滤、分组、排序和限制操作，最后返回所需结果。为了演示这条 SQL 查询的执行，可以先定义数据库表架构并插入一些示例数据。结果与 ORDER BY 相同，因为只有 3 行。在这个例子中已经处理。
PostgreSQL技术内幕10：PostgreSQL事务原理解析-日志模块介绍
qq_40529346的博客
09-18 1130
本文将延续上一篇文章内容，上一篇介绍了PG事务涉及到的模块，本文介绍日志模块，主要介绍PG包含的日志分类，和WAL日志、CLOG的详细介绍。
web基础—dvwa靶场（七）SQL Injection
m0_74080781的博客
09-18 885
SQL 注入攻击就是 Web 程序对用户的输入没有进行合法性判断，从而攻击者可以从前端向后端传入攻击参数，并且该参数被带入了后端执行。在很多情况下开发者会使用动态的 SQL 语句，这种语句是在程序执行过程中构造的，不过动态的 SQL 语句很容易被攻击者传入的参数改变其原本的功能。当我们进行手工 SQL 注入判断是否存在注入，注入是字符型还是数字型猜解SQL查询语句中的字段数；确定显示的字段顺序；获取当前数据库；获取数据库中的表；获取表中的字段名；下载数据。
Oracle SQL基础：SELECT语句详解
"Oracle基本sql语句" Oracle数据库是世界上最广泛使用的数据库系统之一，而SQL（结构化查询语言）是与任何关系型数据库交互的基础。本资料主要关注Oracle中的基本SQL SELECT语句，旨在帮助用户快速复习和掌握SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值