【MySQL 教程（五）】SQL函数详解：字符、数字、日期、转换与通用函数

SQL函数函数介绍函数是 SQL 的一个非常强有力的特性函数能够用于下面的目的执行数据计算修改单个数据项操纵输出进行行分组格式化显示的日期和数字转换列数据类型SQL 函数有输入参数并且总有一个返回值。函数分类单行函数​ 单行函数仅对单个行进行运算并且每行返回一个结果。常见的函数类型字符数字日期转换多行函数多行函数能够操纵成组的行每个行组给出一个结果这些函数也被称为组函数单行函数单行函数分类字符函数大小写处理函数示例显示雇员 Davies 的雇员号、姓名和部门号将姓名转换为大写。select employee_id,UPPER(last_name),department_id from employees where last_name davies;字符处理函数示例显示所有工作岗位名称从第 4 个字符位置开始包含字符串 REP的雇员的ID信息将雇员的姓和名连接显示在一起还显示雇员名的的长度以及名字中字母 a 的位置。select employee_id,concat(first_name,last_name) name,job_id,length(last_name),instr(last_name,a) Contains a? from employees where substr(job_id,4) REP;数字函数ROUND(column|expression, n) 函数ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个参数是 0 或者缺少值被四舍五入为整数。如果第二个参数是 2值被四舍五入为两位小数。如果第二个参数是–2值被四舍五入到小数点左边两位。# SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1); select round(45.923,2),round(45.923,0),round(45.923,-1);TRUNCATE(column|expression,n) 函数TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少值被截断为整数。如果第二个参数是 2值被截断为两位小数。如果第二个参数是–2值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。# SELECT TRUNCATE(45.923,2); select truncate(45.923,2)使用MOD(m,n) 函数MOD 函数找出m 除以n的余数。示例所有job_id是SA_REP的雇员的名字薪水以及薪水被5000除后的余数。# SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id SA_REP; select last_name, salary,mod(salary,5000) from employees where job_id SA_REP日期函数在MySQL中允许直接使用字符串表示日期但是要求字符串的日期格式必须为‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;示例一向 employees 表中添加一条数据雇员ID300名字kevin emailkevinsxt.cn 入职时间2049-5-1 8:30:30工作部门‘IT_PROG’。insert into employees(employee_id,last_name,email,hire_date,job_id) values(300,kevin,kevinsxt.cn,2049-05-01 8:30:30,IT_PROG );示例二显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算用当前日期 (SYSDATE) 减去雇员的受顾日期再除以 7。select last_name, (sysdate()-hire_date)/7 as weeks from employees where department_id 90;转换函数隐式数据类型转换隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。如可以将标准格式的字串日期自动转换为日期类型。MySQL字符串日期格式为‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;显示数据类型转换显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。如DATE_FORMAT(date,format) 将日期转换成字符串;STR_TO_DATE(str,format) 将字符串转换成日期;示例一向 employees 表中添加一条数据雇员ID400名字oldlu emailoldlusxt.cn 入职时间2049 年 5 月 5 日工作部门‘IT_PROG’。insert into employees(employee_id,last_name,email,hire_date,job_id) values(400,oldlu,oldlusxt.cn,str_to_date(2049年5月5日, %Y年%m月%d日),IT_PROG );示例二查询 employees 表中雇员名字为 King 的雇员的入职日期要求显示格式为 yyyy 年 MM 月 dd 日。select date_format(hire_date,%Y年%m月%d日) from employees where last_name King;通用函数示例一查询部门编号是50或者80的员工信息包含他们的名字、薪水、佣金。在income列中如果有佣金则显示‘SALCOMM’无佣金则显示’SAL’。select last_name,salary,commission_pct, if(isnull(commission_pct),SAL,SALCOMM) incom from employees where department_id in(50,80);示例二计算雇员的年报酬你需要用 12 乘以月薪再加上它的佣金 (等于年薪乘以佣金百分比)。select last_name,salary,IFNULL(commission_pct,0), (salary*12) (salary*12*IFNULL(commission_pct,0)) AN_SAL from employees;示例三查询员工表显示他们的名字、名字的长度该列名为expr1姓氏、姓氏的长度该列名为expr2。在result列中如果名字与姓氏的长度相同则显示空如果不相同则显示名字长度。select first_name,length(first_name) expr1, last_name,length(last_name) expr2, nullif(length(first_name), length(last_name)) result from employees;示例四查询员工表显示他们的名字如果 COMMISSION_PCT 值是非空显示它。如果COMMISSION_PCT 值是空则显示 SALARY 。如果 COMMISSION_PCT 和SALARY 值都是空那么显示 10。在结果中对佣金列升序排序。select last_name,coalesce(commission_pct,salary,10) com from employees order by commission_pct;示例五查询员工表如果 JOB_ID 是 IT_PROG薪水增加 10%如果 JOB_ID 是 ST_CLERK薪水增加 15%如果 JOB_ID 是 SA_REP薪水增加 20%。对于所有其他的工作角色不增加薪水。select last_name,job_id,salary, case job_id when IT_PROG then 1.10*salary when ST_CLERK then 1.15*salary when SA_PEP then 1.20*salary else salary end REVISED_SALARY from employees;练习1.显示受雇日期在 1998 年 2 月 20 日 和 2005 年 5 月 1 日 之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。select last_name,job_id,hire_date from employees where hire_date between 1998-2-20 and 2005-5-1 order by hire_date;2.显示每一个在 2002 年受雇的雇员的名字和受雇日期。select last_name,hire_date from employees where hire_date like 2002%;3.对每一个雇员显示 employee number、last_name、salary 和 salary 增加 15%并且表示成整数列标签显示为 New Salary。select employee_id,last_name,salary, round(salary *1.5,0) from employees;4.写一个查询显示名字的长度对所有名字开始字母是 J、A 或 M 的雇员。用雇员的 last name排序结果。select last_name,length(last_name) from employees; where last_name like j% or last_name like A% or last_name like M% order by last_name;5.创建一个查询显示所有雇员的 last name 和 salary。将薪水格式化为 15 个字符长度用 $左填充 。select last_name,lpad(salary,15,$) from employees;6.创建一个查询显示雇员的 last names 和 commission (佣金) 比率。如果雇员没有佣金显示 “No Commission”列标签 COMM。select last_name,ifnull(commission_pct ,NO Commission) COMM from employees;7.写一个查询按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。select job_id, case job_id when AD_PRES THEN A WHEN ST_MAN THEN B WHEN IT_PROG THEN C WHEN SA_REP THEN D WHEN ST_CLERK THEN E else 0 end from employees;