讲稿限制和排序数据

限制和排序数据EpisodeII,PartII限制和排序数据EpisodeII结构目标预备知识

回顾内容

总结

作业目标限定显示查询后的行对查询后的行进行排序显示预备知识回顾基本SELECT语句格式会使用列别名能够查看表结构内容使用WHERE子句限定行使用比较运算符使用BETWEEN，IN，LIKE，ISNULL使用NOT，AND，OR使用ORDERBY子句对查询结果进行排序限定选择的行可以通过WHERE子句限定查询返回的行WHERE子句紧跟在FROM子句后面SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)];SELECTemployee_id,last_name,job_id,department_idFROMemployeesWHEREdepartment_id=90;字符串和日期字符值和日期值使用单引号定界(‘)字符值是大小写敏感的，日期值是格式敏感的默认的日期格式是DD-MON-RRSELECTlast_name,job_id,department_idFROMemployeesWHERElast_name='Whalen';SELECTlast_nameFROMemployeesWHEREhire_date=‘17-3月-96';比较运算符不等于<>介于两个值间BETWEEN

...AND...包含在集合中

IN(set)匹配给定的字符串模式LIKE值为NULLISNULL小于<小于等于<=大于等于>=大于>相等=MeaningOperator使用比较运算符SELECTlast_name,salaryFROMemployeesWHEREsalary<=3000;使用BETWEEN…AND可是限定范围SELECTlast_name,salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500;下界上界使用IN判断成员关系SELECTemployee_id,last_name,salary,manager_idFROMemployeesWHEREmanager_idIN(100,101,201);使用LIKE进行模式匹配可以使用LIKE运算符对字符串进行模式匹配两个特殊符号下划线(_)匹配任意一个字符百分号(%)匹配任意零到多个字符SELECT first_nameFROM employeesWHERE first_nameLIKE'S%';SELECTlast_nameFROMemployeesWHERElast_nameLIKE'_o%';SELECTlast_nameFROMemployeesWHERElast_nameLIKE‘_o\_%‘ESCAPE‘\’;判断NULL值SELECTlast_name,manager_idFROMemployeesWHEREmanager_idISNULL;使用逻辑运算符条件为假返回真NOT有一个条件为真就返回真OR当两个条件都为真时返回真AND意义运算符ANDSELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary>=10000ANDjob_idLIKE'%MAN%';ORSELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary>=10000ORjob_idLIKE'%MAN%';NOTSELECTlast_name,job_idFROMemployeesWHEREjob_idNOTIN('IT_PROG','ST_CLERK','SA_REP');优先级可以使用括号来提升优先级.

不等6

NOT7

AND8

OR9

IS

[NOT]

NULL,LIKE,[NOT]

IN4

[NOT]

BETWEEN5

比较运算符3

联接运算符2

算数运算符1

运算符级别优先级示例SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id='SA_REP'ORjob_id='AD_PRES'ANDsalary>15000;SELECTlast_name,job_id,salaryFROMemployeesWHERE(job_id='SA_REP'ORjob_id='AD_PRES')ANDsalary>15000;12使用ORDERBY子句使用ORDERBY子句可以用来排序查询结果ASC：升序显示，默认值DESC：降序显示ORDERBY子句写在SELECT语句的最后SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;…排序SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_dateDESC;1SELECTemployee_id,last_name,salary*12annsalFROMemployeesORDERBYannsal;2SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBY3;3SELECTlast_name,department_id,salaryFROMemployeesORDERBYdepartment_id,salaryDESC;4总结可以在WHERE子句中使用使用比较运算符使用BETWEEN…AND,IN,LIKE,ISNULL使用NOT，AND，OR能够使用ORDERBY子句进行排序SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];作业1.查询last_name是Chen的员工的信息。2.查询参加工作时间在1997-7-9之后，并且不从事IT_PROG工作的员工的信息。3.查询员工last_name的第三个字母是a的员工的信息。4.查询除了10、20、110号部门以外的员工的信息5.查询部门号为50号员工的信息，先按工资降序排序，再按姓名升序排序。6.查询没有上级管理的员工(经理号为空)的信息。7.查询员工表中工资大于等于4500并且部门为50或者60的员工的姓名(last_name),工资，部门号。函数EpisodeIII结构目标预备知识

回顾内容

总结

作业目标了解SQL函数的类型在SELECT中使用字符/数值/日期函数熟悉转换函数中可用的类型在SELECT中使用转换函数在SELECT中使用条件表达式预备知识回顾可以在WHERE子句中使用使用比较运算符使用BETWEEN…AND,IN,LIKE,ISNULL使用NOT，AND，OR能够使用ORDERBY子句进行排序SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];内容单行函数字符函数数值函数日期和日期函数SQL函数函数输入参数1参数2参数n函数完成某些动作输出结果值SQL函数的两种类型单行函数多行函数对于每一行返回一个结果对于一组行返回一个结果函数单行函数单行函数功能：操作数据接受参数返回一个值每一行返回一个值可以修改数据类型可以被嵌套使用参数可以是列或者表达式函数名[(参数1,参数2,...)]单行函数分类转换函数字符函数数值函数日期函数通用函数单行函数字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写转换函数字符操作函数大小写转换函数这些函数用来转换字符大小写sqlcourseLOWER('SQLCourse')SqlCourseINITCAP('SQLCourse')SQLCOURSEUPPER('SQLCourse')ResultFunction使用大小写转换函数SELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)='higgins';SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name='higgins';字符操作函数用来操作字符串BLACKandBLUEREPLACE

('JACKandJUE','J','BL')10LENGTH('HelloWorld')6INSTR('HelloWorld','W')*****24000LPAD(salary,10,'*')24000*****RPAD(salary,10,'*')HelloWorldCONCAT('Hello','World')elloWorldTRIM('H'FROM'HelloWorld')HelloSUBSTR('HelloWorld',1,5)结果函数字符操作函数的使用SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,'a')"Contains'a'?"FROMemployeesWHERESUBSTR(job_id,4)='REP';231213数值函数ROUND：四舍五入TRUNC：去尾MOD：求余数100MOD(1600,300)45.93ROUND(45.926,2)45.92TRUNC(45.926,2)ResultFunctionROUND函数SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;DUAL是一个虚表，当要显示的内容与表无关时，可以使用此表331212TRUNC函数SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1)FROMDUAL;331212MODSELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id='SA_REP';日期Oracle数据库在内部使用数字格式存储日期：世纪，年，月，日，小时，分，秒默认的日期格式为DD-MON-RRSELECTlast_name,hire_dateFROMemployeesWHEREhire_date<'01-FEB-88';RR格式CurrentYear1995199520012001

27-OCT-9527-OCT-1727-OCT-1727-OCT-95

1995201720171995

1995191720172095当前年份:0–490–4950–9950–99返回日期为当前世纪返回日期为下个世纪返回日期为上个世纪返回日期为当前世纪给定年份:YY格式年份RR格式年份给定日期当前年日期函数SYSDATE能够返回当前服务器的日期和时间SELECTsysdateFROMdual;日期上的计算在日期上加减一个数值会返回一个日期两个日期相减会得到两个日期间相差天数在一个日期上加小时需要除以24日期相关的运算SELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90;日期操作函数查看下一个周几是哪天NEXT_DAY查看月末几号LAST_DAY对日期四舍五入ROUND 对日期进行截断TRUNC两个日期之间相差月数MONTHS_BETWEEN在一个日期上加上多少个月ADD_MONTHS结果函数'08-SEP-95'NEXT_DAY('01-SEP-95','FRIDAY')'28-FEB-95'LAST_DAY('01-FEB-95')19.6774194MONTHS_BETWEEN('01-SEP-95','11-JAN-94')‘29-FEB-96'ADD_MONTHS(‘31-JAN-96',1)功能函数假设

SYSDATE

=

'25-JUL-03':01-JUL-03TRUNC(SYSDATE,'MONTH')01-JAN-03TRUNC(SYSDATE,'YEAR')01-AUG-03ROUND(SYSDATE,'MONTH')01-JAN-04ROUND(SYSDATE,'YEAR')结果函数赠品EXTRACT函数数据转换隐式转换显式转换数据类型转换隐式的数据类型转换在表达式中oracle会自动对数据类型进行转换，可以转换的是NUMBERVARCHAR2orCHARDATEVARCHAR2orCHAR到从VARCHAR2orCHARNUMBERVARCHAR2orCHARDATE到从显式的数据类型转换NUMBERCHARACTERTO_CHARTO_NUMBERDATETO_CHARTO_DATETO_CHAR格式化字符串必须使用单引号定界大小写敏感可以使用fm去掉前导空格或者前导零与日期使用逗号分隔TO_CHAR(date,'format_model')格式化字符串中格式元素两个字母简写的周几DY全写的周几DAY两位月份MM月份全名MONTH三个字母简写月份MON两位数字的日期DD四位年份YYYY用英语拼写的年份YEAR结果元素格式元素DD"of"MONTH12ofOCTOBERddspthfourteenthHH24:MI:SSAM15:45:32PMTO_CHARSELECTlast_name,TO_CHAR(hire_date,'fmDDMonthYYYY')ASHIREDATEFROMemployees;…TO_CHARTO_CHAR(number,'format_model')输出一个小数点.输出一个千分符,显示美元符$显示本地货币符号L表示一个数字9强制显示00结果元素TO_CHARSELECTTO_CHAR(salary,'$99,999.00')SALARYFROMemployeesWHERElast_name='Ernst';TO_NUMBER，TO_DATE转换字符到数值转换字符到日期可以包含fx前缀。TO_NUMBER(char[,'format_model'])TO_DATE(char[,'format_model'])SELECTlast_name,TO_CHAR(hire_date,'DD-Mon-YYYY')FROMemployeesWHEREhire_date<TO_DATE('01-Jan-90','DD-Mon-RR');函数的嵌套调用单行函数可以无限的嵌套嵌套函数计算是从里到外的F3(F2(F1(col,arg1),arg2),arg3)Step1=Result1Step2=Result2Step3=Result3SELECTlast_name,UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US'))FROMemployeesWHEREdepartment_id=60;通用函数Thefollowingfunctionsworkwithanydatatypeandpertaintousingnulls:NVL

(expr1,expr2)NVL2

(expr1,expr2,expr3)NULLIF

(expr1,expr2)COALESCE

(expr1,expr2,...,exprn)NVLFunctionConvertsanullvaluetoanactualvalue:Datatypesthatcanbeusedaredate,character,andnumber.Datatypesmustmatch:NVL(commission_pct,0)NVL(hire_date,'01-JAN-97')NVL(job_id,'NoJobYet')SELECTlast_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0))AN_SALFROMemployees;UsingtheNVLFunction…1122SELECTlast_name,salary,commission_pct,NVL2(commission_pct,'SAL+COMM','SAL')eFROMemployeesWHEREdepartment_idIN(50,80);UsingtheNVL2Function1221SELECTfirst_name,LENGTH(first_name)"expr1",last_name,LENGTH(last_name)"expr2",NULLIF(LENGTH(first_name),LENGTH(last_name))resultFROMemployees;UsingtheNULLIFFunction…123123UsingtheCOALESCEFunctionTheadvantageoftheCOALESCEfunctionovertheNVLfunctionisthattheCOALESCEfunctioncantakemultiplealternatevalues.Ifthefirstexpressionisnotnull,theCOALESCEfunctionreturnsthatexpression;otherwise,itdoesaCOALESCEoftheremainingexpressions.SELECTlast_name,employee_id,COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id), 'Nocommissionandnomanager')FROMemployees;UsingtheCOALESCEFunction……ConditionalExpressionsProvidetheuseoftheIF-THEN-ELSElogicwithinaSQLstatementUsetwomethods:CASEexpressionDECODEfunctionCASEExpressionFacilitatesconditionalinquiriesbydoingtheworkofan

IF-THEN-ELSEstatement:CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDSELECTlast_name,job_id,salary,CASEjob_idWHEN'IT_PROG'THEN1.10*salaryWHEN'ST_CLERK'THEN1.15*salaryWHEN'SA_REP'THEN1.20*salaryELSEsalaryEND"REVISED_SALARY"FROMemployees;UsingtheCASEExpressionFacilitatesconditionalinquiriesbydoingtheworkofan

IF-THEN-ELSEstatement:………DECODEFunctionFacilitatesconditionalinquiriesbydoingtheworkofaCASEexpressionoranIF-THEN-ELSEstatement:DECODE(col|expression,search1,result1

[,search2,result2,...,]

[,default])SELECTlast_name,job_id,salary,DECODE(job_id,'IT_PROG',1.10*salary,'ST_CLERK',1.15*salary,'SA_REP',1.20*salary,salary)REVISED_SALARYFROMemployees;UsingtheDECODEFunction………SELECTlast_name,salary,DECODE(TRUNC(salary/2000,0),0,0.00,1,0.09,2,0.20,