Oracle 笔记本--常用语句
--查询当前数据库名称 10
v$database 10
--查询所有表空间的名称 10
dba_tablespaces 10
--创建永久表空间 10
create tablespace 10
datafile 10
size 10
autoextend on 10
next 10
maxsize 10
--创建临时表空间 10
create temporary tablespace 10
--删除表空间 10
drop tablespace 10
including contents and datafiles 11
drop tablespace 11
--查询所有用户的名称 11
dba_users 11
--创建用户 11
create user 11
identified by 11
default tablespace 11
quota 11
temporary tablespace 11
--更改用户密码 11
alter user identified by 11
alter user 11
--删除用户 11
drop user cascade 11
drop user 11
--查询用户的系统权限 11
user_sys_privs 11
dba_sys_privs 11
--查询用户的对象权限 12
user_tab_privs 12
dba_tab_privs 12
--查询角色 12
dba_roles 12
--创建角色 12
create role 12
--为角色授权 12
grant to 12
grant 12
--为用户授权 13
--查询用户(角色)与角色的关系 13
dba_role_privs 13
--查询角色与权限的关系 13
--删除角色 13
drop role 13
--创建学生信息表 13
create table 13
--添加主键约束确保字段值不能为空且不能重复 13
primary key 13
--添加非空属性确保字段不能为空 14
not null 14
--添加检查约束确保字段只能填写指定数据 14
check 14
unique 14
--添加默认值 14
default 14
--删除表的列、添加表的列
--创建课程信息表 14
--创建学生成绩表 15
--添加外键约束 15
foreign key 15
--组合主键 16
--查询当前用户的可用数据表 16
--查询表所有信息 16
select 16
--仅查询多字段信息 16
--带时字段带表达式 16
--简单的数学运算 17
dual 17
--获得当前日期时间 17
sysdate 17
systimestamp 17
current_date 17
current_timestamp 17
--使用字段别名 17
as 17
--查询员工姓名和年收入 17
nvl(comm,0) 17
--字符串连接 18
|| 18
--查询时消除冗余 18
distinct 18
--同时查询部门编号与岗位 18
--带条件的查询 18
where 18
ename='CLARK 18
sal>1500 18
sal>1500 18
ename>'FORD' 19
where sal>=800 and sal<=1500 19
where sal between and 19
is null 19
is not null 19
where sal in (800,1100,1500) 19
-- 模糊查询 20
like ‘%A%’ 20
__A% 20
order by asc 20
desc 20
avg(sal) 21
group by 21
max(sal) 21
--交叉连接 22
cross join 22
--内连接 23
inner join 23
--三个表的嵌套查询 23
--求各个部门薪水最高的员工信息 25
伪列 25
rownum 25
--取出第十条记录之后的数据 25
--按薪水由高至低排列,找出第6-10名的员工信息 26
--保存数据 26
insert into 26
values 26
--同时插入多条记录 26
union all 26
--更新数据 27
update set 27
update 27
rollback; 27
--删除数据 27
delete from 27
commit; 27
--数据控制语言 27
grant 27
revoke 27
--各种函数 27
abs 28
ceil 28
floor 28
trunc 28
round 28
dbms_random.value 28
concat 28
initcap 28
upper 28
lower 28
instr 28
length 28
rpad 29
lpad 29
rtrim 29
ltrim 29
substr 29
replace 29
reverse 29
to_char 29
L9999999.9999999 29
--字符变数字 29
to_number 29
--字符变日期,数字变日期 30
to_date 30
add_months 30
sysdate+1 30
count 30
--行最大值,行最小值 30
greatest 30
least 31
分析函数 decode 31
嵌套查询 31
--行级锁 31
--表级锁 31
in share mode 31
in exclusive mode 31
lock 31
--私有同义词 31
synonym for 31
drop synonym 32
--公有同义词 32
public synonym 32
--序列 32
sequence 32
start with 32
.nextval 32
.currval 32
--视图 32
create view 32
--复制表 32
--索引 33
create index on 33
create index 33
create unique index 33
重建索引 33
删除索引 33
组合索引 33
--PL/SQL 33
主体 33
begin 33
end; 34
--声明 34
declare 34
x number; 34
变量赋值 34
x := 1000; 34
系统输出 34
dbms_output.put_line 34
使用某表某字段的类型 34
sal%type 34
if语句 35
if 35
end if; 35
if elsif 35
return 36
case 36
when then 36
when 36
end case; 36
loop 37
loop 37
end loop; 38
exit when 38
while 38
for 39
for cnt in 1..1000 loop 39
异常处理 39
exception 39
no_data_found 39
too_many_rows 40
自定义异常 抛出异常 40
raise 40
raise_application_error 40
raise 40
raise_application_error 41
隐式游标 41
sql%found 41
sql%rowcount 41
显式游标 42
cursor is 42
cur%notfound 42
cursor 42
open 42
fetch 42
close 42
取余mod 42
变量声明同时赋值 43
当前游标current of 43
procedure 43
as 43
带参数的过程 44
带返回值得过程 44
--根据员工编号求取该员工的姓名和薪水 45
function 46
return number 46
--根据部门编号获取该部门的薪水总和 47
--触发器 47
--语句级触发器 47
trigger 47
after 48
on 48
行级触发器 49
for each row 49
:new.a 49
:old.a 49
--查询当前数据库名称
v$database;
select name from v$database;
--查询所有表空间的名称
dba_tablespaces
select tablespace_name from dba_tablespaces;
--创建永久表空间
create tablespace MYTABLESPACE
datafile 'D:\oracle_lesson\oradata\orcl\MYTABLESPACE.DBF'
size 100M
autoextend on next 50M
maxsize 500M;
--创建临时表空间
create temporary tablespace MYTEMP
tempfile 'D:\oracle_lesson\oradata\orcl\MYTEMP.DBF'
size 50M
autoextend on
next 10M
maxsize 200M;
--删除表空间
drop tablespace
including contents and datafiles
drop tablespace mytablespace including contents and datafiles;
drop tablespace mytemp including contents and datafiles;
--查询所有用户的名称
dba_users
select username from dba_users;
--创建用户[注意:密码不用数字也不要和用户名一样]
create user tom
identified by orcl
default tablespace mytablespace
quota 20M on mytablespace
temporary tablespace mytemp;
--更改用户密码
alter user identified by
alter user tom identified by orcl;
--删除用户
drop user cascade
drop user tom cascade;
--查询用户的系统权限
user_sys_privs
dba_sys_privs
select * from user_sys_privs;
select * from dba_sys_privs;
--查询用户的对象权限
user_tab_privs
dba_tab_privs
select * from user_tab_privs;
select * from dba_tab_privs;
--查询角色
dba_roles
select role from dba_roles;
--创建角色
create role student;
create role teacher;
create role director;
--为角色授权
grant to
grant select any table to student;
grant student to teacher;
grant insert any table to teacher;
grant teacher to director;
grant update any table to director;
grant delete any table to director;
--为用户授权
grant director to tom;
grant connect to tom;
--查询用户(角色)与角色的关系
dba_role_privs
select * from dba_role_privs;
--查询角色与权限的关系
select * from dba_tab_privs;
select * from dba_sys_privs;
--删除角色
drop role director;
--创建学生信息表
create table student
(
id number(4),
name nvarchar2(4),
gender char(2),
birthday date,
address varchar2(100)
);
--删除表的列
alter table 表名称 Drop column 列名称
--添加表的列
alter table 表名称 add column 列名称
--添加主键约束确保字段值不能为空且不能重复
primary key
alter table student
add constraint pk_student_id primary key (id);
--添加非空属性确保字段不能为空
not null
alter table student
modify name not null;
--添加检查约束确保字段只能填写指定数据
check
alter table student
add constraint ck_student_gender check (gender='男' or gender='女');
--在假设生日不能重复的前提下--添加唯一约束确保字段仅仅不能重复
unique
alter table student
add constraint uq_student_birthday unique (birthday);
--添加默认值属性确保在不填写的情况下自动填写的数据
default
alter table student
modify address default ('北京八维研修学院宿舍');
--创建课程信息表
create table course
(
id number(2),
title varchar2(20),
period number(2)
);
alter table course
add constraint pk_course_id primary key (id);
alter table course
modify title not null;
alter table course
add constraint ck_course_period check(period>=40 and period<=80);
--创建学生成绩表
create table grade
(
sid number(4),
cid number(2),
mark number(3)
);
--添加外键约束确保所用数据来源于其它表中的数据
foreign key
alter table grade
add constraint fk_grade_sid foreign key (sid) references student(id);
alter table grade
add constraint fk_grade_cid foreign key (cid) references course(id);
--组合主键
alter table grade
add constraint pk_grade_sid_cid primary key (sid,cid);
alter table grade
add constraint ck_grade_mark check(mark>=0 and mark<=100);
--查询当前用户的可用数据表
select table_name from user_tables;
--查询表所有信息
select * from emp;
select * from dept;
select * from salgrade;
--仅查询多字段信息
select empno,ename,deptno from emp;
--带时字段带表达式
select ename,12*sal from emp;
--简单的数学运算
dual
select 200+300,12*3 from dual;
--获得当前日期时间
sysdate
systimestamp
current_date
current_timestamp
select sysdate from dual;
select systimestamp from dual;
select current_date from dual;
select current_timestamp from dual;
--使用字段别名
as
select ename as "姓名",12*sal as "年薪" from emp;
select ename "姓名",12*sal "年薪" from emp;
--查询员工姓名和补贴
select ename,comm from emp;
--查询员工姓名和年收入
Select decode(comm,500,’A’,300,’B’,’C’) from emp ;
nvl(comm,0)
select ename "姓名",12*(sal+nvl(comm,0)) "年收入" from emp;
--字符串连接
||
select 'abc'||'def'||'gh' "con" from dual;
select 'Mr.'||ename from emp;
--查询时消除冗余
distinct
select distinct deptno from emp;
--同时查询部门编号与岗位
select distinct deptno,job from emp;
--带条件的查询
where
ename='CLARK
sal>1500
sal>1500;
select * from emp where deptno=10;
--查询部门编号不是10的员工信息
select * from emp where deptno!=10;
select * from emp where deptno<>10;
--查询“CLARK”员工的信息
select * from emp where ename='CLARK';
--查询薪水多于1500元的员工信息
select * from emp where sal>1500;
--查询排在“FORD”之后的员工信息
ename>'FORD'
select * from emp where ename>'FORD';
--查询薪水在800至1500元之间的员工信息
where sal>=800 and sal<=1500
where sal between and
select * from emp where sal>=800 and sal<=1500;
select * from emp where sal between 800 and 1500;
--查询没有津贴的员工信息
is null
is not null
select * from emp where comm is null;
--查询有津贴的员工信息
select * from emp where comm is not null;
--查询薪资指定的员工信息
where sal in (800,1100,1500)
select * from emp where sal=800 or sal=1100 or sal=1500;
select * from emp where sal in (800,1100,1500);
--查询在1981年12月3日之后入职的员工信息
select * from emp where hiredate > '3-12月-1981';
--查询在10号部门工作并且薪水多于1000元的员工信息
select * from emp where deptno=10 and sal>1000;
--查询在10号部门工作或者薪水多于1000元的员工信息
select * from emp where deptno=10 or sal>1000;
--查询在10号部门工作但薪水只有1300或5000元的员工信息
select * from emp where deptno=10 and (sal=1300 or sal=5000);
select * from emp where deptno=10 and sal in(1300,5000);
-- 模糊查询
like ‘%A%’
__A%
--查询姓名中包含”A”字符串的员工信息(模糊)
select * from emp where ename like '%A%';
select * from emp where ename like '%A';
select * from emp where ename like 'A%';
select * from emp where ename like 'A____';
select * from emp where ename like '__A%';
select * from emp where ename like '%A__';
--按员工编号升序排列显示员工信息
order by asc
select * from emp order by empno asc;
--按员工编号降序排列显示员工信息
select * from emp order by empno desc;
--按部门编号升序且姓名降序的方式排列员工信息
desc
select * from emp order by deptno asc,ename desc;
--按员工编号升序排列不在10号部门工作的员工信息
select *
from emp
where deptno!=10
order by empno desc;
--查询姓名第二个字母不是”A”且薪水大于800元的员工信息,按年薪降序排列
select emp.*,12*sal a
from emp
where ename not like '_A%' and sal>800
order by a desc;
--求每个部门的平均薪水
avg(sal)
group by
select avg(sal) from emp;
select deptno from emp group by deptno;
select deptno,avg(sal) from emp group by deptno;
--求各个部门的最高薪水
max(sal)
select max(sal) from emp;
select deptno,max(sal) from emp group by deptno;
select min(sal) from emp;
select sum(sal) from emp;
--求每个部门每个岗位的最高薪水
select * from emp;
select job,deptno from emp group by job,deptno;
select job,deptno,max(sal) from emp group by job,deptno;
--求平均薪水大于2000的部门编号
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
--将员工薪水大于1200且部门平均薪水大于2000的部门编号列出来,
--按部门平均薪水降序排列
select deptno,avg(sal) av
from emp
where sal>1200
group by deptno
having avg(sal)>2000
order by av desc;
--求最高薪水的员工信息
select max(sal) from emp;
select * from emp where sal=5000;
select * from emp where sal=(select max(sal) from emp);
--求多于平均薪水的员工信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
--交叉连接
cross join
select * from emp;
select * from dept;
select * from emp,dept;
select * from emp cross join dept;
--内连接
inner join on
--查询员工姓名及所在部门名称
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename,dname
from emp inner join dept on emp.deptno=dept.deptno;
--求每个员工及他的经理姓名
select * from emp;
select employee.ename as "员工",manager.ename as "经理"
from emp employee cross join emp manager
where employee.mgr=manager.empno;
select employee.ename as "员工",manager.ename as "经理"
from emp employee inner join emp manager
on employee.mgr=manager.empno;
--查询员工姓名及其薪水等级
select * from emp;
select * from salgrade;
select * from emp cross join salgrade;
select ename as "员工姓名",grade as "薪水等级"
from emp inner join salgrade
on sal between losal and hisal;
--三个表的嵌套查询
--输出非办事员的员工姓名,所在部门名称及薪水等级
select * from emp;
select * from dept;
select * from salgrade;
select * from emp,dept,salgrade;
select *
from emp cross join dept
cross join salgrade;
select ename,dname,grade
from emp cross join dept
cross join salgrade
where emp.deptno=dept.deptno
and emp.sal between losal and hisal
and job!='CLERK';
select ename,dname,grade
from emp inner join dept on emp.deptno=dept.deptno
inner join salgrade on emp.sal between losal and hisal
where job!='CLERK';
--输出第二个字母不是”A”员工姓名,所在部门名称及薪水等级
select ename,dname,grade
from emp inner join dept on emp.deptno=dept.deptno
inner join salgrade on emp.sal between losal and hisal
where ename not like '_A%';
--求各个部门薪水最高的员工信息
select deptno,max(sal) from emp group by deptno;
select * from emp;
select ename,deptno,sal
from emp inner join (select deptno d,max(sal) m from emp group by deptno) e
on emp.sal=e.m and emp.deptno=e.d
order by deptno asc;
--求取所有部门的员工姓名
select * from dept;
select * from emp;
select * from dept cross join emp;
select dept.deptno,dname,ename from dept inner join emp on dept.deptno=emp.deptno;
select dept.deptno,dname,ename from dept left join emp on dept.deptno=emp.deptno;
伪列
--输出记录的序号
rownum
select rownum,emp.* from emp;
--输出前五条记录
select rownum,emp.* from emp where rownum<=5;
--取出第十条记录之后的数据
select * from (
select rownum r,emp.* from emp)
where r>10;
--按薪水由高至低排列,找出第6-10名的员工信息
select * from
(select rownum r,e.* from
(select *
from emp
order by sal desc) e) e2
where r>=6 and r<=10;
--保存数据
insert into student(id,name,gender,birthday,address)
values(1000,'李四','男','12-11月-1990',default);
insert into student(id,name,gender,birthday,address)
values(1001,'王五','女',sysdate,'北京市东城区小燕胡同');
--同时插入多条记录
insert into student(id,name,gender,birthday,address)
select 1002,'赵六','女','18-2月-1993','北京市东城区小燕胡同' from dual。
union all
select 1003,'张三','男','20-8月-1989','北京市海淀区小燕胡同' from dual;
select * from student;
--更新数据
update set
update student set birthday='26-6月-1992' where id=1001;
update student
set birthday='18-10月-1991',address='北京市朝阳区光明里小区'
where id=1003;
rollback;
--删除数据
delete from student where id=1003;
commit;
--数据控制语言
grant select on student to tom;
grant insert on student to tom;
revoke select on student from tom;
--各种函数
--绝对值,向上取整,向下取整,取若干位小数不四舍五入,取若干位小数四舍五入,取特定范围内随机数,
--字符串链接,变首字母为大写,全部大写,全部小写,从字符串第若干位查询第若干次出现的字符串的索引值
--字符串长度,为字符串增长并补特定符号(左右),使用特定字符截取字符,从特定字符截取特定长字符,
--替换字符,字符反转,
abs
ceil
floor
trunc
round
dbms_random.value
select abs(-25) from dual;
select ceil(-35.68) from dual;
select floor(-35.68) from dual;
select trunc(12.345678,2) from dual;
select round(12.345678,2) from dual;
select dbms_random.value(10,20) from dual;--包括10,不包括20
concat
initcap
upper
lower
select concat('app','le') word from dual;
select concat('Mr.',ename) ename from emp;
select initcap(ename) from emp;
select upper(lower(ename)) from emp;
instr
length
select instr('aabbaaccaaddaaeeaaff','aa',5,3) from dual;
select ename,length(ename) from emp;
rpad
lpad
rtrim
ltrim
substr
replace
reverse
select rpad('abc',8,'*') from dual;
select lpad('abc',8,'*') from dual;
select rtrim('aabbccbbaa','ab') from dual;
select ltrim('aabbccbbaa','ab') from dual;
select substr('abcdefg',3,4) from dual;
select replace('aabbaacc','aa','X') from dual;
select reverse('12345') from dual;
--数字变字符,数字变人民币字符,日期变字符
to_char
L9999999.9999999
select 123,to_char(123) from dual;
select to_char(1000.12345,'L9999999.9999999') from dual;
select to_char(1000.12345,'L0000000.0000000') from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
--字符变数字
to_number
select to_number('1200') from dual;
select '1200'+'1300' from dual;
--字符变日期,数字变日期
to_date
select to_date('2002-12-20','yyyy-mm-dd') from dual;
select to_date(20021220,'yyyymmdd') from dual;
--加月份,减月份,加天,加小时,加分钟,算月时间差
add_months
sysdate+1
select sysdate,add_months(sysdate,2) from dual;
select sysdate,add_months(sysdate,-12) from dual;
select sysdate,sysdate+1 from dual;
select sysdate,sysdate+1/24/60 from dual;
select months_between(to_date('2014-2-5','yyyy-mm-dd'),
to_date('2013-12-11','yyyy-mm-dd')) from dual;
--统计个数
count
select ename,comm from emp;
select count(comm) from emp;
select to_char(round(avg(sal),2),'L9999.99') from emp;
--行最大值,行最小值
greatest
least
select greatest(1,2,3,4,5) from dual;
select least(1,2,3,4,5) from dual;
select user from dual;
分析函数 decode
Select decode(floor(mark/10),10,’优秀’,9,’ 优秀’,8,’良好’,7,’中等’,6,’及格’,’不及格’);
嵌套查询
Select * from emp where sal>(select avg(sal) from emp);
--行级锁
此四种操作自动加行级锁:
insert
delete
update
select * from emp where for update;
--表级锁
in share mode
in exclusive mode
lock table emp in share mode 共享锁;
lock table emp in exclusive mode 排它锁;
--序列
sequence
start with
.nextval
.currval
create sequence s0 start with 1000;
select s0.nextval from dual;
create sequence sq_t03 start with 100 increment by 10;
select sq|_to1.currval from dual;
--视图
create view v0
as
select * from(
select rownum r,e.* from(
select * from emp order by sal desc)e)
where r>=6 and r<=10;
select * from v0;
--复制表
create table emp0
as
select * from emp;
select * from emp0 where empno=7900;
select * from emp0 where ename='SMITH';
select * from emp0 where empno>7000 and ename<'ZOO'
--索引
create index on
create index i0 on emp0 (empno);
create unique index i1 on emp0(ename);
create index i2 on emp0 (empno,ename);
重建索引
Alter index <索引名> rebuild;
删除索引
Drop index <索引名>;
组合索引
Select * from emp0 where empno>7000 and ename<’ral’;
Create index ins2 on emp0 (empno,ename);