导图社区 ORACLE
- 292
- 12
- 0
- 举报
ORACLE
ORACLE思维导图，包括：Oracle入门使用、数据库表操作、数据操作、CRUD视图、序列(sequence)。
编辑于2022-07-16 11:12:18- 触发器
- 数据操作
- 数据库表…
- 相似推荐
- 大纲
ORACLE
Oracle入门使用
客户端工具
SQL*Plus客户端
运行→sqlplusw
SQL*Plus命令行
cmd→sqlplus /nolog
conn 用户名/密码@连接字符串 as 连接身份
sysdba：数据库管理员
打开关闭数据库服务器
备份数据库
恢复数据库
日志归档
会话限制
管理功能
创建数据库
注意：sys用户必须使用sysdba身份登录，system用户可以使用普通身份登录
sysoper：数据库操作员
打开关闭数据库
备份数据库
恢复数据库
日志归档
会话限制
normal：普通用户
默认身份
conn scott/tiger@ORCL
conn sys/sys@ORCL as SYSDBA
cmd→sqlplus
PL/SQL Developer
连接数据库配置向导
启动Net Configuration Assistant
Oracle HOME/network/ADMIN/tnsnames.ora
常见的Oracle服务
OracleServiceORCL
oracle的数据库启动的基础，只有该服务启动，数据库才可以使用
Oracle10g_home1TnSListener
该服务为客户端提供监听服务，只有该服务启动，客户端才能连接服务器
Oracle用户管理
1.在Oracle中不轻易创建数据库实例
2.使用不同的用户区分访问表的权限
3.创建用户：CREATE USER 用户名 IDENTIFIED BY 密码 ACCOUNT LOCK|UNLOCK
LOCK和UNLOCK是指用户是否处于锁定状态，处于锁定状态的用户无法登录数据库服务器
例如：create user fankai identified by root account unlock;
4.用户创建成功后还不能登录数据库服务器。如果要登录数据库服务器，必须具有create session权限
系统权限
create session
create table
...
数据库对象权限
对表的CRUD操作
角色(ROLE)
角色：若干个权限系统的集合
connect：主要应用在临时用户，不需要建表的用户，该角色能够连接数据库服务器
RESOURCE：该角色可以创建表、序列、存储过程、触发器、索引等
DBA：该角色拥有数据库的所有权限
注意：一般情况下，普通用户拥有connect和resource角色即可
5.授权：GRANT 角色|权限 TO 用户（角色）
例如：GRANT connect TO fankai;
对象权限
alert：允许使用者更改表或序列的定义
execute：允许使用者执行存储过程
index：允许使用者在表上建立索引
insert：允许使用者插入数据
references：允许使用者建立外键
select：允许使用者查询表、视图、序列等信息
update：允许使用者修改数据
示例：grant select on scott.emp to fankai;（允许fankai可以查询scott用户的emp表）
撤销授权：revoke select on scott.emp from fankai;
6.撤销授权：REVOKE 角色|权限 FROM 用户（角色）
例如：REVOKE connect FROM fankai;
7.修改用户
修改用户密码：ALTER USER 用户名 IDENTIFIED BY 新密码
修改用户的锁定状态：ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
数据的导入与导出
导出:exp
导入:imp
数据库表操作
Oracle中SQL语句的组成
DDL：数据定义语言
create
alter
DROP
...
DML：数据操纵语言
insert
update
delete
select
...
TCL：事务控制语言
commit
savepoint
rollback
DCL：数据控制语言
GRANT
REVOKE
Oracle数据类型
文本型
varchar2(n)：存放变长的字符串，长度为n字节，n最大可以到4000字节
nvarchar2(n)：存放变长的Unicode字符串，长度为n字节，最大为4000字节
char(n)：存放固定长度的字符串，长度为n字节，最大为2000字节
nchar(n)：存放固定长度的Unicode字符串，长度为n字节，最大为2000字节
数值型
number(n,[m])：可存放数值型数据，总长度为n位数（默认38位）；n最多38为数；此外number可以指定m位小数。
日期时间型
date：存放日期时间型，长度为7个字节
使用sysdate系统函数可以获取系统当前日期和时间
select sysdate from dual
dual为临时表，为了配合查询语句的完整性而设
创建表
创建表语句
createtable t_student( stuid number(10) notnull, stuname nvarchar2(20) notnull, age number(2) notnull, address nvarchar2(200), codenum nvarchar2(18) ); createtable t_score( scoreid number(10) primarykey, score number(3,1) notnull, stuid number(10) );
主键约束：alter table t_student add constraint PK_t_student primary key(stuid);
检查约束：alter table t_student add constraint ck_t_student_age check(age>18 and age<30);
默认约束：alter table t_student modify (address nvarchar2(200) default 'China');
唯一约束：alter table t_student add constraint un_t_student_codenum unique(codenum);
外键约束：alter table t_score add constraint fk_t_score_t_student foreign key(stuid) references t_student(stuid);
删除约束：alter table t_score drop constraint fk_t_score_t_student;
修改表
添加列：alter table t_student add(sex nvarchar2(2));
修改列：alter table t_student modify (codenum nvarchar2(20));
删除列：alter table t_student drop column sex;
重命名列：alter table t_student rename column age to nianling;
重命名表：rename t_student to t_xuesheng;
数据操作
查询所有记录： select deptno,dname,loc from dept;
Where条件查询：select deptno,dname,loc from dept where deptno = 10;
比较运算符
=：等于
>：大于
>=：大于等于
<：小于
<=：小于等于
<>或!=：不等于
between... and...
in
like
%
_
is null
逻辑运算符
and
or
not
orderby：根据入职时间排序：select * from emp order by hiredate desc;
DESC：降序排列
ASC：升序排列（默认）
函数
字符函数
LOWER：将字符转换为小写
UPPER：将字符转换为大写
INITCAP：首字符大写，其他字符小写
CONCAT：连接字符串
SUBSTR（column,startIndex,num）
column：列名
startIndex：开始的索引
num：从开始的索引起，获取几个字符
获取前三个字符：select ename,substr(ename,0,3) from emp
获取后两个字符：select ename,substr(ename,-2,2) from emp;
LENGTH：获取字符串长度
TRIM：去掉字符右边或左边的空格
select trim(ename),ltrim(ename),rtrim(ename) from emp;
LTRIM：去掉字符左边的空格
RTRIM：去掉字符右边的空格
select lower(ename),upper(ename),length(ename),initcap(ename),concat('你好',ename) from emp;
数值函数
ROUND（num，n）：将列或表达式所表示的数值四舍五入到小数点后第n位
select round('49.255',1),round('49.255',2),round('49.255',-1) from dual;
TRUNC（num，n）：将列或表达式的数值截取到小数点后第n位
select trunc('49.255',1),trunc('49.255',2),trunc('49.255',-1) from dual;
MOD(m,n)：m除以n以后的余数
select mod(3,2) from dual;
日期函数
sysdate：获取当前系统日期及时间
select to_char(sysdate,'yyyy-MM-dd hh:mm:ss') from dual;
用户入职到现在的星期数：select ename,round((sysdate-hiredate)/7,0) as weeks from emp
MONTHS_BETWEEN（date1，date2）：返回date1和date2之间的月份数量，结果为正数或负数
select months_between(sysdate,to_date('2011-01-01','yyyy-MM-dd')) from dual;
add_MONTHS(date,n)：向date加上n个月，n必须是正整数
select add_months(sysdate,2) from dual;
next_day(date,char)：求出date之后一周内某天char的日期，char是一个有效表示星期几的数字或字符串
select next_day(sysdate,7) from dual;
last_day(date)：求出date所在月的最后一天
select last_day(sysdate) from dual;
数据类型转换函数
to_char(date|number,[fmt])：把日期或数值型按照模式fmt转换为变长字符串
转换日期：select to_char(sysdate,'yyyy-MM-dd HH:mm:ss day d q') from dual;
yyyy：年
MM：月
dd：日
HH：小时
mm：分钟
ss：秒
day：星期的英文表示
d：星期的数字表示
q：季度
转换数值： select to_char('89234334.239','L99,999,999.99') from dual;
L：本地货币符号
9：一个有效位
,：千分位
.：小数点
更多实例：
获取一个日期所在的星期X：select '星期' || (to_number(to_char(sysdate,'d')) -1) from dual;
获取月份：select to_char(sysdate,'MM') from dual;
获取天：select to_char(sysdate,'dd') from dual;
获取1981年12月的记录：select * from emp where to_char(hiredate,'MM') = '12' and to_char(hiredate,'yyyy') = '1981';
to_number(char)：把一个由数字组成的字符串转换为数值
select to_number('12312') from dual;
to_date(char,[fmt])：把一个表示日期的字符串按照日期模式fmt转换为日期
select * from emp where hiredate = to_date('1982/1/23','yyyy/MM/dd');
其他函数
nvl：显示当一个值为null时的默认值
select ename,nvl(mgr,'0') from emp;
聚合函数
avg
count
max
min
sum
分组查询
统计每个部门的平均销售额：select avg(sal),deptno from emp where deptno is not null group by deptno;
显示平均销售额在2100以上的部门：select avg(sal),deptno from emp where deptno is not null group by deptno having avg(sal) > 2100;
CRUD
insert
update
delete
select
等值连接查询
输出每人所在的部门及部门所在地：select ename,dname,loc from emp,dept where emp.deptno = dept.deptno;
查询deptno为10的员工及部门所在地：select ename,dname,loc from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;
非等值查询
根据员工的销售额来查询工资等级： select ename,grade,sal from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;
外部连接
显示员工对应的部门，并显示所有部门：select ename,dept.dname,emp.empno,dept.deptno from emp,dept where emp.deptno(+) = dept.deptno;
显示员工对应的部门，并显示所有的员工：select ename,dept.dname,emp.empno,dept.deptno from emp,dept where emp.deptno = dept.deptno(+);
注意
外部连接运算符(+)只能放在连接条件表达式的一侧
外部连接条件中不能使用in操作符，也不能用or运算符与其他运算符相连
标准连接查询
内连接：select ename,dname from emp inner join dept on emp.deptno = dept.deptno;
外连接
左外连接： select ename,dname from emp left join dept on emp.deptno = dept.deptno;
右外连接：select ename,dname from emp right join dept on emp.deptno = dept.deptno;
子查询
显示比id为7655销售额高的记录：select * from emp where sal > (select sal from emp where empno = 7566);
显示和ID为7369相同工作并且工资大于7876的员工信息：select ename from emp where job = (select job from emp where empno = 7369) and sal > (select sal from emp where empno = 7876);
显示工资最低的员工信息：select * from emp where sal = (select min(sal) from emp);
子查询使用原则：
1.子查询要使用括号括起来
2.将子查询放在比较运算符的右边
3.不要在子查询中使用order by字句，select语句中只能有一个order by字句，并且它只能是主select语句的最后一个字句
替代变量
select * from emp where deptno = &deptno;
字符串替代变量需要使用单引号引起来： select * from emp where job = '&job';
替代变量可以出现SQL语句的地方
WHERE条件
order by字句
列表达式
完整的select语句
示例：select ename,&column from emp where &where;
select ename,&column from emp where &where order by ℴ
&sql;
视图
视图的优点
1.限制对数据库的访问，因为视图可以有选择性的显示数据库的某一部分
2.通过简单的查询便能从复杂的查询的到结果
3.维护数据的独立性，尤其对于用户和应用程序来说，视图可以从多个表中检索数据
4.根据用户群组的不同要求，为他们提供数据访问
创建视图
赋予创建视图的权限：grant create view to scott;
创建视图：create view myview1 as select * from emp where deptno = 10;
注意
定义视图的查询可以使用更复杂的select语句，包含连接，分组，子查询
定义视图的查询中不能包含order by语句
删除视图：drop view myview1;
更多示例：
1.在视图的内部使用列的别名： create view myview as select empno as id,ename as name,job from emp;
2.在创建视图是指定列的别名：create view myview(maxsal,minsal,avgsal) as select max(sal),min(sal),avg(sal) from emp;
查询视图的定义：select view_name,text from user_views;
重建视图：create or replace view myview as select * from emp;
优点：不用删除现有视图
序列(sequence)
特点
序列产生器可以为表中的记录自动产生序列值
序列是由用户创建数据库对象并且可以被多个用户共享
序列的典型应用是产生主键值，用于标示记录的唯一性
序列产生及存储和表无关，因此相同的序列可以用于多个表
创建序列：create sequence id_seq increment by 1 start with 1 maxvalue 999999999 nocache nocycle;
increment by：产生序列的间隔
start width：开始的序列值
maxvalue：最大的序列值
nocache：指定的序列值被Oracle服务器预先分配并存储在内存中。nocache表示不预先分配并存储，默认值，如果预先存储在使用cache n n表示存储的数量
nocycle：指定序列值是否循环产生，如果该序列作为主键，则要设定为nocycle，表示不循环产生。循环产生则使用cycle参数
删除序列：drop sequence id_seq;
获取序列信息：select sequence_name,max_value,min_value,increment_by,last_number from user_sequences;
sequence_name：序列名称
max_value：最大的值
min_value：最小的值
increment_by：增长量
last_number：使用的或缓存的最后一个序列号，一般大于缓冲区中的最后一个值
nextval和currval伪列
nextval用于获取序列中下一个有效的序列值：select id_seq.nextval from dual;
currval用于获取序列中当前的有效序列值，在使用之前必须先使用nextval获取序列值：select id_seq.currval from dual;
可用使用的情况
1.select语句的select列表中，不包含子查询
2.insert语句的values子句中
3.update的set子句中
序列的使用：insert into t_student(stuid,stuname,nianling,address) values(id_seq.nextval,'tom','23','china');
序列的修改：alter sequence id_seq increment by 1 maxvalue 999999999 nocache nocycle;
注意
1.修改序列不能修改start with项
2.maxvalue不能比原有的值小
索引
使用索引可以加快对数据的查询
索引可以提供对表中记录直接快速的访问，索引的目的就是通过指针快速定位数据的方法有效的减少磁盘的IO操作。
Oracle服务器自动使用和维护索引，一旦创建了索引就不需要人工干预
创建索引的方式
自动：当在表中定义了primary key和unique约束，Oracle则会自动建立索引，索引名称和约束名称相同。
手动创建：用户可以在列上创建非唯一索引（用户可以创建唯一索引，但不推荐，唯一索引应该由unique约束来自动创建）
创建索引： create index emp_ename_index on emp(ename);
emp_ename_index：索引名称
emp：表名
ename：列名
适合创建索引的情况
1.经常用于WHERE子句或作为连接条件的列
2.所含数据值范围比较大的列
3.含有大量空值的列
4.经常同时用于一个where子句或连接条件的两个或多个列
5.绝大多数情况下只查询出总记录的2%~4%的表
不适合创建索引的情况
1.表数据量很小
2.列很少在查询中作为条件
3.频繁更新的表
4.绝大多数情况下查询的数据量大于总记录的2%~4%的表
查询索引：select * from user_indexes;
删除索引：drop index emp_ename_index;
PL/SQL
PL/SQL块的组成部分
声明部分：该部分包含类变量和常量的定义，以及变量和常量的初始值定义，这部分由关键字declare开始，如果PL/SQL块中不需要声明变量或常量，该部分可以省略
执行部分：执行部分是PL/SQL的执行指令部分，由关键字begin开始，关键字end结尾。所有可执行的SQL语句都放在这一部分，该部分不能省略，end关键字后面使用分号结尾
异常处理部分：该部分是可选的，该部分使用exception关键字将可执行部分分为两个部分，一部分是正常运行的程序，一旦出现异常就跳转到异常部分执行
PL/SQL中符号的声明
:=赋值运算符
|| 字符串连接
--单行注释
/**/多行注释
..范围操作符，例如1..5标示从1到5
算术运算符
+
-
*
/
** 求幂操作 比如3**2=9
关系运算符
>
<
<=
>=
=
<>
!=
逻辑运算符
and
or
not
变量声明
语法：变量名 数据类型[:=初始值]
声明变量： declare name varchar(20) := 'alex'; begin dbms_output.put_line(name); end; /
声明一个名称为name数据类型为varchar的变量，默认值为alex
dbms_output.put_line()用于将变量输出到控制台上，如果没有输出可以通过set serveroutput on设置输出
/用于执行上面的语句
变量的赋值
declare p_v1 int; begin p_v1 := 10; select 123 into p_v1 from dual; dbms_output.put_line(p_v1); end; /
通过:=操作符
通过select ....into 变量名 from..来赋值
注意：查询的结果只能是一行，如果多行或没有行，则会引发异常
声明常量
declare c_pi constant float := 3.14; r int := 4; area float; begin area := c_pi * r * r; dbms_output.put_line('AREA:' || area); end; /
通过constant关键字来声明常量
特殊数据类型
%ROWTYPE
declare emprow emp%ROWTYPE; begin select * into emprow from emp where empno=7934; dbms_output.put_line(emprow.ename); end; /
该数据类型表示一条记录，相当于Java中的一个对象，可以共通过"."点操作符来访问记录中的属性
%TYPE
declare myname emp.ename%TYPE; /*使用emp表中的ename列的数据类型*/ begin select ename into myname from emp where empno = 8888; dbms_output.put_line(myname); end; /
引用某个变量或数据库的列作为数据类型来声明一个变量
条件控制和循环结构
条件分支语句
declare myval number(10) := 10; begin if myval > 10 then dbms_output.put_line('>10'); elsif myval = 10 then dbms_output.put_line('=10'); else dbms_output.put_line(' end if; end; /
循环结构
loop ... end loop循环
declare temp number(3) := 0; total number(5) := 0; begin loop temp := temp + 1; total := total + temp; if temp >= 100 then exit; end if; /*或者采用exit when temp >= 100;*/ end loop; dbms_output.put_line('total:'||to_char(total)); end; /
while循环
declare temp number(3) := 0; total number(5) := 0; begin while temploop temp := temp + 1; total := total + temp; endloop; dbms_output.put_line('total' || to_char(total)); end; /
for循环
declare temp number(3) := 0; total number(5) := 0; begin for temp in 0..100 loop total := total + temp; end loop; dbms_output.put_line('total:' || to_char(total)); end; /
注意：使用exit退出循环
存储过程
普通存储过程
create or replace procedure my_proc9 is begin dbms_output.put_line('hello,Oracle'); end; /
带传入参数的存储过程
create or replace procedure kaishengit_proc1(temp_id in emp.empno%TYPE) is name emp.ename%TYPE; begin select ename into name from emp where emp.empno = temp_id; dbms_output.put_line(name); end; / --执行 execute kaishengit_proc1(9999);
带返回值得存储过程
create or replace procedure kaishengit_proc33(temp_id in emp.empno%TYPE,temp_name out emp.ename%TYPE) is name emp.ename%TYPE; begin select ename into name from emp where emp.empno = temp_id; temp_name := name; end; / --调用-- declare name emp.ename%TYPE; begin kaishengit_proc2(9999,name); dbms_output.put_line(name); end; /
执行存储过程execute 存储过程名称(参数列表):execute my_proc;
删除存储过程：drop procedure 存储过程名称
触发器
insert触发器
create or replace trigger save_before_banji after insert on banji for each row begin dbms_output.put_line('刚插入的id为：' || to_char(:new.id)); end; /
delete触发器
--删除班级 级联删除班级对应的学生 create or replace trigger del_banji_id after delete on banji for each row begin delete from student where b_id = :old.id; end; /
update触发器
create or replace trigger update_before_banji after update on banji for each row begin update student set b_id = :new.id where b_id = :old.id; end; /
Oracle and Java
驱动：oracle.jdbc.driver.OracleDriver
连接字符串：jdbc:oracle:thin:@192.168.0.77:1521:ORCL
在SQL中使用序列：String sql = "insert into dept(deptno,dname,loc) values(id_seq.nextval,'dev','jiaozuo')";
hibernate中使用Oracle
hibernate.cfg.xml
propertyname="connection.driver_class">oracle.jdbc.driver.OracleDriverproperty> propertyname="connection.url">jdbc:oracle:thin:@192.168.0.77:1521:ORCLproperty> propertyname="connection.username">scottproperty> propertyname="connection.password">tigerproperty> propertyname="dialect">org.hibernate.dialect.Oracle10gDialectproperty>
xxx.hbm.xml
classname="Dept"table="dept"> idname="deptno"> "sequence"> "sequence">id_seq id> propertyname="dname">property> propertyname="loc">property> class>