oracle常用sql语句学习

在本文中主要记录一些常用的sql语句的写法，供以后查看；本文参考自：http://wenku.baidu.com/link?url=O8W1lSMvIi91CYXtQM9oq2aaCy25ok_J8xTRSUVMqKmQCSAojs1gR6DmjzXQr962Xvn80Lf30dy8SwvX2WSk3IdjSg0KHF1jWL41bEcCieq&pn=51

1.创建用户：

create user user_name identified by password account unlock;

 2.给用户分配权限：

grant connect to user_name

 3.回收用户权限：

revoke connect from user_name

4.创建表：

create table table_name (id int not null,name varchar(10) not null)

 5.创建约束：

(1).创建主键约束：

alter table table_name add constraint 约束名称 primary key(主键字段)

 (2).创建检查约束：

alter table table_name add constraint 检查名称 check(检查条件，比如：age>0 and age<100)

 (3).创建外键约束：

alter table table_name1 add constraint 外键名称 foreign key (table_name1的关联字段) references table_name2(关联字段)

 6.集合运算的sql语句：(集合运算主要是将两个或多个结果集合并为一个结果集)，

具体方法有以下几种：

(1).返回几个查询共有的记录--intersect(交集)

select deptno from dept
intersect
select deptno from emp;

(2).返回几个查询的所有记录，记录可以重复--union all(并集)

select deptno from dept
union all
select deptno from emp;

 (3).返回几个查询的所有记录，记录不重复--union(并集)

select deptno from dept
union
select deptno from emp;

 (4).返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录--minus(补集)

select deptno from dept
minus
select deptno from emp;

 7.子查询：

(1).单行子查询：

select ename,job,sal
from emp
where sal=(
                select sal from emp where ename='SMITH'
               )

单行子查询比较简单，就是子查询只返回一个结果集，这个时候使用的运算符号可以是=，<=,>=,<,>等这些比较简单的符号。

(2).多行子查询：

select ename,job,sal from emp where sal < any(select sal from emp where job='SALESMAN');

 

select ename,job,sal from emp where sal < ALL(select sal from emp where job='SALESMAN');

 这个子查询返回多个查询结果，这里就不能像单行子查询那样使用=,<等比较简单的符号，如上面的语句，涉及到两个词汇any，all。

--all表示比子查询结果集中任意一个值都大或者小的那些结果。

--any表示比子查询结果集中最大的值都小或者最小的值都大的那些结果。

8.分页：

select * from (select rownum r,ename,job,sal from emp where rownum<=10) where r>5;

 9.nvl函数使用：

(1).nvl的使用：

SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000; 

 这条sql语句表示给工资小于2000没有发放奖金的员工发放奖金100元。

(2).nvl2的使用：

select ename,job,sal,comm,nvl2(comm,comm+100,200) from emp where sal<2000;

 这条sql语句表示给工资小于2000的员工添加100元奖金，如果以前没有奖金的员工添加奖金200元。

10.同义词

(1).同义词的创建：这是为scott用户下面的t_user表创建了一个同义词，这样在这个用户下面可以使用myUser进行访问scott用户下面的那个t_user表

create synonym myUser for scott.t_user

 (2).同义词的删除：

drop synonym myUser;

 11.序列

(1)序列的创建：

create sequence sequence_name --序列的名称
start with 1 --序列的开始值
increment by 1 --序列增长的值
minvalue 1 --序列的最小值(NOMINVALUE)
maxvalue 100 --序列的最大值(NOMAXVALUE)
nocycle --增长或减小到两个极端值时是否进行循环，这里不循环，使用cycle则循环
cache 30;--缓存多少个值在内存中，便于快速的生成序列

 (2)序列的使用

select sequence_name.nextval from dual;
select sequence_name.currval from dual;

 (3)序列的修改：

alter sequence sequence_name
maxvalue 1000
minvalue 10;

 (4)序列的删除：

drop sequence sequence_name;

 12.视图(VIEW)

创建视图：

create or replace view v_user
as
select name,age from t_user
with read only;

 删除视图：

drop view v_user;

 13.索引(index)

创建索引可以提升对表的查询效率，但是在表进行增删改的时候需要更新其对应的索引。因此索引对增删改具有负面效应。

索引的创建：

create index myIndex on t_user(id);

 这个sql语句表示对t_user表的id字段创建了myIndex的索引。

14.表空间(tablespace)

创建表空间：

create tablespace space_name
datafile 'e:/a.ora' size 10M,
            'e:/b.ora' size 5M
extent management local
uniform size 1M;

 注意：表空间只能有系统管理员来进行创建，当表空间内存不足时可以使用alter tablespace对表空间进行扩容。

alter tablespace space_name
add datafile 'e:/c.ora' size 10M;

 为某一用户指定表空间：

CREATE USER ACONG IDENTIFIED BY ACONG
DEFAULT TABLESPACE MYSPACE 

 为表指定表空间：

create table table_name(id int,name varchar(10)) tablespace space_name;

 为索引创建表空间：

create index index_name on table_name(column_name) tablespace space_name

 注意：表及索引一旦创建，表空间无法更改。