Oracle基础知识

Oracle基础知识

（支持SQL 92标准，基于C/S系统结构）

特点: 1.多用户，大事物量的食物处理 2.数据安全性和完整性 3.分布式数据处理 4.可移植性 ————————————————— 用户权限 sys： oracle超级用户 ssytem ： orace的默认管理员 —————————————————- 基本概念： 1.数据库: 磁盘上存储数据的集合，物理上表现为数据文件，日志文件，控制文件，逻辑上以表空间的行式存在。 2.全局数据库名：用于区分一个数据库的标识，由数据库名称和域名构成，使数据库的命名在整个网络环境中唯一。 3.数据库实例：每个启动的数据库都对应的一个数据库实例，由此访问其中的数据。 （通过内存共享运行状态的一组服务器< 数据库为磁盘上的文件>） 4.表空间：每个oracle数据库都是由若干个表空间构成的。 一个发表空间可以由多个数据文件构成，但一个数据文件只能属于一个表空间。【属于数据库的逻辑结构】 每个数据库都有一个默认的SYSTEM的表空间 5.数据文件：扩展名是.dbf ，用于存储数据库中的数据。数据库表和数据文件不存在一对一的关系。 6.控制文件： 扩展名是.ctl， 是个二进制文件。存储数据文件和日志文件的名称，位置，是数据库启动及运行时所必须的文件。 一个数据库至少要有一个以上的控制文件，Oracle11改默认包含3个控制文件，各个控制文件的内容相同。 7.日志文件 : 扩展名是.log ， 记录数据更改的所有信息。提供了一种回复机制。是成组使用的。多个日志文件组之间循环使用。 ———————————————————- 模式：数据库对象的集合（表，索引，也成模式对象） 。每个数据库用户有一个模式。 ———————————————————- 3个常用的数据库服务： 1.OracleServiceSID服务 Oracle数据库服务 默认是启动的 2.OracleOraDb11g_home1TNSListener服务 监听器服务 只有在数据库需要远程访问的时候才需要 3.OracleDBConsoleSID服务 数据库控制台服务 采用浏览器方式打开，用于Oracle企业管理器的程序— 注： 使用自带的SQL*PLUS进行访问时，只需启动第一个服务，并且不能使用@，否则会被视为本地连接。

Sys,System用户都是Oracle的系统用户，而Scott用户是Oracle的测试账户 ——————————————————-

连接数据库： 使用SQL PLUS方式：system/
orcl@orclDB 其中：system：数据库已有的用户名 orcl: 此用户名对应的密码 @分割用户名密码和网络服务名 【本地连接则不需要】 orclDB: 网络服务名，本地服务名， 使用PL/SQL Developer 输入用户名，密码，数据库，连接Normal 普通用户，sysOper 数据库操作员，sysDBA 数据库管理员

——————————————————————————————————- ——————————————————————————————————-

数据类型

1.字符数据类型 >CHAR 长度是1-2000字节 用于定长的字符串 未指明大小时默认占用1字节 >VARCHAR2 1 -4000字节 变长 可以节省磁盘空间 >NCHAR 用法同char，开发需要国际化是使用 区别: NCHAR用于存储Unicode字符集类型，即双字节字符数据

2.数值数据类型 >NUMBER 可以存储正。负，零，定点数，和精度为38位的浮点数 NUMBER(p，s) 【p: 为度，数字的有效位数，s: 为范围，表示小数点右边的数字位数（-84，+127）之间】 规则： 首先精确到小数点右边s位，并四舍五入，如果精确后的有效位数<= p,在正确，否则报错

3.日期时间数据类型(用于存储日期值和时间值) >DATE 采用7字节固定长度， 每个字节分别存储世纪，年，月，日，时，分，秒 范围：公元前4712年1月1日到公元9999年12月31日 SYSDATE函数功能返回的是当前的日期和时间 >TIMESTAMP 用于存储日期的年，月，日以及时间的时，分，秒，秒值精确到小数点后6位，同时包含时区信息 SYSTIMESTAMP 函数的功能返回的是当前日期，时间和时区 注： 如果查询出来的日期格式有问题，可能是字符集出了问题，也可能是未设置系统环境变量， 1.可以采用 ALTER session SET nls_date_formaat = ‘YYYY-MM-DD HH24:MI:SS’ ; 只对当前窗口有效，如果不想每次都设置，就修改系统的环境变量，nls_date_format

4 LOB数据类型 也称为大数据类型， 可以存储多大4G的非结构化信息，允许对数据进行高效，随机，分段的访问 可以是内部的，也可以是外部的，取决于它相对于数据库的位置 修改方式可以使用 SQL数据库操纵语言来完成 ，也可通过PL/SQL提供的DBMS_LOB来完成

>CLOB 字符LOB，能够存储单字节字符数据和多字节字符数据，主要用于存储非结构化的XML文档 >BLOB 二进制BLOB ， 可以存储较大的二进制对象如图像，视频剪辑和声音等。 >BFILE 二进制文件 ， 能够将二进制文件存储到数据库外部的操作系统文件中，BFILE列存储一个BFILE定位器，支持文件最大为4G >NCLOB 用于存储大大的NCHAR字符数据，同时支持固定宽度字符和可变宽度的字符（Unicode字符数据） ，大小不大于4G

注： Long类型和LOB类型都可以存储大数据

5 Oracle中伪列 伪列就像Oracle中的一个表列，但并未存储在表中，伪列可从表中查询，但不能插入，更新或删除它们的值 >ROWID 数据库中每一行都有一个行地址，返回该行地址，可用其来定位表中的一行 通常情况下可以唯一的标识数据库中的一行 用途： 1. 以最快的方式访问表中的一行 2. 能显示表中的行驶如何存储的 3. 可以作为表中行的唯一标识

>ROWNUM 对于一个查询返回的每一行，ROWNUM伪列返回一个数值代表行的次序。 通过使用ROWNUM伪列，可以限制查询返回的行数

——————————————————————————————————- ——————————————————————————————————-


SQL语言简介 —高级的结构化查询语言

> 数据定义语言 DDL (CREATE, ALTER , TRUNCATE 截断, DROP) > 数据操纵语言 DML (INSERT, SELECT , DELETE , UPDATE) 注意： 1.建表时，表名的最大长度为30字符 2.oracle中也有VARCHAR类型，但建议使用VARCHAR2 ,该数据类型是oracle标准数据类型 3.TRUNCATE 只删除表 中的记录而不删除表结构 ，使用该命令将删除表中所有行且不记录日志。 与DELETE命令相比较节省资源，执行速度快 Oracle SQLServer 列数 254 1024 行大小 无限制 8060字节，加16字节指向每个text或image列 最大行数 无限制 无限制 表命名规则 [schema.]tablename [[[server.]database.]owner.]tanlename schema: 对象的所有者，即模式的名称 4. DISTINCT 筛除结果集中内容全部相同的行，仅保留一行 5.列别名中指定包含有特殊字符的列标题，要使用双引号括起来 6.利用现有的表创建新表，可以把现有表中的记录复制到新表中，也可复制选定的列或只复制结构而不复制内容 create table test as select 8 * from student where 1= 2(1不等于2条件不成立，所以只留表而没有数据) 7. 查看表中的行数时，执行语句： select count(*) from stu — 效率低 select count(1) from stu — 效率高 8. 取出stuname,stuage不重复的记录 select stuname,stuage from stu group by stuname,stuage having (count(stuname||stuage) <2);

9. 删除stuname,stuage重复的行（保留一行） delete from stu where rowid not in ( select max(rowid) from stu group by stuname,stuage having (count(stuname||stuage)>1) union select max(rowid) from stu group by stuname,stuage having (count(stuname||stuage) =1)

9. 查看当前用户所有数据量大于100万的表 select table_name from user_all_tables a where a.num_rows > 1000000 其中user_all_tables 为系统提供的数据视图，可通过该视图获得当前用户表的描述 > 事物控制语言 TCL (COMMIT , SAVEPOINT保存点 ,ROLLBACK) oracle中的TCL有以下部分组成： 1） COMMIT ： 提交事务，即把事务对数据库的修改进行永久的保存 2 ) ROLLBACK ：回滚事务，取消对数据库中所做的任何修改 3 ) SAVEPOINT :在事物中创建存储点 4） ROLLBACKTO : 将事务回滚到存储点 ——————————————————————– 1。 开启事务： 事务在上一次事务结束之后，数据“第一次”被修改是自动开启 2。 结束事务： a. 数据被提交（发出从commit命令，执行DDL,DCL后自动提交，与oracle分离） b.数据被撤销 （发出rollback命令，服务进程异常结束，DBA停止会话） 3. 执行了n个sql语句，如果要提交，只能提交所有的sql语句，不能局部提交。 4. 如果要回滚，可以利用事务保存点来做局部回滚 > 数据控制语言 DCL (GRANT , REVOKE回收) 数据控制语言为用户提供权限控制命令。数据库表的所有者对这些对象拥有控制权。

—————————————————————————————————— ——————————————————————————————————


SQL操作符 1.算术操作符 用于查询，算数表达式有NUMBER数据类型的列名，数值常量和连接它们的算数操作符组成【+ – * /】 2.比较操作符 用于比较两个表达式的值，【= ！= <= >= BETWEEN…AND IN(与列表中的列值相匹配) LIKE（匹配字符模式） ISNULL】 3.逻辑操作符 用于组合多个比较运算的结果以生成一个或真或假的结果 【AND OR NOT】 4.集合操作符 将连个查询的结果组合成一个结果集 >UNION (联合) 并集，返回两个查询选定的所有不重复的行。 也可以对联合查询的结果进行排序，使用ORDER BY >UNIONALL （联合所有） 合并两个查询选定的所有的行，包括重复的行。 >INTERSECT （交集） 只返回连个查询都有的行。 >MINUS （减集） 只返回第一个查询选定而未被第二个查询选中的行，在第一个查询的结果中排除 查询结果中出现的行。 使用集合操作符连接起来的select语句需符合规则： 1.由其连接的各个查询具有相同列书，且对应列的数据类型必须兼容 2.这种查询不用包含有LONG类型的列，列标题来自第一个select语句 5.连接操作符 || 用于将两个或多个字符串合并成一个字符串，或者将字符串与一个数值合并在一起

—————————————————————————————————— ——————————————————————————————————
SQL函数 大致分为 >单行函数 : 字符函数，日期函数，数字函数，转换函数及其他函数 >聚合函数（分组函数） 基于数据库表的多行进行运算 >分析函数：对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个

转换函数： 进行数据类型的转换 TO_CHAR（d |n [, fmt]） d 日期，n 数字，fmt对应格式 常被用作格式化显示日期，将日期以fmt指定的格式转换为VARCHAR2类型的值，未指定fmt 则按默认类型转换。 格式化数值 eg： SELECT TO_CHAR(SYSDATE,’YYYY”年”fmMM”月”fmDD”日” HH24:MI:SS’ ) FROM dual “fm”格式掩码，避免空格填充和数字零填充，不使用”fm”,月份会自动补0 TO_DATE (CHAR ,[,fmt]) 将CHAR 或VACHAR数据类型转换成为日期数据类型 用于将字符串类型的日期数据转换为日期类型的数据 TO_NUMBER 将包含数字的字符串转换为NUMBER数据类型，从而可以对该数据执行算术运算，一般不用这样做，Orace可对数字字符串进行隐式转换。

注： Oracle中没有SQL Server中的CONVERT函数，只有TO_CHAR 和TO_DATE

其他函数（除去字符函数，日期函数，数字函数，转换函数外） >NVL(exp1,exp2) 如果exp1=NULL,返回exp2的值，否则为exp1 >NVL(exp1,exp2,exp3) 如果 exp1=NULL ，返回exp3的值，否则返回exp2 >DECODE(value , if1, then1,fi2,then2 , …,else) 如果value值为if1，返回then1，。。。否则返回else 注：任何数和NULL值进行+运算时，结果为NULL

分析函数 ：类似于聚合函数 语法： 函数名（[参数]） OVER ([ 分区子句 PARTITION BY] [排序子句 ORDER BY]) 排序子句对每个分区进行排序 RANK ,DENSE_RANK , ROW_NUMBER 函数用于为每天条记录产生一个从1到n开始的自然数，n值可能小于等于记录的总数，3个函数的唯一区别在于遇到相同数据时的排名策略 ROW_NUMBER 返回1个唯一值，遇到相同数据时，排名按照记录及中的记录顺序依次递增 DENSE_RANK 返回1个唯一值，遇到相同数据时，所有相同数据的排名是相同的 RANK 返回1个唯一值，遇到相同数据时，所有相同数据的排名相同，同时会在最后一条相同记录和下一条不同记录之间空出排名