Oracle 与 SQL Server

Question

才开始学习SQL Server，不知道它与Oracle有什么区别，听说有的面试都有Oracle的一些东西想了解哈！

Answer 1

ORACLE与SQL SERVER的区别 
体系结构 ORACLE的文件体系结构为： 数据文件 .DBF （真实数据） 日志文件 .RDO 控制文件 .CTL 参数文件 .ORA SQL SERVER的文件体系结构为： .MDF （数据字典） .NDF （数据文件） .LDF （日志文件） ORACLE存储结构： 在ORACLE里有两个块参数PCTFREE(填充因子)和PCTUSED(复用因子),可控制块确定块本身何时有，何时没有足够的空间接受新信息(对块的存储情况的分析机制) 这样可降低数据行连接与行迁移的可能性。块的大小可设置(OLTP块和DSS块) 在ORACLE中，将连续的块组成区，可动态分配区(区的分配可以是等额的也可以是自增长的)可减少空间分配次数 在ORACLEl里表可以分为多个段，段由多个区组成，每个段可指定分配在哪个表空间里(段的类型分为：数据段、索引段、回滚段、临时段、CASH段。ORACLE里还可对表进行分区，可按照用户定义的业务规则、条件或规范，物理的分开磁盘上的数据。 这样大大降低了磁盘争用的可能性。 ORACLE有七个基本表空间： ·SYSTEM表空间(存放数据字典和数据管理自身所需的信息) ·RBS回滚表空间 ·TEMP临时表空间 ·TOOLS交互式表空间 ·USERS用户默认表空间 ·INDX索引表空间 ·DBSYS福数据表空间 不同的数据分别放在不同的表空间(数据字典与真实数据分开存放),在ORACLE里基表(存储系统参数信息)是加密存储，任何人都无法访问。只能通过用户可视视图查看。 SQL SERVER 存储结构 以页为最小分配单位，每个页为8K(不可控制，缺乏对页的存储情况的分析机制), 可将8个连续的页的组成一个‘扩展’，以进一步减少分配时所耗用的资源。(分配缺乏灵活性)，在SQL SERVER里数据以表的方式存放，而表是存放在数据库里。 SQL SERVER有五个基本数据库： ·master(数据字典) ·mode(存放样版) ·tempdb(临时数据库) ·msdb(存放调度信息和日志信息) ·pubs(示例数据库) 真实数据与数据字典存放在一起。对系统参数信息无安全机制。 ORACLE登入管理： ·SYSTEM/MANAGER (初始帐户) ·SYS/CHANGE_ON_NSTALL ·INSTALL/ORACLE(安装帐户) ·SCOTT/TIGER(示例数据库,测试用) 在ORACLE里默认只有三个系统用户，ORACLE是通过用户登入。 SQL SERVER登入管理： ·SQL SERVER身份验证 ·WINDOWS 身份验证 在SQL SERVER里是通过WINDOWS用户帐户或是用SQL SERVER身份验证连接数据库的。 SQL不是一种语言，而是对ORACLE数据库传输指令的一种方式。 SQL中NULL既不是字符也不是数字，它是缺省数据。ORACLE提供了NVL函数来解决。 ORACLE中的字符串连接为 string1‖string2 ，SQL中为string1+string2. 集合操作：在SQL中只有UNION（并操作），ORACLE中包含MINUS（差操作）、 INTERECT（交操作）、UNION（并操作）。 索引：SQL的索引分为聚集索引和非聚集索引，还包括全文索引； ORACLE的索引包括：B+树索引，Bitmap位图索引，函数索引，反序索引， 主键索引，散列索引，本地索引。 ORACLE的数据类型比较复杂，有基本数据类型，衍生型，列对象型，表对象型，结构体型；SQL中的数据比较简单，只有一些简单的基本数据类型无法提供事务操作。 在SQL中如果一个事务从开始执行到结束时了错了，它就会回滚到开始之前； 在ORACLE中它采用的是定点回滚，就是该事务函数化和精确错误定位，用savepoint标记保存点，用Rollback标记回滚错误的保存点。 在SQL中循环只有WHILE一种这用起来不灵活，在ORACLE中有多种循环（LOOP循环、WHILE循环、FOR循环）。 在SQL中游标的使用比较复杂，不能同时打开一个以上的游标，因为它只有一个全局变量@@Fast_statues而且声明游标也麻烦，关闭游标时也不清除内存的；ORACLE中游标是以提高速度全部统一的单项游标，可以允许多用户异步读取，而且声明比较简单，就一句declare cursor游标名 is select 语句就可以了。 容错机制：SQL中的错误机制比较复杂，没有提供错误描述；ORACLE中容错类型有三种，一个是预定义错误，一个是非预定义错误，一个是用户自定义，其中在自定义错误中它有两个是在SQL中不有的，那就是SQLCODE 错误号、SQLERRM错误描述。 文件体系结构：SQL中有.MDF(主要数据文件)、.NDF（扩展文件，可以有多个）、 .LDF（日志文件，可以有多个，存放在联机重做日志数据，这里的日志文件有一个缺点就是如果日志文件已填小巧玲珑的话，SQL将自动停止运行并等待人工干预，所以要经常监控日志的情况保证系统的稳定运行）。ORACLE中有.DBF(主要数据文件)、.RDO（日志文件，用来做灾难性的数据备份）、.CTL（控制文件，将数据库的物理文件映射到了数据字典中的逻辑表空间和连机重做日志文件上去，确保数据的一致性）、.ORA（参数文件）。 . SQL只能是本机备份本机的数据库，无法联机备份，而且备份压缩很低，占用了大量空间；ORACLE提供了7种备份机制，具有联机备份功能，有志门的备份机子。 Oracle的日志默认有3个，先写入1号日志，满了后再写入2号日志，2号满了再写入3号日志，3号满了后Oracle将自动备分1号日志的内容，然后清空后写入新的日志信息，且Oracle的日志有多路复用功能，我们可以为日志创建多个镜像，把相同的日志信息同时写入多个日志文件中，这样可以防止磁盘损坏造成的数据丢失。 SQL Server对每个数据库只能分配一个日志文件。且当日志填满后，日志将停止工作，等待人工干预，而无法自动重用。 Oracle的控制文件记录了数据库文件的物理位置和有效性，在每次打开Oracle系统都将自动对数据进行验证，查看其有效性，防止数据的丢失，这体现了Oracle对数据管理的严密性。 SQL Server无此安全机制，只用在使用到了数据库的信息后，系统才去查找数据是否存在。 Oracle的参数文件init.ora是可调的，既我们可以根据数据库的规模调整Oracle对系统资源的使用情况，以达到最合理的资源分配，让Oracle达到最佳的性能。 SQL Server的配置参数是内定的不可调整，它对系统资源的分配是固定的，不受拥护控制，因此无法同时处理大量用户的需求，这限制了它只能作为中，小型数据库。 Oracle以块为最小存储单位，以区为单位分配空间，用户可以根据需要自己定义块的大小，且区可以按等额或递增进行分配，这可以大大减少系统的I/O操作提高数据库的性能。 SQL Server中以页为单位或使用扩展类型以8页为一单位进行空间分配而无法人工干预，当遇到频繁存储或大数据量存储时，系统将频繁进行I/O操作使工作效率低下。 Oracle中的SQL语句书写规范且提供了完整的函数和数据类型。Oracle提供了健全的错误机制，用户可以轻松的获得错误位置和错误原因而加以修改。用户还可以自己定义所须的错误类型，用以检查逻辑错误。Oracle中对数据类型的转换和游标的定义也十分方便。因此，我们对代码的书写和维护也比SQL Server方便许多。 SQL Server使用类C语言，不易维护和查看代码。SQL Server包含的数据类型太少，无法定义结构体，数组，对象。SQL Server无法定义流程机制，类型转换也极不方便，SQL Server中对游标定义十分复杂，且当用户定义的多个游标同时打开时系统却只有一个游标变量，这是SQL SERVER中一个严重的缺陷。 ORACLE中用户可以根据数据需要在创建块时定义填充因子(空闲空间的百分比)和复用因子(当块的存储数据所占的空间下降到这个百分比之下时，块才重新标记为可用)。用户可以根据记录变化频率和数据量的大小设置合适的填充因子和空闲因子。 SQL SERVER只有填充因子，而它的定义与ORACLE刚好相反，它定义的是剩余可用空间的百分比。而SQL SERVER中没有复用因子，当页中数据低于填充因子，但剩余的空间已经不可能再插入新的数据，但页还是标记为可用的，当系统要写入新数据都要访问这个页而浪费时间，这种页称为废页，将使系统插入新数据时浪费大量时间查找可用的页。 ORACLE在创建表时用户可以精确定义数据存放的表空间，甚至可以把一张表分开存放在多个表空间中，这样可以将数据量庞大的表按某些字段分开存放，这将给查询带来极高的效率。

Answer 2

数据库原理一样，sql语句一样不一样的就在于数据库自身的索引，分区，内存等的管理上

Answer 3

SQLServer和Oracle的常用函数对比 　　1.绝对值 
　　S:select abs(-1) value 
　　O:select abs(-1) value from dual 　　2.取整(大) 
　　S:select ceiling(-1.001) value 
　　O:select ceil(-1.001) value from dual 　　3.取整（小） 
　　S:select floor(-1.001) value 
　　O:select floor(-1.001) value from dual 　　4.取整（截取） 
　　S:select cast(-1.002 as int) value 
　　O:select trunc(-1.002) value from dual 　　5.四舍五入 
　　S:select round(1.23456,4) value 1.23460 
　　O:select round(1.23456,4) value from dual 1.2346 　　6.e为底的幂 
　　S:select Exp(1) value 2.7182818284590451 
　　O:select Exp(1) value from dual 2.71828182 　　7.取e为底的对数 
　　S:select log(2.7182818284590451) value 1 
　　O:select ln(2.7182818284590451) value from dual; 1 　　8.取10为底对数 
　　S:select log10(10) value 1 
　　O:select log(10,10) value from dual; 1 　　9.取平方 
　　S:select SQUARE(4) value 16 
　　O:select power(4,2) value from dual 16 　　10.取平方根 
　　S:select SQRT(4) value 2 
　　O:select SQRT(4) value from dual 2 　　11.求任意数为底的幂 
　　S:select power(3,4) value 81 
　　O:select power(3,4) value from dual 81 　　12.取随机数 
　　S:select rand() value 
　　O:select sys.dbms_random.value(0,1) value from dual; 　　13.取符号 
　　S:select sign(-8) value -1 
　　O:select sign(-8) value from dual -1 
　　----------数学函数 　　14.圆周率 
　　S:SELECT PI() value 3.1415926535897931 
　　O:不知道 　　15.sin,cos,tan 参数都以弧度为单位 
　　例如：select sin(PI()/2) value 得到1（SQLServer） 　　16.Asin,Acos,Atan,Atan2 返回弧度 　　17.弧度角度互换(SQLServer，Oracle不知道) 
　　DEGREES：弧度-〉角度 
　　RADIANS：角度-〉弧度 　　---------数值间比较 　　18. 求集合最大值 
　　S:select max(value) value from 
　　(select 1 value 
　　union 
　　select -2 value 
　　union 
　　select 4 value 
　　union 
　　select 3 value)a 　　O:select greatest(1,-2,4,3) value from dual 　　19. 求集合最小值 
　　S:select min(value) value from 
　　(select 1 value 
　　union 
　　select -2 value 
　　union 
　　select 4 value 
　　union 
　　select 3 value)a 　　O:select least(1,-2,4,3) value from dual 　　20.如何处理null值(F2中的null以10代替) 
　　S:select F1,IsNull(F2,10) value from Tbl 
　　O:select F1,nvl(F2,10) value from Tbl 　　--------数值间比较 　　21.求字符序号 
　　S:select ascii('a') value 
　　O:select ascii('a') value from dual 　　22.从序号求字符 
　　S:select char(97) value 
　　O:select chr(97) value from dual 　　23.连接 
　　S:select '11'+'22'+'33' value 
　　O:select CONCAT('11','22')||33 value from dual 　　23.子串位置 --返回3 
　　S:select CHARINDEX('s','sdsq',2) value 
　　O:select INSTR('sdsq','s',2) value from dual 　　23.模糊子串的位置 --返回2,参数去掉中间%则返回7 
　　S:select patindex('%d%q%','sdsfasdqe') value 
　　O:oracle没发现，但是instr可以通过第四霾问刂瞥鱿执问?BR>　　select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6 　　24.求子串 
　　S:select substring('abcd',2,2) value 
　　O:select substr('abcd',2,2) value from dual 　　25.子串代替 返回aijklmnef 
　　S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value 
　　O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual 　　26.子串全部替换 
　　S:没发现 
　　O:select Translate('fasdbfasegas','fa','我' ) value from dual 　　27.长度 
　　S:len,datalength 
　　O:length 　　28.大小写转换 lower,upper 　　29.单词首字母大写 
　　S:没发现 
　　O:select INITCAP('abcd dsaf df') value from dual 　　30.左补空格（LPAD的第一个参数为空格则同space函数） 
　　S:select space(10)+'abcd' value 
　　O:select LPAD('abcd',14) value from dual 　　31.右补空格（RPAD的第一个参数为空格则同space函数） 
　　S:select 'abcd'+space(10) value 
　　O:select RPAD('abcd',14) value from dual 　　32.删除空格 
　　S:ltrim,rtrim 
　　O:ltrim,rtrim,trim 　　33. 重复字符串 
　　S:select REPLICATE('abcd',2) value 
　　O:没发现 　　34.发音相似性比较(这两个单词返回值一样，发音相同) 
　　S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') 
　　O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual 
　　SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差 
　　返回0-4，4为同音，1最高 　　--------------日期函数 　　35.系统时间 
　　S:select getdate() value 
　　O:select sysdate value from dual 　　36.前后几日 
　　直接与整数相加减 　　37.求日期 
　　S:select convert(char(10),getdate(),20) value 
　　O:select trunc(sysdate) value from dual 
　　select to_char(sysdate,'yyyy-mm-dd') value from dual 　　38.求时间 
　　S:select convert(char(8),getdate(),108) value 
　　O:select to_char(sysdate,'hh24:mm:ss') value from dual 　　39.取日期时间的其他部分 
　　S:DATEPART 和 DATENAME 函数 （第一个参数决定） 
　　O:to_char函数 第二个参数决定 　　参数---------------------------------下表需要补充 
　　year yy, yyyy 
　　quarter qq, q (季度) 
　　month mm, m (m O无效) 
　　dayofyear dy, y (O表星期) 
　　day dd, d (d O无效) 
　　week wk, ww (wk O无效) 
　　weekday dw (O不清楚) 
　　Hour hh,hh12,hh24 (hh12,hh24 S无效) 
　　minute mi, n (n O无效) 
　　second ss, s (s O无效) 
　　millisecond ms (O无效) 
　　---------------------------------------------- 　　40.当月最后一天 
　　S:不知道 
　　O:select LAST_DAY(sysdate) value from dual 　　41.本星期的某一天（比如星期日） 
　　S:不知道 
　　O:SELECT Next_day(sysdate,7) vaule FROM DUAL; 　　42.字符串转时间 
　　S:可以直接转或者select cast('2004-09-08'as datetime) value 
　　O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL; 　　43.求两日期某一部分的差（比如秒） 
　　S:select datediff(ss,getdate(),getdate()+12.3) value 
　　O:直接用两个日期相减（比如d1-d2=12.3） 
　　SELECT (d1-d2)*24*60*60 vaule FROM DUAL; 　　44.根据差值求新的日期（比如分钟） 
　　S:select dateadd(mi,8,getdate()) value 
　　O:SELECT sysdate+8/60/24 vaule FROM DUAL; 　　45.求不同时区时间 
　　S:不知道 
　　O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL; 　　-----时区参数,北京在东8区应该是Ydt------- 
　　AST ADT 大西洋标准时间 
　　BST BDT 白令海标准时间 
　　CST CDT 中部标准时间 
　　EST EDT 东部标准时间 
　　GMT 格林尼治标准时间 
　　HST HDT 阿拉斯加—夏威夷标准时间 
　　MST MDT 山区标准时间 
　　NST 纽芬兰标准时间 
　　PST PDT 太平洋标准时间 
　　YST YDT YUKON标准时间  
Oracle支持的字符函数和它们的Microsoft SQL Server等价函数: 函数 Oracle Microsoft SQL　Server 
把字符转换为ASCII ASCII ASCII 
字串连接 CONCAT (expression + expression) 
把ASCII转换为字符 CHR CHAR 
返回字符串中的开始字符（左起） INSTR CHARINDEX 
把字符转换为小写 LOWER LOWER 
把字符转换为大写 UPPER UPPER 
填充字符串的左边 LPAD N/A 
清除开始的空白 LTRIM LTRIM 
清除尾部的空白 RTRIM RTRIM 
字符串中的起始模式（pattern） INSTR PATINDEX 
多次重复字符串 RPAD REPLICATE 
字符串的语音表示 SOUNDEX SOUNDEX 
重复空格的字串 RPAD SPACE 
从数字数据转换为字符数据 TO_CHAR STR 
子串 SUBSTR SUBSTRING 
替换字符 REPLACE STUFF 
将字串中的每个词首字母大写 INITCAP N/A 
翻译字符串 TRANSLATE N/A 
字符串长度 LENGTH DATELENGTH or LEN 
列表中最大的字符串 GREATEST N/A 
列表中最小的字符串 LEAST N/A 
如果为NULL则转换字串 NVL ISNULL 　 日期函数 函数 Oracle Microsoft SQL　Server 
日期相加 (date column +/- value) or 
ADD_MONTHS DATEADD 
两个日期的差 (date column +/- value) or 
MONTHS_BETWEEN DATEDIFF 
当前日期和时间 SYSDATE GETDATE() 
一个月的最后一天 LAST_DAY N/A 
时区转换 NEW_TIME N/A 
日期后的第一个周日 NEXT_DAY N/A 
代表日期的字符串 TO_CHAR DATENAME 
代表日期的整数 TO_NUMBER 
(TO_CHAR)) DATEPART 
日期舍入 ROUND CONVERT 
日期截断 TRUNC CONVERT 
字符串转换为日期 TO_DATE CONVERT 
如果为NULL则转换日期 NVL ISNULL 　 转换函数 函数 Oracle Microsoft SQL　Server 
数字转换为字符 TO_CHAR CONVERT 
字符转换为数字 TO_NUMBER CONVERT 
日期转换为字符 TO_CHAR CONVERT 
字符转换为日期 TO_DATE CONVERT 
16进制转换为2进制 HEX_TO_RAW CONVERT 
2进制转换为16进制 RAW_TO_HEX CONVERT 　 其它行级别的函数 函数 Oracle Microsoft SQL　Server 
返回第一个非空表达式 DECODE COALESCE 
当前序列值 CURRVAL N/A 
下一个序列值 NEXTVAL N/A 
如果exp1 = exp2, 返回null DECODE NULLIF 
用户登录账号ID数字 UID SUSER_ID 
用户登录名 USER SUSER_NAME 
用户数据库ID数字 UID USER_ID 
用户数据库名 USER USER_NAME 
当前用户 CURRENT_USER CURRENT_USER 
用户环境(audit trail) USERENV N/A 
在CONNECT BY子句中的级别 LEVEL N/A 　 合计函数 函数 Oracle Microsoft SQL　Server 
Average AVG AVG 
Count COUNT COUNT 
Maximum MAX MAX 
Minimum MIN MIN 
Standard deviation STDDEV STDEV or STDEVP 
Summation SUM SUM 
Variance VARIANCE VAR or VARP Oracle还有一个有用的函数EXTRACT,提取并且返回日期时间或时间间隔表达式中特定的时间域: 
EXTRACT(YEAR FROM 日期) 

Answer 4

貌似Oracle函数没那么长。。
S:select abs(-1) value 
O:select abs(-1) value from dual 请问O中的from是来自dual 表吗？

Answer 5

面试官想了解的都是一些oracle的内部运行原理的机制，你买本oracle DBA的书籍就可以应付面试了吧！请看看我的oracle面试题目总结，或者对你有所帮助吧！

Answer 6

面试题目总结网址：http://topic.csdn.net/u/20080731/21/4b8eb01e-0523-4898-be3f-9cddc3b3a5b3.html