SQL笔试之远交近攻

之所以叫远交近攻是因为本片为上一篇的进阶之作，夯实目前的SQL基础，为未来SQL的深入学习做一个承接的作用。

一 连接操作符（实现oracle中的for循环）用于快速删除表操作

SQL> select 'drop table '||tname||' purge;' sqltxt from tab where tname not in ('EMP','DEPT','SALGRADE','BONUS');

SQLTXT

-----------------------------------------------

drop table T4 purge;

二 Rownum 和Rowid的伪列用法

2.1 查询工资倒数第5名到倒数第二名的员工信息：

select *from

(selectrownum row_num,ename,sal,hiredate,deptnofrom (select *from emporderby sal)whererownum <=6)

where row_numbetween2and5 ;

ROW_NUM ENAME SAL HIREDATE DEPTNO

---------- ---------- ---------- -------------- ----------

2 JAMES 950 03-12月-8130

3 ADAMS 1100 23-5月 -8720

4 WARD 1250 22-2月 -8130

5 MARTIN 1250 28-9月 -8130

注意如果使用如下语句会报错：

SQL> select rownum row_num,ename,sal,hiredate,deptno from (select * from emp order by sal) where row_num between 2 and 5;

ORA-00904: "ROW_NUM": 标识符无效

SQL> select rownum,ename,sal,hiredate,deptno from ( select * from (select rownum row_num,ename,sal,hiredate,deptno from (select * from emp order by sal) where rownum <=6)where row_num between 2 and 5);

ROWNUM ENAME SAL HIREDATE DEPTNO

---------- ---------- ------------------------ ----------

1 JAMES 950 03-12月-8130

2 ADAMS 1100 23-5月 -8720

3 WARD 1250 22-2月 -8130

4 MARTIN 125028-9月 -8130

*注意这里的rownum与上例的ROW_NUM伪列的区别。

2.2 删除表中相同的条目

SQL> create table t7 as select * from emp;

表已创建。

SQL> insert into t7 select * from emp where empno=7788;

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t7 where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-4月 -873000 20

7788 SCOTT ANALYST 7566 19-4月 -873000 20

任何的sql操作都有对象性可言，但相同的条目无法区分，这个时候需要借助于ROWID这个特殊列对该表进行操作。

SQL> delete t7 where rowid in (select max(rowid) from t7 where empno in (select empno from t7 group by empno having count(empno) > 1) group by empno);

已删除 1 行。

三 SQL与shell的结合

虽然极少情况能碰到这种情况，但也被撞到了。暂且简单的介绍下！

--查找J开头S结尾的ename列，-i忽略大小写

SQL> select ename from emp where regexp_like(ename,'^J.*s$','i');

ENAME

----------

JONES

JAMES

--替换修改

SQL> select ename,regexp_replace(ename,'ES','Hn') re_name from emp where regexp_like (ename,'J..S');

ENAME RE_NAME

---------- --------------------

JONES JONHn

JAMES JAMHn

四 Row_num分析函数的使用

虽然只不过是个分析函数，但这个函数太重要了，应用的场景很多，笔试中也碰到了几次。

SQL> select deptno,ename,sal,rn from (select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn from emp) where rn between 2 and 3;

DEPTNO ENAME SAL RN

---------- ---------- ---------- ----------

10 CLARK 2450 2

10 MILLER 1300 3

20 FORD 3000 2

20 JONES 2975 3

30 ALLEN 1600 2

30 TURNER 1500 3

已选择6行。

五 树形查询

几乎涉及到组织网络模型的都要用到这个函数了，应用面也是非常的广泛

--查找KING下面组织的人，向下查询

SQL> select LEVEL,ename from emp start with (ename='KING') connect by prior empno= mgr ;

LEVEL ENAME

---------- ----------

1 KING

2 JONES

3 SCOTT

4 ADAMS

3 FORD

4 SMITH

2 BLAKE

3 ALLEN

3 WARD

3 MARTIN

3 TURNER

3 JAMES

2 CLARK

3 MILLER

已选择14行。

--查找ADAMS组织的领导，向上查询

SQL> select LEVEL,ename from emp start with (ename='ADAMS') connect by prior mgr=empno ;

LEVEL ENAME

---------- ----------

1 ADAMS

2 SCOTT

3 JONES

4 KING

观察两者的区别，为了方便记忆，先看向下查询，由empno -- > mgr即是查找mgr为KINGD的empno的，然后逐级向下分析查询。然后看向上查询，同理，即是查找empno为 ADAMS的MGR的，然后逐级向上迭代查询。

SQL> break on deptno skip 1
SQL> select deptno,LEVEL,grade,ename,sal,job from (select e.*,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal)

start with (ename='KING') connect by prior empno= mgr order by 1,2;

DEPTNO LEVEL GRADE ENAMESAL JOB
---------- ---------- ---------- ---------- ---------- ---------
101 5 KING 5000 PRESIDENT
2 4 CLARK2450 MANAGER
3 2 MILLER1300 CLERK

202 4 JONES 2975 MANAGER
3 4 SCOTT 3000 ANALYST
3 4 FORD 3000 ANALYST
4 1 ADAMS1100 CLERK
4 1 SMITH800 CLERK

302 4 BLAKE 2850 MANAGER
3 2 MARTIN1250 SALESMAN
3 3 TURNER1500 SALESMAN
3 1 JAMES 950 CLERK
3 3 ALLEN 1600 SALESMAN
3 2 WARD1250 SALESMAN

已选择14行。

这里很有意思MANAGER JONES 竟然没有员工挣得多，谁让SCOTT 和FORD是元老呢，呵呵！

以上就是一些SQL常见的笔试题，SQL这个东西逻辑搞明白了，一些函数搜索下拿过来就用了。当然一天不要面太多家了，记得当初一天面了4家公司，到第5家的时候，已经是下午5点了，一天下来SQL题做到吐。也赶上了这个题比较恶心，从刚开始的表设计到最后的PL/SQL都较偏僻，结果做了10多分钟，也赶上人家5点半要下班，草草交了，这也算一次不成功的笔试了。所以面试也不能贪多求全了，注意身体，劳逸结合！