Oracle数据库之第三篇
2019-10-05 本文已影响0人
小小一技术驿站
/*
起别名使用双引号 处理特殊字符使用
数据库里的字符串都是使用单引号
*/
/*
DDL语句 是数据定义语言 使用语句创建数据库的对象
表空间 是实例分配的一块空间 用于开发使用
创建语法： create tablespace 表空间名
datafile 文件的路径
size 文件大小
autoextend on
next 扩展大小
*/
--演示表空间创建 需要使用管理员操作
create tablespace baidu
datafile 'c:\baidu.dbf'---linux系统
size 100m
autoextend on
next 10m
---------------------------------
/*
开发使用管理员创建分配的用户
用户的创建
create user 用户名
identified by 密码
default tablespace 表空间名
*/
---创建用户
create user baidu
identified by baidu
default tablespace baidu
--创建完成用户 登陆测试 缺少权限
/*
授权语法：
grant 权限 to 用户
权限的分类
connect 连接的权限可以连接数据库
resource 高级一点的权限 可以建表
dba 最高级的权限 相当于管理员
*/
---授权connect给baidu用户 测试登陆
grant connect to baidu
--授予dba权限
grant dba to baidu
------
create table p(
pid number(9),
pname varchar(10)
)
---------------------------------------------------------------
---plsql developer 图形化工具 instanclient
/*
对表结构的设计
表中含有多少个字段 根据需求来
表中字段都是什么数据类型
主键外键 约束
数据类型
数值类型
int double bigint --mySql支持的数据类型
number(16,v2) v1是数值的总长度 v2是数值的小数位数 默认为0
number(6,2)---9999.99
字符类型
char() --固定长度的字符类型 char(20) zs
实际长度是 2 占用空间 20个长度
varchar() --可变长度的字符类型 varchar(20) zs 张三 utf8 6 gbk 4
实际长度是 2 占用空间 2个长度
varchar2() --可变长度的字符类型 varchar(20) zs 张三 utf8 6 gbk 4
实际长度是 2 占用空间 2个长度 推荐使用
日期类型
date mySql格式 yyyy-mm-dd
oracle 格式 yyyy-mm-dd hh:mi:ss
datetime mySql格式 yyyy-mm-dd hh:mi:ss
oracle 格式 yyyy-mm-dd hh:mi:ss 精确到后面的9位纳秒
大文本类型
clob 字符类型大文本 最大支持4G的长度
数据库存放网页的源代码
blob 二进制类型大文本 最大支持4G的长度
long 长文本 最大支持2g的长度
*/
/*
约束
主键约束 primary key 非空加唯一
外键约束 foreign key
唯一约束 unique 唯一
非空约束 not null 非空
检查约束 check (gender in (0,1)) 判断数值是否违反表达式
手动使用关键字指定约束 constraint 约束名称 约束类型(列)
*/
---使用数据类型和约束建表操作
create table person(
pid number(11) ,
pname varchar2(11) not null,
phone varchar2(11) unique,
gender number(1) check (gender in (0,1)),
constraint pk_person_pid primary key(pid)
)
---插入数据测试约束 oracel事务必须手动选择提交或者回滚
insert into person values(1,'zs','11122223333',1);
insert into person values(1,'zs','11122223333',1);--违反主键约束
insert into person values(2,'zs','11122223333',1);--违反唯一约束
insert into person values(2,'zs','11122224444',1);
insert into person values(3,null,'11122225555',1);--违反非空约束
insert into person values(3,'','11122225555',1); --空串违反非空约束
insert into person values(3,' ','11122225555',1);
insert into person values(4,'zs','11122226666',3);--违反检查约束
insert into person values(4,'zs','11122226666',0);
commit;
/*
表结构的修改
增加一列 alter table 表名 add(列名 数值类型)
修改一列 alter table 表名 modify(列名 数值类型)
重命名列 alter table 表名 rename column 旧列名 to 新列名
删除一列 alter table 表名 drop column 列名
*/
--给person表增加地址一列
alter table person add(address varchar2(20));
--修改address为char 类型 10个长度
alter table person modify(address char(10));
alter table person modify(pname number(11)); --列如有有数据不可以修改类型 报错
--重命名gender性别为sex
alter table person rename column gender to sex
--删除地址address
alter table person drop column address
/*
DML语句 数据操作语言 对表中数据做增删改
插入数据 insert into 表名 values(.....)
insert into person values(1,'zs',1); --不能插入 列数量不匹配
insert into person(pid,pname,sex) values(1,'zs',1); --指定列名插入数据
修改数据
update 表名 set 列名=值 where 条件 修改满足条件的记录
删除数据
delete from 表名 where 条件 删除匹配的数据
delete from 表名 删除所有记录 一条条删除
效率低 可以加条件
truncate table 表名 摧毁表结构 再重建表结构
效率高 不能加条件
*/
--想使用emp表的数据做测试
select * from emp;
--创建表的同时拷贝表的数据 scott用户下的emp
create table emp as select * from scott.emp;
--修改SMITH用户更改名称为SSSS
update emp set ename='SSSS' where ename='SMITH';
commit;
/*
存在主外键的情况下 直接删除主表的记录
一、 1.先删除从表记录
2.再删除主表记录
二、级联删除 on delete cascade
三、直接删除主表
*/
--创建主表订单 orders表
create table orders(
oid number(11) primary key,
oname varchar2(11) ,
oprice number(6,2)
)
--创建字表订单明细表 order_detail
create table order_detail(
detail_id number(11) primary key,
detail_name varchar2(11) ,
detail_price number(6,2),
oid number(11),
constraint fk_detail_oid foreign key(oid) references orders(oid)
-- on delete cascade
)
--插入主表和从表的记录
insert into orders values(1,'订单1',1000);
--insert into order_detail values(1,'订单1',1000,2);--违反外键约束
insert into order_detail values(1,'订单1',1000,1);
commit;
----
select * from orders;
select * from order_detail;
----直接删除主表记录测试
delete from orders where oid=1;
delete from order_detail where detail_id = 1;
commit;
drop table order_detail;
--直接删除主表 强制删除 不建议使用
drop table orders cascade constraint
/*
事务 作为一个逻辑操作单元 执行的任务全部成功，或者全部失败
特性：ACID (原子性 持久性 隔离性 一致性)
没有隔离级别 脏读 幻读 不可重复读
调整隔离级别
oracel数据库隔离级别 READ COMMITED ,SERIALIZABLE,READ ONLY
默认隔离级别是READ COMMITED
事务的保存点：
事务保存点的概念: 保存起执行成功的任务
意义：可以保证执行成功的任务正常提交
使用方法：
声明事务保存点 savepoin 保存点名
出现错误回滚到保存点 rollback to 保存点
再继续提交 commit
spring管理事务 在servie层切入点
*/
declare
begin
insert into orders values(1,'订单1',1000);
insert into orders values(2,'订单1',1000);
insert into orders values(3,'订单1',1000);
insert into orders values(4,'订单1',1000);
insert into orders values(5,'订单1',1000);
savepoint s1; --声明保存点
insert into orders values(6,'订单1',1000);
insert into order_detail values(1,'订单1',1000,100);
commit;
exception
when others then
rollback to s1;
commit;
end;
/*
数据库其余对象
视图 是一个虚拟的表 不存放数据 数据来源为原始表
意义是： 为了数据的安全
为了权限的细分
创建视图 查看特定的数据
create view 视图名 as select * from 表
*/
--查看员工信息
select * from emp;
--创建视图
create view emp_view as select empno,ename,job,deptno from emp;
--查询视图
select * from emp_view
--修改视图
update emp_view set ename='SMITH' where ename='SSSS';
commit;
--创建只读的视图
create view e_view as select empno,ename,job,deptno from emp with read only
update e_view set ename='SSSS' where ename='SMITH';
commit;
/*
序列 是oracle数据生成的一系列数值 用来实现 (序列是对象,所以有属性)
表中记录id的自增长
创建序列
create sequence 序列名称
序列的属性
nextval --下一个值
currval --当前值
create sequence sequence
[INCREMENT BY n] 表示自增长,每次增长n个
[START WITH n] 表示初始值,n就是初始值.
[{MAXVALUE n | NOMAXVALUE}] 表示最大值
[{MINVALUE n | NOMINVALUE}] 表示最小值
[{CYCLE | NOCYCLE}] 表示循环 表示如果设置了最大值,当数字达到最大值以后,会进行循环的设置值,这个不适用于自动增长的主键(非空唯一的)
[{CACHE n | NOCACHE}] 表示缓存,表示缓存n个,如果n是10,表示一次缓存10个序列.
*/
--创建序列
create sequence order_sequence
select order_sequence.nextval from dual; --序列默认值从1开始 nextval生成不会因为插入失败回退
select order_sequence.currval from dual; --查看当前值 必须先生成nextval
insert into orders values(order_sequence.nextval,'订单1',1000);
commit;
/*
索引 理解为一本书的目录
没有目录找到特定章节 费时很长
意义：为了提升查询数据的速度(通过id,创建树结构,来提升查询效率)
前提 ： 只有数据量非常大的情况下 才有意义
创建索引
单行索引 create index 索引名称 on 表(列)
复合索引 create index 索引名称 on 表(列,列2)
create index index_order on orders(oname)
创建索引是在数据库中创建索引结构,放的是索引的列的数值rowid,将oname的数值按照从小到大的排序,
当where oid = 139,通过条件去索引结构定义记录的位置,数据库会通过rowid真实地址去查找数据.
优点 : 提升查询速度.
缺点 : 占用空间.
增删改数据效率降低,需要更新索引结构,如果表中有主键和唯一约束会自动创建索引.
索引使用规范 : 用在于列的数值重复数据很少情况.例如 : 当gender列都是1,会影响索引的查询效率
索引分类 : 常用的是 unique唯一索引,normal普通索引.
*/
--创建大数据量的表
--序列生成的属性 在同一sql语句中只会生成一次
declare
begin
for i in 1..5000000 loop
insert into orders values(order_sequence.nextval,'订单'||order_sequence.nextval,1000);
commit;
end loop;
end;
select count(*) from orders
--先查询数据 记录耗时
select * from orders where oname='订单3333333' --2.234 15 18 19
--创建索引
create index index_order on orders(oname) --一分多钟
--创建索引后 查询同样的记录 耗时
select * from orders where oname='订单3333333' ---0.204
--多个条件作为查询 不是触发单行索引
select * from orders where oname='订单3333333' and oprice=1000
--自动创建索引,根据oid创建的.
select * from orders where oid=4444444
/*
rowid 是数据库在保存数据时候 生成的真实物理地址
区别rownum 是一个伪列 是在查询数据时候才会生成的，可变
rowid 是插入数据就已经生成了 固定的物理地址 唯一不变
*/
--通过以下查询,可以对表进行直接修改.
select rowid,emp.* from emp;
/*
同义词 可以理解为一个对象的别名
意义： 为了数据的安全
为了权限的细分
创建同义词
create synonym 同义词名 for 用户.对象
oracel的from后面可以跟同义词,视图,表,这三种对象.
*/
--查询员工表
select * from scott.emp;
create synonym syn_emp for scott.emp;
---查询同义词
select * from syn_emp
/*
数据库的导入导出
1.为了数据安全 备份和还原使用
2.为了服务器的迁移 把以前旧服务器的整个数据库迁移到新服务器
3.开发人员的操作 是为了部署项目导入表结构
开发是在测试环境 开发完成 需要上线
代码 上传到服务器 tomcat
开发的数据库表 上传到服务器数据库
去数据库建表 直接new 有风险
公司里一般都是 导出新建的表结构 通过命令运行
导入导出实现
通过命令行导入导出 需要安装oracel数据库服务器
导出命令 exp
整个数据库导入 exp 用户名/密码 file=文件.dmp full = y
按照用户导出 exp 用户名/密码 owner= 用户 file=文件.dmp
按照表来导出 exp 用户名/密码 file=文件.dmp tables=表名，表名2
导入 将exp 换成 imp
通过图形化工具导入导出
tools --export user objects 导出表结构 不能备份数据 包含所有的对象
tools --exprot tables 导出表可以备份结构和数据 默认情况只能备份数据
oracel数据库格式 .dmp文件 服务器才能做
sql文件格式 .sql文件
图形化工具格式 .pde文件
*/
