疯狂自增记录 truncate table，case实现横排，复制表结构

最新推荐文章于 2023-01-21 11:18:26 发布

最新推荐文章于 2023-01-21 11:18:26 发布 · 147 阅读

本文通过实例展示了如何使用SQL进行快速数据填充、DELETE与TRUNCATE TABLE的区别、利用CASE语句实现数据汇总以及复制表结构的方法。这些技巧对于数据库管理和优化非常实用。

疯狂自增记录~

delete 和 truncate table对比

SQL> create table tb(i number);-- Table created. Elapsed: 00:00:00.09 SQL> insert into tb values(1); 1 row created. Elapsed: 00:00:00.01 SQL> insert into tb 2 select * from tb; 1 row created. Elapsed: 00:00:00.01 SQL> / 2 rows created. Elapsed: 00:00:00.01 SQL> / 4 rows created. Elapsed: 00:00:00.00 SQL> / 8 rows created. Elapsed: 00:00:00.01 SQL> / 16 rows created. Elapsed: 00:00:00.01 SQL> / 32 rows created. Elapsed: 00:00:00.01 SQL> / 64 rows created. Elapsed: 00:00:00.00 SQL> / 128 rows created. Elapsed: 00:00:00.01 SQL> / 256 rows created. Elapsed: 00:00:00.01 SQL> / 512 rows created. Elapsed: 00:00:00.00 SQL> / 1024 rows created. Elapsed: 00:00:00.01 SQL> / 2048 rows created. Elapsed: 00:00:00.03 SQL> select count(*) from tb; COUNT(*) ---------- 4096 Elapsed: 00:00:00.01 SQL> savepoint sp1; Savepoint created. Elapsed: 00:00:00.00 SQL> delete tb; 4096 rows deleted. Elapsed: 00:00:00.06 SQL> select * from tb; no rows selected Elapsed: 00:00:00.01 SQL> rollback to sp1; Rollback complete. Elapsed: 00:00:00.04 SQL> edit Wrote file afiedt.buf 1* select count(*) from tb SQL> / COUNT(*) ---------- 4096 Elapsed: 00:00:00.01 SQL> savepoint sp1; Savepoint created. Elapsed: 00:00:00.01 SQL> truncate table tb; Table truncated. Elapsed: 00:00:00.03 Elapsed: 00:00:00.01 SQL> rollback to sp1; rollback to sp1 * ERROR at line 1: ORA-01086: savepoint 'SP1' never established Elapsed: 00:00:00.01 SQL> spool off

用Case实现横排

因为分组查询中，前面查询的字段除了用来分组的字段外，都必须用聚合函数，否则系统不知道该取同一组数据中的哪一个

SQL> edit Wrote file afiedt.buf 1 create table testCase( 2 name varchar2(20), 3 subject varchar(20), 4* mark number(3,1)) SQL> / Table created. Elapsed: 00:00:00.01 SQL> insert into testCase 2 values('ymc','OS',61); 1 row created. Elapsed: 00:00:00.00 SQL> edit Wrote file afiedt.buf 1 insert into testCase 2* values('ymc','DS',62) SQL> / 1 row created. Elapsed: 00:00:00.01 SQL> edit Wrote file afiedt.buf 1 insert into testCase 2* values('ymc','DB',63) SQL> / 1 row created. Elapsed: 00:00:00.01 SQL> edit Wrote file afiedt.buf 1 insert into testCase 2* values('tingting','OS',91) SQL> / 1 row created. Elapsed: 00:00:00.00 SQL> edit Wrote file afiedt.buf 1 insert into testCase 2* values('tingting','DS',92) SQL> / 1 row created. Elapsed: 00:00:00.01 SQL> edit Wrote file afiedt.buf 1 insert into testCase 2* values('tingting','DB',99) SQL> / 1 row created. Elapsed: 00:00:00.00 SQL> select * from testCase; NAME SUBJECT MARK -------------------- -------------------- ---------- ymc OS 61 ymc DS 62 ymc DB 63 tingting OS 91 tingting DS 92 tingting DB 99 6 rows selected. Elapsed: 00:00:00.06 SQL> edit Wrote file afiedt.buf 1 select Name, 2 sum(case when Subject='OS' then mark end) as OS, 3 sum(case when Subject='DS' then mark end) as DS, 4 sum(case when Subject='DB' then mark end) as DB 5 from testCase 6* group by Name SQL> / NAME OS DS DB -------------------- ---------- ---------- ---------- tingting 91 92 99 ymc 61 62 63 Elapsed: 00:00:00.03 SQL> spool off

复制表结构 where 1=2

SQL> select count(*) from testCase; COUNT(*) ---------- 7 Elapsed: 00:00:00.01 SQL> desc testCase; Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(20) SUBJECT VARCHAR2(20) MARK NUMBER(3,1) SQL> edit Wrote file afiedt.buf 1 create table dupTestCase 2 as 3 select * 4 from testCase 5* where 1=2 SQL> / Table created. Elapsed: 00:00:00.07 SQL> select * from dupTestCase; no rows selected Elapsed: 00:00:00.00 SQL> desc dupTestCase; Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(20) SUBJECT VARCHAR2(20) MARK NUMBER(3,1) SQL> spool off