格式转换函数 cast()和convert()两个函数都可以用来转换数据类型或者转换字符集。 允许转换的数据类型包括： binary[N] char[N] date datetime decimal[M,[D]] time signed[integer] unsigned[integer] mysql自带的数据类型转换： ~~~ mysql> select case when 1='1' then 'a' else 'b' end; +---------------------------------------+ | case when 1='1' then 'a' else 'b' end | +---------------------------------------+ | a | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select case when '.1'='0.1' then 'a' else 'b' end; +--------------------------------------------+ | case when '.1'='0.1' then 'a' else 'b' end | +--------------------------------------------+ | b | +--------------------------------------------+ 1 row in set (0.00 sec) ~~~ 转换数据类型： ~~~ mysql> select case when '100'='00100' then 'a' else 'b' end; +-----------------------------------------------+ | case when '100'='00100' then 'a' else 'b' end | +-----------------------------------------------+ | b | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> select case when cast('100' as signed)=cast('00100' as signed) then 'a' else 'b' end; +--------------------------------------------------------------------------------+ | case when cast('100' as signed)=cast('00100' as signed) then 'a' else 'b' end | +--------------------------------------------------------------------------------+ | a | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ~~~ 聚合函数 用在存在group by子句的语句中 1、AVG([DISTINCT] expr) 计算expr的平均值，distinct关键词表示是否排除重复值 ~~~ mysql> SELECT student_name, AVG(test_score) -> FROM student -> GROUP BY student_name; ~~~ 2、COUNT(expr) 计算expr中的个数，如果没有匹配则返回0，注意NULL的区别 ~~~ mysql> SELECT student.student_name,COUNT(*) -> FROM student,course -> WHERE student.student_id=course.student_id -> GROUP BY student_name; ~~~ 3、COUNT(DISTINCT expr,[expr...]) 计算有多少个不重复的expr值，注意是计算非NULL的个数 `mysql> SELECT COUNT(DISTINCT results) FROM student;` 4、MAX([DISTINCT] expr),MIN([DISTINCT] expr) 返回expr中最大或者最小的值 ~~~ mysql> SELECT student_name, MIN(test_score), MAX(test_score) -> FROM student -> GROUP BY student_name; ~~~ 5、SUM([DISTINCT] expr) 返回expr的求和值