MySQL查询（二）

函数调用：

select 函数名(实参列表) [from 表]

1. 常见函数

1.1. 单行函数

• 字符函数

  • length 获取参数值的字节个数，一个字母是1个字节，一个汉字3个字节

    select length('john');
    

  • concat 拼接字符

    select concat(last_name,'_',first_name) from employees;
    

  • upper、lower 将字符转化为大写或小写

    select upper('john');
    

    select concat(upper(last_name),'_',lower(first_name)) 姓名
    from employees; #将姓大写，名小写，并连接起来，中间用'_'隔开，起别名——姓名
    

  • substr、substring

    截取字符中某一部分。第一个参数是字符；第二个参数是开始的索引，SQL的索引从1开始；第三个参数是截取的字符长度，若没有则截取到末尾。

    select substr('我非常爱SQL',5);     #从索引5开始往后取到末尾，输出结果是SQL
    

    select substr('我非常爱SQL',2,2);  #从索引2开始取，截取2个字符，输出结果是非常
    

  • instr 返回子字符第一次出现的索引，若找不到，返回0

    select instr('我非常爱SQL','非常爱');  #输出结果是2
    

  • trim 去掉字符前后的空格

    select length(trim('   wng   '));   #输出结果是3
    

    select trim('a' from 'aaaaawhioahjgaaaaaaaa');  #去掉字符前后的a，输出结果是whioahjg
    

  • lpad 用指定的字符实现左填充指定长度

    select lpad('殷素素',10,'*');  #用*左填充，使得字符长度达到10，输出结果是*******殷素素
    

  • rpad 用指定的字符实现右填充指定长度

    select rpad('殷素素',10,'ac');  #用ac右填充，使得字符长度达到10，输出结果是殷素素acacaca
    

  • replace 替换

    select replace('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏');  #用赵敏替换所有的周芷若，输出结果是赵敏赵敏张无忌爱上了赵敏
    

• 数学函数

  • round 四舍五入

    select round(-1.567);  #默认四舍五入为整数，结果是-2
    select round(-1.567,2);  #保留小数后两位，结果是-1.57
    

  • ceil 向上取整，返回大于等于该参数的最小整数

    select ceil(1.02);  #结果是2
    

  • floor 向下取整，返回小于等于该参数的最大整数

    select floor(-9.2); #结果是-10
    

  • truncate 截断

    select truncate(1.689，1);  #保留1位小数，直接截断，输出结果是1.6
    

  • mod 取余

    select mod(10,3);   #10除以3的余数，结果是1
    select mod(-10,3);  #-10除以3的余数，结果是-1，余数的正负号与被除数的正负号一致
    

• 日期函数

  • now 返回当前系统日期+时间

    select now();  #2023-02-22 10:49:00
    

  • curdate 返回当前系统日期

    select curdate();   #2023-02-22
    

  • curtime 返回当前时间

    select curtime();  #10:51:33
    

  • datediff 获取两个日期之间相差多少天

    select datediff(now(),'1999-3-6');  #现在距离1999年3月6日的天数
    

  • 可以获取指定的部分，年year、月month、日day、小时hour、分钟minute、秒second

    select year(now());  #获取当前的年份
    select year(hiredate) 年 from employees;  #获取员工入职年份
    

    select month(now());  #获取当前月份
    select monthname(now());  #获取当前月份的英文，输出结果是February
    

  • str_to_date 将字符转换为日期

    格式符	功能
    %Y	四位的年份
    %y	两位的年份
    %m	月份（01,02,…,12）
    %c	月份（1,2,…,12）
    %d	日（01,02,…）
    %H	小时（24小时制）
    %h	小时（12小时制）
    %i	分钟（00,01,…,59）
    %s	秒（00,01,…,59）

    select str_to_date('3-5-1998','%c-%d-%Y');  #将字符转换为日期数据，第二项参数是提示字符中使用的格式，输出结果是1998-03-05
    

  • date_format 将日期转换为字符

    select date_format(now(),'%y年%m月%d日');  #将当前日期转换为字符，字符的格式按照第二项参数写，输出结果是23年02月22日
    

• 其他函数

  • select version();  #查看当前版本
    

  • select database();   #查看当前数据库
    

  • select uesr();    #查看当前用户
    

• 流程控制函数

  • if函数

    select if(10>5,'大','小');  #参数1：返回逻辑值的表达式，参数2：结果是true时的返回值，参数3：结果是false时的返回值
    

  • case

    若作为表达式用在select后面，则then后面只能跟显示的值，并且不加分号；若作为单独的语句，then后面可以跟表达式。

    第一种用法：（一般用于判断等于某个值的时候）

    case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1;
    when 常量2 then 要显示的值2或语句2;
    ...
    else 要显示的值n或语句n;
    end
    

    举例：

    select salary 原始工资,department_id,
    case department_id
    when 30 then salary*1.1
    when 40 then salary*1.2
    when 50 then salary*1.3
    else salary
    end as 新工资
    from employees;
    

    第二种用法：（一般用于判断大于或小于某个值的时候）

    case
    when 条件1 then 要显示的值1或语句1;
    when 条件2 then 要显示的值2或语句2;
    ...
    else 要显示的值n或语句n;
    end
    

    举例：

    select salary,
    case
    when salary>20000 then 'A'
    when salary>15000 then 'B'
    when salary>10000 then 'C'
    else 'D'
    end as 工资级别
    from employees;
    

1.2. 分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

• sum 求和，适用于数值型数据，自动忽略null值

  select sum(salary) from employees;
  

• avg 求均值，适用于数值型数据，自动忽略null值

  select avg(salary) from employees;
  

• max 求最大值，适用于数值型、字符型、日期型数据，自动忽略null值

  select max(salary) from employees;
  

• min 求最小值，适用于数值型、字符型、日期型数据，自动忽略null值

  select min(salary) from employees;
  

• count 计数，适用于所有数据类型，计算的是非空值的个数

  select count(salary) from employees;
  

  select count(*) from employees;  #统计表格中共有多少行，使用最多
  

  select count(1) from employees;  #在表格中增加一列，值全为1，并统计表格中1这一列共有多少行
  

• 可以与distinct搭配使用

  select sum(distinct salary) from employees; #计算工资去重以后的和
  select count(distinct salary) from employees; #计算有多少种不同的工资
  

2. 分组查询

• 基本语法

  select 分组函数,列（要求出现在group by后面）
  from 表
  [where 筛选条件]
  group by 分组的列表
  [order by 排序的字段]
  

• 简单的分组查询

  select avg(salary),job_id
  from employees
  group by job_id;  #查询每个工种的平均工资
  

• 添加分组前的筛选条件

  筛选的数据源是原始表，放在group by子句前面，用where连接

  select avg(salary),department_id
  from employees
  where email like '%a%'
  group by department_id;  #查询每个部门中邮箱包含字符a的员工的平均工资
  

• 添加分组后的筛选条件

  筛选的数据源是分组后的结果表，放在group by子句后面，用having连接

  select count(*) 个数,department_id
  from employees
  group by department_id
  having 个数>2;  #找到部门人数超过2的部门编号。首先按部门分组，计算每个部门人数，再对分组后的结果进行筛选，用having进行筛选
  

  select min(salary),manager_id
  from employees
  where manager_id>102
  group by manager_id
  having min(salary)>5000;  查询领导编号大于102的每个领导下的员工最低工资大于5000的领导编号和最低工资
  

  可以用分组前筛选的优先用分组前筛选。

• 按表达式或函数分组

  select count(*),length(last_name)
  from employees
  group by length(last_name)
  having count(*)>5;  #按照员工名长度分组，查询每组员工个数，筛选员工个数大于5的组
  

  MySQL 中 group by 和 having 后面都支持使用别名。

• 按多个字段分组，多个字段之间用逗号隔开，没有顺序之分

  select avg(salary),department_id,job_id
  from employees
  group by department_id,job_id;  #查询每个部门每个工种的员工平均工资
  

• 添加排序

  select avg(salary),department_id,job_id
  from employees
  where department_id is not null
  group by department_id,job_id
  having avg(salary)>10000
  order by avg(salary) desc;