SQL语句 数据类型

时间：2022-07-16 13:48:06

6.1 Data Type

SQL语句 数据类型SQL语句 数据类型

查看数据所占空间的两个函数：

-- 查看所占字节数
select length('你好，世界') from dual;
-- 查看所占字符数，即多少个字母，多少个汉字
select lengthb('您好，美女') from dual;

-- 比如
create table aaa (a varchar2(6));
insert into aaa values ('aaa');
insert into aaa values ('你好');
select a, length(a), lengthb(a) from aaa;

6.1.1 字符类型

  • char，固定长度，默认1，最大2000，如果长度不够，用空格填充。
  • varchar2 类型，变长字符串，最多4000字节长度。
  • nvarchar2，跟上面比，增加了字符集的支持。
  • long，用于存储大数据，最多 2G。不建议使用，已被 clob 类型替代。
    -- 它的使用跟 SQL 的标准 varchar 基本类似，
    -- 但是，在 Oracle 中请使用 varchar2 而不是 varchar，
    -- varchar2 的效率更高，并且在兼容性上做的更好
    -- 初始化的语句为： name varchar2(20)
    -- 其中 20 代表最大长度， 默认单位是字节。
    -- 如果定义为： name varchar2(20 char)，表示最大保存20个字符长度的字符串。
    create table aaa (
     a varchar2(20), -- 最大是20个字节长度，默认单位字节
     b varchar2(20 byte), -- 最大为20个字节长度，跟上面是一样的
     c varchar2(20 char) -- 最大为20个字符长度
    );

    -- varchar2 最大能保存 4000 个字节
    -- 如果是英文，则是 4000 个英文字母
    -- 如果是中文，需要按照字符集判断：
    -- GBK 用两个字节表示一个汉字，所以 varchar2 最多表示 2000 个汉字
    -- UTF-8 是变长字符集，用一个字节表示一个英文字母，用3个或4个字节表示一个汉字，所以，最多可以保持1333个汉字。
    select length('你好') from dual; -- 2
    select lengthb('你好') from dual; -- 4


    -- char 类型是固定长度的，可能会占用更多空间。但是因为长度固定，块的分配管理比较块，效率很高。
    -- varchar2 是变长的，会占用尽量少的空间。但是需要消耗更多资源为分配变长空间，效率略低。
    -- 所以，用哪一种，酌情而定。

    -- nchar/nvarchar2 是 char/varchar2 的字符集支持版本，对多字节字符有算法上的优化。
    -- 感觉用的比较少。

6.1.2 数值类型

  • number，占用 38 位，有自己的内部表示方式，可以表示天文数字。
  • int/float，number 类型的子类型
    -- number 类型的语法为 NUMBER [位数[,小数点数]]
    create table bbb (
     a number, -- 一共 38 位，其他按照插入的值自动判断
     b number(5), -- 一共5位
     c number(5, 2), -- 一共5位，3位整数，2位小数
     d number (*, 2) -- 保存两位小数，其他随意
     e number (*, 0), -- 相当于 int
     f int
    );

    -- 插入的时候，多余的小数会被切掉，但如果整数位超了，会报错
    insert into bbb values (111.223, 111.223, 111.223, 111.223, 22);

    -- 一般情况下，作为表的主键，设为 int 类型就可以了

6.1.3 日期类型

  • date，存储的年月日，时分秒
  • timestamp，存储的更详细，包括时区，还有精确到小数点后6位的秒数等
  • timestamp with time zone，可以设置时区
  • sysdate/systimestamp 连个函数用来查询当前的日期
    -- 显示当前时间
    select sysdate from dual;
    select systimestamp from dual;

    -- 时间的显示格式，由 nls_date/time_format 等参数控制
    show parameter nls
    -- 如果想格式化时间，可以有下面方法：
    -- 1. 更改注册表中变量
    -- # set NLS_DATE_FORMAT='yyyy.mm.dd'
    -- 2. 更改当前会话中的格式
    alter session set NLS_DATE_FORMAT='yyyy.mm.dd';
    -- 3. 手动转型：to_char
    -------- yyyy 代表四位的年, rr 代表两位的年
    -------- mm 代表两位的数字月,
    -------- dd 代表两位的数字日,
    -------- hh 代表12格式的小时, hh24 代表24格式的小时
    -------- mi 代表分钟数
    -------- ss 代表秒数
    select to_char(sysdate, 'yyyy-mm-dd') from dual;


    -- date 比 timestamp 占用更少空间
    -- timestamp 比 date 更精确
    -- 没有优劣，使用哪一个，按照需求来


    -- 插入时间，使用 to_date 转型。
    -- 符合标准日期格式的字符串，可以隐式转型。
    create table stu (name varchar2(20), birth date default sysdate);
    insert into stu values ('aaa', sysdate); -- 当前时间
    insert into stu values ('bbb', '20100303'); -- 插入成功，char 自动转型为 date
    insert into stu values ('ccc', to_date('1999-01-11', 'yyyy-mm-dd'); -- 手动转型

6.1.4 二进制类型

  • RAW
  • LONG RAW

6.1.5 LOB 类型

Large Object，用来存储大数据。

Oracle 提供了 DBMS_LOG 包对 LOB 类型的数据进行处理。 也可以在 jdbc 中通过 getXLob() 的方式进行 lob 字段的处理。

  • CLOB，charactor，存储大容量字符串
  • BLOB，Binary，存储二进制文件，如小图片、小电影
  • BFile/XMLType 等
    create table article
    (
     id int primary key,
     title varchar2(20) not null,
     content clob,
     createtime date default sysdate
    );
    insert into article (id, title, content) values (1, 'Oracle 使用指南', '你好');

6.1.6 伪列(Pseudo Column)

 
  1. rowid

    rowid 是 oracle 中的伪列。可以通过下面语句显示:

    select d.rowid, d.* from dept d

    它是唯一的，不可变的，固定长度的。

    它是数据存储物理地址的一种映射。一共有18位，前6位表示对象id，后3位表示fno，后6位表示块编号，最后3位表示行编号。 所以，通过rowid可以最快速度地定位到数据所在的位置。

    比如，如果某行数据的 rowid 是 AAAO0fAAFAAAAlmAAA, 那么可以根据它直接定位数据的物理地址：

    AAAO0f AAF AAAAlm AAA
    对象号(6个字符) 文件号(3个字符) 块号(6个字符) 行号(3个字符)

    rowid 是 oracle 特有的。

    不建议使用 rowid 作为表的主键。迁移的需求，有改变的风险。
  2. rownum

    列出每一行数据的行数，从1开始，自然增长。

    -- 基本用法
    select rownum, d.* from dept;
    select rownum, d.* from dept d where rownum < 3; -- 显示前两条
    select * from (select rownum rn, d.* from dept d) t where t.rn = 3; -- 只显示第三条
    select * from (select * from emp order by sal desc) where rownum<=3; -- 显示 emp 表中工资前三位的雇员信息。

6.1.7 NULL

它是 Oracle 中非常特殊的一种类型。它表示不确定，表示没有值。并且它能转化成所有的类型。 向数据库中插入空字符串时，oracle 会把它自动转化为 null 类型。所以，在查询空字符的时候：

select * from n3 where s = '';

上面的语句是非法，不合适的。应该这样查：

select * from n3 where s is null;
select * from n3 where s is not null;

创建表的时候，为了约束插入的数据不能为空，应该在字段的后面写上 not null 约束。

create table n5 (s varchar2(20) not null);

跟 null 做任何的运算，结果仍然是 null.

select null + '' from dual;   -- null
标签：

相关文章