Oracle

概述1.数据库名:数据库的标识。查询当前数据库名数据库实例名:用于和操作系统进行联系的标识，数据库和操作系统之间的交互用的是数据库实例名。查询当前连接数据库的实例名ORACLE_SID:是操作系统的环境变量,ORACLD_SID用于与操作系统交互，操作系统想访问实例必须通过ORACLE_SID，ORACLE_SID必须与instance_name的值一致。在Windows环境下ORACLE_SID存在于注册表中，在Linux环境下可通过exportoracle_sid命令设置。数据库域名:查询当前数据库域名全局数据库名:数据库名+数据库域名数据库服务名:如果数据库有域名，则数据库服务名就是全局数据库名；否则，数据库服务名与数据库名相同。查询数据库服务名2.实例是指一组Oracle后台进程以及共享内存区，数据库是一个磁盘上存储的文件集合(数据文件、日志文件、控制文件等)，实例一生只能装载打开一个数据库，数据库可以由一个或多个实例装载打开。3.物理结构:数据文件、控制文件、日志文件。逻辑结构:表空间(tablespace)段(segment)区(extent)块（block）Oracle中的数据逻辑上存储于表空间中，而物理上则存储于属于表空间的数据文件（datafile）中。4.数据字典是Oracle存放有关数据库信息的地方，比如一个表的创建者信息，所属表空间信息等。数据字典由数据字典表和数据字典视图组成。数据字典表:数据字典表属于sys用户，存储在数据库的SYSTEM表空间中，数据库的信息实际上都是存放在数据字典表中，但是数据字典表中存储的信息通常是经过加密的，用户不能直接访问。数据字典视图:数据字典表中的信息经过解密和一些处理后得到有用的信息，以数据字典视图的方式显示给用户。数据字典视图可以分为三类user_*、all_*、dba_*。5.常见的数据字典视图，以user_*为例。(1)dictionary/dict:保存了当前用户可以查看的数据字典视图。(2)user_catalog:保存了用户拥有的所有表、视图、同义词和序列。user_objects:保存了用户所有对象类型的信息。(3)user_recyclebin:保存了删除的表，可以用flashback命令恢复。(4)user_constraints:保存了有关约束的信息。user_cons_columns:保存了与约束相关的列的信息。(5)user_indexes:保存了有关索引的信息。user_ind_columns:保存了与索引相关的列的信息。(6)user_db_links:保存了dblink相关信息。(7)user_triggers:保存了触发器信息。(8)user_procedures:保存了存储过程信息。(9)user_views:保存了视图信息。(10)user_tablespaces:描述数据库中表空间的信息。user_segments:描述数据库中段的信息。user_extents:描述数据库中区的信息。(11)user_users:保存了用户信息。(12)user_tab_privs:保存用户或角色的对象权限。user_sys_privs:保存用户或角色的系统权限。user_role_privs:保存用户所具有的角色。(13)v$datafile/dba_data_files:保存数据文件信息v$logfile:保存日志文件信息v$controlfile:保存控制文件信息v$tempfile/dba_temp_files:保存临时文件(即临时表空间的数据文件)信息。6.oracle数据类型：(1).数字类型:number(p)/number(p1,p2),p,p1表示总长度,p2表示小数点的位数。(2).字符类型:char(n)定长的字符类型，不够长的补空格，效率高。varchar2(n)不定长的字符类型，效率低。(3).日期类型:date。7.关闭数据库shutdownnormal不允许新的连接等待会话结束等待事务结束做一个检查点不需要实例恢复shutdowntransactional不允许新的连接不等待会话结束等待事务结束做一个检查点不需要实例恢复shutdownimmediate不允许新的连接不等待会话结束事务是自动rollback做一个检查点不需要实例恢复shutdownabort不允许新的连接不等待会话结束不等待事务结束不做检查点需要实例恢复8.打开数据库(1).startupnomount:仅仅创建一个Oracle实例。(2).startupmount:创建实例并且安装数据库，但没有打开数据库。(3).startup:该命令完成创建实例、安装实例和打开数据库的所有。9.改变归档模式dblink1.从一台数据库服务器的一个用户读取另一台数据库服务器下的某个用户的数据，就需要使用dblink。2.创建dblink需要有以下三种权限3.dblink分为私有的链接和公共的链接。私有的链接只有创建者可以使用，而公共的链接(public)所有用户都可以使用。4.配置方式(1)如果在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库链接。(2)如果在本地数据库tnsnames.ora文件中配置要访问的远程数据库链接,如下图可采用如下方式创建5.使用dblink6.删除dblink表空间1.创建表空间2.创建临时表空间3.设置表空间离线，系统表空间（SYSTEM）不能设置为离线。4.设置表空间在线5.为表空间增加数据文件6.设置表空间只读7.设置表空间可读可写6.删除表空间，contents删除对象，datafiles删除数据文件。用户、角色、权限1.创建用户2.创建用户并指定默认表空间、临时表空间。3.建立角色权限分为对象权限、系统权限。4.给用户、角色授予系统权限。withadminoption可以继续给其他用授予此权限。5.回收用户、角色的系统权限，系统权限不是级联回收。6.给用户、角色授予对象权限。7.回收用户、角色的对象权限，对象权限是级联回收的。8.给用户授予角色使该用户具有该角色所具有的权限。9.回收用户角色。SQL(结构化查询语言)一、数据查询语言(DQL)，即select。基本查询1.对数字列进行运算操作2.为列定义别名3.空值和任何数据运算结果也为空。4.||拼接字符串5.distinct去重复值。6.条件查询where、having7.比较运算符、、=、=、!=、、between(闭区间)8.逻辑运算符and、or、in、like、isnull、isnotnulllike结合通配符%、_使用，%代表匹配任意多个字符，_代表匹配一个字符9.orderby排序，desc降序，asc升序。先按部门排序，再按薪水排序。数值函数1.求绝对值2.向上取整3.向下取整4.取余5.四舍五入6.截取字符函数1.获取字符的ASCII码值2.将ASCII码转换为字符3.连接字符串4.首字母大写5.转小写6.转大写7.返回字符串的长度8.lpad(s,n[,c])在字符串s左边填充字符c直到长度为n9.rpad(s,n[,c])在字符串s右边填充字符c直到长度为n10.替换字符串replace(s1,s2[,s3])11.取子串substr(s1,s2[,s3])日期函数1.取当前时间2.获得指定月份后的时间3.获取本月最后一天4.获取日期的间隔月数5.返回下个星期几的时间，1代表周日6.日期可以相加减，单位是天转换函数1.数字、日期转字符串2.字符串转日期3.字符串转数字聚组函数1.求平均值2.求和3.查询行数4.求最大值5.求最小值分析函数1.rank()、dense_rank()统计一组数据的排序值其他函数1.nvl(expr1,expr2)若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。nvl2(expr1,expr2,expr3)expr1不为NULL，返回expr2；expr2为NULL，返回expr3。2.查看当前会话的用户3.返回当前行号4.条件取值内连接驱动表里的记录一定要在匹配表里找到匹配的记录，否则不能出现在结果集中外连接驱动表里的记录无论是否在匹配表里找到匹配的记录，都将出现在结果集中非关联子查询如果查询结果含有NULL且使用notin运算符，无结果集，例如查询没有下属的员工,以下查询方法是错的关联子查询EXISTS/NOTEXISTS集合操作1.union并集，去掉了重复值2.unionall并集，保留了重复值3.intersect交集4.minus属于第一个结果集，但不属于第二个结果集二、数据定义语言(DDL)，即creat、drop1.创建表2.添加列3.重命名列4.修改列数据类型5.删除列6.删除表7.主键约束8.非空约束9.唯一约束10.检查约束11.外键约束12.复制表三、数据操作语言(DML)，即insert、update、delete1.插入一条记录2.插入多条记录3.更新4.删除四、事务控制语言(TCL)，即cmmit、rollback事务即一组DML语句，要么全部成功，要么全部失败。事务有以下特点(1).原子性:一个事务要么全都执行，要么全都不执行。(2).一致性:事务在执行之前和执行之后，必须使所有的数据都保持一致状态。(3).隔离性:一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到。(4).持久性:一旦事务提交，它是永久的。视图1.授予创建视图权限2.创建视图3.删除视图索引1.自动创建索引:当添加唯一约束、主键约束条件时，索引自动创建，索引名称和约束名相同。2.创建索引3.删除索引序列1.创建序列2.nextval、currval3.修改序列4.删除序列PL/SQL1.PL/SQL块结构:块头(Header)、声明单元(Declarationsection)、执行单元(Executionsection)、异常处理单元(Exceptionsection)如下:例如2.控制结构3.循环语句，以1加到100为例(1).loop循环(2).while循环(3).for循环4.游标(1)Oracle游标分为静态游标和动态游标(即ref游标)，其中静态游标分为隐式游标和显示游标。(2)游标属性%rowcount、%found、%notfound、%isopen(3)隐式游标(2)显示游标不带参数的显示游标带参数的显示游标，for循环自动打开关闭游标(3)动态游标5.复合变量6.函数注:存储过程和函数的参数只能是varchar2、number不能是varchar2(n)、number(n)7.存储过程编写一个分页的存储过程，传入表名、列名、每页显示记录数、当前页，返回总记录数、和按传入列名排序后的当前页的信息。JAVA测试编写存储过程的代码8.触发器(1).创建触发器(2).禁止触发器(3).激活触发器备份、恢复1.expconsistent:导出期间是否使数据保持readonlydirect:是否使用直通方式导出file:指定输出文件owner:指定导出的用户rows:是否下载表记录tables:指定导出的表名列表(1)导出表(2)导出表结构(3)导出方案(4)导出数据库2.impfile:导入文件fromuser:指明来源用户ignore:是否忽略对象创建错误rows:是否导入表记录tables:指明导入的表名列表touser:指明目的用户(1)导入表(2)导入方案(3)导入数据库其他1.查询Oracle版本2.设置查询结果的列宽3.保存结果4.同义词5.执行文件脚本