SQL的数据类型

【三】SQL的数据类型(表的字段类型）

3.1 四种基本的常用数据类型（表的字段类型）

1、字符型，2、数值型，3、日期型，4、大对象型

3.1.1 字符型：

字符类型char和varchar2的区别

SCOTT@ prod>create table t1(c1 char(10),c2 varchar2(10));

SCOTT@ prod>insert into t1 values('a','ab');

SCOTT@ prod>select length(c1),length(c2) from t1; char和varchar2默认以byte为计算单位

LENGTH(C1) LENGTH(C2)

---------- ----------

10 2

3.1.2 数值型：

3.1.3 日期型：

系统安装后，默认日期格式是DD-MON-RR, RR和YY都是表示两位年份，但RR是有世纪认知的，它将指定日期的年份和当前年份比较后确定年份是上个世纪还是本世纪（如表）。

当前年份 指定日期 RR格式 YY格式

------------------------------------------------------------------------------

1995 27-OCT-95 1995 1995

1995 27-OCT-17 2017 1917

2001 27-OCT-17 2017 2017

2013 27-OCT-95 1995 2095

3.1.4 LOB型：

大对象是10g 引入的，在11g中又重新定义，在一个表的字段里存储大容量数据，所有大对象最大都可能达到4G。CLOB，NCLOB，BLOB都是内部的LOB类型，没有LONG只能有一列的限制。

保存图片或电影使用BLOB最好、如果是小说则使用CLOB最好。虽然LONG、RAW也可以使用，但LONG是oracle将要废弃的类型，因此建议用LOB。

虽说将要废弃，但还没有完全废弃，比如oracle 11g里的一些视图如dba_views，对于text(视图定义）仍然沿用了LONG类型。

Oracle 11g重新设计了大对象，推出SecureFile Lobs的概念，相关的参数是db_securefile，采用SecureFile Lobs的前提条件是11g以上版本，ASSM管理等，符合这些条件的。BasicFile Lobs也可以转换成SecureFile Lobs。较之过去的BasicFile Lobs, SecureFile Lobs有几项改进：

1）压缩，2）去重，3）加密。

当create table定义LOB列时，也可以使用LOB_storage_clause指定SecureFile Lobs或BasicFile Lobs，而LOB的数据操作则使用Oracle提供的DBMS_LOB包，通过编写PL/SQL块完成LOB数据的管理。

3.2 数据类型的转换

3.2.1 转换的需求

什么情况下需要数据类型转换

1）如果表中的某字段是日期型的，而日期又是可以进行比较和运算的，这时通常要保证参与比较和运算的数据类型都是日期型。

2）当对函数的参数进行抽（截）取、拼接，或运算等操作时，需要转换为那个函数的参数要求的数据类型。

3）制表输出有格式需求的，可将date类型，或number类型转换为char类型

4）转换成功是有条件的，有隐性转换和显性转换两种方式

3.2.2隐性类型转换：

是指oracle自动完成的类型转换。在一些带有明显意图的字面值上，可以由Oracle自主判断进行数据类型的转换。

一般规律：

①比较、运算或连接时：

SQL> select empno,ename from emp where empno='7788'

empno本来是数值类型的，这里字符'7788'隐性转换成数值7788

SQL> SELECT '12.5'+11 FROM dual;

将字符型‘12.5’运转成数字型再求和

SQL> SELECT 10+('12.5'||11) FROM dual;

将数字型11隐转成字符与‘12.5’合并，其结果再隐转数字型与10求和

②调用函数时

SQL> select length(sysdate) from dual;

将date型运转成字符型后计算长度

③向表中插入数据时

create table scott. t1 (id int,name char(10),birth date);

insert into scott.t1 values('123',456,'2017-07-15');

按照字段的类型进行隐式转换

3.2.3 显性类型转换

即强制完成类型转换（推荐），有三种形式的数据类型转换函数：

TO_CHAR

TO_DATE

TO_NUMBER

1）日期-->字符

SQL> select ename,hiredate, to_char(hiredate, 'DD-MON-YY') month_hired from emp

where ename='SCOTT';

ENAME HIREDATE MONTH_HIRED

---------- ------------------- --------------

SCOTT 1987-04-19 00:00:00 19-4月 -87

fm压缩空格或左边的'0'

SQL> select ename, hiredate, to_char(hiredate, 'fmyyyy-mm-dd') month_hired from emp

where ename='SCOTT';

ENAME HIREDATE MONTH_HIRED

---------- ------------------- ------------

SCOTT 1987-04-19 00:00:00 1987-4 19

其实DD-MM-YY是比较糟糕的一种格式，因为当日期中天数小于12时，DD-MM-YY和MM-DD-YY容易造成混乱。

以下用法也很常见：

SQL> select to_char(hiredate,'yyyy') FROM emp;

SQL> select to_char(hiredate,'mm') FROM emp;

SQL> select to_char(hiredate,'dd') FROM emp;

SQL> select to_char(hiredate,'DAY') FROM emp;

2）数字-->字符：9表示数字，L本地化货币字符

SQL> select ename, to_char(sal, 'L99,999.99') Salary from emp where ename='SCOTT';

ENAME SALARY

---------- --------------------

SCOTT ￥3,000.00

以下四个语句都是一个结果：

SQL> select to_char(1890.55,'$99,999.99') from dual;

SQL> select to_char(1890.55,'$0G000D00') from dual;

SQL> select to_char(1890.55,'$99G999D99') from dual;

SQL> select to_char(1890.55,'$99G999D00') from dual; 9和0可用，其他数字不行

3）字符-->日期

SQL> select to_date('1983-11-12', 'YYYY-MM-DD') tmp_DATE from dual;

4）字符-->数字：

SQL> SELECT to_number('$123.45','$9999.99') result FROM dual;

使用to_number时如果使用较短的格式掩码转换数字，就会返回错误。不要混淆to_number和to_char转换。

SQL> select to_number('123.56','999.9') from dual;

报错：ORA-01722: 无效数字

练习：建立t1表，包括出生日期，以不同的日期描述方法插入数据，显示小于15岁的都是谁

SQL> create table t1 (id int,name char(10),birth date);

insert into t1 values(1,'tim',sysdate);

insert into t1 values(2,'brian',sysdate-365*20);

insert into t1 values(3,'mike',to_date('1998-05-11','yyyy-mm-dd'));

这一句也可以写成insert into t1 values(3,'mike',to_date('1998-05-11'))，因为'1998-05-11'是和当前日期格式匹配的。

insert into t1 values(4,'nelson',to_date('15-2月-12','dd-mon-rr'));

SQL> select * from t1;

ID NAME BIRTH

---------- ---------- -------------------

1 tim 2016-02-25 17:34:00

2brian 1996-03-01 17:34:22

3 mike 1998-05-11 00:00:00

4 nelson 2012-02-15 00:00:00

SQL>select name||'的年龄是'||to_char(months_between(sysdate,birth)/12,99) age from t1

where months_between(sysdate,birth)/12<15;

AGE

-------------------------

tim 的年龄是 0

nelson 的年龄是 4

the end ！！！

@jackman 共筑美好！