在查询中使用操作符和函数 · k8s原理与实践

[TOC] # 查询中使用算术操作符算术操作符包括：+(加)、-(减)、\*(乘)、/(除)、-(取反)、MOD(取模)。这些操作符可以作用在数值列上。如“从多个表里查询数据” 页面的示例查询出客户购买的每个商品的数量和价格，其中数量乘以价格就是每类商品的支付总额。所以 select\_list可以增加一列 t3.ol\_quantity \* t4.i\_price item\_sum\_price 。 ~~~ SELECT t1.c_first, t1.c_last, t1.c_credit, t2.o_ol_cnt, t2.o_entry_d, t3.ol_number, t3.ol_quantity, t4.i_name, t4.i_price, t3.ol_quantity * t4.i_price item_sum_price FROM cust t1 JOIN ordr t2 ON (t1.c_id=t2.o_id AND t1.c_w_id=t2.o_w_id AND t1.c_d_id=t2.o_d_id) JOIN ordl t3 ON (t2.o_id=t3.ol_o_id AND t2.o_w_id=t3.ol_w_id AND t2.o_d_id=t3.ol_d_id) JOIN item t4 ON (t4.i_id=t3.ol_i_id ) WHERE t1.c_w_id=2 AND t1.c_d_id=5 and t1.c_last LIKE 'CALLY%' ORDER BY t1.c_id, t2.o_id, t3.ol_number ; ~~~ 查询结果如下： ~~~ +------------------+----------------+----------+----------+------------+-----------+-------------+--------------------------+---------+----------------+ | c_first | c_last | c_credit | o_ol_cnt | o_entry_d | ol_number | ol_quantity | i_name | i_price | item_sum_price | +------------------+----------------+----------+----------+------------+-----------+-------------+--------------------------+---------+----------------+ | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 1 | 5 | FJT8fkxaUh2aUbI | 79.95 | 399.75 | | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 2 | 5 | kiMk43vd9HidvmwG8x | 58.59 | 292.95 | | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 3 | 5 | JnJEOLUCjunrKkt4Z1pL | 85.26 | 426.30 | | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 4 | 5 | CrFVAZW3OhyekdDNc2rPH | 22.30 | 111.50 | | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 5 | 5 | fJpsyG11EjWIceJWaB | 41.39 | 206.95 | | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 6 | 5 | shseF8WI1VSPbWfswSsIuNC | 30.04 | 150.20 | | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 7 | 5 | prjdpUDOxRvAn5WiMVoT85B1 | 18.55 | 92.75 | +------------------+----------------+----------+----------+------------+-----------+-------------+--------------------------+---------+----------------+ 7 rows in set (0.01 sec) ~~~ # 查询中使用数值函数常用数值函数有：sum(求和)、avg(求平均)、ceil(向上取整)、floor(向下取整)、trunc(数值取整)、round(n)(四舍五入保留n位小数)。如求历史表中每个仓库和区域的总销售额和平均每单销售额，SQL 如下： ~~~ SELECT h_w_id, h_d_id, sum(h_amount) sum_h_amount , avg(h_amount) avg_h_amount FROM hist GROUP BY h_w_id, h_d_id ; ~~~ 查询结果如下： ~~~ +--------+--------+--------------+--------------+ | h_w_id | h_d_id | sum_h_amount | avg_h_amount | +--------+--------+--------------+--------------+ | 1 | 1 | 120.00 | 10.000000 | | 1 | 2 | 120.00 | 10.000000 | | 1 | 3 | 120.00 | 10.000000 | | 1 | 4 | 120.00 | 10.000000 | | 1 | 5 | 120.00 | 10.000000 | | 1 | 6 | 120.00 | 10.000000 | | 1 | 7 | 120.00 | 10.000000 | | 1 | 8 | 120.00 | 10.000000 | | 1 | 9 | 120.00 | 10.000000 | | 1 | 10 | 120.00 | 10.000000 | | 2 | 1 | 120.00 | 10.000000 | | 2 | 2 | 120.00 | 10.000000 | | 2 | 3 | 120.00 | 10.000000 | | 2 | 4 | 120.00 | 10.000000 | | 2 | 5 | 120.00 | 10.000000 | | 2 | 6 | 120.00 | 10.000000 | | 2 | 7 | 120.00 | 10.000000 | | 2 | 8 | 120.00 | 10.000000 | | 2 | 9 | 120.00 | 10.000000 | | 2 | 10 | 120.00 | 10.000000 | +--------+--------+--------------+--------------+ 20 rows in set (0.01 sec) ~~~ # 查询中使用字符串连接符 MySQL 租户的字符串连接函数是 concat 、 concat\_ws， ’||’ 默认是表示逻辑运算符`或`。如查看 MySQL 租户下的客户姓名，SQL语句如下： ~~~ obclient> SELECT concat_ws(' ', c_first, c_last) full_name FROM cust ORDER BY c_last LIMIT 2; +---------------------------+ | full_name | +---------------------------+ | fvBZoeIV2uJh7 ABLEABLEESE | | dHmIgRV1IsC ABLEABLEOUGHT | +---------------------------+ 2 rows in set (0.01 sec) ~~~ 如果把 MySQL 租户下的变量 sql\_mode 值增加一个选项 PIPES\_AS\_CONCAT ，则 ’||’ 也会当作字符串连接符。SQL语句如下： ~~~ obclient> SET SESSION sql_mode='PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES'; obclient> SELECT c_first || ' ' || c_last full_name FROM cust ORDER BY c_last LIMIT 2; +---------------------------+ | full_name | +---------------------------+ | fvBZoeIV2uJh7 ABLEABLEESE | | dHmIgRV1IsC ABLEABLEOUGHT | +---------------------------+ 2 rows in set (0.01 sec) ~~~ # 查询中使用字符串函数常用的字符串函数有求字符串长度（length）、字符串截取（substr）、字符串拼接、字符串转大小写（upper lower）、字符串删除前后缀（ltrim rtrim trim）。需要注意的是，在 MySQL 租户里，字符串长度函数（length）长度单位是字节，char\_length 函数的字符串长度单位是字符。 ~~~ $obclient -h192.168.1.101 -utpcc@obmysql#obdemo -P2883 -p123456 -A tpccdb obclient> select length('中'), char_length('中'); +---------------+--------------------+ | length('中') | char_length('中') | +---------------+--------------------+ | 3 | 1 | +---------------+--------------------+ 1 row in set (0.00 sec) ~~~ # 查询中使用时间函数 MySQL 租户常用的时间类型有 date、timestamp、 time、datetime、year 等，更多时间类型用法，请参考《OceanBase SQL参考（MySQL模式）》。 MySQL 租户常用的取数据库时间函数是 now() ，curdate() 和 curtime() 。 * 示例：格式化时间显示 MySQL 租户调整时间类型显示的格式，可以用date\_format 函数，SQL 如下： ~~~ obclient> select now(), date_format(now(), "%Y/%m/%d %T") new_time ; +---------------------+---------------------+ | now() | new_time | +---------------------+---------------------+ | 2020-04-03 15:55:37 | 2020/04/03 15:55:37 | +---------------------+---------------------+ 1 row in set (0.00 sec) ~~~ * 示例：提取时间中的年/月/日/时/分/秒 MySQL 租户从时间中提取年/月/日/时/分/秒，可以用 extract 函数，SQL如下： ~~~ obclient> SET @dt = now(); obclient> SELECT @dt , extract(YEAR FROM @dt) d_year , extract(MONTH FROM @dt) d_month , extract(week FROM @dt) d_week , extract(DAY FROM @dt) d_day , extract(HOUR FROM @dt) d_hour , extract(MINUTE FROM @dt) d_min , extract(SECOND FROM @dt) d_second , extract(year_month FROM @dt) d_year_month , extract(hour_minute FROM @dt) d_hour_min \G *************************** 1. row *************************** @dt: 2020-03-27 18:00:52 d_year: 2020 d_month: 3 d_week: 12 d_day: 27 d_hour: 18 d_min: 0 d_second: 52 d_year_month: 202003 d_hour_min: 1800 1 row in set (0.00 sec) ~~~ * 示例：时间类型加减 MySQL 租户对时间进行加减，可以使用 date\_add 或 date\_sub 函数，SQL 如下： ~~~ obclient> SET @dt = now(); obclient> SELECT @dt , date_add(@dt, INTERVAL 1 DAY ) t1 , date_add(@dt, INTERVAL 1 HOUR ) t2 , date_add(@dt, INTERVAL -10 MINUTE ) t3 , date_add(@dt, INTERVAL -1 MONTH ) t4 , date_sub(@dt, INTERVAL 1 YEAR ) t5 \G *************************** 1. row *************************** @dt: 2020-03-27 18:03:44 t1: 2020-03-28 18:03:44 t2: 2020-03-27 19:03:44 t3: 2020-03-27 17:53:44 t4: 2020-02-27 18:03:44 t5: 2019-03-27 18:03:44 1 row in set (0.01 sec) ~~~ # 查询中使用类型转换函数类型转换函数可以将一种数据类型转换为另外一种数据类型，如数值类型和时间类型到字符串类型的相互转换。 * 示例：时间字符串转换为时间类型 MySQL 租户中，时间字符串可以直接复制给 date 类型，MySQL 可以自动转换为时间类型，另外也可以使用 convert 或 cast 函数做类型转换。SQL语句如下： ~~~ obclient> SELECT CONVERT('2020-02-02 14:30:45', date) t1 , CONVERT('2020-02-02 14:30:45', time) t2 , CONVERT('2020-02-02 14:30:45', datetime) t3 , CAST('2020-02-02 14:30:45' AS date) t4 , CAST('2020-02-02 14:30:45' AS time) t5 , CAST('2020-02-02 14:30:45' AS datetime) t6 \G *************************** 1. row *************************** t1: 2020-02-02 t2: 14:30:45 t3: 2020-02-02 14:30:45 t4: 2020-02-02 t5: 14:30:45 t6: 2020-02-02 14:30:45 1 row in set (0.00 sec) ~~~ MySQL 租户中时间类型转换为字符串类型，可以使用函数 date\_format。 * 示例：数值类型和字符串类型互相转换 MySQL 租户中，数值类型和字符串类型互相转换，可以用函数 convert 、cast。 ~~~ obclient> SELECT convert('3.1415926', decimal) n1 , cast('3.1415926' AS decimal) n2 , convert(3.1415926, char(10)) s1 , cast(3.1414926 AS char(10)) s2 ; +------+------+-----------+-----------+ | n1 | n2 | s1 | s2 | +------+------+-----------+-----------+ | 3 | 3 | 3.1415926 | 3.1414926 | +------+------+-----------+-----------+ 1 row in set (0.00 sec) ~~~ # 查询中使用聚合函数聚合函数扫描一组记录，然后返回单行记录。这组记录可以是一个表或者视图、或者一个子查询的结果。OceanBase 支持的聚合函数详情请参考手册《OceanBase SQL参考（MySQL模式）》。聚合函数通常跟 GROUP BY 子句一起使用，按照一个或多个列的值分组，然后每组返回单笔记录。 **示例：分组统计每个仓库的销售额** MySQL 租户中，聚合函数跟 GROUP BY 子句一起使用的时候，对 select\_list 里的列没有要求。这个可能会导致结果集很奇怪。如果要求 select\_list 里的列跟 GROUP BY 子句中的列保持一致，需要设置 MySQL 命令行下的 sql\_mode 为 ‘ONLY\_FULL\_GROUP\_BY’。SQL 查询如下： ~~~ obclient> SELECT ol_w_id , count(*) order_count , sum(ol_amount) sum_amount , round(avg(ol_amount),2) avg_amount , min(ol_amount) min_amount ,max(ol_amount) max_amount FROM ordl GROUP BY ol_w_id ORDER BY ol_w_id ; +---------+-------------+------------+------------+------------+------------+ | ol_w_id | order_count | sum_amount | avg_amount | min_amount | max_amount | +---------+-------------+------------+------------+------------+------------+ | 1 | 297 | 917174.33 | 3088.13 | 0.00 | 9876.11 | | 2 | 329 | 1153354.23 | 3505.64 | 0.00 | 9979.34 | +---------+-------------+------------+------------+------------+------------+ 2 rows in set (0.01 sec) obclient> SELECT ol_w_id, ol_d_id , count(*) order_count , sum(ol_amount) sum_amount , round(avg(ol_amount),2) avg_amount , min(ol_amount) min_amount , max(ol_amount) max_amount FROM ordl GROUP BY ol_w_id ORDER BY ol_w_id ; +---------+---------+-------------+------------+------------+------------+------------+ | ol_w_id | ol_d_id | order_count | sum_amount | avg_amount | min_amount | max_amount | +---------+---------+-------------+------------+------------+------------+------------+ | 1 | 1 | 297 | 917174.33 | 3088.13 | 0.00 | 9876.11 | | 2 | 1 | 329 | 1153354.23 | 3505.64 | 0.00 | 9979.34 | +---------+---------+-------------+------------+------------+------------+------------+ 2 rows in set (0.00 sec) obclient> show variables like '%sql_mode%'; +---------------+-------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------+ | sql_mode | PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES | +---------------+-------------------------------------------------------+ 1 row in set (0.00 sec) obclient> SET SESSION sql_mode='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) obclient> SELECT ol_w_id, ol_d_id , count(*) order_count , sum(ol_amount) sum_amount , round(avg(ol_amount),2) avg_amount , min(ol_amount) min_amount , max(ol_amount) max_amount FROM ordl GROUP BY ol_w_id ORDER BY ol_w_id ; ERROR 1055 (42000): 'tpccdb.ordl.ol_d_id' is not in GROUP BY obclient> ~~~ # 查询中使用 NULL 相关函数 NULL 相关的函数用于处理 NULL 值。NULL 值的特点是任何数值都不能等于 NULL 或不等于 NULL，可以通过 IS NULL 判断，也可以使用 NVL 函数将 NULL 值转换为可识别的字符串。下面示例如何识别和转换 NULL 值。 **示例：NULL 值转换** MySQL 租户中，如果一个列可能有 NULL 值，可以使用 NVL 或 IFNULL 函数探测并转换为特殊字符。SQL查询如下： ~~~ CREATE TABLE t_null(id number NOT NULL PRIMARY KEY, name varchar(10)); INSERT INTO t_null(id, name) values(1,'A'), (2,NULL), (3,'NULL'); SELECT id, name, nvl(name, 'NOT APPLICABLE') n_name, IFNULL(name, 'NOT APPLICABLE') n2_name FROM t_null; ~~~ 执行结果如下： ~~~ +----+------+----------------+----------------+ | id | name | n_name | n2_name | +----+------+----------------+----------------+ | 1 | A | A | A | | 2 | NULL | NOT APPLICABLE | NOT APPLICABLE | | 3 | NULL | NULL | NULL | +----+------+----------------+----------------+ 3 rows in set (0.01 sec) ~~~ # 查询中使用 CASE 函数 ASE 表达式可以实现类似“IF…ELSE…THEN”的逻辑而不用调用子程序。CASE 表达式有两种使用方法，简单的和带搜索条件的。 * 示例：在查询中使用简单的 CASE 表达式，将国家代码缩写翻译为全称。 ~~~ obclient> CREATE TABLE t_case(id number NOT NULL PRIMARY KEY, abbr varchar(5)); Query OK, 0 rows affected (0.08 sec) obclient> INSERT INTO t_case(id, abbr) VALUES (1,'US'),(2,'UK'),(3,'CN'),(4,'JP'); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 obclient> obclient> SELECT id, abbr, CASE abbr WHEN 'US' THEN 'America' WHEN 'UK' THEN 'English' WHEN 'CN' THEN 'China' ELSE 'UNKOWN' END full_name FROM t_case ; +----+------+-----------+ | id | abbr | full_name | +----+------+-----------+ | 1 | US | America | | 2 | UK | English | | 3 | CN | China | | 4 | JP | UNKOWN | +----+------+-----------+ 4 rows in set (0.00 sec) obclient> ~~~ * 示例：在查询中使用带搜索条件的 CASE 表达式 ~~~ obclient> DROP TABLE IF EXISTS t_case2; Query OK, 0 rows affected (0.02 sec) obclient> CREATE TABLE t_case2(id number NOT NULL PRIMARY KEY, c_date date ); Query OK, 0 rows affected (0.14 sec) obclient> INSERT INTO t_case2(id,c_date) VALUES (1,'2019-03-01') ,(2,'2019-05-08') ,(3,'2019-07-07') ,(4,'2019-10-11') ,(5,'2019-12-12') ,(6,'2020-01-05'); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 obclient> obclient> SELECT id, c_date, CASE WHEN datediff(now(), c_date) > 12*30 THEN 'More than one year ago' WHEN datediff(now(), c_date) > 9*30 THEN 'More than three quarters ago' WHEN datediff(now(), c_date) > 6*30 THEN 'More than half a year ago' WHEN datediff(now(), c_date) > 3*30 THEN 'More than a quarter ago' WHEN datediff(now(), c_date) >= 0 THEN 'Within a quarter' ELSE 'Illegal' END "Duration" FROM t_case2; +----+------------+------------------------------+ | id | c_date | Duration | +----+------------+------------------------------+ | 1 | 2019-03-01 | More than one year ago | | 2 | 2019-05-08 | More than three quarters ago | | 3 | 2019-07-07 | More than three quarters ago | | 4 | 2019-10-11 | More than a quarter ago | | 5 | 2019-12-12 | More than a quarter ago | | 6 | 2020-01-05 | Within a quarter | +----+------------+------------------------------+ 6 rows in set (0.01 sec) ~~~ # 锁定查询结果 SELECT FOR UPDATE OceanBase 支持 MVCC 特性，读是快照读，不阻塞写，是 SELECT 语句还有个特殊的用法可以阻塞写。示例如下： ~~~ obclient> select w_name, w_ytd, w_tax from ware where w_id=1 for update; +------------+---------+--------+ | w_name | w_ytd | w_tax | +------------+---------+--------+ | n1P4zYo8OH | 1200.00 | 0.1868 | +------------+---------+--------+ 1 row in set (0.01 sec) ~~~