二、函数
2.1 数学函数
数学函数 |
含义 |
abs(x) |
返回 x 的绝对值 |
rand() |
返回 0 到 1 的随机数 |
| mod(x,y) | 返回 x 除以 y 以后的余数 |
| power(x,y) | 返回 x 的 y 次方 |
| round(x) | 返回离 x 最近的整数 |
| round(x,y) | 保留 x 的 y 位小数四舍五入后的值 |
| sqrt(x) | 返回 x 的平方根 |
| truncate(x,y) | 返回数字 x 截断为 y 位小数的值 |
| ceil(x) | 返回大于或等于 x 的最小整数 |
| floor(x) | 返回小于或等于 x 的最大整数 |
| greatest(x1,x2…) | 返回集合中最大的值，也可以返回多个字段的最大的值 |
| least(x1,x2…) | 返回集合中最小的值，也可以返回多个字段的最小的值 |
#只能对数值进行操作 mysql> SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89); +---------+--------------------+----------+------------+-------------+ | abs(-1) | rand() | mod(5,3) | power(2,3) | round(1.89) | +---------+--------------------+----------+------------+-------------+ | 1 | 0.2744006926886744 | 2 | 8 | 2 | +---------+--------------------+----------+------------+-------------+ mysql> SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1); +-----------------+-------------------+-----------+------------+-----------------------+ | round(1.8937,3) | truncate(1.235,2) | ceil(5.2) | floor(2.1) | least(1.89,3,6.1,2.1) | +-----------------+-------------------+-----------+------------+-----------------------+ | 1.894 | 1.23 | 6 | 2 | 1.89 | +-----------------+-------------------+-----------+------------+-----------------------+ #对member表生成随机排序 mysql> select * from member order by rand(); +----+----------+--------+--------+---------+--------------------+ | id | name | cardid | phone | address | remark | +----+----------+--------+--------+---------+--------------------+ | 1 | zhangsan | 123123 | 123123 | nanjing | this is vip | | 3 | wangwu | 123123 | 123123 | wuxi | this is vvvip | | 4 | zhaoliu | 123123 | 123123 | nantong | this is vip member | | 2 | lisi | 123123 | 123123 | suzhou | this is vvip | +----+----------+--------+--------+---------+--------------------+ 4 rows in set (0.00 sec)
2.2 聚合函数
聚合函数 |
含义 |
avg() |
返回指定列的平均值 |
count() |
返回指定列中非 NULL 值的个数 |
min() |
返回指定列的最小值 |
max() |
返回指定列的最大值 |
sum(x) |
返回指定列的所有值之和 |
#查询表中sales最大的 mysql> select max(sales) from store_info; +------------+ | max(sales) | +------------+ | 1500 | +------------+ 1 row in set (0.00 sec) #查询表中sales最小的 mysql> select min(sales) from store_info; +------------+ | min(sales) | +------------+ | 250 | +------------+ 1 row in set (0.00 sec) #也可以结合排序找最小值或者最大值 mysql> select sales from store_info order by sales asc limit 1; +-------+ | sales | +-------+ | 250 | +-------+ #查询sales的平均值 mysql> select avg(sales) from store_info; +------------+ | avg(sales) | +------------+ | 687.5000 | +------------+ 1 row in set (0.00 sec) #求sales值的和 mysql> select sum(sales) from store_info; +------------+ | sum(sales) | +------------+ | 2750 | +------------+ 1 row in set (0.00 sec)
新建一个city表，用来演示count()函数
create table city (name char(20)); insert into city values('nanjing'); insert into city values('hangzhou'); insert into city values('shanghai'); insert into city values(); insert into city values(); insert into city values('beijing'); insert into city values(); insert into city values(); #查询city表中的个数时，不会把空的记录算进去 mysql> select count(name) from city; +-------------+ | count(name) | +-------------+ | 4 | +-------------+ #查询个数时，打上*，就不会忽略空行 mysql> select count(*) from city; +----------+ | count(*) | +----------+ | 8 | +----------+
2.3 字符串函数
字符串函数 |
含义 |
trim() |
返回去除指定格式的值 |
concat(x,y) |
将提供的参数 x 和 y 拼接成一个字符串 |
substr(x,y) |
获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同 |
substr(x,y,z) |
获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串 |
length(x) |
返回字符串 x 的长度 |
replace(x,y,z) |
将字符串 z 替代字符串 x 中的字符串 y |
upper(x) |
将字符串 x 的所有字母变成大写字母 |
lower(x) |
将字符串 x 的所有字母变成小写字母 |
left(x,y) |
返回字符串 x 的前 y 个字符 |
right(x,y) |
返回字符串 x 的后 y 个字符 |
repeat(x,y) |
将字符串 x 重复 y 次 |
space(x) |
返回 x 个空格 |
strcmp(x,y) |
比较 x 和 y，返回的值可以为-1,0,1 |
reverse(x) |
将字符串 x 反转 |
返回去除指定格式的值
SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串); #[位置]：的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。 #[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。 mysql> SELECT TRIM(LEADING 'Ne' FROM 'New York'); +------------------------------------+ | TRIM(LEADING 'Ne' FROM 'New York') | +------------------------------------+ | w York | +------------------------------------+ 1 row in set (0.00 sec)
将提供的参数 x 和 y 拼接成一个字符串
mysql> select concat('zhang','san'); +-----------------------+ | concat('zhang','san') | +-----------------------+ | zhangsan | +-----------------------+ ---在zhangsan中间加个空格，就是添加一个空格字符串 mysql> select concat('zhang',' ','san'); +---------------------------+ | concat('zhang',' ','san') | +---------------------------+ | zhang san | +---------------------------+ mysql> select * from location; +--------+-------------+ | region | store_name | +--------+-------------+ | East | Boston | | East | New York | | West | Los Angeles | | West | Houstion | +--------+-------------+ 4 rows in set (0.00 sec) ---对location表中的两个字段进行合并，注意不要加引号 mysql> select concat(region,store_name) from location; +---------------------------+ | concat(region,store_name) | +---------------------------+ | EastBoston | | EastNew York | | WestLos Angeles | | WestHoustion | +---------------------------+ 4 rows in set (0.00 sec) ---如sql_mode开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的 SELECT Region || ' ' || Store_Name FROM location
获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
mysql> SELECT substr(concat(region,store_name),5) FROM location; +-------------------------------------+ | substr(concat(region,store_name),5) | +-------------------------------------+ | Boston | | New York | | Los Angeles | | Houstion | +-------------------------------------+ 4 rows in set (0.00 sec) ---只显示Angele mysql> SELECT substr(store_name,5,6) FROM location where store_name='Los Angeles'; +------------------------+ | substr(store_name,5,6) | +------------------------+ | Angele | +------------------------+ 1 row in set (0.00 sec)
返回字符串 x 的长度
mysql> select store_name,length(store_name) from location; +-------------+--------------------+ | store_name | length(store_name) | +-------------+--------------------+ | Boston | 6 | | New York | 8 | | Los Angeles | 11 | | Houstion | 8 | +-------------+--------------------+ 4 rows in set (0.00 sec)
将字符串 z 替代字符串 x 中的字符串 y
mysql> select replace(region,'st','stern') from location; +------------------------------+ | replace(region,'st','stern') | +------------------------------+ | Eastern | | Eastern | | Western | | Western | +------------------------------+ 4 rows in set (0.00 sec)
