Oracle笔记

1 数据库控制语言（DCL）

1.1 创建表空间

-- 创建db_qmxc的表空间
create tablespace db_qmxc
-- 指定数据文件的存放目录
datafile 'D:/oracle_dbf/db_qmxc.dbf'
-- 设置表空间的大小
size 50M

1.2 创建用户

-- 创建gjy用户
create user gjy
-- 设置密码，密码不能以数字开头
identified by admin
-- 指定默认操作的表空间
default tablespace db_qmxc

1.3 赋予/删除用户权限

权限种类：
connect： 可登录
resource： 可管理表数据
dba： 可管理更多的对象

-- 赋予gjy登录和管理表数据的权限
grant connect,resource to gjy
-- 删除gjy的权限
revoke connect from gjy

1.4 其他控制语言

-- 提交：在执行DML语句后需要commit提交保存
Commit
-- 回滚：撤销执行的DML语句，无法回滚已提交的语句
Rollback
-- 回滚到：回滚到保存点的位置
rollback to
-- 保存点：常与rollback to搭配
Savepoint

---

2 数据库定义语言（DDL）

2.1 Oracle中的数据类型

名称	类型	备注
整型	number(5)
浮点型	number(5,2)	3位整数，2位小数
字符串	varchar2(4000)	最大4000
日期	date
大数据	blob
大文本	clob

2.2 数据库定义语言

-- 创建表格  
create table t_user(
	id		number,
	name	varchar2(200)
)
-- 修改表名
rename t_user to t_abc
-- 删除表格
drop table t_abc
-- 新增一列 age number(3)
alter table t_user add age number(3)
-- 修改列名
alter table t_user rename column age to sex
-- 修改列的数据类型
alter table t_user modify sex number(1)
-- 删除一列
alter table t_user drop column sex

2.3 检查约束

加检查约束前要保证已有数据满足约束，所以建议在空表时加。

--建表后添加约束
alter table 
	t_result
add constraint 
	CK_result_score
check (score>=0 and score<=100)
--建表时添加约束
create table t_abc(
	gender char(2) not null check (gender='男' or gender='女')
)

2.4* 序列(sequence)

oracle没有自增列，使用序列来代替自增列，序列是一个对象，这个对象独立存在属于同一个用户的任何一张表。

序列的创建

create/drop/alter sequence my_seq
start with 1	--默认从1开始
increment by 1	--默认间隔为1
maxvalue 99999	--最大值为99999，默认nomaxvalue 
minvalue 1		--最小值为1，默认是开始值
cycle			--默认nocycle  表示序列达到最大值后，再取值是否从头开始
cache 10		--缓存多少个数值，缓存越多内存占用越多，取值块，反之 内存少，取值慢

序列的使用

--取下一个值（用于添加数据给主键赋值）
select my_seq.nextval from dual
--取当前值（需要在会话中使用过该序列才能获得这个值）
select my_seq.currval from dual

2.5 同义词(synonym)

实际上就是对当前oracle对象（表，序列）起别名，同义词的创建需要dba权限。

私有同义词：
只能在当前用户内访问，且不能与对象同名。

-- 为序列创建/删除同义词
create/drop synonym ms for my_seq
-- 为表创建/删除同义词
create/drop synonym emp for employee
-- test
select ms.nextval from dual
select * from emp

公有同义词：
可被所有的数据库用户访问。

create/drop public synonym common_emp for employee
select * from common_emp

2.6 索引

见 6 索引。

---

3 数据库操纵语言（DML）

--增加语句（注意日期的格式）
insert into t_book values (5,'金瓶梅',40.00,'21-8月-2019');
--删除语句
delete from t_book where id=1
--修改语句
update t_book set name='xxx',price=12.25 where id=1

---

4 数据库查询语言（DQL）

4.1 Oracle中的伪列

 rowid：oracle会为每条数据分配一个rowid，来唯一标示这行在数据库中的唯一性。
 rownum：是查询结果出现之后的行的序号，查询结果表示where条件执行之后。

4.2 数据库查询语言

Oracle的查询SQL和MySql基本一样，这里不做过多讲解，不同之处如下。

必须使用子查询的情况：

-- 如果需要排序后编号（rownum），用子查询
select 
	rownum,r.* 
from 
	(select * from t_book order by price desc) r
-- 由于编号从1开始 所以直接查询非1的行是查询不到的。如果查询结果不从1行开始 ，需要子查询；
select * from
	(select rownum n,b.* from t_book b) r 
where 
	r.n=2

分页查询：

-- 通过rownum伪列进行子查询。
-- 查询不及格中第3页（第21条到第30条）的数据
select rr.* from 
	(select rownum rn, r.* from result r where score < 60 ) rr
where 
	rr.rn>20 and rr.rn<=30

5 函数

5.1 转换函数

to_char()：将数字、日期或者字符串转换为规定格式的字符串;

--oracle中获得当前日期
select sysdate from dual
select to_char(sysdate ,'yyyy"年"mm"月"dd"日"') from dual
select to_char(sysdate ,'yyyy-mm-dd hh24:mi:ss') from dual
select to_char(15000,'$99,999.00') from dual

to_date():在页面上获得的日期不可能是1-12月-2018，更可能是2018-12-1，怎么将2018-12-1转换成日期类型呢？

select to_date('2018-12-1' ,'yyyy-mm-dd') from dual
insert into employee values(3,to_date('2017-9-20','yyyy-mm-dd'))

to_number():

select to_number('10')+to_number('20') from dual

5.2 逻辑函数

nvl():

select studentname,nvl(address,'<地址未填>') from t_student

nvl2():

select studentname,nvl2(address,'地址已填','地址未填') from t_student

decode():

select to_char(sysdate,'mm') from dual
select 
	studentname,
	decode(
		to_char(borndate,'mm'),
			'01','一月',
			'02','二月',
			'03','三月',
			'04','四月',
			'五月后'
	) born_moon
from t_student

---

6 索引(index)

  使用索引提高查询效率，不再需要每次都全表查询。
  主键实际上是一个建表时就会自动创建的索引。主键列的索引是聚集索引，因为聚集索引的顺序和数据库的物理顺序相同，所以使用主键查询数据是非常高效的，一张表只能有一个聚集索引，我们将要创建的索引是非聚集索引。

索引的缺点：

• 占用空间；
• 创建索引后，对数据进行增删操作时，会引发索引的改变，对增删效率有影响。

使用索引的注意事项：

• 要先增数据，再建索引；
• 对经常作为查询条件的列键索引；
• 查询出的内容占所用内容15%以下才有必要建索引；
• 如果查询时使用模糊查询，并使用%作为模糊查询条件的开始，将无法使用索引。

6.1 B树索引

create index userinfo_username on userinfo(username)
drop index userinfo_username

6.2 反向键索引

提高增加数据的效率，查询效率提高不明显。

create index userinfo_username on userinfo(username) reverse

6.3 位图(bitmap)索引

位图索引适合建立在列的数据样本比较少的列中。
(比如：一张员工表中的部门字段)

create bitmap index bituser_belief on userinfo(belief)
drop index bituser_belief

---

7 存储过程

  如果有经常需要运行的代码，只是有个别数据不同还需要修改代码运行，如果oracle中有像java中方法一样的功能，直接调用这个方法即可。存储过程即Oracle中的方法。
  存储过程就是事先为一些业务编写好的，存储在oracle中的编译好的plsql语句。存储过程也是保存在数据库中的，也是向表、序列一样的对象。
  存储过程的优势：1安全；2效率高。

【案例】
银行转账的存储过程：

create or replace procedure 
	zhuan(id1 in number, id2 in number, money in number, msg out varchar2) is
	v_count number(20);
	v_money account.money%type;
begin
	-- 判断这两个id值是否存在
	select count(*) into v_count from account where id in(id1,id2);
	if v_count=2 then
		-- 判断转出账户余额是否能够满足本次转账需求
		select money into v_money from account where id=id1;
		if umoney<v_money then
			update account set money=money-umoney where id=id1;
			update account set money=money+umoney where id=id2;
			commit;--提交
			msg:='ok';
		else
			msg:='money not enough';
		end if;
	else
         msg:='user not found';
	end if;
end zhuan;

java端JDBC操作Oracle数据库调用存储过程：

//1.  加载驱动
Class.forName("oracle.jdbc.OracleDriver");
//2. 获得数据库连接
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl2", "scott", "salmon");	
//3. 获得CallableStatement通道
CallableStatement cs=conn.prepareCall("call zhuan(?,?,?,?)");
//4. 绑定参数
cs.setObject(1, 3);
cs.setObject(2, 1);
cs.setObject(3, 4000);
//5. 注册出参，让java端知晓出参的数据类型
cs.registerOutParameter(4, Types.VARCHAR);
//6. 执行
cs.executeUpdate();
//7. 获得出参数据
Object obj=cs.getObject(4);
System.out.println(obj);

8 自定义函数

自定义函数的优势：1安全；2效率高。

与存储过程的区别：

1. 只返回一个值时可以使用函数，返回多个值时只能使用存储过程；
2. 函数可以在select语句中使用，存储过程只能在java端（服务器端）使用。