一、计算字段函数

1.concat() 函数

函数 concat() 用于拼接串,即将多个串连接形成一个较长的串,各子串之间用逗号分隔。

例如,查询学生姓名和学号,返回的信息格式为:姓名(学号) ,为计算后的列设置一个别名 student_name_no;查询语句如下:

1
select concat(name,'(',student_no,')') as student_name_no from student;

2.trim() 函数

函数 trim() 用于去除字符串两边的空格,其中函数 ltrim() 和 rtrim() 用于仅去除左边或右边的空格。例:

1
select trim(name),age,sex from student;

3.Date() 函数

函数 Date(expr) 提取日期或日期时间表达式 expr 中的日期部分。使用 Date() 函数进行日期的比较是一个良好的习惯,因为数据库中的时间可能为 datetime 类型的,包含其它部分,这样比较就会出现错误。

二、日期时间函数

用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数。

例:

当要查询日期为 2005-09-01 这一天的所有订单信息时:

1
2
3
4
5
6
7
mysql> select * from orders where order_date='2005-09-01';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)

可见该 sql 语句运行正常,但使用 where order_date=’2005-09-01’ 不是可靠的做法。order_date 的数据类型为 datetime,这种类型存储日期和时间值,也是比较普遍的做法。于是只有当 order_date 的时间为 00:00:00 时才能进行匹配,当把该记录时间改为 10:30:00 时,就会查询失败:

1
2
mysql> select * from orders where order_date='2005-09-01';
Empty set (0.00 sec)

可以使用 函数 date(expr) 提取日期或日期时间表达式 expr 中的日期部分(对应的,函数 time(expr) 提取日期或日期时间表达式 expr 中的时间部分 )。例如:

1
2
3
4
5
6
7
mysql> select * from orders where date(order_date)='2005-09-01';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 10:30:00 | 10001 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)

此外,可以使用 函数 year(expr) 提取日期时间表达式 expr 的年份,使用 函数 month() 提取日期时间表达式 expr 的月份,使用 函数 day(expr) 提取日期时间表达式的天数部分。例如:

1
2
3
4
5
6
7
mysql> select * from orders where year(order_date)='2005' and month(order_date)='09' and day(order_date)='01';
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 10:30:00 | 10001 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)

三、聚集函数

1.求值

函数 avg(column_name) 用于获取单个数值列 column_name 的平均值,忽略该列值为 null 的行;函数 sum(column_name) 返回指定列的和;函数 max(column_name) 返回单个列 column_name 的最大值,函数 min(column_name) 返回最小值,一般用于查找数值或日期列。

注意:

  • 当传入的列数据类型为字符(串)时,会取字符串的开始为数字的部分进行计算,返回的不会是预期的结果。
  • 传入的 column_name 可以是进行基本操作符运算后的,如 column_name*2 ,column_name*column_name_2
  • 以上四个聚集函数均会忽略该列值为 null 的行。
  • 列名 column_name 前面默认为 all,可以使用 distinct 对值相同的行进行去重。

例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select avg(prod_price) from products;
+-----------------+
| avg(prod_price) |
+-----------------+
| 16.133571 |
+-----------------+
1 row in set (0.00 sec)

mysql> select avg(prod_price*2) from products;
+-------------------+
| avg(prod_price*2) |
+-------------------+
| 32.267143 |
+-------------------+
1 row in set (0.00 sec)

mysql> select avg(distinct prod_price) from products;
+--------------------------+
| avg(distinct prod_price) |
+--------------------------+
| 17.780833 |
+--------------------------+
1 row in set (0.00 sec)

2.计数

函数 count(*) 对表中行的数目进行计数(某列的行值是否为 null 并不影响结果,会被计数),而 count(column_name) 对单个列 column_name 的行的数目进行计数,忽略该列值为 null 的行。

1
select count(*) from orders;