数据库
设置用户密码
//用户名
system
//密码
zyz
zyz
登录
sqlplus
是oracle自带的工具,可以使用其命令登录到oracle数据中,并执行用户编写的sql语句。 查看系统中 sqlplus 命令的所在位置:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SxuHOP6N-1638151840433)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210708111628425.png)]
注意,如果提示没有sqlplus命令,那么说明环境变量path中,没有配置此路
sqlplus 用户名/密码
//普通用户登录
sqlplus briup/密码
//管理员用户登录
sqlplus system/密码
//使用DBA的身份登录,这种情况不需要密码,但是这种情况需要当操作系统的用户有权限才行
sqlplus "/as sysdba"
登陆成功后,可以查看当前用户是谁:
show user
登录成功后,如果要清屏:
//Windows系统中
$CLS
//或者
$cls
//Ubuntu系统中
!clear
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bmom952T-1638151840437)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210714202852528.png)]
退出
exit
用户
创建用户
//创建用户test1,设置密码test1
create user test1 identified by test1;
//把角色connect和resource授权给test1账号
grant connect,resource to test1;
这里角色代表相关权限的集合:
connect
角色,基本的连接resource
角色,程序开发DBA
角色,数据库管理
切换用户:
conn
//或者
conn 用户名/密码
删除用户:
drop user test1 cascade;
插入信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NWVNiu37-1638151840440)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210708113814498.png)]
要切到sql文件所在的目录登入
E:\java.sql.jdk\Oracle
然后使用导入sql文件命令: @table.sql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WfvS0AQb-1638151840441)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210708113936342.png)]
导入成功后,查看是否成功
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jLQZvv97-1638151840443)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210708114038697.png)]
注意,oracle中的表名、列名(字段名)、SQL语句等是不区分大小写的(大小写不敏感)
字符函数
函数 | 说明 |
---|---|
ASCII(X) | 返回X的ASCII码 |
concat(x,y) | 连接字符串x和y |
insert(str,x,start) | 返回字符串str中字符x的下标,start可以指定开始位置,(可不写) |
length(x) | 返回x的长度 |
lower(x) | 把x的内容转换成小写 |
upper(x) | 把x的内容转换成大写 |
initcap(x) | 把首字母转换成大写,其他字母小写 |
ltrim(str,x) | 把str左边的x子字符串截掉,ltrim(’=Hello=’,’=’)//运行结果:Hello= |
rtrim(str,x) | 把str右边的x子字符串截掉,ltrim(’=Hello=’,’=’)//运行结果:=Hello |
substr(str,start,len) | 返回str的子字符串,从start处开始,截取length个字符,缺省length,默认 到结尾 |
转换函数
等值连接
外连接
– 哪边加
+
符号,则另一边的表即会把没有匹配的也查询出
– 左边加+
符号,即时右外连接,注意,from
后面跟的表的顺序和where
条件等值连接的顺序要一致:
- 则可以直接改为左外连接:
left outer jion .. on
- 则可以直接改为右外连接:
right outer jion .. on
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+);-- 左外连接
select last_name,dept_id,name
from s_emp left outer join s_dept -- 左外连接
on s_emp.dept_id=s_dept.id;
insert into s_dept(id,name) values(60,'st');
select last_name,dept_id,name
from s_emp right outer join s_dept-- 右外连接
on s_emp.dept_id=s_dept.id;
select last_name,dept_id,name
from s_emp , s_dept
where s_emp.dept_id(+)=s_dept.id;-- 右外连接
全连接:
把两张表中没有相互匹配的数据也都显示出来
select last_name,dept_id,name
from s_emp full outer join s_dept
on s_emp.dept_id=s_dept.id;
把两个表的左外连接和右外连接取交集
自连接
伪列
oracle特有的
rownum
每个表中都会有这个列注意,只有以下几种用法
rownum=1;rownum>0;
rownum<number ;rownum<=number;
select last_name,salary,rownum
from s_emp
where rownum<=10
minus
select last_name,salary,rownum
from s_emp
where rownum<=5;
聚合函数:
分组 group by
-- 聚合函数 分组
select count(*),dept_id
from s_emp
group by dept_id
order by dept_id ;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qKoSoueZ-1638151840446)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210712100347612.png)]
select sum(salary),avg(salary),count(*),dept_id,max(salary)
from s_emp
group by dept_id;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GzhtzkJV-1638151840448)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210712100632333.png)]
select sum(salary),avg(salary),count(*),dept_id,max(salary)
from s_emp
where dept_id>10
group by dept_id;
执行顺序
where
子句在分组group
by之前的;
select语句执行顺序:
from
子句,组装来自表的数据,有可能是多张表where
子句,基于指定的条件对记录行进行筛选group by
子句,将数据划分为多个分组- 使用聚合函数对没个小组中的数据进行计算
having
子句,进行条件筛选,这里可以使用聚合函数的计算结果- 计算所有的运算表达式,主要是select部分
order by
子句,对结果集进行排序
select sum(salary),avg(salary),count(*),dept_id,max(salary)-- 3
from s_emp -- 1
where dept_id>40 -- 2
group by dept_id -- 4
order by avg(salary);-- 5
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3ShKOZZN-1638151840450)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210712101318665.png)]
select avg(salary),count(*),dept_id,max(salary)
from s_emp
where dept_id>40
group by dept_id -- 先分组
having avg(salary)>1000 -- 在分组之后执行的条件,聚合函数的计算结果
order by avg(salary);
聚合函数能够出现的位置:
- seelct 后面;
- having 后面;
- order by后面
注意,where后面一定【不能】出现组函数
注意,如果select、having语句后面出现了组函数,那么select、having后面没有被组函数修饰的 列,就必须出现在group by 后面 (非常重要)
where和having对比:
- where和having都是做条件筛选的
- where执行的时间比having要早
- where后面不能出现组函数
- having后面可以出现组函数
- where语句要紧跟from后面
- having语句要紧跟group by后面
group by和having的关系:
- group by可以单独存在,后面可以不出现having语句
- having不能单独存在,如果需要出现,那么就必须出现在group by后面
order by语句:
- 如果sql语句中需要排序,那么就一定要写在sql语句的最后面
- order by后也可以出现组函数
分组之后才可以使用组函数
注意:
select
中出现的列属性(会聚合函数)必须写道group by
后面
having
中出现的非聚合函数的条件的列属性,也必须添加到group by
后面
-- 查询平均工资大于1400的
select avg(salary),dept_id,s_dept.name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id
group by dept_id,s_dept.name
having avg(salary)>1400
order by dept_id;
-- 把连接查询放到having子句中,比前一种的查询效率更高
select avg(salary),dept_id,s_dept.name
from s_emp,s_dept
group by dept_id,s_dept.name,s_dept.id
having avg(salary)>1400 and s_emp.dept_id=s_dept.id
order by dept_id;
数据建模
命名
对应表和列的名字:
- 必须是字母开头;
- 必须是1-30个字符的长度;
- 表名中只能出现字母、数字、_、#
- 不能和数据库中已有的对象名字重复
- 不能是数据库中的关键字
数据类型
- 字符串:
char
存储数据的长度固定。varchar
是数据库标准类型,存储数据的长度可变;varchar2
存储数据的长度可变,是Oracle数据库中特有的;
char
效率比varchar
高;varchar2
不能存放空字符串,可以存null。varchar
可以存空字符串;
-
number
number(p,s)
p 表示最大位数(整数位+小数位), s表示保留的小数位(四舍五入),也可以为负数。例如,number(5,2) 存进去123.456,取出来为123.46 存进去12345.456,运行报错:值大于为此列指定的允许精度 注意1,其实这时候整数位最大只能有3位,因为要留俩位给小数位 注意2,可以直接使用number,不加参数,描述没有默认没限制
-
date
日期类型
-
blob
存二进制对象,例如视频,音频,图片等
-
clob
存储大文本,例如很多很多文字
注意,blob,和clob一般很少用,一般直接放文件的路径
约束
- 主键约束
PRIMARY KEY primary key
- 外键约束
FOREIGN KEY foreign key
- 唯一约束
UNIQUE unique
- 非空约束
NOT NULL not null
- check约束
CHECK check
约束又分为:
- 列级约束;
- 表级约束
not null
只能作为列级约束
create table
drop table table_name [cascade constraint]
当有外键时,强制级联删除
如果要声明的约束为联合主键、联合外键、联合唯一的时候,就一定要用表级约束
constraint
关键字:
- constraint是约束的意思
- 建表的时候可以给约束起一个名字,这个名字起的规律一般会是:表名_列名_约束类型
- 如果没有给约束起名字,那么系统也会给这个约束起一个默认的名字
- 将来我们可以根据之前给约束起好的名字,而找到这个约束,然后进行修改获取其他操作
create table student(
id number constraint student_id_pk primary key,
name varchar2(100) constraint student_name_nn not null,
email varchar2(100) constraint student_email_un unique,
gender char(1) constraint student_gender_ck check(gender in('f','m')),
age number,
birthday date
);
drop table student;
create table t_customer(
id number,
name varchar2(20) not null,
age number,
email varchar2(100),
gender char,
constraint cus_id_pk primary key(id),
constraint cus_email_un unique(email),
constraint cus_gender_ck check(gender in('f','m'))
);
create table t_order(
id number,
price number not null,
customer_id number,
constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);
drop table t_order;
drop table t_customer;
特殊
例如,建立一张表和s_dept一模一样,s_dept的表结构和表中的数据全部复制过来
create table test1
as
select * from s_dept;
例如,建立一张表和s_dept一模一样,只拿来s_dept的表结构,没有数据
create table test2
as
select * from s_dept
where 1=2;
例如,建立一张表和s_dept一模一样,只复制表中某几个指定列的数据
create table test3
as
select id,last_name,salary from s_emp;
语句分类
名称 | 解释 | 命令 |
---|---|---|
DDL(数据定义语句) | 定义管理数据对象,如数据库,数据表等 | CREATE,DROP,ALTER |
DML(数据操作语句) | 用于操作数据库对象中所包含的数据 | INSERT,UPDATE,DELETE |
DQL(数据查询语句) | 用于查询数据库数据 | SELECT |
DCL(数据控制语句) | 管理数据库的语言,包括权限管理,以及数据更改 | GRANT commit rollback |
DML语句:
数据操纵语句
DML用于改变数据库中的数据,主要是包括:
INSERT
UPDATE
DELETE
在插入数据和修改数据时都需要遵从约束;
insert
插入语句
insert into 表名(列1,列2,..) values(值1,值2,...);
例如,特殊的情况,把查询的结果,插入到表中
insert into t_user(id,name,birthday)
select id,last_name,start_date
from s_emp;
update
修改表中的数据
update 表名 set 列1=值1,列2=值2,.... where 条件;
如果不加条件,就表示把表中所有数据更新
delete
删除表中的数据
delete from 表名 where 条件;
注意,如果不加条件,就表示把表中所有数据删除
级联
当两张表级联时:
- A表含有B表的外键列:
- 插入外键列的值时,外键列的值必须在B表对应的属性列中出现过;
- update的where条件涉及到外键列时,也要看外键列所属于的表中也没有对应的值;
- delete也是一样;
-- 使用级联删除teacher表中的c_id字段
alter table teacher
drop column c_id cascade constraint;
drop table table_name [cascade constraint]
当有外键时,强制级联删除
delete on
on delete xxx
语句是在声明外键约束的时候使用的
-
on delete no action
如果在建外键的时候,不加on delete语句,默认就是 on delete no action
-
on delete cascade
create table t_customer( id number, name varchar2(20) constraint customer_name_nn not null, constraint customer_id_pk primary key(id) ); create table t_order( id number, price number, customer_id number, constraint order_id_pk primary key(id), constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete cascade ); insert into t_customer(id,name) values(1,'tom1'); insert into t_customer(id,name) values(2,'tom2'); insert into t_customer(id,name) values(3,'tom3'); insert into t_order(id,price,customer_id) values(1,1000,1); insert into t_order(id,price,customer_id) values(2,2000,2); insert into t_order(id,price,customer_id) values(3,3000,1); insert into t_order(id,price,customer_id) values(4,4000,1); insert into t_order(id,price,customer_id) values(5,5000,1); insert into t_order(id,price,customer_id) values(6,6000,1); delete from t_customer where id = 1;
这次删除成功了,同时级联(cascade)删除了,t_order表中所关联的那条数据
-
on delete set null
这次删除成功了,同时把t_order表中所关联的那条数据的外键设置为了
null
事务
事务的概述:
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
只有
DML
语句才会产生事务,其他语句不会产生事务
DML语句执行的时候,如果当前有事务,那么就使用这个事务。如果当前没有事务,则产生一个新事务
commit、rollback、DDL语句都可以把当前事务给结束掉
commit和DDL语句结束事务的方式是把这个事务给提交了,然后DML操作永久生效
rollback结束事务的方式是把这个事务给回滚了,默认回滚到事务开始的状态
特征
ACID特征
- 原子性(
Atomic
)
整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。
- 一致性(
Consist
)
一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。
- 隔离性(
Isolated
)
隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
- 持久性(
Durable
)
在事务完成以后,该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
rollback
在事务中,我们也可以设置多个回滚点,让事务回滚到指定的位置。
使用关键字 savepoint
DML语句1
savepoint A
DML语句2
savepoint B
DML语句3
rollback to A/B
rollback到回滚点之后,这个事务并没结束,这个时候还可以接着回滚或者commit提交事 务。
隔离
在数据库中,使用事务隔离级别,来解决事务在并发访问中,所产生的一些问题
一般会出现的问题:
- 脏读
- 不可重复读
- 幻读
脏读:主要针对update操作。一个事务A读到另一个事务B中修改过但是还没有提交的数据。
不可重复读:主要针对update操作,一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改 并提交了,所以就出现了事务A里面读一个数据俩次,但是读到的结果是不同的。
幻读:
在这种情况下,数据库中的事务隔离级别,就是来解决这些问题的:
-
read-uncommitted 不提交也能读
-
read-committed 提交之后才能读,解决了脏读
-
repeatable-read 解决了脏读和不可重复读
-
serializable 三个问题都解决了
级别越高解决的问题越多但是效率越低
没有特殊说明不去设置事务的隔离级别
Set Transaction Isolation Level Read Uncommitted
Set Transaction Isolation Level Read Committed
Set Transaction Isolation Level Read Repeatable
Set Transaction Isolation Level Serializable
oracle里面默认的事务隔离级别是第二种:read-committed
mysql里面的默认的事务隔离级别是第三种:repeatable read
alter
在表创建好的情况下,可以使用 alter 关键字,来修改表的信息。
使用
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
drop table t_user;
在表创建好的情况下,可以使用 alter 关键字,来修改表的信息。
alter table t_user
add birthday date;
删除表的某列
alter table t_user
drop column birthday;
给表中的列添加约束
alter table t_user
add constraint user_name_un
unique(name);
注意,这个约束相当于之前的表级约束
删除表中的约束
alter table t_user
drop constraint user_name_un;
修改列名
alter table teacher
rename column gender to sex;
修改表的名
rename t_user to mytest;
rename mytest to t_user;
修改表中某列的数据类型
alter table t_user
modify (name varchar2(500));
让约束失效
alter table t_user
disable constraint user_id_pk cascade;
必须知道约束的名字
让约束再次生效
alter table t_user
enable constraint user_id_pk;
truncate
,截断表中的数据(删除)
truncate table t_user;
-- 相当于:
delete from t_user;
commit;
truncate
操作,不需要提交,默认已经提交,并且不能回滚,因为truncate属于DDL
操作,rename也属于DDL
语句
grant&revoke
授权语法
grant 权限 on 表名[列名] to 用户 with grant option
回收权限
revoke 操作权限 on 表名[列名] from 用户 cascade
案例
-- briup用户将s_emp的查询权限授于test用户,并且test用户可以继续将该权限授予给别的用户(在授权命令后追加 with grant option)
conn briup;
grant select on s_emp to test with grant option;
-- 切换到test用户
conn test
select *from briup.s_emp;
-- .test用户将s_emp的查询权限给test1
conn test
grant select on briup.s_emp to test1 ;
注释
例如,给表添加注释
comment on table t_user is '很好';
例如,给列添加注释
comment on column t_user.name is 'good';
例如,查看表中注释
select * from user_tab_comments where table_name=upper('t_user');
查看列中的注释
select * from user_col_comments
where
comments is not null
and
table_name=upper('t_user');
序列
主要作用于主键自增
序列是oracle数据库所特有的对象,其他数据库中是没有的。
创建
create sequence 序列名
设置属性
create sequence 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
说明,
INCREMENT BY
,用于定义序列的步长,如果省略,则默认为1START WITH
, 定义序列的初始值(即产生的第一个值),默认为1MAXVALUE
定义序列能产生的最大值。NOMAXVALUE是默认值,代表没有最大值定义MINVALUE
定义序列能产生的最小值。NOMAXVALUE是默认值,代表没有最小值定义CYCLE 和 NOCYCLE
表示当序列的值达到限制值后是否循环CACHE
定义存放序列值的缓冲区的大小,默认为20。 NOCACHE 表示不对序列进行内存缓冲
操作
查看序列的当前值
select 序列名.currval from dual;
查看序列的下一个值
select 序列名.nextval from dual;
注意,创建好一个序列之后,不能先使用currval,因为当前还没有值,需要先使用nextval,之后才 可以使用currval
案例
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
drop table t_user;
创建序列
create sequence t_user_seq;
drop sequence t_user_seq;
使用序列产生id值
insert into t_user(id,name,salary) values(t_user_seq.nextval,'tom',2000);
例如,查询当前用户的序列
select sequence_name
from user_sequences;
删除
语法:
drop sequence 序列名
视图
视图(view),它也是一种数据库对象
视图其实就是提取一张表或者多张表的数据生成一个映射。
操作视图从而达到操作原表的效果,方便数据管理和安全操作。
视图的主要作用是隐藏表中的重要数据、代替比较长的sql语句。
分类
- 复杂视图
- 简单视图
简单视图:视图所代表的sql语句中,没有 group by
语句,没有组函数,查询的只是一张表,那么这样的视图就是简单视图;
复杂视图:视图所代表的sql语句中,如果有group by
语句,或者有组函数,或者查询的是多张表,那么这样的视图就是复杂视图;
区别:
通过简单视图可以修改原来表中的数据,通过复杂视图不能修改原来表中的数据
创建
创建视图,需要用户有创建视图的权限,如果之前没有,可专门给用户授权
grant create view to briup;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FigRszZH-1638151840453)(C:\Users\ZYZ\AppData\Roaming\Typora\typora-user-images\image-20210714202908089.png)]
创建视图,语法
create or relplace view to briup
as
sql 语句
测试用表:
create table t_user
as
select id,last_name,salary
from s_emp;
drop table t_user;
简单视图:
create or replace view v_test
as
select id,last_name,salary
from t_user
where id < 10;
查看视图内容
select *
from v_test;
例如,可以通过 简单视图 对原来的表进行数据的删除、更新、插入
delete from v_test where id=6;
update v_test set last_name='zhangsan' where id=8;
insert into v_test(id,last_name,salary) values(6,'tom1',3000);
select * from v_test;
with read only 语句**(只读)**
在创建视图的时候,可以加上with read only语句,表示只能通过该视图进行查询数据,,不能修改
create or replace view v_test
as
select *
from t_user
where id < 10
with read only;
注意,这个视图v_test将来只能查询,不能进行修改,此时再使用上面的DML进行操作数据就会报错
with check option语句
在创建视图的时候,可以加上with check option 语句,表示通过视图进行修改,那么也必须可以通过这个视图能够显示出来
测试表和数据
drop table t_user;
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
insert into t_user values(1,'tom1',1000);
insert into t_user values(2,'tom2',2000);
创建视图
create or replace view v_test
as
select id,name,salary
from t_user
where id=2
with check option;
只有操作id=2的这条数据,才可以成功执行(才可以显示出来)
insert into v_test values(3,'tom3',3000);
-- 插入失败,因为这个操作通过视图显示不出来
update v_test
set name='lily'
where id=1;
,这里会报错,因为这个操作通过视图显示不出来
update v_test
set name='lily'
where id=2;
-- 这次更新成功,因为这个操作通过视图可以
复杂视图
复杂视图只能用来查询,不能修改
例如
create or replace view v_test
as
select avg(salary) avgSal
from t_user;
select * from v_test;
修改数据失败
update v_test
set avgSal=2022;
注意,此时会直接报错:ORA-01732: 此视图的数据操纵操作非法
删除
语法
drop view 视图名称
索引
索引的概念
- 类似书的目录结构
- Oracle的索引是一种对象,是与表关联的可选对象,能提高sql查询语句的速度;
- 索引直接指向包含所查询的行的位置,减少磁盘IO;
- 索引和表是相互独立的物理结构;
- Oracle自动使用并维护索引,插入删除更新表后,自动更新索引;
原理
例如:一个表中有name字段,假设要查找name='tom’的数据,但是数据可能会有多条
- 如果没有索引,查找这个记录时,需要搜索表中所有的记录,因为不能保证只有一个tom,那么就 必须将表中数据全部搜索一遍
- 如果在name上建立索引,oracle会对全表进行一次搜索,将每条记录的name值在什么位置按照一 定的规则进行排列,然后构建索引条目,并存储起来,在查询name为tom时,可以直接查找该数 据所在的对应地方。
注意,创建了索引并不一定就会使用,因为oracle在自动统计表的信息后,会决定是否使用索引, 表中数据很少时,使用全表扫描速度已经很快了,那么就没有必要使用索引了。
创建
创建索引的两种方式
-
自动创建索引:
当在表中指定了primary key 或者unique 约束,会自动创建唯一索引。
-
用户创建索引
用户可以创建非唯一的索引以提高在访问数据的效率;
语法要求:
create index 索引名
on 表名(列名);
例如
create index emp_index
on s_emp(last_name);
例如,创建后,可以查看当前用户创建的索引
select index_name from user_indexes;
注意,在某一个列上加入上了索引,那么也只有在数据量很大的时候,才能有所体现出这个查询的 效率
注意,索引一旦建立成功,那么之后这个索引就由数据库来管理,我们自己是控制不了的
删除
语法
drop index 索引名
种类
-
唯一索引 unique index
创建索引时,列的约束是唯一约束或者是主键约束
主键索引:(Primary Key)
- 最常见的索引类型;
- 确保数据记录的唯一性;
- 确定特定数据记录在数据库中的位置
唯一索引:(Unique)
- 避免同一个表中某数据列中的值重复
区别:主键索引只能有一个,唯一索引可以又多个;
-
非唯一值索引 nounique index
创建索引时,列的约束不是唯一约束或者不是主键约束
-
单行索引 single index
-
多行索引 concatenated index
创建了主键约束和唯一约束,相当于创建流索引
结构
索引的常见结构:
- B-tree
- 位图
- 反序
- 函数
B-tree索引:
默认的索引就是这种结构。
- 适合大量的增、删、改
- 不能用包含OR操作符的查询
- 适合唯一值较多的列
- 典型的树状结构
例如,默认创建的索引就是这种结构。
create index emp_index on s_emp(last_name);
drop index emp_index;
位图索引:
数据基数比较少的时候,较适合建位图索引
- 非常适合OR操作符的查询
- 做UPDATE代价非常高,因为oracle要根据表的每次修改来更新索引
create bitmap index bitmap_index on s_emp(last_name);
drop index bitmap_index;
注意,需要查看当前数据库是否支持这种索引结构
,查询是否具备了bitmap索引的功能
select * from v$option Where PARAMETER='Bit-mapped indexes'
注意,如果value值为false表示,Oracle该版本不具有bitmap索引功能
反序索引:
反向索引是B-tree索引的一个分支,主要是在创建索引时,针对索引列的索引键值进行字节反转。 它可以将索引键值分散到不用的节点中。
例如。
create index emp_index_reverse on s_emp(last_name) reverse;
drop index emp_index_reverse;
函数索引:
经常对某个字段做查询的时候,并且是带函数操作的,那么此时函数索引就可以发挥作用,提高检索速 度。
create index func_index on s_emp(upper(last_name));
drop index func_index;