sql语句的函数整理部分

r囧r小猫 2021-07-25 02:42 499阅读 0赞

### sql语句的函数 ###

*   *  单行函数
     *   *  字符函数
         *   *  ifnull
             *  isnull
             *  length
             *  concat
             *  upper、lower
             *  substr、substring
             *  instr
             *  trim
             *  lpad
             *  rpad
             *  replace
         *  数学函数
         *   *  round
             *  ceil
             *  floor
             *  truncate
             *  mod
         *  日期函数
         *   *  now
             *  curdate
             *  curtime
             *  指定年、月、日、小时、分、秒
             *  符号表格
             *  str\_to\_data
             *  date\_format
             *  datadiff
         *  其他函数
         *   *  version
             *  database
             *  user
         *  流程控制函数
         *   *  if函数
             *  switch语句
             *  if-else语句
     *  多行函数
     *   *  基本
         *  和distinct搭配
         *  count
         *  和分组函数一同查询的字段要求

## 单行函数 ##

### 字符函数 ###

#### ifnull ####

判断某字段或表达式是否为null，如果是返回指定值，否则返回原本值

//显示出user表的全部列，（处理空值）
    select CONCAT(`first_name`,",",IFNULL(last_name,0)) from user  
    
    IFNULL的第二参0是如果last_name为空则用0代替

#### isnull ####

判断某字段或表达式是否为null，如果是返回1，否则返回0

#### length ####

获取参数值的字节个数

select length('haha')    # 4 utf-8下
    select length('洋haha')   # 7 utf-8下

#### concat ####

拼接字符

select concat(fitst,"_",lastname) as '名字' from users

#### upper、lower ####

转大小写

select concat(upper(fitst),lower(lastname)) as '名字' from users

#### substr、substring ####

从某个文本字段提取字符

语法：	SubString(字段，1，end)

#### instr ####

返回字串第一次出现的索引，如果找不到就返回0

select insrt( 'abcdefg','a') as out_str // 0
    select insrt( 'abcdefg','b') as out_str // 1

#### trim ####

去除空格

select trim( ' abcdefg ') as out_str // 'abcdefg'
    elect trim('aa' from 'aaa等等bb') as out_str // 'a等等bb'

#### lpad ####

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

select LPAD('啊洋啊',5,"*") outPrint;  // **啊洋啊

#### rpad ####

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

select RPAD('啊洋啊',5,"*") outPrint;  // 啊洋啊**

#### replace ####

select REPALCE('洋啊洋','洋','6')  // 6啊6

### 数学函数 ###

#### round ####

四舍五入

select round(-1.55)  // -2
    select round(-1.55,1)  // -1.6 保留一位小数

#### ceil ####

向上取整,返回比 此参数大的整数

select ceil(-1.2)  // -1
    select ceil(2.5)   // 3

#### floor ####

向下取整,返回比 此参数小的整数

select floor(-9.99)   // -10
    select floor(9.99)    // 9

#### truncate ####

截断

select truncate(1.69999,1)  // 1.6
    select truncate(1.69999,2)  // 1.69

#### mod ####

取余

select mod(11,3) // 2
    等价于     select 11%3
    select mod(-10,-3)  // -1 数学问题

### 日期函数 ###

#### now ####

返回当前的系统日期+时间

select now()   // 2020-04-14 12:25:09

#### curdate ####

返回当前的系统日期，不包含时间

select curdate() // 2020-04-14

#### curtime ####

返回当前的系统时间，不包含日期

select curdate() // 2020-04-14

#### 指定年、月、日、小时、分、秒 ####

select year(now()) 年    // 2020
    select month(now()) 月   // 4
    // 英文版的
    select monthname(now()) 月 // April

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cjQXjAgL-1587093134072)(C:\\Users\\thinkbook15\\AppData\\Roaming\\Typora\\typora-user-images\\image-20200414144652631.png)\]

#### 符号表格 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">格式符</th> 
   <th align="center">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">%Y</td> 
   <td align="center">四位的年份</td> 
  </tr> 
  <tr> 
   <td align="center">%y</td> 
   <td align="center">二位的年份</td> 
  </tr> 
  <tr> 
   <td align="center">%m</td> 
   <td align="center">月份（01，02，03）</td> 
  </tr> 
  <tr> 
   <td align="center">%c</td> 
   <td align="center">月份（1，2，3，4）</td> 
  </tr> 
  <tr> 
   <td align="center">%d</td> 
   <td align="center">日(01，02，03）</td> 
  </tr> 
  <tr> 
   <td align="center">%H</td> 
   <td align="center">小时（24小时值）</td> 
  </tr> 
  <tr> 
   <td align="center">%h</td> 
   <td align="center">小时（12小时制）</td> 
  </tr> 
  <tr> 
   <td align="center">%i</td> 
   <td align="center">分钟（01，02，03）</td> 
  </tr> 
  <tr> 
   <td align="center">%s</td> 
   <td align="center">秒（00，01，02）</td> 
  </tr> 
 </tbody> 
</table>

#### str\_to\_data ####

将字符通过指定的格式转换为日期

select str_to_date('2020-04-14','%Y-%c-%d') as 日期     # 2020-04-14

查询入职日期（hiredate）为1992–4-3的员工信息

select * from employees 
    where hiredate = str_to_date("4-3 1992",'%c-%d-%Y');

#### date\_format ####

将日期转化为字符

select date_format(now(),'%y年%m月%d日') as 日期     # 20年04月14日

查询有奖金（commission）的员工名（name）和入职日期（hirdate） （xx年/xx月/xx日）

select name,date_format(hirdate,'%年/%d月/%d日') 入职日期
    from employees
    whrere commission is not null;

#### datadiff ####

计算日期相差

查询员工表(employees)中的最大入职时间(hiredate)和最小入职时间的相差天数（aaa）

select datadiff(max(hiredate),min(hiredate)) aaa
    from employees

### 其他函数 ###

#### version ####

select version // 查看数据库版本

#### database ####

select database(); // 查看当前的数据库

#### user ####

// 查看用户
    select user();  //root@localhost

### 流程控制函数 ###

#### if函数 ####

select if(10>5,'大','小')   # 大

#### switch语句 ####

case 要判断的字段或者表达式
    when 常量1 then 要显示的值或语句1
    when 常量2 then 要显示的值或语句2
    ...
    else 要显示的值n或语句n              #相当于switch的default
    end;

查询员工的工资（salary）,要求

部门号===30,显示的工资为1.1倍

部门号===40,显示的工资为1.2倍

部门号===50,显示的工资为1.3倍

其他部门，显示的工资为原工资

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;

#### if-else语句 ####

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

查询员工的工资（salary）情况

如果工资>20000，显示A级别

如果工资>15000，显示B级别

如果工资>10000，显示C级别

否则显示D级别

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

## 多行函数 ##

又称分组函数,聚合函数，统计函数

### 基本 ###

以下的分组函数都忽略了null值

select sum(salary)和,avg(salary)平均,max(salary)最大,min(salary)最小,count(salary),个数 from employees

### 和distinct搭配 ###

select sum(distinct salary) from employees

### count ###

效率对比：

myisam存储引擎下，count(\*)的效率高

INNODB存储引擎下,count(\*)和count(1)的效率差不多,比count(字段)要高一些

### 和分组函数一同查询的字段要求 ###