那些年遇到的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相关