在查詢中使用操作符和函數 · 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) ~~~