test_lesson03单行函数 - 360文档中心

【IT专家】MySQL高级查询函数(单行函数)

本文由我司收集整编，推荐下载，如有疑问，请与我司联系MySQL高级查询函数(单行函数)2017/03/20 737 函数的分类：1，单行函数：对每一条记录输入值进行计算，得到相应的计算结果，返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

2，多行函数：对多条记录输入值进行计算，得到多条记录对应的单个结果。

单行函数: ①:字符串函数(用户处理单行的字符数据，比如大小写转换，字符串截取，拼装等) a.LOWER/UPPER(LOWER(str)：返回字符串str变为小写字母的字符串.UPPER(str)：返回字符串str变为大写字母的字符串) SELECT UPPER(name) FROM student; //全部大写SELECT LOWER(name) FROM student; //全部小写b.CONCAT: CONCAT(str1,str2,...)： 1，返回结果为连接参数产生的字符串。

2，如有任何一个参数为NULL ，则返回值为NULL 3，允许有一个或多个参数 SELECT name,age, CONCAT(name,’-’,age) FROM student;运行的结果为: c.INSERT：把指定（位置，长度）的子字符串替换成目标字符串格式：INSERT(str,pos,len,newstr) 参数：str：(源字符串) pos：(开始插入的位置，索引从1开始) len：(替换字符串的长度) newstr：(待插入的字符串) 1，返回字符串str, 其子字符串起始于pos 位置和长度被字符串newstr取代的len 字符。

2，如果pos 超过字符串长度，则返回值为原始字符串。

3，假如len的长度大于其它字符串的长度，则从位置pos开始替换。

4，若任何一个参数为null，则返回值为NULL示例：替换用户名名的部分字符，规则如下：保留用户名前2位，中间3位使用*代替，如果姓名还有多余的字符，保留 SELECT INSERT(name,2,3,’***’) FROM student;运行的结果为: d.①LENGTH：字符串所占用的字节数。

06.单行函数

0：舍入到没有小数位 -1：表示应该舍入到小数点左侧的位数
26
数字函数 TRUNC函数 2-1
TRUNC(n,p)截断数字 n：要截断的数字或字段 p：表示应该对数据进行删除或截断的数字位置 p是正数，表示小数点右侧位置负数，表示小数点左侧位置
SELECT title,retail, TRUNC(retail,1) FROM books;
12
字符处理函数 SUBSTR函数 2-1
返回字符串的一部分，子串
示例：查看邮政编码的前三位数字
SELECT DISTINCT SUBSTR(zip,1,3) FROM customers;
SUBSTR(c,p,l) c:要提取的字符串 p:要提取的起始字符位置 l:查询中要返回的字符串长度
13
31
DATE函数 ADD_MONTHS函数 2-2
32
DATE函数 NEXT_DAY函数
可以确定在指定日期之后一周的下一个特定日期
NEXT_DAY(d,DAY) d：开始日期 DAY：将要确定的一周中的某一天
SELECT order#,orderdate, NEXT_DAY(orderdate, '星期一') FROM orders WHERE order#=1018;
多行函数，对所处理的一组行或一类行只返回一个结果
4
单行函数
函数类型大小写转换函数字符处理函数数字函数日期函数
其他
函数 LOWER，UPPER，INITCAP SUBSTR ， LENGTH ， LPAD/RPAD ， LTRIM/RTRIM，REPLACE，CONCAT ROUND，TRUNC MONTHS_BETWEEN ， ADD_MONTHS ， NEXT_DAY，TO_DATE NVL，NVL2，TO_CHAR等

Les03-处理单行的函数省名师优质课赛课获奖课件市赛课一等奖课件

三.处理单行旳函数
目的
完毕本节课后，应该到达如下旳目旳：
– 描述在 SQL语句中可能用到旳多种函数。 – 在SELECT 语句中使用字符串、数字、日
期旳函数。 – 描述转换函数旳使用方法
SQL函数
输入
参数 1 参数2
函数函数处理动作
参数n
输出成果值
两种类型旳 SQL函数
函数
单行函数
多行函数
转换函数
数据类型转换
隐式数据类型转换显式数据类型转换
隐式数据类型转换
Oracle服务器能够自动进行下列类型旳转换:
From
To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER
VARCHAR2
DATE
VARCHAR2
隐式数据类型转换
对于体现式旳计算, Oracle服务器能够自动进行下列转换:
成果 GoodString Str 6 3 ******5000 MITH
使用字符操作函数
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),
2
INSTR(ename, 'A')
3 FROM emp
4 WHERE SUBSTR(job,1,5) = 'SALES';
在 TO_CHAR函数中使用这些格式，以按照某种字符格式展示一种数字值
Hale Waihona Puke 9 代表一种数字值0 强制显示一种0
$ 在一种浮点值前面显示一种$符号
L 使用浮点类型旳本地货币符号
.
显示一种小数点

MySQL高级查询函数（单行函数）

MySQL⾼级查询函数（单⾏函数）函数的分类：1，单⾏函数：对每⼀条记录输⼊值进⾏计算，得到相应的计算结果，返回给⽤户，也就是说，每条记录作为⼀个输⼊参数，经过函数计算得到每条记录的计算结果。

2，多⾏函数：对多条记录输⼊值进⾏计算，得到多条记录对应的单个结果。

单⾏函数:①:字符串函数(⽤户处理单⾏的字符数据，⽐如⼤⼩写转换，字符串截取，拼装等)a.LOWER/UPPER(LOWER(str)：返回字符串str变为⼩写字母的字符串.UPPER(str)：返回字符串str变为⼤写字母的字符串) SELECT UPPER(name) FROM student; //全部⼤写SELECT LOWER(name) FROM student; //全部⼩写b.CONCAT: CONCAT(str1,str2,...)： 1，返回结果为连接参数产⽣的字符串。

2，如有任何⼀个参数为NULL ，则返回值为 NULL 3，允许有⼀个或多个参数SELECT name,age, CONCAT(name,'-',age) FROM student;运⾏的结果为:c.INSERT：把指定（位置，长度）的⼦字符串替换成⽬标字符串格式：INSERT(str,pos,len,newstr) 参数：str：(源字符串) pos：(开始插⼊的位置，索引从1开始) len：(替换字符串的长度) newstr：(待插⼊的字符串) 1，返回字符串str, 其⼦字符串起始于pos 位置和长度被字符串 newstr取代的len 字符。

2，如果pos 超过字符串长度，则返回值为原始字符串。

3，假如len的长度⼤于其它字符串的长度，则从位置pos开始替换。

4，若任何⼀个参数为null，则返回值为NULL⽰例：替换⽤户名名的部分字符，规则如下：保留⽤户名前2位，中间3位使⽤*代替，如果姓名还有多余的字符，保留SELECT INSERT(name,2,3,'***') FROM student;运⾏的结果为:d.①LENGTH：字符串所占⽤的字节数SELECT LENGTH(name) FROM student;②CHAR_LENGTH:计算字符数SELECT CHAR_LENGTH(name) FROM student;e:LPAD/RPAD:字符串的字符数如果⼤于给定的个数,如果少了就从函数指定的边补齐指定个数,如果多了从字符串的最后截掉 LPAD(str,len,padstr)：左填补 1，返回字符串str, 其左边由字符串padstr 填补到总长度为len。

Chapter3 单行函数

OCP-007-Chapter3 单行函数目的完成这一章，可以作下列事情：描述SQL中可用的不同函数类型在select语句中使用字符、数字和日期函数描述转换函数的应用函数是SQL非常有用的特性，可以实现下列功能：执行数据的计算编辑单独的数据项操作多行的输出（Manipulate output for groups of rows）格式化日期和数字的显示转换数据类型SQL函数有时候需要参数，但是总会返回一个值；过程主要是完成一个事件单行函数：操作数据项；接受参数并返回一个值；作用于每一个返回的行；每行返回一个结果；可能会改变数据类型；可以嵌套；可以使用用户定义的常量、变量值、列名或者表达式作为参数单行函数分为字符函数、数字函数、日期函数、转换函数和一般函数，可以在select, wher e, order by子句中使用一般函数：NVL，NVL2，NULLIF，COALSECE，CASE，DECODE字符函数接受字符数据，返回字符或者数字。

字符函数分为大小写转换函数（Lower, Upp er, Initcap首字目大写)和字符串操作函数（concat, substr, length, instr, lpad/rpad, trim, replace）。

SUBSTR(column|expression,m[,n])->返回从m开始的n个字符；若m为负值，则从末尾开始数mINSTR(column|expression,'string', [,m], [n] )->从m位置开始寻找第n个string字符（串）（缺省m,n为1）LPAD/RPAD(column|expression, n,'string')->扩展到n个字符宽，不足部分使用string字符添加TRIM(leading|trailing|both, trim_character FROM trim_source)->删除前导字符串，如果后面的trim_character和trim_source都是literal字符串，一定要使用单引号包围起来REPLACE(text,search_string,replacement_string)->替换CONCAT('Hello', 'World') -> HelloWorldSUBSTR('HelloWorld',1,5) -> HelloLENGTH('HelloWorld') -> 10INSTR('HelloWorld', 'W') ->6LPAD(salary,10,'*') -> *****24000RPAD(salary, 10, '*') -> 24000*****TRIM('H' FROM 'HelloWorld') -> elloWorld数字函数：Round圆整，Trunc截断，Mod取模->其中Round, Trunc也可以用于日期函数ROUND(column|expression, n)->四舍五入到n为小数，如果n为负值，则从小数点向左边数round(345.23,-3)=0; round(345.23,-2)=300; round(345.23,-1)=350; round(345.23)=345 TRUNC(column|expression,n)->截断到n为小数，如果n为负值，则从小数点向左边数trunc(345.23,-3)＝0; trunc(345.23,-2)=300; trunc(345.23,-1)=340; trunc(345.23)=345测试函数或者计算结果时可以使用dual这个虚假的表SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;Oracle数据库内部以下列方式存储日期：世纪，年，月，日，小时，分，秒，缺省的日期格式为DD-MON-RR当插入日期函数时，世纪值自动从sysdate函数（返回当前数据库服务器的日期和时间）取得允许的日期和数字的操作：date + number, date - number, date - date, date + number/24日期函数：MONTHS_BETWEEN(date1, date2)->两个日期间的月份，注意月份为中文的情况ADD_MONTHS(date, n)->添加月份NEXT_DAY(date, 'char')->找到下一个星期几（可以为数字或者字符串）LAST_DAY(date)->发现包含date所在月份的最后一天ROUND(date[,'fmt'])->圆整到格式中所指定的单位，省略的话圆整到最近的天TRUNC(date[, 'fmt'])->截断到格式中所指定的单位，省略的话截断到最近的天假定SYSDATE = '25-JUL-95':• ROUND(SYSDATE,'MONTH') -> 01-AUG-95• ROUND(SYSDATE ,'YEAR') -> 01-JAN-96• TRUNC(SYSDATE ,'MONTH') -> 01-JUL-95• TRUNC(SYSDATE ,'YEAR') -> 01-JAN-95转换函数隐式转换和显式转换（data type to data type）尽管隐式转换可以使用，推荐使用显式转换以使得SQL语句更可靠隐式转换：VARCHAR OR VARCHAR2 -> NUMBER; VARCHAR OR VARCHAR2 -> DATENUMBER -> VARCHAR2; DATE -> VARCHAR2显式转换：TO_CHAR(number|date,[ fmt],[nlsparams])TO_NUMBER(char,[fmt],[nlsparams])TO_DATE(char,[fmt],[nlsparams])The nlsparams parameter specifies the following characters, which are returned by number format elements:·Decimal character·Group separator·Local currency symbol·International currency symbolTO_CHAR(date, 'format_model')->格式模式是大小写敏感的，必须使用单引号包围，一定要使用逗号把格式和日期值间隔开来，使用fm可以把前面的空格或者零压缩掉日期格式模式：CC （世纪），YYYY,YYY,YY,Y(四个数字表示的年份；分别表示使用4位、后3位、后两位和后一位来表示年份），YEAR（拼写的年份），Q（季节），MM（两个数字表示的月份），MONTH（月份全名，九个字符长），MON（月份的三字缩写），RM（罗马数字表示的月份），WW（一年的第几个星期），W（月份的第几个星期），DY（星期的三字缩写），DAY（星期全称，九个字符长），DDD（一年的第几天），DD（一个月的第几天），D（一星期的第几天）BC，B.C.，AM，A.M.，HH，HH12，HH24，MI，SS，SSSSS（午夜过后的秒数）HH24:MI:SS AM -> 15:45:32 PM; DD "of" MONTH -> 12 of OCTOBER;/.,"of the" -->标点和引用的字符会在日期中原样输出DDspth -> fourteenth；DDth -> 4th；DDsp -> fourSELECT last_name,TO_CHAR(hire_date, 'fmDD Month YYYY') HIREDATEFROM employees;TO_CHAR(number, 'format_model')9:代表数字0:强制显式0 $:显示$符号L:显示本地货币符号 .:打印小数点,:显示千位的分隔符当值大于给定格式的位数时，Orale数据库会把整个数字显式为＃TO_DATE(char[, 'format_model']) ->有个fx限制符，使用后必须精确的匹配，包括前后的空格个数RR日期格式：________|_____给定年份_________________当前年份|____0～49___|____50～99_______0～49___|___Current__|____Before_______50～99__|____Next____|____Current______单行函数可以嵌套任意层，从最内层开始计算SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'FRIDAY'),'fmDay, Month DDth, YYYY')"Next 6 Month Review"FROM employeesORDER BY hire_date;一般函数可以使用任何数据类型，并且可以使用NULLNVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，原样返回。

单行函数3解读

3
… 1 2 3
使用 COALESCE 函数
• COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 • 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
使用 COALESCE 函数
SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;
使用NVL函数
1 SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; 2
…
1 2
exp1 != null ? exp2 : exp3
• 使用 TO_NUMBER :
TO_NUMBER(‘￥1,234,567,890.00’,’L999,999,999,999.99’) from dual
通用函数
这些函数适用于任何数据类型，同时也适用于空值： • NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2, ..., exprn)
…
条件表达式
• 在 SQL 语句中使用IF-THEN-ELSE 逻辑 • 使用两种方法:
– CASE 表达式 – DECODE 函数
CASE 表达式
在需要使用 IF-THEN-ELSE 逻辑时:

学习python第三天单行函数

学习python第三天单⾏函数1.去重：distinct关键字需求：查看公司⼀共有多少部门？select department_id from employees; //此代码会查出107条记录，存在部门重复的问题！select distinct department_id from employees;思考：参看如下代码有没有问题？select employee_id,last_name,distinct department_id from employees;解答：会出现问题(报错了)，出错原因，因为在执⾏代码的过程中对于employee_id，last_name这两列都有107条数据，⽽department_id去重之后只有12条数据，数量对不上了！所以报错了！总结：使⽤distinct需谨慎...2.单⾏函数：概念：单数据进单结果出(单进单出)分类学习：1).字符函数2).数字函数3).⽇期函数4).转换函数5).通⽤函数字符函数：1).⼤⼩写转换函数：3个①.lower(m)：将字符串中所有字符转换为全⼩写②.upper(m)：将字符串中所有字符转换为全⼤写③.initcap(m)：将字符串中⾸字母转为⼤写，其余字母转换⼩写代码实现：select lower('ORACLE'),upper('oracle'),initcap('oRACLE') from dual;需求：查询名字为Bell这个⼈的信息？select*from employees where lower(last_name) ='bell';select*from employees where upper(last_name) ='BELL';select*from employees where initcap(last_name) ='Bell';2).字符控制函数：8个①.concat(m,n)：将字符串m和字符串n拼接得到⼀个更长的新字符串代码实现：select concat('Hello','World') from dual;select concat(last_name,first_name) from employees;②.length(m)：得到某个数据的长度代码实现：select length('python'),length(123456) from dual;select length(employee_id),length(first_name),length(hire_date) from employees;③.substr(x,y,z)：将x从y位置开始截取z个长度得到⼀个新的字符串返回给程序参数解释：x：原本的字符串数据y：起点位置(下标，索引)z：截取的长度代码实现：select substr('HelloWorld',1,5) from dual;④.instr(m,n)：参数解释：判断某个字符在字符串中⾸次出现的位置m：字符串n：⼀个字符代码实现：select instr('HelloWorld','l') from dual;⑤.replace(x,y,z)：将x中的字符y，全部替换为字符z参数解释：x：字符串y：字符串中某个字符z：需要被替换成的字符代码实现：select replace('aaabcdaacdaabefaanba','a','6') from dual;⑥.trim(x from y)：去除y字符串中⾸尾的字符x参数解释：x：字符y：字符串代码实现：select trim('m'from'mmmmHellmmmomWmmorldmmmmm') from dual;⑦和⑧.lpad和rpad函数(了解)lpad(x,y,z)：rpad(x,y,z)：代码实现：select lpad(salary,10,'*'),rpad(salary,10,'*') from employees;2.数字函数：3个①.round(m,n)：四舍五⼊②.trunc(m,n)：截断③.mod(m,n)：求余代码实现：select round(439.456,1),round(439.456,0),round(439.456,-1) from dual; select trunc(439.456,1),trunc(439.456,0),trunc(439.456,-1) from dual; select mod(1100,300) from dual;3.⽇期函数：3个1).⽇期2).时间①.months_between(m,n)：⽤于计算两个⽇期之间相差的⽉数(精确)②.add_months(m,n)：在原本的m⽉份基础上增加或者删除n个⽉③.last_day(m)：得到某个⽇期所在⽉份的最后⼀天关键字：sysdate表⽰当前系统时间测试如下代码：select sysdate from dual;以上代码由于数据库内部的格式限制只能显⽰出⽇期部分数据⽽不能显⽰出时间，我们可以使⽤转换函数to_char(x,y)来实现⽇期和时间的显⽰补充：oracle中的特殊字母有以下这些yyyy：年mm：⽉dd：天day：星期hh：⼩时(1-12)hh24：⼩时(0-23)mi：分钟ss：秒重构上述代码实现⽇期时间数据的显⽰：select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;参看如下代码：select sysdate +1,sysdate,sysdate -2from dual;select (sysdate - hire_date) from employees;总结：对于⽇期数据可以和数字做加减运算，得到的结果就是往前或者往后的天数对于⽇期和⽇期数据之间只能做减法运算，得到的结果就是两个⽇期数据之间相差的天数案例：1).查询公司员⼯⼊职⾄今⼀共多少天了？(使⽤截断保留到整数位，别名worked_day) select last_name,hire_date,trunc(sysdate - hire_date) "worked_day" from employees;2).查询公司员⼯⼊职⾄今⼀共多少个⽉了？(粗糙版本：假设每⽉都是30天)select last_name,hire_date,(sysdate - hire_date) /30 "month_work",months_between(sysdate,hire_date) "mon_work" from employees;3).查询公司在每个⽉倒数第⼆天⼊职的员⼯有哪些？select last_name,hire_date from employeeswhere last_day(hire_date) -1= hire_date;select last_day(sysdate) from dual;select add_months(sysdate,2),add_months(sysdate,-3) from dual;4.转换函数：3个1).to_date2).to_char3).to_number补充：转换函数中涉及到的格式字符：9-->表⽰1位99-->表⽰2位举例：select to_number('123456','999999') from dual;//早版本，需要定义格式(位数)select to_number('123456') from dual;//新版本，可以省略格式【注意事项】：在使⽤to_number函数进⾏数据转换时，我们⼀定要计算正确需要被转换的数据的长度，才能精确定义格式中需要的位数，⼀旦位数过少，直接报错！！需求：查询公司员⼯的部门编号，如果没有部门的显⽰"没有部门"select last_name,department_id,nvl(to_char(department_id,'999'),'没有部门') from employees; select last_name,department_id,nvl(to_char(department_id),'没有部门') from employees;5.通⽤函数1).nvl(expr1,expr2)：2).nvl2(expr1,expr2,expr3)：nvl2函数的执⾏流程：在执⾏过程中会先执⾏expr1，如果expr1的结果不为null，那么执⾏expr2；如果expr1的结果为null，那么执⾏expr3；需求：查询员⼯编号，姓名，薪资，奖⾦率；如果奖⾦率不为空，那么显⽰奖⾦率 + 0.015以后的结果，如果奖⾦率为空，那么显⽰0.01；代码实现：select employee_id,last_name,salary,commission_pct,nvl2(commission_pct,commission_pct + 0.015,0.01) "new_comm"from employees;sql中的判断结构的引⼊讲解：两种：1).case表达式模板格式：case 字段 when expr1 then xwhen expr2 then ywhen expr3 then z...else n end;需求：查询公司员⼯的编号、姓名、薪资，部门号，如果是70号部门的员⼯，就显⽰⼯资的1.1倍，如果是80号部门的员⼯，就显⽰⼯资的1.2倍，如果是90号部门的员⼯，就显⽰⼯资的1.3倍，其余部门已正常⼯资显⽰；代码如下：select employee_id,last_name,salary,department_id, case department_id when70then salary *1.1 when80then salary *1.2else salary *1.3end-- when 90 then salary * 1.3 end-- else salary endfrom employeeswhere department_id in(70,80,90);2).decode函数模板格式：decode(字段,expr1,val1,expr2,val2,...)：需求：查询公司员⼯的编号、姓名、薪资，部门号，如果是70号部门的员⼯，就显⽰⼯资的1.1倍，如果是80号部门的员⼯，就显⽰⼯资的1.2倍，如果是90号部门的员⼯，就显⽰⼯资的1.3倍，其余部门已正常⼯资显⽰；代码如下：select employee_id,last_name,salary,department_id, decode(department_id,70,salary *1.1,80,salary *1.2,-- 90,salary * 1.3,salary *1.3)from employeeswhere department_id in(70,80,90);。

单行函数——精选推荐

单⾏函数所谓的单⾏函数指的就是完成某⼀具体功能的操作函数，例如：转⼤⼩写等。

⼀般⽽⾔，单⾏函数的格式：返回值函数名称(参数)。

单⾏函数分为以下⼏种：字符串函数、数值函数、⽇期函数、转换函数、通⽤函数。

1.字符串函数字符串函数是处理字符串数据的(对于字符串的数据有可能是从列上找到的，或者是直接设置的字符串常量)。

为了⽅便⽤户进⾏⼀些验证或者是⼀些不需要查询表的查询操作，专门提供了⼀个dual的虚拟表。

1.转⼤写⼩写操作　1)转⼤写SELECT UPPER('hello') FROM dual;　2).将所有姓名转⼩写SELECT LOWER(ename) FROM emp;⼀般在⼀些不区分数据⼤⼩写的情况都会同意的将所有的内容转成⼤写或⼩写的形式处理。

2.⾸字母⼤写其他字母⼩写　1)将所有的雇员姓名以⾸字母⼤写的形式保存SELECT INITCAP(ename) FROM emp;3.取得字符串的长度　1）基础操作SELECT LENGTH('ddffgg') FROM dual;　2）查询雇员姓名长度为5的全部雇员信息SELECT * FROM emp WHERE LENGTH(ename)=5;4.字符串截取　1）验证函数SQL> SELECT SUBSTR('helloworld',6) FROM dual;SUBSTR('HE----------worldSQL> SELECT SUBSTR('helloworld',0,5) FROM dual;SUBSTR('HE----------hello 在程序之中所有的字符串的⾸字母的索引都是0，但是在Oracle⾥⾯，所有的字符串的⾸字母索引都是1，如果设置的是0 ，那么它也会按照1的⽅式进⾏处理。

Les03_单行函数

3-21
Copyright © 2004, Oracle. All rights reserved.
使用日期函数
假设 SYSDATE = '25-JUL-03':
函数 ROUND(SYSDATE,'MONTH') ROUND(SYSDATE ,'YEAR') TRUNC(SYSDATE ,'MONTH') TRUNC(SYSDATE ,'YEAR') 结果 01-AUG-03 01-JAN-04 01-JUL-03 01-JAN-03
SELECT last_name, hire_date FROM employees WHERE hire_date < '01-FEB-88';
3-16
Copyright © 2004, Oracle. All rights reserved.
处理日期
函数SYSDATE 返回:
• • 日期时间
3-8
Copyright © 2004, Oracle. All rights reserved.
使用大小写处理函数
显示员工Higgins的信息:
SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';
1
2

所有的单行函数

要求显示所有雇员的姓名以及姓名的后三个字符
方法1：select ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;：
方法2：select ename ,SUBSTR(ename,-3,3); //从倒数第三个开始取三个
数值函数：对数字进行操作
日期 + 数字 = 日期
日期 - 日期 = 数字（天数） //这里和java中不同，Java是得到毫秒数
显示10部门雇员进入公司的星期数
1。先获得当前时间：select sysdate from dual
2.求星期数：（当前日期 - 雇佣日期）/7 = 星期数
找出各月倒数第三天受雇的所有员工：
select empno from emp where LAST_DAY(hiredate)-2 = hiredate;
LAST_DAY(hiredate)意思是各月的倒数第一天，如果LAST_DAY(hiredate)-1显然是倒数第二天，那么LAST_DAY(hiredate)-2显然是倒数第三天
显示所有员工的前三个字符：
select SUBSTR(ename,0,3) from emp ;
----------------------------------------------------------------------
2.日期函数：
规律：
日期 - 数字 = 日期
select empno,ename,(SYSDATE - hiredate)/7 from emp; //不过这里求出来的日期是有小数点的哈
改进： select empno,ename,ROUND((SYSDATE - hiredate)/7) from emp;

cn03-使用单行函数

使用日期函数
假定 SYSDATE = '25-JUL-03':
函数 ROUND(SYSDATE,'MONTH') ROUND(SYSDATE ,'YEAR') TRUNC(SYSDATE ,'MONTH') TRUNC(SYSDATE ,'YEAR') 结果 01-AUG-03 01-JAN-04 01-JUL-03 01-JAN-03
TO_NUMBER(char[, 'format_model'])
函数把字符串转换成日期: 用TO_DATE函数把字符串转换成日期函数把字符串转换成日期
TO_DATE(char[, 'format_model'])
通用函数
以下函数可以用于任何数据类型: 以下函数可以用于任何数据类型 NVL (expr1, expr2) NVL2 (expr1, expr2, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ..., exprn)
… …
DECODE 函数
显示每位员工的个人所得税应缴比例: 显示每位员工的个人所得税应缴比例
SELECT last_name, salary, DECODE (TRUNC(salary/2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45) TAX_RATE FROM employees WHERE department_id = 80;
练习 3: 部分 2
本练习包含以下内容: 本练习包含以下内容写一个查询语句,使用日期,字符和数值函数写一个查询语句,使用日期, 使用多个函数,函数嵌套使用多个函数, 用字符函数写一个查询语句,使得运行结果和职员名字用字符函数写一个查询语句, 的大小写无关查询职员的入职年限,显示几年几个月查询职员的入职年限,

2003常用函数完全手册(完善版)

OfficeStudy应用技术论坛 Excel 2003函数应用完全手册二〇〇四年二月二日目录一、函数应用基础 (1)(一)函数和公式 (1)1.什么是函数 (1)2.什么是公式 (1)(二)函数的参数 (1)1.常量 (1)2.逻辑值 (1)3.数组 (1)4.错误值 (1)5.单元格引用 (1)6.嵌套函数 (2)7.名称和标志 (2)(三)函数输入方法 (2)1.“插入函数”对话框 (2)2.编辑栏输入 (3)二、函数速查一览 (3)(一)数据库函数 (3)1.DA VERAGE (3)2.DCOUNT (3)3.DCOUNTA (3)4.DGET (3)5.DMAX (3)6.DMIN (3)7.DPRODUCT (3)8.DSTDEV (3)9.DSTDEVP (4)10.DSUM (4)11.DV AR (4)12.DV ARP (4)13.GETPIVOTDATA (4)(二)日期与时间函数 (4)1.DATE (4)2.DATEV ALUE (4)3.DAY (4)4.DAYS360 (5)9.MONTH (5)WORKDAYS (5)11.NOW (6)12.SECOND (6)13.TIME (6)14.TIMEV ALUE (6)15.TODAY (6)16.WEEKDAY (6)17.WEEKNUM (6)18.WORKDAY (6)19.YEAR (7)20.YEARFRAC (7)(三)外部函数 (7)1.EUROCONVERT (7)2.SQL.REQUEST (7)(四)工程函数 (7)1.BESSELI (7)2.BESSELJ (8)3.BESSELK (8)4.BESSELY (8)5.BIN2DEC (8)6.BIN2HEX (8)7.BIN2OCT (8)PLEX (8)9.CONVERT (8)10.DEC2BIN (8)11.DEC2HEX (8)12.DEC2OCT (8)13.DELTA (8)14.ERF (8)15.ERFC (9)16.GESTEP (9)17.HEX2BIN (9)18.HEX2DEC (9)19.HEX2OCT (9)20.IMABS (9)21.IMAGINARY (9)22.IMARGUMENT (9)23.MCONJUGA TE (9)24.IMCOS (9)25.IMDIV (9)26.IMEXP (9)27.IMLN (9)28.IMLOG10 (10)34.IMSQRT (10)35.IMSUB (10)36.IMSUM (10)37.OCT2BIN (10)38.OCT2DEC (10)39.OCT2HEX (10)(五)财务函数 (10)1.ACCRINT (10)2.ACCRINTM (11)3.AMORDEGRC (11)4.AMORLINC (11)5.COUPDAYBS (11)6.COUPDAYS (11)7.COUPDAYSNC (11)8.COUPNUM (11)9.COUPPCD (11)10.CUMIPMT (11)11.CUMPRINC (12)12.DB (12)13.DDB (12)14.DISC (12)15.DOLLARDE (12)16.DOLLARFR (12)17.DURA TION (12)18.EFFECT (12)19.FV (12)20.FVSCHEDULE (12)21.INTRA TE (13)22.IPMT (13)23.IRR (13)24.ISPMT (13)25.MDURATION (13)26.MIRR (13)27.NOMINAL (13)28.NPER (13)29.NPV (13)30.ODDFPRICE (13)31.ODDFYIELD (14)32.ODDLPRICE (14)33.ODDL YIELD (14)34.PMT (14)35.PPMT (14)41.RECEIVED (15)42.SLN (15)43.SYD (15)44.TBILLEQ (15)45.TBILLPRICE (15)46.TBILL YIELD (15)47.VDB (15)48.XIRR (16)49.XNPV (16)50.YIELD (16)51.YIELDDISC (16)52.YIELDMAT (16)(六)信息函数 (16)1.CELL (16)2.ERROR.TYPE (16) (16)4.IS 类函数 (17)5.ISEVEN (17)6.ISODD (17)7.N (17)8.NA (17)9.TYPE (18)(七)逻辑运算符 (18)1.AND (18)2.FALSE (18)3.IF (18)4.NOT (18)5.OR (18)6.TRUE (18)(八)查找和引用函数 (19)1.ADDRESS (19)2.AREAS (19)3.CHOOSE (19)4.COLUMN (19)5.COLUMNS (19)6.HLOOKUP (19)7.HYPERLINK (19)8.INDEX (20)9.INDIRECT (20)10.LOOKUP (20)16.TRANSPOSE (21)17.VLOOKUP (21)(九)数学和三角函数 (21)1.ABS (21)2.ACOS (21)3.ACOSH (22)4.ASIN (22)5.ASINH (22)6.ATAN (22)7.ATAN2 (22)8.ATANH (22)9.CEILING (22)BIN (22)11.COS (22)12.COSH (23)13.COUNTIF (23)14.DEGREES (23)15.EVEN (23)16.EXP (23)17.FACT (23)18.FACTDOUBLE (23)19.FLOOR (23)20.GCD (23)21.INT (23)22.LCM (24)23.LN (24)24.LOG (24)25.LOG10 (24)26.MDETERM (24)27.MINVERSE (24)28.MMULT (24)29.MOD (24)30.MROUND (24)31.MULTINOMIAL (25)32.ODD (25)33.PI (25)34.POWER (25)35.PRODUCT (25)36.QUOTIENT (25)37.RADIANS (25)38.RAND (25)39.RANDBETWEEN (25)45.SIGN (26)46.SIN (26)47.SINH (26)48.SQRT (26)49.SQRTPI (27)50.SUBTOTAL (27)51.SUM (27)52.SUMIF (27)53.SUMPRODUCT (27)54.SUMSQ (27)55.SUMX2MY2 (27)56.SUMX2PY2 (27)57.SUMXMY2 (28)58.TAN (28)59.TANH (28)60.TRUNC (28)(十)统计函数 (28)1.A VEDEV (28)2.A VERAGE (28)3.A VERAGEA (28)4.BETADIST (28)5.BETAINV (28)6.BINOMDIST (29)7.CHIDIST (29)8.CHIINV (29)9.CHITEST (29)10.CONFIDENCE (29)11.CORREL (29)12.COUNT (29)13.COUNTA (30)14.COUNTBLANK (30)15.COUNTIF (30)16.COV AR (30)17.CRITBINOM (30)18.DEVSQ (30)19.EXPONDIST (30)20.FDIST (30)21.FINV (30)22.FISHER (31)23.FISHERINV (31)24.FORECAST (31)25.FREQUENCY (31)31.GROWTH (32)32.HARMEAN (32)33.HYPGEOMDIST (32)34.INTERCEPT (32)35.KURT (32)RGE (32)37.LINEST (32)38.LOGEST (33)39.LOGINV (33)40.LOGNORMDIST (33)41.MAX (33)42.MAXA (33)43.MEDIAN (33)44.MIN (33)45.MINA (33)46.MODE (33)47.NEGBINOMDIST (34)48.NORMDIST (34)49.NORMSINV (34)50.NORMSDIST (34)51.NORMSINV (34)52.PEARSON (34)53.PERCENTILE (34)54.PERCENTRANK (34)55.PERMUT (35)56.POISSON (35)57.PROB (35)58.QUARTILE (35)59.RANK (35)60.RSQ (35)61.SKEW (35)62.SLOPE (35)63.SMALL (36)64.STANDARDIZE (36)65.STDEV (36)66.STDEV A (36)67.STDEVP (36)68.STDEVPA (36)69.STEYX (36)70.TDIST (37)71.TINV (37)72.TREND (37)73.TRIMMEAN (37)75.V AR (37)76.V ARA (37)77.V ARP (37)78.V ARPA (38)79.WEIBULL (38)80.ZTEST (38)(十一)文本和数据函数 (38)1.ASC (38)2.CHAR (38)3.CLEAN (38)4.CODE (38)5.CONCATENATE (38)6.DOLLAR 或RMB (38)7.EXACT (39)8.FIND (39)9.FINDB (39)10.FIXED (39)11.JIS (39)12.LEFT 或LEFTB (39)13.LEN 或LENB (39)14.LOWER (40)15.MID 或MIDB (40)16.PHONETIC (40)17.PROPER (40)18.REPLACE 或REPLACEB (40)19.REPT (40)20.RIGHT 或RIGHTB (40)21.SEARCH 或SEARCHB (41)22.SUBSTITUTE (41)23.T (41)24.TEXT (41)25.TRIM (41)26.UPPER (41)27.V ALUE (41)28.WIDECHAR (41)三、函数应用案例──算账理财 (42)1.零存整取储蓄 (42)2.还贷金额 (42)3.保险收益 (42)4.个税缴纳金额 (43)四、函数应用案例──信息统计 (43)1.性别输入 (43)2.出生日期输入 (44)3.职工信息查询 (44)5.年龄统计 (45)6.名次值统计 (45)7.位次阈值统计 (46)五、函数应用案例──管理计算 (46)1.授课日数 (46)2.折旧值计算 (46)3.客流均衡度计算 (47)4.销售额预测 (47)5.客流与营业额的相关分析 (47)一、函数应用基础( 一) 函数和公式1 .什么是函数Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。

单行函数

复习Scott: tigerSystem: managerSys:change_on_installSqlplus:Sqlplusw:Select {distinct} * |From 表名Where 条件表达对结果进行排序（ORDER BY子句）（重点）在SQL中可以使用ORDER BY子句对查询结果进行排序语法：SELECT ｛DISTINCT｝* | 具体的列别名FROM 表名{ WHERE 条件表达式}{ORDER BY 排序的字段1 ASC|DESC, 排序的字段2 ASC|DESC }其中：ASC 升序，DESC 降序, 默认是升序例：按雇员的编号进行排序，此时，如果想对指定列排序，必须使用ORDER BY 语句，Select * from emp order by empno;例：要求按工资由低到高排序Select * from emp order by sal asc;例：要求查询部门编号为10部门的所有雇员信息，要求按照工资由高到低排序，如果工资相等，则按照雇佣日期由早到晚排序Select * from empWhere deptno = 10Order by sal desc ,hiredate asc;注：排序的操作肯定是放在整个SQL语句的最后执行练习1：查询促销人员的入职日期，要求按照工资由高到低排序Select ename,job,hiredate,SAL from emp where job=’SALESMAN’order by sal desc;练习2：查询部门为30的促销人员，并按入职日期由晚到早排序SELECT * FROM EMPWHERE DEPTNO=30 AND JOB=’SALESMAN’ORDER BY HIREDATE DESC;练习3：查询领导编号为7698的雇员信息，并按编号由低到高排序SELECT * FROM EMPWHERE MGR=7698ORDER BY EMPNO ASC;练习4：查询工资超过1500的促销人员的并且按照工资由高到低排序，如果工资相等，则按照姓名排序SELECT * FROM EMPWHERE SAL>1500 AND JOB=’SALESMAN’ORDER BY SAL DESC ,ENAME单行函数（重点）在数据库系统中，每个数据库之间的唯一不同的最大区别就是对函数的支持一单行函数语法：function_name( column | expression,[arg1,arg2,…..])函数名（数据库列名｜字符串或计算表达式,［参数1，参数2］）, 二单行函数的分类1 字符函数：接受字符输入并且返回字符或数值2 数值函数：接受数值输入并返回数值3 日期函数：操作日期型数据4 转换函数：从一种数据类型转换为另一种数据类型5 通用函数：NVL DECODE 函数Dual: 临时表三字符函数UPPER（列名|字符串）：将字符转换为大写LOWER(): 将字符串转换为小写INITCAP(): 将单词首字母大写练习1：雇员姓名要求首字母大写，并且显示‘雇员姓名’，工作职位要求以小写字母显示，要求查询‘clerk’的信息Select 列from 表名where 从行上选择select initcap(ename) as as 雇员姓名, lower(job)from empwhere job=upper('clerk');CONCAT(字串1，字串2 )：字符串连接不如|| 好用SUBSTR(字符串，起始位置，长度):字符串截取,注：起始位置如果为负数，证明是从后面指定位置截取Substr(‘hello’,2,1): e Substr(‘hello’,-2,1) :lLENGTH():求串长REPLACE(字符串，等替换的字符串，替换的字符串): 内容替换INSTR(字符串，子串)：查找子串, 返回子串第一个字符的位置,没有返回0LPAD(字段名,长度,填充的字符)：返回指定长度的字符串，若不足长度在字符串左边添写填充字符RPAD(字段名,长度,填充的字符)：返回指定长度的字符串，若不足长度在字符串右边添写填充字符TRIM（’替换字符’from 字符串）：字符串两端替换指定的字符LTRIM（字符串，替换字符）左替换RTRIM（）右替换面试：在做字符串截取时，是从0开始还是从1开始？都可以，Oracle自动判断练习2：取员工姓名的后三个字符select ename, substr(ename,length(ename)-2,3) from emp;select ename,substr(ename,-3,3) from emp;四数值函数ROUND(数据，保留的数位) ：四舍五入取数据TRUNC(数据，保留的数位) : 截断数据，无四舍五入功能MOD():求佘五日期函数日期操作的规律：日期+/- 数字= 日期日期- 日期= 数字（天数）获得当前系统日期：sysdate例：显示10部门员工进入公司的星期数求距离现在多少天：当前日期-雇佣天数= 天数Select sysdate-hiredate from emp;求星期数：天数/7 =星期数Select (sysdate-hiredate)/7 from emp;Select ename,hiredate, ROUND((sysdate-hiredate)/7) 星期from emp;Select ename,hiredate, TRUNC((sysdate-hiredate)/7) +1 星期from emp;日期运算函数months_between:表示两个日期的月份之差add_months:表示给指定的日期加一个月数next_day:表示以当前时间为基准,下一个"目标日"的日期last_day:计算当前日期所在月的最后一天round:对日期进行四舍五入trnuc:表示对日期进行截取六转换函数to_char:函数将日期转换为字符类型to_NUMBER(): 转换为数字TO_DATE(): 转换为日期例：要求雇用日期的年月日分开显示七通用函数NVL: 将空值转换为指定值NVL(列名,替换的值)例：要求显示员工的年薪（年薪=（月薪+奖金）*12）select (sal+nvl(comm,0))*12 from emp;DECODE:含义解释：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)该函数的含义如下：Switch(条件)Case 值1：翻译值1Case 值2：翻译值2.。

数据库单行函数

下表中最后两项与","和"."相同，使用这2项的原因是有的地区和国家并不是用","和"."来区分分组和小数位置的。

控制面板中有修改的相应项。

注意：数字转换为字符时，小数部分如超出格式范围，则四舍五入，整数部分如果超出格式范围，显示溢出"#####"。

当字符转化为数字时，无论整数部分还是小数部分超出格式范围都将溢出或出错。

字符型转换为日期型时，只指明年份而没有月份时，默认为当前月。

指定月份但没有月份中哪一天时，默认为该月第1天。

时分秒如果未指定，则分别为00。

采用RR年份时，如果年份为00-49，则为本世纪的年份。

如果年份为50-99，则为赏格世纪的年份。

sql语句单行函数每次传入一行列值,返回一行返回值

SQL语句单行函数是数据库中非常重要的一部分，它可以用来对数据进行处理和转换，在数据分析和报表生成中有着非常重要的作用。

在SQL语句中，单行函数每次接收一行列值作为参数，返回一行返回值。

本文将从基本概念、使用方法和实例等方面来详细介绍SQL语句单行函数的相关知识。

一、基本概念SQL语句单行函数是一种特殊类型的函数，它们作用于查询的每一行数据，并对其进行处理。

这些函数可以用于数学运算、字符串操作、日期处理等各种用途。

常见的单行函数包括但不限于：数值函数、字符函数、日期函数等。

二、使用方法在SQL语句中使用单行函数非常简单，只需要在SELECT语句中的列名或者表达式的位置调用相应的函数即可。

语法格式为：SELECT 函数名(列名或表达式) FROM 表名;其中，函数名为要调用的函数名称，列名或表达式为要传入函数中的参数，表名为要查询的表的名称。

三、实例1. 数值函数数值函数可以用来对数值型的数据进行处理，比如取绝对值、对数运算、四舍五入等。

我们可以使用ABS函数来取得某列数据的绝对值：SELECT ABS(column_name) FROM table_name;2. 字符函数字符函数可以用来对字符串类型的数据进行处理，比如转换大小写、字符串连接、截取字符串等。

我们可以使用UPPER函数将某列数据转换为大写：SELECT UPPER(column_name) FROM table_name;3. 日期函数日期函数用来对日期类型的数据进行处理，比如取得年份、月份、日期间的差值等。

我们可以使用YEAR函数来获取某列数据的年份：SELECT YEAR(date_column) FROM table_name;四、总结通过本文的介绍，我们了解了SQL语句单行函数的基本概念、使用方法和实例。

单行函数在实际的数据处理和分析中有着非常重要的作用，可以大大提高数据处理的效率和灵活性。

掌握SQL语句单行函数的相关知识对于数据库开发和数据分析人员来说是非常重要的。

SQL单行函数和多行函数

SQL单⾏函数和多⾏函数单⾏函数和多⾏函数⽰意图：单⾏函数分为五种类型：字符函数、数值函数、⽇期函数、转换函数、通⽤函数单⾏函数：[sql]1. --⼤⼩写控制函数2. select lower('Hello World') 转⼩写, upper('Hello World') 转⼤写 from dual;3. --initcap: ⾸字母⼤写4. select initcap('hello world') ⾸字符⼤写 from dual;5.6. --字符控制函数7. -- concat: 字符连接函数, 等同于 ||8. select concat('Hello',' World') from dual;9. --substr:求母串中的某个⼦串10. select substr('Hello World',3) from dual;11. select substr('Hello World',3,4) from dual;12. --length和lengthb: 字符数和字节数13. select length('China') 字符数, lengthb('China') 字节数 from dual;14. --instr:在母串中，查找⼦串的位置15. select instr('Hello World','ll') from dual;16. --lpad,rpad: 左右填充,将abcd⽤*填充到10位17. select lpad('abcd',10,'*') 左填充, rpad('abcd',10,'*') 右填充 from dual;18. --trim: 去掉字符串前后指定的字符19. select trim('H' from 'Hello WorldH') from dual;20. --replace:字符串替换函数21. select replace('Hello Wordl','l','*') from dual;22.23. --数字函数24. select round(45.926,2) 四舍五⼊, trunc(45.926,2) 截断 ,mod(1600,300) 求于 from dual;25. --ROUND函数26. select round(45.923,0) 整数位, round(45.923,-1) ⼗位,round(45.923,-2) 百位 from dual;27.28. --⽇期函数29. --显⽰当前⽇期30. select sysdate from dual;31. --显⽰时间部分32. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;33. --显⽰昨天，今天和明天，加减数字仍未⽇期34. select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;35. --两个⽇期相减，结果为相差的天数，查询员⼯信息，显⽰员⼯⼯龄。

行函数知识点总结

行函数知识点总结函数的定义函数是一段完成特定任务的代码块，它可以接受输入参数，并且可以返回一个值。

函数的定义包括函数名、参数列表和函数体。

函数的定义通常遵循以下语法：```pythondef function_name(parameter1, parameter2, ...):# function body# ...return result```其中，def关键字用来定义函数，function_name是函数的名称，parameter1、parameter2等为函数的参数列表，：后面的部分为函数体，return关键字用来返回函数的结果。

函数的调用在需要使用函数的时候，可以通过函数名来进行函数的调用。

函数的调用将会执行函数体中的代码，并且返回函数的结果。

```pythonresult = function_name(argument1, argument2, ...)```其中，function_name为函数名，argument1、argument2等为函数的参数列表。

函数调用的结果可以赋值给变量，也可以直接输出或者进行其他操作。

参数传递函数的参数可以分为两种：实际参数和形式参数。

实际参数是在函数调用时传递给函数的参数，而形式参数则是在函数定义时声明的参数。

在Python中，函数的参数可以分为以下几种类型：- 位置参数：根据参数传递的位置来确定参数的值。

- 关键字参数：根据参数名来确定参数的值。

- 默认参数：在函数定义时指定的参数默认值。

- 可变参数：可以接受任意数量的参数。

以下是一些例子来说明参数传递的使用：```pythondef print_info(name, age, gender):print("Name:", name)print("Age:", age)print("Gender:", gender)# 使用位置参数print_info("Alice", 25, "female")# 使用关键字参数print_info(age=30, name="Bob", gender="male")# 默认参数def greet(name, message="Hello"):print(message, name)greet("Alice") # 输出：Hello Alicegreet("Bob", "Good morning") # 输出：Good morning Bob # 可变参数def sum_values(*args):total = 0for value in args:total += valuereturn totalprint(sum_values(1, 2, 3, 4, 5)) # 输出：15```返回值函数可以通过return语句来返回函数的结果。