MySQL去datetime类型中的年月日 mysql取年月日

目录

一.函数介绍

二.单行函数

三.字符函数

四.数字函数

五.日期函数

六.转换函数

七.通用函数

八.单行函数练习

---

一.函数介绍

本篇讲的函数都是MySQL中的函数，对其他的数据库基本没有兼容性。用select调用函数！

函数是 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 ( last_name ，first_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 ) ;

TRUNCATE( column | expression , n ) 函数

TRUNCATE 函数的作用类似于 ROUND 函数。如果第二个参数是 0或者缺少，值被截断为整数。如果第二个参数是 2 ，值被截断为两 位小数。如果第二个参数是 –2 ，值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。

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' ;

五.日期函数

在 MySQL 中允许直接使用字符串表示日期，但是要求字符串的日期 格式必须为： ‘YYYY-MM-DD HH:MI:SS’ 或者 ‘YYYY/MM/DD HH:MI:SS’;

按照上述格式，字符串胡自动转化为日期类型

示例一：

117 向 employees 表中添加一条数据，雇员 ID ： 300 ，名字： kevin ，email ： [e-mail] ，入职时间： 2049-5-1 8:30:30 ，工作部门： ‘IT_PROG’ 。

insert   into

employees ( EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID )

 values ( 300 , 'kevin' , '[e-mail]' , '2049-5-1 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) 将日期转换成字符串 ;

参数date是日期对象，format是转化为什么格式的字符串。

STR_TO_DATE(str,format) 将字符串转换成日期 ;

参数str是非标准格式的字符串对象，format是转化为什么格式的字符串。

示例一：

向 employees 表中添加一条数据，雇员 ID ： 400 ，名字： oldlu ，

email： [e-mail] ，入职时间：2049 年 5 月 5 日，工作部 门： ‘IT_PROG’ 。

insert   into

employees ( EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID )

values ( 400 , 'oldlu' , '[e-mail]' ,

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 列中，如果有佣金则显示 ‘SAL+COMM’ ，无佣金则显 示 'SAL' 。

SELECT last_name, salary, commission_pct,

if ( ISNULL ( commission_pct ) , 'SAL' , 'SAL+COMM' ) income

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 ) comm

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_REP' 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.15 , 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;