数据处理---那些年遇到的SQL汇总

     那些年遇到的SQL问题真的不少，各种类型的，有面试的时候遇到的SQL，有开发的时候遇到的，有性能优化的时候遇到的，还有在网上看到的各种资料的，林林总总，问题不少，这里做个汇总，从以下几个方面说下：1，各种SQL语句；2索引和SQL性能Explain；3 JOIN相关，为了忘却的的纪念：

1，各种常见SQL语句

• 各种Join：内联接，外联接(LEFT OUTER JOIN,RIGHT  OUTER  JOIN,FULL OUTER JOIN)，交叉联接(笛卡尔积)

 

SELECT a.*,b.*  FROM bbb b,aaa a;SELECT a.*,b.*  FROM aaa a CROSS JOIN  bbb b;
SELECT * FROM aaa a NATURAL INNER JOIN bbb b;
     A，B两个表列名完全不同，上面的结果是相同的。注意第三个NATURAL INNER JOIN如果列名有相同，结果大不相同。
    有这么一个题目：一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，用一条sql 语句显示所有可能的比赛组合。
    答：select a.name, b.name from team a, team b where a.name < b.name

 

• 行列转换

SELECT a.name,SUM(CASE WHEN a.subject='语文' THEN a.score ELSE 0 END) AS 语文,SUM(CASE WHEN a.subject='数学' THEN a.score ELSE 0 END) AS 数学,SUM(CASE WHEN a.subject='英语' THEN a.score ELSE 0 END) AS 英语from  a_student a GROUP BY a.name;
SELECT NAME, (select score from a_student m where m.subject='语文' and m.name=a.name) as 语文,(select score from a_student m where m.subject='数学' and m.name=a.name) as 数学,(select score from a_student m where m.subject='英语' and m.name=a.name) as 英语from a_student a  group by NAME；
    这个例子不多说了，关键是要明白group 之后是个什么样的结果，再来做些处理，顺便看下case语句的写法。可能还有其他的写法，另外有pivot table的用法。

• 删除重复数据

 

      很明显，第5条记录和第一条重复了，删除表中多余的重复记录（多个字段，例如name和subject），只留有rowid最小的记录:
delete from a_student a where (a.name,a.subject) in (select NAME,subject from a_student group by NAME,subject having count(*) > 1) and rowid not in (select min(rowid) from a_student group by NAME,subject having count(*)>1)

 

• 树形结构数据查询

  树形结构的一般是用with 做个临时表，然后父数据和子数据做个内连接，把临时表查询出来；

 

  当然写存储过程也可以实现，或者用oracle 的start with condition   connect by prior的语句也可以；

 

2，索引和SQL性能Explain

  造了两张表，表结构完全相同TEST_A100和TEST_A1000，只是数据量不同，前者塞入100万数据，后者1000万，后面的区域表大约1万条记录
create table TEST_A1000 (
  ID       number not null,
  NAME     varchar2(32),
  CITY     number,
  PROVINCE number,
  DOB      date
);

 

建立区域表和序列：

 

 create table TEST_AREA (
  ID       number not null,
  NAME     varchar2(32),
  PROV_ID  number not null,
  detail   varchar2(2000)
);
create sequence SEQ_TESTAREA
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 1000;

 

create or replace procedure PROC_ISTAS
begin
  for i in 1..10000000 LOOP
    insert into TEST_A1000 SELECT SEQ_TESTA.Nextval,DBMS_RANDOM.STRING('A', 32),TRUNC(DBMS_RANDOM.VALUE(100, 1100)),TRUNC(DBMS_RANDOM.VALUE(0, 100)),to_date(2451984+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual ;
    IF MOD(i,10000)=0 THEN
      --dbms_output.put_line('执行开始,时间: ' || sysdate); 
      commit;
    END IF;
  end loop;
end;
begin
  PROC_IST;
end;
也可以这样塞入数据(使用随借函数和connect by)：
insert into TEST_A100 SELECT SEQ_TESTA.Nextval,DBMS_RANDOM.STRING('A', 32),TRUNC(DBMS_RANDOM.VALUE(100, 1100)),TRUNC(DBMS_RANDOM.VALUE(0, 100)),to_date(2451984+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual connect by level <= 1000000;
insert into TEST_A1000 SELECT SEQ_TESTA.Nextval,DBMS_RANDOM.STRING('A', 32),TRUNC(DBMS_RANDOM.VALUE(100, 1100)),TRUNC(DBMS_RANDOM.VALUE(0, 100)),to_date(2451984+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual connect by level <= 10000000;

insert into TEST_AREA SELECT SEQ_TESTAREA.Nextval,DBMS_RANDOM.STRING('A', 32),TRUNC(DBMS_RANDOM.VALUE(0, 100)),DBMS_RANDOM.STRING('A', 1000) from dual connect by level <= 1100;
 
在表上面添加主键和索引：
alter table TEST_A1000
  add constraint PK_TEST primary key (ID)
  using index ;
-- Create/Recreate indexes 
create index IDX_NAME on TEST_A1000 (NAME);
分别分析下面的SQL语句：
SELECT COUNT(*) FROM TEST_A1000 a WHERE a.name IS NOT NULL;
--IS NOT NULL 走索引，IS NULL不走索引；
SELECT * FROM TEST_A1000 a WHERE a.name!='EnWYrMbvoWfBJSzoOevCfLQVAgIaNeSj'; ----不等于不走索引

 select * from TEST_A1000 where ID=10 or ID=20;   --不走索引
 select * from TEST_A1000 where ID IN (10,20,30);  --走索引，如果ID不加主键也不走
 select * from TEST_A1000 where NAME IN ('EnWYrMbvoWfBJSzoOevCfLQVAgIaNeSj');  --走索引
 select * from TEST_A1000 where ID BETWEEN 10 AND 30;       --走索引
 select * from TEST_A1000 where ID/30=100;               --走索引
 select * from TEST_A1000 where substr(NAME,0,3)='EnW';       --不走索引
 其他的图片没有一一贴出来，总之，不同的数据库，不同的数据库版本，不同的测试场景，可能结果都有所不同，不能道听途说，不能人云亦云，绝知此事要躬行！

3，JOIN相关