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=1
truncate 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 by
8. 一般先执行子查询，再执行主查询；但相关子查询除外
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，否则返回b
select 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，找到返回下标，否则返回0
select 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-RR
select 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 表空间名_dat
datafile 'd:\\oracle\\oradata\\表空间名_dat.dbf' 
size 64m autoextend on next 100m
default storage (
    initial 128K 
    next 128K 
    minextents 1 
    maxextents 505 
    pctincrease 50
);

-- 创建临时表空间
create temporary tablespace 表空间名_tmp
tempfile  'd:\\oracle\\oradata\\表空间名_tmp.dbf' 
size 20m autoextend on next 10m extent management local uniform size 10m;

-- 创建索引表空间
create tablespace 表空间名_tmp_idx
datafile '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 用户名;