ORACLE初识总结

--创建表空间create tablespace oracle17_5datafile 'e:/db/oracle17_5.dbf'size 50M--删除表空间drop tablespace oracle17_5--创建用户create user haoshenidentified by admindefault 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 empadd constraint FK_emp_deptidforeign key (deptid) references dept(deptid);--mysql中学习的增加列、删除列、修改列均可以alter table emp add phone char(11) not null uniquealter table empdrop  column  phonedrop table empdrop table dept所有在oracle数据库中的增删改操作必须提交才能生效--设置检查约束alter  table emp add constraint CK_emp_gendercheck(gender='男' or gender='女')alter  table emp add constraint CK_emp_salarycheck(salary>=0)select * from empinsert 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 empinsert 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 e2rownum是oracle分配给查询结果行的一个临时行号select e.*, rownum from emp e where salary>5000rownum会从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) ewhere rownum<=3关于分页查询如果是不排序的分页select * from(select rownum r,e.* from emp e) eewhere ee.r>9 and ee.r<=12如果是排序的分页select * from (select rownum r, e.* from (select * from emp order by salary desc) e) eewhere ee.r>10 and ee.r<=15--序列--oracle是没有表中的自增列的--怎么样获得一个唯一且不重复的值用作主键呢？--使用序列来代替自增列--序列是一个和任何表都没有绑定关系的独立的对象--序列可以按照一定规则来生成不重复的唯一的值--怎么样创建序列create sequence seq_myseqstart with 1increment by 1CREATE SEQUENCE toys_seqSTART 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');commitselect* from empselect sysdate from dualselect 56*91 from dualselect seq_myseq.nextval from dualcommitselect * from studentselect * from gradeselect * from resultselect * from subject--distinct去掉重复行select distinct studentno from result select distinct studentno,subjectid from result--查询重复的或没有重复的select studentno,subjectid,count(1) from result group by studentno,subjectidhaving count(1)>1select studentno,subjectid,count(1) from result group by studentno,subjectidhaving count(1)=1create 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 empunionselect deptid,deptname,loc, 2 r from dept) eeorder by ee.r--联合查询union在联合之后如果两次查询出现了相同rowid行，那么只会出现一次union all在联合之后如果两次查询出现了相同rowid行，那么两次都会出现intersect在联合之后只显示两次查询都出现的rowid行minus在联合之后只显示A结果不包含B结果中rowid的行select * from emp where gender='男'unionselect * from emp where salary<6000select * from emp where gender='男'intersectselect * from emp where salary<6000select * from emp where gender='男'minusselect * from emp where salary<6000select * from emp where salary<6000minusselect * 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_charselect empname,job,to_char(birthday,'yyyy"年"fmmm"月"dd"日"'),to_char(salary,'$99999.00') from empselect to_char(sysdate,'yyyy"年"fmmm"月"dd"日" hh24:mi:ss') from dual--to_dateselect to_date('2016-6-6','yyyy-mm-dd') from dualinsert 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--逻辑函数--nvlselect studentname,nvl(address,'<未填写>') from student --nvl2select studentname,nvl2(address,'<已填写>','<未填写>') from student--decodeselect 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位进制随机数的字符串