Oracle数据库之第三篇

/*
   起别名使用双引号  处理特殊字符使用
   数据库里的字符串都是使用单引号
	*/
	/*
	   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文件 
	   
	*/