Oracle SQL经典荟萃

来源：互联网发布：dnf烤机数据编辑：程序博客网时间：2024/04/30 04:50

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

-- toy sqlalter 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 '$';

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是以方案的方式管理数据对象的，用户名和方案名相同

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--另类insertinsert 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...thenselect 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.Checkcreate 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_Nameleft 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 isbegin  for i in 1..196 loop      insert into lcqkbt(id) values(seq.nextval);  end loop;  commit;end;alter database datafile '' resize 100M;--dba daily workcreate 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;beginfor 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 rowbegin   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));