–查询当前数据库名称 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);