Oracle数据库

最新推荐文章于 2023-08-13 20:44:07 发布

weixin_30682415

最新推荐文章于 2023-08-13 20:44:07 发布

阅读量489

点赞数

原文链接：http://www.cnblogs.com/stanwuc/p/10648058.html

版权

* 基础 ***

#准备知识

1、Oracle的卸载重装：oracle卸载之后可能无法立即重装

电脑进入安全模式，删除对应磁盘路径的oracle相关文件；
在注册表中搜索与 oracle 相关内容，并且删除；
重启电脑一般情况下即可重新安装；
oracle安装完成后，所有服务设置为手动，只启动实例服务和监听服务即可；

2、sqlplus命令总结：sqlplus输入密码的时候是不会显示的

set linesize 长度设置每行长度
set pagesize 长度设置每页长度
conn 用户名/密码（as sysdba）切换用户，如果是 sys 用户一定要写 as sysdba
host 本机命令调用本机命令

**************************************tips*******************************************

一行数据显示不全（sqlplus中）：col name format a20；set linesize 200

*************************************************************************************

3、scott用户表结构：dept部门表、emp员工表、salgrade工资等级表、bonus工资条表

4、Oracle数据库、实例、表空间、用户、数据文件和数据表之间的关系

oracle 数据库和其他数据库不一样的是 oracle 的表和其他的数据库对象都是存在用户下的；
不同的用户可以在同一个表空间下建立同名的表，同名的表通过用户来区分；
Oracle 的用户和表空间的关系；详细的解释；

#SQL基本分类

1、DDL（数据定义语言）：CREATE、ALTER 、DROP

修改表结构
ALTER TABLE <tablename>
MODIFY (column definition);
ADD (column definition);
DROP COLUMN column;

查看表结构
DESC <tablename>;

给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT UNIQUE (字段名);
alter table test1 add constraint u_id unique(id);
alter table test1 add constraint key_id primary key(id);

改表名
alter table persons2 rename to personone;
改列名
alter table persons  rename COLUMN name to pname;

2、DQL（数据查询语言）：SELECT…FROM…WHERE…

distinct是对行去重，不是对列去重；
select可以输出常量，数字原样输出，字符串加单引号，可以用 || 拼接；
限制数据条数的查询：top（sql server）、limit（mysql）、ROWNUM（oracle）；

select * from (select emp.*,rownum rn from emp where rownum <10) temp  where
temp.rn between 2 and 5;

3、DML（数据操作语言）：INSERT、UPDATE、DELETE

--oracle数据库批量insert
insert all into persons2 values(1,'haha',23)
    into persons2 values(2,'haha',23)
    into persons2 values(3,'haha',23)
    into persons2 values(4,'haha',23)
    into persons2 values(5,'haha',23)
  select 1 from dual;
--mysql批量插入数据
INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

4、DCL（数据控制语言）：GRANT、REVOKE、COMMIT、ROLLBACK

授予对象权限
GRANT SELECT,UPDATE  ON   order_master
  TO MARTIN;

取消对象权限
REVOKE SELECT,UPDATE  ON  order_master
  FROM MARTIN;

事务相关
UPDATE xxx;
 SAVEPOINT mark1;
DELETE FROM xxx;
 SAVEPOINT mark2;
 ROLLBACK TO SAVEPOINT mark1;
 COMMIT;

#where | group by | order by | having等关键词使用

1、关于聚合函数和 group by

group by 只能使用 from 和 where 中定义的别名；
select中有聚合函数但是后面没有group by，则select子句中不能再出现其他的字段
使用了group by 后select子句中只能出现聚合函数和分组字段
聚合函数嵌套之后，select子句中只能有嵌套的聚合函数，不能包含其他的字段

2、伪列 rowid 和 rownum（伪表dual）

rownum（行号）
- 行号是自动生成的，一旦select出来就是临时表的一个列，是固定值；rownum 只能用小于
```
select emp.* ,rownum rn from emp where rownum < 3;
```
- 行号的应用：查询第一行；查询第n到m行（实现分页操作），具体代码的形式如下
```
select * from
(select *,ROWNUM rn from 表 
where ROWNUM<=(currentPage*pageSize)) temp
where temp.rn >=(currentPage-1)*pageSize
```
rowid ：数据行唯一的物理地址的编号（案例：数据去重并保留最早的数据）

3、关于限定查询 where

select 中定义的别名在 where 中不能使用
not in 和 null 不能一起使用
is null 和 is not null
null 做计算结果为 null
多条件用 and 和 or 连接的时候每个条件用（）分割
where不许使用聚合函数（统计函数），这也是他和 having 不同的地方
like模糊匹配：%（匹配多个） _（匹配一个）

4、关于 order by

最后执行，可以使用select中定义的别名
order by 后面可以跟多个字段，实现按多字段的排序

5、关于关键词的顺序

执行顺序：from > where > group by > having > select > order by
书写顺序：select from where group by having order by

---------------------------------tips------------------------------

select having order by都可以使用聚合函数；
分组之后的过滤用having，但是这个过滤是只针对聚合函数和分组字段的；
关于 all 和 any 的用法：any表示有任何一个满足就返回true，all表示全部都满足才返回true
在 mysql 中，修改或者删除表中记录，不允许在子查询中使用相同表，可以将子查询结果作为一张临时表即可解决；
语法：case when … then … when … then … else … end（就是 sql 中 if--else）

--------------------------------------------------------------------

#多表关联查询与子查询

一、表连接

1、笛卡尔积

多表查询产生笛卡尔积，查询结果行数是各表行数乘积；
多表查询使用连接条件就是为了去除无用的笛卡尔积；

2、内连接（inner join）：只有两个表中匹配的行才会在结果集中出现

--显式
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
--隐式
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites,access_log
where Websites.id=access_log.site_id;

3、外连接（outer join）

左外连接：从左表返回所有的行，即使右表中没有匹配

--left join 和 left outer join 是完全等价的
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
--oracle 中特有的写法
FROM e JOIN d ON e.id = d.id(+)

右外连接：从右表返回所有的行，即使左表中没有匹配

SELECT Websites.name, access_log.count, access_log.date
FROM access_log
RIGHT JOIN Websites
ON access_log.site_id=Websites.id
ORDER BY access_log.count DESC;
--oracle 特有写法
FROM e JOIN d ON e.id(+) = d.id

全外连接：返回左表和右表中所有的行，即使没有匹配

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

4、自连接：本质就是把一张表当成两张来用

SELECT FIRST.CNumber， SECOND.PCNumber
FROM Course FIRST， Course SECOND
WHERE FIRST.PCNumber=SECOND.CNumber；

5、总结：连接 n 个表，至少需要（n-1）个连接条件；连接不仅有等值连接，还有不等值连接；

--不等值连接
select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal

二、子查询（单行|多行）

from子句中使用子查询：子查询的结果是多行多列，看成一个临时表作为数据源
where子句中使用子查询：子查询的结果可以是单行单列，多行单列（in | not in）或者单行多列，单行多列的情况比较少
having子句中使用子查询：当使用了聚合函数后才会使用
关于确定数据范围的 in all（比所有数据大）any（比任意一个大）
子查询的效率高于多表查询，尽量少用多表查询

三、结果集的交并差

union（并集去重）、union all（并集不去重）、intersect（交集）、minus（差集）；
使用集合操作的前提是：每个 SELECT 语句有相同数量的列，列有相似的数据类型，列的顺序相同；

#常用函数

一、单行函数

1、字符串函数：length、substr、trim、replace、||

oracle 区分大小写
oracle 字符串的索引左边从1开始，右边从-1开始
substr函数在oracle里支持负索引的截取

2、数值函数

3、日期函数

4、转换函数：to_char 、to_date、 to_number

to_char（sysdate，‘mm’）可以截取年月日
to_char 第二个参数是转换格式，如果是数字转字符的话，9表示任意的数字，L表示本地的货币符号
oracle有类型的自动转换机制（隐式转换）

select to_char(1213.21212,'L9999.99') from dual;

5、通用函数（适用于所有数据类型含空值）

空值处理：nvl（列，0）
条件判断：decode（列名，if1，then1，if2，then2，else）

--decode用法：
select ename,decode(sign(sal-4000),-1,decode(sign(sal-3000),-1,'低收入','中收入'),'高收入') 收入水平 from emp;

--sign函数:sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 
--例如：引用 a=10，b=20 则sign(a-b)返回-1

二、聚合函数：多行函数

avg、max、min、sum、count（count不会统计字段为null ）

#数据更新与事务管理

一、数据更新

表的复制：create table myemp as select * from emp（这里的 as 不能少）
数据插入：字段名不写的时候数据的值要根据表中的顺序插入而且不能有少数据的情况；插入时间和时间磋的时候可以用to_date进行转换
数据更新：update 一定要用 where 子句来限定
数据删除：工作中删除操作要慎重，通常用逻辑删除，而不用物理删除

二、事务管理

数据表的增删改会涉及到数据的变化，要考虑到数据的完整性，因此引入事务；
统一业务分步完成，要么全成功，要么全失败；
数据库通过缓冲区实现事务的管理；
一个session：一个会话、一个用户、一个独立的事务处理；
commit 和 rollback；
死锁的概念：两个session操作同一条数据，一方进行了跟新，且没有进行提交或者回滚，另一方则处于等待，反之也是这样
发生 ddl 操作数据自动提交；

三、事务的隔离级别

1、四种隔离级别

2、说明：

脏读：一个事务读取另一个事务尚未提交的修改就是脏读；
不可重复读：同一查询在同一事务中多次进行，在此期间，其他事务提交了对数据的修改或删除，每次返回不同的结果；
幻读：同一查询在同一事务中多次进行，其他事务做了插入操作并提交了，虽查询条件相同，但每次返回的结果集不同；
oracle 支持后面的三种事务隔离级别，默认是 read committed；
四种隔离级别从上到下：安全级别越来越高，性能越来越差；

#表的创建与管理

1、常用的数据类型：varchar2（m）、number（n，m）、DATE、CLOB（大文本类型）

2、表的创建、删除、修改、复制、截断（truncate）、重命名（rename old to new）

--复制表结构 mysql
create table myactor select * from actor where 1 = 2; 
--复制表数据
insert into myactor select * from actor;
--其他数据库 复制表结构
SELECT *
INTO newtable
FROM table1
WHERE 1=0;
--复制表数据
SELECT name, url
INTO WebsitesBackup2016
FROM Websites;

3、数据字典：oracle数据库对象通过数据字典来管理（user_tables就是一个数据字典）

7、闪回技术（oracle）：强制删除表：drop table myemp purge；清空回收站：purge recyclebin

9、总结：数据表属于数据库的对象，数据库对象的操作只有三类语法：create、drop、alter

#约束创建与管理

1、非空约束：not null

2、唯一约束：unique

--建表时加约束
CONSTRAINT uc_PersonID[约束名] UNIQUE (P_Id,LastName) 
--修改表时加约束
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)|ALTER TABLE Persons ADD UNIQUE (P_Id)

ALTER TABLE Persons DROP INDEX uc_PersonID  --mysql撤销约束
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID  --sqlserver、oracle 撤销约束

3、主键约束：primary key（非空唯一）

复合主键：所有字段均相同才重复，一般不使用；
指定主键约束的名字：constraint pk_pid primary key(pid)；

4、检查约束：check

用途：约束字段的合法范围
写一个类似 where 子句的过滤条件
检查约束越多速度越慢

--检查约束：
alter table temp4 add check(id between 0 and 100);

5、外键约束：foreign key reference

父表关联子表外键的字段必须设置唯一约束或者主键约束
两个表存在主外键关系，要先删除子表才能删除父表，或者采取强制删除的方法（drop table member cascade constraints）
删除父表的数据的时候，如果子表存在对应的记录，则父表的数据无法删除，
强制删除父表数据（在创建约束的时候后面加上）：级联删除：on delete cascade 级联更新：on delete set null

--外键约束：alter table zemp add constraint fk_deptno1 foreign key(deptno) REFERENCES zdept(deptno); alter table zdept add constraint u_deptno unique(deptno);

6、添加和删除约束

添加非空约束只能采取修改表结构的方法
删除约束的语法：alter table emp drop constraint pk
添加约束一定要设置名称

#使用总结

1、关于空值（null）

（1）空值不等与空值；

（2）包含 null 的表达式永远为 null；

（3）排序时的空值处理：通过以下方式放在最前面或者末尾

select * from emp order by sal nulls first select * from emp order by sal nulls last

（4）聚合函数会忽略空值，nvl 使聚合函数无法忽略空值；

（5）子查询的空值问题：

单行：任何值都不等于空值；

多行：not in 不能和 null 共用；

2、sql 优化

（1）select * 和count（*）等表述方式比较慢，建议使用具体的字段；

（2）子查询的效率高于多表查询，尽量少用多表查询；

* 进阶 ***

#常用数据库对象

一、序列（自增列sequence）

1、示例

--序列的使用： create sequence s1 increment by 2 start with 0 maxvalue 100 minvalue 0 nocycle nocache; --当前值 s1.currval; --下一个值 insert into temp4 (id)values(s1.nextval);

2、说明

myseq.nextval：下一个值；
myseq.currval：当前值；
序列的作用：就是实现一个自增长的列，比如主键列；
序列的裂缝产生：系统异常；回滚操作；多表公用一序列；

二、同义词（别名-了解）

create public synonym myemp for scott.emp；
作用：方便其他用户来访问 scott 的 emp 表；
说明：oracle 独有，了解即可；

三、视图（封装复杂查询）

1、视图定义

2、视图授权：对普通用户，创建和查询视图都需要授权

conn sys/change_on_install as sysdba;
grant create view to scott;

3、视图更新

一般来讲涉及多表的视图无法更新数据，单表的视图可以，且更新视图会更新数据来源表的数据
视图尽量只做数据的呈现，不做数据的修改，因为限制很多；
在对视图进行增删改的操作的时候自动加上视图的创建条件（where条件）：创建视图的时候加上 with check option
建议创建只读视图：在后面加 with read only

四、索引（目录）

1、定义

--单列索引
create index sal_index on scott.emp(sal);
--复合索引
create index sal_index on scott.emp(sal,deptno);

2、说明

数据量大且数据的更新不频繁的情况下使用索引很好；
数据量大更新也比较频繁，要求能够更快的检索和统计，只能牺牲实时性，用两张表，一张建立索引，一张记录更新的信息，最后在夜间进行表的更新操作；
索引是通过建立二叉树的原理来实现的；

五、用户管理

1、用户创建：create user dog identified by wangwang

2、授予权限：授予角色后需要重启生效

解锁账户：alter user dog account unlock
修改密码：alter user dog identified by miao
使密码失效：alter user dog password expire
授权给用户：grant create table to dog；grant update[all] on emp to dog；grant connect，resource to dog；

--查看当前用户的系统权限：
select * from session_privs;仅权限
select * from role_sys_privs;角色和权限
--查看用户对象权限：（以sys身份查看）
select * from dba_tab_privs where GRANTEE='SCOTT';

3、用户删除：drop User cat cascade

4、关于权限：系统权限（connect|resource|dba）、对象权限（create 、select、update、insert、delete）

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
以上实际上是三个内置角色；

六、数据库备份（了解）

1、数据的导入和导出（基本不用）

2、数据库的冷备份（关闭服务器，进行备份）

#数据库设计

###基本原则：满足业务需求的同时减少多表查询和复杂查询

1、第一设计范式

数据表中的每个字段都不可再分，数据使用标准类型（number、varchar2，date），使用单表

2、第二设计范式

理解为多对多的关系
多对多要建立中间表

3、第三设计范式

一对多关系，优先使用；
在多方建立外键指向一方的主键

4、powerdesigner使用

#PLSQL编程

一、基本语法

1、PLSQL块

declare
/*申明部分*/
begin
/*程序正文*/
exception
/*异常的处理部分*/
end；

2、说明

作用：sql语言的过程化，实现更加复杂的功能
知识：select into 语法变量赋值用：= 比较相等用 =

三、记录型和引用型变量

1、记录型

概念：逻辑相关的数据集合；类似于 java 中的类的概念；
语法：myemp emp%rawtype（myemp 代表 emp 表中一行的数据类型）；

2、引用型

概念：声明的时候取其他数据的类型；
语法：mysal emp.sal%type（mysal 和 emp 中的 sal 类型相同）；

三、流程控制

--判断和循环语句：
declare
step number:=1;
begin
  while step <10 loop
    if step = 2 then
      dbms_output.put_line('this is 2');
    elsif step = 5 then
      dbms_output.put_line('this is 5');
    else
       dbms_output.put_line(step);
    end if;
    step := step + 1;
  end loop;
end;

--其他两种循环
declare
  step number:=1;
begin
  for step in 1..10 loop
    dbms_output.put_line(step||'haha');
  end loop;
end;

declare
  step number:=1;
begin
  loop
    exit when step >10;
    dbms_output.put_line(step||'kaka');
    step := step +1;
  end loop;
end;

四、游标（Cursor）

1、作用：类似于 java 中的集合，可以存储查询返回的多条数据；

2、使用：类似于集合和遍历

--游标：
declare
  cursor kk is select * from scott.emp;
  pemp scott.emp%rowtype;
begin
 open kk;
      loop
        fetch kk into pemp;
        exit when kk%notfound;
        dbms_output.put_line(pemp.ename || pemp.sal);
      end loop;
 close kk;
end;

五、异常处理

1、常见的系统异常

--异常处理：
declare
  step number:=12;
  haha number;
begin
  haha := step/0;
 exception
   when zero_divide then
     dbms_output.put_line('zero_divide');
   when others then
     dbms_output.put_line('other_error');
end;

2、自定义异常

--自定义异常：
declare
  cursor kk is select * from scott.emp;
  pemp scott.emp%rowtype;
  no_emp_found exception;
begin
 open kk;
      loop
        fetch kk into pemp;
        if kk%notfound then
          raise no_emp_found;
        end if;
        dbms_output.put_line(pemp.ename || pemp.sal);
      end loop;
 close kk;
exception
  when no_emp_found then
    dbms_output.put_line('no_emp_found!!');
  when others then
    dbms_output.put_line('other_error');
end;

#数据库对象补充

一、存储过程

1、概述：就是一组能够完成特定功能的 sql 语句集

2、创建&调用

--存储过程：
create or replace procedure mypro(eno in number) as
  pemp scott.emp%rowtype;
begin
  select * into pemp from scott.emp where empno = eno;
  update scott.emp set sal = sal + 100 where empno = eno;
  dbms_output.put_line('old sal:'||pemp.sal||'   new sal:'||pemp.sal+100);
end;
--查看存储过程编译错误（命令行）：
show errors procedure mypro;
--调用过程：
call mypro(7369);

3、说明

存储过程和存储函数的区别，函数有返回值；
但是二者都有 in | out 参数，通过 out 可以实现返回多个值；
用存储过程就可以了，函数能实现的过程都能实现；
了解 java 怎么调用存储过程；

二、存储函数（了解）

--函数定义：
create or replace function fun(eno in number)
return number is
   psal emp.sal%type;
   pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno = eno;
  return psal*12+nvl(pcomm,0);
end;
--调用：
declare
  allsal number:=0;
begin
  allsal := fun(7369);
  dbms_output.put_line(allsal);
end;
--替换：
create or replace procedure pro1(eno in number,res out number) as
   psal emp.sal%type;
   pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno = eno;
  res := psal*12+nvl(pcomm,0);
end;
--调用：
declare
  innum number:=7369;
  outnum number:=0;
begin
  pro1(innum,outnum);
  dbms_output.put_line(outnum||'haha');
end;

三、触发器

1、概述

触发器是表关联的存储的PL SQL程序，在对表进行增删改的操作时会自动执行；
作用：安全检查；数据确认；数据跟踪；数据备份和同步；

2、分类

--语句级：不管语句影响了多少行，都只在语句执行前或者后执行一次；
create or replace trigger tri1
after update on zemp
declare
begin
    dbms_output.put_line('data updated'); 
end tri1;
--行级：语句影响一行执行一次；
create or replace trigger tri2
before update of sal on zemp
for each row
declare
begin
  if :old.sal>:new.sal then
    dbms_output.put_line('old sal can not be largger than new sal');
  end if;
end tri2;

3、触发语句与伪记录变量的值

4、实际应用：使用序列和触发器模拟 mysql 中的自增效果

--建表
create table haha(
  id int not null,
  name varchar(50)
);
--建序列
create sequence sq8 increment by 2 start with 0 maxvalue 999 minvalue 0 nocache nocycle;
--建触发器
create or replace trigger index1 
before insert on haha
for each row
declare
begin
  select sq8.nextval into :new.id from dual;
end;
--测试触发器
insert all into haha (name)values('a1')
into haha (name)values('a2')
into haha (name)values('a3')
select 1 from dual;

转载于:https://www.cnblogs.com/stanwuc/p/10648058.html

weixin_30682415

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库

*********************** 基础 *************************#准备知识1、Oracle的卸载重装：oracle卸载之后可能无法立即重装电脑进入安全模式，删除对应磁盘路径的oracle相关文件；在注册表中搜索与 oracle 相关内容，并且删除；重启电脑一般情况下即可重新安装；oracle安装完成后，所有服务设置为手动，只启动...
复制链接

扫一扫