Oracle学习笔记

Oralce数据库基础知识
1) 连接用户：conn 用户名/密码
2) 显示用户：show user
3) 在DOS下进入SqlPlus：sqlplus用户名/密码 ——连接到用户进入
Sqlplus/nolog ——未连接到用户进入
4) SQL 语句分类： DML：insert update delete merge
DDL：create alter drop truncate
DCL：grant revoke
TCL：commit rollback savepoint
SELECT查询语句：select
5)显示当前模式中的表： select * from tab;
6)显示表中的字段：desc table name；
7)用户解锁：进入system用户，alter user hr account unlock;
8)更改密码：进入system用户，alter user hr identified by hr;
9)进入文本编辑：ed 执行时：’/’ 用分号无效
后缀文件：.dbf 数据文件
.ctl 控制文件
.log 日志文件 (以上三种类型文件不可修改)
.ora 初始化参数文件
10)运算符：+ - * / 可以对数值进行操作
+ - 对日期的天数进行操作
不能对字符串进行 + - * / 操作，对于字符串的连接用‘||’
null 值进行算术运算，其结果都为空
其他符号：“”双引号，只能用于别名，用于输出想要的表现形式的字段形式
‘’单引号，字符串
11)比较运算符：between…and… 在两值之间（包含）
In(list) 匹配列出的值
Like： % 表示0个或多个字符
_ 表示一个字符
Is null 空值
= <> >= <=
逻辑运算符： and or not
对查询数据的排序： order by 字段 [顺序] asc升序（默认） desc降序
order by 子句出现在select 语句的最后
order by唯一的一个可以对别名进行操作的子句
12)大小写问题：字段名对大小写不敏感，但是对于所存数据内容，严格判断大小写

13)[b]Sql 中的函数[/b]：
[b]单行函数[/b]：接受参数并返回一个值，分为五类函数
(1)字符函数
大小写转换函数：Lower Upper Initcap首字母大写
字符处理函数： concat字符串连接
substr(字段名，起始位置，长度) 返回指定长度的字串
length返回字符串长度
instr(字段名，子串) 返回子串的位置 lpad|rpad(字段名，长度，补位符)补位函数，若字符串超过了 指定的长度，则将会截取指定长度的字符串
trim取消前后空格，对于字符串内包含的空格无效
replace(字段名，需要替换的字符，替换的字符)
PS：位置从1开始
(2)日期函数
select sysdate from dual; 从临时表中获取当前系统日期
months_between(日期1，日期2) 日期1-日期2，两日期相差多少月
add¬_months(日期，月份数) 添加月份，月份数可为负数
next_day(日期，指定日期) 指定日期的下一个日期
last_day(日期) 当月的最后一天
round(日期，‘year/month’) 日期四舍五入，会对日期进行初始化
trunc(日期，‘year/month’) 会对日期进行初始化
(3)数字函数
round(数字，位数)，四舍五入到指定小数位数 (数字)取整
trunc(数字，位数) ，截取到指定的小数位数
位数为负数时，倒推到整数
mod 取余
(4)转换函数
隐含的数字类型转换
显示的数字类型转换： to_char(日期，格式)日期转换成字符串，格式必须用单引 号括起来，大小写敏感
to_char(数字，格式)将数字作为字符显示，9代表数字， 0表示强制输出， L表示获取当地货币符号，逗 号表示千位指示符
to_number(字符串，格式)将字符串转换成数字 to_date(字符串，日期格式)将字符串转换成日期，格式必 须跟字符串中的日期格式相对应
PS：格式必须匹配，且长度必须满足要匹配的数据，否则会以#号显示
(5)其他函数
nvl(字段名，值) 将空值转换为实际的值，数据类型必须匹配
nvl2(表达式1，表达式2，表达式3)
nullif(表达式1，表达式2) 比较两个表达式，如果相等返回空值
coalesce (表达式列表) 返回表达式列表里的第一非空表达式
case表达式，实现逻辑的if-then-else
case 字段名when …then …else… end期间不用空格
decode(字段名，值，新值，值，新值，… 默认值)
多行函数：又称为分组函数，运算每一组记录，每一组返回一个结果，最多只能嵌套两层
avg(distinct|all 字段名) 平均值
count() 计数
max() 最大值
min() 最小值
sum() 求和
group by 分组
having分组之后的条件限定，跟在group by 之后。 在where子句中不允许使用分组函数
查询格式：select * , column newName
from table
[where 条件表达式]
[group by column]
[having 分组条件表达式]
[order by column desc]


14)[b]多表连接和子查询[/b]
可利用表的别名进行操作
连接的类型：
(旧标准下的连接方式)：对于两张表中的相同字段名，必须指明表名
(1)等值连接 = 注意效率的优化 ，连接N个表，则至少需要N-1个连接条件
(2)非等值连接
(3)外连接
(4)自连接
(新标准下的连接方式)：
(1) cross join 交叉连接 笛卡尔乘积
(2) natural join 自然连接 保证两张表只有一个字段是相等的
(3) join using
(4) join on
(5) left/right/full outer join on

多表连接：
select 字段名
from table1
[cross join table2 ] --交叉连接
[natural join table2] --自然连接
[join table2 using (相同字段名) ] --多个字段相同情况下的自然连接，对于两张表中的相同字段名，不允许指明表名
[join table2 on (等值条件) ，join table3 on()。。。]
[left | right | full outer join table2 on(等值条件)]

子查询：先执行子查询，再执行主查询，子查询要写在括号内
单行子查询
多行子查询：in 等于列表中的任何值
any 将值与子查询返回的任意一个值进行比较 <any指小于最大值， >any指大于最小值
all 比较子查询返回的每一个值
rownum ：TOP-N ，返回所需要求的记录数
--返回总人数排名前三的部门名称
select rownum , department_name , total
from (select d.department_name , count(employee_id) total
from employees e , departments d
where e.department_id = d.department_id
group by d.department_name
order by total desc
)
where rownum <= 3;

15)[b]DDL 数据定义语言[/b]
表空间和用户是两个相互独立的对象，两个都需要经过各自的drop之后删除
DDL，在操作时需要指定 table column关键字，DML则不需要指定table，column关键字，同时DML执行后需要与TCL配合使用。DDL执行后就生效。
创建表空间：create tablespace 表空间名
datafile ‘ D:soft\oracle\product\10.2.0\oradata\orcl\表空间名.dbf ’
size 大小(200m);
查看系统用户表空间：select * from user_tablespaces;
创建用户： create user 用户名
Identified by 密码 default tablespace 表空间名
quota 50m on 表空间
account unlock;
需要设置用户名，密码，默认的表空间，使用的空间大小以及来自于哪个表空间， 用户解锁
查看所有的用户信息：select * from dba_users;
删除用户：drop user 用户名 cascade;
删除表空间：drop tablespace 表空间名 including contents; 表空间中的用户下有数据
更改ALTER：
增加字段
alter table tablename
add (column_name datatype , column_name datatype 。。。)
若要加上非空约束条件，则表必须为空
修改字段：只能动态扩大列宽，不允许动态减小列宽。数据类型改变，前提是该列为空。对于以存在的字段，添加Not null 约束，前提是该列不能有空值存在
alter table tablename
modify column_name datatype
删除字段
alter table tablename
drop column column_name;
修改字段名：
alter table tablename
rename column column_name to new_column_name;
删除整张表的数据： 可以释放表的存储空间
truncate table table_name;
delete 和 truncate 之间的区别：
(1) delete 删除之后可以回滚，但是truncate 是永久性删除
(2) delete 可以选择性删除数据，truncate 则是整体删除
(3) truncate 执行之后，空间被释放，delete则不释放空间。


16)[b]DCL 数据控制语言[/b]
刚创建好的用户，没有任何权限，需要授权
授权：grant 权限　to 用户名 DBA权限：（dba）
连接权限：grant connect to 用户名
建表权限：grant create table to 用户名 with admin option(具有授权建表的权限)
查询其他表的权限：grant select any table to 用户名
撤销权限：revoke 权限 from 用户名


17)[b]DML 数据操作语言[/b]
数据定义语言操作之后需要进行commit
插入： insert into 表名(字段) values (字段);
更新： update 表名
set column = value,[column = value]
[where condition ]
删除： delete from 表名
[where condition]
合并： 根据条件在表中执行修改或插入数据的动作 ( 9i 及以上版本)，永久保存需要commit 提交
Merge into table_name table_alias(别名)
Using (table | view | sub_query) alias
On (join condition)
When matcher then
Udate set col1 = col_value。。。
When not matched then
Insert values();
18)[b]TCL 事务控制语言[/b]
commit ：提交
rollback ：回滚到最后一次commit之后
savepoint 点名：rollback to 点名 ，返回到指定存储点，commit之后，存储点自动消失


19)[b]数据库中的主要对象[/b]
[b]数据库对象的命名规则[/b]：
必须以字母开头
可包括数字和三个特殊字符( # _ $)
不要使用oracle 的保留字
同一用户下对象不能同名
[b]数据库的对象[/b]：
(1)表： 基本的数据存储对象，有两部分组成，表名字段 + 数据，在Oracle中存在用户表和系统表两类。
建表必须具有建表的权限，足够的存储区域。最多254列
常用的数据类型：varchar2(1-32767) char(size) number(p,s) date/timestamp long(2G) Binary_Float BFILE BLOB / CLOB(大二进制对象 / 大字符串对象)
指定用户创建表： create table 用户名.表名
指定表空间： 在最后括号外分号前+ tablespace 表空间名
使用子查询创建表： create table 表名 as select * from 另一张表名
更改字段名：select 时用别名 或 create table 表名（字段名）。。。
只获取表结构而不读取内容：create table 表 as select * from 表where 1=2
更改表名： rename table_name to new_table_name
(2)数据字典：也就是系统表，存放数据库相关信息的表
是每个Oracle数据库的核心，用于描述数据库和它的所有对象，包括了只读的表和视图，被SYS用户拥有，被Oralce Service服务。
三种字典视图： DBA 所有方案包含的对象信息
ALL 用户可以访问的对象信息
USER 用户方案的对象信息
select * from user_objects
(3)约束条件Constraint：强制执行数据校验规则，保证了数据完整性。
自定义约束名 constraint tablename_columnname_约束缩写 constraint_type
非空 not null (nn)
唯一键 unique (uk) 同时也会创建索引，null值不作比较
主键 primary key (pk) 每一张表只有一个主键，同时也会分配一个唯一性的索引
外键 foreign key (fk) 作为主键的字段或者是唯一键 能作为外键 references
检察 check
可增加或删除约束，但不能直接修改，可使约束启用和禁用，非空约束必须使用modify子句增加
添加约束：alter table table_name
add constraint_name (column);
非空约束：alter table table_name
modify column not null;
删除约束：alter table table_name
drop constraint constraint_name ;
删除主键约束和相关的外键约束 delete primarykey cascade;
禁用约束：alter table table_name
disable constraint constraint_name;
启动约束:alter table table_name
enable constraint constraint_name;
启动约束时，所存数据不能违反约束，否则无法启动
(4)视图：一个或多个表数据的逻辑显示
好处：可以限制对数据的访问，可以使复杂的查询变的简单，提供了数据的独立性，提供了对相同数据的不同显示。增强数据的安全性。
创建视图：
create [or replace] view viewname [alias [,alias]]
as 子查询
[with read only] --禁止对视图执行DML操作
删除视图：drop view view_name;
(5)索引：用于提高查询的性能，schema中的一个数据库对象，用来加速对表的查询，与表独立存放，由Oracle数据库自动维护
索引的创建： 自动 & 手动
自动：当表上定义主键 或唯一键时，自动创建一个对应的唯一索引
手动：在一列或者多列上创建索引
create index index_name
on table ( column ,[column。。。]);
删除索引：drop index index_name
序列sequence
create sequence 序列名
increment by 数值 default increment by 1
start with 数值 要大于等于minvalue
minvalue 数值
maxvalue 数值
cycle or nocycle 循环 / 不循环 default nocycle
cache or no cache 缓冲 / 不缓冲 default cache 20
(6)同义词synonym：对象的别名，使用范围全局
创建：create synonym 同义词名 for 对象
删除：drop synonym 同义词名


20)[b]数据库开发[/b]
六个阶段：
1． 需求分析
2． 概念结构设计 E-R图
3． 逻辑结构设计
4． 数据库物理设计
5． 数据库实施
6． 数据库运行

21)[b]PL/SQL开发[/b]
PL/SQL是Oracle数据库对SQL语句的扩展，编程语言
优点：使一组语句功能形成模块化程序开发，使用过程性语言控制程序结构，可以对程序中的错误进行处理，具有较好的可移植性，
[b]PL/SQL块的基本结构[/b]：
声明部分declare、异常部分exception、执行部分begin end(必要部分)
declare ---可选部分
变量，常量，游标，用户定义异常声明
begin ---必要部分
SQL 语句
PL/SQL语句
exception ---可选部分
程序出现异常时，捕获异常并处理异常
end ；---必要部分

[b]启用输[/b]出：set serveroutput on;
[b]控制台打印[/b]：dbms_output.put_line();
[b]匿名块[/b]：只使用一次的PL/SQL程序块，没有名称，也不被存储在数据库中不能重复使用。
语法结构：声明，执行，异常
在PL/SQL中处理变量常量：
变量名 [constant(常量)] datatype [not null]
[:= | default expr];
在声明时，每行只能声明一个标识符，number可以不指定长度，varchar2需要指定长度
变量的类型：
(1)简单变量 ：基本类型 char varchar2 number date long boolean binary_integer。。。
(2)复合（组合）变量:表类型 & 记录类型 ，可以被使用多次
--记录类型
定义： TYPE name_record_type is record
(field_name1 field_type [not null {:= | default} expr]);
使用： 变量名 name_record_type;
--表类型
定义： Type name_table_type is table of datatype
Index by binary_integer;
使用： 变量名 name_table_type;
变量名(index) := value;

属性：count, delete(index), first, last,(返回index)
prior, next, exists(index)

(3)外部变量
%TYPE属性——变量名 表名.字段名%TYPE;
%ROWTYPE——变量名 表名%rowtype
22)[b]PL/SQL语法规则[/b]：
(1)标识符最多可以包含30个字符
(2)select 查询数据：
必须使用into ,查询必须并且只能返回一行，可以使用完整的select 语法
Select * from table_name
Into identifiers
From src
Where conditions;
(3)通过 execute immediate 执行DDL , DCL语句,并且要执行的DDL,DCL语 句要放在单引号内
(4)DML , TCL语句可以直接使用
23)[b]PL/SQL中的流程控制语句:[/b]
(1)条件判断语句： 最后不要忘了 end if;
If – then – end if
If – then – else – end if
If – then – elsif – then – [else] end if
(2)循环语句
1.简单循环
Loop
Statement1;
Exit [when condition]; --必须使用exit退出循环，否则会无 End loop; 限循环
2.For循环: 循环变量不用声明
For name in [reverse] low .. up(value) loop
end loop;
3.While循环
While condition loop
End loop;
(3)无条件跳转语句：goto 标签名 , 跳出循环，执行下面语句
定义标签：<<标签名>> 执行语句
24)[b]PL/SQL程序单元[/b]
(1)存储过程 procedure ：执行特定操作
1.创建存储过程：
Create or replace procedure name[(parameter ,. . .)]
Is / as
变量
Pl/sql_block(具体操作);
2.调用存储过程：
1.Call name();
2.Begin
Name();
End;
Parameter 的语法如下：
Parameter_name [in(default) | out | in out ] datatype(不 需要给长度)
[{:= | default }expr]
(2)函数function ：进行复杂计算
1.创建函数：
Create or replace function name
[(parameter,. .)]
Return datatype
Is
变量
Pl/sql_block;
在PL/SQL块中至少包含一个有效的return 语句。
2.调用函数：
Begin
V_value := F_name(parameters)
End
或者 select f_name(parameter) form dual;
函数和存储过程使用规则：
SQL语句中只能使用函数，不能使用过程，函数中不允许DML语句，形参必须为 IN，必须返回ORACLE支持的数据类型，不能使用PL/SQL数据类型，必须有 execute权限，存储过程作为一个PL/SQL语句来执行，函数作为表达式的一部分调 用
(3)包 package ：将逻辑上相关的过程和函数组织在一起
包有两个独立的部分，包头和包体，需要分别定义，包头包含了包中过程，函数的 声明，包体是真正的过程和函数的执行部分，包体只能在包头完成编译后才能进行 编译
Create or replace package name_pkg
Is/as
Function f_name (parameters) return datatype;
Procedure p_name (parameters);
。。。。。。
(声明：过程，函数等等)
End name_pkg;

Create or replace package body name_pkg
Is/as
包头声明的过程，函数等
Is/as
执行块
End 各个过程或函数名
End name_pkg;
(4)触发器 trigger ：事件触发，执行相应操作
触发器是在事件发生时隐式运行的，并且触发器不能接受参数。运行触发器的方式 叫做触动(firing)，触发的事件可以是对数据库表的DML操作或某个视图的操作， 也可以是系统事件，例如数据库的启动和关闭以及一些DDL操作。
触发器的作用：
安全性，
产生对数据值修改的审计，
提供更灵活的完整性校验规则，
提供表数据的同步复制，
事件日志记录
Oracle中的触发器：
(1)DML触发器:主要包括 insert update delete 三种触发器，TCL语句不 能在触发器中使用
触发时机：before after 视图的话instead of
触发范围：行级触发或语句级触发
1)语句级触发：
Create or replace trigger trigger_name
Timing(before/after)
Insert or update or delete [of column]
on table_name
[when conditions]
Trigger_body
2)行级触发器：
在on table 后面加上For each row
Begin中 :new.column : old.column 冒号需要加上
When中 new.column与insert old.column与update
可以设置when()子句
(2)Instead of 替代触发器
Create or replace trigger trigger_name
Instead of
Event1 or event2 or event3
On view_name
[For each row]
[Referencing old as old | new as new]
Trigger_body
(3)系统触发器
用户触发事件：create alter drop on database|schema
系统触发事件：启动关闭数据库，特殊情况
管理触发器：
启用或禁用某个触发器：alter trigger trigger_name disable/enable;
启用或禁用某个对象上的所有触发器：
Alter table table_name disable/enable all triggers;
重新编译触发器：alter trigger trigger_name compile;

25)SQL游标 Cursor：是一段私有的SQL工作区，有两种类型：隐式游标，显式游标，类似于java中的集合。
1.隐式游标：
[SQL|expr]%rowcount 受SQL影响的行数
[SQL|expr]%found Boolean值，是否还有数据
[SQL|expr]%notfound boolean值，是否已无数据
[SQL|expr]%isopen 总是为false
2.显式游标：
无参数的游标
--定义游标
Cursor c_name is select column form table_name;
--打开游标
Open c_name;
Loop
--将游标中的数据提取到变量中
Fetch c_name into 变量名;
Exit when c_name%notfound ;
执行块;
End loop;
--关闭游标
Close c_name;
带参数的游标：
cursor c_name(参数 类型) is select 字段 from 表名 where 条件

26)异常 Exception：
查询异常：too_many_rows
No_data_found
Zero_divide
Exception
When exception_name then 执行块；
When others then执行块；
自定义异常：定义：name exception; 抛出：raise exception