Oracle数据库

2023-10-14 19:02 由 L0ngyc 发表于 #数据库

Ⅰ 数据库

　　①层次型数据库
　　②网状型数据库
　　③关系型数据库（主要介绍）

E-R图：属性（椭圆形），实体（矩形），联系（菱形-一对一、一对多、多对多）
注：有的联系也有属性

关系型数据库的设计范式：

　　第一范式（1NF）：属性不可再分，字段保证原子性

　　第二范式（2NF）：在满足1NF的基础上，要求表中的每条记录必须被唯一的区分

　　第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

Oracle数据库：
　　三大概念：实例、数据库、数据库服务器
　　实例：指一组Oracle后台进程以及在服务器中分配的共享内存区域；
　　数据库：由基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等组成的物理文件集合；
　　数据库服务器：指管理数据库的各种软件工具（比如，sqlplus、OEM等）和实例及数据库三个部分。
　　实例与数据库两者联系：实例用于管理和控制数据库，而数据库为实例提供数据；一个数据库可以被多个实例装载和打开，而一个实例在其生存期内只能装载和打开一个数据库。

Oracle数据库的逻辑存储结构　　

Ⅰ表空间管理
　　一）创建表空间
　　　语法：

create [smallfile小文件 | bigfile大文件表空间] tablespace 表空间名 datafile 存放路径 size ***k/m reuse -【options】-如下

本地化管理方式：extent management local [autoallocal自栋扩展 |uniform size **k/m 等同大小进行]
自动扩展：autoextent [on | off] next **k/m  ##若自动，则设置next的值
在线/离线：online | offline
永久/临时表空间：permanent | temporary
是否产生日志：logging | nologging  ##默认产生

//代码示例//

--查询表空间

select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name; 

--创建表空间
create tablespace tbs_test1 datafile 'D:\oracle_service\oracle_tablespace\tbs_test1.DBF' size 10m reuse
extent management local autoallocate; 

select * from v$datafile;

--重命名
alter tablespace tbs_test rename to tbs_test1; 

--查询数据文件
select tablespace_name,file_name,bytes,autoextensible from DBA_DATA_FILES; 

--添加数据文件
alter tablespace tbs_test1 add datafile 'D:\oracle_service\oracle_tablespace\tbs_test2.DBF' size 10M reuse
autoextend on next 2m maxsize unlimited; 

--删除数据文件
alter tablespace tbs_test1 drop datafile 'D:\oracle_service\oracle_tablespace\tbs_test2.DBF'; 

--修改数据文件为自动扩展
alter database datafile 'D:\oracle_service\oracle_tablespace\tbs_test1.DBF' autoextend on next 2m maxsize unlimited;

　　二）撤销表空间
　　

create undo tablespace avatar_undo datafile ..
alter system set undo_tablespace=avatar_undo

　　作用：使读写一致、可回退事务、事务恢复、闪回操作

参数：
undo_tablespace
undo_management
undo_retention

　　三）临时表空间：（磁盘空间）

create temporary tablespace tablespace_name datafile ..

Ⅰ Oracle用户管理
　一）【用户】

　　查询用户

select * from dba_users;
select * from all_users;
select * from user_users;

　　创建一个用户peppa,密码peppa，默认表空间

create user peppa identified by peppa;

　　创建用户yclhj,密码yclhj,表空间为users表空间

create user yclhj identified by yclhj default tablespace users;

　　查询系统权限和对象权限【权限】

select * from system_privilege_map;
select * from table_privilege_map;

　　查看角色【角色】

select * from dba_roles;

　　直接给peppa用户授予登录权限create session

grant create session,alter session to peppa with admin option;

　　通过角色来添加用户权限

grant connect,resource to peppa;
grant connect,resource to yclhj;

　二）【用户与角色】
　　用户拥有的角色

select * from dba_role_privs;
select * from user_role_privs;
select * from role_role_privs;

　三）【用户与权限】
　　用户拥有的系统权限：

select * from dba_sys_privs;
select * from user_sys_privs;

　　用户拥有的对象权限：

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
select * from dba_col_privs;
select * from all_col_privs;
select * from user_col_privs;

Ⅰ SQL语句

　　1）DML语句（数据操作语言）

　　　　Insert / Update / Delete/merge

　　2）DDL语句（数据定义语言）

　　　　Create / Alter / Drop / Truncate(删除数据立即生效)
　　3）DCL语句（数据控制语言）
　　　　Grant（赋于权限） / Revoke（回收权限）
　　4）事务控制语句
　　　　Commit / Rollback / Savepoint
　　5）查询语句
　　　　简单查询 / 条件查询 / 连接查询
　　6）约束
　　　　NOT NULL 约束：确保某列不能有 NULL 值。
　　　　DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
　　　　UNIQUE 约束：确保某列中的所有值是不同的。
　　　　PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
　　　　CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
　　7）单行函数
　　　　使用语法：
　　　　　　函数名称(操作的列 | 具体的数值 [,若干参数])
　　　　1.字符串函数

大小写转换：UPPER()、LOWER()
首字母大写：INITCAP()
替换：REPLACE()
长度：LENGTH()、
截取：①SUBSTR(str,index)     ②SUBSTR(str，begin，size)
连接：CONTACT()

　　　　2.数值函数

round()函数: 表示的是四舍五入，而且是带小数点的
trunc()函数: 直接截取，不再进行四舍五入了（'+'是指小数点后，'-'是指小数点前，表示一个方向）
mod()取余

　　　　3.转换函数

TO_CHAR() #转换为字符串
TO_DATE() #转换为日期形式
TO_NUMBER() #转换为数字型

　　　　4.日期函数
　　　　当前系统时间：【sysdate】

SELECT SYSDATE FROM dual;

　　　　三大计算公式：
　　　　日期 – 数字 = 日期；
　　　　日期 + 数字 = 日期；
　　　　日期 – 日期 = 数字（天数）

MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间的月数；
ADD_MONTHS()：求出几个月之后的日期；
NEXT_DAY()：求出下一个的今天；
LAST_DAY()：求出给定日期的最后一天。

　　　　5.聚合函数

　　COUNT()：统计数据量；
　　SUM()：求和；
　　AVG()：求平均；
　　MAX()：最大值；
　　MIN()：最小值；

　　　　6.通用函数

　　①nvl（）: 如果第一个参数为空，就取第二个参数的值，如果不为空就去第一个参数的值
　　②decode() : 多条件判断
　　　　语法：
　　　　DECODE(列 | 值，判断条件1，输出结果1，判断条件2，输出结果2，.....)

　　8）子查询

　　SQL执行的顺序

　　　　1.FROM先确定从哪个表中获取数据
　　　　2.WHERE对条件加以限定
　　　　3.GROUP BY ...HAVING
　　　　4.聚合函数
　　　　5.SELECT字段筛选
　　　　6.ORDERBY

　　语法：

SELECT 查询的字段,(
SELECT 查询的字段
FROM 表名称1 别名，表名称2 别名，....
[WHERE 条件(s)]
[GROUP BY 分组字段]
[ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ;
)
FROM 表名称1 别名，表名称2 别名，.... (
SELECT 查询的字段
FROM 表名称1 别名，表名称2 别名，....
[WHERE 条件(s)]
[GROUP BY 分组字段]
[ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ;
)
[WHERE 条件(s) (
SELECT 查询的字段
FROM 表名称1 别名，表名称2 别名，....
[WHERE 条件(s)]
[GROUP BY 分组字段]
[ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ;
)]
[GROUP BY 分组字段]
[ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ;

　　9）分页查询

　　rowid：代表该条记录的物理地址
　　rownum ：为生成的数据的编号，使用分页需要使用到rownum

select * from emp order by sal;
select rowid,rownum,emp.* from emp;

示例：
--每页显示3条记录，查询第一页
select * from emp where rownum<=3;
--查询第二页记录
select * from emp where rownum>3 and rownum <=6; --rownum>*不存在，条件满足不了
--解决问题：使用子查询先生成rownum,然后再基于生成的rownum进行分页
select empno,ename,sal,comm,job
from (select rownum a,empno,ename,sal,comm,job from emp)r
where r.a>3 and r.a<=6;

--按照sal排序
--正确顺序：先排序，再生成rownum
select rownum,empno,ename,sal,comm,job
from (select empno,ename,sal,comm,job
from emp order by sal);

--完整语句
select empno,ename,job,sal,comm from (
select rownum r,empno,ename,job,sal,comm from (
select empno,ename,job,sal,comm from emp order by sal))
where r>3 and r<=6;