ORACLE初识总结

--创建表空间
create tablespace oracle17_5
datafile 'e:/db/oracle17_5.dbf'
size 50M


--删除表空间
drop tablespace oracle17_5


--创建用户
create user haoshen
identified by admin
default tablespace  oracle17_5


--删除用户
drop user t7


--分配权限(角色带有的权限)
connect(登录)   resource(维护)  DBA(系统管理员)


grant connect,resource to haoshen


--撤销权限
revoke connect,resource f rom haoshen




数据类型
字符串：
Char(len)固定长度字符串
Varchar2(len)可变长度字符串
Nchar(len)：只支持unicode字符编码的字符串
Nvarchar2(len)：只支持unicode字符编码的字符串


数值类型：
Number：如果没写（）规定是32位整数
Number(p):表示p位整数
Number(p,s):举例(8,2)表示6位整数、2位小数


id number(8)如果不写(8)默认是32位整数
money number(8,2)表示总共8位，其中2位是小数
如果想存1234.4321 number(8,4)


日期
Date  年月日时分秒
--创建表
create  table emp(
empid number(8) primary key,--使用序列来代替自增列 
empname varchar2(40) not null,
gender char(2) not null,
job varchar2(20) not null,
birthday date ,
salary number(8,2) not null,
deptid references dept(deptid) not null
)


create table dept(
deptid number(8) primary key,
deptname varchar2(20) not null,
loc varchar2(40) not null


)


alter table emp
add constraint FK_emp_deptid
foreign key (deptid) references dept(deptid);


--mysql中学习的增加列、删除列、修改列均可以


alter table emp 
add phone char(11) not null unique


alter table emp
drop  column  phone




drop table emp
drop table dept


所有在oracle数据库中的增删改操作必须提交才能生效


--设置检查约束
alter  table emp 
add constraint CK_emp_gender
check(gender='男' or gender='女')


alter  table emp 
add constraint CK_emp_salary
check(salary>=0)


select * from emp
insert into emp values(102,'周伯通','男','老顽童','23-11月-2015',5200,'2');


insert into dept values(1,'人力资源部','中关村');
insert into dept values(2,'财务部','金融街');
insert into dept values(3,'开发部','上地');
insert into dept values(4,'市场部','望京');
commit;提交
rollback;回滚


delete from emp
insert into emp values(101,'欧阳锋','男','西毒','27-12月-2015',1111,'1');
insert into emp values(102,'周伯通','男','老顽童','27-12月-2015',5200,'2');
insert into emp values(103,'黄药师','男','东邪','27-12月-2015',4200,'3');
insert into emp values(104,'丘处机','男','二货','27-12月-2015',3200,'2');
insert into emp values(105,'杨过','男','神雕大侠','27-12月-2015',7200,'3');
insert into emp values(106,'欧阳克','男','流氓','27-12月-2015',2222,'1');
insert into emp values(107,'王重阳','男','中神通','27-12月-2015',5200,'2');
insert into emp values(108,'黄大力','男','卖药的','27-12月-2015',6000,'3');
insert into emp values(109,'尹志平','男','仙道','27-12月-2015',12000,'2');
insert into emp values(110,'杨康','男','官二代','27-12月-2015',9900,'3');
commit;


mysql和oracle表中存储英文的不同
mysql表中的英文字母在查询时是不分大小写的
oracle表中的英文字母在查询时是区分大小写的


SELECT * FROM EMP WHERE EMPNAME='HuangYaoShi'


关于伪列
1rowid
是在数据添加到表时oracle系统分配给这行的唯一值
相当于这行数据在整个数据库中的主键
rowid不会重复出现，始终跟随这条数据


select e.*,rowid from emp e




2rownum
是oracle分配给查询结果行的一个临时行号


select e.*, rownum from emp e where salary>5000


rownum会从where条件筛选结束之后对查询结果进行rownum的生成 


select e.*, rownum from emp e where salary>5000 order by salary desc


可以在where条件中查询前几条


select  e.*, rownum from emp e where salary>5000 and rownum<=3


但是由于rownum不会在order by之后重新生成，所以直接取前几条往往没有意义


select rownum, e.* from 
(select * from emp where deptid=2 order by salary desc) e
where rownum<=3


关于分页查询
如果是不排序的分页
select * from(
select rownum r,e.* from emp e) ee
where ee.r>9 and ee.r<=12


如果是排序的分页
select * from (
select rownum r, e.* from 
(select * from emp order by salary desc) e) ee
where ee.r>10 and ee.r<=15




--序列
--oracle是没有表中的自增列的
--怎么样获得一个唯一且不重复的值用作主键呢？
--使用序列来代替自增列


--序列是一个和任何表都没有绑定关系的独立的对象
--序列可以按照一定规则来生成不重复的唯一的值


--怎么样创建序列
create sequence seq_myseq
start with 1
increment by 1






CREATE SEQUENCE toys_seq
	START WITH 10 --初始值(默认1)
	INCREMENT BY 10 --步长(默认1)
	MAXVALUE 2000  --最大值(默认无限大)
	MINVALUE 10    --最小值(默认1)
	NOCYCLE	       --不循环(默认不循环)
	CACHE 10;      --缓存数 




insert into emp values(seq_myseq.nextval,'萧峰','男','帮主','27-10月-2015',11000,'3');
insert into emp values(seq_myseq.nextval,'段誉','男','皇二代','27-12月-2015',9500,'2');
insert into emp values(seq_myseq.nextval,'虚竹','男','和尚','27-12月-2015',6500,'3');
commit


select* from emp


select sysdate from dual


select 56*91 from dual


select seq_myseq.nextval from dual


commit


select * from student
select * from grade
select * from result
select * from subject


--distinct去掉重复行


select distinct studentno from result 


select distinct studentno,subjectid from result


--查询重复的或没有重复的


select studentno,subjectid,count(1) from result group by studentno,subjectid
having count(1)>1


select studentno,subjectid,count(1) from result group by studentno,subjectid
having count(1)=1




create table guanxi(
rid number(8),
fid number(8)
)


--删除重复行




delete from guanxi where rowid not in
(select max(rowid) from guanxi group by rid,fid having count(1)=1)
and
 rowid not in(
select max(rowid) from guanxi group by rid,fid having count(1)>1
)




--union将两个表的查询结果和在一张表中
--union可以将两张不同表的数据在一个查询结果中显示，但是需要满足条件
--1两次查询的列数一致    2对应的列的数据类型相同
select * from (
select empid,empname,job, 1 r from emp
union
select deptid,deptname,loc, 2 r from dept
) ee
order by ee.r


--联合查询
union在联合之后如果两次查询出现了相同rowid行，那么只会出现一次
union all在联合之后如果两次查询出现了相同rowid行，那么两次都会出现
intersect在联合之后只显示两次查询都出现的rowid行
minus在联合之后只显示A结果不包含B结果中rowid的行




select * from emp where gender='男'
union
select * from emp where salary<6000




select * from emp where gender='男'
intersect
select * from emp where salary<6000


select * from emp where gender='男'
minus
select * from emp where salary<6000


select * from emp where salary<6000
minus
select * from emp where gender='男'


--oracle的数据导出：
1sql文件
      优点：可读、灵活，缺点：运行不稳定、维护困难，不适合数据量较大的数据备份和导出
2oracle通用方式 *.dmp文件
      优点：封装后的文件、安全性高，运行可靠，文件较sql文件小
      缺点：不可读


3plsqldev方式
      优点：封装后的文件、安全性高，运行可靠，文件较sql文件小
      缺点：不可读，只能由plsqldeveloper运行
      
drop table emp;
drop table dept;
drop table result;
drop table subject;
drop table student;
drop table grade;
drop table guanxi;




--事务控制
--什么是事务：就是业务逻辑中的最小单元
--就是多条更新语句要么都执行，要么都不执行的情况
--事务的特征
       --一致性
       --原子性
       --永久性
       --隔离性
       


       
--oracle中的运算符
算术：+ - * / %
比较：>  <  >=  <=  =  <>(!=)
逻辑运算符 and or not
       
--连接符




select empid||'-'||salary from emp


--oracle中的系统函数
--转换函数
--to_char
select empname,job,to_char(birthday,'yyyy"年"fmmm"月"dd"日"'),to_char(salary,'$99999.00') from emp


select to_char(sysdate,'yyyy"年"fmmm"月"dd"日" hh24:mi:ss') from dual
--to_date


select to_date('2016-6-6','yyyy-mm-dd') from dual


insert into emp values(seq_myseq.nextval,'萧峰','男','帮主',to_date('2013-9-15','yyyy-mm-dd'),11000,'3');


--to_char是将日期等类型转换成字符串varchar2
--to_date是将字符串类型转换成日期类型date，其中在开发中to_date更常用


select '5'+'8' from dual


--逻辑函数
--nvl
select studentname,nvl(address,'<未填写>') from student 


--nvl2
select studentname,nvl2(address,'<已填写>','<未填写>') from student


--decode
select empname,decode(deptid,'1','启用','2','禁用','已删除') from emp




--分析函数
我们经常对一个查询结果进行排序，由于排序列的值可能相同，导致排序的序号总不能满意




select * from (
select studentno,subjectid,score,
       rank() over(order by score desc) srank,
       dense_rank() over(order by score desc) 连续名次,
       row_number() over(order by score desc) 行号
 from result
 ) ee
 where ee.srank=1
 
 
 --关于数据库自动主键值


可以采用序列作为主键值
但是序列只能提供给一个数值类型的主键
如果想使用varchar2类型作为主键要什么技术呢？
就是sys_guid()


select sys_guid() from dual 
sys_guid()就是一个生成32位16位进制随机数的字符串