SQL基础之函数和谓词

本文主要是阅读《SQL 基础教程》这本书总结而来，有需求的话可以阅读原著。

---

1. 各种各样的函数

• 算术函数
• 字符串函数
• 日期函数
• 转换函数
• 聚合函数

# 创建数据库
mysql> create table SampleMath(
    -> m numeric (10, 3),
    -> n integer,
    -> p integer);
Query OK, 0 rows affected (0.01 sec)

# 数据库中的内容
mysql> select * from SampleMath;
+---------+------+------+
| m       | n    | p    |
+---------+------+------+
| 500.000 |    0 | NULL |
| 180.000 |    0 | NULL |
|    NULL | NULL | NULL |
|    NULL |    7 |    3 |
|    NULL |    5 |    2 |
|    NULL |    4 | NULL |
|   8.000 | NULL |    3 |
|   2.270 |    1 | NULL |
|   5.555 |    2 | NULL |
|    NULL |    1 | NULL |
|   8.760 | NULL | NULL |
+---------+------+------+
11 rows in set (0.00 sec)

---

1.1 算术函数

• + ==> 加法
• - ==> 减法
• * ==> 乘法
• / ==> 除法

---

• abs ==> 绝对值

# 格式
abs(数值)

# 实例
mysql> select m,
    -> abs(m) as abs_col
    -> from SampleMath;
+---------+---------+
| m       | abs_col |
+---------+---------+
| 500.000 | 500.000 |
| 180.000 | 180.000 |
|    NULL |    NULL |
|    NULL |    NULL |
|    NULL |    NULL |
|    NULL |    NULL |
|   8.000 |   8.000 |
|   2.270 |   2.270 |
|   5.555 |   5.555 |
|    NULL |    NULL |
|   8.760 |   8.760 |
+---------+---------+
11 rows in set (0.00 sec)

• mod ==> 求余

# 格式
mod(被除数，除数)

# 实例
mysql> select n,p,
    -> mod(n,p) as mod_col
    -> from SampleMath;
+------+------+---------+
| n    | p    | mod_col |
+------+------+---------+
|    0 | NULL |    NULL |
|    0 | NULL |    NULL |
| NULL | NULL |    NULL |
|    7 |    3 |       1 |
|    5 |    2 |       1 |
|    4 | NULL |    NULL |
| NULL |    3 |    NULL |
|    1 | NULL |    NULL |
|    2 | NULL |    NULL |
|    1 | NULL |    NULL |
| NULL | NULL |    NULL |
+------+------+---------+
11 rows in set (0.00 sec)

• round ==> 四舍五入

# 格式
round(对象数值，保留小数的位数)

# 实例
mysql> select m,n,
    -> round(m,n) as round_col
    -> from SampleMath;
+---------+------+-----------+
| m       | n    | round_col |
+---------+------+-----------+
| 500.000 |    0 |   500.000 |
| 180.000 |    0 |   180.000 |
|    NULL | NULL |      NULL |
|    NULL |    7 |      NULL |
|    NULL |    5 |      NULL |
|    NULL |    4 |      NULL |
|   8.000 | NULL |      NULL |
|   2.270 |    1 |     2.300 |
|   5.555 |    2 |     5.560 |
|    NULL |    1 |      NULL |
|   8.760 | NULL |      NULL |
+---------+------+-----------+
11 rows in set (0.00 sec)

---

1.2 字符串函数

# 创建数据库
mysql> create table SampleStr(
    -> str1 varchar(40),
    -> str2 varchar(40),
    -> str3 varchar(40));
Query OK, 0 rows affected (0.01 sec)

# 数据库中的内容
mysql> select * from SampleStr;
+-----------+--------+--------+
| str1      | str2   | str3   |
+-----------+--------+--------+
| opx       | rt     | NULL   |
| abc       | def    | NULL   |
| 山田    | 太郎 | 是我 |
| aaa       | NULL   | NULL   |
| NULL      | xyz    | NULL   |
| @!#$%     | NULL   | NULL   |
| ABC       | NULL   | NULL   |
| aBC       | NULL   | NULL   |
| abc太郎 | abc    | ABC    |
| bcdefabc  | abc    | ABC    |
| micmic    | i      | I      |
+-----------+--------+--------+
11 rows in set (0.00 sec)

---

• || ==> 拼接

# 语法
字符串1 || 字符串2

# 实例
# 因为各种数据库中的函数定义的方法也有所不同

# Mysql
mysql> select str1, str2,
    -> concat(str1, str2) as str_concat
    -> from SampleStr;
+-----------+--------+--------------+
| str1      | str2   | str_concat   |
+-----------+--------+--------------+
| opx       | rt     | opxrt        |
| abc       | def    | abcdef       |
| 山田    | 太郎 | 山田太郎 |
| aaa       | NULL   | NULL         |
| NULL      | xyz    | NULL         |
| @!#$%     | NULL   | NULL         |
| ABC       | NULL   | NULL         |
| aBC       | NULL   | NULL         |
| abc太郎 | abc    | abc太郎abc |
| bcdefabc  | abc    | bcdefabcabc  |
| micmic    | i      | micmici      |
+-----------+--------+--------------+
11 rows in set (0.00 sec)

# PostgreSQL/Oracle/DB2
mysql>
mysql> select str1, str2,
    -> str1 || str2 as str_concat
    -> from SampleStr;

# Mysql
mysql> select str1, str2, str3,
    -> concat(str1, str2, str3)
    -> from SampleStr;
+-----------+--------+--------+--------------------------+
| str1      | str2   | str3   | concat(str1, str2, str3) |
+-----------+--------+--------+--------------------------+
| opx       | rt     | NULL   | NULL                     |
| abc       | def    | NULL   | NULL                     |
| 山田    | 太郎 | 是我 | 山田太郎是我       |
| aaa       | NULL   | NULL   | NULL                     |
| NULL      | xyz    | NULL   | NULL                     |
| @!#$%     | NULL   | NULL   | NULL                     |
| ABC       | NULL   | NULL   | NULL                     |
| aBC       | NULL   | NULL   | NULL                     |
| abc太郎 | abc    | ABC    | abc太郎abcABC          |
| bcdefabc  | abc    | ABC    | bcdefabcabcABC           |
| micmic    | i      | I      | micmiciI                 |
+-----------+--------+--------+--------------------------+
11 rows in set (0.00 sec)

• length ==> 计算字符串长度

# 语法
length(字符串)

# 实例
mysql> select str1,
    -> length(str1) as len_str
    -> from SampleStr;
+-----------+---------+
| str1      | len_str |
+-----------+---------+
| opx       |       3 |
| abc       |       3 |
| 山田    |       6 |
| aaa       |       3 |
| NULL      |    NULL |
| @!#$%     |       5 |
| ABC       |       3 |
| aBC       |       3 |
| abc太郎 |       9 |
| bcdefabc  |       8 |
| micmic    |       6 |
+-----------+---------+
11 rows in set (0.00 sec)

• lower ==> 小写转换

# 语法
lower(字符串)

# 实例
mysql> select str1,
    -> lower(str1) as low_str
    -> from SampleStr;
+-----------+-----------+
| str1      | low_str   |
+-----------+-----------+
| opx       | opx       |
| abc       | abc       |
| 山田    | 山田    |
| aaa       | aaa       |
| NULL      | NULL      |
| @!#$%     | @!#$%     |
| ABC       | abc       |
| aBC       | abc       |
| abc太郎 | abc太郎 |
| bcdefabc  | bcdefabc  |
| micmic    | micmic    |
+-----------+-----------+
11 rows in set (0.00 sec)

# 添加where
mysql> select str1, lower(str1) as low_str from SampleStr
    -> where str1 in ('ABC', 'aBC', 'abc', '山田');
+--------+---------+
| str1   | low_str |
+--------+---------+
| abc    | abc     |
| 山田 | 山田  |
| ABC    | abc     |
| aBC    | abc     |
+--------+---------+
4 rows in set (0.00 sec)

• replace ==> 字符串的替换

# 语法
replace(对象字符串，替换前的字符串，替换后的字符串)

# 实例
mysql> select str1, str2, str3,
    -> replace(str1, str2, str3) as rep_str
    -> from SampleStr;
+-----------+--------+--------+-----------+
| str1      | str2   | str3   | rep_str   |
+-----------+--------+--------+-----------+
| opx       | rt     | NULL   | opx       |
| abc       | def    | NULL   | abc       |
| 山田    | 太郎 | 是我 | 山田    |
| aaa       | NULL   | NULL   | NULL      |
| NULL      | xyz    | NULL   | NULL      |
| @!#$%     | NULL   | NULL   | NULL      |
| ABC       | NULL   | NULL   | NULL      |
| aBC       | NULL   | NULL   | NULL      |
| abc太郎 | abc    | ABC    | ABC太郎 |
| bcdefabc  | abc    | ABC    | bcdefABC  |
| micmic    | i      | I      | mIcmIc    |
+-----------+--------+--------+-----------+
11 rows in set (0.00 sec)

• substring ==> 字符串截取

# 语法
substring(对象字符串 from 截取的初始位置 for 截取的字符数)

# 实例
# 因为在标识数据库的时候，没有设定语言，所以导致出现问题
mysql> select str1,
    -> substring(str1 from 3 for 2) as sub_str
    -> from SampleStr;
+-----------+---------+
| str1      | sub_str |
+-----------+---------+
| opx       | x       |
| abc       | c       |
| 山田    | ��      |
| aaa       | a       |
| NULL      | NULL    |
| @!#$%     | #$      |
| ABC       | C       |
| aBC       | C       |
| abc太郎 | c�      |
| bcdefabc  | de      |
| micmic    | cm      |
+-----------+---------+
11 rows in set (0.00 sec)

• upper ==> 大写转换

# 语法
upper(字符串)

# 实例
mysql> select str1,
    -> upper(str1) as up_str
    -> from SampleStr
    -> where str1 in ('ABC', 'aBC', 'abc', '山田');
+--------+--------+
| str1   | up_str |
+--------+--------+
| abc    | ABC    |
| 山田 | ű�ǔ� |
| ABC    | ABC    |
| aBC    | ABC    |
+--------+--------+
4 rows in set (0.01 sec)

---

1.3 日期函数

• current_date ==> 当前日期

# 语法
current_date

# 实例
mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2016-05-24   |
+--------------+
1 row in set (0.01 sec)

• current_time ==> 当前时间

# 语法
current_time

# 实例
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 14:24:04     |
+--------------+
1 row in set (0.00 sec)

• current_timestamp ==> 当前日期和时间

# 语法
current_timestamp

# 实例
mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-05-24 14:25:03 |
+---------------------+
1 row in set (0.00 sec)

• extract ==> 截止日期函数

# 语法
extract(日期元素 from 日期)

# 实例
mysql> 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 |
+---------------------+------+-------+------+------+--------+--------+
| 2016-05-24 14:30:43 | 2016 |     5 |   24 |   14 |     30 |     43 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)

---

1.4 转换函数

数据类型转换 - 数值转换

• cast ==> 类型转换

# 语法
cast(转化前的值 as 想要转换的数据类型)

# 实例
# 之后可以使用from <表名>
mysql> select cast('0001' as signed integer) as int_col;
+---------+
| int_col |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select cast('2009-12-14' as date) as date_col;
+------------+
| date_col   |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)

• coalesce ==> 将 null 转换为其他值
  • 该函数会返回可变参数中左侧开始第一个不是null的值

# 语法
coalesce(数据1，数据2，...)

# 实例
mysql> select coalesce(null, 1) as col1,
    -> coalesce(null, 'test', null) as col2,
    -> coalesce(null, null, '2009-09-09') as col3;
+------+------+------------+
| col1 | col2 | col3       |
+------+------+------------+
|    1 | test | 2009-09-09 |
+------+------+------------+
1 row in set (0.00 sec)

---

2. 谓词

• 返回值为真值的函数就是谓词
• 通常数来，返回值有可能是数字、字符串或者日期等等
• 真值包括true、false、unknown

# 创建数据库
mysql> create table SampleLike
    -> ( strcol varchar(6) not null,
    -> primary key (strcol));
Query OK, 0 rows affected (0.00 sec)

# 数据库内容
mysql> select * from SampleLike;
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| ddabc  |
| dddabc |
+--------+
5 rows in set (0.00 sec)

---

2.1 like ==> 字符串的部分一致查询

• 前方一致
• 中间一致
• 后方一致

# 实例 - 前方一致
# % 标识匹配零个字符以上的任何字符
# - 标识任意一个字符
mysql> select * from SampleLike
    -> where strcol like 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (0.00 sec)

mysql> select * from SampleLike where strcol like 'abc___';
+--------+
| strcol |
+--------+
| abcddd |
+--------+
1 row in set (0.00 sec)

mysql> select * from SampleLike where strcol like 'dd%';
+--------+
| strcol |
+--------+
| ddabc  |
| dddabc |
+--------+
2 rows in set (0.00 sec)

# 实例 - 中间一致
mysql> select * from SampleLike where strcol like '%ddd%';
+--------+
| strcol |
+--------+
| abcddd |
| dddabc |
+--------+
2 rows in set (0.00 sec)

# 实例 - 后方一致
mysql> select * from SampleLike where strcol like '%ddd';
+--------+
| strcol |
+--------+
| abcddd |
+--------+
1 row in set (0.00 sec)

---

2.2 betweent ==> 范围查询

• betweent匹配的时候，会将边界也算在内
• 如果不想使用边界中的值，可以使用<和>

mysql> select shohin_mei, hanbai_tanka
    -> from Shohin
    -> where hanbai_tanka between 100 and 1000;
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| T恤衫    |         1000 |
| 打孔器  |          500 |
| 叉子     |          500 |
| 切菜板  |          880 |
| 圆珠笔  |          100 |
+------------+--------------+
5 rows in set (0.00 sec)

mysql> select shohin_mei, hanbai_tanka
    -> from Shohin
    -> where hanbai_tanka > 100
    -> and hanbai_tanka < 1000;
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| 打孔器  |          500 |
| 叉子     |          500 |
| 切菜板  |          880 |
+------------+--------------+
3 rows in set (0.00 sec)

---

2.3 is null, is not null ==> 判断是否为 null

mysql> select shohin_mei, shiire_tanka
    -> from Shohin
    -> where shiire_tanka is null;
+------------+--------------+
| shohin_mei | shiire_tanka |
+------------+--------------+
| 叉子     |         NULL |
| 圆珠笔  |         NULL |
+------------+--------------+
2 rows in set (0.00 sec)

mysql> select shohin_mei, shiire_tanka
    -> from Shohin
    -> where shiire_tanka is not null;
+------------+--------------+
| shohin_mei | shiire_tanka |
+------------+--------------+
| T恤衫    |          500 |
| 打孔器  |          320 |
| 运动T恤 |         2800 |
| 菜刀     |         2800 |
| 高压锅  |         5000 |
| 切菜板  |          790 |
| 印章     |           10 |
+------------+--------------+
7 rows in set (0.00 sec)

---

2.4 in ==> or 的简便实用

• 使用in简化操作

mysql> select shohin_mei, shiire_tanka
    -> from Shohin
    -> where shiire_tanka = 320
    -> or shiire_tanka = 500
    -> or shiire_tanka = 5000;
+------------+--------------+
| shohin_mei | shiire_tanka |
+------------+--------------+
| T恤衫    |          500 |
| 打孔器  |          320 |
| 高压锅  |         5000 |
+------------+--------------+
3 rows in set (0.00 sec)

mysql> select shohin_mei, shiire_tanka
    -> from Shohin
    -> where shiire_tanka in (320, 500, 5000);
+------------+--------------+
| shohin_mei | shiire_tanka |
+------------+--------------+
| T恤衫    |          500 |
| 打孔器  |          320 |
| 高压锅  |         5000 |
+------------+--------------+
3 rows in set (0.00 sec)

---

2.5 使用子查询作为 in 谓语参数

# 创建数据库
mysql> create table TenpoShohin(
    -> tenpo_id char(4) not null,
    -> tenpo_mei varchar(200) not null,
    -> shohin_id char(4) not null,
    -> suiyo integer not null,
    -> primary key (tenpo_id, shohin_id));
Query OK, 0 rows affected (0.01 sec)

# 数据库内容
mysql> select * from TenpoShohin;
+----------+-----------+-----------+-------+
| tenpo_id | tenpo_mei | shohin_id | suiyo |
+----------+-----------+-----------+-------+
| 000A     | 东京    | 0001      |    30 |
| 000A     | 东京    | 0002      |    50 |
| 000A     | 东京    | 0003      |    15 |
| 000B     | 名古屋 | 0002      |    30 |
| 000B     | 名古屋 | 0003      |   120 |
| 000B     | 名古屋 | 0004      |    20 |
| 000B     | 名古屋 | 0006      |    10 |
| 000B     | 名古屋 | 0007      |    40 |
| 000C     | 大阪    | 0003      |    20 |
| 000C     | 大阪    | 0004      |    50 |
| 000C     | 大阪    | 0006      |    90 |
| 000C     | 大阪    | 0007      |    70 |
| 000D     | 福冈    | 0001      |   100 |
+----------+-----------+-----------+-------+
13 rows in set (0.00 sec)

• in和子查询
  • in谓语或者是not in谓语具有其他谓语所没有的使用方法，那就是可以使用子查询作为其参数来使用
  • 子查询就是 SQL 内部生成的表，所以可以说成是，能够将视图作为 in 的参数

mysql> select shohin_mei, hanbai_tanka
    -> from Shohin
    -> where shohin_id in (
    -> select shohin_id from TenpoShohin
    -> where tenpo_id = '000C');
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| 运动T恤 |         4000 |
| 菜刀     |         3000 |
| 叉子     |          500 |
| 切菜板  |          880 |
+------------+--------------+
4 rows in set (0.00 sec)

• not in和子查询

mysql> select shohin_mei, hanbai_tanka
    -> from Shohin
    -> where shohin_id not in (
    -> select shohin_id from TenpoShohin
    -> where tenpo_id = '000A');
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| 菜刀     |         3000 |
| 高压锅  |         6800 |
| 叉子     |          500 |
| 切菜板  |          880 |
| 圆珠笔  |          100 |
| 印章     |           95 |
+------------+--------------+
6 rows in set (0.00 sec)

---

2.6 exist谓语

• exist的使用方法与之前的都不相同
• 语法理解起来比较困难
• 实际上即使不使用exist，基本上也都可以使用in或者是not in来代替

exist 谓语的使用方式

• 作用就是，判断是否存在满足某种条件的记录
• exist的参数
  • 因为exist是只有一个参数的谓词
  • exist只需要在右侧书写一个参数

# 使用exist选取出‘大阪店’(000C)在销售品(shohin_id)的销售单价(hanbai_tanka)
mysql> select shohin_mei, hanbai_tanka
    -> from Shohin as S
    -> where exists (
    -> select * from TenpoShohin as TS
    -> where TS.tenpo_id = '000C'
    -> and TS.shohin_id = S.shohin_id);
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| 运动T恤 |         4000 |
| 菜刀     |         3000 |
| 叉子     |          500 |
| 切菜板  |          880 |
+------------+--------------+
4 rows in set (0.00 sec)

子查询中的select *

• exist只关心记录是否存在，所以返回哪些列都是没有关系
• exist用来判断是否存在满足子查询中where子句指定条件的记录，只有存在记录时才返回真的谓语

# 使用exist选取出‘大阪店’(000C)在销售品(shohin_id)的销售单价(hanbai_tanka)
mysql> select shohin_mei, hanbai_tanka
    -> from Shohin as S
    -> where exists (
    -> select 1 from TenpoShohin as TS
    -> where TS.tenpo_id = '000C'
    -> and TS.shohin_id = S.shohin_id);
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| 运动T恤 |         4000 |
| 菜刀     |         3000 |
| 叉子     |          500 |
| 切菜板  |          880 |
+------------+--------------+
4 rows in set (0.00 sec)

使用not exist替代not in

# 使用exist选取出非‘大阪店’(000C)在销售品(shohin_id)的销售单价(hanbai_tanka)
mysql> select shohin_mei, hanbai_tanka
    -> from Shohin as S
    -> where not exists (
    -> select 1 from TenpoShohin as TS
    -> where TS.tenpo_id = '000C'
    -> and TS.shohin_id = S.shohin_id);
+------------+--------------+
| shohin_mei | hanbai_tanka |
+------------+--------------+
| T恤衫    |         1000 |
| 打孔器  |          500 |
| 高压锅  |         6800 |
| 圆珠笔  |          100 |
| 印章     |           95 |
+------------+--------------+
5 rows in set (0.00 sec)

---

3. case 表达式

• case表达式分为简单case表达式和搜索case表达式
• 搜索case表达式包含简答case表达式的全部功能
• case表达式中的end不能省略
• 使用case表达式能够将select语句的结果进行组合

---

3.1 case 表达式的使用方式

# 什么是case表达式
case表达式是一种进行运算的功能

# 语法
case when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     ...
     else <表达式>
end

# 实例-Mysql
mysql> select shohin_mei
    -> case when shohin_bunrui = '衣服' then concat('A ', shohin_bunrui)
    -> when shohin_bunrui = '办公用品' then concat('B ', shohin_bunrui)
    -> when shohin_bunrui = '厨房用具' then concat('C ', shohin_bunrui)
    -> else null
    -> end as abc_shohin_bunrui
    -> from Shohin;

# 实例-ProgressSQL
mysql> select shohin_mei,
    -> case when shohin_bunrui = '衣服' then 'A ' || shohin_bunrui
    -> when shohin_bunrui = '办公用品' then 'B ' || shohin_bunrui
    -> when shohin_bunrui = '厨房用具' then 'C ' || shohin_bunrui
    -> else null
    -> end as abc_shohin_bunrui
    -> from Shohin;

---

3.2 case 表达式的书写位置

# 需要将下列数据纵向输出，group by只能横向输出
mysql> select shohin_bunrui,
    -> sum(hanbai_tanka) as sum_tanka
    -> from Shohin
    -> group by shohin_bunrui;
+---------------+-----------+
| shohin_bunrui | sum_tanka |
+---------------+-----------+
| 衣服        |      5000 |
| 办公用品  |       695 |
| 厨房用具  |     11180 |
+---------------+-----------+
3 rows in set (0.00 sec)

# 使用case表达式实现
mysql> select sum(case when shohin_bunrui = '衣服'
    -> then hanbai_tanka else 0 end) as sum_tanka_ihuku,
    -> sum(case when shohin_bunrui = '厨房用具'
    -> then hanbai_tanka else 0 end) as sum_tanka_kitchen,
    -> sum(case when shohin_bunrui = '办公用品'
    -> then hanbai_tanka else 0 end) as sum_tanka_jumu
    -> from Shohin;
+-----------------+-------------------+----------------+
| sum_tanka_ihuku | sum_tanka_kitchen | sum_tanka_jumu |
+-----------------+-------------------+----------------+
|            5000 |             11180 |            695 |
+-----------------+-------------------+----------------+
1 row in set (0.00 sec)