Oracle数据库，实例与数据库两者联系

Oracle数据库，实例与数据库两者联系

Ⅰ 数据库

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

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;