IT Notes‎ > ‎Database‎ > ‎Oracle‎ > ‎

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;

延伸阅读:

Comments