第三章SQL和OracleSQL函数

1、本章要点本章要点 SQL数据类型数据类型OracleSQL函数函数SQL 是是 Structured Query Language（结构化查询语言）（结构化查询语言）的首字母缩写词的首字母缩写词SQL 是数据库语言，是数据库语言，Oracle 使用该语言存储和检索信息使用该语言存储和检索信息表是主要的数据库对象，用于存储数据表是主要的数据库对象，用于存储数据通过通过 SQL可以实现与可以实现与 Oracle 服务器的通信服务器的通信SELECT ename FROM Emp;发送 SQL 查询Oracle 服务器enameBLAKESMITH ALLEN DAVID MARTIN发送命令输出到

2、用户端用户wang3.1 SQL简介简介SQL 支持下列类别的命令：支持下列类别的命令：数据定义语言（数据定义语言（DDL）数据操纵语言（数据操纵语言（DML）事务控制语言（事务控制语言（TCL）数据控制语言（数据控制语言（DCL）数据定义语言数据定义语言 CREATEALTERDROP数据操纵语言数据操纵语言 INSERTSELECTDELETEUPDATE事务控制语言事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言数据控制语言 GRANTREVOKE3.1 SQL简介简介字符数据类型字符数据类型CHARVARCHAR2LONG当需要固定长度的字符串时，使用当需要固定长

3、度的字符串时，使用 CHAR 数据类型。数据类型。CHAR 数据类型存储字母数字值。数据类型存储字母数字值。CHAR 数据类型的列长度可以是数据类型的列长度可以是 1 到到 2000 个字节。个字节。VARCHAR2数据类型支持可变长度字符串数据类型支持可变长度字符串VARCHAR2数据类型存储字母数字值数据类型存储字母数字值VARCHAR2数据类型的大小在数据类型的大小在1至至4000个字节范围内个字节范围内LONG 数据类型存储可变长度字符数据数据类型存储可变长度字符数据LONG 数据类型最多能存储数据类型最多能存储 2GB(2千兆字节千兆字节)基本使用基本使用CLOB类型类型3.2 Or

4、acle 数据类型数据类型H elloWorld11数据数据长度字节长度字节存储在一个存储在一个VARCHAR2(80)中的中的Hello WorldH elloWorld80数据数据长度字节长度字节69空格空格存储在一个存储在一个CHAR(80)中的中的Hello World数值数据类型数值数据类型可以存储整数、浮点数和实数可以存储整数、浮点数和实数最高精度为最高精度为 38 位位数值数据类型的声明语法：数值数据类型的声明语法：NUMBER ( p, s)P表示精度，表示精度，S表示小数点的位数表示小数点的位数日期时间数据类型存储日期和时间值，包括年、月、日，日期时间数据类型存储日期和时间值

5、，包括年、月、日，小时、分钟、秒小时、分钟、秒主要的日期时间类型有：主要的日期时间类型有：DATE - 存储日期和时间部分，精确到整个的秒存储日期和时间部分，精确到整个的秒TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点存储日期、时间和时区信息，秒值精确到小数点后后6位位3.2 Oracle 数据类型数据类型 NUMBER 类型的示例：类型的示例：格式格式输入的数字输入的数字实际的存储实际的存储NUMBER1234.5671234.567NUMBER(5,2)123.4567123.46NUMBER(4,2)12345.67超过了所指定精度，不能存储日期类型日期类型的示例：的

6、示例：NUMBER(5,2)：可以存储介于：可以存储介于-999.99999.99之间所有值。之间所有值。不要依赖于一个默认日期格式！不要依赖于一个默认日期格式！如：insert into t(date_column) values(01/02/03); 较好的做法：insert into t(date_column) values(to_date(01/02/03, DD/MM/YY) );更好的做法：insert into t(date_column) values(to_date(01/02/2003, DD/MM/YYYY) );insert into emp(DOB) values(

7、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)时间单位时间单位操作

8、操作描述描述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+NUM

9、TOYMINTERVAL(n, year) numtoyminterval(n, year)建议：建议：1. 使用使用NUMTODSINTERVAL函数来增加小时、分钟和秒；函数来增加小时、分钟和秒； 2. 使用使用ADD_MONTHS函数来增加月和年。函数来增加月和年。 日期增加示例：日期增加示例：alter session set nls_date_format = yyyy-mm-dd hh24:mi:ss;select dt,add_months(dt,1) from(select to_date(2001-02-28,yyyy-mm-dd) dt from dual);select

10、dt,add_months(dt,1) from(select to_date(2000-02-29,yyyy-mm-dd) dt from dual);ADD_MONTHS函数专门处理月末日期没问题！select dt,dt+numtoyminterval(1,month) from(select to_date(2000-01-30,yyyy-mm-dd) dt from dual);Numtoyminterval函数进行下面操作时出错！EXTRACT函数函数。格式：格式：EXTRACT(fmt from SYSDATESYSTIMESTAMP)fmt:YEAR、MONTH、DAY、HOU

11、R、MINUTE、SECOND其中其中HOUR、MINUTE、SECOND必须与必须与TIMESTAMP类型类型Select SYSDATE “date” , EXTRACT(YEAR from SYSDATE) “year”, EXTRACT(MONTH from SYSDATE) “month”, EXTRACT(DAY from SYSDATE) “day”, EXTRACT(HOUR from SYSTIMESTAMP) “hour”, EXTRACT(MINUTE from SYSTIMESTAMP) “minute”, EXTRACT(SECOND from SYSTIMESTAM

12、P) “second”, from dual;请注意请注意HOUR值！值！q Oracle 中伪列就像一个表列，但是它并没有存储在表中q 伪列可以从表中查询，但不能插入、更新和删除它们的值q 常用的伪列有ROWID和ROWNUMROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数伪列伪列SQL SELECT ROWID,ENAME FROM EMP WHERE SAL2000;ROWIDAAAMgzAAEAAAAAgAADAAAMgzAAEAAAAAgAAFA

13、AAMgzAAEAAAAAgAAGAAAMgzAAEAAAAAgAAHAAAMgzAAEAAAAAgAAIAAAMgzAAEAAAAAgAAMENAMEJONESBLAKECLARKSCOTTKINGFORDSQL SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM SELECT CHR(67) FROM dual;q以下是一些其它的字符函数：qCHR和ASCIIqLPAD和RPADqTRIMqLENGTHqDECODESQL SELECT LPAD(function,15,=) FROM dual;SQL SELECT TRIM(9 from

14、9999876789999) FROM dual;SQL SELECT LENGTH(frances) FROM dual;SQL SELECT vencode, DECODE(venname,frances,Francis) name FROM vendor_master WHERE vencode=v001;DECODE函数函数DECODE(input_value,value,result,value,result,default_result);格式：格式：示例：示例：select id, decode(sign(score-85),1,优秀优秀,0,优秀优秀,-1,decode(sig

15、n(score-70),1,良好良好,0,良好良好,-1,decode(sign(score-60),1,及格及格,0,及格及格,-1,不及格不及格)from student；数字函数数字函数q数字函数接受数字输入并返回数值结果 函数函数 输入输入 输出输出Abs(n) Select abs(-15) from dual; 15Ceil(n) Select ceil(44.778) from dual; 45Cos(n) Select cos(180) from dual; -.5984601 Cosh(n) Select cosh(0) from dual; 1Floor(n) Select

16、 floor(100.2) from dual; 100Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select mod(10,3) from dual; 1Round(m,n) Select round(100.256,2) from dual; 100.26 Trunc(m,n) Select trunc(100.256,2) from dual; 100.25 Sqrt(n) Select sqrt(4) from dual; 2 Sign(n)Select sign(-30) from dual;-1数字函数数字函数转换函数转

17、换函数q转换函数将值从一种数据类型转换为另一种数据类型q常用的转换函数有：qTO_CHARqTO_DATEqTO_NUMBERSELECT TO_CHAR(sysdate,YYYY年年fmMM月月fmDD日日 HH24:MI:SS)FROM dual;SELECT TO_CHAR(itemrate,C99999) FROM itemfile;SELECT TO_DATE(2005-12-06 , yyyy-mm-dd)FROM dual;SELECT TO_NUMBER(100) FROM dual;其它函数其它函数q以下是几个用来转换空值的函数：qNVLqNVL2qNULLIFSELECT

18、itemdesc, NVL(re_level,0) FROM itemfile;SELECT itemdesc, NVL2(re_level, re_level, max_level) FROM itemfile;SELECT itemdesc, NULLIF(re_level, max_level) FROM itemfile;函数的标准格式是：函数的标准格式是：DUMP(expr,return_fmt,start_position,length)DUMP函数函数expr：这个参数是要进行分析的表达式(数字或字符串等，可以是各个类型的值)return_fmt：指返回参数的格式： 1) 8：以

19、8进制返回结果的值 2) 10：以10进制返回结果的值(默认) 3) 16：以16进制返回结果的值 4) 17：以单字符的形式返回结果的值 5) 1000：以上4种加上1000，表示在返回值中加上当前字符集 start_position：开始进行返回的字符位置 length：需要返回的字符长度一些DUMP函数示例：1. SELECT DUMP(abc) FROM DUAL;Typ=96 Len=3: 97,98,992. SELECT DUMP(abc,1016) FROM DUAL;Typ=96 Len=3 CharacterSet=UTF8: 61,62,633. SELECT DUMP(

20、abc,17,2,2) FROM DUAL;Typ=96 Len=3: b,c4. create table t (dt date, ts timestamp(9); insert into t values(sysdate, systimestamp); select dump(dt,10)dump, dump(ts,10)dump from t;Typ=12 Len=7:120,114,3,16,18,19,55Typ=180 Len=11:120,114,3,16,18,19,55,8,88,59,0 1 VARCHAR2 2 NUMBER 8 LONG 12 DATE 23 RAW 2

21、4 LONG RAW 69 ROWID 96 CHAR 112 CLOB 113 BLOB 114 BFILE 180 TIMESTAMP 181 TIMESTAMP WITH TIMEZONE 182 INTERVAL YEAR TO MONTH 183 INTERVAL DAY TO SECOND 208 UROWID 231 TIMESTAMP WITH LOCAL TIMEZONE分组函数分组函数q分组函数基于一组行来返回结果q为每一组行返回一个值AVGMINMAXSUMCOUNTSELECT AVG(re_level) FROM itemfile WHERE p_category=accessories;SELECT MAX(max_level) FROM itemfile;SELECT SUM(itemrate*max_level) FROM itemfile;SELECT COUNT(*) FROM itemfile;SELECT COUNT(itemrate) FROM itemfile; SELECT COUNT(DISTINCT qty_hand) FROM itemfile;