Oracle基础

Oracle基础

Oracle体系架构主要有两大部分组成：数据库实例(Instance)和数据库文件(database)(硬盘文件data files，control files，redo log files，数据文件是数据库的物理存储单位)

数据库实例:数据库服务器的内存(SGA)及相关处理程序
SGA(System Global Area)：由所有服务进程和后台进程共享；
PGA(Program Global Area)：由每个服务进程、后台进程专有；每个进程都有一个PGA。

–SGA包含三个部分：
1、数据缓冲区，存放sql运行结果;
2、日志缓冲区，存放数据库运行生成的日志,提升了数据增删改的速度，减少磁盘的读写而加快速度;
3、共享池，共享SQL区(存放用户SQL命令)和数据字典缓冲区

–后台进程
1.系统监控进程
2.进程监控
3.数据库写进程
4.日志写进程
5.检验点进程

创建表空间（逻辑结构）

create tablespace mydemo
datafile '/opt/oracledb/mydemo.dbf' size 500m
autoextend on next 50m maxsize 1000m;

创建用户

create user lc identified by ok default tablespace mydemo;

查询哪些用户在连接

select username,sid,serial# from v$session;

断开连接进程
alter system kill session ‘sid,serial#’;

Oracle中存在三个角色：connect角色/resource角色/dba角色

CONNECT:
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图

RESOURCE 角色–是授予开发人员的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型

DBA 角色
–拥有全部特权，是系统最高权限，只有 DBA
才可以创建数据库结构，并且系统权限也需要DBA 授出，且DBA
用户可以操作全体用户的任意基表，包括删除

授权用户连接和使用资源权限

grant connect,resource to lc

锁住一个用户

alter user lc account lock; 锁
alter user lc account unlock; 解锁

Oracle数据类型

varchar2(n) 字符长度<=4000字节
number(n)表示一个整数 number(m,n):表示一个小数
date 日期类型(插入数据时需to_date('1998-8-7','yyyy-mm-dd'))
clob 表示大文本数据类型，可存4G
blob 表示二进制数据，可存 4G,适合存储视频\文档等数据

函数

日期函数
sysdate			 当前系统时间(27-APR-21)
months_between() 获得两个时间段中的月数
last_day()某月最后天
add_months()  =>add_months(sysdate,-10*12)10年前

转换函数
TO_DATE:日期转换函数
select months_between(sysdate,to_date('2021-01-7','yyyy-mm-dd'))from dual;

TO_CHAR:字符串转换函数,拆年月日分时秒需要使用通配符
select to_char(sysdate,'yyyy') from dual;

To_number
select to_number('13')from dual;

trunc(date,[fmt]) 函数可用于截取日期时间

通用函数
nvl 空值处理
decode if-else判断函数：decode(数据,'aa','数据为aa的时候返回的值','bb','数据为bb的时候返回的值',...,'else之前条件都不符合返回的值')
concat<=>||字符串拼接
NULLIF(exp1,exp2) 如果表达式exp1与exp2的值相等则返回null，否则返回exp1的值

DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数

创建表

create table userinfos(
       userid int primary key not null,
       username varchar2(20) not null,
       birthyear date not null,
       userhigh number(5,2) not null
);

插入数据

insert into userinfos values(1,'zs',to_date('1998-8-7','yyyy-mm-dd'),180.5);

批量插入数据

insert into tableName
	select ...from xxx
	union
	select ...from xxx

导出/导入

exp lc/ok@orcl file=/opt/datas 导出
imp lc/ok@orcl file=/opt/datas.dmp 导入

Rownum与分页查询（Rownum:表示行号，这个列是一个伪列）

select * from (
select rownum no,e.* from (
select * from emp order by sal desc) e where rownum<=5 ) where no>=3;

多行子查询

select * from emp where sal > any(select avg(sal) from emp group by deptno);
select * from emp where sal > all(select avg(sal) from emp group by deptno);

分组函数嵌套

select max(avg(sal))from emp group by deptno;

SQL优化(充份利用索引,减少表扫描的I/O次数，尽量避免全表扫描和其他额外开销)

1.尽量少用 IN 操作符
2.尽量用NOT EXIST或者外连接替代NOT IN操作符
3.尽量不用<>或者!=操作符
4.在设计表时，把索引列设置为 NOT NULL
5.尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
6.Where 子句中避免在索引列上使用计算
7.用“>=”替代“>”
8.使用表的别名，并将之作为每列的前缀
9.用UNION ALL代替UNION

级联删除
在建立外键的时候必须指定级联删除（ON DELETE CASCADE）

创建序列

Create sequence myseq(序列名)
Start with 1 
Increment by 1 
Order
cache 20
Nocycle;

Select myseq.nextval from dual;

同义词

sys->grant create public synonym to lc;
lc->create public synonym users for lc.users;
lc->grant select on lc.users to lc1;

循环

打印三角形
--Loop 循环
declare
  m number;
  n number;
begin
  m:=1;
  loop
     n:=1;
    loop
      DBMS_OUTPUT.put('*');
      exit when n>=m;
      n:=n+1;
    end loop;
    DBMS_OUTPUT.new_line;
    exit when m>=5;
    m:=m+1;
  end loop;
end;

--while 循环
while(判断循环的条件) loop
循环的语句 ;
循环条件的改变 ;
End loop ;

--for 循环
FOR 变量名称 in 变量的初始值..结束值 LOOP
循环语句 ;
END LOOP ;

declare
  m number;
  n number;
begin
  for i in 1..5 loop
    for j in 1..i loop
     DBMS_OUTPUT.put('*'); 
    end loop;
    DBMS_OUTPUT.new_line;  
  end loop;
end;

游标（一种 PL/SQL 控制结构；可以对 SQL 语句的处理进行显示控制，便于对表的行数据逐条进行处理）

--静态游标
declare
  cursor cur_users is select *from users;  --声明游标
  userinfo users%rowtype;
begin
  open cur_users; --打开游标
  fetch cur_users into userinfo; --取出结果，此时的结果取出的是一行数据
  DBMS_OUTPUT.put_line(userinfo.userid);  --打印
  close cur_users; --关闭游标
end;

–动态游标，无return(弱)

declare
  userid lc.users.userid%Type;
  username lc.users.username%type;
  type cur_user is ref cursor;
  cur_info cur_user;
begin
  open cur_info for 'select userid,username from users';
  fetch cur_info into userid,username;
  DBMS_OUTPUT.put_line(userid||','||username); 
  close cur_info;
end;

–动态游标，return(强)每次接收一行

declare
  myrow lc.users%rowtype;
  type cur_user is ref cursor return lc.users%rowtype;
  cur_info cur_user;
begin
  open cur_info for select * from users;
  fetch cur_info into myrow;
  DBMS_OUTPUT.put_line(myrow.userid||','||myrow.username); 
  close cur_info;
end;

函数

--创建普通函数
create or replace function func_calhigh(y varchar)return number
as
avg_high number;
begin
  select avg(userhigh) into avg_high from users where to_char(birthyear,'yyyy')=y;
  return avg_high;
end;

select func_calhigh('1992') from dual;

--函数返回游标
create or replace function func_calhigh2(y varchar) return sys_refcursor
as
hc sys_refcursor;
begin
  open hc for select avg(userhigh)  from users where to_char(birthyear,'yyyy')=y;
  return hc;
end;

declare 
  lc sys_refcursor;
  ah number;
begin
  lc:=func_calhigh2('1992');
  fetch lc into ah;
  DBMS_OUTPUT.put_line(ah);
  close lc;
end;

存储过程

create or replace procedure func_calhigh3(y varchar,hg out number) 
as
begin
  select avg(userhigh) into hg from users where to_char(birthyear,'yyyy')=y;
end;

declare 
  ah number;
begin
  func_calhigh3('1992',ah);
  DBMS_OUTPUT.put_line(ah);
end;

触发器（银行开户/销户）

CREATE TABLE bank_user(
    userid int primary key not null,
    username varchar2(20) not null
);
CREATE TABLE bank_trans(
    transid int primary key not null,
    cq int not null,
    userid int,
    money number(10,2)not null
);
--主外键关联
alter table bank_trans add constraint FK_user_trans foreign key(userid)
references bank_user(userid);
--创建序列
create sequence seq_user;
create sequence seq_trans;

after触发器（开户）

Create or replace trigger tr_openaccount
after insert on bank_user
--行级触发器（执行DML操作时，每作用一行就触发一次触发器）
for each row
begin
  insert into bank_trans values(seq_trans.nextval,1,:new.userid,100);
end;

insert into bank_user values(seq_user.nextval,'lc');

sequence批量插入

insert into bank_user select seq_user.nextval,name from(
       select 'lc1' name from dual
       union
       select 'lc2' name from dual
)

before触发器（销户）

Create or replace trigger tr_desctoryaccount
before delete on bank_user
for each row
begin
  delete from bank_trans where userid=:old.userid;
end;

delete from bank_user where username='lc2';