• CUBRID学习笔记 25 数据类型2


    ---恢复内容开始---

    6枚举类型

    语法

    <enum_type>

        : ENUM '(' <char_string_literal_list> ')'

    <char_string_literal_list>

        : <char_string_literal_list> ',' CHAR_STRING

        | CHAR_STRING

     

    CREATE TABLE tbl (

        color ENUM('red', 'yellow', 'blue')

    );

    结果

    Value

    Index Number

    NULL

    NULL

    'red'

    1

    'yellow'

    2

    'blue'

    3

     

     

     

     

     

     插入 INSERT into tbl values ('yellow'), ('red'), (2), ('blue');

    结果

    SELECT color FROM tbl;

     

      color

    ======================

      yellow

      red

      yellow

      blue

     

    SELECT color FROM tbl ORDER BY color ASC;

     

      color

    ======================

      red

      yellow

      yellow

      blue

     

    SELECT color FROM tbl ORDER BY cast(color as char) ASC;

     

      color

    ======================

      blue

      red

      yellow

      yellow

     

    •  使用字符串上下文,则枚举返回字符串 SELECT CONCAT(enum_col, 'color') FROM tbl_name;

       

        CONCAT(color, '_color')

      ======================

        yellow_color

        red_color

        yellow_color

        blue_color

    使用数字上下文 返回数字

    • SELECT color + 0 FROM tb;

       

        color + 0

      ======================

        2

        1

        2

        3

    •  是条件为数字的时候 ,为枚举的索引  SELECT color FROM tbl WHERE color <= 1;

       

        color

      ======================

      red

       

    • 使用字符串条件 枚举为字符串
      • SELECT color FROM tbl WHERE color <= 'red';

         

          color

    注意索引默认从0开始   字符串的值不可以是null

    如果枚举值本身就是数字,请用单引号括起来 如'1'

    枚举排序是按索引排序的. 所以下面的语句是什么意思,应该明白了吧.先cast转换下 

    SELECT color FROM tb ORDER BY cast(color as char) ASC;

    枚举和普通类型对应关系

    *SHORT

    Index Number

    *INTEGER

    Index Number

    *BIGINT

    Index Number

    *FLOAT

    Index Number

    *DOUBLE

    Index Number

    *NUMERIC

    Index Number

    *MONETARY

    Index Number

    *TIME

    String

    *DATE

    String

    *DATETIME

    String

    *TIMESTAMP

    String

    *CHAR

    String

    *VARCHAR

    String

    BIT

    String

    VARBIT

    String

    注意 如果是使用jdbc驱动,使用枚举有些区别

    7 集合

    有三种

    Type

    Description

    Definition

    Input Data

    Stored Data

    SET

    A union which does not allow duplicates

    col_name SET VARCHAR(20)
    col_name SET (VARCHAR(20))

    {'c','c','c','b','b','a'}
    {'c','c','c','b','b', 'a'}

    {'a','b','c'}
    {'a','b','c'}

    MULTISET

    A union which allows duplicates

    col_name MULTISET VARCHAR(20)
    col_name MULTISET (VARCHAR(20))

    {'c','c','c','b','b','a'}
    {'c','c','c','b','b','a'}

    {'a','b','b','c','c','c'}
    {'a','b','b', 'c','c','c'}

    LIST
    SEQUENCE

    A union which allows duplicates and stores data in the order of input

    col_name LIST VARCHAR(20)
    col_name LIST (VARCHAR(20))

    {'c','c','c','b','b','a'}
    {'c','c','c','b','b', 'a'}

    {'c','c','c','b','b','a'}
    {'c','c','c','b','b','a'}

    集合间的转换

    TO

    FROM

    SET

    MULTISET

    LIST

    SET

    -

    O

    O

    MULTISET

    O

    -

    X

    LIST

    O

    O

    -

    ---恢复内容结束---

  • 相关阅读:
    柱状图最大的矩形
    单词搜索
    最小覆盖子串
    颜色分类
    编辑距离
    X的平方根
    二进制求和
    最大子序和
    N皇后
    java8-14-时间API
  • 原文地址:https://www.cnblogs.com/wang2650/p/5286575.html
Copyright © 2020-2023  润新知