Orcale数据库基础学习

数据库的基础查询:

函数：

• nvl函数，nvl(string1,string2) 如果string1为 null,则结果为string2的值 ；
• decode函数，decode(condition,case1,express1,case2 , express2,….casen , expressn, expressionm) ；
• case when then else end 函数；
• count:统计记录数；
• max、min：最大值、最小值；
• sum:求和；
• avg:平均值；

decode和case when then else end函数的基本使用

select ename,
sal,
deptno,
decode(deptno,
10,
sal * 1.1,
20,
sal * 1.08,
30,
sal * 1.15,
sal * 1.2) raisesal
from emp;
--case when then else end
select ename,
sal,
deptno,
(case deptno
when 10 then
sal * 1.1
when 20 then
sal * 1.08
when 30 then
sal * 1.15
else
sal * 1.2
end) raisesal
from emp;

注意:

1、组函数仅在选择列表和Having子句中有效；

2、出现组函数，select只能由组函数或分组字段；

3、组函数不能用在where中，能使用的地方有select、having；

4、null不参与运算；

--统计员工数
select count(eno) from emp ;
--统计可以获得奖金的总数
--comm为null的不参与运算，比如员工表中有10条数据，其中有6条数据奖金值为null，那么使用以下sql语句，输出结果是4
select count(comm) from emp;

日期中的两个转换函数：

• to_date(c,m)->字符串以指定格式转换为日期，c为字符串，m为规定的格式，即字符串c需按照m格式给值；
• to_char(d,m)->日期以指定格式转换为字符串，d为日期型，m为转换格式；

其他函数：

• decode：decode(deptno,10,‘十’,20,‘二十’，默认值），注意这里的默认值是不符合前面的判定条件才给默认值。

分组：

• group by，将符合条件的记录进一步的分组
• 过滤组：having，过滤组信息，表达式的应用同where一致

select语句的结构：

select distinct * | 字段 | 表达式 | 	函数 as 别名 
from 表 表别名 
where 过滤行记录条件 
group by 分组字段列表 
having 过滤组 
order by 字段列表 asc | desc

sql语句的解析步骤：from->where->group by->having->order by;

注意：1）当select中出现分组函数，就不能使用非分组信息，但是可以使用group by字段对表中的数据进行分组，这时候group by 之后修饰的字段为分组字段，这时候该字段可以在select后使用；

having：过滤组

1. where：过滤行记录，不能使用组函数；
2. having：过滤组可以使用组函数；

行转列：

select name,
decode(course, '语文', score) 语文,
decode(course, '数学', score) 数学,
decode(course, '英语', score) 英语
from tb_student;

使用行转列前部分效果图：

使用行转列后的效果图：

rowid与rownum

rowid：ROWID 是 ORACLE 中的一个重要的概念。用于定位数据 库中一条记录的一个相对唯一地址值。通常情况下，该 值在该行数据插入到数据库表时即被确定且唯一。 ROWID 它是一个伪列，它并不实际存在于表中。它是 ORACLE 在读取表中数据行时，根据每一行数据的物理 地址信息编码而成的一个伪列。所以根据一行数据的 ROWID 能找到一行数据的物理地址信息。从而快速地定 位到数据行。数据库的大多数操作都是通过ROWID 来完 成的，而且使用ROWID 来进行单记录定位速度是最快 的。我们可以将其用于删除重复数据。

rownum：ROWNUM 是一种伪列，它会根据返回记录生成一个序列 化的数字。排序后的结果集的顺序号，每一个结果集 都 有自己顺序号 ，不能直接查询大于 1 的数。利用 ROWNUM，我们可以生产一些原先难以实现的结果输 出。 例如实现分页操作。

--如果某个数据中的某个table没有主键，即一个table中可能会有相同两条元组，即两条相同的数据，但是这时候我有一个需求---要求删除该表中的与自己相同的另外一条数据或者多条数据，那么这时候可以使用rowid来处理

--找出保留的rowid
select min(rowid) from tb_student group by name,course;
--删除
delete from tb_student where rowid not in
(select min(rowid) from tb_student group by
name,course);

rowid的实例图(每个表的每一行的rowid是唯一的，即可以根据这个对重复数据进行删除）：

rownum使用注意点：

• 必须排序
• 不能直接取大于 1 的数

--如果按照非主键排序，则rownum的创建先于排序前
select empno,ename,sal,rownum from emp order by sal;

--如果按照主键排序，则rownum的创建于排序后
select empno,ename,sal,rownum from emp order by empno;

--这时候查询到的数据为空，因为rownum是后于select，rownum是从1开始，而还没开始查询时，即可以看做是一个空表，当查到一个值时rownum=1，但是这里条件是5>=row>=3，所以查询到的结果是一个空的

--改善前
select e.*, rownum
from emp e
where rownum <= 5
and rownum >= 3;


--但是可以改善，即可以把已经查询到的一个结果集看做一个新表，这时候rownum已经被初始化了
--改善后
select e.*, rownum
from (select * from emp order by sal desc) e
where rownum <= 5
and rownum >= 3;

改善前效果截图：

改善后效果截图：

改善方案一结果图：

改善方案二结果图：

92语法中表连接：

笛卡尔积：

简而言之就是多个表数据的结合，例如有两个表A表、B表，其中A表有n个元组(即n行数据)，B表有m行数据，那么A×B就会有n×m行数据，数据库中代码如下：

select * from emp,dept;

等值连接:

在笛卡尔基础上，取条件列相同的值,但是查询到的结果集有两列数据相同，即没有把查询到的重复列去重。

select * from emp e,dept d where e.deptno=d.deptno;

非等值连接：

--查询员工基本信息以及工资等级
select * from emp, salgrade where sal between losal and hisal;

自连接：

特殊的等值连接(来自于同一张表)，即自己跟自己连接；

--带有(+)的为附表，没有的为主表 其中在这里我定义e1为员工表、e2为上级表
--查询员工及其上级基本信息，员工表为主表 上级表为从表
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);
--查询上级基本信息以及以下员工的基本信息，上级表为主表员工表为从表
select * from emp e2，emp e1 where e2.empno=e1.mgr(+);

外连接：

• 左外连接
• 右外连接

--主表是看where条件语句中没有带有+的  看查询语句表的中的逗号  主表在‘,’的左边就叫左外连接  主表在‘,’的右边就叫右外连接
--左外连接
select * from emp e,dept d where e.deptno=d.deptno(+);
select * from emp e left join dept d on e.deptno=d.deptno ;
--右外连接
select * from emp e,dept d where e.deptno(+)=d.deptno;
select * from emp e right join dept d on e.deptno=d.deptno ;

99语法中的表连接：

• 交叉连接cross join 等同于笛卡尔积；
• 自然连接(根据主外键来连接，两表连接起来的结果集中没有重复列)natural join -->等同于等值连接，但是也有一点点区别，即两表中的重复列去重了(主键外键只保留了其一)；
• join on|using连接—>等值连接 非等值连接 自连接；
• left|right [outer] join on|using -->外连接;
• full join on|using -->全连接满足直接匹配，不满足相等互补充null，确保所有表的记录都至少出现一次；

交叉连接：

--交叉连接
select * from emp cross join dept;

自然连接：

--自然连接
select * from emp natural join dept;

join using 连接：

指定同名字段做等值连接

--using连接(主外键列只有其一，没有相同的两列)
select * from emp join dept using(deptno);

join on连接：

--on连接(同时有主键列和外键列，有相同的两列)
select * from emp e join dept d on e.deptno=d.deptno;

外连接：

--左外连接
--92语法中的左外连接
select * from emp e,dept d where e.deptno=d.deptno(+);
--99语法中的左外连接
select * from emp e left join dept d on e.deptno=d.deptno ;
--右外连接
--92语法中的右外连接
select * from emp e,dept d where e.deptno(+)=d.deptno;
--99语法中的右外连接
select * from emp e right join dept d on e.deptno=d.deptno ;

全连接：

select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  full join (select 1 no, 'c' "name"
               from dual
             union
             select 3 no, 'd' "name"
               from dual) b
    on a.no = b.no;

视图view

视图：建立在表|结果集|视图上的虚表，有以下作用：

• 简化：select查询语句；
• 重用：封装select语句；
• 隐藏：内部细节；
• 区分：相同数据不同查询；

--视图的创建
create or replace view view_02 as select ename from emp;
--修改真实表中的数据 视图中的相应的数据也会被修改
update emp set ename='yangyang' where empno=7369;
select * from view_02;
--修改视图中的数据 真实表中的数据也会得到相应的修改
update view_02 set ename='yangyang' where ename='SMITH';

索引：

索引：提高查询速度的一种手段–>目录

• 唯一性较好字段适合建立索引；
• 大数据量才有效果；
• 主键|唯一：唯一索引；

表设计：

设计表首先应该按需遵循三范式：

1. 确定表名

2. 确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯 一)

   主键： 唯一标识一条记录（唯一并且非空） primary key

   唯一： 唯一 unique

   非空：不能为空 not null

   默认： 当没给值时使用给定一个默认值 defalut(默认值–需给相应类型数据)

   外键：参考其他表（自己）的某个（某些）字段 references 表名(引用键字段)

   检查：自定义的规则 check( 里面的条件与where条件书写格式一致 )

创建表时设定约束条件的三种方式：

• 第一种：直接在创建表时，在创建表的字段名后追加添加约束条件；
• 第二种：在创建表时，在末尾使用constraint对某些字段添加约束条件；
• 第三种：已经建完表后使用 alter 追加约束条件；

第一种使用案例：

create table shop(
 sid number(5) primary key,
 sname varchar2(10) not null
)
create table user(
--主键约束
userid number(5) primary key,
--外键约束
sid number(5) reference shop(sid),
--第二种方式添加外键约束
--constrain sid foreign key user(sid) reference shop(sid)
username varchar2(30) check(length(username)between 4 and 20) constraint nn_user_name not null ,
--nn_user_pwd是设置一个别名，即若插入数据时出现某种错误，使用该别名便于查找在哪个位置出现的插入数据的错误
userpwd varchar2(20) constraint nn_user_pwd not null ,
age number(3) default(18) check(age>=18),
gender char(3) default('男'),
email varchar2(30),
regtime date default(sysdate)
);

第二种使用案例：

create table tb_user(
userid number(5),
username varchar2(30) constraint
nn_user_name not null ,
--nn_user_pwd是设置一个别名，即若插入数据时出现某种错误，使用该别名便于查找在哪个位置出现的插入数据的错误
userpwd varchar2(20) constraint nn_user_pwd not null ,
age number(3) default(18) ,
gender char(2) default('男'),
email varchar2(30),
regtime date default(sysdate),
    
constraint pk_user_id primary key (userid),
constraint ck_user_name check(length(username)between 4 and 20),
constraint ck_user_pwd check(length(userpwd) between 4 and 18),
constraint ck_user_age check(age>=18),
constraint ck_user_gender check(gender in('男','女')),
constraint uq_user_email unique(email)
);

第三种使用案例：

create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)  
);
alter table tb_txt add constraint pk_txt_id primary key(txtid);
alter table tb_txt add constraint ck_txt_id check(length(title)>=4 and length(title)<=30);

--注意非空 默认
alter table tb_txt modify (title constraint
nn_txt_title not null) ;
alter table tb_txt modify (pubtime
default(sysdate));

三种级联删除操作规则：

--三种级联删除规则
    --必须先删除从表，然后才可以删除主表
    alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid)
    references tb_user(userid);
    --可以先删除主表，并且被从表引用的数据也会一起被删除
    alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on delete cascade ;
    --可以先删除主表，并且被从表引用的数据被置为null
    alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on delete set null;

为每个字段添加解释：

create table tb_txt(
    txtid number(10),
    title varchar2(32),
    txt varchar2(1024),
    pubtime date,
    userid number(5)  
    );
    --注释
    comment on table tb_txt is '文章表';
    comment on column tb_txt.txtid is '流水号,主键';
    comment on column tb_txt.title is '标题';
    comment on column tb_txt.txt is '正文';
    comment on column tb_txt.pubtime is '发布时间';
    comment on column tb_txt.userid is '发布人,外键，参考tb_user的userid列';

表的拷贝

拷贝已有表的结构

create table 表名 as select 字段列表 from 已有表 where 1！=1;
--实例：拷贝emp表中的结构
create table emp_his as select ename,sal from emp where 1!=1;

拷贝已有表的结构+数据

create table 表名 as select 字段列表 from 已有表 where 拷贝条件；
--实例：拷贝结构emp+数据
create table emp_his2 as select ename,sal from emp where sal>2000;

约束

在 oracle中所有的一切都是对象， 约束也是一个个的对 象，除了能创建约束我们还能对约束进行一些其他的操 作 。

查看某个用户的约束

select constraint_name, constraint_type
from user_constraints
where owner = upper('SCOTT');

查看表的约束

select constraint_name, constraint_type
from user_constraints
where table_name = upper('emp');

查看字段名+约束

select constraint_name, column_name
from user_cons_columns
where table_name = upper('emp');

约束的禁用与启用

ALTER TABLE tb_user disable constraint
nn_user_name;
ALTER TABLE tb_user enable constraint
nn_user_name;

删除约束

alter table tb_user drop constraint
uq_user_email cascade;

修改约束

--非空
alter table tb_user modify (username
varchar2(20));
--默认
alter table tb_user modify (age default null);

DDL

SQL语言结构：

DDL(数据定义语言)

用于操 作对象和对象的属性，这种对象包括数据库本身，以及 数据库对象，像：表、视图等等，DDL 对这些对象和属 性的管理和定义具体表现在 create、drop 和 alter 上。特 别注意：DDL 操作的“对象”的概念，”对象“包括对象及 对象的属性，而且对象最小也比记录大个层次。以表举 例：create 创建数据表，alter 可以更改该表的字段， drop 可以删除这个表，从这里我们可以看到，DDL 所站 的高度，他不会对具体的数据进行操作。

DDL的主要语句：

语句	作用
create	可以创建数据库和数据库的一些对象
alter	可以删除数据表、索引、条件约束等
drop	修改数据表定义及属性

删除表：

drop table 表名 (cascade constraints)
--删除表
drop table emp_his;
--主从表关系下删除表
--先删除从表 再删除主表 ；同时删除约束
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
--删除主表的同时级联删除约束
drop table emp_his cascade constraints;
--倘若只有在主外键约束条件下没有添加其他删除级联操作，如果使用：drop table 表名 cascade constraints 删除主表中的数据，从表中的数据仍然会保留

修改表结构

1. 修改表名 :rename to
2. 修改列名: alter table 表名 rename column to
3. 修改类型: alter table 表名 modify(字段 类型)
4. 修改约束: 先删除 后添加
5. 添加列: alter table 表名 add 字段 类型
6. 删除列:alter table 表名 drop column 字段

--修改表名
rename tb_txt to tb_txt_new;
--修改列名
alter table tb_txt_new rename column txtid
to tid;
--修改类型
alter table tb_txt_new modify(tid
varchar2(20));
--添加列
alter table tb_txt_new add col varchar2(30);
--删除列
alter table tb_txt_new drop column col;
select * from tb_txt_new;

事务

事务是指作为单个逻辑工作单元执行的一组相关操作。 这些操作要求全部完成或者全部不完成。使用事务是为 了保证数据的安全有效。

事务的特点

• 原子性：事务中所有数据的修改，要么全部执行，要么全部不执行。
• 一致性：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行 的所有数据修改，必须在所有相关的表中得到反映。
• 隔离性：事务应该在另一个事务对数据的修改前或者修改后进行访问。
• 持久性：保证事务对数据库的修改是 持久有效的，即使发生系统故障，也不应该丢失。

事务的隔离级别

• Orcale默认的隔离级别时read committed；
• Oracle 支持上述四种隔离级别中的两种:read committed 和 serializable。除此之外， Oralce 中还定义 Read only 和 Read write 隔离级别；
• Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集 ；
• Read write：它是默认设置，该选项表示在事务中可以有 访问语句、修改语句，但不经常使用。

脏读

事务 T1 更新了一行数据，还没有提交所做的修 改，T2 读取更新后的数据，T1回滚，T2 读取的数据无 效，这种数据称为脏读数据。

不可重复读

事务 T1 读取一行数据，T2 修改了 T1 刚刚 读取的记录，T1 再次查询，发现与第一次读取的记录不 相同，称为不可重复读。

幻读

事务 T1 读取一条带 WHERE 条件的语句，返回结 果集，T2 插入一条新纪录，恰好也是 T1 的 WHERE 条 件，T1 再次查询，结果集中又看到 T2 的记录，新纪录 就叫做幻读。

事务的开启

自动开启与DML之insert delete update

事务结束

1. 成功

• 正常执行完成的 DDL 语句：create、alter、drop ；
• 正常执行完 DCL 语句 GRANT、REVOKE ；
• 正常退出的 SQLPlus 或者 SQL Developer 等客户端 ；
• 如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接) ；
• 手动提交 ：使用 commit ；

2. 失败

• rollback，手动回滚
• 非法退出 意外断电

rollback只能对未提交的数据撤销，已经Commit的数据时无法撤销的，因为commit之后已经持久化到数据库中。

DML

DML（Data Manipulation Language 数据操控语言）用于 操作数据库对象中包含的数据，也就是说操作的单位是 记录。

DML的主要语句操作

语句	作用
insert	向数据表中插入一条数据
delete	删除数据表中的一条或多条记录，也可以删 除数据表中的所有记录，但是，它的操作对 象仍是记录
update	用于修改已存在表中的记录的内容

截断数据与删除数据区别：

即truncate与delete区别：

1、truncate -->ddl ，不涉及事务，就不能回滚 delete -->dml ,涉及事务，可以回滚 ；

2、truncate 截断所有的数据 delete 可以删除全部 或者 部分记录

3、truncate从结构上检查是否存在主外键，如果存在，不让删除 ；delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

truncate table tb_user;
delete from tb_user;

据库对象中包含的数据，也就是说操作的单位是 记录。

DML的主要语句操作

语句	作用
insert	向数据表中插入一条数据
delete	删除数据表中的一条或多条记录，也可以删 除数据表中的所有记录，但是，它的操作对 象仍是记录
update	用于修改已存在表中的记录的内容

截断数据与删除数据区别：

即truncate与delete区别：

1、truncate -->ddl ，不涉及事务，就不能回滚 delete -->dml ,涉及事务，可以回滚 ；

2、truncate 截断所有的数据 delete 可以删除全部 或者 部分记录

3、truncate从结构上检查是否存在主外键，如果存在，不让删除 ；delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

truncate table tb_user;
delete from tb_user;