目录
序言
本文章内容为同步Oracle笔记，方便在线查看，由问题还请指出，随时更新。
第一章
创建用户
一个实例只有一个SID号，
dba数据语法格式：
- 创建用户：
sys，system（尽这两个用户有权限）
Create user 用户名 identified by “密码” Default tablespace 用户表空间 Temporary tablespace 临时表空间
角色与权限
角色（一组权限的集合）：
分类：1.系统权限
Create table （创建表）
2.对象权限（自己创建的东西都是对象）
对数据进行操作（增insert、删delete、改、查）
查：select * from 用户名.表
DML 数据操作语言
常见角色：
- 连接（connect）
- （resource）
- 管理员权限（dba）
sys和system可给用户分配角色、权限
分配角色：grant 角色 1,角色2 to 用户名
回收角色：revoke 角色1，角色2 from 用户名
分配系统权限：grant 系统权限 to 用户名
回收系统权限：revoke 系统权限 from 用户名
分配对象权限：grant 对象权限 on 表 to 用户名（grant 对象权限 on 用户名1.表 to 用户名）
回收对象权限：revoke 对象权限 on 表 from 用户名
权限的传递：分配对象权限 + 用户名 with grant option
回收传递权限：回收对象权限 + 用户名 with grant option
Eg：分配所有权限给一个用户： grant all on 表 to 用户名
修改密码：在oracle 数据中用户只能修改自己的密码 而管理员可以修改任何人的密码
语法：alter user 用户名 identified by “新密码”
账号加锁（超级管理员）：alter user 用户名 account lock
解锁账号（超级管理员）：alter user 用户名 account unlock
删除账号（超级管理员）：drop user 用户名（该用户只能为空用户）
drop user 用户名 cascade（删除非空用户）
事务
事务（保证的数据的 完整性、一致性）
- 显性事务（默认的。没有提交前其他用户看不到，只有提交后才能看到，没有提交前只在本会话期间可见）
提交指令：commit
回滚指令：rollback
- 隐性事务（当进行ddl和dcl时内部隐藏了一个commit命令）
- 自动提交事务（不提倡，不安全）
设置自动提交：set.autocommit on|off
（一条sql语句只能选择一种，一般为显性事务）
Eg：1.创建用户 tiger 密码 123 分配角色 connect 和 resource 系统权限create user
SQL（一种语言，符合ANSI，能操作数据库）
增：create table 表名（字段1 数据类型[约束], 字段2 数据类型[约束]）
删：delete from 表名
改：update
查：select
备注：中括号的内容可以省略
数据类型：int ，bigint（长整型），char，varchar2（存放字符串，默认50字符最大3000），number（m，n）（放小数，m=小数+整数，n=小数），boolean布尔型，
date（日期）。
约束
分为字段级和表级
primary key 主键（联合主键，逻辑主键，表级约束），标识一条记录，设在字段（字段唯
且不为空）中。
Unique 唯一约束 允许字段为空
外键约束 B数据表中的主键在A数据表中为普通字段,则这个字段为B的外键或B关联A的外键。A（主、--父表---） B（从、---子表---）
Not null 非空（字段级约束）
Null 空（字段级约束）
检查约束 check
Default 默认约束
注：一个字段可以拥有多个约束
SQL.PULS（一个环境，是oracle的一个属性，不能操作数据库）
Save保存文件 路径：文件
Conn 连接数据库 用户/密码
Discon 断开数据库
查表信息列表
查字段信息
查约束信息
Data数据
Metadata 元数据
数据表加主键：
alter table表名 add constraint 主键名（pk_） primary key （字段）
主外键：名称可以不一样，数据类型必须一样（在父表中是主键，在子表中是字段）
alter table 子表 add constraint 外键名（fk_子表_父表） foreign key（子表中的字段） reference 父表（父表中的字段）
唯一约束：
Alter table 表名 add constraint 约束名（uq_） unique（字段）
检查约束：
Alter table 表名 add constraint 约束名 check（表达示）
删除约束：
Alter table 表名 drop constraint 约束名
删除数据表：
drop table 表名
表空间
创建表格空间（只能用sys用户建立）
创建用户（数据）表空间：
create tablespace 表空间名 datefile ‘路径+文件名1.dbf’ size 值1，……. ‘路径+文件名n.size 值n
创建临时表空间：
create temporary tablespace 表空间名 tempfile‘路径+文件名1.dbf’ size 值1，……. ‘路径+文件名n.size 值n
索引表空间：
语法与数据表空间一样，用名字来区分。
修改表空间大小
修改（用户）数据表空间：
Alter database datafile ‘路径+文件名.dbf’resize值
修改临时表空间：
Alter database tempfile ‘路径+文件名.dbf’resize值
追加（用户）数据表空间大小：
Alter database 表空间名 add datafile ‘路径+文件名.dbf’size 值
追加临时表空间：
Alter database 表空间名 add tempfile ‘路径+文件名.dbf’size 值
删除表空间（sys权限操作）：
删除空表空间：Drop tablespace 表空间
删除非空表空间：drop tablespace 表空间 including contents
Drop tablespace 表空间 includeing contents and datafiles（sql输入攻击数据库）
完整建表格式：
Create table 表名（
字段1 数据类型 【约束】，
字段2 数据类型 【约束】
）
【Tablespace 表空间】
命名规范：字母、数字、下划线、并且只能以字母开头，所有对象命名方式应该以驼峰方式命名（首字母小写其他大写eg：studentName）
第二章
增删改查
新增操作：
Insert into 表名（字段1,字段2……字段n） values（值1,值2,……值n）
删除操作：
Delete from 表名 [where 条件表达式] 删除符合条件的数据
Delete from 表名 删除当前数据表中的所有记录
更新操作：
Update 表名 set 字段1=值1,字段2=值2……字段n=值n [where 条件表达式]
查找操作：
Select 字段1,字段2,……字段n from 表1,表2,……表n [where 条件表达式] [group by 字段1,字段2……字段n having 条件表达式] [order by字段1，字段2……字段n asc|desc]
备注：group by A havingB A分组按照条件B
Order by 排序 asc升序 desc降序
Select * from 表名 查询这个表中的全部记录
序列
1.序列是独立对象。2.可产生连续且唯一的编号。3.一张表只用一个序列
创建序列：
Create sequence 序列名
Increment by 值----增量
Start with 值----初始值
Maxvalue 值-----最大值
Minvalue 值-----最小值
Cycle|no cycle------是否循环
Cache 值|no cache-----是否缓存
伪列
Nextval-----下一个值
Currval----当前值
Select 序列名.nextval from dual 查询虚拟表中序列的下一个值
备注：第一次使用currval之前必须进行一次nextval取值。
带序列向数据表中插入数据：
Insert into 表名（字段1，……） values（序列名.nextval……）
Sysdate （系统当前时间）
子表中的记录在主表中有定有，在主表中有的在子表中不一定有。
外键值被引用了，在主表中不能删除。
修改表结构
增加一列：
Alter table 表名 add 字段 数据类型 [default 表达式]
增加多列：
Alter table 表名 add （字段1 类型 [约束],字段2 类型 [约束]）
修改列：
修改字段数据类型、默认值：
Alter table 表名 modify 字段 数据类型 [default表达式]
删除字段：
Alter table 表名 drop column 字段
特殊字符
||----表示字符串连接
%----匹配一个多个或零个字符
_-----只能匹配一个字符
>-------大于
< ---------小于
<>------不等于
= ------等于
a>=18 and a<=28 a between 18 and 28 a=18 or 19 ……or 28
in-----或者 in（“张三”，“李四”）
all -----全部所有
any----任何一个
操作函数
nvl （判断字段值是否为空）
nvl（表达式1，表达式2）当表达式1的值为非空时整个nvl的值等于表达式1的值否则整个nvl的值等于表达式2的值。
Count（求记录数）
Select count （*） from emp 在emp表中查询出所有的记录数
Select count （ename）from emp 在emp表按照ename列的数来查询统计数
Max（最大值），min（最小值）
Select max（sal） from emp 从emp中找出工资最大的金额
Select * from emp where sal=max（sal） 在emp表中找到工资最大金额的记录
Avg（平均值）
Select avg（sal）from emp 计算emp表中工资的算术平均值
Sum（求和）
Select sum（sal） from emp 在emp中把所有工资做和
Distinct（去掉重复记录）
Select distinct ename from emp 按姓名合并、删除重复记录
Union（并为）
Select ……
Union
Select……
将查询语句1和查询语句2的结果取并集并且删掉重复记录
Union all 只合并不删除重复记录
Intersect（求交集）
Select……
Intersect
Select……
将查询语句1和查询语句2的结果取交集。
Minus（补集）
除去a和b重复的部分只留a的记录。（在a中去掉与b重复的部分）
字符串函数
Upper（）
Upper 表达式 将表达式中的字符全部变为大写
Lower（）
Lower 表达式 将表达式中的字符全部变为小写
Length（）
Length 表达式 取表达式中字符串的长度
Substr（）
Substr （表达式，m，n）取子串 在表达式中第m个位置取n个字符长度
Concat
concat（表达式1，表达式2）连接字符串
initcap
initcap （表达式） 将表达式的首字母变为大写
replace（替代）
replace（表达1，表达式2，表达式3）用表达式3代替表达式2在表达1出现的字符串
instr
instr（表达式1，表达式2） 求表达式2在表达式1中的位置若未找到返回值为0（从0下标开始计数）
trim
trim（表达式） 去掉表达两端的空格 ltrim 去掉左端的空格 rtrim 去掉右端的空格
ascii
ascii（字符） 返回字符的ASCII码值
lpad（左填充函数）
lpad（目的字符串，填充后字符串的长度，被填充的字符串） 左填充函数
select lpad （‘adcde’，10，‘F’）from dual FFFFFabcde
select lpad （‘adcde’，3，‘F’）from dual adc
rpad（右填充）
替代函数不等价于填充函数
备注：Select ename en from emp ==select ename as en from emp ename的小名为en
数学操作函数：
Abs（表达式）
取表达式的绝对值
Mod（表达1，表达式2）
表达式1对表达式2取余
Trunc（表达式，m）
Trunk（表达式） 取整
截取m位的小数（不四舍五入）
Round（表达式，m）
对表达式第m位小数进行四舍五入，
日期函数：
Sysdate（）
取系统当前时间
Add-month（表达式，m）
给表达式加上或减少，m个值
Months_between（表达式1，表达式2）
求表达式1和表达式2的差值，以月为单位返回，不够时自动进1
To_char（表达式，’格式’）
将表达式转换为字符
yyyy------年份用4位表示
mm-------月份2位
dd-----天，日2位
day-----星期几
hh12------12进制表示小时
hh24------24进制表示小时
mi-----分钟
ss-----秒
oracle 默认为日月年
to_date（表达式，‘格式’）
将表达式转换为日期
‘yyyy-mm-dd hh:mi:ss’
属性
Is null （属性）
Eg：ename is null 查询ename的值为空的字段
Eg：ename is not null 查询ename的值不为空的字段
Rownum
Eg：select * from emp where rownum <=3 或者<3 查询前三条记录
Depton in(10,20,30)-----depton的值为10,20或者30 == depton=10 or depton=20 or depton=30
Like ------模糊查询 like ‘%王%’
字符串区分大小写，命令不区分大小写
字符串：
第三章
高级查询
笛卡尔积（将表1的序列数乘表2的序列数）
多表查询：需要利用多张数据表通过一定条件将表连接起来进行查询
Eg：select *from emp，dept where emp.deptno=dept.id
从emp表和dept表中查询出符合emp表中deptno的值等于dept表中id的值
内部连接：
Select 表1.*,表2.* from 表1 inner join
表2 on a.字段=b.字段
内连接值返回满足符合条件的字段
等值连接：
Select *from emp
外连接：
不仅返回满足条件的记录也返回不满足条件的记录
左连接：
Select a.*,b.* from 表1（左表） a left join 表2（右表） b on a.字段=b.字段
不仅返回满足条件的记录也返回左表不满足条件的记录，右表以空填充
右连接
Select a.*,b.* from 表1 a right join
不仅返回满足条件的记录也返回右表不满足条件的记录，左表以空填充
全连接
Select a.*,b.* from 表1（左表） a full join 表2（右表） b on a.字段=b.字段
上面的返回满足条件的记录，中间放左连接的记录，下面返回右连接的记录
子查询
Select a.*,b.* from （）where a.depton=b.id and a.enam=（）
Select emp.* from emp where emp.deptno =(select id from dept)
Select *from emp where sal>any（select sal from emp where deptno =20）
Select *from emp where sal>all（select sal from emp where deptno =20）
在一个select语句中使用另一个select语句作为它的条件或数据来源
不相关查询
子查询不依赖于主查询，子查询可以独立运行
相关查询：
子查询依赖主查询，子查询不可以独立运行。把主查询作为子查询的条件
Oracle 操作
数据字典
匿名块
将一组sql语句放到一个匿名块中执行。
关键字声明：
Declare
变量声明
Begin
执行部分
End;
在匿名块存储过程函数中每条sql语句结束后必须加英文分号
变量声明：
直接声明：
变量名 数据类型;
（一条sql语句只可以声明一条语句 ）（代表数据表中的一个据的值）
列变量（与某张数据表的某一列的数据类型完全相同）：
变量名 数据表.列%type;(代表数据表中的一列)
行变量：
变量名 数据表%rowtype;(代表数据表中的一行)
变量赋值：
直接赋值：
a：=1; 赋值1给a
交互式赋值：
a:=&a;数值型
a:=’&a’字符串型
查询赋值：
Select 字段1，……字段n into 变量1，……变量n
From 表 where 条件;（每个字段只能返回一条记录）
异常：
No_data_found 没有找到数据
Too_many_rows 返回多条记录异常
Others 其他异常（必须放在所有异常最后）
Dbms_output.put_line（表达式）； 输出
条件语句
If 表达式 then
sql语句
end if；
if 表达式 then
sql语句
else
sql语句
end if；
if表达式 then
sql语句
elsif表达式 then
sql语句
elsif 表达式 then
sql语句
end if
循环语句
Loop
Sql语句
Exit when 表达式；
End loop；
（先执行再判断，不满足退出）
While 表达式
Loop
Sql语句
End loop
（先判断再执行）
For 变量 in 最小值..最大值
Loop
Sql语句
End loop；
不需要声明变量，不需要打开游标，不需要读取记录，不需要判断结束，不需要关闭游标
Case
When 表达式1 then 值1
When 表达式n then 值n
Else
表达式 n+1
End case
Decode（表达式1，条件1，结果1，
表达式1，条件n，结果n，
表达式2，条件2，结果2，
表达式n，条件n，结果n）
批处理文件：一次点击执行多条指令
视图
视图是一个虚拟表只存放select语句，数据来源于数据表
虚拟的数据表称为视图，视图不是用来存放数据的，数据表用于存放数据
视图优点：1.安全性好
2.可重复利用
新建视图：
Create or replace view 视图名
As
Select 语句
若重名覆盖掉原来视图，数据来源于select语句
视图的数据与数据表的数据一一对应。
同义词
创建私有同义词：
Create synonym 同义词名 for 数据表|视图
删除私有同义词：
Drop synonym 同义词（谁创建的谁删除）
创建公有同义词：
Create public synonym 同义词名 for 数据表|视图 （sys权限）
删除公有同义词：
Drop public synonym
游标
游标：内存区域，可以通过指向记录的指针逐行返回记录
初始状态指向第一条记录的上方
创建游标：
Cursor 游标名 is select 语句
应有游标：
open cur；--打开游标
myrow emp%rowtype；--定义行变量
fetch 游标名 into --行变量—将游标下移再读取游标中的值
close cur；--关闭游标
游标名%found---如果游标读取到记录，返回值true否则返回false
游标名%notfound—如果游标没有找到记录返回true否则返回false
游标名%ravcount—游标读取的记录数，打开游标后值为0，每fetch一次加一
游标名%isopen—判断游标是否打开，打开返回true否则返回false
带参数的游标：
Cursor 游标名（变量 数据类型） is select语句where 条件=变量；
带有参数的游标 要先进行交互式赋值，再打开游标
Eg： 变量名为vempno
vempno：=&vempno；
Open e（变量名）
游标中参数的数据类型不需要指明长度。
存储过程
存储过程是一组为实现某个目地的sql语句的集合
存储过程编译以后，就存放在数据库中了。
优点：
统一操作流程
重复利用
安全性高
降低I/O
声明存储过程
Create or replace procedure 存储名（形参列表）
As|is
声明部分；
Begin
执行部分；
异常；
End；
Eg：declare
声明；
Begin
存储过程（实参）；
异常；
End；
实参与形参个数相同，相对应的实参、形参数据类型相同。
参数类型：
In--输入参数（实参给形参传递值）
Out--输出参数（空值传入代值传出）
In out --输入输出参数（非空传入非空传出）
复制表
Crate table 表名 as（ select 语句）
当结果集为空时，只复制表结构，结果集非空时，不仅复制表结构也复制数据。
自定义函数
函数是由一组sql语句集合组成的，在函数中必须要有return语句。
函数有于存储过程相同的优势。
新建函数：
Crate or replace function 函数名（形参列表）
Return 数据类型；
As|is
变量声明部分；
Begin
执行部分；
Return语句；
异常处理；
End；
存储过程没有返回值，函数必须有返回值，不需要有字符长度。
函数调用：
变量=函数（实参列表）；
Java与数据库
在Java中连接数据库：
.jar----架包
ClassesB.java------驱动包
Classforname（”oracle.jdbc.driver.oracledriver”）;---加载驱动
Connection con=DriverManager.getconnection
conn=DriverManager.getConnection("jdbc:mysql5656://127.0.0.1:3306/iepdb?useUnicode=true&characterEncoding=UTF-8","root","123456");
// 指定驱动，指定服务器，指定数据库，设置登录名和密码
触发器
当执行某sql语句时可以触发其他sql语句。
级连删除：先删除子表中的数据，再删除主表中的数据。
触发器是一种特殊的存储器，不接收也不返回任何值，由特定的值触发。没有形参没有return语句，触发器分为：DML触发器，代替触发器。
DML触发器：
Create or replace trigger 触发器名称
Before|after insert|delete|update on 表名
For each row
Declare
变量声明；
Begin
执行部分；
End；
行级触发器 影响一条记录执行一次。有两个伪列， ：new.字段----新值
：old.字段-----旧值
语句级触发器 没有for each row 无论影响多少条记录触发器只执行一次。
触发器不能使用事务不能有提交，有错误会自动回滚。
|
| New | Old |
| Update | √ | √ |
| Delete | ╳ | √ |
| Insert | √ | ╳ |
|
|
|
|
|
|
|
|
代替触发器
主要用于视图
语法：
Create or replace trigger 触发器名
Instead of insert|update|delete on 视图
For each row
Declare
变量；
Begin
执行部分；
End；
用来代替dml触发器操作，因为对视图进行操作，所以可以对多张数据表进行操作。
删除触发器
Drop trigger 触发器名
关闭
Alter trigger 触发器名 disable
打开
Alter trigger 触发器名 enable
。
扫一扫
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
