投稿
  1. 极客之音首页
  2. 技术随笔

92连接法、99连接法

seven_ • • 技术随笔 • 阅读 237

导读：本篇文章讲解 92连接法、99连接法，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

一、92表连接(select 数据 from 表1，表2…)

1.笛卡尔积

-- 笛卡尔积  对乘效果
select * from emp,dept;
select * from emp,salgrade;
select ename e.deptno,dname from emp e,dept d;

2.等值连接（在笛卡尔积的基础上，条件相等的值）

-- 查询所有员工的信息,以及所在的部门信息
-- 数据: 员工信息  部门信息
-- 来源: emp dept
-- 等值连接
select * from emp,dept where emp.deptno=dept.deptno;
-- 注意:当使用同名字段时候,需要指明字段出处
select empno,ename,job,mgr,e.deptno,dname from emp e,dept d where e.deptno=d.deptno;

3.非等值连接 > < != <> between and

-- 非等值连接
-- 查询员工信息以及每个员工的薪资等级
select empno,ename,sal,grade from emp,salgrade where sal between losal and hisal;

4.自连接：特殊的等值连接（同一张表）

-- 自连接  一张表,当多张表使用
-- 查询有上级的员工的员工信息,以及这个员工的上级信息
-- 数据: 员工信息 上级信息
-- 来源: 员工表emp e1  上级表 emp e2
select * from emp e1,emp e2 where e1.empno=e2.mgr;

5.外连接

-- 外连接
-- 左外连接|左连接  右外连接|右连接 :主表在逗号的左边叫做左连接,主表在逗号的右边叫做右连接
-- 主表: 主表中的数据无论是否满足连接条件都要显示
-- 查询所有员工信息,以及这个员工的上级信息
-- 员工表作为主表
select * from emp e2,emp e1 where e1.mgr=e2.empno(+);--右连接
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);--左连接
-- 10,20部门
select * from emp e1,emp e2 where e1.mgr=e2.empno(+) and e1.deptno in(10,20) order by e1.deptno;--左连接

二、99连接select 数据 from 数据来源1 join 数据来源2；

1.交叉连接

-- 查emp表和dept表信息
select * from emp e cross join dept d; 
select * from emp e cross join salgrade s;

2.自然连接

-- 自然连接  natural join 自动帮你做等值连接  同名字段|主外键关系
 select empno,ename,deptno,dname from emp e natural inner join dept d;
 -- emp和dept 信息表
select * from emp natural inner join dept d;

3.using连接

 -- join using(字段) 指明对哪一个字段做等值连接
 select  empno,ename,deptno,dname from emp inner join dept  using(deptno);

4.join on 连接

-- 数据来源1 join 数据来源2  on 连接条件(等值|非等值)  同名字段需要指明出处
select empno,ename,emp.deptno,dname from emp join dept on emp.deptno=dept.deptno;
-- 非等值连接
-- 员工信息和工资等级
select * from emp e inner join salgrade s on e.sal between losal and hisal;
select * from emp e join salgrade s on e.sal between losal and hisal;
-- 部门为30员工信息和工资等级信息
select *
  from emp
  join dept
    on emp.deptno = dept.deptno
  join salgrade
    on sal between losal and hisal
 where emp.deptno = 30;

5.外连接

-- 外链接  left join  | right join
-- 主表
-- 所有员工信息和上级信息
select * from emp e1 left join emp e2 on e1.mgr = e2.empno;
select * from emp e1 right join emp e2 on e1.mgr=e2.empno;

6.全连接

 -- 全连接  union

select 1 no, 'a' "name" from dual union select 2 no, 'b' "name" from dual;
 
select 1 no, 'c' "name" from dual union select 3 no, 'd' "name" from dual;
 
-- 内连接  满足条件显示
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;
    
-- 左外链接 a表中的数据满不满足条件都显示  
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  left join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;
-- 右外链接 b表中的数据满不满足条件都显示  
select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) a
  right join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) b
    on a.no = b.no;

-- 全链接 a,b表中的数据满不满足条件都显示  
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;

三、视图和索引

1.视图

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

–视图是虚拟表,操作视图其实数据还是在对应的表中,
–使用视图最大的好处可以简化select语句代码
–不是所有的账号都有权限创建视图
–需要授权
– 1)切换到管理员账号sys
– 2) grant dba to 账户名;授权
– 3)回收权限revoke dba from 账号名;

2）创建视图

-- 创建视图
-- create or replace view 视图名 as select语句 [with read only];  权限问题
create or replace view vw_haha as select empno,ename,sal from emp where deptno=30 with read only;
-- 添加权限
grant dba to SCOTT;
-- 查看 视图
select * from vw_haha;
-- 删除视图
drop view vw_haha;
-- update 表名 set 字段=值 [,....] where 过滤行记录;--视图就是简化查询
update vw_haha set sal=200 where empno=7499;
update vw_haha set sal=200  where empno = 7499;

-- 对每个部门中所有经理人求平均薪资,查询平均薪资最低的部门的部门民称
-- 所有经理人
select distinct mgr from emp where mgr is not null;
-- 创建视图存储所有的经理人
create or replace view vw_mgr as select distinct mgr from emp where mgr is not null with read only;
select * from vw_mgr;
-- 对所有的经理人进行分组,查到部门编号,经理人的平均薪资
select deptno, avg(sal)
  from emp
 where empno in (select distinct mgr from emp where mgr is not null)
 group by deptno;
-- 最低平均薪资
select min(avg(sal))
  from emp
 where empno in (select distinct mgr from emp where mgr is not null)
 group by deptno;
-- 判断哪一个部门的薪资与最低平均薪资相等,获取到这个部门的部门编号
select deptno
  from (select deptno, avg(sal) avg_sal
          from emp
         where empno in (select distinct mgr from emp where mgr is not null)
         group by deptno)
 where avg_sal =
       (select min(avg(sal))
          from emp
         where empno in (select distinct mgr from emp where mgr is not null)
         group by deptno);
-- 根据部门编号获取部门名称
select dname
  from dept
 where deptno =
       (select deptno
          from (select deptno, avg(sal) avg_sal
                  from emp
                 where empno in
                       (select distinct mgr from emp where mgr is not null)
                 group by deptno)
         where avg_sal =
               (select min(avg(sal))
                  from emp
                 where empno in
                       (select distinct mgr from emp where mgr is not null)
                 group by deptno));
-- 视图
select dname
  from dept
 where deptno = (select deptno
                   from (select deptno, avg(sal) avg_sal
                           from emp
                          where empno in (select * from vw_mgr)
                          group by deptno)
                  where avg_sal = (select min(avg(sal))
                                     from emp
                                    where empno in (select * from vw_mgr)
                                    group by deptno));

2.索引

l 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

l 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

l 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

l 索引一旦建立，在表上进行 DML 操作时（例如在执行插入、修改或者删除相关操作时），oracle 会自动管理索引，索引删除，不会对表产生影响

l 索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变

l oracle 创建主键时会自动在该列上创建索引

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

1、唯一性较好字段适合建立索引

2、大数据量才有效果

3、主键|唯一: 唯一索引

-- 索引
-- 是数据库的对象之一,是透明的,有没有索引,sql都一样
-- 大量的数据的查询,如果数据量比较小,如果不是大量做查询,二十大量的执行增删改的操作,反而会降低效率,因为需要维护索引
-- 字典的目录
-- create index 索引名 on表名 (字段列表...)
-- drop index 索引名
create index i_sal on emp (ename,sal);
drop index i_sal;

四、设计表

1.设计表

-- 表设计
-- 三范式的规范
-- 表的名称, 字段,类型,要求(约束)
-- 表和表之间的关系   一对一    一对多|多对一(主外键)   多对多(中间表)

-- 创建表 和添加约束
-- 先创建表,后续为这个表的什么字段添加约束
-- 创建表的同时添加约束
      -- 创建表 字段后直接添加约束  1)默认的约束名  2)指定约束名
      -- 字段定义完成之后,统一为不同的字段的添加约束
      
-- DDL 定义语句    创建表 create 删除表drop  修改alter
-- 创建表
--create table 表名(
      --字段名 类型  约束,
      --字段名 类型  约束,
     -- 字段名 类型 ,
     --约束;
     --约束....
--)
-- 追加约束

2.创建表

-- 创建表
create table sxt_student(
       sid number(5), --5为有效数字  (5,2) 5位有效数字其中2位是小数
       sname varchar2(4 char),  --默认字节数 如果想要指定字符个数 varchar2(4 char)
       sage number(3),
       gender char(3), --默认3个字节
       birthday date
)
 
 -- 查表
select * from sxt_student;
-- 插入
insert into sxt_student values(123,'郑爽呵呵',18,'女',sysdate);
insert into sxt_student values(123,'古力娜扎',18,'女',sysdate);
-- 删除表
drop table sxt_student;

五、创建表

1.创建表（添加约束+默认约束）

-- 创建表的同时添加约束  1)默认约束名  优点:简单  缺点:不便于后期维护
-- 学生表

create table sxt_student(
       -- 主键约束（非空+唯一）
       sid number(5) primary key, --5为有效数字  (5,2) 5位有效数字其中2位是小数
       -- 非空约束 not null
       sname varchar2(4 char) not null,  --默认字节数 如果想要指定字符个数 varchar2(4 char)
       -- 检查约束 check()
       sage number(3) check(sage>=0 and sage<=150),
       -- 检查约束 男 女
       gender char(3) check(gender in('男','女')), --默认3个字节
       -- 默认 约束
       birthday date default(sysdate),
       -- 唯一约束 手机号不能重复
       phoneNum number(11) unique
    )

2.创建表（添加约束+指定约束）

-- 创建表的同时添加约束  2)字段后添加指定约束名 3)字段声明后,表结构结束之前添加约束

create table sxt_student(
       -- 主键约束（非空+唯一）
       sid number(5) constraints pk_student_sid primary key, -- 5为有效数字  (5,2) 5位有效数字其中2位是小数
       -- 非空约束 not null
       sname varchar2(4 char) constraints sname_not_null not null,  -- 默认字节数 如果想要指定字符个数 varchar2(4 char)
       -- 检查约束 check()
       sage number(3) check(sage>=0 and sage<=150),
       -- 检查约束 男 女
       gender char(3), -- check(gender in('男','女'))--默认3个字节
       -- 默认 约束
       birthday date default(sysdate),
       -- 唯一约束 手机号不能重复
       phoneNum number(11),      -- unique
       cid number(5),
       -- 添加约束
      -- constraints ck_gender check(gender in('男','女'));
       constraints un_phoneNum unique(phoneNum),
       constraints fk_sxt_class_cid foreign key(cid) references sxt_class(cid) 
)
-- 表结构结束之后追加约束
alter table sxt_student add constraints ck_gender check(gender in('男','女'));
alter table sxt_student drop constraints ck_gender;
-- 加入注释
comment on table sxt_student is '学生表';
comment on column sxt_student.sid is '学生编号,主键';
-- 看表 右键 edit 给程序员看的注释

六、表的其他操作(ddl)

1.修改表结构

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;

2.删除表

-- 删除数据
-- 被别的表引用，需要处理，如果没有引用就直接删除
delete from sxt_class where cid = 202;
-- 处理：
-- 方法1：先删除引用表的数据
delete from sxt_student where sid = 125;
-- 方法2：添加外键约束的时候 on delete set null,当主表删除是，从表引用字段值null
-- 方法3： 添加外键约束时候 on delelt cascade 删除主表数据的时候联删除从表引用了这些数据
-- 2.删除主表中的数据的时候,
       -- 这个主表数据如果没有被从表中的数据引用,可以直接删除
       -- 这个主表数据如果已经被从表中的数据引用,需要处理
       -- 1)先删除从表中引用了的数据  再删除主表的这个数据     
       -- 2) 添加外键约束的时候 on delete set null,当主表数据被删除时,从表引用这条数据的外键字段值为null                                    --3) on delete cascade 删除主表数据的时候级联删除从表中引用了的这些数据
       
-- 班级表   主表
create table sxt_class(
       cid number(5) primary key,
       cname varchar2(15) not null
)

-- 学生表  从表
create table sxt_student(
       -- 主键约束  (非空+唯一)
       sid number(5) primary key, 
       -- 非空约束 not null
       sname varchar2(4 char) constraints sname_not_null not null, 
       cid number(5),
       constraints fk_sxt_class_cid foreign key(cid) references sxt_class(cid) on delete cascade
    -- on delete set null
)

select * from student;
select * from sxt_class;
-- 删除表
drop table sxt_student;
-- 删除表与表之间的约束（外键）
drop table sxt_class cascade constraints;

-- 添加数据
insert into sxt_student values(123,'古力娜扎',202);
insert into student values(sq_class_cid.nextval,'迪丽热巴',206);

insert into sxt_student(sid,sname) values(01,'lisi');

insert into sxt_class values(202,'java33期');
insert into sxt_class values(203,'java34期');
insert into sxt_class values(sq_class_cid.nextval,'java34期');
-- 删除数据
-- 被别的表引用，需要处理，如果没有引用就直接删除
delete from sxt_class where cid = 202;
-- 处理：
-- 方法1：先删除引用表的数据
delete from sxt_student where sid = 125;
-- 方法2：添加外键约束的时候 on delete set null,当主表删除是，从表引用字段值null
-- 方法3： on delelt cascade 删除主表数据的时候联删除从表引用了这些数据

3.截断数据

truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在 外键关联

截断数据同时 从结构上检查

create table emp_his as select * from emp where 1=1;
select * from emp_his;
--截断所有的数据
truncate table emp_his;
--不能截断: truncate table dept;

4.序列

-- 序列工具  帮助我们管理为表中的主键字段添加值使用的
-- create sequence 序列名 start with 起始值 increment by 步进;
create sequence sq_class_cid start with 200 increment by 1;
-- currval 当前值
-- nextval 下一个值,获取最新值
-- 注意:第一次要使用nextval
select sq_class_cid.nextval from dual;
select sq_class_cid.currval from dual;
-- 删除drop sequence 序列名
drop sequence sq_class_cid;

七、事务

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

1.事务有一下四个特点：（ACID）**

1、 原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。

2、 一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。

3**、 隔离性（Isolation）**：事务应该在另一个事务对数据的修改前或者修改后进行访问。

4、 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。

Oracle默认的隔离级别是 read committed。

Oracle 支持上述四种隔离级别中的两种:read committed 和serializable。除此之外，

Oralce 中还定义 Read only 和 Read write 隔离级别。

Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集。

Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。

丢失更新：两个事务同时存储， 一个存储 100 ， 一个存储 200，最终可能至存储了

200或者 100，那另一个的更新就没成功，即结果不为预想的 300

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

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

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

2、事务的开启

自动开启于 DML 之 insert delete update

3、事务的结束

1) 成功

l 正常执行完成的 DDL语句：create、alter、drop

l 正常执行完 DCL语句 GRANT、REVOKE

l 正常退出的 SQLPlus或者 SQL Developer 等客户端

l 如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)

l 手动提交 ：使用 commit

2) 失败

l rollback ，手动回滚

l 非法退出 意外的断电

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

八、DML(数据操纵语言)

1.理解

insert 注册 向数据表张插入一条记录
update 修改密码 用于修改已存在表中的记录的内容
delete 退出、删除、剔除会员 删除数据表中的一条或多条记录
select 登录|查看会员

2.insert

insert into 表名 [(字段列表)] values(值列表); 添加记录

insert into 表(指定列) select 查询列 from 源表 where 过滤数据; insert into 表(指定列) values(值列表); 
insert into 表名  select 查询列 from 源表 where 过滤数据; insert into 表名  values(值列表 );

3.update

update为修改数据

update 表名 set 字段=值 [,…] where 过滤行记录;

select * from tb_user; 
--重置所有人员的密码  8888 
update tb_user set userpwd=8888 where 1=1; rollback;

4.delete

delete删除数据

delete [from] 表名 where 过滤行记录

说明：

1、delete 可以删除指定部分记录，删除全部记录

2、记录上存在主外键关联时， 删除存在关联的主表的记录时,注意 参考外键约束， 约束强制不让删除 先删除从表 再删除主表

-- 删除全部数据 
delete from tb_user where 1=1;

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/121483.html

赞 (0)
seven_的头像seven_bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！