格式轉換函數 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的求和值