Oracle SQL语句总结备查

0. 前言

本文以自己在学习、练习和工作中遇到的与Oracle相关的sql语句，现以笔记的形式在博文中给出，主要是为自己以后备忘查询，如有错误希望各位仁心发现后帮忙指出，本人将非常感谢。

---

1. 数据库基本操作

1.1 常用语句

--查看oracle版本select * from v$version;       select * from product_component_version;    --查看Oracle数据库中所有表空间的保存路径select file_name,tablespace_name from dba_data_files --第一种方式select name from v$datafile; --第二种方式--查看到控制文件存放位置。select name from v$controlfile;--显示oracle数据库的各种参数信息show parameter;--更改ORACLE 用户：OPEN表示账户为解锁状态；EXPIRED表示账户为过期状态(需要设置口令才能解除此状态)；LOCKED表示账户为锁定状态。）select username,account_status from dba_users; --查看所有的账户状态alter user hr account unlock;   --用ALTER USER语句为hr账户解锁alter user hr identified by hr; --用ALTER USER语句为hr账户设置口令

1.2 Dos相关

ed --修改上一次出现错误的sql语句，将在默认的编辑器中打开host cls        --windows清屏host clear      --linux清屏show user;      --显示当前用户save c:\sql;    --保存脚本@c:\a.sql;      --加载并执行脚本spool c:\听课笔记.txt;  --录屏命令开始，保存到特定文件spool off;              -- 录屏结束命令set pagesize 20;        --设置每页显示的数据行数（表头、空行都算）desc emp;               --查看某张表

2. 表操作

2.1 增删改（CDU）

--指定数据表中某列为主键（指定tuser表中user_id列为主键，主键约束名为tuser_PK）alter table tuser add constraint tuser_PK  primary key(user_id)--删除主键名（无主键约束名的情况）（删除tuser表的主键）select * from user_cons_columns; --先查询出表的主键约束名alter table tuser drop constraint SYS_C0010342;--清除表中的数据delete from tablename where 1=1truncate Table tablename

2.2 查询（R）

2.2.1 普通查询语句

--查询当前用户所有的表select * from tab;  --tab：数据字典，可以查看当前用户下的所有表--查询员工的所有信息select * from  emp; select linesize 120;  --设置行宽(设置每行120个字符)col ename for a8 col sal for 9999  --9代表数字，表示sal最多显示4位/ --执行上述所有sql语句--查询员工信息：员工号  姓名 月薪select empno,ename,sal;--查询员工信息:员工号  姓名 月薪 年薪select empno,ename,sal,sal*12;--错误语句示例--★知识点：员工号，姓名是没有区别的，但是员工号和职位是有区别的select empno as "员工号", ename as "姓名",sal 月      薪，job 职位;--错误原因，别名中间不能有空格  select empno as "员工号", ename as "姓名",sal "月      薪"，job 职位;--修改后--★distinct去掉重复的（作用于后面所有的列，只有当后面的所有列值都相同对distinct才起作用）select distinct deptno from emp;

2.2.2 条件查询语句

--查询10号部门的员工信息select * from emp  where deptno=10;--查询入职日期为17-11月-81的员工select *  from emp  where hiredate='17-11月-81';select *   from emp where hiredate='1981-11-17';  --错误格式select sysdate from dual; --可以通过打印系统时间来查看系统时间格式col parameter for a30 --确定显示格式select *  from v$nls_parameters; --查询表中数据字典的参数alter session set nls_date_format='yyyy-mm-dd';--修改日期格式--查询薪水1000~2000之间的员工select   from emp where sal between 1000 and 2000; --第一个参数必须比第二个小--查询部门号是10和20的员工select * from emp  where deptno in (10,20);--查询名字以S打头的员工select *  from emp where ename like 'S%';--查询名字是4个字的员工select *  from emp where ename like '____';--查询名字中含有下划线的员工select *  from emp where ename like '%\_%' escape '\\';--★order by后面+列名  表达式  别名 序号--查询员工信息 按照年薪排序select ename,sal,sal*12  from emp order by sal*12;--order by 后面加别名和序号select ename,sal,sal*12 年薪 from emp * order by 4;--order by 后面+多列（order by作用于后面所有的列，先按照第一列排序）select *  from emp  order by deptno,sal;--desc只作用于离他最近的一列select * rom emp order by deptno desc,sal desc;--查询员工信息，按照奖金排序（奖金还有空值）select * from emp order by comm;select * from emp  order by comm desc nulls last; --表示没有数据的（null值的）排在后面

2.2.3 多表查询

--理解笛卡尔集--等值连接-->查询员工信息： 员工号 姓名 月薪 部门名称select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;--如果不给任何的条件，则出现的是笛卡尔集--不等值连接-->查询员工信息： 员工号 姓名 月薪 工资级别select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;--★★外连接--按部门统计员工人数： 部门号 部门名称 人数select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname;--★等值连接形式出错，有一个部门没有员工，统计不到--希望在最后的结果中，包含某些不成立的记录select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;--★★自连接--查询员工信息：***的老板是***（自连接：通过表的别名，将同一张表视为多张表）select e.ename||'的老板是'||b.ename from emp e,emp b where e.mgr=b.empno;--【注意：自连接操作不太适合操作数据量太大的表，因为存在笛卡尔集，数据的条数是平方的关系】--★★层次查询（解决上面问题）select level,empno,ename,sal,mg  from emp  connect by prior empno=mgr start with mgr is null order by 1; --前面操作的员工号等于后面操作的老板号/*执行过程： 1. KING: start with mgr is null ---> empno=7839                        2. where mgr = 7839; ---> 7566 7698 7782                        3. where mgr in (7566 7698 7782) */

2.2.4 子查询

/*1. 括号2. 合理的书写风格3. 可以主查询的where select from having后面放置子查询4. 【不可以在主查询的group by后面放置子查询】5. ★强调from后面的子查询6. 主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可7. 一般不在子查询使用order by，但在Top-N分析问题中 必须使用order by8. 一般先执行子查询，再执行主查询；但相关子查询除外9. 单行子查询只能使用单行操作符 多行子查询只能使用多行操作符10. 子查询中null*/--查询工资比SCOTT高员工select * from emp where sal > (select sal  from emp where ename='SCOTT');--【可以主查询的where select from having后面放置子查询】select ename,sal,(select job from emp where empno=7839) 一列 from emp;--查询员工的姓名和薪水【强调from后面的子查询】select * from (select ename,sal from emp);--将from后面的这个集合看成一个表--查询部门名称为SALES的员工信息【主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可】select * from emp where deptno=(select deptno from dept where dname='SALES');select e.* from emp e,dept d  where e.deptno=d.deptno and d.dname='SALES'; --也可以用多表查询 --查询部门名称是SALES和ACCOUNTING的员工select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');--查询部门名称是SALES和ACCOUNTING的员工【in 在集合中】select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');--查询工资比30号部门任意一个员工高的员工信息【 any: 和集合中、任意一个值比较，将多行集合变成单行数据】select * from emp where sal > any (select sal from emp where deptno=30);select * from emp where sal > (select min(sal) from emp where deptno=30);  --也可使用min函数--查询工资比30号部门所有员工高的员工信息【 all:和集合的所有值比较】select * from emp where sal > all (select sal from emp where deptno=30);select *  from emp where sal > (select max(sal) from emp where deptno=30); --也可使用max函数--是老板的员工（是老板的员工的员工号在mgr集合当中）select *  from emp  where empno in (select mgr from emp); --★查询不是老板的员工（emp表中的mgr含有null值，不能使用not in）select *  from emp  where empno  not in (select mgr from emp);  --是错误的select * from emp  where empno not in (select mgr from emp where mgr is not null); --正确的)

2.2.5 集合运算

--查询部门号是10和20的员工信息select * from emp where deptno in (10,20);select * from emp where deptno=10 or deptno=20;--按照部门统计各部门不同工种的工资情况select deptno,job,sum(sal) from emp group by rollup(deptno,job);select deptno,job,sum(sal) from emp group by deptno,job union          select deptno,sum(sal) from emp group by deptno union         select sum(sal) from emp; --错误：ORA-01789: 查询块具有不正确的结果列数 /*【 注意的问题】错误原因分析： 1. 参与运算的各个集合必须列数相同 且类型一致 2. 采用第一个集合的表头作为最后的表头 3. 如果排序，必须 在每个集合后使用相同order by 4. 括号 */select deptno,job,sum(sal) from emp group by deptno,job union        select deptno,to_char(null),sum(sal) from emp group by deptno union        select to_number(null),to_char(null),sum(sal) from emp;)

3. 函数

3.1 通用函数

--nvl2(a,b,c) 当a=null时，返回c，否则返回bselect sal*12+nvl2(comm,comm,0) from emp;--COALESCE :从左往右找到第一个不为null的值select comm,sal,COALESCE(comm,sal) from emp;

3.2 单行函数

--★单行函数中的字符函数====================================================--大小写转换select lower('Hello WORLd') 转小写,upper('Hello WORLd') 转大写, initcap('hello world') 首字母大写 from dual;--substr(a,b) 从a中，第b位开始取，取右边所有的字符select substr('hello world',3) from dual;--substr(a,b,c) 从a中，第b位开始取，取c位select substr('hello world',3,4) from dual;--length 字符数  lengthb 字节数select length('hello world')  字符数,lengthb('hello world')  字节数  from dual;--instr(a,b) 从a中查找b，找到返回下标，否则返回0select instr('hello world','ll') from dual;--lpad 左填充 rpad右填充select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右  from dual;--trim: 去掉前后指定的字符select trim('H' from  'Hello WorldH') from dual; --replace 替换 select replace('hello world',  'l','*') from dual;-- ★单行函数中的数值函数==================================================== --四舍五入，也可以操作日期select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三, ROUND(45.926, -1) 四, ROUND(45.926, -2) 五 from dual;--截断，也可以操作日期select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三, TRUNC (45.926, -1) 四, TRUNC(45.926, -2) 五 * from dual;--★单行函数中的日期函数====================================================--Oracle中的日期型数据含有两个值，日期和时间。默认日期格式：DD-MON-RRselect sysdate from dual;   --当前日期--昨天 今天 明天（不允许日期 + 日期 ）select (sysdate-1) 昨天, sysdate 今天,(sysdate+1) 明天  from dual; --+1单位为天--计算员工的工龄select ename,hiredate,(sysdate-hiredate) 天, (sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;--MONTHS_BETWEEN 返回两个日期相差的月数select ename,hiredate,(sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二 from emp;--ADD_MONTHS: 加上若干个月select ADD_MONTHS(sysdate,100) from dual;--next_day：下一个星期三select next_day(sysdate,'星期三') from dual;--提示：next_day应用： 每个星期一做数据备份（涉及分布式数据库）--显示当前时间: 2013-06-19 14:45:23今天是星期三select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是" day') from dual;--制定格式输出：查询员工薪水：（格式） 两位小数 货币代码  千位符select to_char(sal,'L9,999.99') from emp;

3.3 多行函数

--工资总额select sum(sal) from emp;--平均工资select sum(sal)/count(*) 一,avg(sal) 二 from emp;--平均奖金: 空值 5（组函数自动滤空）select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp; --三个平均值，二三是相同的，一是不同的--理解：空值 5: 组函数自动滤空select count(*), count(comm)  from emp; --第一个是14第二个是4（只统计非null的情况），用哪种情况要区分业务要求--屏蔽组函数的自动滤空（为空时用0代替），select count(*), count(nvl(comm,0))  from emp;--求每个部门的平均工资select deptno,avg(sal)  from emp group by deptno; ---写语句时注意逻辑顺序（格式：select a,组函数(x) from ** group by a;）--分组数据--求每个部门的平均工资select deptno,avg(sal) from emp group by deptno;--按照部门统计各部门不同工种的工资情况select deptno,job,sum(sal) from emp group by rollup(deptno,job);--指定显示格式，按照部门统计各部门不同工种的工资情况break on deptno skip 2 --指定显示格式相同deptno只显示一次；不同deptno之间空格两行select deptno,job,sum(sal) from emp group by rollup(deptno,job);--取消上面指定的显示格式break on null

附录

附录1：创建表空间代码模板

-- 创建数据表空间create tablespace 表空间名_datdatafile 'd:\\oracle\\oradata\\表空间名_dat.dbf' size 64m autoextend on next 100mdefault storage (    initial 128K     next 128K     minextents 1     maxextents 505     pctincrease 50);-- 创建临时表空间create temporary tablespace 表空间名_tmptempfile  'd:\\oracle\\oradata\\表空间名_tmp.dbf' size 20m autoextend on next 10m extent management local uniform size 10m;-- 创建索引表空间create tablespace 表空间名_tmp_idxdatafile 'd:\\oracle\\oradata\\表空间名_tmp_idx.dbf' size 128m autoextend on next 100m default storage (    initial 64K     next 64K     minextents 1     maxextents 505     pctincrease 50);-- 创建表用户create user identified by 用户名 default tablespace 表空间名_dat temporary tablespace 表空间名_tmp;-- 为用户授权grant connect, resource ,dba to 用户名;