Oracle SQL经典云集_SQL

Oracle SQL经典荟萃

将自己学习，工作中整理的一些经典SQL和大家分享一下。注意，在Oracle 9i中tiger账户默认是开启的，后续版本则改变了策略~同时，关于sql优化尤其是hint的使用，这里不做分享，具体可以查阅民间流传的50大招~

Sql代码?

--?toy?sql??
alter?user?scott?account?unlock;??
select?ename,sal*12?as?"annual?sal"?from?emp;??
--注意：""保持了字段名的小写状态??
select?ename,sal*12?+?comm?from?emp;??
select?job||'KaK''a'?from?emp;??
select?ename,sal*12?+?nvl(comm,0)?from?emp;??
--注意：任何含有空值的数学表达式，计算结果都是空值;字符串连接过程中，单引号使用'转义??
select?distinct?deptno,job?from?emp;??
--distinct?deptno,job组合唯一??
select?ename,sal?from?emp?where?deptno?<>10;??
--Oracle中日期型处理技巧。1.1-1月-1982?2。date'1982-01-01'??
select?ename,hiredate?from?emp?where?hiredate>'1-1月-1982';??
select?ename,hiredate?from?emp?where?hiredate>to_date('1982-1-1','yyyy-mm-dd');??
--SQL中不等于的方法??
select?ename,sal?from?emp?where?sal?between?800?and?1500;??
select?ename,sal?from?emp?where?sal?>=800?and?sal<=1500;??
--指定$为转义字符??
select?ename?from?emp?where?ename?like?'%$%%'?escape?'$';??

Sql代码?

select?lower(ename)?from?emp;??
select?upper(ename)?from?emp;??
select?substr(ename,1,3)?from?emp;??
select?*?from?emp?where?length(ename)=5;??
select?upper(substr(ename,1,1))?from?emp?||?select?lower(substr(ename,2,length(ename)-1))from?emp;??
select?chr(65)?from?dual;??
select?ascii('A')?from?dual;??
select?round(25.656,-1)?from?dual;??
--L,本地货币符号，C，国际货币符号??
select?to_char(sal,'$999,999.999')from?emp;??
select?to_char(sal,'L999,999.999')from?emp;??
select?to_char(sal,'L000,000.000')from?emp;??
select?to_char(hiredate,'YYYY-MM-MM?HH24:MI:SS')?from?emp;??
select?ename,hiredate?from?emp?where?hiredate?>?to_date('1981-02-20?12:00:00','YYYY-MM-DD?HH24:MI:SS');??
select?sal?from?emp?where?sal>to_number('$1,250.00','$99,999.99');??
select?replace(ename,'a','A')?from?emp;??
select?sys_context('USERENV','db_name')?from?dual;??
select?sys_context('USERENV','current_schema')?from?dual;??
--用户和方案的关系:一一对应,Oracle是以方案的方式管理数据对象的，用户名和方案名相同??

Sql代码?

select?deptno,job,max(sal)?as?max_sal?from?emp?group?by?deptno,job;??
--按照两个字段进行分组??
select?deptno,round(avg(sal),2)?from?emp?group?by?deptno;??
--注意：出现在select列表中的字段，如果没有出现在组函数里，那么必需出现在group?by语句里！??
--分组函数只能出现在选择列表，having，order?by子句中！??
select?ename,deptno?from?emp?where?sal?in?(select?max(sal)from?emp?group?by?deptno);??
select?ename?from?emp?where?sal?in?--=?(select?max(sal)?from?emp);??
select?deptno,max(sal)?from?emp?group?by?deptno;??
select?deptno,round(avg(sal))?from?emp?group?by?deptno?having?avg(sal)>2000?order?by?deptno;??
--注意：where是对单条语句过滤，而having是对分组进行过滤！??
select?ename,deptno?from?emp?order?by?deptno?desc;??
select?avg(sal)?from?emp?where?sal>1200?group?by?deptno?having?avg(sal)?>1500?order?by?avg(sal)?desc;??
select?*?from?product_component_version;??
select?ename,sal?from?emp?where?sal?>?(select?avg(sal)?from?emp);??
??
--每个部门中薪水最高的人??
select?ename,deptno,sal?from?emp?where?sal?in?(select?max(sal)?from?emp?group?by?deptno);??
--仔细分析上述写法，在大数据量条件下，会出现偏差??
select?ename,sal,emp.deptno?from?emp?join?(select?max(sal)?as?max_sal,deptno?from?emp?group?by?deptno)?t?on(emp.sal?=?t.max_sal?and?emp.deptno?=?t.deptno);??
??
select?t1.ename?as?clerk,t2.ename?as?boss?from?emp?t1?,emp?t2?where?t1.mgr?=?t2.empno;??
select?t1.ename?as?clerk,t2.ename?as?boss?from?emp?t1?join?emp?t2?on?(t1.mgr?=?t2.empno);??
??
select?t1.ename?as?clerk,t2.ename?as?boss?from?emp?t1?left?outer?join?emp?t2?on?(t1.mgr?=?t2.empno);??
select?ename,dname?from?emp?right?outer?join?dept?on?(emp.deptno?=?dept.deptno);??
select?ename,dname,grade?from?emp?e,dept?d,salgrade?s?where?e.deptno?=?d.deptno?and?e.sal?between?s.losal?and?s.hisal?and?job?<>?'CLERK';??
--超强震撼，99语法将连接条件与数据过滤条件分离，仔细品味吧！??
select?ename,dname,grade?from?emp?join?dept?on?(?emp.deptno?=?dept.deptno?)?join?salgrade?on(emp.sal?between?salgrade.losal?and?salgrade.hisal)?where?ename?not?like?'_A%';??
??
--SQL?99与SQL?92的区别?:?Where?语句后只跟数据过滤条件?！??
select?ename,dname?from?emp,dept;??
select?ename,dname?from?emp?cross?join?dept;??
--?等值连接??
select?ename,dname?from?emp,dept?where?emp.deptno?=?dept.deptno;??
select?ename,dname?from?emp?join?dept?on?(?emp.deptno?=?dept.deptno?);??
select?ename,dname?from?emp?join?dept?using?(deptno);??
--部门平均薪水的等级??
select?deptno,avg_sal,grade?from(select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno)?t?join?salgrade?s?on(t.avg_sal?between?s.losal?and?s.hisal);??
--部门平均的薪水等级??
select?deptno,avg(grade)?from(select?deptno,ename,grade?from?emp?join?salgrade?on(emp.sal?between?salgrade.losal?and?salgrade.hisal))?group?by?deptno;??
--雇员中谁是经理人??
select?ename?from?emp?where?empno?in?(select?distinct?mgr?from?emp);??
--不用聚集函数求最高薪水??
select?distinct?sal?from?emp?where?sal?not?in(select?distinct?e1.sal?from?emp?e1?join?emp?e2?on(e1.sal?<?e2.sal));??
--平均薪水最高的部门的部门编号??
select?deptno,avg_sal?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)???
where?avg_sal?=(select?max(avg_sal)?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno));??
--同样的例子，使用聚集函数嵌套！??
select?deptno,avg_sal?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)???
where?avg_sal?=(select?max(avg(sal))?from?emp?group?by?deptno);??
--平均薪水最高的部门的部门名称??
select?dname?from?dept?where?deptno?=?(select?deptno?from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)???
where?avg_sal?=(select?max(avg_sal)???
from?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)));??
--平均薪水等级最低的部门的部门名称???
select?t1.deptno,dname,avg_sal,grade?from??
(??
???select?deptno,grade,avg_sal?from??
?????(??
????????select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno??
?????)?t?join?salgrade?s?on??
?????????(??
????????????t.avg_sal?between?s.losal?and?s.hisal??
??????????)??
)?t1?join?dept?on???
????(??
???????t1.deptno?=?dept.deptno??
????)?where?t1.grade?=??
??????(??
???????????select?min(grade)?from???
????????????(??
????????????????select?deptno,avg_sal,grade?from??
?????????????????(??
???????????????????select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno??
?????????????????)?t?join?salgrade?s?on??
???????????????????(??
?????????????????????t.avg_sal?between?s.losal?and?s.hisal??
???????????????????)??
???????????)??
????????);??
--显示高于自己部门平均工资的员工信息??
select?t1.ename,t1.sal,t1.deptno,t2.avg_sal?from?emp?t1,(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)?t2?where?t1.deptno?=?t2.deptno?and?t1.sal?>?t2.avg_sal;??
--另类做法??
--1.创建视图??
create?view?v$dept_avg_sal_info?as???
select?deptno,grade,avg_sal?from??
?????(??
????????select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno??
?????)?t?join?salgrade?s?on??
?????????(??
????????????t.avg_sal?between?s.losal?and?s.hisal??
??????????);??
--2.使用刚创建的视图进行查询???????????
select?t1.deptno,dname,avg_sal,grade?from??
(??
???select?deptno,grade,avg_sal?from??
?????(??
????????select?deptno,avg(sal)?as?avg_sal?from?emp?group?by?deptno??
?????)?t?join?salgrade?s?on??
?????????(??
????????????t.avg_sal?between?s.losal?and?s.hisal??
??????????)??
)?t1?join?dept?on???
????(??
???????t1.deptno?=?dept.deptno??
????)?where?t1.grade?=??
??????(??
???????????select?min(grade)?from?v$dept_avg_sal_info??
??????);??
--比普通员工的最高薪水还高的经理人名称??
select?ename?from?emp?where?empno?in???
(?????
????select?distinct?mgr?from?emp?where?mgr?is?not?null??
)??
??and?sal?>??
????(??
??????select?max(sal)?from?emp?where?empno?not?in(select?distinct?mgr?from?emp?where?mgr?is?not?null)??
?????);??
--备份表??
create?table?emp2?as?select?*?from?emp;??
grant?dba?to?scott;??
??
grant?create?table,create?view?to?scott;??
??
--逻辑备份??
--1.?exp??
--2.?create?user?**?identified?by?**?default?tablespace?users?quota?10M?on?users??
--3.?grant?create?session,create?table,create?view?to?**??
--4.?imp??
--另类insert??
insert?into?emp2?select?*?from?emp;??
??
select?ename?from?(select?rownum?r?,ename?from?emp)?where?r?>?10;??
--薪水最高的前5名雇员??
--错误的写法！（错误原因：先取出前5个，才排序）??
select?ename,sal?from?emp?where?rownum?<=5?order?by?sal?desc;??
--正确的写法！??
select?ename,sal?from?(select?ename,sal?from?emp?order?by?sal?desc)?where?rownum?<=5;??
??
--薪水最高的第6个到第10个人??
select?ename,sal,rownum?r?from?(select?ename,sal,rownum?r?from(select?ename,sal?from?emp?order?by?sal?desc))where?r<=10?and?r>=6;??
--hibernate?写法??
select?*?from?(?select?row_.*,?rownum?rownum_?from?(?select?ename,sal?from?emp?order?by?sal?desc)?row_?where?rownum?<=?10)?where?rownum_?>=6;??
--显示比部门30的所有员工的工资都高的员工姓名，工资和部门号??
select?ename,sal,deptno?from?emp?where?sal>?all--any?(select?sal?from?emp?where?deptno=30);??
select?ename,sal,deptno?from?emp?where?sal?>(select?max(sal)?from?emp?where?deptno?=30);--效率高??
--查询与SMITH的部门和岗位完全相同的所有员工??
select?*?from?emp?where?(deptno,job)=(select?deptno,job?from?emp?where?ename='SMITH');??
--希望SCOTT员工的岗位，工资，津贴与SMITH员工一样??
update?emp?set(job,sal,comm)=(select?job,sal,comm?from?emp?where?ename='SMITH')?where?ename='SCOTT';??
--查询8个月前入职的员工??
select?*?from?emp?where?sysdate>add_months(hiredate,8);??
--显示满10个月服务年限的员工姓名和受雇日期??
select?ename,hiredate?from?emp?where?sysdate>=add_months(hiredate,12*10);??
--显示每个雇员入职天数??
select?ename,floor(sysdate-hiredate)?as?'入职天数'?from?emp;??
--各月倒数第3天受雇的员工??
select?*?from?emp?where?last_day(hiredate)-2=hiredate;??
--SQL注入漏洞??
select?*?from?emp?where?empno='7369'?and?ename='SMITH'?or?1='1';??
--MS?SQL中起作用??
select?*?from?emp?where?empno='7369';delete?from?emp;--'and?ename='SMITH';??
--查询员工信息（包括其直接上司）??
select?e1.empno,e1.ename,e1.mgr,e2.ename?from?emp?e1?left?join?emp?e2?on?e1.mgr=e2.empno???
select?e1.empno,e1.ename,e1.mgr,(select?e2.ename?from?emp?e2?where?e2.empno?=?e1.mgr)?as?mgrname?from?emp?e1??
--查询员工7902的所有上级??
select?empno,ename,level?from?emp?connect?by?empno?=?PRIOR?mgr?start?with?empno?=?7902;??
--查询员工7902的所有下级??
select?empno,ename,level?from?emp?connect?by?PRIOR?empno?=?mgr?start?with?empno?=?7902;??
--使用rowid删除重复项，保留rowid最大一项，模式如：??
select?rowid,t.product_type,t.order_datetime?from?tb_order_view?t?where?rowid?!=?(select?max(rowid)from?tb_order_view?b?where?b.product_type?=?t.product_type?and?b.order_datetime?=?t.order_datetime)??
--case...when...then??
select?e1.empno,??
???????e1.ename,??
???????e1.job,??
???????e1.hiredate,??
???????e1.sal,??
???????case??
?????????when?e1.sal?<?1000?then??
??????????'低'??
?????????else??
??????????(case??
????????????when?e1.sal?<=?3000?then??
?????????????'中'??
????????????else??
?????????????'高'??
??????????end)??
???????end?as?grade??
??from?emp?e1??
????
select?count(case?when?sal<1000?then?1?else?null?end)low,??
???????count(case?when?sal?between?1000?and?3000?then?1?else?null?end)mid,??
???????count(case?when?sal?>3000?then?1?else?null?end)?high?from?emp??
??
??
--数据库完整性实现方式之-约束（5种）,包括列级定义，表级定义??
--1.非空?2.唯一?3.主键?4.外键?5.Check??
create?table?stu(??
id?number(6),??
name?varchar2(20)?constraint?stu_name_nn--自定义约束名字?not?null,??
sex?char(2)?default?'男'?check(sex?in('男','女')),??
age?number(3)?check(age>0),??
sdate?date,??
grade?number(2)?default?1,??
class?number(4)?--references?class(id),??
email?varchar2(50),??
constraint?stu_fk?foreign?key?(class)?references?class(id),??
constraint?stu_id_pk?primary?key(id),??
constraint?stu_unique?unique(email,name)???
);??
create?table?class(??
id?number(4)?primary?key,??
name?varchar2(20)??
);??
alter?table?class?modify?name?not?null;??
alter?table?class?add?constraint?nameUnique?unique(name);??
select?constraint_name,constraint_type,status,validated?from?user_constraints?where?table_name='emp';??
select?column_name,position?from?user_cons_columns?where?constraint_name='nameUnique';??
--null值，数据库不认为是重复值??
??
--表修改??
alter?table?stu?add(addr?varchar2(100));??
alter?table?stu?drop(addr);??
alter?table?stu?drop?column?addr;??
rename?stu?to?student;--修改表名字??
alter?table?stu?modify(addr?varchar2(50));--修改字段长度或类型（空表）??
--约束条件操作??
alter?table?stu?drop?constraint?stu_fk;??
alter?table?stu?add?constraint?stu_fk?foreign?key?(class)?references?class(id);??
??
--数据字典表??
desc?user_tables;??
select?table_name?from?user_tables;??
select?view_name?from?user_views;??
select?constraint_name?from?user_constraints;??
select?constraint_name,table_name?from?user_constraints;??
??
desc?dictionary;??
select?table_name?from?dictionary;??
??
--索引与视图(主键约束和唯一约束)??
create?index?index_stu_email?on?stu(email);--也可以设置复合索引，注意顺序不同（区分度高的字段放后面），索引不同，索引的层次不要超过4层。??
drop?index?index_stu_email;??
select?index_name?from?user_indexes;??
select?view_name?from?user_views;??
--索引的缺点。1.系统需要为索引开辟大约为表存储容量1.2倍的空间（硬盘空间和内存空间）。2.更新数据时，系统需要额外的时间来对索引进行同步更新。??
select?index_name,index_type?from?user_indexes?where?table_name='emp';??
select?table_name,column_name?from?user_id_columns?where?index_name='emp';??
??
create?sequence?seq;??
select?seq.nextval?from?dual;??
??
create?view?v$view_table?as?select?M.column_name?FieldName?from?(select?*?from?user_tab_columns?where?upper(Table_name)?=?upper('BLB'))?M?left?join?user_col_comments?A?ON?M.COLUMN_NAME=A.COLUMN_NAME?and?M.Table_Name?=?A.Table_Name??
left?join?(select?a.table_name,b.column_name?from?user_constraints?a,?user_cons_columns?b?where?a.constraint_name=b.constraint_name?and?upper(a.table_name)=upper('BLB')?and?a.constraint_type='P')?B?ON?M.Table_Name?=?B.TABLE_NAME?and?M.COLUMN_NAME=B.COLUMN_NAME?order?by?M.column_id;??
??
insert?into?[用户表](id)?select?*?from?v$view;??
??
create?or?replace?procedure?insert_ID?is??
begin??
??for?i?in?1..196?loop??
??????insert?into?lcqkbt(id)?values(seq.nextval);??
??end?loop;??
??commit;??
end;??
??
alter?database?datafile?''?resize?100M;??
??
--dba?daily?work??
create?table?errorlog(??
???id?number?primary?key,??
???errcode?number,??
???errmsg?varchar2(1024),??
???errdate?date??
);??
create?sequence?seq_errorlog_id?start?with?1?increment?by?1;??
declare???
??v_deptno?dept.deptno%type?:=10;??
??v_errcode?number;??
??v_errmsg?varchar2(1024);??
begin??
??delete?from?dept?where?deptno?=?v_deptno;??
??commit;??
exception??
??when?others?then??
???????rollback;??
??????????????v_errcode?:=SQLCODE;??
??????????????v_errmsg:=SQLERRM;??
???????insert?into?errorlog?values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);??
???????commit;??
end;??
??
declare??
??cursor?c?is??
?????????select?*?from?emp;??
??v_emp?c%rowtype;??
begin??
??open?c;??
??loop??
???????fetch?c?into?v_emp;??
???????exit?when(c%notfound);??
???????dbms_output.put_line(v_emp.ename);??
??end?loop;??
??close?c;??
end;??
??
declare??
??cursor?c?is??
?????????select?*?from?emp;??
??v_emp?c%rowtype;??
begin??
??open?c;??
???????fetch?c?into?v_emp;??
???????while(c%found)?loop??
???????dbms_output.put_line(v_emp.ename);??
???????fetch?c?into?v_emp;??
??end?loop;??
??close?c;??
end;??
??
declare??
??cursor?c?is??
?????????select?*?from?emp;??
begin??
????for?v_emp?in?c?loop??
???????dbms_output.put_line(v_emp.ename);??
??end?loop;??
end;??
??
update?dept?set?deptno?=?99?where?deptno?=?10;??
create?or?replace?trigger?cascadeUpdate??
???after?update?on?dept??
???for?each?row??
begin??
???update?emp?set?deptno?=?:NEW.deptno?where?deptno=:OLD.deptno;??
end;??
??
create?or?replace?procedure?bbs(v_pid?article.pid%type,v_level?binary_integer)?is??
???cursor?c?is?select?*?from?article?where?pid=?v_pid;??
???v_preStr?varchar2(1024):='';??
begin??
???for?i?in?1..v_level?loop??
??????v_preStr?:=?v_preStr?||'****';??
???end?loop;??
?????
???for?v_article?in?c?loop??
???????dbms_output.put_line(v_preStr||v_article.cont);??
???????if(v_article.isleaf?=?0)?then??
???????????p(v_article.id,v_level+1);??
???????end?if;???
???end?loop;??
end;??
??
--Oracle?date默认格式:11-12月-1997?or?11-12月-97??
--更改输入格式??
alter?session?set?nls_date_format='yyyy-mm-dd?hh:mi:ss';??
??
1.insert?into...??
1.savepoint?a;??
3.delete?from?stu;--删除所有记录,表结构还在,写日志，可以恢复，速度慢??
4.rollback?to?a;??
??
truncate?table?stu；--删除所有记录，表结构还在，不写日志，无法恢复，速度快??
set?timing?on;??
??
--union?all和union的区别在于后者会去掉重复行???
--minus，intersect??
??
--创建数据库??
--使用?Configuration?and?Migration?Tools->Database?Configuration?Assistant?8步，默认20进程??
??
--只读事务??
set?transaction?read?only;??
??
--预定义角色是在数据库安装后，系统自动创建的一些常用的角色??
--1．CONNECT,RESOURCE,DBA?---这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。??
--2．DELETE_CATALOG_ROLE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE?---这些角色主要用于访问数据字典视图和包。??
--3.?EXP_FULL_DATABASE,IMP_FULL_DATABASE?---这两个角色用于数据导入导出工具的使用。??
select?*?from?role_sys_privs??where?role=upper('connect');??
--对象权限就是指在表、视图、序列、过程、函数或包等对象上执行特殊动作的权利。有九种不同类型的权限可以授予给用户或角色??
--系统权限需要授予者有进行系统级活动的能力,如连接数据库,更改用户会话、建立表或建立用户等等。你可以在数据字典视图SYSTEM_PRIVILEGE_MAP上获得完整的系统权限。对象权限和系统权限都通过GRANT语句授予用户或角色。需要注意的是在授予对象权限时语句应该是WITH?GRANT?OPTION子句，但在授予系统权象时语句是WITH?ADMIN?OPTION;??
--查看死锁信息&解除死锁??
select?sess.sid,??
???????sess.serial#,??
???????lo.oracle_username,??
???????lo.os_user_name,??
???????ao.object_name,??
???????lo.locked_mode??
??from?v$locked_object?lo,?dba_objects?ao,?v$session?sess??
?where?ao.object_id?=?lo.object_id??
???and?lo.session_id?=?sess.sid;??
?????
???SELECT?a.sid,?b.owner,?object_name,?object_type??
??FROM?v$lock?a,?all_objects?b??
?WHERE?TYPE?=?'TM'??
???and?a.id1?=?b.object_id;??
?????
alter?system?kill?session?'3267,62017'??
??
--查看死锁机器??
select?username,?lockwait,?status,?machine,?program??
??from?v$session??
?where?sid?in?(select?session_id?from?v$locked_object)??
???
?--查看死锁字段??
?select?sql_text??
??from?v$sql??
?where?hash_value?in??
???????(select?sql_hash_value??
??????????from?v$session??
?????????where?sid?in?(select?session_id?from?v$locked_object));??