第三章SQL和OracleSQL函数

第三章SQL和OracleSQL函数本章要点SQL数据类型OracleSQL函数SQL是StructuredQueryLanguage（结构化查询语言）的首字母缩写词SQL是数据库语言，Oracle使用该语言存储和检索信息表是主要的数据库对象，用于存储数据通过SQL可以实现与Oracle服务器的通信SELECTenameFROMEmp;发送SQL查询Oracle服务器enameBLAKESMITHALLENDAVIDMARTIN发送命令输出到用户端用户wang3.1SQL简介SQL支持下列类别的命令：数据定义语言（DDL）数据操纵语言（DML）事务控制语言（TCL）数据控制语言（DCL）数据定义语言CREATEALTERDROP数据操纵语言INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言GRANTREVOKE3.1SQL简介字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用CHAR数据类型。CHAR数据类型存储字母数字值。CHAR数据类型的列长度可以是1到2000个字节。VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB(2千兆字节)基本使用CLOB类型3.2Oracle数据类型HelloWorld11数据长度字节存储在一个VARCHAR2(80)中的HelloWorldHelloWorld80…数据长度字节69空格存储在一个CHAR(80)中的HelloWorld数值数据类型可以存储整数、浮点数和实数最高精度为38位数值数据类型的声明语法：NUMBER[(p[,s])]P表示精度，S表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒主要的日期时间类型有：DATE-存储日期和时间部分，精确到整个的秒TIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位3.2Oracle数据类型NUMBER类型的示例：格式输入的数字实际的存储NUMBER1234.5671234.567NUMBER(5,2)123.4567123.46NUMBER(4,2)12345.67超过了所指定精度，不能存储日期类型的示例：NUMBER(5,2)：可以存储介于-999.99~999.99之间所有值。不要依赖于一个默认日期格式！如：insertintot(date_column)values(‘01/02/03’);较好的做法：insertintot(date_column)values(to_date(‘01/02/03’,‘DD/MM/YY’));更好的做法：insertintot(date_column)values(to_date(‘01/02/2003’,‘DD/MM/YYYY’));insertintoemp(DOB)values(to_date(‘01/02/10’,‘DD/MM/YY’));你能确定2010还是1910？向日期增加/减时间时间单位操作描述N秒DATE+n/86400一天86400秒相当于n/24/60/60DATE+NUMTODSINTERVAL(n,’second’)Numtodsinterval(n,‘second’)日/秒数间隔函数时间单位操作描述N分钟DATE+n/1440一天1440分钟相当于n/24/60DATE+NUMTODSINTERVAL(n,’minute’)Numtodsinterval(n,‘minute’)时间单位操作描述N小时DATE+n/24一天24小时DATE+NUMTODSINTERVAL(n,’hour’)Numtodsinterval(n,‘hour’)时间单位操作描述N天DATE+n增加或减去n天时间单位操作描述N周DATE+7*n一周7天时间单位操作描述N月ADD_MONTHSADD_MONTHS(DATE,n)DATE+NUMTOYMINTERVAL(n,’month’)numtoyminterval(n,‘month’)时间单位操作描述N年ADD_MONTHSADD_MONTHS(DATE,12*n)DATE+NUMTOYMINTERVAL(n,’year’)numtoyminterval(n,‘year’)建议：1.使用NUMTODSINTERVAL函数来增加小时、分钟和秒；2.使用ADD_MONTHS函数来增加月和年。日期增加示例：altersessionsetnls_date_format=‘yyyy-mm-ddhh24:mi:ss’;selectdt,add_months(dt,1)from(selectto_date(‘2001-02-28’,’yyyy-mm-dd’)dtfromdual);selectdt,add_months(dt,1)from(selectto_date(‘2000-02-29’,’yyyy-mm-dd’)dtfromdual);ADD_MONTHS函数专门处理月末日期没问题！selectdt,dt+numtoyminterval(1,’month’)from(selectto_date(‘2000-01-30’,’yyyy-mm-dd’)dtfromdual);Numtoyminterval函数进行下面操作时出错！！！EXTRACT函数。格式：EXTRACT(fmtfromSYSDATE[SYSTIMESTAMP])fmt:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND其中HOUR、MINUTE、SECOND必须与TIMESTAMP类型SelectSYSDATE“date”,EXTRACT(YEARfromSYSDATE)“year”,EXTRACT(MONTHfromSYSDATE)“month”,EXTRACT(DAYfromSYSDATE)“day”,EXTRACT(HOURfromSYSTIMESTAMP)“hour”,EXTRACT(MINUTEfromSYSTIMESTAMP)“minute”,EXTRACT(SECONDfromSYSTIMESTAMP)“second”,fromdual;请注意HOUR值！Oracle中伪列就像一个表列，但是它并没有存储在表中伪列可以从表中查询，但不能插入、更新和删除它们的值常用的伪列有ROWID和ROWNUMROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数伪列SQLSELECTROWID,ENAMEFROMEMPWHERESAL2000;ROWIDAAAMgzAAEAAAAAgAADAAAMgzAAEAAAAAgAAFAAAMgzAAEAAAAAgAAGAAAMgzAAEAAAAAgAAHAAAMgzAAEAAAAAgAAIAAAMgzAAEAAAAAgAAMENAMEJONESBLAKECLARKSCOTTKINGFORDSQLSELECTROWNUM,ENAME,JOB,SALFROMEMPWHEREROWNUM=5;ROWNUMENAMEJOBSAL12345SMITHALLENWARDJONESMARTINCLERKSALESMANSALESMANMANAGERSALESMAN8001600125029751250ROWID：ROWNUM：3.2Oracle数据类型-LOB类型Oracle支持4种类型的LOB(largeobject)。CLOB：字符LOB。这种类型用于存储大量的文本信息，如XML或者纯文本。需要进行字符集转化。NCLOB：另一种字符LOB。采用的是数据库的国家字符集，而不是数据库默认的BLOB：二进制LOB。存储大量的二进制信息，如字处理文档，图像和你能想到的任何其他数据。BFILE：二进制文件LOB。存储的只是操作系统中某个文件的一个指针。3.3SQL函数Oracle提供一系列用于执行特定操作的函数SQL函数带有一个或多个参数并返回一个值以下是SQL函数的分类：SQL函数单行函数分析函数分组函数单行函数分类单行函数对于从表中查询的每一行只返回一个值可以出现在SELECT子句中和WHERE子句中单行函数可以大致划分为：日期函数数字函数字符函数转换函数其他函数日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果日期函数包括：ADD_MONTHSMONTHS_BETWEENLAST_DAYROUNDNEXT_DAYTRUNCEXTRACT日期函数字符函数2-1函数输入输出Initcap(char)Selectinitcap(‘hello’)fromdual;HelloLower(char)Selectlower(‘FUN’)fromdual;funUpper(char)Selectupper(‘sun’)fromdual;SUNLtrim(char,set)Selectltrim(‘xyzadams’,’xyz’)fromdual;adamsRtrim(char,set)Selectrtrim(‘xyzadams’,’ams’)fromdual;xyzadTranslate(char,from,to)Selecttranslate(‘jack’,’j’,’b’)fromdual;backReplace(char,searchstring,[repstring])Selectreplace(‘jackandjue’,’j’,’bl’)fromdual;blackandblueInstr(char,m,n)Selectinstr(‘worldwide’,’d’)fromdual;5Substr(char,m,n)Selectsubstr(‘abcdefg’,3,2)fromdual;cdConcat(expr1,expr2)Selectconcat(‘Hello’,’world’)fromdual;Helloworld字符函数字符函数接受字符输入并返回字符或数值字符函数2-2SQLSELECTCHR(67)FROMdual;以下是一些其它的字符函数：CHR和ASCIILPAD和RPADTRIMLENGTHDECODESQLSELECTLPAD(‘function’,15,’=’)FROMdual;SQLSELECTTRIM(9from9999876789999)FROMdual;SQLSELECTLENGTH('frances')FROMdual;SQLSELECTvencode,DECODE(venname,'frances','Francis')nameFROMvendor_masterWHEREvencode='v001';DECODE函数DECODE(input_value,value,result[,value,result…][,default_result]);格式：示例：selectid,decode(sign(score-85),1,'优秀',0,'优秀',-1,decode(sign(score-70),1,'良好',0,'良好',-1,decode(sign(score-60),1,'及格',0,'及格',-1,'不及格')))fromstudent；数字函数数字函数接受数字输入并返回数值结果函数输入输出Abs(n)Selectabs(-15)fromdual;15Ceil(n)Selectceil(44.778)fromdual;45Cos(n)Selectcos(180)fromdual;-.5984601Cosh(n)Selectcosh(0)fromdual;1Floor(n)Selectfloor(100.2)f