sql 5 函数

sql 5 函数

函数

sql也有自带的函数来处理各种各样的情况。大致可以分为 算术函数，字符串函数，日期函数，转换函数（转换数据类型和值）和聚合函数。

算术函数

算术函数 含义
abs(值) 求绝对值
mod(被除数,除数) 求余 可以使用%来计算余数
round(对象数值,保留的位数) 四舍五入

求m字段的绝对值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select m,abs(m) from samplemath;

    m     |   abs
----------+---------
  500.000 | 500.000
-180.000 | 180.000
          |
          |
          |
          |
    8.000 |   8.000
    2.270 |   2.270
    5.555 |   5.555
          |
    8.760 |   8.760

求出n和p字段的余数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select n, p, mod(n,p) from samplemath;
或者
select n, p, n%p as mod from samplemath;

  n   |  p   | mod
------+------+------
    0 |      |
    0 |      |
      |      |
    7 |    3 |    1
    5 |    2 |    1
    4 |      |
      |    3 |
    1 |      |
    2 |      |
    1 |      |
      |      |

m字段保留一位小数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select m,round(m,1) from samplemath;

    m     | round
----------+--------
  500.000 |  500.0
-180.000 | -180.0
          |
          |
          |
          |
    8.000 |    8.0
    2.270 |    2.3
    5.555 |    5.6
          |
    8.760 |    8.8

字符串函数

字符串函数 含义
字符串1 || 字符串2 拼接字符串 （Oracle，DB2，PostgreSQL）
length(字符串) 求字符串长度
lower(字符串) 小写转换 只针对字母
upper(字符串) 大写转换 只针对字母
replace(字符串,替换前字符串,替换后字符串) 字符串替换
substring(字符串 from 截取起始位置 for 截取字符数) 字符串截取 （postgresql/MySQL专用）

拼接字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select str1,str2,str1||str2 as str_concat from SampleStr;

   str1    | str2 |  str_concat
-----------+------+--------------
opx       | rt   | opxrt
abc       | def  | abcdef
山田      | 太郎 | 山田太郎
aaa       |      |
          | xyz  |
@!#$%     |      |
ABC       |      |
aBC       |      |
abc太郎   | abc  | abc太郎abc
abcdefabc | abc  | abcdefabcabc
micmic    | i    | micmici

注意：值是null时 拼接字符串后得null

1
2
3
4
5
select str1,str2,str3,str1||str2||str3 as str_concat from SampleStr where str1='山田';

str1 | str2 | str3 |  str_concat
------+------+------+--------------
山田 | 太郎 | 是我 | 山田太郎是我

concat(字符串…) （MySQL专用）

1
select concat(str1,str2,str3) as str_concat from SampleStr where str1='山田';

求字符串长度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select str1,length(str1) from SampleStr;

  str1    | length
-----------+--------
opx       |      3
abc       |      3
山田      |      2
aaa       |      3
          |
@!#$%     |      5
ABC       |      3
aBC       |      3
abc太郎   |      5
abcdefabc |      9
micmic    |      6

改小写

1
2
3
4
5
6
7
8
9
select str1,lower(str1) from  SampleStr where str1 in ('abc','ABC','aBC','山田');

str1 | lower
------+-------
abc  | abc
山田 | 山田
ABC  | abc
aBC  | abc
(4 rows)

改大写

1
2
3
4
5
6
7
8
select str1,upper(str1) from  SampleStr where str1 in ('abc','ABC','aBC','山田');

str1 | upper
------+-------
abc  | ABC
山田 | 山田
ABC  | ABC
aBC  | ABC

替换字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select str1,str2,str3,replace(str1,str2,str3) as re_str from SampleStr;

   str1   | str2 | str3 |  re_str
-----------+------+------+-----------
opx       | rt   |      |
abc       | def  |      |
山田      | 太郎 | 是我 | 山田
aaa       |      |      |
          | xyz  |      |
@!#$%     |      |      |
ABC       |      |      |
aBC       |      |      |
abc太郎   | abc  | ABC  | ABC太郎
abcdefabc | abc  | ABC  | ABCdefABC
micmic    | i    | I    | mIcmIc

截取字符串的第三和第四位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select str1, substring(str1 from 3 for 2) as sub_str from SampleStr;

  str1    | sub_str
-----------+---------
opx       | x
abc       | c
山田      |
aaa       | a
          |
@!#$%     | #$
ABC       | C
aBC       | C
abc太郎   | c太
abcdefabc | cd
micmic    | cm

substring(字符串 , 截取起始位置 , 截取字符数) （sqlserver专用）

1
select str1, substring(str1 , 3 , 2) as sub_str from SampleStr;

substr(字符串 , 截取起始位置 , 截取字符数) （Oracle/db2专用）

1
select str1, substr(str1 , 3 , 2) as sub_str from SampleStr;

日期函数

日期函数 含义
current_date 当前日期
current_time 当前时间
current_timestamp 当前日期和时间
extract (日期元素 from 日期) 截取日期元素 返回值是数字类型

取得当前日期

1
2
3
4
5
select current_date;

current_date
--------------
2019-02-21

取得当前时间

1
2
3
4
5
select current_time;

    current_time
--------------------
22:23:02.913548+08

取得当前日期和时间

1
2
3
4
5
select current_timestamp;

       current_timestamp
-------------------------------
2019-02-21 22:25:27.130797+08

查看日期元素

1
2
3
4
5
6
7
8
9
10
11
select current_timestamp,
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;

       current_timestamp       | year | month | day | hour | minute |  second
-------------------------------+------+-------+-----+------+--------+----------
2019-02-21 22:36:05.566313+08 | 2019 |     2 |  21 |   22 |     36 | 5.566313

转换字符

转换在sql有两层意思1）数值类型的转换，英语交cast 2）数值的转换

转换函数 含义
cast( 转换前的值 as 想要转换的数据类型 ) 类型转换
coalesce(字段/参数1,…) 把null转换为其他值 转换为参数中左边开始第一个非null的值

字符串类型转整型

1
2
3
4
5
select cast('0001' as integer) as int_col;

int_col
---------
       1

字符串类型转日期类型

1
2
3
4
5
select cast('2019-02-21' as date) as date_col;

  date_col
------------
2019-02-21

把null转换为其他值

1
2
3
4
5
select coalesce(null,1) as col_1,coalesce(null,'test',null) as col_2,coalesce(null,null,'2019-02-21') as col_3;

col_1 | col_2 |   col_3
-------+-------+------------
     1 | test  | 2019-02-21

把str2字段中null转换’null’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select coalesce(str2, 'null') from samplestr;

coalesce
----------
rt
def
太郎
null
xyz
null
null
null
abc
abc
i

谓词

满足特点条件的的函数，其条件就是返回值为真值（true/false/unkown）。

like谓词

字符串部分一致的查询。部分一致分为前方一致，前方一致和后方一致。=就是字符串完全一致。

前方一致：选取查询条件字符串和查询对象字符串开始部门相同的记录的方法
中间一致：选取含有查询条件字符串的记录的方法
后方一致：选取查询条件字符串和查询对象字符串尾部部门相同的记录的方法

%表示任意字符串
_表示任意一个字符

前方一致查询

1
2
3
4
5
select * from SampleLike where strcol like 'ddd%'; 

strcol
--------
dddabc

中间一致查询

1
2
3
4
5
6
7
select * from SampleLike where strcol like '%ddd%';  #只有含有ddd的字符串都会符合

strcol
--------
abcddd
dddabc
abdddc

后方一致查询

1
2
3
4
5
select * from SampleLike where strcol like '%ddd';

strcol
--------
abcddd

查询中abc+任意两个字符

1
2
3
4
5
select * from SampleLike where strcol like 'abc__';

strcol
--------
abcdd

between谓词

范围查询。数值包括两个临界值

查询hanbai_tabka在100至2000的记录

1
2
3
4
5
6
7
select shohin_mei,hanbai_tabka from t2 where hanbai_tabka between 100 and 2000;

shohin_mei | hanbai_tabka
------------+--------------
打孔器     |          500
T恤        |         2000
叉子       |          500

is null/is not null

判断是否为null

查询shiire_tanka是null的记录

1
2
3
4
5
6
7
select shohin_mei,shiire_tanka from t2 where shiire_tanka is null;

shohin_mei | shiire_tanka
------------+--------------
叉子       |
运动服     |
(2 rows)

查询shiire_tanka不是null的记录

1
2
3
4
5
6
7
8
select shohin_mei,shiire_tanka from t2 where shiire_tanka is not null;

shohin_mei | shiire_tanka
------------+--------------
打孔器     |          320
T恤        |          500
擦菜板     |          395
菜刀       |         1400

in谓词

指定多个查询记录的值 代替or的。

查询出hanbai_tabka是1000，2000，4000的记录

1
2
3
4
5
6
select shohin_mei,hanbai_tabka from t2 where hanbai_tabka in(1000,2000,4000);

shohin_mei | hanbai_tabka
------------+--------------
T恤        |         2000
运动服     |         4000

查询出hanbai_tabka非1000，2000，4000的记录

1
2
3
4
5
6
7
8
select shohin_mei,hanbai_tabka from t2 where hanbai_tabka not in(1000,2000,4000);

shohin_mei | hanbai_tabka
------------+--------------
打孔器     |          500
叉子       |          500
擦菜板     |            0
菜刀       |         2990

子查询作为in的参数

1
select product_name, sale_price from Product where product_id in (select product_id from ShopProduct where shop_id = '000C');

上面sql语句执行分两步
1）从ShopProduct表中查询出shop_id = ‘000C’得销售商品product_id 2）再从Product表中选取出上一步的product_id对应的销售单价sale_price

exist谓词

exist是判断是否存在条件的记录。它右边通常是一个关联子查询作为参数 查询中通常使用select *

1
select product_name, sale_price from Product as P exist (select * from ShopProduct as SP where shop_id = '000C' and SP.product_id = P.product_id);

exist只会判断where子句中指定的条件shop_id 是000C 商品表Product和商店商品表ShopProduct中商品编号product_id相同的记录，满足就返回true。

查询不存在shop_id是000A,商品表Product和商店商品表ShopProduct中商品编号product_id相同的记录

1
select product_name, sale_price from Product as P not exist (select * from ShopProduct as SP where shop_id = '000A' and SP.product_id = P.product_id);

case表达式

case表示式是函数的一种。在区分情况时使用，通常称为条件（分支）。类似编程语言中的if语句，case语句。
case表示式语法分为简单case表示式和搜索case表示式。搜索case表示式包含简单case表示式的全部功能，因此case表示式语法使用搜索case表示式。case表达式多用于对查询结果进行编辑处理

搜索case表示式

语法格式

1
2
3
4
5
case when <求值表达式> then <表达式>
         when <求值表达式> then <表达式>
        ...
        else <表达式>
end

流程 逐个判断每个when的求值表达式（列=值）为真，执行then后的表达式，为假就执行下一个when子句 ….，最后返回一个值。

把商品类型以A:办公用品,B:衣服,C:厨房用具显示出来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select shohin_mei, 
case when shobin_bunrui = '办公用品' then 'A:'||shobin_bunrui 
when shobin_bunrui = '衣服' then 'B:'||shobin_bunrui 
when shobin_bunrui = '厨房用具' then 'C:'||shobin_bunrui 
else null 
end as product_type 
from t2;

shohin_mei | product_type
------------+--------------
打孔器     | A:办公用品
T恤        | B:衣服
叉子       | C:厨房用具
擦菜板     | C:厨房用具
菜刀       | C:厨房用具
运动服     | B:衣服

统计各商品类并以列显示出来

1
2
3
4
5
6
7
8
9
select 
sum(case when shobin_bunrui='办公用品' then hanbai_tabka else 0 end)as sum_price_office,
sum(case when shobin_bunrui='厨房用具' then hanbai_tabka else 0 end)as sum_price_kitchen,
sum(case when shobin_bunrui='衣服' then hanbai_tabka else 0 end)as sum_price_clothes 
from t2;

sum_price_office | sum_price_kitchen | sum_price_clothes
------------------+-------------------+-------------------
              500 |              3490 |              6000

简单case表示式

语法格式

1
2
3
4
5
6
case <表达式>
        when <表达式> then <表达式>
         when <表达式> then <表达式>
        ...
        else <表达式>
end

把商品类型以A:办公用品,B:衣服,C:厨房用具显示出来

1
2
3
4
5
6
7
8
select shohin_mei, 
case shobin_bunrui
when '办公用品' then 'A:'||shobin_bunrui 
when  '衣服' then 'B:'||shobin_bunrui 
when '厨房用具' then 'C:'||shobin_bunrui 
else null 
end as product_type 
from t2;

注意：简单case表示式在when中不能指定不同的列

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

欢迎光临<br><br>本博客是用来记录学习Linux Python Shell和Perl<br>谢谢大家