数据库命令的深入学习

一.连表查询

​ 当要查询的数据来自于不同的表中,可以使用连表查询

1. 92语法

​ 表和表之间使用,连接
select 数据 from 数据来源1,数据来源2,数据来源3…
笛卡尔积 (对乘)

(1)内连接

(内部做满足条件显示不满足不显示)和外链接(有的表中的数据不满足条件也可以显示)
连接条件: where中定义连接条件 等值连接 非等值连接
注意:同名字段必须指明出处

select 数据 from 数据来源1,… where 行过滤条件|表连接条件 group by 分组字段… having 组过滤条件 order by 排序字段…;

–执行流程: from —> where --> group by —> having —> select —> order by

--等值连接:两个表的连接字段,不定义是同名字段或者主外键关系的,数据类型需要保持一致
--查询员工的信息和所在的部门信息
select * from emp, dept where emp.deptno = dept.deptno;
select * from emp e, dept d where e.deptno = d.deptno;
select * from emp , dept where emp.ename = dept.dname;

--select 数据 from 数据来源1,....  where 行过滤条件|表连接条件  group by 分组字段.. having 组过滤条件 order by 排序字段..;
--执行流程: from --- where--group by ---having ---select ---order by
--查询30部门的员工信息和所在部门信息
select empno,ename,d.deptno,dname from emp e,dept d where e.deptno = d.deptno and e.deptno!=30 order by e.deptno desc; --先连接后判断,相对效率较低,连接数据多
--先过滤后连接
--select * from (30部门员工信息) e,(30部门的部门信息) d;
select * from (select * from emp where deptno = 30) e,(select * from dept where deptno=30) d;

--非等值连接
--查询2500工资所在的等级信息
select * from salgrade;
select grade from salgrade where 2500 between losal and hisal;

--查询的所有的员工的信息以及对应的薪资等级
--数据:员工信息,薪资等级
--来源: emp,salgrade
--条件:sal between losal and hisal;
select * from emp,salgrade where emp.sal between losal and hisal;

--30部门的,薪资>1500的员工信息,所在的部门信息,工资等级信息
select *
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and sal between losal and hisal
   and e.deptno = 30
   and sal > 1500;

(2)外连接

​ 左外连接和右外连接

​ 主表:主表中的数据无论是否满足条件都会显示

​ 主表在左边就是左连接,主表在右边就是右连接

--做表连接的时候,要满足连接条件才能显示,如果想要某张表中的数据无论是否满足条件都显示,可以使用外链接
--外连接:左外连接和有外连接接
--主表:主表中的数据无论是否满足条件都会显示
--查询所有员工信息以及上级经理人信息  员工表作为主表
--emp e1,emp e2  主表在左边就是左连接,主表在右边就是右连接
select * from emp e1,emp e2 where e1.mgr= e2.empno(+);  --左连接
select * from emp e2,emp e1 where e1.mgr= e2.empno(+);  --右连接

2. 99语法

select 数据 from 数据来源1 join 数据来源2;
笛卡尔积 (对乘) cross join

--select 数据 from 数据来源1 join 数据来源2; 
--笛卡尔积  对乘   cross join
select * from emp e cross join dept d; --99
select * from emp,dept;  --92

1)内连接

​ (inner) join inner默认可以不写

(1)自然连接

​ 自动做等值连接

​ 注意: 同名字段不要使用限定词

​ natural join 自动帮你做等值连接 同名字段|主外键关系

​ join using(字段) 指明对哪一个字段做等值连接

(2)等值连接

--(inner) join  内连接
--等值连接
--自动做等值连接
--注意: 同名字段不要使用限定词
--自然连接  natural join 自动帮你做等值连接  同名字段|主外键关系
  select empno,ename,deptno,dname from emp e  natural inner join dept d;
  --join using(字段) 指明对哪一个字段做等值连接
  select  empno,ename,deptno,dname from emp inner join dept using(deptno);
  
-- 数据来源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
  join dept
    on emp.deptno = dept.deptno
  join salgrade
    on sal between losal and hisal
 where emp.deptno = 30;

(3)外链接

外链接: left join | right join 左外链接|右外链接

全连接: 结果集 union 结果集

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

--全连接 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;

3.视图

视图是虚拟表

操作视图其实数据还是在对应的表中,使用视图最大的好处可以简化select语句代码

不是所有的账号都有权限创建视图

1)授权

​ 1)切换到管理员账号sys

​ 2) grant dba to 账户名; 授权

​ 回收权限 revoke dba from 账号名;

2)创建视图

create or replace view 视图名 as select语句 [with read only];

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

--update 表名 set 字段=值 [,....] where 过滤行记录;
update vw_haha set sal=200  where empno = 7499;

grant dba to SCOTT;
--对每个部门中所有经理人求平均薪资,查询平均薪资最低的部门的部门民称
--所有的经理人
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));

3)修改

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

4.索引

--索引
--是数据库的对象之一,是透明的,有没有索引,sql都一样
--大量的数据的查询,如果数据量比较小,如果不是大量做查询,二十大量的执行增删改的操作,反而会降低效率,因为需要维护索引
--字典的目录
select * from emp where sal>1500;

--create index 索引名 on表名 (字段列表...)
--drop index 索引名

5.表设计

​ 三范式的规范

​ 表的名称, 字段,类型,要求( 一对多|多对一(主外键) 多对多(中间表)

--DDL 定义语句    创建表 create 删除表drop  修改alter
--创建表
--create table 表名(
     --字段名 类型  约束,
     --字段名 类型  约束,
     -- 字段名 类型 ,
     --约束;
     --约束....
--)
--追加约束

--创建表,不添加约束
--学生表
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
)
--分析: 学堂  学生  老师  班级  定义表
--分析: 学生的id等字段应该不能重复等其他要求 解决办法: 1) 逻辑方面,代码层面上进行判断  2)为数据库中的表的字段添加约束要求

--创建表的同时添加约束  1)默认约束名  优点:简单  缺点:不便于后期维护
--学生表
create table sxt_student(
       --主键约束  (非空+唯一)
       sid number(5) primary key, 
       --非空约束 not null
       sname varchar2(4 char) not null, 
       --检查约束  check() 
       sage number(3) check(sage >=0 and sage<=150),
       --检查约束 '男' '女'
       gender char(3) check(gender in ('男','女')),
       --默认约束
       birthday date default(sysdate),
       --唯一约束  手机号不能重复
       phoneNum number(11) unique
)

--班级表
create table sxt_class(
       cid number(5) primary key,
       cname varchar2(15) not null
)

6.约束

创建表 和 添加约束

--先创建表,后续为这个表的什么字段添加约束
--创建表的同时添加约束
      --创建表 字段后直约束)
--表和表之间的关系   一对一接添加约束  1)默认的约束名     2)指定约束名
      --       字段定义完成之后,统一为不同的字段的添加约束
      
--创建表的同时添加约束  1)默认约束名  优点:简单  缺点:不便于后期维护

--创建表的同时添加约束  2)字段后添加指定约束名 3)字段声明后,表结构结束之前添加约束
create table sxt_student(
       --主键约束  (非空+唯一)
       sid number(5),      -- constraints pk_sxt_student_sid primary key, 
       --非空约束 not null
       sname varchar2(4 char) constraints sname_not_null not null, 
       --检查约束  check() 
       sage number(3) constraints ck_sage check(sage >=0 and sage<=150),
       --检查约束 '男' '女'
       gender char(3),
       --默认约束
       birthday date default(sysdate),
       --唯一约束  手机号不能重复
       phoneNum number(11),
       --外键约束  学生所在的班级
       cid number(5), --constraints fk_sxt_class_cid  references sxt_class(cid),--references sxt_class(cid),
       
       --添加约束 
       constraints pk_sxt_student_sid primary key(sid),
       --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 '学生编号,主键';
comment on column sxt_student.sname is '学生姓名';

7. DDL 和 DML

--班级表   主表
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),
       -- 添加外键约束的时候 on delete set null,当主表数据被删除时,从表引用这条数据的外键字段值为null
       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;
delete from sxt_student where sid = 125;

8.主从表关系

当有主从表关系的两张表

1)删除表

(1)默认先删除从表,后删除主表

(2)删除主表并同时删除主外键约束 cascade constraints

2)删除数据

​ 这个主表数据如果没有被从表中的数据引用,可以直接删除

​ 这个主表数据如果已经被从表中的数据引用,需要处理

​ (1)先删除从表中引用了的数据,再删除主表的这个数据

​ (2) 添加外键约束的时候 on delete set null,当主表数据被删除时,从表引用这条数据的外键字段值为null

​ (3) on delete cascade 删除主表数据的时候级联删除从表中引用了的这些数据

rename sxt_student to student;

--序列工具  帮助我们管理为表中的主键字段添加值使用的
--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;