Oracle中SQL操作与查询

1. SQL简介

    Oracle中SQL由如下命令组成：

• DDL（数据定义语言）
      CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。
• DML（数据操作语言）
      INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT … FOR UPDATE（查询）命令等。
• DQL（数据查询语言）
      基于查询语句、Order By子句、Group By子句等。
• TCL（事务控制语言）
  COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
• DCL（数据控制语言）
      GRANT（授权）命令、REVOKE（撤销）命令。

2. Oracle中的数据类型

Oracle中常见的数据类型有：

类型	含义
char（length）	存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。
varchar2（length）	存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
number（p, s）	既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。
date	存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
timestamp	不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。
clob	存储大的文本，比如存储非结构化的XML文档
blob	存储二进制对象，如图形、视频、声音等。

其中，对于number类型：

格式	输入的数字	实际的存储
NUMBER	1234.567	1234.567
NUMBER（6，2）	123.4567	123.46
NUMBER（4，2）	12345.67	输入的数字超过了所指定的精度，数据库不能存储

对于日期类型，可以使用sysdate内置函数获取当前系统的系统日期和时间，返回的是date类型，使用systimestamp函数可以返回当前日期、时间和时区。

select sysdate,systimestamp from dual;

Oracle中必须使用 select 列... from 表 的完整语法，当查询单行函数时，from后面使用 dual 表，dual表在系统重只有一行一列，该表在输出单行函数时是为了 select 列... from 表 的完整语法而使用的。

3. 创建表和约束

3.1 表结构

创建表语法：

CREATE TABLE 表名(
	字段名称 类型  约束,
	字段名称 类型  约束,
	字段名称 类型  约束
);

Oracle中不区分大小写，并且Oracle中创建表时不能直接添加字段注释，添加字段注释需要单独执行 comment 命令：

-- 给表添加注释
comment on table 表名 is '表名注释';
-- 给字段添加注释
comment on column 字段名 is '字段名注释';

Oracle中更新表结构：

-- 添加字段
alter table 表名 add 字段 类型 约束;
-- 修改字段类型
alter table 表名 modify 字段 类型 约束;
-- 修改字段名称
alter table 表名 rename column 原来的字段名 to 新的字段名;
-- 删除字段
alter table 表名 drop column 字段名; 

3.2 约束

Oracle中约束有如下：

• not null 非空约束，字段不允许为空。
• default 默认值约束，如果该列为null就使用默认值填充。
• unique 唯一性约束，有数据的情况下，该列不能出现重复记录，null 不包括在内。
• primary key 主键约束，能够唯一标识字段的约束，主键修饰的字段是不能重复且不能出现空值的。
• foreign key 外键约束，表与表之间的关联关系。
• check 检查约束，在字段类型的基础上，更进一步的提升数据的精度。例如：

DROP TABLE t_student;
CREATE TABLE t_student(
	id  number(5) unique not null , -- 学生编号
	name varchar2(20)  not null, -- 学生的姓名
	--age number(2)  check(age > 1 and age < 25), -- 学生的年龄
	age number(2)  check(age between 1 and 25), -- 学生的年龄
	address varchar2(100),  -- 学生的地址
	gender char(3) check (gender in ('男','女'))
);

4. DML语句

4.1 INSERT

INSERT INTO 表名(fieldName1,fieldName1,...fieldNameN)values(value1,value2,...,valueN)
-- 案例
insert into t_class(id,name)values(1,'计算机1班');

4.2 DELETE

 DELETE FROM 表名 [where 条件]
 delete from t_class where id = 17;
 -- 对于null的查询我们是用 is null 来匹配的
 delete from t_class where name is null;
 
 -- delete 删除会做数据的缓存，能回滚，会删除表中的所有的数据，效率低
delete from t_class ; 
-- 删除全表的数据，直接删除数据，不做缓存处理，不能回滚， 效率高，风险大
truncate table t_class ;

4.3 UPDATE

UPDATE 表名 SET field1=value1,field2=value2 ... [where 条件]
update t_class set name = '软件班级' ;

4.4 多行插入

-- 多行插入
insert into t_class_copy(id,name) select id,name from t_class;
--复制表，不带数据
create table t_class_copy as select * from t_class where 1!=1;
-- 复制表，带数据
create table t_class_copy as select * from t_class;

4.5. 序列号

应用场景：主键，插入时要保证其唯一性

• Oracle中提供了序列化
• MySQL中则是采用主键自增
• 分布式环境则是采用分布式ID

创建序列号语法：

create sequence 序列名称
[increment by] -- 每次自增的数量
[start with 1] -- 从1开始计数
[nomaxvalue] -- 不设置最大值
[nocycle] -- 一直累加，不循环
[cache 10]; -- 缓存10

序列号使用：

-- 从1开始，每次+1
create sequence s_student;
-- 获取+1后的值
select s_student.nextval from dual;
-- 获取当前序列值，currval只有在执行了nextval后才会生效
select s_student.currval from dual;
-- 插入到表中
insert into t_student values(s_student.nextval, '软件2班');

5. DQL语句

基本语法：

--语法结构
SELECT <列名>
FROM <表名>
[WHERE <查询条件> ]
[ORDER BY <排序的列名>[ASC或者DESC] ]
[GROUP BY <分组字段> ]

5.1 简单查询语句

-- 1. 查询所有列
select * from t_student;
-- 2. 查询特定列
select id,name,age from t_student;
-- 3. 查询字段使用别名
-- 3.1 查询字段别名只能用双引号"而不能使用单引号'
-- 3.2 别名的双引号"可以省略
-- 3.3 当别名包含特殊字符，必须添加双引号"
select
	id as "学生编号",
	name as 学生姓名,
	age as "【学生年龄】"
from t_student;
-- 4. 查询字段中添加常量
select id,name,age,55 as 体重 from t_student;
-- 5. 拼接内容，oracle中拼接使用的是 ||
select id,name,age,'【' || id || '-' || 'name' || '】' as 组合名 from t_student;

5.2 聚合函数

聚合函数常用于统计。

-- 1. 统计学生人数，count(字段名)统计该列数据不为空的记录数
select count(*),count(name),count(gender),count(age),count(1) from t_student;
-- count(*) 会统计所有列，选出值最大的，效率低；
-- count(1) 则是给没列添加了一列为1的常量，等同于:
-- select t_student.*,1 from t_student;
-- 2. 其它统计函数
select count(1),max(age),min(age),sum(age),round(avg(age)) from t_student ;

5.3 分组函数

分组函数 group by

-- 1. 不与聚合函数一起使用时，作用与 `distinct` 是一样的，可以去重
select deptno from emp group by deptno;
-- 等同于
select distinct deptno from emp;
-- 2. 与聚合函数一起使用，聚合函数统计的就不是查询的所有数据了，而是分组后的数据
-- 分组后的数据中不能出现非分组的字段
-- 2.1 统计出学生表中男生和女生的人数
select gender,count(1) from t_student group by gender;
-- 2.2 统计出学生表中每个班级的人数
select class_id,count(1)  from t_student t group by t.class_id;
-- 2.3 统计出学生表中每个班级中的男生和女生人数 聚合函数统计的是分组后的最小单位的数据
select class_id,gender,count(1) from t_student group by class_id ,gender ;
-- 2.4 统计出学生表中年龄大于18的男生和女生的人数
select gender,count(1)
from t_student
where age > 18 -- where 的位置，必须是要在group by 之前。作用呢是筛选要分组的数据
group by gender
-- 2.5 统计出学习表中年龄大于18的班级人数大于1的记录
select class_id,count(1)
from t_student
where age > 18
group by class_id
having count(1) > 1 -- 在group by 之后。和group by 配合使用。作用是过滤分组后的数据

5.4 多表查询

-- 1. 交叉连接，获取两张表的笛卡尔积
select * from t_class cross join t_student ;
select t1.*,t2.* from t_student t1,t_class t2;
-- 2. 等值连接
select t1.*,t2.* 
from t_student t1,t_class t2 -- 1000 * 1000 = 100w
where s.class_id = c.id; -- where是在结果集加载到内存后才会做筛选
-- 3. 内连接，左表中记录和有表中记录连接时就会根据on中的条件进行过滤，满足才获取，否则直接丢失
select t1.*,t2.*
from t_student t1 
	inner join t_class t2 on t1.class_id = t2.id;
-- 4. 左连接，在内连接的基础上，让左表每条记录一一去匹配右表所有记录，右表不匹配的字段均为NULL
select t1.*,t2.*
from t_student t1 left outer join t_class t2
on t1.class_id = t2.id;
-- 5. 右连接，在内连接基础上，让右表每条记录一一去匹配左表所有记录，左表不匹配的字段均为NULL
select t2.*,t1.*
from t_student t1 
	right outer join t_class t2 on t1.class_id = t2.id;
-- 6. 全连接,交集 + (左表剩余内容+右表NULL) + (左表NULL+右表剩余内容)
select t1.*,t2.*
from t_student t1 full join t_class t2
on t1.class_id = t2.id;
-- 7. 合并结果集 union all，union
select t1.*,t2.*
from t_student t1 
	left outer join t_class t2 on t1.class_id = t2.id
union -- 合并结果集。会去掉重复的记录  和 全连接差不多
select t1.*,t2.*
from t_student t1 
	right outer join t_class t2 on t1.class_id = t2.id;

select t1.*,t2.*
from t_student t1 
	left outer join t_class t2 on t1.class_id = t2.id
union all -- 合并结果集。不会去掉重复的记录  
select t1.*,t2.*
from t_student t1 
	right outer join t_class t2 on t1.class_id = t2.id;