cubrid的中sql查詢語法 SELECT [ ] [{TO | INTO} ] [FROM ] [WHERE ] [GROUP BY {col_name | expr} [ASC | DESC], ...[WITH ROLLUP]] [HAVING ] [ORDER BY {col_name |
cubrid的中sql查詢語法
SELECT [
[{TO | INTO}
[WHERE
[HAVING
[ORDER BY {col_name | expr} [ASC | DESC], ... [NULLS {FIRST | LAST}][LIMIT [offset,] row_count]
[USING INDEX { index_name [,index_name, ...] | NONE }][FOR UPDATE [OF
<qualifier> ::= ALL | DISTINCT | DISTINCTROW | UNIQUE
<select_expressions> ::= * | <expression_comma_list> | *, <expression_comma_list>
<variable_comma_list> ::= [:] identifier, [:] identifier, ...
<extended_table_specification_comma_list> ::=
<table_specification> [
{, <table_specification> } ... |
<join_table_specification> ... |
<join_table_specification2> ...
]
TABLE (
ALL
[INNER | {LEFT | RIGHT} [OUTER]] JOIN
- 還是看例子吧
distinct 去重
SELECT DISTINCT host_nation
FROM olympic;
分頁
SELECT host_year as col1, host_nation as col2
FROM olympic
ORDER BY col2 LIMIT 5;
SELECT CONCAT(host_nation, ', ', host_city) AS host_place
FROM olympic
ORDER BY host_place LIMIT 5;
還可以
SELECT 1+1 AS sum_value;
其實就類似子查詢
SELECT SUM (n)
FROM (SELECT gold FROM participant WHERE nation_code = 'KOR'
UNION ALL
SELECT silver FROM participant WHERE nation_code = 'JPN') AS t(n);
換個姿勢
SELECT nation_code, host_year, gold
FROM participant p
WHERE gold > (SELECT AVG(s)
FROM (SELECT silver + bronze
FROM participant
WHERE nation_code = p.nation_code
AND silver > 0
AND bronze > 0)
AS t(s));
where條件
WHERE
<search_condition> ::=
<comparison_predicate>
<between_predicate>
<exists_predicate>
<in_predicate>
<null_predicate>
<like_predicate>
<quantified_predicate>
<set_predicate>
比較運算
= <> != > < >= <=
成立是1 不成立是0
any some all運算
any 任一個條件符合 some 是一部分(文檔暫缺)
SELECT * FROM condition_tbl WHERE dept_name = ANY{'devel','sales'};
all 條件全部符合
SELECT * FROM condition_tbl WHERE salary > ALL{3000000, 4000000, NULL};
SELECT * FROM condition_tbl WHERE ((0.9 * salary) < ALL (SELECT salary FROM condition_tbl
WHERE dept_name = 'devel')
);
between 運算符
SELECT * FROM condition_tbl WHERE salary BETWEEN 3000000 AND 4000000;
SELECT * FROM condition_tbl WHERE (salary >= 3000000) AND (salary <= 4000000);
SELECT * FROM condition_tbl WHERE salary NOT BETWEEN 3000000 AND 4000000;
SELECT * FROM condition_tbl WHERE name BETWEEN 'A' AND 'E';
exists 運算符
SELECT 'raise' FROM db_root WHERE EXISTS(
SELECT * FROM condition_tbl WHERE salary < 2500000);
SELECT 'raise' FROM db_root WHERE NOT EXISTS(
SELECT * FROM condition_tbl WHERE salary < 2500000);
in 運算符
SELECT * FROM condition_tbl WHERE dept_name IN {'devel','sales'};
SELECT * FROM condition_tbl WHERE dept_name = ANY{'devel','sales'};
SELECT * FROM condition_tbl WHERE dept_name NOT IN {'devel','sales'};
isnull
SELECT * FROM condition_tbl WHERE salary IS NULL;
SELECT * FROM condition_tbl WHERE salary IS NOT NULL;
SELECT * FROM condition_tbl WHERE salary = NULL;
like 運算符
轉義用\
SELECT * FROM condition_tbl WHERE name LIKE '%s%';
SELECT * FROM condition_tbl WHERE UPPER(name) LIKE '_O%';
SELECT * FROM condition_tbl WHERE name LIKE '___';
REGEXP, RLIKE 運算符
這個例子用的少,轉碼比較
SELECT ('a' collate utf8_en_ci REGEXP BINARY 'A' collate utf8_en_ci);
0
SELECT ('a' collate utf8_en_cs REGEXP BINARY 'A' collate utf8_en_cs);
0
SELECT ('a' COLLATE iso88591_bin REGEXP 'A' COLLATE iso88591_bin);
1
SELECT ('a' COLLATE iso88591_bin REGEXP BINARY 'A' COLLATE iso88591_bin);
0
主要看這個
支持正則表達式嘍
SELECT name FROM athlete where name REGEXP '^[a-d]';
下麵更像是演示正則表達式
匹配特殊的字元
SELECT ('new\nline' REGEXP 'new
line');
SELECT ('cubrid dbms' REGEXP '^cub');
SELECT ('this is cubrid dbms' REGEXP 'dbms$');
SELECT ('cubrid dbms' REGEXP '^c.*$');
SELECT ('Aaaapricot' REGEXP '^A+pricot');
SELECT ('Apricot' REGEXP '^Aa?pricot');
SELECT ('Aapricot' REGEXP '^Aa?pricot');
('Aapricot' regexp '^Aa?pricot')
1 代表成立
SELECT ('Aaapricot' REGEXP '^Aa?pricot');
('Aaapricot' regexp '^Aa?pricot')
0
-- (cub)* : match zero or more instances of the sequence abc.
SELECT ('cubcub' REGEXP '^(cub)*$');
('cubcub' regexp '^(cub)*$')
1
-- [a-dX], [^a-dX] : matches any character that is (or is not, if ^ is used) either a, b, c, d or X.
SELECT ('aXbc' REGEXP '^[a-dXYZ]+');
('aXbc' regexp '^[a-dXYZ]+')
1
SELECT ('strike' REGEXP '^[^a-dXYZ]+$');
('strike' regexp '^[^a-dXYZ]+$')
1
case 運算符
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM case_tbl;
結果
a case when a=1 then 'one' when a=2 then 'two' else 'other' end
1 'one'
2 'two'
3 'other'
NULL 'other'
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
ENDFROM case_tbl;
SELECT a,
CASE WHEN a=1 THEN 1
WHEN a=2 THEN 1.2345
ELSE 1.234567890
END
FROM case_tbl;
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 1.2345
END
FROM case_tbl; //報錯,類型轉換錯誤
COALESCE 函數
對null處理的函數
類似
CASE WHEN a IS NOT NULL
THEN a
ELSE b
END
當a的值為null的時候 返回10.0000
SELECT a, COALESCE(a, 10.0000) FROM case_tbl;
decode 函數
類似
CASE WHEN a = b THEN c
WHEN a = d THEN e
ELSE f
END
參數有三個,
第一個 可以是表達式要或者比較的值 如下例的a
第二個 結果 如下例的1 ,2
第三個 預設值 如other
a 列
1
2
3
NULL
執行
SELECT a, DECODE(a, 1, 'one', 2, 'two', 'other') FROM case_tbl;
結果
1 'one'
2 'two'
3 'other'
NULL 'other'
a為1 等於one a為2 等於two a為3和null ,沒有符合條件的項,使用預設值other
SELECT a, DECODE(a, 1, 1, 2, 1.2345, 1.234567890) FROM case_tbl;
註意以下的結果,數字都是單精度的.隱式轉換了. 以預設結果的類型為準.
1 1.000000000
2 1.234500000
3 1.234567890
NULL 1.234567890
下麵的這個會失敗,因為one不能轉為 double
SELECT a, DECODE(a, 1, 'one', 2, 'two', 1.2345) FROM case_tbl;
GREATEST 函數
獲取最大的值
SELECT gold, silver , bronze, GREATEST (gold, silver, bronze)
FROM participant
WHERE nation_code = 'KOR';
結果是三個參數列每行值中的的最大值
gold silver bronze greatest(gold, silver, bronze)
9 12 9 12
8 10 10 10
7 15 5 15
12 5 12 12
12 10 11 12
c#,net,cubrid,教程,學習,筆記歡迎轉載 ，轉載時請保留作者信息。本文版權歸本人所有，如有任何問題，請與我聯繫[email protected] 。 過錯 if函數
三個參數,
第一個表達式返回真假
第二個表示真的時候返回的值
第三個表示假的時候返回的值
類似
CASE WHEN a IS TRUE THEN b
ELSE c
END
SELECT a, IF(a=1, 'one', 'other') FROM case_tbl;
IFNULL NVL函數
給null的列設預設值,註意返回的類型,取決於預設值的類型.ifnull和Nvl類似.但是Nvl支持集合類型.
類似
CASE WHEN a IS NULL THEN b
ELSE a
END
例如SELECT a, IFNULL(a, 'UNKNOWN') FROM case_tbl;
結果
1 '1'
2 '2'
3 '3'
NULL 'UNKNOWN'
ISNULL函數 返回整數 1或者0
LEAST函數
參數可以是多個表達式,返回最小的值, 參數是null則返回null
SELECT gold, silver , bronze, LEAST(gold, silver, bronze) FROM participant
WHERE nation_code = 'KOR';
結果
gold silver bronze least(gold, silver, bronze)
9 12 9 9
8 10 10 8
7 15 5 5
12 5 12 5
12 10 11 10NULLIF 函數
相等返回null 否則原樣返回
類似CASE
WHEN a = b THEN NULL
ELSE a
END
SELECT a, NULLIF(a, 1) FROM case_tbl;
結果 a是1的時候返回null
a nullif(a, 1)
1 NULL
2 2
3 3
NULL NULL
NVL2(expr1, expr2, expr3) 函數
第一個表達式不等於null的是否返回第二個表達式,等於null返回第三個表達式
SELECT a, NVL2(a, a+1, 10.5678) FROM case_tbl;
a nvl2(a, a+1, 10.5678)
1 2
2 3
3 4
NULL 11
