# 函数、谓词

## 函数

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

**ABS(数)**，绝对值函数，对NULL数据操作返回NULL

**MOD(被除数,除数)**，求余

**ROUND(数,保留位数)**，四舍五入，

**str1||str2||str3...**，多个字符串拼接，oracle

**str1+str2+str3...**，多个字符串拼接，sql server

**CONCAT(str1,str2,str3...)**，多个字符串拼接，mysql，sql server2012及以后

**LENGTH(字符串)**，字符串长度

**LOWER(字符串)**，大写转小写

**UPPER**，小写转大写

**IN** 函数：

```
SELECT str1,LOWER(str1) AS low_str
 FROM SampleStr
WHERE str1 IN('ABC','aBC','abc')
```

**REPLACE(对象字符串,替换前的字符串,替换后的字符串)**，字符串的替换

**SUBSTRING(对象字符串 FROM 截取起始位 FOR 截取的字符数)**，PostgreSQL、Mysql

```
SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
 FROM SampleStr
```

**CURRENT\_DATE**，当前日期，返回SQL当前执行日期

**CURRENT\_TIME**，当前时间，返回SQL当前执行时间

**CURRENT\_TIMESTAMP**，当前日期和当前时间

**CAST**，类型转换

```
SELECT CAST('0001' AS INTEGER) AS int_col; #sql server
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col; # mysql
```

**COALESCE**，将NULL值转换为其它值，返回从左开始第一个不是NULL的值

```
COALESCE(数据1,数据2,数据3......)

mysql> SELECT COALESCE(NULL,1) AS col_1, 
                COALESCE(NULL,'test',NULL) AS col_2, 
                COALESCE(NULL,NULL,'2009-11-01') AS col_3;

+-------+-------+------------+
| col_1 | col_2 | col_3      |
+-------+-------+------------+
|     1 | test  | 2009-11-01 |
+-------+-------+------------+
1 row in set (0.00 sec)


# 把包含NULL的替换为NULL字符串
SELECT COALESCE(str2,'NULL')
 FROM SampleStr;
```

## 谓词

* LIKE：模糊查询

  ```
  # 查询ddd开头的
  # %代表0字符以上的任意字符，任意多个
  SELECT *
   FROM SampleStr
  WHERE strcol LIKE 'ddd%';

  # _ 下划线，表示任意一个字符，abc__匹配abcdd、abcsa...
  SELECT *
   FROM SampleStr
  WHERE strcol LIKE 'abc__';
  ```
* BETWEEN：范围查询，`WHERE sale_price BETWEEN 100 AND 1000`;会包含100和1000临界值
* IS NULL、IS NOT NULL：专门用来判断NULL的
* IN、NOT IN 上面用了

混合使用IN，查询Product中product\_id在ShopProduct表中满足shop\_id = '000C'的product\_id的Product中的product\_name,sale\_price数据:

```
SELECT product_name,sale_price
 FROM Product
WHERE product_id IN(SELECT product_id
                     FROM ShopProduct
                    WHERE shop_id = '000C'
);
```

* EXISTS：是否满足存在，和 IN可相互替换，NOT EXISTS 类似NOT IN

```
SELECT product_name,sale_price
 FROM Product AS P
WHERE EXISTS(SELECT *
             FROM ShopProduct AS SP
             WHERE SP.shop_id='000C'
             AND SP.product_id = P.product_id
);
```

* CASE：

```
CASE WHEN <匹配条件> THEN <返回值处理表达式>
         WHEN <匹配条件> THEN <返回值处理表达式>
         WHEN <匹配条件> THEN <返回值处理表达式>
                 .....
         ELSE <执行表达式>
END

# 例子，查询product_name和product_type（product_type执行各种CASE判断，转换，变成abc_product_type）
SELECT product_name,
     CASE WHEN product_type = '衣服' THEN CONCAT('A:',product_type)
              WHEN product_type = '办公用品' THEN CONCAT('B:',product_type)
              WHEN product_type = '厨房用品' THEN CONCAT('C:',product_type)
              ELSE NULL
     END AS abc_product_type
FROM Product;

# 例
mysql> SELECT product_name, CASE WHEN product_type = '衣服' THEN CONCAT('A:',product_type)  
    -> WHEN product_type = '办公用品' THEN CONCAT('B:',product_type)
    -> WHEN product_type = '厨房用品' THEN CONCAT('C:',product_type)
    -> ELSE NULL
    -> END AS abc_product_type
    -> FROM Product;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤          | A:衣服           |
| 打孔器       | B:办公用品       |
| 运动T恤      | A:衣服           |
| 菜刀         | NULL             |
| 高压锅       | NULL             |
| 叉子         | NULL             |
| 擦菜板       | NULL             |
| 圆珠笔       | B:办公用品       |
| T恤          | A:衣服           |
+--------------+------------------+
9 rows in set (0.00 sec)
```

CASE技巧，查询不同类型物品各自总价：

```
mysql> SELECT product_type,SUM(sale_price) AS sum_price FROM Product GROUP BY product_type;
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服         |      6200 |
| 办公用品     |       600 |
| 厨房用具     |     11180 |
+--------------+-----------+
3 rows in set (0.00 sec)

# 但是这样是行排的，现在希望列排，1行输出？

mysql> SELECT 
SUM(CASE WHEN product_type='衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes, 
SUM(CASE WHEN product_type='厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen, SUM(CASE WHEN product_type='办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
|              6200 |             11180 |              600 |
+-------------------+-------------------+------------------+
1 row in set (0.00 sec)
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/shu-ju-ku/mysql/han-shu-wei-ci.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.