oracle数据库dblink创建语句_SQL语句（二）与Oracle数据库对象

【024】

1. 子查询

用于当一次查询的结果是另一次查询所需要的时候, 可以使用子查询

1.1 单行子查询

子查询的返回结果是单行数据.

a)查询所有比“CLARK”工资高的员工的信息

select * from emp where sal > (select sal from emp where
ename='CLARK');

b)查询工资高于平均工资的雇员名字和工资

select ename, sal from emp where sal>(select avg(sal)
from emp);

c)查询和 SCOTT 同一部门且比他工资低的雇员名字和工资

select ename, sal, deptno
from emp
where deptno=(select deptno from emp where
ename='SCOTT')
and sal<(select sal from emp where ename='SCOTT');

d)查询职务和 SCOTT 相同，比 SCOTT 雇佣时间早的雇员信息

select *
from emp
where job=(select job from emp where ename='SCOTT')
and hiredate<(select hiredate from emp where
ename='SCOTT');

1.2 多行子查询

子查询的返回结果是多行数据. 此时, 不能再使用普通的比较运算符了.

多行记录比较运算符:

ANY: 跟结果中的任何一个数据进行比较

查询工资低于任何一个“CLERK”的工资的雇员信息

select * from emp where sal<ANY(select sal from emp
where job='CLERK') and job<>'CLERK';

ALL: 跟结果中的所有数据进行比较

查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资

select empno, ename, sal
from emp
where sal>ALL(select sal from emp where
job='SALESMAN');

IN: 等于结果中的任何一个

查询部门 20 中职务同部门 10 的雇员一样的雇员信息

select *
from emp
where job in (select job from emp where deptno=10)
and deptno=20;
select *
from emp
where job =any (select job from emp where deptno=10)
and deptno=20;

1.3 相关子查询和不相关子查询

• 不相关子查询: 子查询不会用到外查询的数据, 子查询可以独立运行.
• 相关子查询: 子查询会用到外查询的数据, 子查询不能独立运行.

查询本部门最高工资的员工

a)不相关子查询的实现方式

select *
from emp e
where (e.deptno, e.sal) in (select deptno, max(sal)
from emp group by deptno);

b)相关子查询的实现方式

select *
from emp e
where sal=(select max(sal) from emp where
deptno=e.deptno);

1.4 子查询可以作为一张表格进行多表连接查询

查询每个部门平均薪水的等级

select t.deptno, t.avg_sal, s.grade
from salgrade s
join (
 select deptno, avg(sal) avg_sal from emp group
by deptno
) t
on t.avg_sal between s.losal and s.hisal
order by t.deptno;

二、数据库用户

1. 用户的创建

a) 语法

create user 用户名 identified by 密码;

b) 创建用户 bjsxt, 设定密码为 bjsxt

注意: 操作数据库对象是需要 dba 权限的

create user bjsxt identified by bjsxt;

1. 给用户授权

Oracle 中的权限很多, 为了方便用户管理权限, 提供了角色这个概念.角色代表一个身份, 该身份拥有固定的权限. 常见的角色:

• DBA: 数据库管理员
• CONNECT: 临时用户, 拥有少量的权限
• RESOURCE: 比较靠谱的用户, 拥有更多的权限, 不能管理数据库

a)给 bjsxt 用户授予普通用户的权限
grant connect, resource to bjsxt;
b)从 bjsxt 用户撤销 resource 权限
revoke resource from bjsxt;
2. 删除用户
drop user bjsxt;
3. 修改用户
3.1 修改用户密码
alter user bjsxt identified by 123;
3.2 锁定用户和解锁定用户
a) 锁定用户
alter user bjsxt account lock;
c)解锁用户
alter user bjsxt account unlock;

1. Oracle 中的数据类型

1.1 字符类型
a) varchar2  可变长度的字符串, 效率较低
b) char  不可变长度的字符串, 效率较高
1.2 数值类型
number, 既可以表示整数, 也可以表示浮点数
1.3 日期时间类型
a) date, 存放日期和时间
b) timestamp, 比 date 更精确的日期时间类型
1.4 lob 类型
a) blob, 用于存放二进制数据, 可以用于存放文件, 图片,音频, 视频等二进制数据.
b) clob, 用于存放大文本信息.

2. 创建表格

创建学生表, 字段包含学号、姓名、性别，年龄、入学日期、

班级，email 等信息

create table student (
 sno number(4),
 sname varchar2(12),
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50)
);

1. 修改表格

1.1 添加字段
alter table student add (score number(3));
1.2 修改字段的类型
alter table student modify (score number(5,2));
1.3 重命名字段
alter table student rename column score to fenshu;
1.4 删除字段
alter table student drop column fenshu;
1.5 重命名表格
rename student to stu;
2. 删除表格
drop table stu;

三、 表格的约束

1、创建约束的时机

• 在建表的同时创建

• 建表后创建

2、 约束从作用上分类，可以分成两大类：

• 表级约束：可以约束表中的任意一列或多列。可以定义出了Not Null以外的任何约束。

• 列级约束：只能约束其所在的某一列。可以定义任何约束。

3、命名规则推荐采用：约束类型_约束字段

• 非空约束 NN__表名列名
• 唯一约束 UK_表名_列名
• 主键约束 PK_表名
• 外键约束 FK_表名_列名
• 检查约束 CK_表名_列名

constraints, 约束, 用于对表格的数据进行限制, 保证表格数据的完整性和一致性.

语法:

constraints 约束名 约束类型 (约束字段)

1.1 主键约束(primary key)

主键是用于唯一标识一条记录的字段, 必须保证既非空又唯一.一张表中, 只能有一个主键.

a)在表级别定义主键约束, 对约束统一管理

create table student (
 sno number(4),
 sname varchar2(12),
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50),
 constraints pk_student primary key (sno)
);

b)表级别简化版定义主键约束, 省略约束名

create table student (
 sno number(4),
 sname varchar2(12),
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50),
 primary key (sno)
);

c)在列级别定义主键约束

create table student (
 sno number(4) constraints pk_student primary key,
 sname varchar2(12),
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50)
);

d)在列级别简化版定义主键约束

create table student (
 sno number(4) primary key,
 sname varchar2(12),
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50)
);

e)联合主键, 只能在表级别定义, 因为一个表只能有一个主键

create table student (
 sno number(4),
 sname varchar2(12),
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50),
 constraints pk_student primary key (sno, email)
);

1.2 非空约束(not null)

字段值不允许为空, 非空约束只能在列级别定义

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50),
 constraints pk_student primary key (sno)
);

1.3 唯一约束(unique)

要求字段值不能重复.

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50), -- unique,
 constraints pk_student primary key (sno),
 constraints uk_student_email unique (email)
);

1.4 检查约束(check)

用于限定字段值的取值范围

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3) check (gender in ('男', '女')),
 age number(3),
 sdate date,
 clazz varchar2(20),
 email varchar2(50),
 constraints pk_student primary key (sno),
 constraints uk_student_email unique (email),
 constraints ck_student_age check (age between 18 and 30)
);

1.5 外键约束(foreign key)

用于约束表和表之间的关系. 两张表的依赖关系. 以 emp 表和 dept 表为例, emp 表依赖 dept 表. 因此, dept 表可以被称之为主表, emp 表被称之为从表.

注意:

主表中, 只有主键或者唯一键才可以被从表参考.

从表中作为外键的列, 类型一定要和主表的被参考列相同.

a)建立主表 clazz

create table clazz (
 cno number(3) primary key,
 cname varchar2(20) not null,
 croom number(3)
);

b)创建从表, 并定义外键 student

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3) default '男' check (gender in ('男', '女')),
 age number(3),
 sdate date,
 email varchar2(50),
 cno number(3),
 constraints pk_student primary key (sno),
 constraints uk_student_email unique (email),
 constraints ck_student_age check (age between 18 and 30),
 constraints fk_student_cno foreign key (cno) references clazz
(cno)
);

c)也可以在列级别定义外键, 如下:

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3) default '男' check (gender in ('男', '女')),
 age number(3),
 sdate date,
 email varchar2(50),
 cno number(3) references clazz(cno),
 constraints pk_student primary key (sno),
 constraints uk_student_email unique (email),
 constraints ck_student_age check (age between 18 and 30)
);

1. 外键中的删除问题:

主表中的数据在被引用时,无法直接删除,默认情况下,需要先删除从表中对应的所有数据, 再删除主表中的数据.

可以在定义外键时, 设置删除策略为 cascade(级联),

表示当删除主表的信息时, 同时删除从表中所有关联的信息

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3) default '男' check (gender in ('男', '女')),
 age number(3),
 sdate date,
 email varchar2(50),
 cno number(3),
 constraints pk_student primary key (sno),
 constraints uk_student_email unique (email),
 constraints ck_student_age check (age between 18 and 30),
 constraints fk_student_cno foreign key (cno) references clazz
(cno) on delete cascade
);

 可以在定义外键时, 设置删除策略为 set null(设空),表示当删除主表的信息时, 将从表中的相关数据设置为 null

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3) default '男' check (gender in ('男', '女')),
 age number(3),
 sdate date,
 email varchar2(50),
 cno number(3),
 constraints pk_student primary key (sno),
 constraints uk_student_email unique (email),
 constraints ck_student_age check (age between 18 and 30),
 constraints fk_student_cno foreign key (cno) references clazz
(cno) on delete set null
);

2. 在修改表格时添加约束

a) 创建主表 clazz

create table clazz (
 cno number(3) primary key,
 cname varchar2(20) not null,
 croom number(3)
);

b) 创建从表 student

create table student (
 sno number(4),
 sname varchar2(12) not null,
 gender char(3) default '男',
 age number(3),
 sdate date,
 email varchar2(50),
 cno number(3)
);

c) 给 student 表添加约束

-- 主键约束
alter table student add constraints pk_student primary key (sno);
-- 唯一约束
alter table student add constraints uk_student_email unique (email);
-- 检查约束
alter table student add constraints ck_student_age check (age between
18 and 30);
alter table student add constraints ck_student_gender check (gender in
('男','女'));
-- 外键约束
alter table student add constraints fk_student_cno foreign key (cno)
references clazz (cno);

序列

1. 序列(sequence)

序列是 Oracle 中特有的对象, 用于生成一个自动递增的数列. 通常被用来作为主键的值.

1.1 创建序列

a) 语法

create sequence seq_name
[increment by n
start with n
maxvalue n|nomaxvalue // 10^27 or -1
minvalue n|no minvalue
cycle|nocycle
cache n|nocache]
increment by, 代表每次增长的步长, 默认是 1, 可以是负数, 表示每次递减;
start with, 从哪个值开始, 默认是 1;
maxvalue, 序 列 能 到 达 的 最 大 值 , 默 认 值 是
nomaxvalue, 此时正数最大值是 10^27, 负数最大值是-1;
minvalue, 序 列 能 到 达 的 最 小 值 , 默 认 值 是
nominvalue, 此时正数的最小值是 1, 负数的最小值是-10^26;
 cycle|nocycle, 表示是否循环. 如果是 cycle, 达到最大值是会重新从头开始, 
如果是 nocycle, 最大值后会报错.
cache n|nocache, 表示高速缓存, 可以优化序列, 缓存的默认值是 20. nocache 表示没有缓存.

创建学生序列

create sequence seq_student;
1.2 序列的使用
1.2.1 nextval(序列的下一个值)
查看序列的下一个值
select seq_student.nextval from dual;
1.2.2 currval(序列的当前值)
查看序列的当前值
select seq_student.currval from dual;
1.2.3 在插入数据时使用序列
insert into student values (seq_student.nextval, '小
红', '女', 19, sysdate, 'hong@sxt.com', 102);
1.3 删除序列
drop sequence seq_student;

1. 索引(index)

为了提高查询效率, 可以建立类似目录的数据库对象, 实现数据快速查询, 这就是索引(Index)

1.1 索引的创建

1.1.1 自动创建

Oracle 对 primary key 和 unique 约束的列, 会自动创建索引.

1.1.2 手动创建

对于不是 primary key 和 unique 约束的列, 如果经常会被查询或用于排序, 可以手动给其创建索引, 例如:

create index idx_sname on student (sname desc);
1.2 索引的使用

索引被创建后, 查询时会自动生效, 提高查询效率.

1.3 索引的删除

drop index idx_sname;

1.4 索引的优缺点

1.4.1 优点：当数据量比较庞大时, 索引可以大大提高查询的效率

1.4.2 缺点：

a) 索引会单独存放, 索引过多会占用大量的存储空间;

b) 索引会降低 DML 的效率, 因为数据发生变化时, 还需要重新维护索引;

c) 对于唯一性不好的数据, 不适合创建索引.

1. 视图(view)

视图是从若干基本表和（或）其他视图构造出来的表. 视图中并不会存放数据, 只会存放视图的定义语句. 在用户使用视图时, 才去动态检索数据.

1.1 创建视图
a) 语法
create [or replace] view 视图名 as (查询) [with read only]
b) 创建简单视图
create or replace view v_student as (select * from student);
c) 可以对视图进行 DQL 和 DML 操作
-- 查询视图
select * from v_student;
-- 新增
insert into v_student values (seq_student.nextval, '小刚', '男', 20,
sysdate, 'gang@sxt.com', 102);
-- 修改
update v_student set age=21 where sno=2;
-- 删除
delete from v_student where sno=2;
1.2 只读视图
只读视图, 只能对视图进行查询(DQL)操作, 不能执行增删改(DML)操作
create or replace view v_student as (select * from
student) with read only;

1. 视图的作用

a) 限制对表格数据的访问

b) 相同数据的不同访问

c) 将复杂的查询简单化, 例如:

create or replace view v_student3 as (
 select c.*, count(s.sno) cnt
 from clazz c
 left join student s
 on c.cno=s.cno
 group by c.cno, c.cname, c.croom
);
select * from v_student3;

2. 删除视图

drop view v_student;

1. 事务(Transaction)

事务（Transaction）是一个操作序列。这些操作要么都做,要么都不做, 是一个不可分割的工作单元, 是数据库环境中的最小工作单元。

为什么需要事务？

事务包含了一组操作，这些操作可以是可以是一条SQL语句、一组SQL语句或整个程序。如果其中一个操作不成功，这些操作就都不会执行，前面执行的操作也会回滚原状态，保证了数据的一致性和完整性。就像银行的转账，张三给李四转账，只有当张三账户的钱转走了，并且李四账户的钱收到了之后转账事务才能提交。否则，如果张三账户钱转走了，在李四还没收到钱之前忽然断电或者断网了，本次转账就不会成功。张三和李四账户的状态会回滚到转账前的状态，保证数据一致性，保证数据不会出错。

1.1 事务的特性(ACID)

1.1.1 Atomicity(原子性)

原子性是指事务包含的所有操作要么全部成功, 要么全部失败回滚, 因此事务的操作如果成功就必须要完全应用到数据库, 如果操作失败则不能对数据库有任何影响.

1.1.2 Consistency(一致性)

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态, 也就是说一个事务执行之前和执行之后都必须处于一致性状态.

1.1.3 Isolation(隔离性)

隔离性是当多个用户并发访问数据库时, 比如操作同一张表时, 数据库为每一个用户开启的事务, 不能被其他事务的操作所干扰, 多个并发事务之间要相互隔离.

1.1.4 Durability(持久性)

持久性是指一个事务一旦被提交了, 那么对数据库中的数据的改变就是永久性的, 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作.

1.2 事务的提交和回滚

a) 提交, 在确保事务执行成功时, 应该将事务进行提交. 提交后, 数据被永久保存, 不能进行回滚.

commit;

b)回滚,当事务执行出现故障时,应该进行事务的回滚操作,本次事务的所有操作将被还原, 保证数据库的一致性.

rollback;

1. rowid 和 rownum

1.1 rowid

a) rowid 在记录创建时生成，而且是不变，直接指向硬件上的存储位置

b) 通过 rowid 查询是效率最高的, 但是 rowid 是由 Oracle维护的, 人力无法做到

1.2 rownum

rownum 是一个伪列, 查询的时候除非特别指定，否则不会显

示. 表示行号, 常用于控制查询返回的行数.

1.2.1 通过 rownum 进行 TOP-N 查询

当 rownum 和 order by 一起使用时，会首先选出符合 rownum条件的记录，然后再进行排序. 因此, 需要用子查询来完成.

例如: 查询工资排名前 5 的员工信息

select *
from (
 select e.* from emp e order by sal desc
)
where rownum<=5;

1.2.2 通过 rownum 进行分页查询

由于先要进行 where 条件判断, 满足条件后才能生成rownum, 所以导致 rownum 无法进行大于(>)和大于等于(>=)的判断. 此时, 需要使用嵌套子查询来实现.

例如: 分页查询员工信息, 按工资降序排序. 用 page 表示当前页数, size 表示每页显示的记录数, 则分页查询语句为:

select *
from (
 select rownum rn, t.*
 from (
 select * from emp order by sal desc
 ) t
 where rownum<=page*size
) tt
where tt.rn>(page-1)*size;