Oracle常用语句语法汇总

1 第一章Oracle命令
a) 系统管理员连接 conn */* as sysdba
b) 查询当前用户 show user
c) 创建新用户 create user 用户名 identified by 密码（密码不能以数字开头）。例如create user abc identified by cba

d) 用户登录 conn 用户名/密码。例如conn abc/cba
e) 用户授权 grant 权限 to 用户。例如grant connect,resource to abc；grant select on scott.emp to abc

f) 收回权限 revoke 权限 from 用户。例如revoke resource from abc；revoke select on scott.emp from abc g) 修改密码
alter user用户名 identified by 新密码。例如alter user abc identified by cba12
h) 锁定用户 alter user用户名 acco
unt lock。例如alter user scott account lock

i) 解锁用户 alter user用户名 account unlock。例如alter user scott account unlock

j) 创建表空间 create tablespace 表空间名 datafile 表空间文件路径 size 初始大小 autoextend on(/off)。例如create
tablespace svse ‘c:\1.dbf’ size 10m autoextend on

k) 为某个用户指定表空间 alter user 用户名 default tablespace 表空间名

l) 修改表空间的文件大小：alter database datafile 路径(路径要加’) resize 新大小。例如alter database datafile ‘c:
\1.dbf’ resize 20m

m) 向表空间添加文件：alter tablespace 表空间名 add datafile 路径 size 初始大小。例如alter tablespace svse add datafile
‘c:\2.dbf’ size 5m

n) 让表空间文件自动扩展：alter database datafile 路径 autoextend on next 每次扩展量 maxsize 文件的最大值。例如alter
database datafile ‘c:\2.dbf’ autoextend on next 5m maxsize 50m

o) 修改表空间的名字：alter tablespace 表空间原名 rename to 新名。注意这个命令是10G新增加的，在9I中不能运行。

p) 使表空间临时脱机。使表空间脱机就相当于sqlserver2005中的分离数据库，就是让服务器不再管理这个表空间了：alter
tablespace 表空间名 offline temporary

q) 使表空间联机。相当于sqlserver2005中的附加数据库，就是让服务器重新管理这个表空间：alter tablespace 表空间名 online

r) 删除表空间。如果表空间里面有对象用：drop tablespace 表空间名 including contents。如果表空间里什么也没有用drop
tablespace 表空间名

s) 更改环境变量 设置每行显示 set linesize 大小 设置每页显示 set pagesize 大小。例如set pagesize 500，set lines 300

t) 设置sqlplus代码保存路径：spool on 路径，注意在9i下路径不能加’，在10G下可以加。保存代码spool off。例如spool on
‘c:\1.sql’；

u) 查看表的结构：desc 表名。例如：desc scott.emp

v) 代码错误后修改：edit/ed。 注意在弹出的文本文件中不能在结尾加分号

w) 执行外部文件用下面3个命令中的任何一个都可以：start / @ 文件路径。例如：@ ‘c:\1.sql’

x) 清屏命令：clear screen第二章Oracle命令 修改会话的日期格式信息：alter session set nls_date_format=’yyyy-mm-dd’
显示当前日期：select sysdate from dual。注意oracle规定如果一个函数没有参数则不能加()，sysdate就没有参数所以没加()
to_date函数是把一个字符串按指定的格式转换成日期。例如to_date(‘1-20-2000’,’mm-dd-yyyy’)返回的就是2002年1月20日这个
日期 to_char函数把一个日期按指定格式转换为字符。例如to_char(sysdate, ‘yyyy-mm-dd’) to_number(‘123’)函数把一个字符
串转换为数字 伪列rowid存储的是这条记录在硬盘上的绝对位置

第一篇 基本操作

--解锁用户 alter user 用户 account unlock;
--锁定用户 alter user 用户 account lock;
alter user scott account unlock;

--创建一个用户yc 密码为a create user 用户名 identified by 密码；
create user yc identified by a;

--登录不成功，会缺少create session 权限，赋予权限的语法 grant 权限名 to 用户；
grant create session to yc;

--修改密码 alter user 用户名 identified by 新密码；
alter user yc identified by b;

--删除用户
drop user yc ;

--查询表空间
select *from dba_tablespaces;
--查询用户信息
select *from dba_users;
--创建表空间
create tablespace ycspace
datafile 'E:\oracle\app\product\11.2.0\dbhome_1\oradata\ycspace.dbf'
size 2m
autoextend on next 2m maxsize 5m
offline ;

--创建临时表空间
create temporary yctempspace
tempfile 'E:\oracle\app\product\11.2.0\dbhome_1\oradata\ycspace.dbf'
size 2m
autoextend on next 2m maxsize 5m
offline ;

--查询数据文件
select *from dba_data_files;

--修改表空间

--1、修改表空间的状态
--默认情况下是online,只有在非离线情况下才可以进行修改
alter tablespace ycspace offline ; --离线状态，不允许任何对象对该表空间的使用，使用情况：应用需要更新或维护的时候；数据库备份的时候
alter tablespace ycspace read write;--读写状态
alter tablespace ycspace online;
alter tablespace ycspace read only; --只读，可以查询信息，可以删除表空间的对象，但是不能创建对象和修改对象 。使用情况：数据存档的时候

--2、修改表空间的大小
--增加文件的大小
alter database datafile 'E:\oracle\app\product\11.2.0\dbhome_1\oradata\ycspace.dbf' resize 10m;
--增加数据文件
alter tablespace ycspace add datafile 'E:\oracle\app\product\11.2.0\dbhome_1\oradata\add.dbf' size 2m;

--删除表空间的数据文件
alter tablespace 表空间的名字 drop datafile 数据文件名；

--删除表空间
drop tablespace ycspace;

--删除表空间且表空间中的内容和数据文件
drop tablespace ycspace including contents and datafiles;

--指定表空间 的 创建用户的语法
create user yc1 identified by a default tablespace ycspace temporary tablespace temp;

--删除用户
drop user yc1;

--权限
--赋予创建会话的权限
grant create session to yc1;

--创建一个表
create table studentInfo(
sid int,
sname varchar2(10)
);

--赋予yc1用户创建表的权限
grant create table to yc1;
--赋予yc1使用表空间的权限
grant unlimited tablespace to yc1;

--系统权限

--对象权限

--插入
insert into studentInfo values (2,'abcd');
--查询
select *from studentInfo;
--修改
update studentInfo set sid=1;
--删除
delete studentInfo ;
drop table studentInfo; --系统权限删除表

--赋权的语法

--系统权限
grant 权限名（系统权限或对象权限,角色，all) to 用户(角色，public) with admin option;

--对象权限

grant 权限名（系统权限或对象权限,角色，all) on 用户(角色，public) with grant option;

--收权语法
--系统权限
revoke 权限名（系统权限或对象权限,角色，all) from 用户(角色，public) with admin option;
--对象权限
revoke 权限名（系统权限或对象权限,角色，all) from 用户(角色，public) with grant option;

--赋予创建用户的权限并且把这个权限传递下去，即yc1可以给别人赋权
grant create user to yc1 with admin option;

--收回权限,只能收回scottd ,不能收回由scott赋权的yc1的权限
revoke create user from scott;

--查看用户所具有的权限
select *from user_sys_privs;

--对象权限详解
select * from emp;
--使用yc1来查询scott里面的emp表
select * from scott.emp;

--赋予yc1查询emp表和插入的权限
grant select on emp to yc1;
grant insert on emp to yc1;
grant update(empno,ename) on emp to yc1;

grant delete on emp to yc1;

--对scott的emp表添加数据
insert into scott.emp(empno,ename) value(111,'acv');
update scott.emp set ename='yc'where empno=111;

--赋予查询、赋予删除、添加、修改
grant select on 表名 to 用户

--grant select,delete,update,insert on 表名 to 用户
grant select,delete,update,insert on emp to yc1;
grant all on dept to yc1; --all代表所有的对象权限

select *from scott.emp;

select *from scott.dept;
insert into scott.dept values(50,'企事业文化部','bumen');

--查看角色
--dba:数据库管理员，系统最高权限，可以创建数据结构（表空间等）
--resource:可以创建实体（表、视图），不可以创建数据库的结构
--connect:连接的权限，可以登录数据库，但是不可以创建实体和不可以创建数据库结构

select *from role_sys_privs;

grant connect to yc1;

--将可以连接的角色赋予给yc1，则yc1就是应该可以连接数据库的人，类似于 create session 。
create table StuInfos(sid int);

select *from StuInfos;

create table stuInfo(
sid int primary key , --主键 primary key 非空且唯一 （主键约束）
sname varchar2(10) not null, --姓名不能为空,（非空约束）
sex char(2) check(sex in('男','女')), --（检查约束），check,
age number(3,1) constraint ck_stuInfo_age check(age>10 and age=2;

--修改记录的语法
--update 表名 set [字段='值' ] [where 条件]
update classInfo set cname='三班'; --会修改所有该字段
update classInfo set cname='四班' where cid=1;
update classInfo set cname='五班', stasuts ='未毕业' where cid=3;

--alter table classInfo drop constraint SYS_C0011213;

--添加多个时可以使用序列
--用序列来做自动增长
create sequence seq_classInfo_cid start with 1001 increment by 1;

insert into classInfo values(seq_classInfo_cid.Nextval,'七班','未毕业');
insert into classInfo values(seq_classInfo_cid.Nextval,'八班','未毕业');
insert into classInfo values(seq_classInfo_cid.Nextval,'九班','未毕业');
insert into classInfo values(seq_classInfo_cid.Nextval,'十班','未毕业');

create table classInfo2(
cid int primary key, --班级id
cname varchar2(20) not null unique , --班级名
stasuts varchar2(100)

);
select *from classInfo2;
drop table classInfo2;

insert into classInfo2 select *from classInfo;
insert into classInfo(cname,cid) select cname,cid from classInfo;
alter table classInfo2 drop constraint SYS_C0011213;

select seq_classInfo_cid.nextval from dual;
select seq_classInfo_cid.Currval from dual;

--直接创建一个新表，并拿到另一个表其中的数据
create table newTable as select cname,cid from classInfo;
create table newTable1 as select *from classInfo;

select *from newTable;
select *from newTable1;
insert into newTable1 values(1008,'dg','');

第二篇：高级操作

直接在使用scott登陆，进行查询操作
----------------------------------------------------------------------------------
--简单查询
select *from emp;

select empno as id,ename as name from emp;

select empno 编号,ename 姓名 from emp;

--去除重复
select job from emp;
select distinct job from emp;
select job,deptno from emp;
select distinct job,deptno from emp;

--字符串的连接
select '员工编号是' ||empno || '姓名是' ||ename ||'工作是'||job from emp;

--乘法
select ename,sal *12 from emp;
--加减乘除都类似

---------------------------------------------------------------------
--限定查询
--奖金大于1500的
select *from emp where sal>1500;
--有奖金的
select *from emp where comm is not null;
--没有奖金的
select *from emp where comm is null;
--有奖金且大于1500的
select *from emp where sal>1500 and comm is not null;
--工资大于1500或者有奖金的
select *from emp where sal>1500 or comm is not null;
--工资不大于1500且没奖金的
select *from emp where sal1500 or comm is not null);
--工资大于1500但是小于3000的
select *from emp where sal>1500 and sal(select avg(sal) from emp);

--查询各部门中工资比本部门平均工资高的员工号，姓名和工资
select t.empno,t.ename,t.sal
from emp t, (select avg(e.sal) avgsal,e.deptno from emp e group by e.deptno) a
where t.sal>a.avgsal and t.deptno=a.deptno;

--查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select t.empno,t.ename from emp t
where t.deptno in( select e.deptno from emp e where e.ename like '%U%')
and t.empno not in ( select e.empno from emp e where e.ename like '%U%') ;

--查询管理者是king的员工姓名和工资
select t.ename,t.sal from emp t
where t.mgr in
(select e.empno from emp e where e.ename='KING');

-------------------------------------------------------------------------------------
---sql1999语法
select *from emp join dept using(deptno) where deptno=20;
select *from emp natural join dept;
select *from emp e join dept d on e.deptno=d.deptno;
select *from dept;
select *from dept d left join emp e on d.deptno=e.deptno;
select *from dept d,emp e where d.deptno=e.deptno(+);

---分组
select count(empno) from emp group by deptno;
select deptno,job,count(*) from emp group by deptno,job order by deptno;
select *from EMP for UPDATE;

--group by 后面有的字段，select后才可以有，group by后面没有的字段，select后面绝对不能有
select d.dname, d.loc, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by d.dname, d.loc ;

----------------------------------------------------------------------------------------------------
--子查询
select *from emp t where t.sal>(select *from emp e where e.empno=7654);

select rownum ,t.* from emp t where rownum 0;

--索引
create index person_index on person(p_name);

--视图
create view view2 as select *from emp t where t.deptno=20;
select *from view2;