oracle回顾

Oracle

oracle的表是基于用户管理的

mysql的表是基于库管理的

基本语句

表空间

创建表空间

-- tablespace:表空间名字
-- datafile：存放位置  
-- size : 表空间大小
-- autoextend: 自动扩展大小
-- next ： 每次扩展多大
create tablespace tstest
datafile 'd:\tstest.dbf'
size 100m
autoextend on
next 10m;

• 这时候删除d:\tstest.dbf文件显示文件正在使用 使用命令

删除表空间

drop tablespace tstest

• 再删除文件

创建用户

-- user  用户名
-- identified 密码
-- tablespace 用户属于那个表空间
create user zhaoxingyu
identified by 123456
default tablespace tstest;

常用的角色

connect --连接角色
resource -- 开发者角色
dba  -- 超级管理员角色 

• 不授权是不能登录的

给用户授权

-- 给予zhaoxingyu用户dba角色
grant dba to zhaoxingyu;

数据类型

1. 字符类型

数据类型	长度	说明
CHAR(n BYTE/CHAR)	默认1字节，n值最大为2000	末尾填充空格以达到指定长度，超过最大长度报错。默认指定长度为字节数，字符长度可以从1字节到四字节。
NCHAR(n)	默认1字符，最大存储内容2000字节	末尾填充空格以达到指定长度，n为Unicode字符数。默认为1字节。
NVARCHAR2(n)	最大长度必须指定，最大存储内容4000字节	变长类型。n为Unicode字符数
VARCHAR2(n BYTE/CHAR)	最大长度必须指定，至少为1字节或者1字符，n值最大为4000	变长类型。超过最大长度报错。默认存储的是长度为0的字符串。
VARCHAR	同VARCHAR2	不建议使用

2. 数字类型

数据类型	长度	说明
NUMBER(p[,s])	1-22字节。P取值范围1到38S取值范围-84到127	存储定点数，值的绝对值范围为1.0 x 10 -130至1.0 x 10 126。值大于等于1.0 x 10 126时报错。p为有意义的10进制位数，正值s为小数位数，负值s表示四舍五入到小数点左部多少位。
BINARY_FLOAT	5字节，其中有一长度字节。	32位单精度浮点数类型。符号位1位，指数位8位，尾数位23位。
BINARY_DOUBLE	9字节，其中有一长度字节。	64位双精度浮点数类型。

3. 时间、时间间隔类型

时间字段可取值范围：

时间字段	时间类型有效值	时间间隔类型有效值
YEAR	-4712至9999，包括0	任何整数
MONTH	01至12	0至11
DAY	01至31	任何整数
HOUR	00 至 23	0 至 23
MINUTE	00 至 59	0至 59
SECOND	00 to 59.9(n)，9(n)不适用与DATE类型	0 to 59.9(n)
TIMEZONE_HOUR	-1至14，不适用与DATE和TIMESTAMP类型	不可用
TIMEZONE_MINUTE	00至59，不适用与DATE和TIMESTAMP类型	不可用
TIMEZONE_REGION		不可用
TIMEZONE_ABBR		不可用

时间、时间间隔类型：

数据类型	长度	说明
DATE	7字节	默认值为SYSDATE的年、月，日为01。包含一个时间字段，若插入值没有时间字段，则默认值为：00:00:00 or 12:00:00 for 24-hour and 12-hour clock time。没有分秒和时间区。
TIMESTAMP [(fractional_seconds_precision)]	7至11字节	fractional_seconds_precision为Oracle存储秒值小数部分位数，默认为6，可选值为0到9。没有时间区。
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	13字节	使用UTC，包含字段YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	7至11字节	存时使用数据库时区，取时使用回话的时区。
INTERVAL YEAR [(year_precision)] TO MONTH	5字节	包含年、月的时间间隔类型。year_precision是年字段的数字位数，默认为2，可取0至9。
INTERVAL DAY [(day_precision)]TO SECOND [(fractional_seconds_precision)]	11字节	day_precision是月份字段的数字位数，默认为2，可取0至9。

1. TO_DATE()、DATE使用的时间字段值都是午夜值。或者使用TRUNC()函数进行过滤，确保时间字段为午夜值。
2. 时间和时间间隔类型操作规则：

在DATE和TIMESTAMP（会被转化为DATE类型值）类型上加、减NUMBER类型常量，该常量单位为天数。

所有TIMESTAMP类型运算都以UTC时间为准。即对于TIMESTAMP WITH LOCAL TIME ZONE来说，先转化为UTC时间，计算完成后再转化回来。

1. INTERVAL YEAR TO MONTH常量：

INTERVAL‘year-month’YEAR/MONTH(precision) TO MONTH

year位数超过precision时，返回一个错误。

其中precision为最大的位数，默认为2，可取0到9。

例子：INTERVAL ‘123-2’ YEAR(3) TO MONTH 、

INTERVAL ‘123’ YEAR(3) 、

INTERVAL ‘300’ MONTH(3)。

1. INTERVAL DAY TO SECOND常量：

INTERVAL ‘n/time_expr/n time_expr’ DAY/HOUR/MINUTE(leading_precision) TO HOUR/MINUTE/SECOND(fractional_second_precision)

INTERVAL ‘n/time_expr’ SECOND(leading_precision, fractional_second_precision)

time_expr格式：HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n] 若n大于分秒精度，则四舍五入n。

只有当第一个字段是DAY时，才可以使用n time_expr。

leading_precision默认为2，可取0至9。

4. 大对象类型

数据类型	长度	说明
BLOB	最大为(4GB-1)*数据库块大小	存储非结构化二进制文件。支持事务处理。
CLOB	最大为(4GB-1)*数据库块大小	存储单字节或者多字节字符数据。支持事务处理。
NCLOB	最大为(4GB-1)*数据库块大小	存储Unicode数据。支持事务处理。
BFILE	最大为2 32-1字节	LOB地址指向文件系统上的一个二进制文件，维护目录和文件名。不参与事务处理。只支持只读操作。

1. LOB列包含一个LOB地址，指向数据库内或者数据库外的LOB类型值。

5. 其他类型

数据类型	长度	说明
LONG	最大为2GB	变长类型，存储字符串。创建表时不要使用该类型。
RAW(n)	最大2000字节，n为字节数，必须指定n	变长类型，字符集发生变化时不会改变值。
LONG RAW	最大为2GB	变长类型，不建议使用，建议转化为BLOB类型，字符集发生变化时不会改变值。
ROWID	10字节	代表记录的地址。显示为18位的字符串。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。
UROWID(n)

1. ROWID：数据对象编号32位(6个字符)、文件编号10位(3个字符)、块编号22位(6个字符)、行编号16位(3个字符)
2. 使用dqms_rowid包获得ROWID信息：

select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, dbms_rowid.rowid_row_number(rowid) num from dept;

创建表

-- 创建表 person
create table person(
       pid number(20),
       pname varchar2(10)
);

增删改

insert into person(pid,pname) values(1,'小明');
commit;

• 凡是增删改都需要提交事务

update person set pname='小黑' where pid = 1;
commit;

删除

-- 删除表数据
delete from person;
-- 删除表
drop table person;
-- 删除表数据  （理解为删除表再创建表结构）
truncate table person;

序列

创建序列

-- increment by 每次加几
-- start with 从几开始
-- cache 2 先缓存后面两个
create sequence s_person
increment by 2
start with 5
cache 2;

-- currval 查询当前序列到几了 
-- nextval 获得下一个序列
-- dual oracle的虚表 （在oracle查询时必须用from指定一个表）
select s_person.currval from dual;

scott

-- scott用户介绍 密码默认是tiger
-- 解锁scott用户
alter user scott account unlock;
-- 解锁scott用户的密码  (设置scott用户的密码为tiger)
alter user scott identified by tiger;

查询

单行查询函数

单行函数： 作用于一行，返回一个值

-- 字符函数
select upper('yes') as "column" from dual;
select lower('YES') as "column" from dual;

-- 数值函数  
select round(26.5, -1) from dual; -- 四舍五入
select trunc(26.5,-1) from dual; -- 直接截取
select mod(10,3) from dual; -- 求余数

-- 日期函数  
-- 查询员工入职距离现在多少天
select sysdate-e.hiredate from emp e;
-- 明天的这个时间
select sysdate+1 from dual;
-- 距离现在有几月
select floor(months_between(sysdate,e.hiredate)) from emp e;
-- 距离现在多少年
select floor(months_between(sysdate,e.hiredate)/12) from emp e;
-- 距离现在有几周
select trunc((sysdate-e.hiredate)/7) from emp e;

-- 转换函数
-- 日期转字符串 不想要时间里的0 加上fm
select to_char(sysdate,'fm yyyy-MM-dd hh24:mi:ss') from dual;
-- 日期转字符串 
select to_date('2021-7-4 14:7:56','fm yyyy-mm-dd hh24:mi:ss') from dual;
-- 处理空值nvl  (如果e.comm是null就设置成0)
select e.sal*12+nvl(e.comm,0) from emp e;

• 坑点日期处理：
  • oracle中不区分大小写
  • 日期中的分钟和月 mm MM 一个意思都是获取月的
  • oracle分钟使用mi
  • 24小时制使用hh24
  • 不想显示时间首位是0的加上 fm

条件表达式

case

（oracle和mysql case一样）

-- 语法 
select e.ename,case e.ename
	when 'SMITH' then '张三'
	when 'ALLEN' then '李四'
    else '其他名称'
    end as as_name
from emp e;

-- 不带列名
select e.sal,case
	when e.sal>3000 then '高收入'
	when e.sal>1500 then '中等收入'
    else '低收入'
    end
from emp e;

decode

select e.ename,decode( e.ename,
	 'SMITH' , '张三',
	 'ALLEN' , '李四',
     '其他名称'
    ) as as_name
from emp e;

• oracle起别名出要么不加引号 要么只能加双引号

多行函数

-- 多行函数【聚和函数】： 作用于多行，返回一个值
select count(1) from emp;  
select sum(sal) from emp;  
select min(sal) from emp;  
select max(sal) from emp;  
select avg(sal) from emp; 

分组查询

查询的列只能是分组的列 除非在聚和函数中查询

条件语句中不能使用列的别名

-- 按照部门分组，查询大于平均薪资大于2000的部门
select e.deptno ,avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000

连接查询

-- 笛卡尔积  14*4两表数据相乘
select * from 
emp e,dept d;
-- 等值连接
select * from 
emp e,dept d
where e.deptno = d.deptno;
-- 内连接
select * from 
emp e inner join dept d
on e.deptno = d.deptno;
-- 左外连接
select * from 
dept d left join emp e 
on e.deptno = d.deptno;
-- oracle专用的内连接 (+)在那边就以那张表为准
select * from 
emp e,dept d
where e.deptno(+) = d.deptno;

自查询

-- 查询员工名称和员工对用领导的名称
select e1.empno 员工编号, e1.ename 员工姓名,e1.mgr 员工领导编号,
        e2.empno 领导编号, e2.ename 领导名称,d2.dname 领导的部门
from emp e1,emp e2,dept d1,dept d2
where e1.mgr = e2.empno
and d1.deptno = e2.deptno
and d2.deptno = e1.deptno;

子查询

-- 查询出工资和SCOTT一样的员工
select * from emp where sal = (
select sal from emp where ename='SCOTT')
and ename<>'SCOTT'

-- 查询和10号部门任意员工薪资一样的人
select * from emp where sal in (
select sal from emp where deptno=10)
and deptno<>10

-- 查询每个部门最低工资，和最低工资的员工姓名和所在部门名称
select e.ename,e.sal,d.dname
from (select deptno ,min(sal) msal
      from emp
      group by deptno) mt, emp e,dept d
where mt.deptno = e.deptno
and mt.msal = e.sal
and d.deptno = e.deptno;

分页

rownum

• rownum关键字只能做< <=的判断，不能进行> >=的判断

-- 如果rownum中有排序 在套一层循环（先执行select 中的rownum 在排序顺序就乱了）
select * from (
    select rownum rn,t.* from (
    	select ename,sal from emp order by sal desc) t
)
where rn between 5 and 10

-- 或者
select * from (
    select rownum r,t.* from (
        select ename,sal from emp  order by sal)
     t where rownum<=10 ) 
where r>5

分页规律总结：每页显示m条数据，查询第n页数据

select * from (select rownum r,e. * from 要分页的表 e where rownum<=m*n) t where r>m*n-m ;

视图

视图就是一个提供查询的窗口，所有数据都来来自于原表

-- 跨用户创建表
create table emp as (select * from scott.emp);

-- 创建视图
create view view_emp as (select ename,sal from emp)
-- 查询视图
select * from view_emp
-- 修改视图 （就是修改原表中的数据）
update view_emp set sal='10000' where ename='SMITH'
commit;
-- 为了安全 创建只读视图 （with read only;）
create view view_emp2 as (select ename,sal from emp) with read only;

简单索引

-- 单列索引
-- 为ename创建索引
create index idx_ename on emp(ename);

-- 复合索引
create index idx_enamejob on emp(ename,job);