部分内容摘抄于:https://www.oraclejsq.com/article/010100478.html
https://blog.csdn.net/qq_43270074/article/details/85727108
Oralceo数据库基本操作及用法
[ DDL语句管理表 ]
①创建表空间
语法:
create tablespace 表空间的名称
datafile ‘文件的路径(服务器上)’
size 大小
autoextend on 自动扩展
next 每次扩展的大小
[ 代码:
[ 删除表空间 ]
②创建用户
create user 用户名 (hname)
identified by 密码 (nametest)
default tablespace 表空间的名称 (hname)
[ 给hname授权 ]
grant dba to hname;
[ Oralce中的数据库类型 ]
int ---数值类型，只能存储整数
number ----数值类型，可以存储整数也可以存储浮点数 number(3,1)
varchar2 ----字符类型，可变长字符类型 varchar2(6)
char ---字符类型，定长字符类型 char(6)
date ---日期类型，在oracle中精确到秒，
---该数据类型可以使用java的Date类型接收，接收后只能精确到日。
[ 创建表 ]
创建一个person表
create table person(
pid number(10),
pname varchar2(10)
);
----修改表结构
---添加一列
alter table person add gender number(1);
---修改列类型
alter table person modify gender char(1);
---修改列名称
alter table person rename column gender to sex;
---删除一列
alter table person drop column sex;
----增删改数据
--查询数据
select * from person;
---添加数据
insert into person (pid, pname) values (1, '小明');
commit;
---oracle中除了别名都必须使用单引号。
---修改数据
update person set pname = '小马' where pid = 1;
commit;
---三种删除
drop table person;--删除表结构
---这种在数据量大的情况下效率低
delete from person;--删除表中全部数据
---此中删除数据是先删除表结果，再次创建表
---删除表结构的时候会先把表中所有的索引和约束等删除
---然后再除数据，最后才删除表。
---这种方式在数据库大的情况下删除数据效率高，
---因为它避开了索引和约束对增删改效率的影响。
truncate table person;--删除表中全部数据
----序列：序列是给主键赋值使用的。
---序列默认从1开始自增，每次增加1。
---创建一个序列
----序列：序列是给主键赋值使用的。
---序列默认从1开始自增，每次增加1。
---创建一个序列
create sequence seq_person;
---dual虚表：虚表只是为了补全语法，没有任何意义。
----oracle中查询语句必须有from关键字。
---但是很多函数或方法根本就不属于任何表，我们单一看特性的时候，可以只从虚表中查询。
---查询序列
select seq_person.nextval from dual;
--查询当前值，如果是一个新的序列没有用过，直接查询是报错的。
select seq_person.currval from dual;
---使用序列添加数据
insert into person (pid, pname) values (seq_person.nextval, '小明');
commit;
select * from person;
---SCOTT用户 密码是tiger
---解锁scott用户
alter user scott account unlock;
---解锁密码【此句可以用来重置密码】
alter user scott identified by tiger;
---切换到scott用户下
---emp表典型的树形结构表
select * from emp;
---查询工资高于2000的员工信息
---where要先于select
---除了子查询，其余任何时候条件中都不能出现别名
select s.ename, s.sal sa from emp s where s.sal > 2000;
---查询出员工姓名和工作，在一列上显示。比如：xx的工作为ss。
---说明concat在oracle中只能连接两个字符串
select concat(e.ename, e.job) from emp e
---oracle中连接多个字符串使用||
select e.ename || '的工作为' || e.job "工作信息" from emp e
---字符函数
select upper('yes') from dual;--YES
select lower('YES') from dual;--yes
---数值函数
select round(58.69, -2) from dual;---四舍五入
select trunc(58.69,1) from dual;---直接截取
---日期函数
---日期类型可以直接和数字做算术运算，单位按天来算
---算出emp表中所有员工入职距离现在多少天
select round(sysdate-e.hiredate) from emp e;
---算出emp表中所有员工入职距离现在多少礼拜
select round((sysdate-e.hiredate)/7) from emp e;
---算出emp表中所有员工入职距离现在多少月
select round(months_between(sysdate, e.hiredate)) from emp e;
---算出emp表中所有员工入职距离现在多少年
select round(months_between(sysdate, e.hiredate)/12) from emp e;
---算出明天此时
select sysdate+1 from dual;
----转换函数
to_char--转成字符串
--当前日期转成字符串
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
to_date--转成日期
--把字符串转成日期
select to_date('2019-01-03 16:24:50', 'yyyy-mm-dd hh24:mi:ss') from dual;
---常用的日期转换说明
select to_char(sysdate, 'yyyy') from dual;--2019
select to_char(sysdate, 'year') from dual;--twenty nineteen
select to_char(sysdate, 'mm') from dual;--01
select to_char(sysdate, 'month') from dual;--january
select to_char(sysdate, 'mon') from dual;--jan
select to_char(sysdate, 'dd') from dual;--03
select to_char(sysdate, 'day') from dual;--thursday
select to_char(sysdate, 'dy') from dual;--thu
---把数值转成字符串
select to_char(123456789, '999,999,999.000') from dual;
---通用函数
---注意：null值做任何算术运算结果都是null
---查询出emp表中所有员工的年薪
---nvl(e.comm, 0)当第一个参数为null时，使用第二个参数
select e.sal*12+nvl(e.comm, 0) from emp e;
----条件表达式
---条件表达式是根据条件处理数据。
---条件表达式等值判断案例
---给emp表中所有员工起中文名
select e.ename,
case e.ename
when 'SMITH' then '曹贼'
when 'ALLEN' then '司马老贼'
--else '诸葛小儿'
end 中文名 from emp e;
---条件表达式范围判断案例
---工资超过3000显示有钱人
---工资在1500到3000之间显示中产阶级
---工资低于1500显示穷人
select e.ename,
case
when e.sal>3000 then '有钱人'
when e.sal>1500 then '中产阶级'
else '穷人'
end 工资等级 from emp e;
---以上条件表达式都是mysql和oracle通用
---oracle专用条件表达式【不常用】
select e.ename,
decode(e.ename,
'SMITH', '曹贼',
'ALLEN', '司马老贼',
'诸葛小儿')
中文名 from emp e;
----多行函数【聚合函数】
----聚合函数如果是在group by中使用，是组内聚合。
----聚合函数有一个特性，可以吧多行记录变成一个值。
----是可以改变表结构的一种函数。
select count(1) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
---笛卡尔积
select * from emp, dept;
---等值连接
select * from emp e, dept d where e.deptno=d.deptno;
---内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
---外连接
---查询出所有部门以及部门下所有员工信息
select * from emp e right join dept d on e.deptno = d.deptno;
---oracle中专用的外连接
--查询出所有部门以及部门下所有员工信息
select * from emp e, dept d where e.deptno(+)=d.deptno;
---子查询
----子查询结果当条件使用
---查询出和SCOTT工资一样的员工信息
select * from emp where sal in
(select sal from emp where ename='SCOTT');
----子查询结果当表使用
----查询出每个部门最低工资员工姓名，最低工资，部门名称
---先查询出每个部门的最低工资
select deptno, min(sal) msal from emp group by deptno;
---然后三表联查
select e.ename, d.dname, t.msal from emp e, dept d, (select deptno, min(sal) msal
from emp
group by deptno) t
where e.deptno=d.deptno
and e.deptno=t.deptno
and e.sal=t.msal;
---自连接
---自连接是站在不同的角度把一张表看成多张表。
---查询出员工姓名，员工领导姓名
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr=e2.empno;
---查询出员工姓名，员工部门名称，员工领导姓名，领导部门名称
select e1.ename, d1.dname, e2.ename, d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr=e2.empno
and d1.deptno=e1.deptno
and d2.deptno=e2.deptno;
----作业：查询出员工姓名，员工部门名称，员工工资等级，
-----员工领导姓名，领导部门名称，领导工资等级，
-----要求工资等级显示汉字。
----分组查询
---查询出每个部门平均工资
---只有在group by后面出现的原始列，才能出现在select后面
---如果没有出现在group by后面，要想出现在select后面，必须加聚合函数
---因为聚合函数可以把多行记录变成一个值。
select e.deptno, avg(e.sal) from emp e group by e.deptno;
---查询出平均工资高于2000的部门
select e.deptno, avg(e.sal) asal from emp e group by e.deptno having avg(e.sal)>2000;
---查询出每个部门工资高于1000的员工的平均工资
select e.deptno, avg(e.sal) from emp e where e.sal>1000 group by e.deptno;
---where过滤的分组前原来表的数据
---having是过滤分组后新表的数据
---下面这样写不行
select e.deptno, avg(e.sal) from emp e where avg(e.sal)>1000 group by e.deptno;
-------------------------------
union或者union all
视图__
oracle视图可以理解为数据库中一张虚拟的表，他是通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。查询视图，本质上是对表进行关联查询。
视图的本身是不包含任何数据，只是一个查询结果，当基表的数据发生变化时，视图里面的数据也会跟着发生变化。我们经常在实际开发过程中遇到的视图可以大概分为三种：单表视图、多表关联视图、视图中含有子视图。
[ 视图的作用和优势 ]
既然视图在实际开发过程当中被广泛使用到，它到底有哪些作用和优势呢？
1、使数据简单化：可以将复杂的查询创建成视图，提供给他人使用，他人就不需要去理解其中复杂性的业务关系或逻辑关系。这样对视图的使用人员来说，就简化了数据的，屏蔽了数据的复杂性。
2、表结构设计的补充：系统刚刚开始设计时，大部分程序是直接访问表结构的数据的，但是随着业务的变化、系统的更新等，造成了某些表结构的不适用，这时候去修改表结构对系统的影响太大，开发成本较高，这个时候可以创建视图来对表结构的设计进行补充，降低开发成本。程序可以直接通过查询视图得到想要的数据。
3、增加安全性：视图可以把表中指定的字段展示给用户，而不必把表中所有字段一起展示给用户。在实际开发中，视图经常作为数据的提供方式，设置为只读权限提供给第三方人员进行查询使用。
[ 创建视图 ]
1、OR REPLACE：如果视图已经存在，则替换旧视图。 2、WITH READ ONLY：默认不填的，用户是可以通过视图对基表执行增删改操作，但是有很多在基 表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行 insert 操作，或者基表设置了某些约束，这时候插入视图或者修改视图的值，有可能会报错）， WITH READ ONLY 说明视图是只读视图，不能通过该视图进行增删改操作。但是在现实开发中， 基本上不通过视图对表中的数据进行增删改操作。
[ 栗子 ]
利用学生信息表（stuinfo）、班级表（class）关联创建视图，只提供一些学生基本信息和班级信息（剔除学生一些敏感信息：如身份证，家庭地址等）。
[ 代码如下：
序列
Oracle序列Sequence是用来生成连续的整数数据的对象，它经常用来作为业务中无规则的主键。Oracle序列可以是升序列也可以是降序列。
索引
创建索引:
语法解析：1、UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引，BITMAP表示位图索引。 2、index_name：指定索引名。 3、tabl_name：指定要为哪个表创建索引。 4、column_name：指定要对哪个列创建索引。我们也可以对多列创建索引，这种索引称为组合索引。也可以是函数表达式，这种就是函数索引。
修改索引：
alter index index_old rename to index_new;--重新命名索引
合并索引、重新构造索引：我们索引建好后，经过很长一段时间的使用，索引表中存储的空间会产生一些碎片，导致索引的查询效率会有所下降，这个时候可以合并索引，原理是按照索引规则重新分类存储一下，或者也可以选择删除索引重新构造索引。
删除索引
查看索引
栗子1:
学生信息表（stuinfo）创建的时候就对学号（stuid）设置了主键（PK_STUINFO），当我们学生信息表数据量大的情况下，我们明显发现班号（classno）需要一个索引，不仅仅是用来关联班级信息表（class）、而且经常作为查询条件，因此创建脚本
栗子2:
对于学生信息我们经常用性别作为统计条件进行对学生信息进行统计，因此我们可以在性别（sex）建立一个位图索引进行查询优化
三种索引的状态：
查询结果:
PL/SQL基本语法
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
解释:
DECLARE 声明部分
此部分开头使用关键字DECLARE。它是一个可选的部分，并限定在该程序中使用的所有变量，游标，子程序，和其他元素。
BEGIN执行命令块
此部分是封闭关键字BEGIN和END，这是一个强制性的部分。它由程序的可执行文件的PL/SQL语句。它应具有至少一个可执行的代码行，这可能仅仅是一个空命令，以指示什么都不执行。
[ 声明部分声明了一个 长度为20的varchar2类型 变量 message 并且初始化。 ：= 为赋值号。
在程序执行块中使用dbms_output.put_line（）函数输出出来。]
__DECLARE部分 :
①定义基本类型变量
类型:char,varchar2,date,number,boolean,long
定义语法：变量名 数据类型（长度） 如: name varchar2(100), age number(10) .....
②引用型变量%TYPE类型 和 记录型变量%ROWTYPE类型
引用型变量 my_name emp.ename%type;
[ --表示my_name这个变量的类型是引用emp这个表的ename列的类型
相当于引用某一个数据到student表中 ]
栗子:
declare
my_name emp.ename%type;
begin
select t.name into my_name
from student t;
记录型变量 my_name emp.ename%rowtype
[ --my_name相当于一个数组，存的类型是一条记录中的类型，打印某一列的时候，直接用 emp_rec.列名即可 相当于引用一行数据到student表中 ]
栗子:
declare
my_info emp.ename%rowtype;
begin
select t.* into my_info
from student t
where t.stuid='SC10086';
如: my_info.stuname || ' 学号:' || ls_info.stuid || ' 年龄：' || my_info.age;
-----------------------------------------------------------------------------------------------------------------------------------
流程控制
IF-THEN-END IF (与java中if else差不多)
if (条件) then
--满足条件执行体;
endif;
if my_info.sex='1' then --性别编码为1的是男生
my_number+1; --(当my_info.sex='1'条件满足时执行my_number+1)
end if;
IF-THEN-ELSE- END IF
if 条件 then
--满足条件执行体;
else
--不满足条件执行体;
end if;
if my_info.sex='1' then--性别编码为1的是男生
my_number_boy+1;--(当my_info.sex='1'条件成立时,执行my_number_boy+1操作)
else
my_number_boy-1;--(当my_info.sex='1'条件不成立时,执行my_number_boy-1操作)
end if;
IF-THEN -ELSIF-THEN -END IF
if 条件1 then
--条件1成立执行体;
els if 条件2 then
--条件1不成立,条件2成立执行体;
else
--条件都不成立执行体;
end if;
if my_info.sex='1' then--性别编码为1的是男生
my_number_boy+1;--(当my_info.sex='1'条件成立时,执行my_number_boy+1操作)
els if my_info.sex='2' then--性别编码为1的是男生
my_number_boy+2;--(当my_info.sex='1'条件不成立时,执行my_number_boy+2操作)
else
my_number_boy-3;--(当my_info.sex='1'条件不成立时,执行my_number_boy-3操作)
end if;
CASE...WHEN...THEN (与java中switch差不多)
CASE选择体
WHEN表达式1 then执行体;
WHEN表达式2 then执行体;
WHEN表达式3 then执行体;
......
ELSE表达式n then执行体;
END CASE;
case my_info.age
when 26 then
my_number_26+1;
when 27 then
my_number_27+1;
else
my_number-1;
end case;
循环结构
Oracle 提供的循环类型有：FOR 循环语句和WHILE循环语句。
栗子:
Declare
int NUMBER(2):=0; (定义一个变量)
BEGIN
LOOP (执行语句)
int :=int+1 (循环内容)
DBMS_OUTPUT.PUT_LINE('int的当前值：'||int); (输出内容)
EXIT WHEN int=10; (exit when 条件满足时 退出循环)
END LOOP;
END;
WHILE循环
栗子:
Declare
x NUMBER(1):=1; (定义一个变量x)
BEGIN
WHILE x<10 LOOP (条件满足时循环)
DBMS_OUTPUT.PUT_LINE('X的当前值为：'||x);
x:=x+1;
END LOOP;
FOR循环
BEGIN
FOR int in 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('int的当前值为：'||int);
END LOOP;
注：每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。
--------------------------------------------------------------------------------------------------------------------------------
异常处理
语法解析： 1、exception是异常处理部分开始的标志。 2、when后面是跟着异常的名称， then后面是对应异常处理程序。也就是当异常exception1出现时，执行的是异常1处理程序。其它异常程序不会进入。 3、when others then 指的是异常再前面异常捕获中未捕获到对应的异常处理程序，则全部进入其它异常处理程序进行异常处理。
Oracle一共提供了25种预定义异常名称 (命令如下)
select * from dba_source t where t.TEXT like '%EXCEPTION_INIT%' AND NAME='STANDARD';
栗子:
declare
my_info stuinfo%rowtype;
begin
select t.* my_info
from stuinfo t
where t.stuid='SC2018010061';
--该学号找不到对应的学生
exception
when no_data_found then (对应图中的第7条)
dbms_output.put_line('该学生在学生信息表中找不到');
end;
-----------------------------------------------------------------------------------------------------------------------------------
游标
(%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT) 游标属性
Oracle游标 可以分为显式游标和隐式游标两种之分。
显式游标：指的是游标使用之前必须得先声明定义，一般是对查询语句的结果事进行定义游标，然后通过打开游标循环获取结果集内的记录，或者可以根据业务需求跳出循环结束游标的获取。循环完成后，可以通过关闭游标，结果集就不能再获取了。全部操作完全由开发者自己编写完成，自己控制。
隐式游标：指的是PL/SQL自己管理的游标，开发者不能自己控制操作，只能获得它的属性信息。
隐式游标虽然不能像显式游标一样具有操作性，但是在实际开发过程当中还是经常使用到它的属性值，隐式游标主要是用在select语句活DML语句时，PL/SQL程序会自动打开隐式游标，这个隐式游标是不受开发者控制的
1、声明游标：
声明游标是给游标命名并给游标关联一个查询结果集，具体声明语法如下：
declare cursor cursor_name(游标名) is select＿statement(查询语句);
2、打开游标：
游标声明完，可以通过打开游标打开命令，初始化游标指针，游标一旦打开后，游标对应的结果集就是静态不会再变了，不管查询的表的基础数据发生了变化。打开游标的命令如下：
open cursor_name;
3、读取游标中数据：
读取游标中的数据是通过fetch into语句完成，把当前游标指针指向的数据行读取到对应的变量中（record 变量）。游标读取一般和循环LOOP一起使用，用于循环获取数据集中的记录。
fetch cursor_name into record变量
4、关闭游标：
游标使用完，一定要关闭游标释放资源。关闭后，该游标关联的结果集就释放了，不能够再操作了，命令如下：
close cursor_name;
在declare定义或中 定义一个游标 cursor cur_xsjbxx is 数据来源
select * from stuinfo order by stuid; 再定义一个记录型变量
ls_curinfo cur_xsjbxx%rowtype;在begin执行域中 打开游标
open cur_xsjbxx; loop循环读取游标并记录在 ls_curinfo变量中. 通过游标的属性变量%NOTFUND来获取游标的结束，跳出LOOP循环 最后循环结束后
close cur_xsjbxx;--关闭游标
隐式游标
隐式游标虽然不能像显式游标一样具有操作性，但是在实际开发过程当中还是经常使用到它的属性值，隐式游标主要是用在select语句活DML语句时，PL/SQL程序会自动打开隐式游标，这个隐式游标是不受开发者控制的
oracle隐式游标没有像显式游标一样声明游标名，直接采用SQL名称作为隐式游标的名称。然后可以利用游标的属性，做一些逻辑判断，隐式游标的属性值和显式的一样，有%NOTFOUND、%FOUND、%ROWCOUNT、%ISOPEN。显式游标表示的属性值都是对结果集行数的一些判断，而隐式游标对应的就是DML语句影响的行数。
触发器
Oracle触发器可以根据不同的数据库事件进行特定的调用触发器程序块，因此，它可以帮助开发者完成一些PL/SQL存储过程完成不了的问题，比如操作日志的记录、防止一些无效的操作、校验数据的正确性、限制一些对数据库对象的操作、提供数据同步的可行性。但是不推荐在触发器当中写业务逻辑程序，因为这样对后期数据的维护将大大提高成本。
触发器的类型
触发器按照用户具体的操作事件的类型，可以分为5种触发器。大致如下：
1、数据操作（DML）触发器：此触发器是定义在Oracle表上的，当对表执行insert、update、delete操作时可以触发该触发器。如果按照对表中行级数据进行触发或语句级触发，又可以分为行级（row）触发器，语句级触发器，按照修改数据的前后触发触发器，又可以分为 after 触发器和before触发器之分。
2、数据定义操作（DDL）触发器：当对数据库对象进行create、alter、drop操作时，触发触发器进行一些操作记录保存、或者限定操作。
3、用户和系统事件触发器：该类型的触发器是作用在Oracle数据库系统上，当进行数据库事件时，触发触发器，一般用来记录登录的相关信息。
4、INSTEAD OF 触发器：此类型的触发器是作用在视图上，当用户对视图进行操作时，触发该触发器把相关的操作转换为对表进行操作。
5、复合触发器：指的是对数据操作(DML)触发器当中的多种类型触发器进行复合，比如；一个触发器当中包含着after（或before）的行级触发器和after（或before）的语句级触发器，来完成一些更为复杂的操作。
语法解析：
1、or replace :存在同名的触发器就覆盖保存。
2、trigger：创建触发器的关键词。
3、before | after 表示是选择的触发器是数据改变之前触发、数据改变之后触发。
4、delete| insert | update:表示触发器触发的事件类型是删除、插入或更新。
5、for each row: 表示行级触发器、不填就是语句级触发器
6、follows :表示触发器的顺序是跟在哪个之后。
7、when 表示触发器语句触发的条件
创建DML类型触发器
网友评论