92语法
–当要查询的数据来自于不同的表,需要使用表连接
–select 数据 from 数据来源1,数据来源2; 对乘 笛卡尔积
select * from emp,dept; --笛卡尔积 48条
–select 数据 from 数据来源1,数据来源2 where 表连接条件|行过滤条件;
select * from emp,dept where emp.deptno = dept.deptno;
select ename,sal,e.deptno from emp e,dept d where e.deptno = d.deptno; --同名字段指明出处
–查询有上级的员工的信息以及上级经理人信息
–来源: 员工表e1 经理人 e2
–连接条件: e1.mgr = e2.empno
select * from emp e1,emp e2 where e1.mgr = e2.empno; --11条,有一个king没有上级
外连接
–外链接
–当你想要表连接中某一张表中所有的数据全部展示,无论是否满足满足连接条件都显示,可以把这张表设置为主表
–在外连接中主表的表中所有数据都能展示
–如何设置外连接中的主表,在连接条件位置 主表对象加(+)
–左外连接 : 主表在逗号左边就是左连接
–右外连接 : 主表在逗号右边就是右连接
–查所有员工的信息以及上级经理人信息
–员工表为主表
select * from emp e1,emp e2 where e1.mgr = e2.empno(+);
select * from emp e2,emp e1 where e1.mgr = e2.empno(+);
99语法(join)
内连接 (inner) join
–笛卡尔积 对乘
–cross join
select * from emp inner cross join dept; --99
select * from emp,dept;
–查询30部门的员工信息以及所在部门信息
–99
select *
from (select * from emp where deptno in 30)
cross join (select * from dept where deptno in 30);
–92
select *
from (select * from emp where deptno in 30),
(select * from dept where deptno in 30);
等值连接
–自然连接 natural join 自动根据同名字段|主外键关系
select ename,sal,deptno from emp natural join dept;
–同名字段不能指明出处
–jion using(等值连接字段名) 当存在多个同名字段,可以指明使用哪一个做等值连接
select ename,sal,deptno from emp join dept using(deptno);
– 数据来源1 join 数据来源2 on 连接条件 ; 即可以实现等值连接 可以实现非等值连接
select * from emp e join dept d on e.deptno = d.deptno;
非等值连接
–查询员工信息以及每一个员工的薪资等级
select * from emp e join salgrade s on e.sal between s.losal and s.hisal;
– 查询非20部门并且薪资>1500的员工信息以及薪资等级信息
select *
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
where e.deptno != 20
and sal > 1500
order by sal desc;
外链接
–想要某张表中不满足连接条件的数据都显示,把这张表定义为主表
–左外 left join
–右外 right join
select * from emp e1 right join emp e2 on e1.mgr = e2.empno;
–全连接 full join 两张表都作为主表
select * from emp e1 full join emp e2 on e1.mgr = e2.empno;
rowid
–rowid 和 rownum 伪列(表没有,但是可以在select查询)
–rowid 相当于对象的地址,区分表中的数据,是记录的唯一,在数据插入表中时就存在
–作用: 可以多相同数据做去重(没有主键的表)
select *
from dept
where rowid in (select rowid from dept where deptno = 10);
–实现去重,表中没有主键,但是存在多条重复数据,重复数据只保留一条,实现去重
–查询要保留的那些数据的rowid
--select distinct id,name,course,score,rowid from tb_student;
select max(rowid) from tb_student group by id;
–查到要删除的数据的rowid
select rowid from tb_student where not rowid in(select max(rowid) from tb_student group by id);
–删除数据 delete from 表名 where 条件; 满足条件的语句删除
delete from tb_student
where rowid in
(select rowid
from tb_student
where not rowid in (select max(rowid) from tb_student group by id));
rownum
–rownum 伪列 结果集的序号 只要有select就有结果集就有rownum,每一套结果集都有自己的rownum
–规律: 把以确定结果集中的数据从第一个开始 设置rownum,从1开始,依次+1
–优点: 有规律,规律可循,是数字,可以进行判断
–如果在使用rownum值判断之前就已经确定了一个结果集,这个结果集中的rownum就是已经确定的
–再嵌套一个select
select empno,ename,rownum n from emp; --数据来源 确定rownum
select *
from (select empno, ename, rownum n from emp)
where n >= 5
and n <= 10; --where中rownum
要使用别名,确定内部select语句的字段,如果直接写rownum,被认为是外部select语句的
–如果存在排序,rownum的序号可能出现问题(乱号),如果:根据主键进行order by,先排序再rownum,如果根据其他字段排序,一般会先rownum,再order by
select deptno,dname,rownum from dept order by deptno;
select empno,ename,deptno,rownum from emp order by deptno;
–如果rownum乱掉怎么办?
–解决方案: 外层嵌套一个select
select empno,ename,deptno,rownum num from emp order by deptno;
–以确定的有规律的rownum,如果判断,根据这个rownum判断,把当前select当做数据源使用
select empno, ename, deptno, rownum 外层的rownum, num 内层rownum
from (select empno, ename, deptno, rownum num from emp order by deptno);
–分页
–按照工资降序排序 查询第三页的数据, 每一页显示4条记录
select * from (select empno, ename, deptno,sal,rownum n
from (select * from emp order by sal desc))
where n between 4*3-3 and 4*3;
拓展,同时显示工资等级
select * from (select empno, ename, deptno,sal,grade,rownum n
from (select * from emp e join salgrade s on e.sal between s.losal and s.hisal order by sal desc))
where n between 4*3-3 and 4*3;
试图
–create or replace view 试图名字 as 结果集 with read only;
–建立在表和结果集之间的就是试图,其实也是结果集,但是这个结果集可以被存储,以后可以查询试图中的数据
–最大的优点:就是封装,简化sql
–合理使用试图,不要过于使用
create or replace view vm_emp as select empno,ename, sal,deptno from emp where deptno in (20,30) with read only;
–删除试图
drop view vw_emp;
–思考题(难,存疑):*
–查询每个部门中所有经理的人的平均薪资,以部门为单位,求出最低平均薪资的那个部门的部门名称
–找到所有经理人
select distinct mgr from emp where mgr is not null;
–打印所有经理人信息,分组
select *
from emp
where empno in (select distinct mgr from emp where mgr is not null);
–计算每组经理人平均薪资,部门编号
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, avg(sal)
from emp
where empno in (select distinct mgr from emp where mgr is not null)
group by deptno
having 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 emp
where empno in (select distinct mgr from emp where mgr is not null)
group by deptno
having avg(sal) = (select min(avg(sal))
from emp
where empno in (select distinct mgr
from emp
where mgr is not null)
group by deptno));
–创建试图简化代码
create or replace view vw_mgr as select distinct mgr from emp where mgr is not null;
select * from vw_mgr;
select dname
from dept
where deptno = (select deptno
from emp
where empno in (select * from vw_mgr)
group by deptno
having avg(sal) = (select min(avg(sal))
from emp
where empno in (select * from vw_mgr)
group by deptno));
权限不够
–切换管理员sys用户
–授权: grant dba to scott;
–回收: revoke dba from scott;
索引(数据库优化)
–索引
–是数据库的对象之一
–字典的目录
–只有在大量数据查询的时候效率较高
–大量数据 查询,如果增删改效率降低,因为对象要维护
–索引的创建与删除完全不影响字段的使用
–唯一字段适合设置索引
–oracle自动为主键设置索引
select * from emp where sal>900;
–创建索引
–create index 索引名 on表名 (字段列表…)
create index index_sal on emp(sal);
–删除索引
–drop index 索引名
drop index index_sal;
表设计
–表设计
–表 表名 字段 约束 表与表之间的关系
–三范式
–表与表之间的关系: 一对一 一对多|多对一(主外键) 多对多{中间表}
–创建表与约束问题
–1)创建表的同时不创建约束, 结束后追加约束
–2)创建表的同时为字段添加约束
–3)创建表的结构结束之间添加约束
–约束的添加: 1)物理约束 :表中字段上添加
2)逻辑约束:java代码上使用逻辑判断
–主键 外键 非空 唯一 检查
–1)创建表的同时不添加约束
create table 表名(
字段 字段类型,
字段 字段类型,
…
)
create table sxt_student(
sid number(5), --5代表有效数字 (5,2)其中2为是小数位
sname varchar2(15), --可变长字符 默认字节个数 指明字符个数:(5 char)
sage number(3),
sgender char(1 char), --定长字符
hiredate date
)
–2)创建表的同时添加约束 1)字段后直接添加约束,没有约束名 不便于后期维护,但是编写简单 2)字段后直接添加约束包括约束名
create table sxt_student(
--学生编号 主键约束
sid number(5) primary key,
--学生姓名 非空约束
sname varchar2(15) not null unique,
--年龄 检查约束0~150
sage number(3) check(sage between 0 and 150),
--性别 检查约束 '男' '女'
sgender char(1 char) check(sgender in('男','女')),
--入学日期 默认值 sysdate
hiredate date default(sysdate),
cid number(5)
)
–3)字段后添加约束并指定约束名 constraints
```sql
```sql
create table sxt_student(
--学生编号 主键约束
sid number(5) constraints pk_sxt_student_sid primary key,
--学生姓名 非空约束
sname varchar2(15) constraints sxt_student_sname_notnull not null,
--年龄 检查约束0~150
sage number(3) check(sage between 0 and 150),
--性别 检查约束 '男' '女'
sgender char(1 char),
--入学日期 默认值 sysdate
hiredate date default(sysdate),
cid number(5),
--创建表结构结束前 添加约束
constraint ck_user_pwd check(length(userpwd) between 4 and 18),
-- constraints pk_sgender check(sgender in('男','女')),
constraints sxt_student_sname_unique unique(sname) )
–后续追加约束
alter table sxt_student add constraints pk_sgender check(sgender in('男','女'));
–删除约束
alter table sxt_student drop constraints sxt_student_sname_notnull;
–加入注释
comment on table sxt_student is '学生表';
comment on column sxt_student.sid is '学号,主键';
comment on column sxt_student.sname is '学生姓名';
comment on column sxt_student.hiredate is '入学日期';
comment on column sxt_student.sage is '年龄';
comment on column sxt_student.sgender is '性别';
comment on column sxt_student.cid is '班级编号';
create table sxt_student(
--学生编号 主键约束
sid number(5) constraints pk_sxt_student_sid primary key,
--学生姓名 非空约束
sname varchar2(15) constraints sxt_student_sname_notnull not null,
--年龄 检查约束0~150
sage number(3) check(sage between 0 and 150),
--性别 检查约束 '男' '女'
sgender char(1 char),
--入学日期 默认值 sysdate
hiredate date default(sysdate),
--外键约束 关联sxt_class班级表的主键cid
--cid number(5) references sxt_class(cid)
cid number(5) --constraints fk_sxt_student_cid_class references sxt_class(cid)
--追加外键约束
-- constraints fk_sxt_student_cid_class foreign key(cid) references sxt_class(cid)
)
–追加外键约束
alter table sxt_student add constraints fk_sxt_student_cid_class foreign key(cid) references sxt_class(cid) on delete cascade;
create table sxt_class(
cid number(5) primary key ,
cname varchar2(15) constraints haha not null unique
)
–删除表
drop table sxt_student;
drop table sxt_class cascade constraints; --cascade constraints删除表的同时级联删除约束
–插入数据
insert into sxt_class values(1001,'java35期');
insert into sxt_class values(1002,'java36期');
insert into sxt_student values(01,'古力娜扎',18,'女',sysdate,1001);
–主键关系|约束
–主从表的概念 从表|子表 主表|父表
–如果两张表存在主外键约束:
–删除表:
–默认先删除从表 再删除主表
–cascade constraints 删除主表的同时级联删除约束
–删除数据:
–删除从表数据: 直接删除
–删除主表数据:
–从表中是否有数据引用了当前的主表数据
–没有引用可以直接删除
–有引用,想要删除需要一下三种方式处理:
–默认 先删除从表引用的数据,再删除被引用的主表数据
–添加外键约束时,on delete set null 删除主表数据的同时,从表引用字段设置为null
–添加外键约束时,on delete cascade 删除主表数据的同时,级联删除从表引用了的数据
数据截断
–约束的禁用
ALTER TABLE tb_user disable constraint nn_user_name;
–约束启用
ALTER TABLE tb_user enable constraint nn_user_name;
–delete 与 truncate 数据截断
–共同点: 可以实现删除全部数据
–区别
delete: 可以删除全部,删除删除某一部分
自动开启事务
检查主表数据是否有被从表中引用…3中解决方案
truncate 删除全部数据
不会开启事务
直接检查表结构上是否有被从表关联
select * from sxt_class;
select * from sxt_student;
select * from t_user;
delete from sxt_class;
truncate table sxt_class;
create table sxt_student(
sid number(5),
sname varchar2(15),
cid number(5) references sxt_class(cid)
)
insert into sxt_class values(1001,'java35');
insert into sxt_student values(01,'lisi',1001);
insert into t_user values(01,'wangwu','12345');
insert into t_user values(02,'zhangsan','123');
序列
–作用:帮助我们管理为表中字段添加的数据值 ,数值型 可以按照一定的规律进行增长和管理
–create sequence 序列名 start with 起始值 increment by 步长;
create sequence sq_haha_user_id start with 100 increment by 2;