
Oracle基础笔记
Oracle基础
对用户解锁:
alter user 用户名 account unlock;
alter user 用户名 identified by 新密码;
Oracle的内置用户:
sys 超级管理员
system 管理员
scott 普通用户
使用dos命令的时候的操作:
查询表数据显示格式化
set linesize; 设置行长度
set pagesize; 设置每页的条数
Col 列名 A长度; dos窗口显示字段的时候,字段设置的长度会使用空格补全,当显示不下的时候可以设置列的长度
conn system/manager : 切换用户
show user :查看当前登录的用户
编写SQL语句的的时候:
使用"ed xx" 然后回车会自己提示创建以.sql结尾的文本文档, ctrl+c 赋值,
回到命令窗口右击鼠标复制
也可以编写完之后使用命令“@刚才的文件名”回车直接执行
当不在同一账户的情况下如果想查其他用户的数据表
select * from user.table;(模式名称.表名称)OR(用户名称.表名称)
在SQL plus里除了使用Oracle的命令之外还可以利用HOST指令调用本机的操作系统命令
DOS echo
sqlplus : HOST echo
HOST copy D:\test.text e:\卧槽.text
数据库操作语言
DML(数据库操作语言,开发人员):
DDL(数据库定义语言,开发前设计):
DCL(数据库控制语言,系统人员 DBA):
--将现有的表的数据怼到新表中;
create table student2
as
SELECT *FROM student;
--将现有的表的某些字段怼到新表中;
create table student2
as
SELECT name,age,address FROM student;
--将现有的表结构怼到新表中 使1=2不成立来抛开数据
create table student2
as
SELECT *FROM student where 1=2;
简单查询:
简单查询就是指的就是查询全部的数据行记录,但是由SELECT子句控制列;
消除重复列:
DISTINCT
它只能跟在SELECT子句后边,但是;如果查询的列是多个列,那么只有这个多个列的数据都相同时才可以消除
常量:
SELECTC '这是一列',empno,ename,job,sal FROM emp;
示例:
'这是一列' EMPNO ENAME JOB SAL
------------------------ ---------- -------------------- ------------------ ----------
这是一列 7369 SMITH CLERK 800
这是一列 7499 ALLEN SALESMAN 1600
||:
用法是将列于列之间的分割消除
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
这是一列7369SMITHCLERK800
这是一列7499ALLENSALESMAN1600
这样是没有什么意思
主要的作用是格式转换:
SELECT '雇员编号:' || empno || ' ,雇员姓名:' || ename|| ' ,收入:' || sal FROM emp
--------------
雇员编号:7369 ,雇员姓名:SMITH ,收入:800
雇员编号:7499 ,雇员姓名:ALLEN ,收入:1600
总之是在SELECT子句中 只要出现字符串 就要使用''引起来
\:
转义符:比如使用_来查询,字段带有下划线的 就可以使用\来转义
nulls last:
在Oracle中排序的话是null最大 排在最前,
select * from emp where order by sal desc nulls lasrt;
a:
代表追加 append,
select * from emp ;
a order by sal desc;
/
多列排序:
SELECT * FROM emp order by sal desc, hiredaet asc;
##函数
单行函数:
lower uper initcap(首字母大写) substr(str,x,x)从1开始
length (字符数) / lengthb(字节数)
英文/数字 都是一个意思
中文:
select length("蜗牛"), lengthb("犀牛")
UTF-8 2(几个字就是几个字符) 6(一个字符就是三个字节)
GBK 一个汉字或符号占两个字节
查看当前系统的编码格式:select * from nls_database_parameters;
多行函数:
Oracle数据库应用
一,表空间
作用是:
对不同的用户分配不同的表空间,对不同的模式对象分配不同的表空间
可以将不同的数据库分配到不同的磁盘中,利于管理磁盘提高I/O性能,利于数据库备份和恢复数据
--创建表空间:
CREATE TABLESPACE 名称
DATAFILE ‘盘符路径’ [SIZE xx M|xx K]
[ AUTOEXTEND [ OFF | ON ] ];
--AUTOEXTEND子句用来启用或禁用数据文件的自动扩展,设置为ON则空间使用会完毕自动扩展,OFF则很容易出现表空间剩余0的情况,是数据不能存储到数据库中
--示例
CREATE TABLESPACE tp_orders
DATAFILE 'D:\ORACLE\data\tp_orders.dbf'
SIZE 80M AUTOEXTEND ON;
--删除表空间
DROP TABLESPACE 名称 INCLUDING CON (INCLUDING CON的作用是带数据删除)
--更改表空间的状态为只读
create table t1(id int) tablespace tbs1;
--解除表空间只读属性
alter tablespace tbs1 read write;
--创建用户:
CREATE USER 用户名
IDENTIFIED BY 密码
[ DEFAULT TABLESPACE 表空间名 ]
[ TEMPORARY TABLESPACE 表空间名 ]
--示例
CREATE USER lph
IDENTIFIED BY 980311; 此时创建出来这个用户没有任何的权限 ,所以我们可以给它赋值权限
--删除用户
DROP USER 用户名 CASCADE;
二,数据库权限管理
--赋权:可以赋单个权限 也可以赋值一组角色的权限
GRANT 权限|角色 CONNECT(连接数据库),RESOURCE(DDL),DBA (SYS的劝降相当于)
--撤销权限:
REVOKE 权限|角色 FROM 用户名;
--常用的系统预定义的角色
CONNECT :连接数据库 适合不许要创建表的用户
RESPURCE:可以创建数据库表,触发器,过程等。。
DBA :最高权限,
三,序列
--作用,代替自增列
--创建语法:
CERATE SEQUENCE 序列名
[ START WITH integer (从几开始) ]
[ INCREMENT BY integer ]
[ MAXVALUE integer | NOMAXVALUE ]
[ MINVALUE integer | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE integer | NOCACHE ];
ALTER SEQUENCE
--说明
START WITH :指定需要生成的第一个序列号,对于升序序列,其默认值为序列的最小值,对于降序,其默认值是最大值
INCREMENT BY :用于指定序列号之间的间隔,其默认值为1,如果n为正值,则生成的序列将按升序排列,如果n为负值,则生成的序列为倒序排列。
MAXVALUE : 指定最大值
NOMAXVALUE :如果指定了NOMAXVALUE,那么Oracle将升序列的最大值设为10的22开平方,将降序序列的值设为-1;这是默认选项
MINVALUE :指定最小值,MINVALUE必须小于或等于START WITH 的值,并且必须小于MAXVALUE
NOMINVALUE :如果指定了NOMINVALUE,那么Oracle将升序列的最小值设为1,将降序序列的值设为-10的26次开平方;这是默认选项
CYCLE : 排序到达最大或最小值后将继续重头开始生成值
使用序列
create SEQUENCE myseq
START WITH 1000 开始为1000
INCREMENT BY 10 步长10
MAXVALUE 1100 最大值1100
cycle 到最大后重1000开始
insert into Stock_Received values(myseq.nextval,to_date('2021-05-20','yyyy-mm-dd'),'demo');
更改序列
ALTER SEQUENCE myseq
INCREMENT BY 10 步长10
MAXVALUE 1100 最大值1100
cycle 到最大后重1000开始
删除序列
DROP SEQUENCE name;
四,同义词
简化SQL
隐藏对象的名称和所有者
提供对对象的公共访问
有两种类型
公共同义词 :
可以被所有数据库用户访问
私有同义词
只能在当前模式下访问,且不能于当前模式名的对象同名
语法:
私有同义词
CREATE OR REPLACE SYNONYM emp FOR SCOTT.emp
模式名 表名
公有同义词
CREATE PUBLIC SYNONYM public_sy_dept FOR SCOTT.dept;
删除同义词
DROP [ PUBLIC ] SYNONYM [ schema. ]name;
五,索引
1.)索引分类
物理分类 | 逻辑分类 |
---|---|
分区或非分区索引 | 单列或组合索引 |
B数索引 | 唯一或非唯一索引 |
正常或反向索引 | 基于函数索引 |
位图索引 |
2.)B数索引
CREATE [ UNIQUE ] INDEX index_name ON table_name (column_list)
[ TABLESPACE tablespace_name ];
>UNIQUE:用于指定唯一索引,默认情况下为非唯一索引
>index_name:指所创建索引的名称
>table_name:表示为之创建索引的表名
>column_name:在其上创建索引的列名的列表,可以基于多列创建索引,列之间用逗号分隔。
>tablespace_name:为索引指定表空间
唯一索引和非唯一索引
唯一索引:
定义索引的列中任何两行没有重复值。唯一索引中的索引关键之只能指向表中的一行。在创建主键约束和唯一约束时都会创建一个与之对应的唯一索引
非唯一索引:
单个关键字可以有多个与其关联的行
示例:
CREATE UNIQUE INDEX index_uniqe_stock_id ON Stock_Received(Stock_ID);
3.)反向键索引
--反向键索引(反转索引键)
CREATE INDEX index_id ON T (id) REVERSE;
与常规的B数索引相反,反向键索引是保证索引列的顺序的同时反转索引列的字节,反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列,反转索引列可以将索引数据分散在多个索引区间,减少I/O瓶颈的发生
反向键索引通常建立在一些值连续增长的列上,如系统生成的员工编号,但不能执行范围搜索
4.)位图索引
--位图索引适合创建在低基数列上(即该列的值是有限的,理论上不会是无穷大,例如工种)
CREATE BITMAP INDEX index_bit_job ON T (id)
位图索引的优点:
相对于大批即时查询,可以减少响应的时间
相比其他索引,占用空间明显减少
即使在配置很低的终端硬件上,也能获得显著的性能
位图索引不应该用在频繁发生 INSERT UPDATE DELETE 上,
5.)其他索引
1.组合索引:
--(创建组合索引)
CREATE INDEX index_name ON table_name(clo1,clo2);
比较:
b数索引 | 位图索引 |
---|---|
适用于高基数列 | 适用于低基数列 |
在键上更新相对价廉 | 更新键行非常昂贵 |
使用OR 谓词的查询效率很低 | 使用OR谓词的查询效率很高 |
行级锁定 | 位图段级 |
存储较多 | 存储 |
6.)原则
频繁搜索的列可以作为索引
经常排序,分组的列
经常用作连接的列(主外键)
将索引放在一个但单独的表空间中,不要放在有回退段,临时段和表的表空间中
对于大型索引而言,考虑使用NOLOGGING子句创建的大型索引
根据业务数据发生的频率,定期重新组织索引,并进行碎片整理
仅包含几个不同值的列不可以创建为B树索引,可以根据需要创建位图索引
不要仅在含几行的表中创建索引
7.)删除索引
--Oracle索引名在用户账户中是唯一的删除时不需要指定表名
DROP INDEX idnex_name;
--何时删除索引
执行大量数据加载前,加载后在创建索引,好处:提高加载性能,更有效的使用索引空间
8.)重建索引
--将反向索引更改为正常的B树索引
ALTER INDEX index_reverse_empno REBUILD NOREVERSE;
--何时应重建索引
1,用户表被移动道新的表空间后,表上的素偶i你不是自动转移,此时需要将索引移到指定的表空间
ALTER INDEX index_naem REBUILD TABLESPACE tablespace_name;
2,索引中包含很多已删除的项,对表频繁的删除,造成索引空间的浪费,可以重新创建索引
3,需将现有的正常的索引转换为反向索引
六,分区表
分区的优点
1.改善查询性能,可以访问某一个区的数据而非整个表。
2.更容易管理,因为分区表的内容存储在多个部分中,按分区加载和删除更容易
3.便于备份和恢复
4.提高数据安全性,将不同的分区数据放在不同的盘下,减小所有分区同时损坏的可能性
符合条件:
数据大于2G,已有数据和新添加的数据有明显的界限划分
注意 :
要分区的表不能有LONG和LONG RAW数据类型的列
分区的种类:
范围分区
列表分区
散列分区
复合分区
间隔分区
虚拟列分区 Oracle11g新特性
1.范围分区:
create table rangeOrders (
ORDER_ID number(12) primary key,
order_date date not null,
order_mode varchar2(8),
customer_id number(6) not null,
orders_status number(2),
order_total number(8,2),
sales_rep_id number(6),
promotion_id number(6)
)
PARTITION BY RANGE (order_date)(
PARTITION P1 VALUES LESS THAN (to_date('2005-01-01','yyyy-mm-dd')),
PARTITION P2 VALUES LESS THAN (to_date('2006-01-01','yyyy-mm-dd')),
PARTITION P3 VALUES LESS THAN (to_date('2007-01-01','yyyy-mm-dd')),
PARTITION P4 VALUES LESS THAN (to_date('2008-01-01','yyyy-mm-dd')),
PARTITION P5 VALUES LESS THAN (to_date('2009-01-01','yyyy-mm-dd')),
PARTITION P6 VALUES LESS THAN (maxvalue)
);
--查询分区的数据
SELECT * FROM rangeOrders PARTITION(P6);
--根据分区删除数据
delete from rangeOrders where order_id in (SELECT order_id FROM rangeOrders PARTITION(P3)
--一般会将最后一个分区设置为 maxvalue
maxvalue PARTITION P6 VALUES LESS THAN (maxvalue)
2.)间隔分区
间隔分区是范围分区的增强
create table InteravlOrders (
ORDER_ID number(12) primary key,
order_date date not null,
order_mode varchar2(20),
customer_id number(6) not null,
orders_status number(2),
order_total number(8,2),
sales_rep_id number(6),
promotion_id number(6)
)
PARTITION BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))(
PARTITION P1 VALUES LESS THAN (to_date('2005-01-01','yyyy-mm-dd'))
);
--INTERVAL(NUMTOYMINTERVAL(1,'YEAR')
表示一年是一个分区,MONTH:月
--获得分区情况
SELECT table_name,partition_name
FROM user_tab_partitions
where table_name=UPPER('InteravlOrders');