第九章-操作符及sql函数.ppt

单击此处编辑母版标题样式,第一级,第二级,第三级,第四级,第五级,Click to edit the title text format,2012,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,Inspur,Education,2012,Inspur Group,Click to edit the title text format,2012,Inspur Group,Inspur,Education,Click to edit the title text format,2012,Inspur Group,Inspur,Education,Click to edit the title text format,2012,Inspur Group,Inspur,Education,Click to edit the title text format,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,Click to edit the title text format,Inspur,Group,Inspur,Education,版权所有：,浪潮优派教育科技有限公司,版权所有：,浪潮优派教育科技有限公司,谢谢大家,!,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,Inspur,Education,版权所有：,浪潮优派教育科技有限公司,版权所有：,浪潮优派教育科技有限公司,谢谢大家,!,Click to edit the title text format,Inspur,Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Inspur,Education,Inspur Group,单击此处编辑母版标题样式,单击此处编辑母版文本样,单击此处编辑母版标题样式,编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,Inspur,Education,第,9,章 操作符及SQL函数,知识点回顾,表连接原理,多表连接查询,等值连接,非等值连接,交叉连接,内连接,外连接,特殊连接,子查询的概念及应用,单行子查询,多行子查询,2,本章目标,掌握 SQL 操作符,掌握并熟练应用SQL 函数,单行函数：日期、字符、数字、转换、其他,分组函数,分析函数,3,算术操作符,算术操作符用于执行数值计算,可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成,算术操作符包括加(+)、减(-)、乘(*)、除(/),SELECT f_stuid,f_grade+1 as new_grade,FROM t_grade WHERE f_courseid=01;,SELECT f_stuid,f_grade*1.1 as new_grade,FROM t_grade WHERE f_courseid=01;,4,SQL 操作符,SQL,操作符,集合操作符,逻辑操作符,比较操作符,算术操作符,连接操作符,Oracle,支持的,SQL,操作符分类如下：,5,比较操作符,比较操作符用于比较两个表达式的值,比较操作符包括,=,、,!=,、,、,=,、,BETWEEN,AND,、,IN,、,LIKE,和,IS NULL,等,SQL SELECT f_id,f_name,f_birth,FROM t_student,WHERE f_birth SELECT f_id FROM t_student,WHERE f_birth IN(01-8,月,-84,30-8,月,-85);,SQL SELECT f_id,f_name,f_sex,f_birth,FROM t_student,WHERE f_name LIKE _,富,%;,6,逻辑操作符,逻辑操作符用于组合多个比较运算的结果以生成一个或真或假的结果。,逻辑操作符包括与(AND)、或(OR)和非(NOT)。,SELECT*FROM t_student,WHERE f_birth=01-1,月,-80,AND f_birth=70 and f_courseid=01,INTERSECT,select f_stuid from t_grade where f_grade=70 and f_courseid=02;,统计,C,语言或,Java,为良好的同学学号,select f_stuid from t_grade where f_grade=70 and f_courseid=01,UNION,select f_stuid from t_grade where f_grade=70 and f_courseid=02;,统计,C,语言良好但,Java,未达到良好的同学学号,select f_stuid from t_grade where f_grade=70 and f_courseid=01,MINUS,select f_stuid from t_grade where f_grade=70 and f_courseid=02;,9,连接操作符,连接操作符用于将多个字符串或数据值合并成一个字符串,SELECT(,学号为,|f_id|,的同学姓名是,|f_name)as ddress,FROM t_student;,通过使用连接操作符可以将表中,的多个列合并成逻辑上的一行列,10,操作符的优先级,SQL 操作符的优先级从高到低的顺序是：,算术操作符 -最高优先级,连接操作符,比较操作符,NOT 逻辑操作符,AND 逻辑操作符,OR 逻辑操作符 -最低优先级,11,SQL 函数,Oracle 提供一系列用于执行特定操作的函数,SQL 函数带有一个或多个参数并返回一个值,以下是SQL函数的分类：,SQL,函数,单行函数,分析函数,分组函数,12,单行函数分类,单行函数对于从表中查询的每一行只返回一个值,可以出现在 SELECT 子句中和 WHERE 子句中,单行函数可以大致划分为：,日期函数,数字函数,字符函数,转换函数,其他函数,13,日期函数,日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果,日期函数包括：,ADD_MONTHS,MONTHS_BETWEEN,LAST_DAY,ROUND,NEXT_DAY,TRUNC,EXTRACT,14,字符函数 2-1,函数,输入,输出,Initcap(char),select initcap(hello)from dual;,Hello,Lower(char),select lower(FUN)from dual;,fun,Upper(char),select upper(sun)from dual;,SUN,Ltrim(char,set),select ltrim(xyzadams,xyz)from dual;,adams,Rtrim(char,set),select rtrim(xyzadams,ams)from dual;,xyzad,Translate(char,from,to),select translate(jack,j,b)from dual;,back,Replace(char,searchstring,rep string),select replace(jack and jue,j,bl)from dual;,black and blue,Instr(char,m),select instr(worldwide,d)from dual;,5,Substr(char,m,n),select substr(abcdefg,3,2)from dual;,cd,Concat(expr1,expr2),select concat(Hello,world)from dual;,Hello world,字符函数接受字符输入并返回字符或数值,15,字符函数 2-2,以下是一些其它的字符函数：,CHR和ASCII,LPAD和RPAD,TRIM,LENGTH,DECODE,SQL SELECT CHR(67)FROM dual;,SELECT LPAD(function,15,=)FROM dual;,SQL SELECT TRIM(9 from 9999876789999)FROM dual;,SELECT LENGTH(student)FROM dual;,SELECT f_id,f_name,DECODE(f_calss,1,class one,2,class two,other class)class,FROM t_student;,16,数字函数,函数,输入,输出,Abs(n),Select abs(-15)from dual;,15,Ceil(n),Select ceil(44.778)from dual;,45,Cos(n),Select cos(180)from dual;,-.5984601,Floor(n),Select floor(100.2)from dual;,100,Power(m,n),Select power(4,2)from dual;,16,Mod(m,n),Select mod(10,3)from dual;,1,Round(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,转换函数,转换函数将值从一种数据类型转换为另一种数据类型,常用的转换函数有：,TO_CHAR,TO_DATE,TO_NUMBER,SELECT TO_CHAR(sysdate,YYYY,年,fmMM,月,fmDD,日,HH24:MI:SS),FROM dual;,Select TO_CHAR(0.123,0.999)FROM DUAL;,SELECT TO_DATE(2005-12-06,yyyy-mm-dd),FROM dual;,SELECT TO_NUMBER(100)FROM dual;,18,其它函数,以下是几个用来转换空值的函数,：,NVL,NVL2,NULLIF,SELECT f_id,f_name,NVL(avg(f_grade),0),FROM t_student left join t_grade on f_id=f_stuid,GROUP BY f_id,f_name;,SELECT f_id,f_name,NVL2(f_department,existed dep,other dep as dep),from t_student;,SELECT NULLIF(100,200)FROM DUAL;,19,分析函数 2-1,分析函数根据一组行来计算聚合值,用于计算完成聚集的累计排名、移动平均数等,分析函数为每组记录返回多个行,分析函数,DENSE_RANK,RANK,ROW_NUMBER,20,分析函数 2-2,以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始,ROW_NUMBER 返回连续的排位，不论值是否相等,RANK 具有相等值的行排位相同，序数随后跳跃,DENSE_RANK 具有相等值的行排位相同，序号是连续的,select f_stuid,f_courseid,row_number()over,(partition by f_courseid order by f_grade desc)as rank,from t_grade,21,总结,SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符,SQL 函数可大致分为：,单行函数：日期、字符、数字、转换、其他,聚合函数,分析函数,22,谢谢！,23,