mysql内置函数查询

1. 聚合函数 ：聚合函数查询时纵向查询，它是对一列的 值进行计算，然后返回一个单一的值，聚合函数会忽略空值。

name	description
avg()	返回参数的平均值
bit_and()	按位返回and
bit_or()	按位返回or
bit_xor()	按位返回异或
count()	返回返回的指定列不为null的行数
count(distinct)	返回去重后的计算
group_concat()	返回连接的字符串
json_array()	将结果集作为单个的json数组返回
json_object()	将结果集作为单个json对象返回
max()	返回最大值，如果指定列是字符串类型，则按字符串排序运算
min()	返回最小值，如果指定列是字符串类型，则按字符串排序运算
sum()	返回数值和，如果指定列类型不是数值类型，则返回结果为0
std()	返回样本的标准差
stddev()	返回样本的标准差
stddev_pop()	返回样品的标准差

• 语法

  select count(id) from table_name;
  select count(id) from product where price > 200; -- 查询价格大于200的商品总条数
  select sum(price) from product where category_id = 'c001'; -- 查询商品类别为'c001'的所有商品价格总和
  

• 聚合函数对null值的处理

  – count()：如果count函数的参数为*，即count(*)，则统计所有记录的个数，如果参数为字段，则不统计包含null的值的记录的个数。

  – sum()和avg()：他俩忽略null值的存在，就好像该记录不存在。

  – max()和min()：同样忽略null的存在。

• group_concat()：

  – 函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果.

  – 语法：

  group_concat([distinct]) 字段名 [order by 排序字段 sac/desc] [separator '分隔符']
  

  • 使用distinct可以排除重复值；
  • 如需要对结果中的值进行排序，可以使用prder by子句；
  • separator 是一个字符串值，默认为逗号。

  – 示例：

  • 创建数据库demo1，并在demo1中创建员工表emp、插入数据

    create database demo1;
    use demo1;
    
    create table emp(
    	emp_id int primary key auto_increment comment '员工编号',
    	emp_name varchar(16) not null default '' comment '员工姓名',
    	salary decimal(8,2) not null default 0 comment '员工工资',
    	department char(16) not null default '' comment '所属部门'
    	)charset=utf8;
    	
    insert into emp(emp_name, salary, department) values('金金',5000,'财务'),
    ('王非',5800,'财务'),
    ('李刚',6200,'财务'),
    ('小刘',5700,'生产'),
    ('王鹏',6700,'生产'),
    ('张烟',5200,'生产'),
    ('刘云',7500,'商务'),
    ('许新',7200,'商务'),
    ('许新',7800,'商务');
    

  ​ – 将所有员工的名字合并成一行

  select group_concat(emp_name) from emp;
  

​ – 指定分隔符合并

select group_concat(emp_name separator '--') from emp;

​ – 指定排序方式和分隔符

select group_concat(emp_name order by salary separator '--') from emp;

– 将同一部门的员工拼接在一行

select department, group_concat(emp_name order by salary separator '--') from emp group by department;

2. 数值计算类函数

name	description	example
abs(x)	返回x的绝对值	select ABS(-1); --返回 1
ceil(x)	向上取整	select CEIL(1.5); --返回2
floor(x)	向下取整	select FLOOR(1.5); --返回1
GREATEST(exp1,exp2,exp3,…)	返回列表中的最大值	select GREATEST(3,24,38,5); – 返回38
LEAST(exp1,exp2,exp3,…)	返回列表中的最小值	select LEAST(3,24,38,5); – 返回3 select LEAST(‘Google’,‘Runoob’,'Apple); – 返回 Apple
max(expression)	返回expression中的最大值	select max(salary) from emp; – 返回emp表中salary字段最大的值（最高工资)
min(expression)	返回expression中的最小值	select min(salary) from emp; – 返回emp表中salary字段最小的值（最低工资)
mod(x,y)	返回x/y的模	select mod(5,2); --返回5除以2的余数
pi()	返回π（30141593）	select pi(); --返回3.141593
power(x,y)	返回x的y次方	select pow(2,3); --返回8
rand()	返回0到1内的随机数	select rand()
round(x,y)	将x四舍五入保留y位小数	select round(3.1415,3); --返回3.142 select round(3.1415); --返回3
truncate(x,y)	返回x保留y位小数的截断值， 省略y则去掉所有小数部分， 如果 y 为正数，那么小数点后 y 位将被截断； 如果 y 为负数，则会从左边开始截断	select truncate(3.1415,3); --返回3.141 select truncate(3.1415); --返回3 select truncate(3141.5); --返回3100

3. 日期计算类函数

   name	description
   curdate()	返回当前日期
   curtime()	返回当前时间
   now()	返回当前日期和时间
   unix_timestamp(date)	返回日期date的unix时间戳
   from_unixtime	返回unix时间戳的日期值
   week(date)	返回日期date为一年的第几周
   year(date)	返回日期date的年份
   hour(time)	返回time的小时值
   minute(time)	返回time的分钟值
   monthname(date)	返回date的月份名
   date_format(date,fmt)	返回按字符串fmt格式化日期date值
   date_add(date,interval expr type)	返回一个日期或时间值加上一个时间间隔 的时间值
   datadiff(exper,exper2)	返回起始时间expr和结束时间expr2之间的天数

4. 字符串相关函数

   name	description	example
   cancat(s1,s2,…sn)	无分隔符连接字符串s1,s2,…sn	select cancat(‘hello’,‘world’); --返回helloworld select cancat(c1,c2) from tabalename;
   cancat_ws(x,s1,s2,…sn)	使用分隔符x连接字符串s1,s2,…sn	select cancat(‘–’,hello’,‘world’); --返回hello–world
   char_length(str)	获取字符串str中字符个数	select char_length(‘hello’); --5 select char_length(‘你好吗’);–3
   field(s,s1,s2,…)	返回s在字符串列表（s1,s2,…）中第一次出现的位置	select field(‘d’,‘a’,‘b’,‘c’,‘d’,‘e’); --4
   length(str)	获取字符串的字节长度，一个英文字符占1个字节， mysql默认的utf8编码中一个汉字占3个字节	select length(‘hello’); --5 select length(‘你好吗’); --9
   insert(str,x,y,instr)	将字符串str中从x位置开始，y个长度的字符替换为instr
   lower(str)	将字符串str转为小写
   upper(str)	将字符串str转为大写
   left(str,x)	返回字符串str最左边的x个字符
   right(str,x)	返回字符串str中最右边的x个字符
   lpad(str,n,pad)	用pad在str左边进行填充直到字符串达到n个字符长度， 如果字符串本身的长度已经等于或超过指定的长度，则不做任何改变
   rpad(str,n,pad)	用pad在str左边进行填充直到字符串达到n个字符长度， 如果字符串本身的长度已经等于或超过指定的长度，则不做任何改变
   ltrim(str)	去掉字符串str左边的空格
   rtrim(str)	去掉字符串str右边的空格
   trim(str)	去掉字符串str左右两边的空格
   repeat(str,x)	返回str重复x次的结果
   replace(str,a,b)	用b替换str中所有的a
   strcmp(s1,s2)	比较s1和s2
   substring(str,x,y)	返回在str中从x位置开始y个字符长度的子串
   position(x in y)	返回字符串x在Y中第一次出现的位置	select positon(‘opq’,‘abcopqefgopq’); --4

5. mysql中常用的其他函数

   name	description
   datebase()	返回当前数据库名
   version()	返回当前数据库版本
   user()	返回当前登录的用户名
   inet_aton(ip)	返回ip地址的数字表示
   inet_ntoa(num)	返回数字代表的ip地址
   password(str)	返回字符串str的加密后的字符串
   md5()	返回字符串str的md5的值
   ifnull(column,0)	如果column的值为null，则将其按0处理，否则等于其值本身