Oracle 常用 SQL

SQL 语句本身是很简单的，但有的少用，用时易忘，尤其是一些 DDL(data definition language)语句。这里记录一些常见的 DDL 和 DML(data manipulation language)语句，以备不时之需。

DDL

数据库操作

创建数据库

修改数据库

删除数据库

创建数据文件

修改、删除

创建表空间

给表空间添加，删除数据文件等

表操作

表名的限制：必须以字母开头，长度不超过30个字符（9i/10g/11g 皆如此），可以用 sys 用户查到这个限制（参这里）：

select DATA_LENGTH from dba_tab_columns where column_name='TABLE_NAME' and table_name='DBA_TABLES'

表名不能用 Oracle 的保留字，只能用如下字符：a-z, A-z, 0-9, $, #等

1. 创建表

例如：

create table uims_users_info (
            userId number,
            realname nvarchar2(50) not null, -- 姓名
            birthday Date, -- 出身年月日
            sex char(1) default('M') check in('M', 'F'),-- 性别，需要考察一下用中文‘男‘’女‘时，是否需要2个字节？
            email varchar2(100) not null,
            mobile varchar2(20),
            pic   blob,
            info    nclob -- 庞大的个人信息存放空间
      );

下面对上表添加主键(PK, primary key)和外键(foreign key)约束。定义表的约束，可以用两种方式。一种是“列级定义“，在定义表的同时将约束定义好，一种是“表级定义“，在定义好表之后再通过 alter table 来定义约束。先看“表级定义”：

alter table uims_users_info add constraint pk_uims_users_info primary key(userId); 
alter table uims_users_info add constraint fk_uims_users_info foreign key(userId) references uims_sys_users(userId);

创建备份表

create table xxx_bak as select * from xxx

这里要注意，关键字 as 不能省略，这点和 MySQL 不一样。

2. 约束

接下来尝试删除约束的操作：

alter table 表名 drop constraint 约束名

另外一个新建约束的例子：

alter table EbkImportDocLcApp
 add constraint FK_EbkImportDocLcApp foreign key (EbkImportDocLcAppKy)
 references EBKFINANCIALTRANSACTION (EBKFINACINLTRANSACTKY);

删除主键：

alter table uims_users_info drop primary key

另外一个删除主键的例子：

alter table EBKIMPORTDOCLCAPP drop constraint FK_EBKIMPORTDOCLCAPP;

如果因为外键依赖关系导致无法删除，可以使用 cascade 关键字：

alter table uims_users_info drop primary key cascade

注意，约束信息都记录在表 user_constraints 中。

3. 对属性（列）的操作

添加属性：

alter table EBKIMPORTLCAPPBASIC add DAFTATDAYS2 number;

删除属性：

ALTER TABLE acc_recbankdata DROP COLUMN checkdate;

列名必须用所谓的 simple column name

simple column name

修改属性名：

alter table EBKIMPORTDOCLCAPP rename column PAYEEACOUNT to PAYEEACCOUNT;

修改属性类型：

alter table EBKIMPORTLCAPPBASIC modify LATESTSHIPMENTDATE VARCHAR2(19);

注：这里需要注意的是，新的数据类型，必须能满足该列现有的所有数据。如果要跨类型调整，比如字符型改成数字型，就需要将该列原先的数据清空以后再操作。

4. 删除表

该操作会将表数据和表结构都删除了，例如：

drop table uims_sys_users;

Sequence

创建序列（Sequence）

create sequence seq_uims_sys_users minvalue 1 start with 1 increment by 1;

使用时，用 seq_name.nextval 和 seq_name.currval 得到下一个和当前值。

删除序列

drop sequence seq_uims_sys_users;

DML

这部分主要是常见的对数据的增删改插(CRUD)操作。

查询 Query

查询的结构是 select ... from ... where ... 数据库系统扫描 where 条件时，从右到左，具体写的时候，筛选数据多的条件写最后。

where

where 条件中的作为右值的大小写是敏感的。

select a.sal, a.job, b.dname 
  from emp a, dept b 
 where a.deptno = b.deptno 
   and a.ename = 'SMITH' -- 大小写敏感的。

between

between 关键字。原则上可以通过 AND 两个比较表达式。

select a1.ename, a1.sal, a2.grade 
  from emp a1, salgrade a2 
where a1.sal between a2.losal and a2.hisal

排序

ESC, ascending order 顺序

DESC, descending order 反序

用多个字段排序时，排序的优先级从左到右。可以使用别名排序： 

select ename, sal*12 "年薪" from emp order by 年薪;

上面的别名用不用但双引号都可以，虽然是汉字，有点不可思议，呵呵。

查询空值

查询空值比较特殊，需要用 is null, 而不能用 = null，例如：

select * from student where birthday is null;

这里不能用 select * from student where birthday = null 这样是查不出来的。

空值的计算

和空值作运算，结果总是空。需要引入 nvl 运算符。

select ename "姓名", (sal*13+comm) "年总收入" from emp; --这里用双引号的

姓名 年总收入

---------- ------------

SMITH

ALLEN 21100

WARD 16750

JONES -- 奖金有为空的，相加得到空。

MARTIN 17650

修改版本：

select ename "姓名", (sal*13+nvl(comm,0)) "年总收入" from emp; --nvl(a, b) 如果a为null，这取值b

like

like 的通配符 % 表示任意多字符，_ 表示单个字符

UNION

UNION 对两个集合作“并”运算，相同记录之保存一条。另外有一个类似的运算符 UNION ALL，也是对两个集合做并运算，但相同的元素都保留。

举例：使用 UNION:查最高和最低工资：

select max(sal) from emp
union
select min(sal) from emp;
或
select max(sal), min(sal) from emp

注：有关 UNION 和 UNION ALL 的区别，详细可以参考本站另一篇短文。

子查询

同时显示最高工资和该工资的员工名：

select ename, sal from emp where sal = (select max(sal) from emp);

Group by 子句

如 select 项中有列和分组函数，那么这些列应该出现在 group by 子句中，否则会报错：“ORA-00979 不是 GROUP BY 表达式” 。分组函数只能出现在选择列表， having，order by 子句中。

例1：显示每个部门的最高工资和平均工资

select avg(sal), max(sal), dept.dname from emp, dept where emp.deptno = dept.deptno group by dept.dname;

例2：显示每个部门下每个工种的平均工资和最高工资

select avg(sal), max(sal) from emp group by deptno, job; -- 先按部门，再按工种分组。

例3：显示平均工资低于2000的部门号和它的平均工资

select deptno, sal2
  from (select deptno, avg(sal) sal2 from emp group by deptno)
 where sal2 < 2000

如果在select 语句中同时包含 where, group by, having, order by 顺序应该如前所写，where 先对表的结果进行过滤，group by 确定分组的依据，group by 对分组后的查询结果再过滤，order by 设置排序字段。

select avg(sal), max(sal), dept.dname
  from emp, dept
 where emp.deptno = dept.deptno
   and emp.hiredate < to_date('1982-12-05', 'yyyy-mm-dd')
 group by dept.dname
having avg(sal) > 2000
 order by avg(sal);

以上查询的含义是：查询各部门在1982-12-05前出生的人的平均工资，只显示平均工资大于2000的查询结果，并按平均工资升序排列。

多表查询

n 张表，至少需要 n-1 个查询条件，才可能排出笛卡尔积。不要联太多，四五张已经很复杂了。

自连接。连接中有一种自身连接自身的关系，称为“自连接”，例如：查询员工的上级 

select a.ename "员工名",
       a.empno "员工号",
       a.mgr   "员工上司编号",
       b.ename "上司",
       b.empno "上司号"
  from emp a, emp b
 where a.mgr = b.empno

或者写成：

select worker.ename, boss.ename
  from emp worker, emp boss
 where worker.mgr = boss.empno
   and worker.ename = 'FORD'

另外一个自连接例子：显示与Simth同一部门的所有员工

方法一：子查询（单行子查询）

select ename
  from emp
 where deptno = (select deptno from emp where ename = 'SMITH')
 order by ename;

或方法二：自连接

select b.ename
  from emp a, emp b
 where a.deptno = b.deptno
   and a.ename = 'SMITH'
 order by b.ename

有关表连接的介绍，可以参考本站《表的连接》一文。

多行子查询

查出和部门10工作相同的雇员名，岗位，工资，部门号

select ename, job, sal, deptno
  from emp
 where job in (select job from emp where deptno = 10);

或用连接的方式改写

select a.ename, a.job, a.sal, a.deptno
  from emp a, emp b
 where a.job = b.job
   and b.deptno = 10

工资比部门10的所有员工的工资都高的员工信息

select emp.ename, emp.job, emp.sal
  from emp
 where sal > all (select sal from emp where deptno = 10);

可考虑用下面的方式改写，上面那句的效率要高很多，因为按 Oracle 的扫描方式，先用函数算了。

select ename, job, sal
  from emp
 where sal > (select max(sal) from emp where deptno = 10);

in 关键字也常用子查询，如：查询和Smith的部门很岗位相同的雇员信息：

select *
  from emp
 where (job, deptno) in (select job, deptno from emp where ename = 'SMITH')

在 from 子句中也使用子查询，如：如何显示高于自己部门平均工资的员工信息

select a.*
  from emp a, (select deptno, avg(sal) avgsal from emp group by deptno) b
 where a.deptno = b.deptno
   and a.sal > avgsal

注：在from字句中使用子查询，会被当作视图对待，即“内嵌视图”，必须给这个内嵌视图定义别名。给表起别名时不能用 as，给列起别名可用可不用as

下面这个查询理解有误，“自己部门”是指员工所在的自己部门。 

select * 
  from emp 
 where sal > 
       (select avg(sal) 
          from emp 
         where deptno = (select deptno from emp where ename = 'SMITH'))

更新 Update

注意 update ... (a, b) =(...) 的形式，在 JDBC 中使用的话，只能在有子查询的情况下才能用。

插入 Insert

举例如下：

insert into uims_sys_users
  (userid, username, passwd, roleid, createddate, activeflag)
values
  (seq_uims_sys_users.nextval, 'admin', 'hello', '1', sysdate, 'Y');

表数据的自我复制(Funny, weird)：

insert into helloworld select * from helloworld;

删除数据 Delete

删除数据

delete from student where ...

注：使用 delete 删除表的数据，表结构还在，写日志，可以恢复数据，速度慢。

如果想要快速清空一张表中的数据，可以使用 truncate 方式：

truncate table student

注：使用 truncate 删除表中所有记录，表结构在，不写日志，无法找出删除记录，速度快。

删除一张表，包括表的结构和数据

drop table student;

延伸阅读：

https://en.wikipedia.org/wiki/Data_definition_language

http://en.wikipedia.org/wiki/Data_manipulation_language

http://www.w3school.com.cn/sql/