单行函数

第四章单行函数1. 单行函数的分类字符函数数字函数日期函数转换函数通用函数2.字符函数注意：只是把查询后的结果进行字符处理，数据库中的信息并没有改变。

lower作用：把查询的内容变成小写。

SQL> select * from employees where lower(first_name)='steven';Upper作用：把查询的内容变成大写。

SQL> select * from employees where upper(first_name)='STEVEN';Initcap作用：把每个单词的首字母变大写SQL> select initcap('i love beijing') from dual;Concat作用：连接两个字符串，与| |功能一致SQL> select concat('ab','cd') from dual;Substr作用：截取子字符串语法：substr(‘字符串’, 从哪开始截取, 截取字符数)，其中“从哪开始截取”表示字符所在位置，从1开始SQL> select substr('123456789',3,3) from dual;Length作用：获取字符长度SQL> select first_name,length(first_name) from employees;Instr作用：查询指定字符所在位置SQL> select instr('hellokitty','o') from dual;查询第二个“c”的位置，从第4个位置开始检索SQL> select instr('abcabcabc','c',4) from dual;Lpad/Rpad:填充函数Lpad/Rpad语法：lpad（‘字符串’, 显示几位，‘填充字符’），如果字符串多于显示位数，则截取成显示位数。

mysql常见的聚合函数、单行函数MySQL是一种常用的关系型数据库管理系统，它提供了许多聚合函数和单行函数，以帮助我们对数据进行汇总、计算和处理。

在本文中，我们将介绍一些常见的聚合函数和单行函数，并说明它们的用途和用法。

一、聚合函数1. COUNT：用于计算指定列中的行数。

例如，可以使用COUNT函数统计某个表中的记录数。

2. SUM：用于计算指定列中数值的总和。

例如，可以使用SUM函数计算某个表中某个字段的总和。

3. AVG：用于计算指定列中数值的平均值。

例如，可以使用AVG函数计算某个表中某个字段的平均值。

4. MAX：用于计算指定列中数值的最大值。

例如，可以使用MAX函数找出某个表中某个字段的最大值。

5. MIN：用于计算指定列中数值的最小值。

例如，可以使用MIN函数找出某个表中某个字段的最小值。

二、单行函数1. CONCAT：用于将多个字符串连接在一起。

例如，可以使用CONCAT函数将姓和名连接成一个完整的姓名。

2. LENGTH：用于计算字符串的长度。

例如，可以使用LENGTH函数计算某个字段中字符串的长度。

3. UPPER：用于将字符串转换为大写。

例如，可以使用UPPER函数将某个字段中的字符串转换为大写。

4. LOWER：用于将字符串转换为小写。

例如，可以使用LOWER函数将某个字段中的字符串转换为小写。

5. SUBSTRING：用于提取字符串的一部分。

例如，可以使用SUBSTRING函数提取某个字段中字符串的前几个字符。

总结：在MySQL中，聚合函数和单行函数是非常常用的工具，它们可以帮助我们对数据进行汇总、计算和处理。

通过使用这些函数，我们可以更加高效地分析和处理大量的数据。

无论是统计记录数、计算总和还是提取字符串的一部分，这些函数都能够很好地满足我们的需求。

因此，在使用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。

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，原样返回。

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

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

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

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

包含的函数有如下⼏种：No函数名称返回类型描述1 UPPER(列|字符串)字符串将传⼊的字符串变成⼤写形式2 LOWER(列|字符串)字符串将传⼊的字符串变成⼩写形式3 INITCAP(列|字符串)字符串开头⾸字母⼤写，其他的字母变为⼩写4 LENGTH(列|字符串)数字取得指定字符串的长度5SUBSTR(列|字符串，开始索引，[长度])字符串进⾏字符串的截取，如果没有设置长度，表⽰从截取全部6REPLACE(列|字符串，旧内容，新内容)字符串将指定的字符串数据以新数据替换旧数据范例：在Oracle⾥⾯，所有的函数如果要想进⾏验证，也必须编写SQL语句。

为了⽅便⽤户进⾏⼀些验证或者是⼀些不需要查询表的查询操作，专门提供了⼀个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的⽅式进⾏处理。

单行函数：1. 字符串类型的函数2. 数字类型的函数3. 日期类型的函数注意：1. 函数的名称2. 2.函数的作用3. 函数传递参数的数量以及参数的类型4. 函数的返回结果Select语句查询：用户名（username）s1，密码（password）为：是的用户的信息操作符：1. between。

and。

2.in【list】2. like3. %:0个或多个任意字符4. _: 1个任意字符5. escape：转移特殊字符6. is null5.> < >= <= <> !=6.And or notSqlplus工具的使用：字符串函数：LOWER：将所有字母转换为小写；UPPER：将所有字母转换为大写；INITCAP：将字符串首字母转换为大写；CONCAT：连接多个首字母的值；SUBSTR：截取信息；LENGTH：求长度；NVL：将空值转换为指定的字符串；数字类型的转换：ROUND；TRUNC；Sysdate:当前日期；Dummy：亚表；MOD：求余；字符串类型的转换：TO_CHAR;TO_NUMBER:TO_DATE;外连接分类：左外连接，右外连接和全连接左外连接：（+）在等号右边的链接；特征：会将左边表中不满足条件的记录一并查找出来右外连接：“+”在等号的左边的链接；特征：会将右边表中的不满足条件的记录一并查处；Select name，last_name from s_customer c left outer join s_emp e on e.id=c.sales_rep_id;Select name,last_name from s_emp e right outer join s_customer on e.id=c.sales_rep_id;查询：从员工表中查找员工。

复习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.。