Oracle的基础用法详细实例（一）

Oracle的基础用法详细实例（一）

—删除表空间
drop tablespace ts1706;

—创建表空间
create tablespace ts1706
—将表空间放置位置
datafile ‘F:\ts1706.dmp’
—表空间大小
size 5m
—表空间是否可扩展
reuse autoextend on/off;

—-删除用户
drop user rose;
—创建新用户
create user rose
—用户密码
identified by 111111
—用户默认表空间
default tablespace ts1706
—用户临时表空间储存在temp
temporary tablespace temp;

—授权
—系统权限
—connect 连接oracle的权力
—resource 操作系统资源的权力
resourse 包括（create table,create,view,…..）

—赋予权力
grant connect,resource to rose;

—管理员回收系统资源权限
revoke resource from rose;

—用户管理的命令
—添加用户
alter user rose identified by 222222;

—锁定/解除当前用户
alter user rose account unlock/lock;

—删除用户
drop user rose cascade;

—rose登录后，可以执行操作（rose必须要赋予相应权力才能执行相应操作）
create table demo
(
dno int primary key,
dname varchar2(20) not null
)

—对创建好的表进行列的添加
alter table demo add loc varchar2(30) not null;

—实现数据的完整性
—约束
—域完整性：主要通过在表中设置默认约束与检查约束来实现
—实体完整性：创建表的同时创建PRIMARYKEY或UNIQUE约束以保证实体完整性
—参照完整性：参照完整性通过建立表之间的主外键关系实现
create table person
(
pid varchar2(18) primary key,
pname varchar2(30) unique not null,
—实体完整性
sex varchar2(2) check(sex=’男’ or sex=’女’),
—域完整性
age int check (age>=1 and age<=160),
address varchar2(30) default ‘地址不详’,
dno int,
foreign key (dno) references dept(deptno)
—参照完整性
)

—添加语句
insert into person(pid,pname,sex,age,dno) values(‘121’,’jack’,’男’,56,10);
—-sql复习

—去掉重复
select distinct job from emp;

—-子查询(用一个查询语句的结果作为另一个查询语句的条件)
—查询和smith从事同一种工作的员工信息
select * from emp where job=(select job from emp where ename=’SMITH’)

—-查询所有经理的信息
select * from emp where empno in (select mgr from emp);
—连接查询

—内连接：返回两个表中有对应关系的数据
—查询员工的名字和其所在部门的名字
select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

—左外连接:左表中的所有数据，右表中有对应关系的记录
select ename,dname from emp left join dept on emp.deptno=dept.deptno;

—右外连接:右表中的所有数据，左表中有对应关系的记录
select ename,dname from emp right join dept on emp.deptno=dept.deptno;

—full join:完全连接
—返回两张表中所有的数据
select ename,dname from emp full join dept on emp.deptno=dept.deptno;

—-cross join:交叉连接(返回两张表的排列组合)
select ename,dname from emp cross join dept

—排序(降序desc,升序asc)默认升序
select ename,sal from emp order by sal desc

—分组
—-集合查询
select * from emp where deptno=20
minus
select * from emp where sal>=3000

—-oracle的系统函数
—日期函数
ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
ROUND
NEXT_DAY
TRUNC
EXTRACT
select add_months(sysdate,5) from dual;
select months_between(sysdate,to_date(‘2019-4-5’,’yyyy-MM-dd’)) from dual;
select last_day(sysdate) from dual;
select round(sysdate) from dual;
select next_day(sysdate,’星期三’) from dual;

—截断
select trunc(sysdate) from dual;
select extract(year from sysdate) from dual;

—-字符函数

select initcap(‘hello word’) from dual;
select upper(‘hello’) from dual;
select lower(‘HELLO’) from dual;
select ltrim(‘—abc’,’—’) from dual;
select translate(‘ab–a—b–’,’ab’,’**’) from dual;
select replace(‘ab—a—b’,’ab’,’**’) from dual;
select instr(‘hello’,’l’) from dual;
select substr(‘good good study’,1,9) from dual;
select concat(ename,’—-‘) from emp;
select chr(119) from dual;
select ascii(‘a’) from dual;
select lpad(ename,10,’-‘) from emp;
select rpad(ename,10,’-‘) from emp;
select trim(9 from 99988888999) from dual;
select trim(‘a’ from ‘aaabbbbaaaa’) from dual;
select length(‘哈哈’) from dual;
select ename,decode(deptno,null,’暂无部门’) from emp;

—-数学函数

select ceil(12.34) from dual;
select floor(12.34) from dual;
select power(2,3) from dual;
select sqrt(16) from dual;
select sqrt(10) from dual;
select mod(4,5) from dual;
select round(12.34) from dual;
select round(12.45343434,2) from dual;
select trunc(12.56789,2) from dual;
—转换函数

select sysdate from dual;
—varchar2->date
select to_date(‘2018-6-7’,’yyyy-MM-dd’) from dual;
—date->varchar2
select to_char(sysdate,’yyyy”年”MM”月”dd hh:mi:ss’) from dual;
select to_number(‘100’)+100 from dual;
select * from emp;
—-if comm=null then comm=0
select ename,sal+nvl(comm,0) from emp;
—if deptno!=null then deptno=第二个参数
—else deptno=第三个参数
select ename,nvl2(deptno,’有部门’,’没有部门’) from emp;
update emp set comm=0 where ename=’SMITH’;
—if comm=0 then comm=null
select ename,nullif(comm,0) from emp;
select nullif(ename,’SMITH’) from emp;

—-分析函数
—查询工资最高的前三名员工信息
select * from (select ename,sal,dense_rank() over(order by sal desc) rn from emptest) ep where rn<=3;

—-创建序列化
create sequence seq_002
increment by 1
start with 1001
nomaxvalue

—序列化对象的使用
–Nextval 访问序列化的下一个值
–Currval 访问序列化的当前值
select * from kind;
insert into kind values(seq_002.nextval,’aa’);
—删除序列化
drop sequence seq_002;

—视图
—with read only
—删除视图
drop view e_d_v;
—创建视图
create view e_d_v
as
select ename,sal,emp.deptno,dname from emp inner join dept on emp.deptno=dept.deptno
with read only;

—-视图2
—with check option
—删除视图
drop view e_d_v;
create view emp_view
as
select * from emp where sal>=3000
with check option

—对视图进行操作
select * from emp_view;
update emp_view set sal=2000;

—同义词
—删除同义词
drop synonym e;
—synonym(定义表的别名,仅该用户可使用该别名)
create synonym e for emp;
select * from e;
—赋予创建同义词的权力
grant create public synonym to scott;

—public synonym(定义表的别名,所有用户可使用该别名)
create public synonym d for dept;
select * from d;

—-索引
—-标准索引
create index idx_001 on emp(ename);
select * from emp where ename=’SCOTT’;

—组合索引
create index idx_001 on emp(ename,sal);

—唯一索引
create unique index idx_001 on emp(ename);
drop index idx_001;

—反向键索引
—红黑二叉树
create index idx_001 on emp(ename) reverse;

—位图索引
create bitmap index idx_001 on emp(ename);

—事务(要么多个任务同时完成，要么多个任务同时失败)
—银行转账的事务
update emp set sal=sal-1000
where ename=’SCOTT’;
update emp set sal=sal+1000
where ename=’SMITH’;
—-红色按钮(回滚)
rollback;
—绿色按钮(提交)
commit;

—-行级锁
—for update(锁定该行，其他人同一时刻不能操作该行，除非该用户解锁)
select * from emp where sal>=3000 for update;

—-表级锁
—exclusive独占锁/排它锁
—share 共享锁
lock table emp in share/exclusive mode;

—分区管理
—-范围分区
drop table bank;
create table bank
(
bno varchar2(20) primary key,
bmoney float
)
partition by range(bmoney)(
partition p1 values less than(5000),
partition p2 values less than(15000),
partition p3 values less than(25000)
)

—-散列分区
create table bank
(
bno varchar2(20) primary key,
bmoney float
)
partition by hash(bmoney)(
partition p1,
partition p2,
partition p3
)

—-列表分区
—分区的维护
—添加分区
alter table bank add partition p4;
—删除分区
alter table bank drop partition p3;

—删除分区里数据
alter table bank truncate partition p1;

—-PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
—给变量赋值有两种方法：
—使用赋值语句 :=
—使用 SELECT INTO 语句
declare
r int;
pi constant float:=3.14;
begin
r:=&请输入圆的半径;
dbms_output.put_line(r*r*pi);
end;

—-动态输入员工的姓名，查询该员工的工资
declare
en varchar2(20);
esal emp.sal%type;
begin
en:=’&请输入员工的姓名’;
select sal into esal from
emp where ename=en;
dbms_output.put_line
(en||’的工资是’||esal);
end;

—-%type(列类型)
—%rowtype(一行数据的类型)
declare
e emp%rowtype;
begin
select * into e from emp
where ename=’SCOTT’;
dbms_output.put_line(e.empno||’,’
||e.hiredate||’,’||e.sal);
end;

—-条件语句
declare
sal float;
begin
sal:=&请输入您的工资;
if sal>=20000 then
dbms_output.put_line(‘金领’);
elsif sal>=10000 then
dbms_output.put_line(‘白领’);
elsif sal>=5000 then
dbms_output.put_line(‘蓝领’);
else
dbms_output.put_line(‘灰领’);
end if;
end;

—-switch语句的实现
declare
c char(2);
begin
c:=’&请输入您考试的等级’;
case c
when ‘A’ then
dbms_output.put_line(‘90-100’);
when ‘B’ then
dbms_output.put_line(‘80-90’);
when ‘C’ then
dbms_output.put_line(‘70-80’);
when ‘D’ then
dbms_output.put_line(‘60-70’);
else
dbms_output.put_line(‘<60’);
end case;
end;

—-循环语句
declare
i int:=1;
begin
while i<=100
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

begin
for i in reverse 1..50
loop
dbms_output.put_line(i);
end loop;
end;
—异常机制
declare
e emp%rowtype;
begin
select * into e from emp;
exception
when too_many_rows then
dbms_output.put_line(‘您的数据返回的太多了呀’);
dbms_output.put_line(‘——-‘);
end;

declare
e emp%rowtype;
begin
select * into e from emp where ename=’aa’;
exception
when no_data_found then
dbms_output.put_line(‘您没找到数据呀’);
dbms_output.put_line(‘——-‘);
end;

—自定义异常
declare
age int;
ageError exception;
begin
age:=&请输入您的年龄;
if age<0 or age>150 then
raise ageError;
end if;
dbms_output.put_line(age);
exception
when ageError then
dbms_output.put_line(‘年龄不合法’);
end;

—函数的使用
—定义一个函数，实现查询某个部门的平均
—工资
create or replace function
ageDept(dno number)
return float
is
sa float;
begin
select avg(sal) into sa from
emp where deptno=dno;
return sa;
end;

—-函数的调用
select ageDept(20) from dual;
或
declare
r float;
begin
r:=ageDept(20);
dbms_output.put_line(round(r,2));
end;

—计算两个的数的和并返回，同时
—可以将两个数交换位置返回
create or replace function
sumNum(x in out int,y in out int)
return int
is
t int;
begin
t:=x;
x:=y;
y:=t;
return x+y;
end;

—调用
declare
a int:=20;
b int:=50;
r int;
begin
r:=sumNum(a,b);
dbms_output.put_line(‘r=’||r);
dbms_output.put_line(‘a=’||a);
dbms_output.put_line(‘b=’||b);
end;

—-游标的使用
begin
delete from emp;
dbms_output.
put_line(SQL%rowcount||’行被删除了’);
end;

begin
delete from emp where ename=’aa’;
if SQL%notfound then
dbms_output.put_line(‘员工不存在’);
end if;
end;
select * from emp;

—显示游标
declare
e emp%rowtype;
cursor cur1 is
select * from emp;
begin
open cur1;
–游标遍历
loop
—提取一行数据
fetch cur1 into e;
if cur1%notfound then
exit;
end if;
dbms_output.put_line(e.ename);
end loop;
close cur1;
end;

declare
e emp%rowtype;
cursor cur1 is
select * from emp;
begin
—游标的简化遍历
for e in cur1 loop
dbms_output.put_line(e.ename);
end loop;
end;

select * from demo;