oracle sql语句

##########基本操作##########启动数据库：su - oraclesqlplus / as sysdbasql> startup对scott用户解锁：sql> conn / as sysdbasql> alter user scott identified by tiger account unlock;连接到指定的数据库用户：sql> conn scott/tiger实现操作系统开机数据库自动open：vi /etc/oratab------------------------------------------orcl:/u01/app/oracle/product/11.2.0/db_1:Ydb01:/u01/app/oracle/product/11.2.0/db_1:Ncrm:/u01/app/oracle/product/11.2.0/db_1:Y------------------------------------------vi /etc/rc.local------------------------------------------su - oracle '-c dbstart $ORACEL_HOME'------------------------------------------################基本select语句################查看scott用户下有哪些表和视图：sql> select * from tab;描述一张表的结构，不看表中的数据sql> desc dept查看雇员表中的所有数据：select * from dept;描述雇员表的结构sql> desc emp查看emp表中感兴趣的列：sql> select ename,sal from emp;在select中使用四则运算:null不能参与四则运算select ename,(sal+100)*12 from emp;为列定义别名select ename AS first_name,sal*12 "Annual Salary" from emp;连接操作符select ename,job,ename||' is a '||job detail from emp;压缩重复行select distinct deptno,job from emp;将缓冲区中的命令保存为脚本sql> save p1_1.sql查看sql脚本内容sql> get p1_1.sql运行sql脚本sql> @p1_1.sql###############限制和排列数据###############工资高于1500的销售员？查询10部门的雇员和20部门工资小与2000的雇员？查询有奖金的雇员？使用rownum伪列限制查询返回的行的数量:sql> select * from emp where rownum<6;sql> select * from emp where sal between 2000 and 3000;select to_char(sysdate,'yyyy') curr_year,to_char(to_date('05','yy'),'yyyy') yy05,to_char(to_date('99','yyyy') yy99,'rr'),'yyyy') rr05,'yyyy') rr99from dual;select ename,sal,case job when 'CLERK' then sal*1.05 when 'ANALYST' then sal*1.1else sal end raise_salfrom emporder by job;select ename,decode(job,'CLERK',sal*1.05,'ANALYST',sal*1.1,sal) raise_salfrom emp order by job;##########多表连接##########笛卡尔连结：select ename,loc from emp cross join dept;自然连接：等值连接select ename,loc from emp natural join dept;两张表有多个同名的列：select ename,loc from emp join dept using (deptno);两张没有同名的列：select ename,loc from emp e join dept d on (e.deptno=d.deptno);右外连接select ename,loc from emp right outer join dept using (deptno);左外连接select ename,loc from emp left outer join dept using (deptno);全外连接select ename,loc from emp full outer join dept using (deptno);10部门的最大工资查找重复的工资80年 81年 82年 87年都有多少新员工1980 1981 1982 1987---- ---- ---- ---- 1 10 1 2工资高于BLAKE的？工资最低的人？低于10部门最低工资的人？高于30部门最高工资的人？工资相同的人？blake的工资是smith的几倍？每个部门工资最高的人？每个部门工资最高的前2个人？工资最高的前5行？ 工资6～10名？随机从表中取出3行数据？取每一个部门工资最高的前2个人方法1：select deptno,ename,sal from empwhere sal in (select max(sal) from emp group by deptno) orsal in (select max(sal) from (select sal,deptno from emp where sal not in (select max(sal) from emp group by deptno)) group by deptno)order by 1;方法2：select ename,deptno,sal from emp e where (select count(*) from emp where sal>e.sal and deptno=e.deptno)<2;select count(*) from emp where sal>800 and deptno=20;方法3：select * from (select ename,rank () over (partition by deptno order by sal desc) Ord from emp) where ord<=2;select ename,row_number () over (partition by deptno order by sal desc) Ord from emp;select * from (select rownum rn,a.* from (select ename,sal from emp order by sal desc) a) where rn between 6 and 10;select * from (select * from emp order by dbms_random.value()) where rownum<=3;查询雇员的姓名，工资，税，(1级不缴税，2-->2%,3-->3%,4-->4%,5-->5%)select e.ename,e.sal,(sal*decode(s.grade,1,2,0.02,3,0.03,4,0.04,5,0.05,0)) taxfrom emp e,salgrade s where e.sal between s.losal and s.hisal;部门总工资和部门上缴个税总和select deptno,sum(sal),sum(tax)from(select e.sal,0)) tax,deptnofrom emp e,salgrade s where e.sal between s.losal and s.hisal)group by deptno;比WARD奖金低的人？select ename,comm from emp where NVL(comm,0)<(select comm from emp where ename='WARD');select ename,comm from emp where comm<(select comm from emp where ename='WARD') or comm is null;奖金最高的前两名雇员？select * from (select ename,comm from emp order by comm desc nulls last) where rownum<=2;select * from (select ename,comm from emp where comm is not null order by comm desc) where rownum<=2;工资高于本部门平均工资的人？使用替代变量进行分页查询select * from (select rownum rn,a.* from (select * from emp order by sal desc) a)where rn between &p*5-4 and &p*5;set PAUSE onshow pagesizehost pwdspool 0728am.txt appendspool offinsert into (select empno,hiredate from emp where hiredate<=sysdate with check option)values (2,'jerry',to_date('20160729','yyyymmdd'));ins10.sql--------------------------------insert into (select * from emp where deptno=10 with check option) values (&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);--------------------------------数据源：emp目标表：copy_empcreate table copy_emp as select * from emp where 1=0;matched--> 目标表中的主键值在数据源中被找到not matched --> 数据源中主键在目标表中不存在merge into copy_emp cusing emp eon (c.empno=e.empno)when matched thenupdate setc.ename=e.ename,c.job=e.job,c.mgr=e.mgr,c.hiredate=e.hiredate,c.sal=e.sal,c.comm=e.comm,c.deptno=e.deptnowhen not matched theninsert values(e.empno,e.ename,e.job,e.mgr,e.hiredate,e.comm,e.deptno);commit;select ename,sal from emp where sal>(select sal from emp where ename='BLAKE');1. scott的工资时blake的几倍？select (select sal from emp where ename='SCOTT')/(select sal from emp where ename='BLAKE') from dual;select a.sal/b.sal from emp a,emp b where a.ename='SCOTT' and b.ename='BLAKE';2. 工资最高的人是谁？3. 工资相同的人都有谁？4. 30部门工资最高的人？5. 每个部门工资最高的人？6. 每个部门工资最高的前两个人？select ename,deptno from emp e where (select count(*) from emp where deptno=e.deptno and sal>e.sal)<2 order by 3;7. 大于10部门最小工资的人？8. 工资高于本部门平均工资的人？select e.ename,e.deptno,a.avgsalfrom emp e,(select deptno,avg(sal) avgsal from emp group by deptno) awhere e.deptno=a.deptnoand e.sal>a.avgsal;9. 工资前5的员工？10.工资6～10的员工？select * from (select rownum rn,sal from emp order by sal desc) a) where rn between 6 and 10;11. 分页查询：每页5行grant alter session to scott;alter session set events 'immediate trace name savepoints level 1';sql> show parameter background background_dump_dest = /u01/app/oracle/diag/rdbms/mydb/mydb/traceselect COLUMN_NAME,DATA_DEFAULT from user_tab_columns where table_name='E01'create table t03 (x number,y date default sysdate);select column_name,data_default from user_tab_columns where table_name='T03';user_tablesuser_cataloguser_objects-----------------------------------------------------------------------------------------键表时直接启用约束，列级别启用约束，约束产用系统命名create table e01 (id number primary key,name varchar2(12) not null,email varchar2(30) unique);键表时直接启用约束，列级别启用约束，约束产用户命名create table e01 (id number constraint pk_e01_id primary key,name varchar2(12) constraint nn_e01_name not null,email varchar2(30) constraint uk_e01_mail unique);添加列的同时带有约束create table e03 (id number constraint fk_e03_id references e01);外键的级联操作：alter table e03 drop constraint fk_e03_id;alter table e03 add constraint fk_e03_id foreign key (id) references e01 on delete set null;alter table e03 add constraint fk_e03_id foreign key (id) references e01 on delete cascade;create table t04 (x number not null,y date default sysdate);select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where table_name='T04';create table t04 (x number constraint nn_t04_x not null,y date default sysdate);create table t04 (x number constraint nn_t04_x not null,y date default sysdate constraint uk_t04_y unique);create table t04 (x number constraint pk_t04_x primary key,y date default sysdate constraint uk_t04_y unique);create table t05 (x number constraint fk_t05_x references t04,y number constraint ck_t04_y check(y>100));add constraint:create table d as select * from dept;create table e as select * from emp;alter table d add constraint pk_d_id primary key (deptno);alter table e modify (empno number(4) constraint nn_e_id not null);alter table e add constraint uk_e_name unique (ename);alter table e add constraint fk_e_deptno foreign key (deptno) references d;alter table e add constraint ck_e_sal check (sal>=1000 and sal is not null);@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlexcpt.sqlalter table e add constraint ck_e_sal check (sal>=1000 and sal is not null) exceptions into exceptions;drop constraint:alter table e drop constraint XXXXXXXXXX;alter table d drop constraint PK_D_ID cascade;查看约束状态：select CONSTRAINT_NAME,STATUS,VALIDATED from user_constraints where table_name='E01';alter table e modify constraint CK_E_SAL disable;alter table e modify constraint CK_E_SAL enable;ENABLED VALIDATEDENABLED NOT VALIDATEDDISABLED NOT VALIDATEDDISABLED VALIDATED alter table e modify constraint CK_E_SAL disable validate;-----------------------------------------------------------alter table e modify constraint UK_E_NAME disable;insert into e (empno,ename) values (2,'SCOTT');alter table e modify constraint CK_E_SAL enable novalidate;create index i_e_name on e (ename);-----------------------------------------------------------select distinct privilege from dba_sys_privs;--with admin option 权限回收无级联--with grant option 权限回收有级联sql> select * from session_privs;PRIVILEGE----------------------------------------CREATE SESSIONUNLIMITED TABLESPACECREATE TABLECREATE CLUSTERCREATE SEQUENCECREATE PROCEDURECREATE TRIGGERCREATE TYPECREATE OPERATORCREATE INDEXTYPEconn / as sysdbagrant create view to scott;create or replace view vu30 as select empno,deptno from emp where deptno=30;create or replace view vu30 as select empno employee_id,ename first_name,sal salary,deptno department_id from emp where deptno=30;create or replace view vu30 (employee_id,first_name,salary,department_id) as select empno,deptno from emp where deptno=30;create or replace force view vu30 as select empno,deptno from e01 where deptno=30;create or replace force view vu30 as select empno,deptno from e01 where deptno=30 with check option;create or replace force view vu30 as select rownum rn,empno,deptno from e01 where deptno=30 with check option;-----------------------------------------------------------------------------create sequence seq_empno start with 7935 increment by 1minvalue 7935maxvalue 9999cache 50nocycle;select seq_empno.currval,seq_empno.nextval from dual;alter sequence seq_empno increment by 5;alter sequence seq_empno minvalue 7936;alter sequence seq_empno maxvalue 8888;alter sequence seq_empno cache 100;alter sequence seq_empno cycle;insert into emp (empno) values (seq_empno.nextval);drop sequence seq_empno;-------------------------------------------------------7839:AAAVREAAEAAAACXAAIAAAVRE AAE AAAACX AAIOOOOOO FFF BBBBBB RRROOOOOO --> object_idFFF --> file_idBBBBBB --> block_idRRR --> row numberA - Z a - z 0 - 9 + /0 - 25 26 - 51 52 - 61 62 63VRE = 21*64*64+17*64+4 = 87108 2*64+23 = 151 create index i_emp_name on emp (ename);set autot trace expselect * from emp where ename='SCOTT';create index i_emp_name_f on emp (upper(ename));select * from emp where upper(ename)='SCOTT';drop index I_EMP_NAME_F;create synonym e01 for scott.e01;create public synonym e01 for scott.e01;---------------------------------------------------------------------------create user smith identified by smith;sql> select distinct privilege from dba_sys_privs;sql> grant create session to smith;sql> conn smith/smithsql> select * from session_privs;create table t01 (x int);sql> grant select on scott.e01 to smith;sql> grant update (comm) on scott.e01 to smith;sql> grant delete on scott.e01 to smith;sql> grant insert on scott.e01 to smith;select table_name from dict where table_name like '%ROLE%';create role r1;grant create session,create table to r1;create role r2;grant create view to r2;grant delete on scott.e01 to r2;create role r3;grant create procedure to r3;grant update (sal) on scott.e01 to r3;grant r3 to r1;create user tom identified by tom;grant r1,r2 to tom;grant create sequence to tom;grant select on scott.e01 to tom;grant insert on scott.e01 to tom;grant update (comm) on scott.e01 to tom;-------------------------------------------------查看用户被授予的系统权限：sql> select privilege from dba_sys_privs where GRANTEE='TOM';查看用户被授予的对象权限:col GRANTEE for a15col PRIVILEGE for a20col owner for a15sql> SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE='TOM';查看用户被授予的列级别的对象权限:sql> SELECT OWNER,TABLE_NAME,COLUMN_NAME,PRIVILEGE FROM DBA_COL_PRIVS where GRANTEE='TOM';用户被授予的角色:SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='TOM';角色被授予的角色: R1，R2，R3SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='R1';SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='R2';角色被授予的系统权限:select * from ROLE_SYS_PRIVS WHERE ROLE='R1';select * from ROLE_SYS_PRIVS WHERE ROLE='R2';select * from ROLE_SYS_PRIVS WHERE ROLE='R3';角色被授予的对象权限:select * from ROLE_TAB_PRIVS WHERE ROLE='R1';select * from ROLE_TAB_PRIVS WHERE ROLE='R2';select * from ROLE_TAB_PRIVS WHERE ROLE='R3';select * from v$timezone_names;修改用户口令：alter user tom identified by oracle;sql> passwordChanging password for TOMOld password: ******New password: ***Retype new password: ***Password changedsql> revoke r2 from tom;级联授权：dba --> user A --> user B系统权限级联授权：with admin option 权限回收无级联grant CREATE SEQUENCE to tom with admin option;对象权限级联授权：with grant option 权限回收有级联grant insert on scott.e01 to tom with grant option;---------------------------------------------------------union会压缩重复值：select * from e01unionselect * from emp;union all没有去重效果select * from e01union allselect * from emp;select * from e01intersectselect * from emp;select * from e01minusselect * from emp;select * from empminusselect * from e01;select * from e01union allselect dept.*,null,null from dept;---------------------------------------------------时间戳sql> create table t01 (x int,y timestamp);sql> insert into t01 values (1,current_timestamp);sql> alter table t01 modify (y timestamp(9));全球化时间戳：sql> create table t02 (x int,y timestamp with time zone);本地时间戳：sql> create table t03 (x int,y timestamp with local time zone);sql> select sessiontimezone from dual;SESSIONTIMEZONE----------------+08:00sql> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(current_date,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY TO_CHAR(CURRENT_DAT------------------- -------------------2015-12-22 13:41:00 2015-12-22 13:41:00sql> alter session set time_zone='-8:00';sql> select to_char(sysdate,'YY TO_CHAR(CURRENT_DAT------------------- -------------------2015-12-22 13:43:38 2015-12-21 21:43:38select to_char(sysdate,current_timestamp from dual;TO_CHAR(SYSDATE,'YY TO_CHAR(CURRENT_DAT CURRENT_TIMESTAMP------------------- ------------------- ---------------------------------------------------------------------------2015-12-22 13:46:26 2015-12-21 21:46:26 21-DEC-15 09.46.26.500401 PM -08:00select to_char(sysdate,current_timestamp,localtimestamp from dual;sql> select dbtimezone,sessiontimezone from dual;sql> select extract(month from sysdate) from dual;sql> select extract(year from sysdate) from dual;sql> select from_tz(timestamp '2015-12-22 13:58:00','+08:00') from dual;sql> select * from v$timezone_names;sql> select tz_offset('US/Samoa') from dual;select sysdate+to_yminterval('02-06') from dual;select to_char(sysdate,to_char(sysdate+to_dsinterval('5 02:10:18'),'yyyy-mm-dd hh24:mi:ss')from dual;--------------------------------------------------------------------------roll(a,b,c) --> n+1种聚集运算的结果group by agroup by a,bgroup by a,ctotalcube(a,c) --> 2的n次方种聚集运算的结果group by agroup by bgroup by cgroup by a,cgroup by b,cgroup by a,ctotalselect deptno,grouping(deptno),grouping(job) from emp group by rollup(deptno,job);col deptno for a15select decode(GROUPING(DEPTNO)||GROUPING(JOB),'01','subtotal '||deptno,'11','total ',deptno) deptno,sum(sal) from emp group by rollup(deptno,job);select deptno,mgr,sum(sal) from emp group by grouping sets ((deptno,job),(job,mgr));---------------------------------------------------------------------------分级查询（爬树）：select level,lpad(ename,length(ename)+level*2-2,' ') ename from emp start with empno=7839 connect by prior empno=mgr;修改爬树的起点： start withselect level,' ') ename from emp start with ename='JONES' connect by prior empno=mgr;修改爬树的方向：connect by prior 父键在前向下爬，子键在前向上爬select level,' ') ename from emp start with ename='JONES' connect by prior mgr=empno;剪枝：剪枝条件出现在where子句,剪一个节点select level,' ') ename from emp where ename<>'BLAKE'start with empno=7839 connect by prior empno=mgr;剪枝条件出现在connect by prior子句，剪一个派系select level,' ') ename from emp start with empno=7839 connect by prior empno=mgr and ename<>'BLAKE';------------------------------------------------------------------------drop table e01 purge;drop table e02 purge;create table e01 as select empno,sal from emp where 1=0;create table e02 as select ename,comm,deptno from emp where 1=0;insert allinto e01 values (empno,sal)into e02 values (ename,deptno)select empno,deptno from emp;带条件的insert all:insert allwhen deptno=10 theninto e01 values (empno,sal)when sal>2000 theninto e02 values (ename,deptno from emp;带条件的insert first:insert firstwhen deptno=10 theninto e01 values (empno,deptno from emp;旋转插入：创建一张表：销售元数据create table sales_source_data (employee_id number,week_id number,sales_mon number,sales_tue number,sales_wed number,sales_thur number,sales_fri number);insert into sales_source_data values (178,3500,2200,4300,1500,5000);insert into sales_source_data values (179,2800,3300,1000,800,4400);创建一张表：销售信息表create table sales_info (employee_id number,week number,sales number);insert into sales_info select * from (select employee_id,week_id week,sum(decode(WEEK_ID,SALES_MON,SALES_mon)) salesfrom sales_source_data group by employee_id,week_idunion allselect employee_id,SALES_tue,SALES_tue)) salesfrom sales_source_data group by employee_id,SALES_wed,SALES_wed)) salesfrom sales_source_data group by employee_id,SALES_thur,SALES_thur)) salesfrom sales_source_data group by employee_id,SALES_fri,SALES_fri)) salesfrom sales_source_data group by employee_id,week_id);~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~insert allinto sales_info values (employee_id,week_id,SALES_mon)into sales_info values (employee_id,SALES_tue)into sales_info values (employee_id,SALES_wed)into sales_info values (employee_id,SALES_thur)into sales_info values (employee_id,SALES_fri)select * from sales_source_data;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~create table sales_info (employee_id number,day_id varchar2(4),sales number);insert allinto sales_info values (employee_id,'MON','TUE','WED','THUR','FRI',SALES_fri)select * from sales_source_data;外部表:vi /home/oracle/1.txt----------------------------------------------------------7369,SMITH,CLERK,7902,1980/12/17:00:00:00,852,207499,ALLEN,SALESMAN,7698,1981/02/20:00:00:00,1673,300,307521,WARD,1981/02/22:00:00:00,1251,500,307566,JONES,MANAGER,7839,1981/04/02:00:00:00,2980,207654,MARTIN,1981/09/28:00:00:00,1290,1400,307698,BLAKE,1981/05/01:00:00:00,2900,30----------------------------------------------------------vi /home/oracle/2.txt----------------------------------------------------------7782,CLARK,1981/06/09:00:00:00,2450,107839,KING,PRESIDENT,1981/11/17:00:00:00,5000,107844,TURNER,1981/09/08:00:00:00,30----------------------------------------------------------创建逻辑目录并授权:conn / as sysdbaCREATE DIRECTORY mydir AS '/home/oracle';GRANT READ,WRITE ON DIRECTORY mydir TO SCOTT;创建外部表:conn scott/tigerCREATE TABLE scott.refemp(emp_id number(4),ename varchar2(12),job varchar2(12),mgr_id number(4),hiredate date,salary number(8),comm number(8),dept_id number(2))ORGANIZATION EXTERNAL(TYPE ORACLE_LOADERDEFAULT DIRECTORY mydirACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINEFIELDS TERMINATED BY ','(emp_id char,ename char,job char,mgr_id char,hiredate char date_format date mask "yyyy/mm/dd:hh24:mi:ss",salary char,comm char,dept_id char))LOCATION('1.txt','2.txt'));select * from e01 a where exists (select 1 from e01 where a.rowid!=e01.rowid and e01.empno=a.empno);select * from e01 a where rowid in (select max(rowid) from e01 where e01.empno=a.empno);delete e01 where rowid not in (select max(rowid) from e01 group by empno,hiredate,deptno);找到重复的行select * from e01 a where exists (select 1 from e01 e where a.rowid!=e.rowid and e.empno=a.empno);查找重复行的rowid 方法1：select rowid from e01 a where a.rowid!= (select max(rowid) from e01 e where e.empno=a.empno and e.ename=a.ename);查找重复行的rowid 方法2：select rowid from e01 a where rowid not in (select max(rowid) from e01 group by empno,ename);找到不重复的行select * from e01 a where not exists (select 1 from e01 e where a.rowid!=e.rowid and e.empno=a.empno);去掉重复的行：select * from e01 a where rowid in (select max(rowid) from e01 e where e.empno=a.empno);