Oracle基础笔记
AI-摘要
切换
鸡哔你 GPT
AI初始化中...
介绍自己
生成本文简介
推荐相关文章
前往主页
前往tianli博客
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');
PL/SQL编程
评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果
