来自 首页 2019-12-07 06:18 的文章
当前位置: 澳门太阳娱乐手机登录 > 首页 > 正文

Server函数之空值处理,CUBRID学习笔记

coalesce( expression [ ,...n ] )再次来到其参数中第三个非空表明式。

cubrid的中sql查询语法


SELECT [ ] <select_expressions> [{TO | INTO} <variable_comma_list>] [FROM <extended_table_specification_comma_list>] [WHERE <search_condition>] [GROUP BY {col_name | expr} [ASC | DESC], ...[WITH ROLLUP]] [HAVING <search_condition> ] [ORDER BY {col_name | expr} [ASC | DESC], ... [NULLS {FIRST | LAST}] [LIMIT [offset,] row_count] [USING INDEX { index_name [,index_name, ...] | NONE }] [FOR UPDATE [OF <spec_name_comma_list>]]

<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_specification> ::= <single_table_spec> [] [WITH (<lock_hint>)] | <metaclass_specification> [ ] | | TABLE ( )

::= [AS] [(<identifier_comma_list>)]

<single_table_spec> ::= [ONLY] <table_name> | ALL <table_name> [ EXCEPT <table_name> ]

<metaclass_specification> ::= CLASS <class_name>

<join_table_specification> ::= [INNER | {LEFT | RIGHT} [OUTER]] JOIN <table_specification> ON <search_condition>

<join_table_specification2> ::= CROSS JOIN <table_specification>

<lock_hint> ::= READ UNCOMMITTED

  • 大概看例子吗

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>

<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, LacrosseLIKE 运算符 那一个例子用的少,转码相比 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 ('newnline' 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'
   END

FROM 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,教程,学习,笔记欢迎转载 ,转载时请保留作者信息。本文版权归本人所有,如有任何问题,请与我联系wang2650@sohu.com 。 过错  

if函数 四个参数, 第2个表明式重返真假 第三个代表确实时候回来的值 第多少个象征假的时候回来的值

类似 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                           10

NULLIF 函数 相等再次来到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卡塔尔(قطر‎ 函数 第4个表明式不等于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

 

Select coalesce(null,null,'1','2') //结果为 1

coalesce(expression1**,**...n卡塔尔国 与此 case函数等效:

 

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

 

注意:
当第二个表明式为字符串且不能够转变为整数时,若在背后的表明式中有整数,那样的讲话是会报错的。
例如:

Select COALESCE('a',null,'1',2卡塔尔 //那是漏洞非常多的

这一定于

 

select 
CASE
WHEN ('a' IS NOT NULL) THEN 'a'
WHEN (2 IS NOT NULL) THEN 2
ELSE NULL
END //会现身错误,因为系统不可能将a转变为对应的莫西干发型

 

但是

 

select 
CASE
WHEN ('a' IS NOT NULL) THEN 'a'
WHEN (2 IS NOT NULL) THEN '2'
ELSE NULL
END //那是科学的

 

由此大家在利用coalesce函数时,应该超小心。
isnull( check_expression , replacement_value 卡塔尔 使用钦定的替换值替换 NULL。
replacement_value 必需是能够隐式转变为 check_expresssion 类型的类型。
在表stu中
图片 1

select isnull(ssex,'p'卡塔尔(英语:State of Qatar) from stu //若ssex列中有NULL值,那么重返p

nullif( expression , expression 卡塔尔(英语:State of Qatar) 若是五个钦点的表明式相等,则赶回空值,否则 NULLIF 再次来到第一个 expression 的值。

select nullif(1,1) //结果为 NULL
select nullif(1,2) //结果为 1

本文由澳门太阳娱乐手机登录发布于首页,转载请注明出处:Server函数之空值处理,CUBRID学习笔记

关键词: