您的位置:澳门新葡8455最新网站 > 数据库管理 > CUBENCOREID学习笔记,Server函数之空值管理

CUBENCOREID学习笔记,Server函数之空值管理

发布时间:2019-12-01 12:31编辑:数据库管理浏览(180)

    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, 奥迪Q5LIKE 运算符 这几个例子用的少,转码比较 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'卡塔尔(英语:State of Qatar);

    ('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函数 几个参数, 第一个表明式重返真假 第贰个代表确实时候回来的值 第多少个象征假的时候回来的值

    类似 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卡塔尔国 函数 第二个表达式不等于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'卡塔尔(قطر‎ from stu //若ssex列中有NULL值,那么重临p

    nullif( expression , expression 卡塔尔(قطر‎ 若是几个钦命的表达式相等,则赶回空值,不然 NULLIF 再次回到第二个 expression 的值。

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

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:CUBENCOREID学习笔记,Server函数之空值管理

    关键词: