第三章SQL和OracleSQL函数.ppt

第三章SQL和OracleSQL函数 本章要点 SQL数据类型OracleSQL函数 SQL是StructuredQueryLanguage 结构化查询语言 的首字母缩写词SQL是数据库语言 Oracle使用该语言存储和检索信息表是主要的数据库对象 用于存储数据 通过SQL可以实现与Oracle服务器的通信 发送SQL查询 用户wang 3 1SQL简介 SQL支持下列类别的命令 数据定义语言 DDL 数据操纵语言 DML 事务控制语言 TCL 数据控制语言 DCL 数据定义语言 CREATE ALTER DROP 数据操纵语言 INSERT SELECT DELETE UPDATE 事务控制语言 COMMIT SAVEPOINT ROLLBACK 数据控制语言 GRANT REVOKE 3 1SQL简介 字符数据类型 CHAR VARCHAR2 LONG 当需要固定长度的字符串时 使用CHAR数据类型 CHAR数据类型存储字母数字值 CHAR数据类型的列长度可以是1到2000个字节 VARCHAR2数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值VARCHAR2数据类型的大小在1至4000个字节范围内 LONG数据类型存储可变长度字符数据LONG数据类型最多能存储2GB 2千兆字节 基本使用CLOB类型 3 2Oracle数据类型 H e l l o W o r l d 11 数据 长度字节 存储在一个VARCHAR2 80 中的HelloWorld H e l l o W o r l d 80 数据 长度字节 69空格 存储在一个CHAR 80 中的HelloWorld 数值数据类型可以存储整数 浮点数和实数最高精度为38位数值数据类型的声明语法 NUMBER p s P表示精度 S表示小数点的位数 日期时间数据类型存储日期和时间值 包括年 月 日 小时 分钟 秒主要的日期时间类型有 DATE 存储日期和时间部分 精确到整个的秒TIMESTAMP 存储日期 时间和时区信息 秒值精确到小数点后6位 3 2Oracle数据类型 NUMBER类型的示例 日期类型的示例 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 向日期增加 减时间 建议 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和ROWNUM ROWID是表中行的存储地址 该地址可以唯一地标识数据库中的一行 可以使用ROWID伪列快速地定位表中的一行 ROWNUM是查询返回的结果集中行的序号 可以使用它来限制查询返回的行数 伪列 SQL SELECTROWID ENAMEFROMEMPWHERESAL 2000 ROWIDAAAMgzAAEAAAAAgAADAAAMgzAAEAAAAAgAAFAAAMgzAAEAAAAAgAAGAAAMgzAAEAAAAAgAAHAAAMgzAAEAAAAAgAAIAAAMgzAAEAAAAAgAAM ENAMEJONESBLAKECLARKSCOTTKINGFORD SQL SELECTROWNUM ENAME JOB SALFROMEMPWHEREROWNUM 5 ROWNUMENAME JOB SAL 12345 SMITHALLENWARDJONESMARTIN CLERKSALESMANSALESMANMANAGERSALESMAN 8001600125029751250 ROWID 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 MONTHSMONTHS BETWEENLAST DAYROUNDNEXT DAYTRUNCEXTRACT 日期函数 字符函数2 1 字符函数 字符函数接受字符输入并返回字符或数值 字符函数2 2 SQL SELECTCHR 67 FROMdual 以下是一些其它的字符函数 CHR和ASCIILPAD和RPADTRIMLENGTHDECODE SQL SELECTLPAD function 15 FROMdual SQL SELECTTRIM 9from9999876789999 FROMdual SQL SELECTLENGTH frances FROMdual SQL SELECTvencode 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 数字函数 数字函数接受数字输入并返回数值结果 数字函数 转换函数 转换函数将值从一种数据类型转换为另一种数据类型常用的转换函数有 TO CHARTO DATETO NUMBER SELECTTO CHAR sysdate YYYY 年 fmMM 月 fmDD 日 HH24 MI SS FROMdual SELECTTO CHAR itemrate C99999 FROMitemfile SELECTTO DATE 2005 12 06 yyyy mm dd FROMdual SELECTTO NUMBER 100 FROMdual 其它函数 以下是几个用来转换空值的函数 NVLNVL2NULLIF SELECTitemdesc NVL re level 0 FROMitemfile SELECTitemdesc NVL2 re level re level max level FROMitemfile SELECTitemdesc NULLIF re level max level FROMitemfile 函数的标准格式是 DUMP expr return fmt start position length DUMP函数 expr 这个参数是要进行分析的表达式 数字或字符串等 可以是各个类型的值 return fmt 指返回参数的格式 1 8 以8进制返回结果的值2 10 以10进制返回结果的值 默认 3 16 以16进制返回结果的值4 17 以单字符的形式返回结果的值5 1000 以上4种加上1000 表示在返回值中加上当前字符集start position 开始进行返回的字符位置length 需要返回的字符长度 一些DUMP函数示例 1 SELECTDUMP abc FROMDUAL Typ 96Len 3 97 98 99 2 SELECTDUMP abc 1016 FROMDUAL Typ 96Len 3CharacterSet UTF8 61 62 63 3 SELECTDUMP abc 17 2 2 FROMDUAL Typ 96Len 3 b c 4 createtablet dtdate tstimestamp 9 insertintotvalues sysdate systimestamp selectdump dt 10 dump dump ts 10 dumpfromt Typ 12Len 7 120 114 3 16 18 19 55Typ 180Len 11 120 114 3 16 18 19 55 8 88 59 0 1VARCHAR22NUMBER8LONG12DATE23RAW24LONGRAW69ROWID96CHAR112CLOB113BLOB114BFILE180TIMESTAMP181TIMESTAMPWITHTIMEZONE182INTERVALYEARTOMONTH183INTERVALDAYTOSECOND208UROWID231TIMESTAMPWITHLOCALTIMEZONE 分组函数 分组函数基于一组行来返回结果为每一组行返回一个值 AVG MIN MAX SUM COUNT SELECTAVG re level FROMitemfileWHEREp category accessories SELECTMAX max level FROMitemfile SELECTSUM itemrate max level FROMitemfile SELECTCOUNT FROMitemfile SELECTCOUNT itemrate FROMitemfile SELECTCOUNT DISTINCTqty hand FROMitemfile 分组函数 GROUPBY和HAVING子句 GROUPBY子句用于将信息划分为更小的组每一组行返回针对该组的单个结果HAVING子句用于指定GROUPBY子句检索行的条件 SELECTp category MAX itemrate FROMitemfileGROUPBYp category SELECTp category MAX itemrate FROMitemfileGROUPBYp categoryHAVINGp categoryNOTIN accessories 分析函数2 1 分析函数根据一组行来计算聚合值用于计算完成聚集的累计排名 移动平均数等分析函数为每组记录返回多个行 分析函数 DENSE RANK RANK ROW NUMBER 以下三个分析函数用于计算一个行在一组有序行中的排位 序号从1开始ROW NUMBER返回连续的排位 不论值是否相等RANK具有相等值的行排位相同 序数随后跳跃DENSE RANK具有相等值的行排位相同 序号是连续的 SELECTename