单表查询及函数

查询SQL培训——单表查询及函数培训要点查询语句整体结构关于SELECT子句关于ORDERBY子句关于WHERE子句运算符表达式关于GROUPBY子句关于HAVING子句查询语句的整体结构

SELECT

字段清单

FROM

表名【WHERE

查询条件】

【GTOUPBY

分组表达式】【HAVING

分组查询条件】【ORDERBY排序表达式】可选子句SELECT语句From子句Where子句GroupBy子句Having子句Select子句OrderBy子句查询语句的执行顺序SELECT语句关于Select子句SELECT

字段清单FROM

表名指定多列时，各列之间用逗号分开；指定表中所有列时，可用*实现；AS：在列名后使用此关键字来指定列的别名；Distinct：在字段清单中使用此关键字，可去除查询结果中的重复信息。SelectDistinctRiskcodeAs

险种,KindcodeFromPrpcitemkind例SELECT语句selectt.uuidas编号,t.username名称fromtb_user_infot关于OrderBy子句SELECT

字段清单FROM

表名ORDERBY排序表达式OrderBy子句要放在所有子句的最后；排序表达式可以指定多列，各列之间用逗号分开，按列名顺序确定优先级；ASC：升序（默认）；DESC：降序。SelectDistinctRiskcode,KindcodeFromPrpcitemkindOrderByRiskcodeDESC,Kindcode例SELECT语句selectt.uuidas编号,t.username名称,

t.addtimefromtb_user_infotorderbyt.addtimedesc关于Where子句SELECT

字段清单FROM

表名WHERE

查询条件ORDERBY排序表达式

查询条件可以是单条件，也可以是组合条件；构成查询条件时要使用运算符。查询条件中字符串和日期要用单引号扩起来；字符串是大小写敏感的；日期值是格式敏感的，缺省的日期格式是'DD-MON-RR'

SelectDistinctRiskcode,KindcodeFromPrpcitemkindWhereRiskcode=‘2701’OrderByRiskcodeDESC,Kindcode例SELECT语句selectt.lotteryid彩种,t.lotteryissue期号,

fromtb_lottery_infotwheret.starttimestamp>to_date('2012-03-06','yyyy-mm-dd')orderbyt.lotteryidasc,t.lotteryissuedesc比较运算符运算符说明=等于>大于<小于>=大于等于<=小于等于<>或!=不等于!<不小于!>不大于

列名运算符常量值Select*FromPrpcfeeWhereCurrecy1=‘CNY’列名运算符列名Select*FromPrpcfeeWhereCurrecy1=Currency例运算符selectt.unionorderid方案号fromtb_lotteryschemeinfotwhere

t.bonusvalue>10000selectt.unionorderid方案号fromtb_lotteryschemeinfotwheret.bonusvalue>t.schemevalueBETWEEN…AND…运算符在两值（包含）间进行比较筛选，例：selectt.unionorderid方案号fromtb_lotteryschemeinfotwheret.bonusvalue/100between500and50000selectt.unionorderid方案号fromtb_lotteryschemeinfotwheret.bonusvalue/100>=500

and

t.bonusvalue/100<=50000运算符IN运算符在指定的多个条件值中进行比较筛选，只要满足其中一个值即条件成立，例：selectt.lotteryid,t.descriptionfromtd_lotterytwheret.lotteryidin(214,215,216,217)selectt.lotteryid,t.descriptionfromtd_lotterytwheret.lotteryid=214ort.lotteryid=215ort.lotteryid=216ort.lotteryid=217运算符LIKE运算符

查询条件部分匹配即可得查询结果，实现模糊查询；需与通配符一同使用；SQL提供的通配符有：“%”、“-”；%：可表示0个或多个字符；-：可表示一个字符只有CHAR、VARCHAR、TEXT类型的数据才能使用Like运算符和通配符；运算符LIKE运算符CNOCNAMECTIMESCOUNT1应用数学基础481202生物工程32803计算机软件基础32704计算机硬件基础24805模拟电路设计28906机械设计实践48687数据库设计16808设计理论28459计算机入门24150Course运算符

Select

*

From

CourseWhere

CnameLIKE

‘%设计%’ISNULL运算符

查询包含空值的记录；Select*FromPrplclaimWhereendcasedateISNULL例运算符

selectt.uuid,t.realusername,t.usermobile

from

tb_user_privateinfot

where

t.cardidisnullselectt.lotterylevel2id,

t.lotteryvalue/100,

t.bonusvalue/100

fromtb_lottery_orderdetailstwhere

t.lotteryvalue/100>5000

and

t.bonusvalue/100>5000逻辑运算符AND、OR、NOT表示“与”的关系，即AND连接的条件表达式均为“True”时，Where子句的查询结果才为“True”AND运算符selectt.lotterylevel2id,t.lotteryvalue/100,t.bonusvalue/100

fromtb_lottery_orderdetailstwheret.lotteryvalue/100>5000

ort.bonusvalue/100>5000表示“或”的关系，即OR连接的条件表达式中只要有一个为“True”时，Where子句的查询结果即为“True”OR运算符逻辑运算符AND、OR、NOT逻辑运算符AND、OR、NOT用于对搜索条件的布尔值求反；NOT运算符不能单独应用，而要与其他运算符联合使用NOTNOTBETWEEN……AND……NOTINNOTLIKEISNOTNULL

selectt.uuid,t.username,t.email

fromtb_user_infotwheret.emailisnotnull运算符算术运算符运算符号说明+加-减*乘/除%求余对NUMBER型数据可以使用的算术运算符号有（+-*/）对DATE型数据可以使用的算术运算符号有（+-）算术表达式可用于Select子句、Where子句例表达式

select

t.lotterylevel2id,t.uuid

fromtb_lottery_orderdetailst

where

(t.bonusvalue-t.lotteryvalue)/100>500

andt.tickettime>sysdate-10运算符的优先级1算术运算2比较运算3NOT4AND5OR括号可以改变优先级运算符

select

t.lotterylevel2id,t.uuid

fromtb_lottery_orderdetailst

where

(t.bonusvalue-t.lotteryvalue)/100>500

andt.tickettime>sysdate-10例连接符（||）作用：将列或字符与其它列连结；相连接的列必须有相同或相近的数据类型；可用于Select子句、Where子句SelectPolicyno保单号,Currency||’’||Sumpremium保费FromPrpcmainWhereRiskcode=‘0101’例表达式

Select‘彩民’

||

t.username||

‘于’

||

t.wintime||

‘日中奖’

||

t.bonusvalue||'元'

fromtb_lottery_winnertWhere

t.wintime='2012-02-29‘关于GroupBy子句SELECT

字段清单FROM

表名WHERE

查询条件GroupBY列名[，列名…]

既可以按照单列分组，也可以按照多列分组；出现在SELECT子句中的字段，如果不是用在组函数中，那么必须出现在GROUPBY子句中；

Selectjob,avg(sal)FromempWhereGroupByjob;例SELECT语句关于Having子句SELECT

字段清单FROM

表名WHERE

查询条件GroupBY列名[，列名…]Having

分组查询条件Having子句的作用是对分组结果进行筛选，去除不满足条件的组；

包含在Having子句中的列必须是组列，即通过分组函数构成的条件；

Selectjob,avg(sal)Fromemp

WhereGroupByjobHavingavg(sal)>2000;例SELECT语句Having子句与Where子句Having子句与Where子句的相似之处在于，两者都定义了筛选条件。但Having子句与Where子句不同在于，Having子句与组有关，而Where与单个行有关；SELECT语句单行函数单行函数字符函数数字函数日期函数转换函数其他函数函数字符函数函数格式参数及功能说明LOWER（字符串）把字符串全部转换为小写UPPER（字符串）把字符串全部转换为大写LTRIM（字符串）把字符串头部的空格去掉RTRIM（字符串）把字符串尾部的空格去掉LENGTH(字符串)返回指定字符串的长度函数格式举例结果LOWER（字符串）LOWER（’aBc’）abcUPPER（字符串）UPPER（’aBc’）ABCLTRIM（字符串）LTRIM（’aBc’）aBcRTRIM（字符串）RTRIM（’aBc’）

aBcLENGTH(字符串)LENGTH(‘aBc’)3单行函数字符函数函数格式参数及功能说明SUBSTR（字符串，n，m）返回部分字符串，返回的子串是从字符串左边第n个字符起，m个字符函数格式举例结果SUBSTR（字符串，n，m）SUBSTR（'abcde',2,3）bcd单行函数数字函数函数格式参数及功能说明ABS（数字表达式）返回表达式的绝对值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)100单行函数日期函数函数格式参数及功能说明SYSDATE返回系统当前日期TRUNC(date,格式)按照给出的格式要求将日期截断,如：fmt=mi表示保留分,截断秒格式—YY（年）,MM（月）,DD（日）,HH（时）,MI（分）函数格式举例结果SYSDATESelectsysdateFromdual2010/5/1215:22:21TRUNC(date,格式)trunc(sysdate,’hh’)2010/5/1215:00:00单行函数转换函数

TO_CHAR（表达式，‘格式’）

将日期型或数值型的表达式，按指定格式转换为字

符型；在转换日期为字符串的时候，日期中的年，月，日，时，分，秒都需要按相应的“格式”来控制转换后的显示格式。在转换数值为字符串的时候，数值也要按相应的“格式”来控制转换后的显示格式。单行函数转换函数

TO_CHAR（表达式，‘格式’）

函数中的指定格式转换后的显示YYYY,YYY,YY显示4位，3位，2位的数字年份YEAR显示“年”的英文拼写MM显示2位的数字月份MONTH显示实际月份的英文全拼名称MON显示实际月份的英文缩写DD显示2位的数字“天”DAY显示实际星期的英文全拼名称DY显示实际星期的英文缩写年、月、日的格式定义单行函数转换函数

TO_CHAR（表达式，‘格式’）

函数中的指定格式转换后的显示AM显示为上午或下午HH24,HH12显示24小时制的“时”或12小时制的“时”MI显示分钟数字SS显示秒的数字SP显示数字的英文拼写TH显示数字的在英文中序数词的形式“特殊字符”显示在日期时间中加入的特殊字符时、分、秒的格式定义单行函数转换函数

TO_CHAR（表达式，‘格式’）

函数转换后的显示TO_CHAR(‘1980-12-17’,'YYYY-MM-DD:DAY')1980-12-17:星期三TO_CHAR(‘2007-07-2423:55:17’,'YYYY-MM-DDHH24:MI:SSAM‘)2007-07-2423:55:17下午TO_CHAR(‘2007-10-2423:55:17’,'DD"of"MONTH‘)24ofOCTOBERTO_CHAR(‘2007-07-1423:55:17’,‘ddspth‘)fourteenth例单行函数转换函数

TO_CHAR（表达式，‘格式’）

函数中的指定格式转换后的显示9对应显示一个实际的数字字符0对应强制显示0字符$显示“$”符号L显示本地的货币符.显示小数点，显示千分位符数字格式定义单行函数转换函数

TO_CHAR（表达式，‘格式’）

函数转换后显示TO_CHAR(‘800.00’,‘$99,999.99’）

$800.00TO_CHAR(‘800.00’,‘L00,000.00’)RMB00,800.00例单行函数转换函数

TO_NUMBER（表达式，‘格式’）

将字符型的表达式，按指定格式转换为数字（“格式”同TO_CHAR函数的数字格式定义）；

TO_NUMBER('$123,345.99','$999,999.00')

转换后的结果：123345.99例单行函数转换函数

TO_DATE（表达式，‘格式’）

将字符型的表达式，按指定格式转换为日期（“格式”同TO_CHAR函数的日期及时间格式定义）；

TO_DATE('1998-8-23','YYYY-MM-DD')

转换后的结果：1998-8-23例单行函数其它函数-空值函数函数格式参数及功能说明NVL(表达式，指定值)将空值转换为指定的值；表达式可以是日期，字符，数字；“表达式”与“指定值”数据类型必须匹配NVL2(表达式，值1，值2)若“表达式”不为Null，则返回“值1”，空返回“值2”单行函数例Selecta.policyno,quotationno,

NVL(quotationno,‘直接出单'),a.*FromGupolicymaina其它函数-判断函数SelectPolicyno保单号,

Decode（Jfeeflag，’1’，‘见费出单’)标志FromPrpcmain查询结果：保单号标志301012009110000000001301012009120000000005见费出单

例DECODE（表达式,判断值,结果值

[,判断值,结果值,]……）单行函数SelectPolicyno,

Decode(riskcode,‘0101’,‘财产基本险')

FromGupolicyriskCASE表达式SelectPolicyno保单号,(CASEWHEN

Jfeeflag=’1’

THEN‘见费出单’)标志FromPrpcmain例CASEWHEN

条件表达式

THEN

表达式返回“True”时的结果[ELSE

表达式返回“False”时的结果]ENDSelectPolicyno,

(CASE

WHENRiskcode='0101'THEN'财产基本险‘

END)FromGupolicyriskEMPLOYEES4400…95003500640010033数据分组EMPDEPTNOSAL------------------10245010500010130020800201100203000203000202975301600员工表中工资的最高值MAX(SAL)---------5000SELECT语句分组函数也叫多行函数。分组函数对多行输入值进行计算，得到多行对应的单行结果。在通常的情况下，会对一个表总的数据进行分组，得到多组数据，每个组中包括一行或多行数据。分组函数对每组记录分别进行计算，每组返回一个结果。最终产生多个组对应的数据集合。分组有多少，最终的结果集就有多少行。EMPDEPTNOSAL------------------10245010500010130020800201100203000203000202975301600员工表中工资的最高值MAX(SAL)---------5000分组函数的概念分组函数常用的分组函数函数名称函数功能说明SUM（）返回一组数据的总和针对数值类型的数据AVG（）返回一组数据的平均值MAX（）返回一组数据的最大值适用于任何数据类型的数据MIN（）返回一组数据的最小值COUNT（）返回一组数据中的记录数分组函数适用于Select子句，Having子句分组函数求和函数—SUM()格式：SUM([DISTINCT]列名)说明：只适用于数值类型的数据DISTINCT为可选项如果指定列中含有NULL值，SUM函数会忽略该值SelectSUM（Sumpremium）

总保费FromPrpcmainWhereRiskcode=‘0101’例分组函数Select

SUM(Sumnetpremium)FromGupolicyriskWhereRiskcode='0101'均值函数—AVG()格式：AVG([DISTINCT]列名)说明：只适用于数值类型的数据DISTINCT为可选项如果指定列中含有NULL值，AVG函数会忽略该值Sel