三篇拿下oracle【篇二】

多表查询

内连接
	inner join
外连接
	left outer join
	right outer join
	(画图)特殊链接+
//内连接
select * from emp el,emp t1 where el.mgr = t1.empno;
//t1表的所有记录都会显示，如果t1.empno在el.mgr中没有对应的记录，就加空值
select * from emp el,emp t1 where el.mgr（+） = t1.empno;

子查询
	select ename,deptno from emp;
	关联子查询
		select e.name,e.deptno,(select d.dname) 

DDL DML

DDL
	创建表空间
DML
其他数据库对象
	视图
	序列
	索引
PLSQL编程语言

DDL

创建表空间：逻辑单位，通常我们新建一个项目，就会去新建表空间，在表空间中创建用户来创建表
create tablespace 表空间名称 datefile ‘文件路径’ size 大小 autoextend on next 每次扩展的大小;
drop tablespace name;
创建用户（没有权限）
create user 用户名 identified by 密码 default tablespace 表空间名称 授权（oracle默认三个角色DBA CONNECT RESOURCE）
grant 角色/权限 to 用户
用户：
create user name identified by 123456;
alter user name identified by 123;
drop user name cascde;

创建表：
	create table 表名(
		列名 列的类型[约束]，
		列名 列的类型
	);
	
	列的类型：
		varchar
		varchar2(len) 可变  varchar2(10) hello 占5个字符
		char(len) 固定      char(10) hello 占10个字符，空格填充
		number(总长度，小数长度) 数字类型
		date   年月日时分秒
		timestamp  时间戳，比date更精确
	
	create table test1(
		name1 varchar2(10),
		name2 char(10),
		age number(2,3)
	);
	insert into test1(name1,age) values('hello',12);
	
	使用子查询创建表
		create table 表名 as 查询语句；
			*只会负责表结构和表中的数据，不会负责列的约束
			*如果查询语句没有结果，只负责结构
		select * from scott.emp;
		create table emp as select * from scott.emp;
		
修改表：
	添加列 
	修改列 
	删除列
	修改列名
	重命名表
	
	--添加一列
		alter table stu add phone varchar2(11);
		
		alter table stu add (
							phone varchar2(11),
							sex varchar2(2)
							);`
	--修改列的类型
		alter table stu modify sex varchar2(4);
	--修改列名
		alter table stu rename column sex to gender;
	--删除列
		alter table stu drop  column gender;
	--修改表名
		rename stu to student;
	--删除表
		drop table student;
		truncate table stu;//清空表的数据
		
表的五大约束:
列的约束：约束表中数据的规则
	主键约束 primary key
	非空约束 not null
	唯一约束 unique
	检查约束 check (mysql忽略检查约束)
	
	外键约束：references
		约束从表中记录必须存在于主表中
	
	create table student(
		stuid number primary key,
		sanme varchar2(10) unique,
		age varchar2(10) not null,
		gender varchar2(4) check(gender in ('男','女','人妖'))
	)；
	
	--主表中必须存在11号，先在主表中插入数据，再在从表中插入数据
		insert into category vlaues(1,'电脑办公');
		insert into product(11,'外星人',2);
	--强制删除外键主表:先删除外键关联表的约束，再删除自己（不推荐使用）
	drop table category cascade constraint;
	--级联删除表
	添加外键约束，使用级联约束(只在删除的时候，使用级联删除)
	alter table product add foreign key(cno) references category(cid) on delete cascade;

DML

插入数据 insert into 表名 values(所有列的值都要对应写上);
insert into 表名(1,2) values(1,2);
使用子查询插入数据：inset into 表名 查询语句；

	--将emp中10号部门的员工插入到emp1
	insert into emp1 select * from emp where deptno=10;
	
更新数据：
	update 表名 set 列名=值  {where=}；
删除数据：
	delete from 表名 {where=};
	
	delete和truncate差别
		delete:逐条删除 DMl 支持事务操作
		truncate:先删除表再创建表 DDl 不支持事务操作（效率高）

事务

一系列操作，要么成功，要么失败
四大特性：原子性 隔离性 持久性 一致性

如果不考虑隔离级别：脏读 虚读 不可重复读
mysql事务隔离级别：READ UNCOMMITTED,READ
COMMITTED,REPEATABLE READ,SERIALIZABLE
oracle隔离级别：Read COMMITTED ， SERIALIZABLE READ ONLY
默认隔离级别：READ COMMITTED

提交：commit
事务的保存点/回滚点：savepoint 保存点的名称
回滚：rollback

				begin
					insert into lout values(1);
					insert into lout values(2);
					savepoint dangban;
					insert into lout values(3);
					commit；
				exception
					when others then
						rollback to dangban；
						commit;
				end;

视图

是对查询结果的一个封装；
视图里所有的数据，都是来自原表，视图本身不存储任何数据

• 1.封装复杂的查询结果
• 2.屏蔽表中的细节

语法：
create [or replace] view 视图名称 as 查询语句 [with read only];
		例子：	可以隐藏表中的其他数据
		create or replace view Vtest1 as select ename,job,mgr from emp;
		select * from Vtest1;
##通常不要通过视图去修改，视图创建时候，加[with read only]
		update Vtest1 set ename='SMITH2' where name ='SMITH';

同义词的概念

create synonym dept for view_test3;
	select * from dept;

序列

生成类似auto_increment 这样id自动增长；
auto_increment 是mysql (oracle不存在)

语法：
			create sequence 序列的名称
				start with 从几开始
				increment by 每次增长多少
				maxvalue 最大值 | nomaxvalue
				minvalue 最小值 | nominvalue
				cycle | nocycle 是否循环
				cache 缓冲数量 |nocache
				
		如何从序列中获取值
			currval:当前值
			nextval:下一个值
				注意：currval需要在调用nextval之后才能使用
			select seq_test1.nextval from dual;	
			select seq_test1.currval from dual;

索引

相当于一本书的目录，提高我们查询效率；
如果某一列，你经常作为查询的条件，那么就有必要创建索引，数据量比较的情况。

语法：	
		create index 索引的名称 on 表名(列)
		
		注意：主键约束自带组件索引，唯一约束自带唯一索引。
	
	索引原理：btree balance Tree 平衡二叉树
			插入会导致树结构的变化，故插入较慢；
			索引创建好后，过了一段时间，DBA会做重构索引；
			
	create table wubaiwan(
		name2 varchar2(30),
		address varchar2(20)
	);
	
	insert into wubaiwan values('')
	declare
	begin
		for i in 1..5000000 loop
			insert into wubaiwan values('姓名'||i，'地址'||identified);
		end loop;
		commit;
	end;

PLSQL编程

procedure Language 过程语言 Oracle对sql的扩展；
让我们像在java中一样写 条件表达式，还可以编写相应的循环逻辑 for while

语法：	
	declare
		//声明变量
		变量名 变量类型;
		变量名 变量类型 := 初始值;
		vsal emp.sal%type;--引用型变量
		vrow emp%rowtype;--声明记录型变量
	begin
		//业务逻辑
	end;

例子1：		
	declare
		 i varchar2(10):='张三';
	begin
		--输出
		dbms_output.put_line(i);
	end；
	例子2：查询员工7369的工资
	declare
		 vsal emp.sal%type;--vsal的类型和sal相同
	begin
		--将查询结果赋值给vsal
		select sal into vsal from emp where empno = 7369;
		dbms_output.put_line(vsal);
	end；
	例子3：
	declare
		 vrow emp%rowtype;--声明行变量
	begin
		
		select sal into vrow from emp where empno = 7369;
		dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.ral);
	end；
	
	
	PL条件判断:
		if then 
		
		elseif then
		
		else
		
		end if;
	PL循环：
		while
				while 条件 loop
				end loop;
			
		for
				for 变量 in {reverse倒序} 起始..结束值 loop
				end loop;
		loop
				loop
					exit when 条件;
				end loop;
		
	--输出1-10
		declare
		 i number :=1;
		begin
		 while i<=10 loop
			dbms_output.put_line(i);
			i := i+1;
		end loop;
		end；