Oracle
数据库：存储和管理数据的仓库
RDBMS：关系型数据库管理系统（数据仓库+用户）
MySQL：一个根用户 管理多个数据库，不同的应用使用不同的数据库
Oracle：一个数据库中有多个用户，不同的用户管理不同的内容，不同的应用分配不同的用户
一个数据库服务器可以创建多个数据库，但是创建一个Oracle数据库会占用大量的内存空间，所以一般一个Oracle数
据库服务器只创建一个数据库
数据库服务：
只需要开启两个服务：
1、OracleServiceORCL：Oracle服务的主服务
2、Oracle....TNSListenter：监听服务
监听服务是负责监听客户端对数据库服务的请求
登录数据库的3中方式：
1、使用Oracle自带的sql plus
2、使用dos命令
cmd--sqlplus--输入用户名--输入口令
cmd--sqlplus 用户名--------输入口令
cmd--sqlplus 用户名/密码
3、使用第三方的图形化工具
Oracle预定义了用户：
sys：超级管理员 数据库的拥有者
system：管理员
scott、hr
登录sys用户时必须以管理员身份才能登录
cmd--sqlplus--sys as sysdba--输入口令
cmd--sqlplus sys as sysdba---输入口令
cmd--sqlplus sys/密码 as sysdba
常用的命令：
1、账户的锁定和解锁（只有管理员才有权限锁定和解锁用户）
锁定：alter user 用户 account lock;
解锁：alter user 用户 account unlock;
2、密码修改
修改自己的密码：
password--输入旧口令--输入新口令
passw--输入旧口令--输入新口令
alter user 自己的用户名 identified by 新密码
修改他人密码（只有管理员才有权限）
alter user 用户名 identified by 新密码
3、显示当前用户
show user
4、切换用户
conn 用户名
操作数据库使用的是SQL语句
SQL：Struct Query Language结构化查询语言
DQL：数据查询语言
DML：数据操作语言
DDL：数据库定义语言
DCL：数据库控制语言
TCL：事务控制语言
SQL语句是不区分大小写
用户管理：
创建用户：只有创建用户权限的用户才有权限创建用户
create user 用户名 identified by 密码;
登录发现报错：user KING lacks CREATE SESSION privilege;logon denied
新创建的用户没有任何的权限，新用户必须让管理员赋予权限后才可以对数据库进行一定的操作
删除用户：drop user 用户名;
修改用户：
修改用户密码：alter user 用户名 identified by 新密码
权限：可以做什么
系统权限：对Oracle数据库系统进行操作的权限（登录、创建表、创建视图、创建用户等）
对象权限：对Oracle数据库中对象（表、视图等，对表执行增删改查）操作的权限
给用户赋予权限：grant 权限 to 用户
撤销用户权限：revoke 权限 from 用户
每个用户会有多个系统权限和对象权限，挨个赋予权限太麻烦
角色：
角色是权限的集合
角色是命名的可以授予用户的相关权限的组，该方法使得授予、撤回和维护权限容易的多
Oracle预定义的角色：connect、resource、scheduler_admin、dba、select_catalog_role
自定义角色：
1、创建角色：create role 角色名;
2、授权给角色：grant 权限1,权限2，... to 角色名
将角色授予用户：grant 角色1,角色2 to 用户名
表空间：
创建用户时：默认表空间：当前用户的数据的默认存储位置
一个Oracle数据库能够有一个或多个表空间，而一个表空间则对应着一个或多个物理的数据库文件
每个Oracle数据库均有system表空间，这是数据库创建时自动创建的，system表空间必须总要保持联机，因为其包
含着数据库运行所要求的基本信息
创建表空间：
Create tablespace 表空间名
Datafile '文件名' 表空间所对应的数据文件的绝对路径
Size 大小 初始大小 m或k
Autoextend on 是否自动增长 on|off
Next 增长多少
Maxsize 最大值
一般情况下，默认表空间使用system，临时表空间使用temp
数据库设计（三范式）：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各
种范式呈递次规范，越高的范式数据库冗余越小
满足最低要求的范式是第一范式（1NF），在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其
余范式以此类推；一般来说，数据库只需满足第三范式（3NF）就行了。
第一范式（1NF）：数据库表的每一列都是不可分割的原子数据项，即实体中的某个属性有多个值时，必须拆分为不同
的属性；在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。
第二范式（2NF）：要求数据库表中的每个实例或记录必须可以被唯一地区分（设置主键）；选取一个能区分每个实体
的属性或属性组，作为实体的唯一标识。
第三范式（3NF）：要求一个关系中不包含已在其他关系已包含的非主关键字信息。
SQL语句中的注释：
单行注释 --
多行注释 /* */
表的创建：
语法：create table 表名(
列名 列的数据类型
)
Oracle中数据类型：
char：定长字符序列
varchar2：可变长字符序列
number：数值型；number(8,2)：总共有8位，其中小数点后两位
date：日期时间
表名和列名的命名规则：
* 必须以字符开头
* 必须在1-30个字符之间
* 必须只能包含A-Z,a-z,0-9,_,$,和#
* 必须不能和用户定义的其他对象重名
* 必须不能是Oracle的保留字
Oracle默认存储是都为大写
约束：constraint 约束是表一级的限制
非空约束：not null
唯一性约束：unique
检查约束：check()
主键约束：primary key
外键约束：foreign key
主键：可以唯一的标识一条数据的属性组；非空且唯一
外键：用来指明和关联表之间关联关系的列；外键列应该是关联表的主键
DQL：Data Query Language数据查询语言
基本查询：
查询指定列：select 列名1,列名2 from 表名
查询所有列：select * from 表名
查询的时候可以起别名：列名后面 as "别名" （as 可以省略）
查询时候可以做运算：+ - * / 可以是值和值、值和列、列和列
对于null：任何数据和null做运算，结果都为null
连接符：|| 可以连接字符串和列、字符串和字符串、列和列
字符串和日期需要使用单引号
删除重复行：使用distinct关键字
过滤查询：过滤使用where关键字 select ... from ... where 过滤条件
比较运算符：=, >, >=, <, <=, != ,<>（不等于）
in(值1，值2，值3）：等于值列表中的任意一个
between a and b:在a和b之间
like：模糊查询 %：零个或多个字符；_：一个下划线代表一个字符
是空：is null；非空：is not null
逻辑运算符：与 and；或 or；非 not；
排序：使用order by关键字
order by后面是用来排序的内容，可以使用列名、列的别名、查询列的顺序值、多列排序，先使用第一列排序，第一
列相同时再按照第二列排序；
使用多列排序的时候，每一列都需要指定排序规则；
排序规则：asc:升序；desc:降序；默认是升序
SQL函数：
单行函数：
字符函数：
lower(s):转换为小写
upper(s):转换为大写
initcap(s):首字母大写，其他小写
concat(s1,s2):连接字符串
substr(s,begin,len):截取字符串，从第begin个开始，截取len个
length(s):获取字符串长度
instr(s,s1):s1在s中第一次出现的位置
trim(c from s):从s字符串前后去除指定字符c
replace(s,old,new):将s中的old替换成new
lpad(s,n,s1):左填充，希望得到的字符串长度为n,如果s的长度不足n,左边用s1填充
dual:伪表
数字函数：
mod(a,b):a对b求余
power(a,n):a的n次方
sqrt(a):求a的平方根
trunc(a,b):截断，向下取，保留到小数点后b位
round(a,b):四舍五入，保留指定位数
日期函数：
sysdate:当前系统时间
months_between(date1,date2):获取两个日期相差的月数
add_months(date,n):给指定日期加上指定的月数
last_day(date):获取指定日期当月的最后一天
next_day():指定日期的下一个星期几
round():日期的四舍五入
trunc():日期的截断
日期格式化为字符串：to_char(date,format)
字符串转换为日期：to_date(str,format)
通用函数：
NVL(e1,e2):e1的值不为null时返回e1,当e1为null时返回e2
NVL2(e1,e2,e3):e1的值不为null时返回e2,当e1为null时返回e3
NULLIF(e1,e2):当e1=e2时返回null,不相等时返回e1
COALESCE(e1,e2,...,en):返回第一个不为空的值
实现分支的函数：decode函数（Oracle特有的函数）
decode(表达式,
值1，结果1,
值2，结果2，
...
结果n)
条件表达式：（符合SQL1999标准）
方式一：
case 表达式
when 值1 then 结果1
when 值2 then 结果2
...
else 结果n
end
方式二：
case
when 表达式1 then 结果1
when 表达式2 then 结果2
...
else 结果n
end
多行函数：
分组函数：
sum:求和
count:计数
avg:求平均值
min:求最小值
max:求最大值
count,avg统计的都是非空的数据，空值不计算在内
分组：使用group by关键字
过滤：
where:是对分组前的数据进行过滤；where后面不能有分组函数；where只能放在group by的前面
having:是对分完组后的数据进行过滤，过滤掉的是组；having后面只能跟分组函数或者是用来分组的列；having推
荐写在group by后面
使用：
如果用来过滤的不是分组函数，就是用where，放在group by的前面
如果用来过滤的是分组函数，就是用having,放在group by的后面
在有分组的查询中，select后面查询的内容只能是分组函数或者是用来分组的列
子查询：一个查询中需要使用到另一个查询的结果
1、子查询要写在小括号中
2、子查询在外层查询之前执行
3、单行操作符对应单行子查询，多行操作符对应多行子查询
单行操作符：=、>、<、>=、<=、!=或<>
多行操作符：in、any、all
分页：
MySQL数据库提供了函数limit可以实现分页
Oracle中使用伪列：rownum（对查询出来的数据排的序号）
注意：对于rownum，只能使用小于，不能使用大于
例如：
查询前三条：select * from emp where rownum<=3;
查询4-6条：select * from (select e.*,rownum rn from emp e where rownum<=6) where rn>=4;
集合运算：
并集：union：去掉重复数据；union all：保留重复数据
交集：intersect
差集：minus
多表查询：（首先理解多张表之间的关联关系）
笛卡尔积的问题：多表查询缺少连接条件或者连接条件无效；为了避免出现此问题，n张表之间至少应该有n-1个连接
多表之间连接方式（符合SQL1999规则的连接方式）
内连接：表A [inner] join 表B on 连接条件
Oracle：select... from 表A,表B where 连接条件
外连接：
左外连接：表A left [outer] join 表B on 连接条件
Oracle：select...from 表A,表B where 表A.xxx=表B.xxx(+)
右外连接：表A right [outer] join 表B on 连接条件
Oracle：select...from 表A,表B where 表A.xxx(+)=表B.xxx
满外连接：表A full [outer] join 表B on 连接条件
自然连接：（根据两张表中相同名字的列做等值连接）
表A natural join 表B
数据库事务：一个数据库事务包含一组操作，在这个事务中的所有操作要么全部成功，要么全部失败
事务特性：（ACID）原子性、一致性、隔离性、持久性
事务在执行第一个DML语句的时候开启
事务的操作：
提交：就是将数据永久的保存到数据库中
手动提交：commit
自动提交：执行DDL、DCL，正常退出数据库（exit）
回滚：撤销之前的操作
手动回滚：rollback
自动回滚：非正常退出数据库，直接关闭窗口，关机，断电
设置保存点：savepoint 保存点名
回滚到指定保存点：rollback to 保存点名
多个事务访问数据库时，可能会产生并发问题：
脏读：两个事务T1、T2，T1读取了已经被T2更新但没被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
不可重复度：两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段，值就不
同了
幻读：两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取
同一个表，就会多出几行
为了解决并发问题，提供了事务的隔离机制：
事务的机制：读未提交数据；读已提交数据；可重复读；串行化
Oracle支持两种：读已提交数据；串行化；默认是读已提交数据
MySQL支持四种：默认是可重复读
DML：数据操作语言：insert、delete、update
插入：
给部分列插入数据：insert into 表名 (列名列表) vaules (值的列表);注意：值列表的顺序应该和列名列表的顺
序保持一致
给所有列插入数据：insert into 表名 values (值列表);注意：值列表的顺序应该和表中列的顺序保持一致
从其他表中拷贝数据：insert into 表名 (列名列表) select...from...查询语句中查询列的顺序应该和列名列表
顺序保持一致
删除：
delete from 表名 where 过滤条件;注意：删除一般有过滤条件，如果没有，就是删除数据库表中的所有数据
更新：
update 表名 set 列名=值,列名1=值1 where 过滤条件;注意：更新一般有过滤条件，如果没有，就是更新数据库
表中的所有数据
DDL：数据库定义语言：create、drop、alter
创建表：create table 表名 (
列名 列的数据类型 约束
)
删除表：drop table 表名
修改表结构：alter table 表名...
添加列：alter table 表名 add (列名 列的数据类型 约束)
删除列：alter table 表名 drop column 列名
修改列：alter table 表名 modify (列名 数据类型)
列的重命名：alter table 表名 rename column 旧名字 to 新名字
表的重命名：rename 旧名字 to 新名字
查看表结构：desc 表名;或者describe 表名;
视图：（一种虚表，向用户提供基表（已有表）数据的另一种形式）
优点：简化复杂查询；限制数据访问；同样的数据，可以有不同的显示方式
注意：操作视图本质上还是操作的基表，所以说视图并不能提高查询效率
创建视图：create [or replace] view 视图名 as select查询语句
只读视图（只能执行select,不能执行DML语言）：create view 视图名 as 查询语句 with read only
查询视图：select...from 视图名 where...
删除视图：drop view 视图名
序列：可以为不同的用户产生唯一的数值，主要用于生成主键
创建序列：create sequence 序列名
[start with a]---从a开始
[increment by n]---每次增加n
[minvalue x]---最小值为x
[maxvalue y]--最大值为y
[cycle|nocycle]---是否循环（一般不会使用循环）
[cache m|nocache]---缓存
获取序列中的数值：
nextval---获取序列中的下一个值
currval---获取序列的当前值
序列名.nextval---自动递增：创建完序列后是没有值的，需要使用nextval获取下一个值后才有值，才可以使用
currval
删除序列：drop sequence 序列名
修改序列：alter sequence 序列名
修改序列的增量，最大值，最小值，循环选项，或是否装入内存：
* 必须是序列的拥有者或对序列有alter权限
* 只有将来的序列值会被改变
* 改变序列的初始值只能通过删除序列之后重建序列的方式实现
索引：
* 索引类似于图书的目录，是为了提高查询的效率
* 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
* 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
* 索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引，用户不用
* 在
查询语句中指定使用哪个索引
* 在删除一个表时，所有基于该表的索引会自动被删除
* 通过指针加速Oracle查询速度
* 通过快速定位数据的方法，减少磁盘I/O
创建索引：
自动创建：在定义primary key 或 unique约束后系统自动在相应的列上创建唯一性索引
手动创建：用户可以在其他列上创建非唯一的索引，以加速查询
可以在表的一个列或多个列创建索引：create index 索引名 on 表名(列名1,列名2)
创建索引的条件：
* 列中数据值分布范围很广
* 列经常在where子句或连接条件中出现
* 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
* 表不常更新
删除索引：drop index 索引名
同义词：使用同义词访问相同的对象：方便访问其他用户的对象；缩短对象名字的长度
给一个数据库对象创建一个同义词，就可以使用这个同义词操作数据库对象
创建同义词：create synonym 同义词名 for 数据库对象
删除同义词：drop synonym 同义词名
PLSQL编程：是对Oracle中SQL的过程化扩展；添加了分支、循环语句
语法：declare
声明部分--变量声明、游标声明、异常声明
begin
执行部分
exception
异常处理部分
end;
声明部分：
变量声明：语法：变量名 数据类型
数据类型：
基本类型变量：直接是Oracle中基本数据类型char、varchar2、number
引用类型变量：%type 如：sname emp.ename%type;声明了一个变量sname,变量的类型和emp表中ename的类型一
致
行记录类型变量：%rowtype 如：rw emp%rowtype;声明了一个变量rw,变量代表emp表中的一行
变量赋值：1.直接赋值 变量名:=值 2.将查询结果赋值给变量 使用into select xxx into 变量名
from...where...
分支语句：
case语句：case
when 表达式1 then 结果1
when 表达式2 then 结果2
... ...
else 结果n
end
if语句：
结构一：if...then...
end if;
结构二：if 表达式 then 语句1
else 语句2
end if;
结构三：if 表达式1 then 语句1
elsif 表达式2 then 语句2
else 语句n
end if;
循环语句：
loop循环：loop
循环体
exit when 退出循环的条件
end loop;
for循环：for 循环变量 in a...b
loop
循环体
end loop;
while循环：while 循环条件
loop
循环体
end loop;
游标：光标cursor：游标是内存中的一块区域，主要用来暂存从数据库中查出来的数据
隐式游标：是在执行DML语句或者是单行查询的时候自动创建的
隐式游标的名字默认是SQL
%rowcount：执行DML语句影响的数据的条数
%found：执行成功返回true，执行失败返回false
%notfound：和found相反
%isopen：游标是否打开，执行DML过程中为true，执行完毕为false，我们能看到的总是false
显示游标：是需要我们自己创建的，用来存放多行查询的结果
%rowcount：查询结果条数
%found：最近的一次fetch语句有数据返回true，没有数据返回false
%notfound：和found相反
%isopen：游标是否打开
显示游标的使用步骤：
1、声明游标：cursor 游标名(参数名 参数类型) is 查询语句
2、打开游标：open 游标名(参数值) 注：打开游标的时候执行查询语句并将查询结果保存到游标中
3、循环遍历游标获取每一条数据：fetch语句--类似于java中迭代器的next()方法
loop
fetch 游标名 into 变量--获取单条数据并赋值给变量
exit when 游标名%notfound
end loop;
4、关闭游标：close 游标名
异常：
异常分类：系统定义异常（①预定义异常；②非预定义异常）；自定义异常
异常的处理：exception when 异常名 then 处理异常的代码
自定义异常：
1. 声明异常：异常名 exception
2. 指明什么情况下抛出自定义异常：raise 异常
3. 处理自定义异常：exception when 异常 then 处理异常
存储过程和存储函数：指存储在数据库中供所有用户程序调用的子程序
存储过程和存储函数的区别：只有一个返回值时使用存储函数；没有或者有多个返回值时使用存储过程
创建存储过程：create or replace procedure 过程名(参数列表)
as
变量声明
begin
程序体
end;
调用存储过程：
方式一：使用PLSQL调用存储过程
declare
begin
存储过程名(参数值)
end;
方式二：使用execute直接调用：execute 存储过程名(参数值)
创建存储函数：create or replace function 函数名(参数列表) return 返回值类型
as
变量声明
begin
程序体
return 返回值;
end;
调用存储函数：使用PLSQL：declare
定义变量用来接收返回值
begin
变量:=存储函数(参数值);
end;
数据库触发器：是一个与表相关联的、存储的PLSQL程序。
每当一个特定的DML语句在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列
触发器类型：
语句级触发器---DML语句
在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行
行级触发器（for each row）---行记录
触发语句作用的每一条记录都被触发，在行级触发器中使用old和new伪记录变量，识别值的状态
触发器可用于：
* 数据确认
* 实施复杂的安全性检查
* 做审计，跟踪表上所做的数据操作等
* 数据的备份和同步
创建触发器的语法：
语句级触发器：create or replace trigger 触发器名
before|after
insert|update|delete
on 表名
begin
子程序体
end;
行级触发器：:old 旧值 : new 新值
create or replace trigger 触发器名
before|after
insert|update|delete of 列名
on 表名
for each row
begin
子程序体
end;
数据库字典：
dictionary：查看所有的数据库字典
user_objects：当前用户的所有对象
user_tables：当前用户的所有表
user_tab_columns：当前用户创建的所有的列
user_constraints：当前用户创建的所有约束
user_views：当前用户创建的所有视图
user_sequences：当前用户创建的所有序列
user_procedures：当前用户所有的过程，包括存储过程、存储函数和触发器
导出：exp 用户名/密码@ip:端口号/数据库名 file=导出文件的路径.dmp tables=要导出的表
如果需要导出用户的所有内容：exp 用户名/密码@ip:端口号/数据库名 file=导出文件的路径
导入：imp 用户名/密码@ip:端口号/数据库名 file=要导入的文件路径.dmp tables=要导入的表 fromuser=原用
户 touser=导入到的用户
导入导出命令直接在dos命令窗口下执行即可
扫一扫
11-25
1703
1703
05-06
378
378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
