SQL 语句

Data Type

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

-- 查看所占字节数
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;

字符类型

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 的字符集支持版本，对多字节字符有算法上的优化。
-- 感觉用的比较少。

数值类型

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 类型就可以了

日期类型

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'); -- 手动转型

二进制类型

RAW
LONG RAW

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 使用指南', '你好');

伪列(Pseudo Column)

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 作为表的主键。迁移的需求，有改变的风险。

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 表中工资前三位的雇员信息。

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

运算符

算术运算符

加 / 减 / 乘 / 除

连接运算符

是用来连接字符串的。跟java中的 + 是一致的。

select 'abc' || ' bcd ' as 连接后的结果 from dual;
select d.dname || ' 部门' from dept d;

比较运算符

> / < / >= / <= / != / <> /IS NULL / Like / Between / In / Exsist

-- 判断 null 值
select * from n5 where s is null;
select * from n5 where s is not null;

-- like 模糊查询。慎用，有可能会导致全表扫描，效率低。
-- % 匹配0到多个字符，_ 匹配一个字符
select username from dba_users where username like 'VI_';
select username from dba_users where username like 'SC%';
select username from dba_users where username like '%SC%';

-- in，是 where x = a or x = b or x = c 的一种缩写。下面两条是等价的。
select * from emp where empno in (700, 800, 900);
select * from emp where empno = 700 or empno = 800 or empno = 900;
-- in 后面跟的不一定是逗号分隔的单项，也可能是一个完整的查询语句。
-- 下面两条结果是一致的
-- 这种 in 慎用
select * from emp where deptno in (select deptno from dept where dname = 'SALES');
select a.* from emp a,dept d where a.deptno =d.deptno and d.dname='SALES';


-- between...and
select * from emp where empno between 7800 and 9000;
-- 等同于：
select * from emp where empno >= 7800 and empno <= 9000;

逻辑运算符

and / or / not

not 的优先级 > and 的优先级 > or 的优先级

集合操作符

Union / UnionAll / Intersect / Minus

-- 生成测试数据
create table dept_01 as select * from dept where rownum < 6;
create table dept_02 as select * from dept where rownum < 4;
insert into dept_02 values (98, '小吃部', '斗门');
insert into dept_02 values (99, '外卖部', '香洲');
commit;

select * from dept_01
union
select * from dept_02;

select * from dept_01
union all
select * from dept_02;

select * from dept_01
intersect
select * from dept_02;

select * from dept_01
minus
select * from dpet_02;

Join Query

-- 初始化表
create table loc (id int primary key, name varchar2(20));
create table person (name varchar2(20), locid references loc);

-- 初始化数据
insert into gp values (1, '和平', '万岁');
insert into gp values (2, '*', '很好');
insert into gp values (3, '*', '爱国');
insert into gp values (4, '敬业', '友善');
insert into loc values (11, '广东');
insert into loc values (22, '山东');
insert into loc values (33, '湖南');
insert into loc values (44, '江西');
insert into person values ('王xx', 33);
insert into person values ('吴xx', 33);
insert into person values ('杜xx', 44);
insert into person values ('范xx', 11);
commit;

select * from person;
select * from loc;

-- 查询就是一个逐步过滤的过程
-- 叉乘，内部杂交，虚拟表，16条数据
select * from person, loc;
-- 过滤掉不符合条件的数据。即完成一次外连接。
-- 即：增一表，加一条件。
select * from person, loc where person.locid = loc.id;
-- 其他条件，即在上面的基础上继续过滤
select * from person, loc where person.locid = loc.id and person.name like '王%';


-- 增加另一个表
create table gp (id int primary key, name varchar2(20), logo varchar2(10)); 
alter table person add  (gpid int references gp);
-- 修改每个人的 group
-- update ..

select * from gp;
select * from loc;
select * from person;

update loc set name='*湖南省' where id=33;
commit;

select * from person p, loc l, gp g;
-- 将不符合的排除掉
select * from person p, loc l, gp g
  where p.locid = l.id
    and p.gpid = g.id
    -- and   l.name = '江西'
    and g.name='和平'
    and p.name like '王%';

-- 
-- oracle 写法，非标准
select * from person p, loc l, gp g
  where p.locid = l.id and p.gpid = g.id;

-- ansi sql 写法，join 默认是 inner join
select * from person p
   join loc l on p.locid = l.id
   join gp g on p.gpid = g.id;


-- 增加一个没有地区的人
insert into person values ('黄秀', null, null);
commit;

-- 显示所有人，并将其地区信息查询出来。
-- 需要使用外连接，将即使不匹配的人也查询出来
-- oracle 写法
select * from person, loc
  where person.locid = loc.id(+);
-- 标准写法
select * from person p
  left outer join loc l on p.locid = l.id;

SQL 语句的类型和 用法