oracle学习笔记_2019-07-17

创建表空间问题：11版本使用10闪退，使用相对应的oracle版本

表空间 数据文件 用户 方案
多个用户可用一个表空间
一个表空间可以存储多个数据文件
通常一个用户用一个表空间
方案是用户所拥有的的数据库对象的集合

10.2.0 Oracle Enterprise Manager Console
11.2.0 em浏览器 plsql

1.设置表空间
2.创建用户
3.设置用户指令
4.角色 对象授权设置权限 DBA权限 连接权限 CONNECT
EXP_FULL_db 导出权限
先建表空间
临时表空间
默认表空间temp

表名
字段信息 （自增number型主键、name、birthday、…和其他表的关系所属班级、）
四个必须的字段
主键、创建人、创建日期、备注
列名 有意义
类型
约束

常用数据类型 varchar2 number date （不推荐*大字段 clob blog二进制数据存储）

伪列
ROWID 修改数据 for update 开关锁 提交 不倡导
ROWNUM 序列号

Oracle操作符
Union 组合每个查询的所有行，并删除重复行

Order by
单列 多列 列别名 列位置

部门表
部门号 deptno DEPTNO NUMBER(6) not null,
部门名 dname DNAME VARCHAR2(20),
所属部门编号 upperdeptno UPPERDEPTNO NUMBER(6),
创建日期 cdate CDATE DATE,

员工表
员工号 empno EMPNO VARCHAR2(10) not null,
员工名 ename ENAME VARCHAR2(20),
所属部门号 deptno DEPTNO NUMBER(6),
上级名 mgr manager MGR VARCHAR2(10),
工作名 job JOB VARCHAR2(10),
奖金数 comm COMM NUMBER(12,3),
工资数 sal SAL NUMBER(12,3),
入职日期 hiredate HIREDATE DATE,
工资等级表
GRADE NUMBER(3) not null,
LOSAL NUMBER(12,3) not null,
HISAL NUMBER(12,3) not null,

–查询表dept

select t.* from dept t
Select t.* from dept t

–复制表结构及数据

create table dept1 as
select t.* from dept t
Create table detpt1 as select t.* from dept t

–复制表数据

create table dept2 as
select t.* from dept t where t.dname='华东'
Create table dept2 as select t.* from dept where t.dname=’华东’

–只复制表结构

create table dept3 as
select  t.* from dept t where 1=2

–查询创建时间最早的前三条记录

select * from 
(select * from dept t order by t.cdate) 
where rownum<4

–修改数据for update

--select * from dept2 for update 开锁、修改、关锁、提交commit

–删除表数据，不需要提交

truncate table dept1

–删除表数据，需要提交

delete dept3

–删除表

drop table dept2

–查询部门号为201或202的人

select * from emp where deptno = 201 or deptno = 202
select * from emp e where e.deptno in (201,202)

–查询不在201 202下的所有人

select * from emp e where e.deptno not in (201,202)
select * from emp e where e.deptno !=201 and e.deptno !=202

–查询工作岗位是办事员的姓名和年薪

select e.ename 姓名,nvl(e.sal,0)*12+nvl(comm,0)年薪 from emp e where e.job='CLERK' 

(注：where后的条件数据，大小写格式需与表中数据一致)

–查询500-1500工资的人员信息
between and

select * from emp e where e.sal>=500 and e.sal <= 1500 

–查询存在dep表中的所有人员

select * from emp2 e where exists(select 1 from dept d where d.deptno=e.deptno)
select * from emp2 e where e.deptno in (select d.deptno from dept d where d.deptno=e.deptno)

–查询存在dep表中的所有人员

select * from emp2 e where exists (select 1 from dept d where d.deptno = e.deptno)
select * from emp2 e where e.deptno in (select deptno from dept d )

–查询不存在dep表中的所有人员

select * from emp2 e where e.deptno not in (select deptno from dept d)

–查询姓李的人员

select * from emp e where e.ename like '李%'

–查询工资为空的人员信息

select * from emp2 e where e.comm is null 

–查询雇用日期为空的人员信息

select * from emp2 e where e.hiredate is null

–查询雇佣日期不为空的人员信息

select * from emp2 e where e.hiredate is not null 
select * from emp2 e where e.hiredate is not null

–去重组合查询

select * from emp e1 union select * from emp2 e2

–非去重组合查询

elect * from emp e1 union all select * from emp2 e2

–求差集,会展示出不一样的部分，多用单个字段

select e2.* from emp2 e2 minus select e1.* from emp e1
select e2.empno from emp2 e2 minus select e1.empno from emp e1

–求交集

select e2.* from emp2 e2 intersect select e1.* from emp e1
select e2.* from emp2 e2 for update 

–查询工资最高的第2-5条记录

select ename, sal from
(
 select ename,sal,rownum r from
 (select ename,sal from emp order by nvl(sal,0) desc)
)
where r<=5 and r>=2

–查询工资最高的第2-5条记录

--select ename, sal from(select ename,sal,rownum r from(select ename,sal from emp order by nvl(sal,0) desc))where r<=5 and r>=2

–查询雇佣日期在2011前的所有人员信息(比较时字符串匹配)

--select * from emp2 e2 where to_char(e2.hiredate,'yyyy') < '2011'

–**查询在2012年入职的姓刘或姓李的人员名称

--select * from emp e where to_char(e.hiredate,'yyyy')='2012'and (e.ename like '刘%'or e.ename like '李%')
--SELECT * FROM EMP E WHERE TO_CHAR(E.HIREDATE,'YYYY')='2012' AND (E.ENAME LIKE '刘%' OR E.ENAME LIKE '李%')

–**查询2009年后入职的办事员和经理的信息

--select * from emp2 e2 where to_char(e2.hiredate,'yyyy')>'2019'and e2.job in('CLERK','MANAGER')
--SELECT * FROM EMP E WHERE TO_CHAR(E.HIREDATE,'YYYY')>'2009' AND (E.JOB = 'MANAGER' OR E.JOB = 'CLERK')

–查询2012年2月入职的人员姓名

--select e2.ename from emp2 e2 where to_char(e2.hiredate,'yyyy-mm')='2012-02'

–查询2012-12-17入职奖金大于500

--select e2.ename from emp2 e2 where to_char(e2.hiredate,'yyyy-mm-dd')='2012-12-17'and e2.comm>500

–查询没有奖金的人员信息

--select * from emp2 e2 where e2.comm is null 
--select * from emp2 e2 where nvl(e2.comm,0) !=0
--select * from emp2 e2 where trim(e2.ename) is not null 

–使用日期函数

--select t.deptno,t.dname,to_char(t.cdate,'yyyy-mm-dd') datee from dept t

–yyyy-mm-dd hh24:mi:ss

--select t.deptno,t.dname,to_char(t.cdate,'yyyy-mm-dd hh24:mi:ss') datee from dept t
--select t.deptno,t.dname,to_char(t.cdate,'yyyy-mm-dd hh24:mi:ss') datee from dept t

–查询当前时间,数据库服务器的时间,以web服务器为准

--select sysdate from dual
--select to_char(sysdate+2,'yyyy-mm-dd') as thedayaftertomorrow from dual

–去重

--select distinct e.ename from emp2 e
--select distinct e.ename,e.empno from emp2 e

–如果雇佣日期为空，设为明天

--select nvl(e.hiredate,sysdate+1) from emp2 e
--select to_char(nvl(hiredate,to_date(to_char(sysdate+1,'yyyy-mm-dd'),'yyyy-mm-dd')),'yyyy-mm-dd hh24:mi:ss') from emp0

–连接字符串,先打 ‘此员工的姓名为：【】 ’ 然后在需要加字段的位置加入’|| 字段名 ||’

--select '此员工的姓名为：【'|| e.ename || '】' from emp e

–谁的岗位是什么

--select '【'||e.ename||'】的岗位是'||e.job||'' from emp e

–******谁的岗位是什么，年薪是。。。若工资为空，默认设为1000元

--select '【'||e.ename||'】的岗位是'||e.job||',年薪是'||(nvl(e.sal,1000)*12+nvl(e.comm,0))||''from emp e

–****查询所有人员信息并按照工资有大到小奖金有小到大排序

--select * from emp e order by nvl(sal,0) desc, nvl(comm,0) 

–****年薪由大到小排列，若工资为空设为1000

--select e.ename ,(nvl(e.sal,1000)*12+nvl(e.comm,0)) yearsal from emp e order by (nvl(e.sal,1000)*12+nvl(e.comm,0)) desc

--select * from emp e order by e.ename||e.empno

–根据别名进行排序

--select e.ename ,(nvl(e.sal,1000)*12+nvl(e.comm,0)) yearsal from emp e order by yearsal desc

–**根据列位置进行排序 order by 后的1 为第一列

--select to_char(deptno) temp,dname from dept union select empno,ename from emp order by 1