Oracle数据库

1.去除单列重复的值: select distintct stuName from student;
2.在Oracle中 字符型的数据和日期型的数据 需要 单引号 例如: where stuName=‘张三’; 不能使用双引号 " "
3.日期格式默认为 ‘dd-mm-yyyy’ 也就是日月年的格式 当然也可以对日期格式进行转换

事务控制语言部分

insert into student values(5,‘马六’,19);
insert into student values(6,‘马四’,12);
savepoint a;
insert into student values(6,‘马八’,12);
rollback to a;
commit;
select * from student;
以上代码只会插入"马六"和"马四"两条语句
savepoint a;是一个保存点 在执行到第三条语句时 rollback to a是回滚到 a这个保存点
所以第三条语句不会进行插入

Oracle的SQL函数部分

转换函数:

**TO_CHAR() **
功能:转换成字符串类型
示例:TO_CHAR(1234.5,’$9999.9’)
结果为:$1234.5

**TO_DATE() **
功能:转换日期类型
示例:TO_DATE(‘1980-01-01’,‘yyyy-mm-dd’)
结果为:
TO_NUMBER()
功能:转换成数值类型
示例:

其他函数:

NVL(exp1,exp2)
功能:如果exp1的值为 null,则返回exp2的值,否则返回 exp1的值
NVL2(exp1,exp2,exp3)
功能:如果exp1的值为 null,则返回exp3的值,否则返回 exp2的值
DECODE(value,if1,then1. if2,then2,…,else)
功能: 如果 value的值为 if1,则返回then1的值; 如果 value的值为 if2,则返回then2的值,…否则就返回 else的值

分析函数

分析函数是对一组查询结果进行运算,然后获得结果.从这个意义上说,分析函数非常类似于聚合函数,区别在于分析函数每个组返回多行, 聚合函数每组返回一行.
语法:
函数名([参数]) over([分区子句][排序子句])
在语法中:

函数名: 表示分析函数的名称.
参数: 表示函数需要传入参数.
分区子句(partition by): 表示将查询结果分为不同的组,功能类似于 group by 语句,是分析函数工作的基础.默认将所有的结果作为一个分组.
排序子句(order by): 表示将每个分区进行排序.
1.row_number
函数返回一个唯一的值,当遇到相同数据时,排名按照记录集中记录的顺序 依次递增
2.dense_rank
函数返回一个唯一的值,当遇到相同数据时,所有相同数据的排名都是一样的.
3.rank
函数返回一个唯一的值,当遇到相同数据时,所有相同数据的排名都是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名.

SQL语法

添加字段和删除字段
alter table employee add(empTel_no varchar2(12),empAddress varchar2(20));
alter table employee drop(empTel_no,empAddress);

在Oracle中实现分页
使用伪列 ROWNUM
1.首先先查询出结果集(排序什么的要在ROWNUM之前 先排序 不然的话ROWNUM会先生成,再排序)
2.再利用ROWNUM 生成排列对结果集进行标识 第一列数据标识 1 第二列标识为2 以此类推…
3.对这个标识进行筛选 就可以实现分页效果了.

表空间

–创建表空间 设置大小 以及是否自动扩充
create tablespace tb_hr
datafile ‘d:/oracle_db/tb_hr01.dbf’
size 10m
autoextend on;
–修改表空间大小
alter database
datafile ‘d:/oracle_db/tb_hr01.dbf’
resize 15m;
–添加文件的形式 修改表空间大小(推荐使用这样的方式)
alter tablespace tb_hr
add datafile ‘d:/oracle_db/tb_hr02.dbf’
size 30m
autoextend on;
–删除表空间(以及数据,文件还是存在的)
drop tablespace tb_hr
including contents;

自定义用户管理

创建用户>设置密码>默认表空间>临时表空间(创建完的用户是默认在 Users下的,可以点击Users 查看,并修改它的权限)

create user mark
identified by 123123
default tablespace users
temporary tablespace temp;

删除用户
当删除的用户拥有模式对象时则无法删除用户,而必须使用 cascade 选项删除用户和用户模式对象

drop user mark cascade

权限管理

方式1: PL/SQL Developer (中文版)工具直接可以修改 并可以给出相应的命令.步骤如下:

1.找到你想修改的用户
2.点击编辑
3.点击查看 SQL就会有相应的语法(也可以直接手动修改 权限)

方式2:SQL命令的方式(一般用户给 这两个权限即可 dba这个权限不要轻易给普通用户)

–赋予权限(命令的形式) 也可以通过软件本身修改
grant connect,resource to mark;
–赋予它查询 scott模式下的emp表的权限
grant select on scott.emp to mark;
–赋予它 test表 插入语句的权限
grant insert on test to mark;

撤销权限的语法

revoke connect,resource from mark;

oracle的序列

oracle没有像mySQL那样的自动增长的属性 .序列就类似于自动增长.
序列是用户生成唯一 连续序号的对象
序列是可以升序的,也可以是降序的
使用 create sequence 语句创建序列
创建序列
也可以通过 PL/SQL Developer (中文版)工具找到 用户(创建的用户)>objects>sequences>进行创建

–创建序列(从1开始,每次增长1,最大数为9999,超出后异常,缓存为30个)
create sequence seq1
start with 1
increment by 1
maxvalue 9999
nocycle
cache 30;

访问序列
创建完序列后可以使用nextval和currval伪列来访问该序列的值,可以从位列中选择值,但不能操纵它们的值.
nextval:(一般使用在插入语句中,为新的数据添加序列)利用上面创建序列的increment by子句在增加序列值,并返回这个值.
currval:(一般使用在查询语句中,查看最后一次创建的序列值)返回该序列的当前值,即最后一次引用nextval时返回的值
更改序列

alter sequence seq1
…省略

删除序列

drop sequence seq1

并行环境下可以使用sys_guid函数生成32位的唯一编码作为主键(由于生成的值比较困难,使用环境为并行或者希望避免使用序列的情况下才选择它作为主关键字)

同义词

同义词是现有对象的一个别名

简化 SQL语句
隐藏对象的名称和所有者
提供对对象的公共访问

分为私有同义词和公有同义词
私有同义词

创建:create sysnonym 别名 for 模式名 . 表名 (别名不能与模式名和表名一致 )
删除:drop sysnonym 别名;

公有同义词

管理员用户直接创建

创建:create public sysnonym 别名 for 模式名 . 表名 (别名不能与模式名和表名一致 )
删除:drop public sysnonym 别名;

普通用户,创建公有同义词是要 给予它创建的权限,然后设置权限可以让别人看到(不然别人查看时会提示权限不足)

GRANT SELECT ON p_sy_dept TO PUBLIC;

索引

索引是与表关联的可选结构,是一种快速访问数据的途径,可提高数据库性能.数据库可以明确地创建索引,以加快对表执行SQL语句的速度
索引分类

物理分类	逻辑分类
分区或非分区索引	单列或组合索引
B树索引	唯一或非唯一索引
正常或反向键索引	基于函数索引
位图索引

B树索引

语法: create [unique] Index index_name ON table_name(colum_list) [tablespace tablespace_name]
说明: 创建 唯一或者非唯一的索引 也可以为索引指定表空间

反向键索引

语法: create Index index_reverse_empno ON employee(empno) reverse;
说明: 与常规B树索引相反,反向键索引在保持列顺序的同时反转索引列的字节 例如: 7566 会变成 6657 但是rowid还是指向的7566(了解即可)

位图索引

语法: create bitmap Index index_bit_job_empno ON employee(job) ;
说明: 使用场景一般 像图书类别列 用户类别 >例如: 1 对应 管理员 2 对应 普通用户(像这样的逻辑外键)

优点:

1. 对于大批即时查询,可以减少响应时间.
2. 相比其他的索引技术,占用空间明显减少.
3. 即时在配置很低的终端硬件上,也能获得显著的性能.

删除索引(统一):

drop index 索引名

重建索引(将反向索引更改为正常的B树索引)

alter index 索引名 rebuild noreverse;

分区表

• 允许用户将一个表分成多个分区
• 用户可以执行查询,只访问表中的特定分区
• 将不同的分区存储在不同的磁盘,提高访问性能和安全性
• 可以独立地备份和恢复每个分区

只能在创建表的时候才能分区
范围分区

create table e
partition by range(sal)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (4000),
partition p5 values less than (maxvalue)
)
as select * from employee
说明: 创建表 e来自于 employee 以薪金sal字段作为分区
1000以下的会存在 p1区 … 超过所有的范围的信息 会存放在p5区 也就是超过4000的值会在p5区(就是按照范围值来进行分区)

查询分区

select * from e partition(p2);

删除分区

delete from e partition(p2);

间隔分区

create table emp2
partition by range(hireddate)
interval(numtoyminterval(3,‘month’))
(
partition p1 values less than (to_date(‘1981-01-01’,‘yyyy-MM-dd’))
)
as select * from employee

查看间隔分区(需要先查看分区情况,因为分区我们自己定义了一个p1,但剩下的是由它自己完成的,系统会自动分配分区名给它,这时我们无法知晓剩下的分区名)

1. 查看分区情况

select table_name,partition_name
from user_tab_partitions
where table_name=upper(‘emp2’);

2.按照查询出来的分区名 来查询

select * from emp2 partition(SYS_P21);

此文章供自己以后复习参考