Oracle学习笔记
第二章 SQL函数
转换函数
数据类型转换:1 隐式转换
2 显示转换
显示转换: to_date
to_number
to_char select to_char(hiredate,'YY-MM-DD') from emp;
NVL函数 将NULL值转换成一个实际的值
当数值与空值进行运算的时候 结果同样为空 这就需要在运算时避免使用空值
NVL(comm,0) 如果字段comm为空时 用0替代
NVL(hiredate,'2007-12-18')
DECODE函数 功能类似 CASE 或者 IF-THEN-ELSE 语句 但更容易
DECODE('表达式或值','结果1','返回值1',
'结果2','返回值2'...,
'结果n','返回值n','没有对应结果时的返回值');
函数嵌套
单行函数可以多重嵌套
Oracle调试时常用到的命令
运行SQL*PLUS常常碰到错误 需要调试 此时需要使用许多Oracle命令
1 save命令
SQL> save 'D:\empsql.txt' 把上次运行的sql语句 保存到 d 盘 empsql.txt文件中
2 get命令 读取文件
SQL> get 'D:\empsql.txt'; 读取硬盘上D:\empsql.txt 文件中的sql语句
加载sql语句但不执行 使用run命令可以执行
3 @ 命令 与get命令相似 不同的是@命令 加载sql语句并执行
SQL> @'D:\empsql.txt'; 加载empsql.txt文件 并运行其中sql语句
4 connect 连接命令 如果命令没有与其他命令重复时可以简写为conn
connect scott/tiger 连接到本机scott用户
conn 简写connect命令 sys/sys 连接到本机sys用户
conn oracle/oracle@oracle 如果加上连接字符串(@oracle)表明连接到远程服务器 如果不加则连接本地
5 在同一个服务器上有不同的用户名 用户之间权限是不同的 如果两个用户(MAIN,SCOTT)互相访问的话需要授权
授权语句:grant select on emp to MAIN; 把emp表查询权限 赋予MAIN
grant select,update,inster on emp to MAIN; 把emp表查询,修改,插入权限 赋予MAIN
访问其他用户语句:select * from SCOTT.emp; MAIN 访问SCOTT用户下的 emp表;
另外,grant resource to main;语句可以赋予用户main当前的资源创建(创建视图的时候可用)
6 exit 退出SQL*PLUS
7 clear screen 清屏命令 清除SQL*PLUS页面上所有显示文字
问 ed 是什么命令?
第三章 数据库锁及表分区
数据库锁:
锁的原因:多个客户端同时访问一个表时 同时执行事务处理 有的查询 有的修改等 同时操作 产生冲突
共享资源:不仅指数据库行,而且也指其他的资源(如表)
当多个用户同时访问数据库中的同一资源时,就会产生对同一资源的访问的冲突.为了防止资源被破坏,
数据库采取了解决访问冲突的机制,称之为锁.
更改提示符(SQL>)显示: "set sqlprompt MAIN>" 把提示符改为 "MAIN"
锁:
行级锁 锁住部分行 行被排他锁定
在某行的锁被释放之前,其他用户不能
修改此行
使用commit或rollback释放锁
获取行级锁:
1 使用 insert,update 自动锁
2 select...for update (of ename) 语句自动获取行级锁
锁住查询得到的行数据 在表的一行或多行上放置排他锁
用于防治其他用户更新该行 但其他用户可以执行除更新之外的其他操作
"of ename" 表示可以锁定规定的字段
3 select...for update wait 15
锁住其他用户更新操作15秒 15秒以后其他用户会返回错误信息.
表级锁 锁住整个表 保护表的数据
在多个用户同时访问数据时确保数据的完整性
使用commit或rollback释放锁
三种模式可设置: 语法:Lock table
1 共享锁模式
语法:Lock table
例如:SQL>LOCK TABLE scott.employees IN share MODE;
允许多个用户同时对一个表放置共享锁
当A用户对表锁定 其他用户只可以查询数据 不能 更新 插入 删除 数据
当有多个用户对表同时上锁 那么所有的用户都只能执行查询操作
2 共享更新锁模式
语法: Lock table
锁定要被更新的行
允许其他用户同时查询 插入 更新未被锁定的行
在SELECT语句中使用"FOR UPDATE"子句,可以强制使用共享更新锁
允许多个用户同时锁定表的不同行
3 排他锁模式
语法:Lock table
限制性最强 仅允许其他用户查询数据 不允许插入 删除 更新操作
在同一时间仅允许一位用户在表上放置排他锁
注: 在使用排他锁时,可加参数"nowait"
例:Lock table test in exclusive mode nowait;
如果表已被其他用户锁住时 不等待直接返回报错信息
Oracle表操作:
Oracle数据库存储数据的方式: 规则表 分区表 索引组织表 簇
Oracle数据库中的数据类型:
VARCHAR2(size): 可变字符数据
CHAR(size): 固定长度字符数据
NUMBER(p,s): 数值型 例如 NUMBER(5,3)的最大值为99.999
DATE 日期时间型
LONG 可变长度的字符型 最大2G Oracle7.0以后支持
CLOB 单字节的大型字符对象 最大4G
ROW and LONG RAW 二进制型
BLOB 二进制大型对象,最大可达4G
BFILE 二进制数据,在数据库外部存储文件 最大4G
表的创建和维护
创建表: CREATE TABLE 表名 (字段1 类型1,字段2 类型2,...,字段n 类型n)
` create table emp as select * from scott.emp; //在另一个用户下创建用户scott下emp表的复制表
create table student (stuno int,stuname varchar2(10) not null,stubirth date
default to_date('1980-1-1','YYYY-MM-DD'));
添加表字段: ALTER TABLE 表名 ADD(字段1 类型1,... ...
,字段n 类型n);
alter table student add(stuphone varchar(18));
修改表字段: ALTER TABLE 表名 MODIFY(字段名,类型);
alter table student modify(stuname varchar(12));
删除表字段: ALTER TABLE 表名 DROP COLUMN 字段1,字段2,...;
alter table student drop column stuphone;
注意:当有其他用户操作或使用表时 Oracle不允许本用户删除表字段 此时可把字段设置为无效
ALTER TABLE 表名 SET UNUSED COLUMN 字段名;
alter table student set unused column stuphone;
当某字段设置为"无效"即不可恢复 且在查看(desc)表结构时该字段不会显示
要删除表中已经设置为"无效"的字段 可使用:
ALTER TABLE 表名 DROP UNUSED COLUMNS;
alter table student drop unused columns;
表增加约束: ALTER TABLE 表名 ADD CONSTRAINT PK_字段名 PRIMARY KEY(字段名);
alter table student add constraint pk_stuno primary key(stuno);
删除约束: ALTER TABLE 表名 DROP CONSTRAINT PK_字段名;
alter table student drop constraint pk_stuno;
查看表约束:desc user_constraints;(数据字典中提供)
表分区:
Oracle允许用户对表进一步的规划,即对表进一步拆分,将表分成若干个逻辑部分,每个部分称其为表分区
优点:
1 增强可用性,单个分区出现故障,不影响其他分区;
2 数据在各个分区合理的分布 使得I/O负载平衡,不同的分区可以映射到不同的硬盘;
3 改善性能 提高效率;
第一种方案:范围分区发
1 根据表中某一列的值得范围对表进行分区
2 分区时,首先依据列中的值可能的范围进行划分
例如: 对某省参加四六级考试的成绩进行整理(按得分数 60以下 60-80 80-100 )并保存至数据库
注:范围分区的字段也可以是 时间类型
范围分区语法:
Create table Student ( //创建表 Student
Studentid integer not null, //非空字段 Studentid
Studentname varchar2(20), //字段 Studentname
Score integer //字段 Score
)
Partition by range(Score) ( //以字段Score 作为分区标准
Partition p1 values less than(60), //p1区为<60分
Partition p2 values less than(80), //p2区为>60 and <80分
Partition p3 values less than(maxvalue) //范围分区 要考虑空值 "maxvalue" 包括最大值及空值等
);
第二种方案:散列分区法
1 散列分区法提供了一种通过指定分区编号来均匀的分布数据的方法 比较复杂
2 通过Hash函数将数据映射到相应的分区上 具体映像过程怎么操作 不需要知道
3 使得数据均匀的分布到各分区上,个分区大小趋向一致
散列分区语法:
Create table department ( //创建表 department
Deptno int,
Deptname varchar2(20);
)
Partition by hash(deptno) ( //"deptno"是散列分区的字段 hash函数是由oracle提供的
Partition p1, //具体 p1 p2 分区怎么划分 由hash函数决定
Partition p2
);
第三种方法:复合分区法
复合分区先对数据进行范围分区,然后在每个子分区又进行散列分区的一种表分区方法
复合分区语法:
Creat table salgrade
(grade number,losal number,hisal number)
Partition by range(grade) //首先进行范围分区 以工资级别"grade"为分区标准
Subpartition by hash(losal,hisal) //在范围分区又进行散列hash分区 losal-最低工资标准 hisal-最好标准
(
Partition p1 values less than(10), //定义范围分区 p1
(subpartition sp1,subpartition sp2), //把范围分区 再散列分区分为 sp1 sp2
partition p2 values less than(20)
(subpartition sp3,subpartition sp4)
)
第四种分区方法:列表分区
1 列表分区允许用户明确地控制行到分区的映射
2 不同于范围分区或散列分区,它允许按自然方式对无序和不相关的数据集进行分组和组织
例如:在客户表中记录着客户的国别信息 中国,美国,法国,英国,加拿大 那么在创建表时
我们可以对表进行列表分区
列表分区语法:
Create table customer ( //创建表 customer
custNo int,
custname varchar(20),
cusrState varchar(20)
)
Partition by list(custState) ( //以字段 custState 划分列表分区
Partition asia values('中国','韩国','新加坡'), //asia列表区 只能出现 中国等
partition Europe values('英国','法国','德国'),
partition ameria('美国','加拿大','墨西哥')
)
注:向已经分区的表中插入数据 与普通的一样 新加入的数据会自动分配到所属的分区
查询分区表数据 可以按照普通的方法 查询所有数据 也可以只查询某个分区的数据
例如:Select * from student partition(p1) //只查询分区p1 中的数据
SQL> desc user_tab_partitions; 查看数据字典中 包含的表分区信息
SQL> select table_name,partition_name,partition_position from user_tab_partitions; 通过数据字典查看分区的表名 分区名等
分区表维护:
添加分区: Alter table student add partition p5 values less than(120);
删除分区: Alter table student drop partition p4;
截短分区: Alter table student truncate partition p5;
合并分区: Alter table student merge partitions p3,p4 Into partition p6;
设计到表分区的数据字典:
User_tab_partitions;
User_ind_partitions; 查询表分区的索引信息
第四章 数据库对象
课程目标
同义词
序列
视图
索引
簇
同义词: 数据库对象的替换名称 实
质上就是别名
优点: 简化SQL语句
隐藏对象的名称和所有者
为分布式数据库的远程对象提供了位置透明性
提供对对象的公共访问
同义词类型:
私有: 在某一个用户下创建的同义词 只为这个用户使用
共有: 由DBA(数据库管理员)创建的同义词 并授予权限至所有用户
CREATE SYNONYM 同义词 for 原表名 ; 命令用于创建同义词
例如:create public synonym employee for scott.emp; 把scott用户下emp表 创建"公共"同义词为employee
DROP SYNONYM 命令用于删除同义词
例如:Drop synonym employee;
USER_SYNONYMS 数据字典 包含有关同义词的信息
序列:
序列是能够自动产生连续唯一值的数据库共享对象
序列用于为主键提供值
创建序列语句: CREATE SEQUENCE 序列名
[INCREMENT BY n] //增长间隔数 默认是1
[START WITH n] //增长开始数
[{MAXVALUE n | NOMAXVALUE}] // "MAXVALUE" 是指定序列的最大值 如果不限定最大值使用'NOMAXVALUE' 默认不限定最大值
[{MINVALUE n | NOMINVALUE}] // "MINVALUE" 指定序列最小值 默认为0 一般序列多数向上增长 也有向下减少 递减方式
[{CYCLE | NOCYCLE}] //设定是否循环
[{CACHE n | NOCACHE}] //如果一个序列多个用户访问 增长较快 可设定缓存区大小
删除序列: drop sequence 序列名;
序列的两个重要属性:
NEXTVAL 返回下一个可用的序列值 或初始化序列值
CURRVAL 获取当前的序列值
对于一个新定义的序列必须先用NEXTVAL产生值 再可以用CURRVAL返回当先值
调用这两个方法的时候通常用-- 序列名.nextval 或 序列名.currval
修改序列定义: Alter Sequence
删除序列: Drop Sequence
序列的数据字典:USER_SEQUENCES 查看数据字典用 desc user_sequences;
视图:
用来显示来自一个或多个表的数据 也称为"虚拟表"或"已存储的查询" 大部分应用在分布式数据库中
基表: 创建视图所依据的表
视图的优点:
提供了另为一种级别的表安全性
隐藏数据的复杂性
简化用户的SQL命令
将应用程序与基表定义的修改隔离开来 限定了表的修改
从另一个角度提供数据
视图的创建:
CREATE OR REPLACE VIEW 语句用于创建视图 其中"OR REPLACE"表示如果这个用户下视图存在则替换 否则即创建
ORDER BY 子句可以用来按特定的顺序对行进行排列
例如:create or replace view v_emp as select empno as 编号,ename as 姓名
from scott.emp where deptno=10;
带有错误的视图 : 使用"FORCE"可以强制创建一个带有错误的视图
FORCE 选项用于创建带有错误的视图
查询引用不存在的表
查询引用无效
列
创建视图时没有权限
例如,可以用一个不存在的 基表 创建视图 CREATE OR REPLACE FORCE VIEW v_ss as select * from tt; //表tt不存在
联接视图:
通过联接多个表来创建视图
DML 只能修改一个基表
键保留表: 视图涉及到多张表关联 所有的表中的主键全部出现在视图当中
联机视图中的表,其主键列显示在联接视图中
视图中的函数
在视图中可以使用函数和表达式
分区视图
数据存储在单独的表分区中
在运行期间,使用UNION ALL 将这些表分区联合起来
例如:create or replace view UnionView as select * from temp union all select * from temp1;
表temp和temp1 结构相同 可分区的字段相同
DROP VIEW 语句
从数据库中删除视图
有关视图的信息(数据字典)
查询 USER_VIEWS desc user_views;
注: alter view 视图名 compile; 重新编译视图 一般用于 基表 结构改变等原因而导致的视图失效或暂时停用
索引:
又称为"快表" 提供了 快速检索表中数据的机制 快表也是一张表 可以存储数据
1 索引与表是相关联的
2 加快SQL语句的执行
3 减少磁盘I/O
4 CREATE INDEX 语句用于创建索引
5 在逻辑上和物理上独立于表中的数据
索引从物理上讲 确实存储在oracle物理文件上 oracle后台有数据文件存储了索引
当表中的数据增加时 索引也增加 当表非常大时 索引页非常大
Oracle在创建数据库 划分表空间时 单独划出索引表空间
6 Oracle 自动维护索引
7 普通索引的过程(除索引组织表): 索引和表的数据是分离的 检索数据是先到索引表中检索 速度快 再到表中把数据取出来
唯一索引
确保在定义索引的列中没有重复的值
起到唯一值约束的作用 当表中数据重复时 索引也会重复
Oracle 自动为主键和唯一键列创建唯一索引
CREATE UNIQUE INDEX 语句用于创建唯一索引
例如:create unique index idx_studentid on student(studentid); //创建表student中字段studentid的索引--idx_studentid
组合索引
将表中某几列的字段的值合起来构成一个索引 也称为"连接索引"
组合索引中的列可以按任意顺序排列
对于在where子句中包含多个列的查询,可以提高数据访问速度
例如:create index idx_empnoanddeptno on emp(empno,deptno); //创建表emp中字段empno,deptno 的索引--idx_empnoanddeptno
反向键索引 使用的不多 大多适合经常向表中添加数据而不适合经常修改数据 方向键索引修改数据效率比较低
反转索引列中的每一个字节
将数据插入操作分布在整个索引上
在创建索引时使
用REVERSE 关键字
标准索引 反向键索引
10001 10001
10002 20001
10003 30001
10004 40001
位图索引 极其特殊的一类索引 多用在重复率比较大的数据列上
为 低基数列(重复率比较大的数据列) 创建
可减少响应时间 降低空间占用
BITMAP INDEX 语句用于创建位图索引
例如: create bitmap index idx_job on emp(job); //为表emp中job列创建位图索引--idx_job
索引组织表 就是索引表与实际表合二为一
普通索引的过程: 索引和表的数据是分离的 检索数据是先到索引表中检索 速度快 再到表中把数据取出来
索引组织表的过程: 索引就是表中的数据 索引表的数据和表的数据是存储在一起的 表中的数据就是按照索引的顺序存储的 所以检索非常快 而且不需要另外的创建空间
表的数据存储在与其关联的索引中
对表数据的修改只会导致对索引的更新
基于主键进行搜索
CREATE TABLE 命令与 ORGANIZATION INDEX子句一起用于创建索引组织表
例如: create table indorg //创建表indorg
(
vencode int primary key, //创建主键vencode 并且作为索引列
venname varchar2(20)
)
organization index; //创建索引
索引组织表与普通表的对比:
普通表 索引编排表
ROWID唯一地标识行 主键唯一地标识行
隐式的ROWID列 没有隐式的ROWID列
基于ROWID的访问 基于主键的访问
顺序扫描返回所有行 完全索引扫描返回所有行 并按主键顺序排列
普通表可以储存在簇中 索引组织表不能存储在簇中
支持分发 复制和分区 不支持分发 复制和分区
基于函数的索引
基于一个或多个列上的函数或表达式创建的索引
表达式中不能包含聚集(聚合)函数(sum,count,avg,min,max等)
不能在LOB,REF或嵌套表列上创建
例如: create index idx_ename on emp(lower(ename)); //创建表emp字段ename小写函数索引--idx_ename
在查询时使用到相应的函数就会自动调用对应的函数索引
例如: select * from emp where lower(ename)='smith'; //查询时使用到了lower函数 检索时会自动使用函数索引
删除索引
drop index idx_studentid;
注: 在一个字段上创建多个索引 容易造成冲突
Oracle中每个表都有两个隐藏的字段 rowid 和 rownum
rowid 表中每一行的数据对应的唯一的标识符 代表了数据行的所有信息 如定位这个数据在哪一个数据库的哪一个表 哪一个文件的哪一个块上 是一个绝对地址
rownum 表示一个输出序列 按照行输出的顺序排列 一般情况下不会改变
使用rowid作为条件查询数据是
最快的 但很不常用
使用rownum检索数据时只能使用 '<' 或 '<=' 不能用'=' '>'等符号
使用rownum的注意:
select rowid,rownum,empno,sal from scott.emp where rownum<=5 order by sal desc; 先取出前5条数据 然后排序
select empno,sal from(select * from scott.emp order by sal desc) where sal is not null and rownum<=5; 先排序后 在取出前5个数据