Oracle学习

自己一些关于Oracle练习整理

一、Oracle体系结构
1、概念关系
数据库与实例：Oracle只有一个数据库，但可以有多个实例
Oracle数据库与实例一对多，一般情况下只有一个实例，我们平常说的Oracle数据库名，其实是Oracle的实例名
实例与用户一对多
实例与表空间一对多
表空间与用户一对多
2、Oracle结构
物理结构：数据文件(xxx.dbf、xxx.ora)
逻辑结构：数据库 表空间 段 区 Oracle数据块

二、Oracle实用命令及语句
在物理机的dos窗口输入：sqlplus system/password@192.168.25.128:1521/orcl
查询所有表空间：select tablespace_name from dba_tablespaces;
查询所有角色：select * from dba_sys_privs;
查看所有用户：select * from all_users;
查看当前用户：show user
通过scott登录可以做测试：sqlplus scott/tiger@192.168.25.128:1521/orcl
账号锁定报错：ERROR:ORA-28000: the account is locked
解决：重新用system登录，执行alter user scott account unlock;给scott用户解锁
密码过期报错：ERROR:ORA-28001: the password has expired
解决：重新输入密码tiger即可
查看当前用户下的所有表：select table_name from user_tables;
查看表结构：desc emp
清屏：host cls
设置行宽：set linesize 150
显示行宽：show linesize
设置列宽：col ename for a8（8个字符的宽度）
设置每页多少条数据：set pagesize 20
显示每页多少条数据：show pagesize
将命令行的语句写入到指定的目下的指定的文件中：
指定写到哪个目录的哪个文件上：spool d:\1.text
写入完毕：spool off
编辑之前输入过的命令：
输入ed，然后回车
该命令主要用在当前一个sql语句写错时，又不想重新写，这个时候可以用ed命令调出记事本
对先前的命令进行编辑（如果该命令用在Linux系统中，调出来的是vi编辑器）
执行之前缓存过的命令：/

三、创建表空间+创建用户+系统管理员授权
1、创建表空间tablespace
create tablespace waterbos
datafile 'c:\waterbos.dbf'
size 100m
autoextend on
next 10m;

2、创建用户user
    create user xuming
    identified by itcast
    default tablespace waterbos;
    
3、给创建的新用户授权grant
    ORA-01045: user lacks CREATE SESSION privilege; logon denied
    解决办法：系统管理员给新用户授权：
    grant create session,resource to 用户名; 等同于grant connect,resource to 用户名;
    grant dba to 用户名;
    
    张三        超级管理员      对A资源的增加
    李四        普通管理员      对A资源的删除
    王五        项目经理        对A资源的修改
    赵六        产品经理        对A资源的查询
    钱七      技术顾问        对B资源的查询

四、创建表+维护表结构
DDL：Data Definition Language 数据定义语言 如：create、alter、drop、truncate
DML：Data Manipulation Language 数据操纵语言 如：select、update、delete、insert
DCL:Data Control Language 数据控制语言 如：grant、revoke
1、oracle常用数据类型
char,varchar2,long,number,date,clob,blob

2、创建表+约束
    1）基本创建表语句
        create table myemp3(
            id number(10),
            name varchar2(10)  constraint myemp3_name_nn not null,--非空约束
            gender varchar2(4) default '男',--默认值
            deptno NUMBER(2),
            email varchar2(20),
            constraint myemp3_id_pk primary key(id), --主键约束
            constraint myemp3_gender_ck check(gender in('男','女')), --检查约束
            constraint myemp3_email_uk unique(email), --唯一约束
            constraint myemp3_deptno_fk foreign key(deptno) references dept(deptno) on delete set null --外键约束
            -- 没有这种写法constraint myemp3_name_nn not null
        );
        
    2）使用子查询创建表
        create table emp2 as select * from emp where 1=2;
        由于1=2为假，该语句只拷贝了结构，没有拷贝数据
           
3、修改表
    1）修改表中列  
        oracle: alter table myemp3 modify ename vachar2(20); 不能带column
        mysql : alter table myemp3 modify (column) ename varchar(20);
        
    2）增加表中列  
        oracle: alter table myemp3 add gender varchar2(4);
        mysql : alter table myemp3 add (column) gender varchar(4);
        
    3）删除表中列  
        oracle: alter table myemp3 drop column gender; 必须带column
        mysql : alter table myemp3 drop (column) gender;
        
    4）重新命名表中列名 
        oracle: alter table myemp3 rename column ename to myname; 必须带column
        mysql : alter table myemp3 change (column) ename myname varchar(20);
        
    5）重新命名表名 
        oracle: rename myemp3 to myemp4;
        mysql : rename table mysqltname3 to mysqltname4; 必须带table
        
4、删除表
    1）彻底删除表
        drop table myemp3 purge; 不加purge是暂时放到回收站中了
        
    2）drop之后的数据放到了recyclebin回收站中
        查看回收站：show recyclebin;
        彻底清除回收站：purge recyclebin;
        彻底删除表：drop table myemp3 purge;
        查看回收站表数据： select * from "BIN$cd/KYgV5RY6/RxGTjn2Skg==$0";
        
    3）闪回
        flashback table myemp3 to before drop;

五、对表数据操作（insert,update,delete）
1）向表中插入数据(insert into values)
insert into t_owners values(2,'张三',1,'3-3','7895',sysdate,1);
insert into t_owners2 select * from t_owners;
insert into 表名(id,name) values(xx,yy),(xx1,yy1) （mysql中可以这样写,oracle不能这样写）
向多张表中插入数据：insert all into 表名(id,name) values(xx1,yy1) into 表名(id,name) values(xx2,yy2);

2）修改表中数据(update set)
    update 表名 set dateddd=dateadd-3,col1=col1+1;
    
3）删除表中数据
    使用delete删除数据   delete from myemp where empno=7369;
    使用truncate 删除整张表数据   truncate table myemp;功能上与delete from myemp; 相同
    delete和truncate 删除数据的区别：
        (1)delete可以回滚，truncate不可以
        (2)truncate比delete效率要高
    实际企业开发中删除数据库中数据注意点：
        (1)先把要删除的数据备份
        (2)确认用于删除的sql语句无误
        (3)尽量选择能够回滚数据的方式delete
        (4)drop > truncate > delete

六、导入和导出
1）整库导入导出
exp system/itcast full=y
exp system/itcast full=y file=abc.dmp
imp system/itcast full=y
imp system/itcast full=y file=abc.dmp

2）按用户导入与导出
    exp system/itcast owner=xuming file=abc.dmp
    imp system/itcast file=abc.dmp fromuser=xuming
    
3）按表导入导出
    exp xuming/itcast file=a.dmp tables=t_owners;
    imp xuming/itcast file=a.dmp tables=t_owners;
    
4）mysql导入导出
    第一种导入数据：
    mysql -uroot -proot
    mysql>create database crm;
    mysql>use crm;
    mysql>source d:/crm.sql
    第二种导入数据：
    先创建好数据库crm，执行
    mysql -uroot -proot crm < d:\crm.sql

    mysql导出数据
    mysqldump -uroot -proot crm > d:\crm1.sql

七、总结oracle与mysql在项目中使用区别
1、导入jar包不同
oracle：ojdbc14.jar 版本可能会变
mysql：mysql-connector-java-5.1.7-bin.jar 版本可能会变
注意：不同项目，jar包版本会有区别
2、jdbc获取连接写法
oracle：Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.25.128:1521:orcl", "xuming","itcast");
mysql：Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "root");
3、配置文件
# oracle jdbc properties
jdbc.url = jdbc:oracle:thin:@localhost:1521:xe
jdbc.driver= oracle.jdbc.driver.OracleDriver
jdbc.user = bos
jdbc.password = bos

    #mysql jdbc properties
    jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/bos?characterEncoding=utf-8
    jdbc.user=root
    jdbc.password=root

======================================================================================================================================================================================================

一、单表查询
完整语句：select from where group by having order by
1、简单条件查询
=,!=,>,>=,<,<=
like
and 和or 逻辑运算，and的优先级大于or
between and:小值在前，大值在后；左右两边都包括边界
is null/is not null：在mysql和oracle中没有=null和!=null
not like,not between and,not in
2、去重：select distinct deptno,mgr保证deptno和mgr两个列组成的值的唯一性
3、排序
order by sal,deptno desc;
desc只能对deptno进行降序排列
底层实现的顺序：首先按照sal升序排列，然后如果有相同的sal，再按照deptno进行降序排列。
4、伪列rowid和rownum
rowid:每一行的物理地址 rownum:每一行排序的序号
select rowid,rownum,empno,ename from emp;
ROWID ROWNUM EMPNO ENAME
------------------ ---------- ---------- ------
AAAMfPAAEAAAAAgAAA 1 7369 SMITH
AAAMfPAAEAAAAAgAAB 2 7499 ALLEN
AAAMfPAAEAAAAAgAAC 3 7521 WARD
AAAMfPAAEAAAAAgAAD 4 7566 JONES
AAAMfPAAEAAAAAgAAE 5 7654 MARTIN
AAAMfPAAEAAAAAgAAF 6 7698 BLAKE
AAAMfPAAEAAAAAgAAG 7 7782 CLARK
AAAMfPAAEAAAAAgAAH 8 7788 SCOTT
AAAMfPAAEAAAAAgAAI 9 7839 KING
AAAMfPAAEAAAAAgAAJ 10 7844 TURNER
AAAMfPAAEAAAAAgAAK 11 7876 ADAMS

5、聚合函数：min(),max(),avg(),sum(),count(),其中count(对于null值行直接滤过)
    group by 分组
    select deptno,max(sal) from emp group by deptno;//这里的deptno为非聚合函数的列，所有需要参与到分组中去
    select deptno,job,max(sal) from emp group by deptno,job;//多列分组，首先按照deptno分组，相同的组再按照job分组
    select empno,deptno,sum(sal) from emp group by empno,deptno;
    聚合函数group by使用having来过滤分组后的结果，
    普通的列使用where过滤
    
6、别名：select ename as "员工姓名",sal 工资 from emp;
    as可以加，也可以省略，如果别名包含了特殊的关键词必须加上""；否则可以不用加""
    oracle中字符串和日期都是单引号，只有别名才是双引号。

二、多表查询
只要两个表能够建立关联关系，两个表都能够联合查询
1、内连接：在笛卡尔集上选择了满足on条件的记录行，连接的是两个相同的列的值
（笛卡尔集：记录条数是多个表记录乘积，列数是多个表的列之和）
显式内连接：select * from emp e inner join dept d on e.deptno=d.deptno;
隐式内连接：select * from emp e , dept d where e.deptno=d.deptno;

2、外连接：左外连接left join on/right join on  
    遵循的SQL99语法    select * from emp left join dept on emp.deptno=dept.deptno;
    oracle中(+)        select * from emp where emp.deptno=dept.deptno(+);
    
select d.deptno 部门编号,d.dname 部门名称,count(e.empno) 部门总人数 from emp e, dept d  where e.deptno(+)=d.deptno 
group by d.deptno,d.dname order by d.deptno;

三、子查询
1、子查询写法
where后的子查询：先查子查询，子查询的结果作为主查询的过滤条件
select ename from emp e where e.deptno in
(select dept.deptno from dept where deptno>10);
from后的子查询：先查子查询，子查询的结果，可以看成一张表，被主查询查
select t.部门总人数 from (select d.deptno 部门编号,d.dname 部门名称,
count(e.empno) 部门总人数 from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname order by d.deptno) t;
select后的子查询：先查主查询，主查询的结果作为子查询的参数，最后再查主查询
select ename 员工编号,
(select dname from dept where dept.deptno=emp.deptno) 部门名称 from emp;
注意：select后面的子查询，一定是单行子查询（只返回一条结果记录）

2、子查询运算符
    单行子查询使用(=,<>,>,>=,<,<=)，多行子查询使用单行运算符和(in(),any(),all())

四、分页查询
1、rownum
一张表不经过任何操作默认带有rownum行号,经过排序操作之后，该行号也随着排序了，但不是从1-2-3排序的
为了重新按照1-2-3排序，我们把rownum当成列来使用而不是行号
2、可以提炼成公式：
页码pageNo=1 每页条数pageSize=5
select * from
(select rownum r,t.* from
(select * from emp order by sal desc) t
where rownum <=pageNopageSize)
where r>(pageNo-1)pageSize;

    select * from emp order by sal desc limit 0,5;
    select * from emp order by sal desc limit (pageNo-1)*pageSize,pageSize;

五、函数：任何一个函数都有返回值 （只作了解，把握可以处理哪几种数据，以及处理数据功能）
参考"oracle函数大全(分类显示).chm"
字符函数
length(),concat()和||,substr()

数值函数
round() trunc()
select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;
select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五 from dual;

日期函数
sysdate  systimestamp
next_day(,'星期一')
last_day()
trunc()
months_between()
add_months()

转换函数
to_char() 可以转换成日期或者指定格式
to_number() 字符串转换成数字
to_date() 字符串转换日期

其他函数
关于null的函数：
nvl(a,b)    a为null，则返回b
nvl2(a,b,c) a为null,则返回c,否则返回b
在mysql中nullif(a,b) a=b，则返回null,否则返回a
在mysql中使用ifnull(a,b) 如果a为null ,则返回b

条件语句函数（重要）
case when then else end 
decode(ename,'',,,,)

分析排名函数()
rank() over(order by xxx )       相同的值排名相同，排名跳跃
DENSE_RANK() over(order by xxx)  相同的值排名相同，排名连续
ROW_NUMBER() over(order by xxx)  连续的排名，无论值是否相等

六、集合操作
union 去重,union all不去重
集合参与运算(并集，交集，差集)，笔记中图案的黄色部分为查询结果
差集：a minus b a-（a和b的交集）
集合操作必须满足如下原则：
1、两个集合的列数要一致
2、两个集合中的列要顺序相同，对应类型相同
order by放在最后；
select ename,sal from emp
union all
select sal,ename from emp; 会报错
======================================================================================================================================================================================================

一、视图
1、普通视图（复杂查询结果放到一张虚拟表中,对视图的操作其实是构成视图基表操作）
创建视图：create or replace view myview_view as select * from myemp3;
删除视图：drop view myview;
理解：视图一般不推荐做插入、修改操作，推荐做查询，
因为如果遇到一些特殊语句，insert,update 操作就不行，
企业开发中用的最多的是with read only视图

2、物化视图（能够说出来，物化视图是怎么一个原理）
    创建视图时是否生成数据：BUILD IMMEDIATE（默认，创建即生成）,BUILD DEFERRED
    刷新方法有三种： FAST（增量刷新）,COMPLETE（全量刷新） ,FORCE（默认强制刷新）
    刷新的模式有两种：ON DEMAND（默认，手动刷新） 和 ON COMMIT（自动刷新）
    理解：物化视图存储基于基表的数据，也可以称为快照，可以理解成一种特殊的表。
     
    删除物化视图：drop materialized view myemp3_materialized;
    
3、  普通视图与物化视图的异同
    1）相同：都指向一段sql语句
    2）不同：普通视图相当于虚拟表；物化视图会真正生成一张特殊的表
    3）查询物化视图和查询表的效率一样
    
4、增量刷新的物化视图
    1）增量刷新跟全量刷新的区别？提示：下载app
    2）创建物化视图日志，记录基表数据的变化：insert、update、delete
    3）创建物化视图的sql语句里面（as后面）一定要有rowid，
        物化视图日志表的rowid和物化视图表的rowid做比较

二、序列（主键自增使用）
创建序列：create sequence myemp3_seq;
删除序列：drop sequence myemp3_seq;
使用序列：在insert into myemp3 values(myemp3_seq.nextval,'张三','男',10,'101001@qq.com');
获取当前值使用myemp3_seq.currval
注意：myemp3_seq.nextval 每调用一次nextval 指针向后移动一位，也就是该序列增加一次；

create sequence seq_test999
increment by 10
start with 1
minvalue 0
maxvalue 190
cycle;
会报错：CACHE值必须小于CYCLE值（因为一次CACHE的值有重复的会出错）
-- cache值：默认20 指20个数 
-- cycle值：ceil((maxvalue-minvalue)/abs(increment))
-- CACHE值必须小于等于CYCLE值
需要满足一个公式：cache <= ceil((maxvalue-minvalue)/abs(increment))

       1 11 21 31 ... 191 20个数
cache  1 11 21 31 ... 191 1 11 21 ...191
场景：发消息 1、2、3、4、5、6、7

20 <= ceil((190-0)/10)
改成
create sequence seq_test999
increment by 10
start with 1
minvalue 0
maxvalue 191
cycle;

三、同义词(给其他对象取一个别名，方便其他用户调用和缩写对象名称)
创建同义词：create synonym mysys_synonym for myemp3; for 可以是其他对象
删除同义词：drop synonym mysys_synonym;

四、索引（提高检索速度）
创建索引：create index myemp3_index on myemp3(name,email) 联合(复合)索引
删除索引：drop index myemp3_index;
创建索引的场景：
该列值很多（覆盖很多条记录），经常被查询，例如where ename="",where A.xx=B.xx
如果该列值经常做update操作不适合建索引
======================================================================================================================================================================================================

一、plsql的基本结构和变量声明
1、基本结构
declare
--声明变量
begin
--plsql体执行其他操作
exception --有异常捕获处理可以加上该关键字
--异常处理
end;

2、变量声明：（常量、普通变量、引用普通类型变量、引用行类型变量、异常类型变量、游标类型变量）
set serveroutput on  --注意这里使用sqldeveloper 工具需要将控制台输出打开一次就行
DECLARE
    id constant number(2):=1;--使用constant 关键词定义常量
    name VARCHAR2(10):='悟空';--定义指定类型变量
    mysal emp.sal%type;--定义引用存在表的列类型
    myrow emp%rowtype;--定义引用存在表行类型，相当于java中对象，该行类型包含所有列   select * into v_account from xx
    no_data exception;--异常定义
    cursor c1 is select sal from emp;--游标定义，该c1中相当于java中集合，给
    cursor c2(dno number) is select sal from emp where  deptno=dno;---带参游标定义，该c2相当于java中带泛型集合
BEGIN
    raise no_data;
exception
    when no_data then xx;
    when others then  xx;
    --常量就不能再赋值了 id:=2 错误
    SELECT sal INTO mysal FROM emp WHERE empno=7369;
    SELECT * INTO myrow FROM emp WHERE empno=7369;
    dbms_output.put_line(id);
    dbms_output.put_line(name);
    dbms_output.put_line(mysal);
    dbms_output.put_line(myrow.ename || '   ' || myrow.sal);
END;

二、条件判断语句
1、select case when then when then else end
2、select decode()
3、if then elsif then else end if;
if xx then xx
elsif xx then xx --注意不能写成elseif
elsif xx then xx
else xx
end if;---注意加上分号

三、循环语句
第1种：
for i in 1..100 --退出循环是根据循环次数来定的
loop
end loop;
游标使用for in格式更简单
for 不需要定义任意变量名称 in c1;
loop
end loop;
第2种：
while --此处while后面声明退出循环条件
loop
end loop;
第3种：
open c1; ---此处打开游标
loop
fetch c1 into xx;
exit when c1%notfound; --此处exit when 声明退出循环条件
end loop;
close c1; --此处关闭游标

四、存储过程
第1种创建方式：不带参数
create or replace procedure mypro1 as
--这里可以声明变量
begin
end;
第2种创建方式：带in和out参数，参数个数不限制
create or replace procedure mypro2(eno in emp.empno%type,name out varchar2) as
--这里可以声明变量
myrow emp%rowtype;
begin
select * into myrow from emp where empno=eno;
name:=myrow.ename;
end;
plsql调用存储过程：
execute mypro1;---对于无参数存储过程可以这样调用
或者
call mypro1(xxx)--对于只有in 参数可以这样调用
或者
declare --存储函数和存储过程都可以这样调用
name varchar2;
begin
mypro2(7369,name);---此处声明一个变量用于接收out参数输出
end;

五、存储函数
1、创建存储函数：
create or replace function myfn1(eno in emp.empno%type) return varchar2 as
--实际return name 的name类型一定要和定义的return varchar2要一致
--这里可以声明变量
myrow emp%rowtype;
name varchar2;
begin
select * into myrow from emp where empno=eno;
name:=myrow.ename;
return name;
end;
2、plsql调用存储函数：
select myfn1(7369) from dual;
或者
declare
name varchar2;--此处要声明一个变量用于接收返回的结果
begin
name:=mypro2(7369);
dbms_output.put_line(ename);
end;

六、java api 调用存储过程和存储函数
DriverManager.getConncetion() -> Connection ->CallableStatement prepareCall
-> 设置传入参数直接通过设置类型setInt() ->
设置传出参数，需要registerOutParameter
CallableStatement execute();

CallableStatement调用存储过程和存储函数的语句
{call mypro1(?,?)}  调用存储过程
{?=call myfn1(?)}   调用存储函数

七、触发器
1、前置与后置
前置触发器：sql语句commit之前
后置触发器：sql语句commit之后
2、从影响行数来看
insert into emp() values() 插入了3条记录
行级触发器：触发3次
表级触发器（或者说语句触发器）：触发了1次
3、语法
create or replace trigger 触发器名
before|after
delete|update|insert
on 表名
for each row--行级触发器
begin
pl/sql语句
end;

====================================================================================================================================

oracle总结：
一、数据库基本功底(ddl+dml（insert/update/delete）+单表+多表)
基本语句：select from where group by having order by
多表：where连接内连接+on连接的外连接
jdbc操作相关：
Statement/PreparedStatement/CallableStatement
con.prepareCall("存储函数和存储过程")
mysql连接语法：jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
oracle连接语法：jdbc:oracle:thin:@192.168.25.128:1521:orcl

二、数据库优化
1、sql语句优化
1）查询的时候尽量使用列名；如：select empno,所有列名写出来 from emp;
2）分组查询有聚合函数,如果使用过滤，使用having
普通查询带条件，使用where
3）在子查询和多表查询之间选择，尽量使用多表查询
4）在集合运算中，如果union 和union all都可以，考虑使用union all因为union需要去掉重复的
5）能够不要使用集合运算就不要使用集合运算
6）对于多个条件连接，可以将false的放在and最右边，可以将true的条件放在or的最右边

2、索引优化
    建立索引：单个列建立索引以及多个列上建立索引（复杂或者联合索引）
    怎么建立索引：建立索引的两个场景需要斟酌
    1）建立索引：在某一个列上建立索引，必须考虑到该列的值是否覆盖更广，并且查询很频繁
    2）不建立索引：某一个列经常被改变，就不要建立索引

三、实际中常用的：
DML insert+update+delete+select
DDL create alter drop truncate