单表查询及函数

1、单表查询及函数查询查询语句语句整体整体结构结构关于关于SELECT子句子句关于关于ORDER BY子句子句关于关于WHERE子句子句 运算符运算符 表达式表达式关于关于GROUP BY子句子句关于关于HAVING子句子句 SELECTSELECT 字段清单 FROMFROM 表名【WHEREWHERE 查询条件】【GTOUP BYGTOUP BY 分组表达式】【HAVINGHAVING 分组查询条件】【ORDER BY ORDER BY 排序表达式】可选子句From子子句句Where子子句句Group By子句子句Having子子句句Select子子句句Order By子句子句SELECTSE

2、LECT 字段清单FROMFROM 表名n 指定多列时，各列之间用逗号分开；n 指定表中所有列时，可用*实现；n AS：在列名后使用此关键字来指定列的别名；n Distinct：在字段清单中使用此关键字，可去除查询结果中的重复信息。 Select Riskcode 险种,Kindcode From Prpcitemkindselect t.uuid as 编号编号,t.username 名称名称from tb_user_info tSELECTSELECT 字段清单FROMFROM 表名ORDER BY ORDER BY 排序表达式n Order By子句要放在所有子句的最后；n 排序表达式可

3、以指定多列，各列之间用逗号分开，按列名顺序确定优先级；n ASC：升序（默认）；n DESC：降序。 Select Riskcode,Kindcode From Prpcitemkind Order By Riskcode ,Kindcodeselect t.uuid as 编号编号, t.username 名称名称, t.addtimefrom tb_user_info torder by t.addtime descSELECTSELECT 字段清单FROMFROM 表名WHEREWHERE 查询条件ORDER BY ORDER BY 排序表达式n 查询条件可以是单条件，也可以是组合条件；

4、n 构成查询条件时要使用运算符。n查询条件中字符串和日期要用单引号扩起来；n字符串是大小写敏感的；日期值是格式敏感的，缺省的日期格式是 DD-MON-RR Select Riskcode,Kindcode From Prpcitemkind Where Riskcode=2701 Order By Riskcode ,Kindcodeselect t.lotteryid 彩种, t.lotteryissue 期号, from tb_lottery_info twhere t.starttimestampto_date(2012-03-06,yyyy-mm-dd)order by t.lotte

5、ryid asc, t.lotteryissue desc运算符运算符说明说明=等于大于=大于等于=小于等于或!=不等于!不大于 Select *From PrpcfeeWhere Currecy1=CNYSelect *From PrpcfeeWhere Currecy1=Currencyselect t.unionorderid 方案号from tb_lotteryschemeinfo twhere t.bonusvalue10000select t.unionorderid 方案号from tb_lotteryschemeinfo twhere t.bonusvaluet.schemev

6、alue在两值（包含）间进行比较筛选，例：select t.unionorderid 方案号from tb_lotteryschemeinfo twhere t.bonusvalue/ 100 between 500 and 50000select t.unionorderid 方案号 from tb_lotteryschemeinfo t where t.bonusvalue / 100 = 500 and t.bonusvalue / 100 5000 and t.bonusvalue / 100 5000表示“与”的关系，即AND连接的条件表达式均为“True”时，Where子句的查询结

7、果才为“True”select t.lotterylevel2id, t.lotteryvalue / 100, t.bonusvalue / 100 from tb_lottery_orderdetails twhere t.lotteryvalue / 100 5000 or t.bonusvalue / 100 5000表示“或”的关系，即OR连接的条件表达式中只要有一个为“True”时，Where子句的查询结果即为“True”用于对搜索条件的布尔值求反；NOT运算符不能单独应用，而要与其他运算符联合使用 NOT BETWEENAND NOT IN NOT LIKE IS NOT NUL

8、L select t.uuid,t.username,t.email from tb_user_info twhere t.email is not null运算符号运算符号说明说明+加-减*乘/除%求余l对NUMBER型数据可以使用的算术运算符号有（+ - * /）l对DATE型数据可以使用的算术运算符号有（+ -）l算术表达式可用于Select子句、Where子句 select t.lotterylevel2id,t.uuid from tb_lottery_orderdetails t where (t.bonusvalue - t.lotteryvalue) / 100 500 and

9、 t.tickettime sysdate - 101算术运算2 2比较运算3NOT4AND5OR括号可以改变优先级 select t.lotterylevel2id,t.uuid from tb_lottery_orderdetails t where (t.bonusvalue - t.lotteryvalue) / 100 500 and t.tickettime sysdate - 10l 作用：将列或字符与其它列连结；l 相连接的列必须有相同或相近的数据类型；l 可用于Select子句、Where子句 Select Policyno 保单号, Currency| |Sumpremiu

10、m 保费 From Prpcmain Where Riskcode=0101 Select 彩民 | t.username | 于 | t.wintime | 日中奖 | t.bonusvalue | 元 from tb_lottery_winner tWhere t.wintime = 2012-02-29SELECTSELECT 字段清单FROMFROM 表名WHEREWHERE 查询条件Group BY Group BY 列名，列名n 既可以按照单列分组，也可以按照多列分组；n 出现在SELECT子句中的字段，如果不是用在组函数中，那么必须出现在GROUP BY子句中； Select j

11、ob,avg(sal) From emp Where Group Group By By job;SELECTSELECT 字段清单FROMFROM 表名WHEREWHERE 查询条件Group BY Group BY 列名，列名HavingHaving 分组查询条件n Having子句的作用是对分组结果进行筛选，去除不满足条件的组；n 包含在Having子句中的列必须是组列，即通过分组函数构成的条件； Select job,avg(sal) From emp Where Group By job Having avg(sal)2000;pHaving子句与Where子句的相似之处在于，两者都

12、定义了筛选条件。p但Having子句与Where子句不同在于，Having子句与组有关，而Where与单个行有关；LOWER（字符串）把字符串全部转换为小写UPPER（字符串）把字符串全部转换为大写LTRIM（字符串）把字符串头部的空格去掉RTRIM（字符串）把字符串尾部的空格去掉LENGTH(字符串)返回指定字符串的长度LOWER（字符串）LOWER（aBc）abcUPPER（字符串）UPPER（aBc）ABCLTRIM（字符串）LTRIM（ aBc）aBcRTRIM（字符串）RTRIM（ aBc ） aBc LENGTH(字符串)LENGTH(aBc)3SUBSTR（字符串，n，m）返回部

13、分字符串，返回的子串是从字符串左边第n个字符起，m个字符SUBSTR（字符串，n，m）SUBSTR（abcde,2,3） bcdABS（数字表达式）返回表达式的绝对值ROUND（数字表达式，n）四舍五入到指定的小数位TRUNC（数字表达式，n）将数字表达式截断到指定的小数位MOD（被除数，除数）返回相除后的余数ABS（数字表达式）ABS（-190）190ROUND（数字表达式，n）ROUND(45.926, 2)45.93TRUNC（数字表达式，n）TRUNC(45.926, 2)45.92MOD（被除数，除数）MOD(1600, 300)100SYSDATE返回系统当前日期TRUNC(dat

14、e,格式)按照给出的格式要求将日期截断,如：fmt=mi表示保留分,截断秒格式YY（年）,MM（月）,DD（日）,HH（时）,MI（分）SYSDATESelect sysdateFrom dual2010/5/12 15:22:21TRUNC(date,格式)trunc(sysdate,hh)2010/5/12 15:00:00p 将日期型或数值型的表达式，按指定格式转换为字 符型； 在转换日期为字符串的时候，日期中的年，月，日，时，分，秒都需要按相应的“格式”来控制转换后的显示格式。 在转换数值为字符串的时候，数值也要按相应的“格式”来控制转换后的显示格式。p YYYY,YYY,YY显示4位

15、，3位，2位的数字年份YEAR显示“年”的英文拼写MM显示2位的数字月份MONTH显示实际月份的英文全拼名称MON显示实际月份的英文缩写DD显示2位的数字“天”DAY显示实际星期的英文全拼名称DY显示实际星期的英文缩写年、月、日的格式定义p AM显示为上午或下午HH24,HH12显示24小时制的“时”或12小时制的“时”MI显示分钟数字SS显示秒的数字SP显示数字的英文拼写TH显示数字的在英文中序数词的形式“特殊字符”显示在日期时间中加入的特殊字符时、分、秒的格式定义p 函数函数转换后的显示转换后的显示TO_CHAR(1980-12-17,YYYY-MM-DD:DAY) 1980-12-17:

16、星期三TO_CHAR(2007-07-24 23:55:17, YYYY-MM-DD HH24:MI:SS AM) 2007-07-24 23:55:17 下午TO_CHAR(2007-10-24 23:55:17, DD of MONTH) 24 of OCTOBERTO_CHAR(2007-07-14 23:55:17, ddspth) fourteenthp 函数中的指定格式函数中的指定格式转换后的显示转换后的显示9对应显示一个实际的数字字符0对应强制显示0字符$显示“$”符号L显示本地的货币符.显示小数点，显示千分位符数字格式定义p 函数函数转换后显示转换后显示TO_CHAR(800.

17、00, $99,999.99） $800.00TO_CHAR(800.00, L00,000.00) RMB00,800.00p 将字符型的表达式，按指定格式转换为数字（“格式”同TO_CHAR函数的数字格式定义）； TO_NUMBER($123,345.99,$999,999.00) 转换后的结果：123345.99p 将字符型的表达式，按指定格式转换为日期（“格式”同TO_CHAR函数的日期及时间格式定义）； TO_DATE(1998-8-23,YYYY-MM-DD) 转换后的结果：1998-8-23NVL (表达式，指定值)将空值转换为指定的值；表达式可以是日期，字符，数字；“表达式”与

18、“指定值”数据类型必须匹配 NVL2 (表达式，值1，值2)若“表达式”不为Null，则返回“值1”，空返回“值2”Select a.policyno, quotationno, NVL(quotationno,直接出单), a.* From Gupolicymain aSelect Policyno 保单号, （Jfeeflag，1，见费出单) 标志From Prpcmain查询结果：保单号 标志301012009110000000001 301012009120000000005 见费出单 DECODE（表达式, 判断值, 结果值 , 判断值, 结果值,）Select Policyno,

19、Decode(riskcode,0101,财产基本险)From GupolicyriskSelect Policyno 保单号, (Jfeeflag=1 见费出单) 标志From Prpcmain CASECASEWHENWHEN 条件表达式 THENTHEN 表达式返回“True”时的结果ELSEELSE 表达式返回“False”时的结果ENDENDSelect Policyno, (CASE WHEN Riskcode=0101 THEN 财产基本险 END)From GupolicyriskEMPLOYEES440095003500640010033 DEPTNO SAL- - 10 2

20、450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600员工表中工资的最高值员工表中工资的最高值 MAX(SAL)- 5000分组函数也叫多行函数。分组函数对多行输入值进行计算，得到多行对应的单行结果。在通常的情况下，会对一个表总的数据进行分组，得到多组数据，每个组中包括一行或多行数据。分组函数对每组记录分别进行计算，每组返回一个结果。最终产生多个组对应的数据集合。分组有多少，最终的结果集就有多少行。 DEPTNO SAL- - 10 2450 10 5000 10 1300 20 800 20 1100 20 300

21、0 20 3000 20 2975 30 1600员工表中工资的最高值员工表中工资的最高值 MAX(SAL)- 5000函数名称函数名称函数功能函数功能说明说明SUM（）返回一组数据的总和针对数值类型的数据AVG（）返回一组数据的平均值MAX（）返回一组数据的最大值适用于任何数据类型的数据MIN（）返回一组数据的最小值COUNT（）返回一组数据中的记录数l分组函数适用于Select子句，Having子句说明：1.只适用于数值类型的数据2.DISTINCT为可选项3.如果指定列中含有NULL值，SUM函数会忽略该值Select SUMSUM（Sumpremium） 总保费From Prpcmain Where Riskcode=0101Select SUM (Sumnetpremium)From GupolicyriskWhere Riskcode = 0101说明：1.只适用于数值类型的数据2.DISTINCT为可选项3.如果指定列中含有NULL值，AVG函数会忽略该值Select AVGAVG（Sumpremium） 平均保费From Prpcmain Where Riskcode=0101Select AVG (Sumnetpremium)From GupolicyriskWhere Riskcode = 0101