Oracle常用语句语法汇总

Oracle常用语句语法汇总

第一篇 基本操作

--解锁用户 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<100) , --也可以用varchar ；age between 10 and 100 ,在10和100之间，是一个闭区间
tel number(15) unique not null, --唯一约束，
address varchar2(200) default '什么鬼'
)


insert into stuInfo values（3,'大大','男',18，4321543,default);
insert into stuInfo values(1,'张三','男',10);
select *from stuInfo;

drop table stuInfo;


create table classInfo(
cid int primary key, --班级id
cname varchar2(20) not null unique --班级名
)
create table stuInfo(
sid int primary key,
sname varchar2(20),
cid int constraint fofk_stuInfo_cid references classInfo(cid) on delete cascade
)
insert into classInfo values(1,'1班');
insert into classInfo values(2,'2班');
insert into classInfo values(3,'3班');
insert into classInfo values(4,'4班');


select *from classInfo;
select *from stuInfo;

insert into stuInfo values(1001,'张三',2);
insert into stuInfo values(1002,'张四',4);

update classInfo set cid=1 where cid=8;

drop table stuInfo;--要先删除这个
drop table classInfo; --再删除这个

delete classInfo where cid=4 ;--同时删除这两个表中的4


--删除用户的时候
drop user yc1 [cascade] --删除用户的同时把它创建的对象都一起删除


--修改表
--1、添加表中字段
--alter table 表名 add 字段名 类型
alter table classInfo add status varchar2(10) default '未毕业'


--2、修改已有字段的数据类型
--alter table 表名 modify 字段名 类型
alter table classInfo modify status number(1)

--3、修改字段名
--alter table 表名 rename column 旧字段名 to 新的字段名
alter table classInfo rename column cname to 班级名;

--4、删除字段
--alter table 表名 drop column 字段名
alter table classInfo drop column status ;


--5、修改表名
--rename 旧表名 to 新表名
rename classInfo to 班级信息；

--删除表
--1、截断表效率高，每删除一次会产生一次日志 2、截断会释放空间，而delete不会释放空间
--删除表结构和数据
drop table 表名；
--删除表中所有数据
truncate table classInfo;
delete classInfo;


create table classInfo(
cid int primary key, --班级id
cname varchar2(20) not null unique , --班级名
stasuts varchar2(100)
);
select *from classInfo;

--数据的操作

--增加数据语法
--insert into 表名[(列名，....)] values (对应的数据的值）；
insert into classInfo values(1,'一班','未毕业');--需要按照表结构的顺序插入
insert into classInfo values(4,'六班','未毕业');
insert into classInfo(cname,cid) values('二班',2); --需要按照括号中的顺序插入，但是 not null primary key 必须插入的。
insert into classInfo(cname,cid) values('三班',3);

--删除的语法
--delete 表名 [where 条件]
delete classInfo where cid>=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 sal<=1500 and comm is null;
select *from emp where not (sal >1500 or comm is not null);
--工资大于1500但是小于3000的
select *from emp where sal>1500 and sal<3000;
select *from emp where sal between 1500 and 3000; --between是闭区间，是包含1500和3000的
--时间区间
select *from emp where hiredate between to_date('1981-01-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
--查询雇员名字
select *from emp where ename='SMITH';
--查询员工编号
select *from emp where empno=7369 or empno=7499 or empno=7521;
select *from emp where empno in(7369,7499,7521);
select *from emp where empno not in(7369,7499,7521); --排除这3个，其他的都可以查

--模糊查询
select *from emp where ename like '_M%'; --第2个字母为M的
select *from emp where ename like '%M%';
select *from emp where ename like '%%'; --全查询

--不等号的用法
select * from emp where empno !=7369;
select *from emp where empno<> 7369;




--对结果集排序
--查询工资从低到高
select *from emp order by sal asc;
select *from emp order by sal desc,hiredate desc; --asc 当导游列相同时就按第二个来排序
--字符函数
select *from dual;--伪表
select 2*3 from dual;
select sysdate from dual;
--变成大写
select upper('smith') from dual;
--变成小写
select lower('SMITH') from dual;
--首字母大写
select initcap('smith') from dual;
--连接字符串
select concat('jr','smith') from dual; --只能在oracle中使用
select 'jr' ||'smith' from dual; --推荐使用
--截取字符串
select substr('hello',1,3) from dual; --索引从1开始
--获取字符串长度
select length('hello') from dual;
--字符串替换
select replace('hello','l','x') from dual; --把l替换为x
--------------------------------------------------------------------------------------------------
--通用函数
--数值函数
--四舍五入
select round(12.234) from dual;--取整的四舍五入 12
select round (12.657,2) from dual; --保留2位小数
select trunc(12.48) from dual;--取整
select trunc(12.48675，2) from dual; --保留2位小数
--取余
select mod(10,3) from dual;--10/3取余 =1

--日期函数
--日期-数字=日期 日期+数字=日期 日期-日期=数字

--查询员工进入公司的周数
select ename,round((sysdate -hiredate)/7) weeks from emp;
--查询所有员工进入公司的月数
select ename,round(months_between(sysdate,hiredate)) months from emp;
--求三个月后的日期
select add_months(sysdate,6) from dual;
select next_day(sysdate,'星期一') from dual; --下星期
select last_day(sysdate) from dual; --本月最后一天
select last_day(to_date('1997-1-23','yyyy-MM-dd')) from dual;

--转换函数
select ename ,
to_char(hiredate,'yyyy') 年，
to_char(hiredate,'mm')月，
to_char(hiredate,'dd') 日
from emp;

select to_char(10000000,'$999,999,999') from emp;

select to_number('20')+to_number('80') from dual; --数字相加

--查询员工年薪
select ename,(sal*12+nvl(comm,0)) yearsal from emp; --空和任何数计算都是空

--Decode函数，类似if else if (常用）
select decode(1,1,'one',2,'two','no name') from dual;

--查询所有职位的中文名
select ename, decode(job,
'CLERK',
'业务员',
'SALESMAN',
'销售',
'MANAGER',
'经理',
'ANALYST',
'分析员',
'PRESIDENT',
'总裁',
'无业')
from emp;

select ename,
case
when job = 'CLERK' then
'业务员'
when job = 'SALESMAN' then
'销售'
when job = 'MANAGER' then
'经理'
when job = 'ANALYST' then
'分析员'
when job = 'PRESIDENT' then
'总裁'
else
'无业'
end
from emp;


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

--多表查询

select *from dept;
select *from emp,dept order by emp.deptno;
select *from emp e,dept d where e.deptno=d.deptno;
select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

--查询出雇员的编号，姓名，部门编号，和名称，地址
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

--查询出每个员工的上级领导
select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;

select e.empno,e.ename,d.dname
from emp e,dept d ,salgrade s, emp e1
where e.deptno=d.deptno
and e.sal between s.losal
and s.hisal
and e.mgr=e1.empno;

select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno(+) ;



--外连接
select *from emp order by deptno;
--查询出每个部门的员工
/*
分析：部门表是全量表，员工表示非全量表，
在做连接条件时，全量表在非全量表的哪端，那么连接时全量表的连接条件就在等号哪断
*/
--左连接
select * from dept d,emp e where d.deptno=e.deptno(+) order by e.deptno;
--右连接
select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;


-----------------------------作业
--查询与smith相同部门的员工姓名和雇佣日期
select *from emp t
where t.deptno= (select e.deptno from emp e where e.ename='SMITH')
and t.ename<> 'SMITH';

--查询工资比公司平均工资高的员工的员工号，姓名和工资
select t.empno,t.ename,t.sal
from emp t
where t.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 <6 ;

--pagesize 5
select *from(select rownum rw,a.* from (select *from emp ) a where rownum <16) b where b.rw>10;
select *from (select *from emp) 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;



--------------------------------------------------------------------------------------------------------
--pl/sql
--plsql是对sql语言的过程化扩展
-----
declare
begin
dbms_output.put_line('hello world');
end;
-------
declare
age number(3);
marry boolean := true; --boolean不能直接输出
pname varchar2(10) := 're jeknc';
begin
age := 20;
dbms_output.put_line(age);
if marry then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if ;
dbms_output.put_line(pname);
end;

--常量和变量
--引用变量,引用表中的字段的类型
Myname emp.ename%type; --使用into来赋值


declare
pname emp.ename%type;
begin
select t.ename into pname from emp t where t.empno=7369;
dbms_output.put_line(pname);
end;

--记录型变量
Emprec emp%rowtype; --使用into来赋值

declare
Emprec emp%rowtype;
begin
select t.* into Emprec from emp t where t.empno=7369;
dbms_output.put_line(Emprec.empno || ' '||Emprec.ename||' '||Emprec.job);
end;

--if分支

语法1：
IF 条件 THEN 语句1;
语句2;
END IF;
语法2：
IF 条件 THEN 语句序列1；
ELSE 语句序列 2；
END IF；
语法3：
IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSE 语句;
END IF;
--1
declare
pname number:=#
begin
if pname = 1 then
dbms_output.put_line('我是1');
else
dbms_output.put_line('我不是1');
end if;
end;

--2
declare
pname number := #
begin
if pname = 1 then
dbms_output.put_line('我是1');
elsif pname = 2 then
dbms_output.put_line('我是2');
else
dbms_output.put_line('我不是12');
end if;
end;



--loop循环语句
语法2：
Loop
EXIT [when 条件];
……
End loop


--1
declare
pnum number(4):=0;

begin
while pnum < 10 loop
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;

--2 (最常用的循环)
declare
pnum number(4):=0;
begin
loop
exit when pnum=10;
pnum:=pnum+1;
dbms_output.put_line(pnum);
end loop;
end;

--3
declare
pnum number(4);
begin
for pnum in 1 .. 10 loop
dbms_output.put_line(pnum);
end loop;
end;

----------------------------------
--游标
语法：
CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;
例如：cursor c1 is select ename from emp;


declare
cursor c1 is
select * from emp;
emprec emp%rowtype;
begin
open c1;
loop
fetch c1
into emprec;
exit when c1%notfound;
dbms_output.put_line(emprec.empno || ' ' || emprec.ename);
end loop;
close c1; --要记得关闭游标
end;


--------例外
--异常，用来增强程序的健壮性和容错性
-- no_data_found (没有找到数据)
--too_many_rows (select …into语句匹配多个行)
--zero_divide ( 被零除)
--value_error (算术或转换错误)
--timeout_on_resource (在等待资源时发生超时)



--写出被0除的例外程序
declare
pnum number(4) := 10;
begin
pnum := pnum / 0;
exception
when zero_divide then
dbms_output.put_line('被0除了');
when value_error then
dbms_output.put_line('算术或转换错误');
when others then
dbms_output.put_line('其他异常');
end;

--自定义异常
--No_data exception;
--要抛出raise no_data;

declare
cursor c1 is
select * from emp t where t.deptno = 20;
no_data exception;
emprec emp%rowtype;
begin
open c1;
loop
fetch c1
into emprec;
if c1%notfound then
raise no_data;
else
dbms_output.put_line(emprec.empno || ' ' || emprec.ename);
end if;
end loop;
close c1;

exception
when no_data then
dbms_output.put_line('无员工');
when others then
dbms_output.put_line('其他异常');
end;


--存储过程
语法：
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体；
End;


或者


create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体；
End 过程名;

-----创建一个存储过程helloworld
create or replace procedure helloworld is
begin
dbms_output.put_line('hello world');
end helloworld;




------创建一个涨工资的
create or replace procedure addsal(eno in emp.empno%type) is
emprec emp%rowtype;
begin
select * into emprec from emp t where t.empno = eno;

update emp t set t.sal = t.sal + 100 where t.empno = eno;
dbms_output.put_line('涨工资前是' || emprec.sal || ',涨工资后是' ||
(emprec.sal + 100));
end addsal;


----------------------------------------------
--java代码调用存储过程和函数
--存储过程
--
create or replace procedure acc_yealsal(eno in emp.empno%type,yearsal out number) is
pcomm emp.comm%type;
psal emp.sal%type;
begin
select t.sal,t.comm into psal,pcomm from emp t where t.empno=eno;
yearsal :=psal*12 +nvl(pcomm,0);
end;

----存储函数
create or replace function 函数名(Name in type, Name in type, .. .)
return 数据类型 is
结果变量 数据类型;
begin


return(结果变量);
end函数名;
--存储函数计算年薪
create or replace function accf_yearsal(eno in emp.empno%type)
return number is
Result number;
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;
Result := psal * 12 + nvl(pcomm, 0);
return(Result);
end accf_yearsal;



-----------------------------------
---触发器
--触发语句：增删改：
语法：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块
End 触发器名

---插入一个新员工则触发
create or replace trigger insert_person
after insert on emp
begin
dbms_output.put_line('插入新员工');
end;


select *from emp;
insert into emp values(1001,'李四','管理',7902,sysdate,100,100,20);


--raise_application_error(-20001, '不能在非法时间插入员工')



--==============================================================================
SQL> @ E:\powerDesigner\A_脚本\user.sql --导入脚本文件

select *from H_USER ;

insert into h_user valuer(sequserid.nextval,'a','a',sysdate,'北京',1);


--------------------------------------------------------------
--数据库建模
--一对多：多的一端是2，箭头指向的是表1，即少的一端
--在实体类中一的一端的实体类有多的一端的实体类的集合属性
--使用powerDesiger进行数据库建模，然后将数据导入，导入到plsql中进行使用


--------------------连接远程数据库
--方法1，修改localhost的地址
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.lan)
)
)
--方法2
--或者直接在登陆界面在database中输入远程数据库的ip地址和端口号进行远程登陆