92语法
--多表连接 *****
--92语法
--99语法
--笛卡尔积
select * from dept,emp;
--等值连接 如果使用多个表相同的字段,注意指明字段的出处 ,等值连接的时候两个表中的字段类型必须相同,字段名可以不同
-- 查询所有员工的信息以及所在部门的信息
--数据:员工信息,部门信息
--数据的来源:emp,dept
select empno,ename,dept.deptno,dname from emp,dept where emp.deptno=dept.deptno;
select empno,ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno; --别名
--找出30部门的员工名称及部门名称
select ename,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=30; --先连接后判断
select ename from emp where deptno=30;
select dname from dept where deptno=30;
select * from (select ename from emp where deptno=30),(select dname from dept where deptno=30);--先判断后连接
--非等值连接
-- 2500 所在的工资等级是多少
select * from salgrade where 2500 between losal and hisal;
--查询员工姓名,工资及等级
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
-- 10和30部门的员工信息,部门信息以及工资等级
select ename,sal,grade from emp,salgrade where sal between losal and hisal and deptno !=20;
select ename, sal, grade,emp.deptno,dname
from emp, salgrade, dept
where sal between losal and hisal
and emp.deptno = dept.deptno
and emp.deptno != 20;
--外连接 主表:所有的数据都能显示,在连接条件上在主表对面的表上添加(+)
--左外连接 观察主表在from emp,dept在逗号的左边就是左外连接
--右外连接 观察主表在from emp,dept在逗号的左边就是右外连接
--所有部门及其员工信息
select * from emp,dept where dept.deptno=emp.deptno(+);
--自连接
-- 找出有上级的员工的名称和对应的上级名称
--数据来源:员工表emp e1和上级表emp e2
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
-- 找出所有的员工的名称和对应的上级名称
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);
--找出 所有部门的员工数 及部门名称
--找出 所有部门的员工数和部门 编号
select count(1),deptno from emp group by deptno;
select d.deptno, num
from dept d, (select count(1) num, deptno from emp group by deptno) e
where d.deptno = e.deptno(+);
select dname, nvl(num,0)
from dept d, (select count(1) num, deptno from emp group by deptno) e
where d.deptno = e.deptno(+);
99语法
--99语法
--笛卡尔积 cross join
select * from emp cross join dept;
-- 查询所有员工的信息以及所在部门的信息
--等值连接
--自然连接 natural join 自动匹配表中的同名字段主外键关系
select deptno from emp natural join dept; --同名列不能写限定词
--join using(字段) 某个字段相同的等值连接
select dept.deptno from emp join dept using(deptno); --同名列不能写限定词
--表1 join 表2 on 连接条件 可以做等值和非等值连接,自定义连接条件 同名字段的限定名必须写
--找出30部门的员工名称及部门名称
select ename,dname from emp inner join dept using(deptno) where deptno=30;
select ename,dname from emp inner join dept on emp.deptno=dept.deptno where emp.deptno=30; --先连接后判断
select * from (select ename from emp where deptno=30) cross join (select dname from dept where deptno=30);--先判断后连接
--非等值连接
--查询员工姓名,工资及等级
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
select ename,sal,grade from emp join salgrade on sal between losal and hisal;
-- 10和30部门的员工信息,部门信息以及工资等级
select ename "员工名称", dname "部门名称", grade "工资等级"
from emp
join dept
on emp.deptno = dept.deptno
join salgrade
on sal between losal and hisal where emp.deptno in(10,30);
--外连接 主表:所有的数据都能显示
--左外连接 观察主表在from emp dept在逗号的左边就是左外连接 left join
--右外连接 观察主表在from emp dept在逗号的左边就是右外连接 right join
--所有部门及其员工信息
select * from emp right join dept on dept.deptno=emp.deptno;
--自连接
-- 找出有上级的员工的名称和对应的上级名称
--数据来源:员工表emp e1和上级表emp e2
select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno;
-- 找出所有的员工的名称和对应的上级名称
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
--找出 所有部门的员工数 及部门名称
--找出 所有部门的员工数和部门 编号
select dname, nvl(num,0)
from dept d left join (select count(1) num, deptno from emp group by deptno) e
on d.deptno = e.deptno;
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) d1
join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) d2
on d1.no = d2.no;
--左外连接
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) d1
left join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) d2
on d1.no = d2.no;
--右外连接
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) d1
right join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) d2
on d1.no = d2.no;
--全连接 两张表都作为主表
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) d1
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) d2
on d1.no = d2.no;
rowid和rownum
--rowid伪列,每一行记录的标识,在行记录插入表时就确定的,相当于对象的地址,不是真实的地址
select deptno,dname,rowid from dept;
select ename,rowid from emp;
select * from tb_student;
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
--没有主键的表中相同数据的去重
--找到要保留的数据,多条相同数据中的任意一个
select name,course,score,min(rowid) from tb_student group by name,course,score;
--要删除,除了保留以外的其他数据
select * from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score);
delete from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score);
--rownum 伪列 结果集的序号,从1开始
--rownum和排序顺序:
-- 如果按照主键进行排序先排序后确定rownum
-- 如果按照非主键字段排序,先确定rownum再排序
select deptno,dname,rownum from dept order by deptno desc;
select empno,ename,sal,rownum from emp order by empno desc;
--分页 应该对已经确定的并且不变的rownum,有规律的做判断
--获取前3条数据
select empno,ename,sal,rownum from emp where rownum<=4;
--每页显示几条数据 num=4 当前的页数:i 要显示的数据的rownum的范围: 最小值: (页数i-1)*每页显示几个num+1 最大值: 页数i*每页显示几个num
select empno,ename,sal,rownum from emp;
select empno,ename,deptno,rownum,n,sal
from (select empno, ename,deptno, sal, rownum n from emp order by sal desc) --确定有规律的不变的num
where n >= (2-1)*4+1
and n <= 2*4;
--根据薪资进行降序排序找第二页的数据
select ename, sal
from (select ename, sal, rownum n
from (select empno, ename, deptno, sal, rownum
from emp
order by sal desc))
where n >= (2 - 1) * 4 + 1
and n <= 2 * 4;
--真分页:每次真实去数据库中查询一条数据
--假分页:每次查出一批数据,按需求展示
视图和索引
--视图是一个虚拟表 介于表和结果集之间的,不是真实存在的,根据需求进行添加提供使用效率
--create or replace view 视图名 as select语句 [with read only];
create or replace view vw_emp as select empno,ename,sal,deptno from emp where deptno in(10,30) with read only;
--使用视图 操作视图不会修改原表中的数据
select * from vw_emp;
--删除
drop view vw_emp;
--查询出所有上级经理人的中平均薪资中最低的部门名称
--先找出所有的上级经理人员工编号
select distinct mgr from emp where mgr is not null;
--上级经理人的员工信息
select empno, ename, deptno, sal
from emp
where empno in (select distinct mgr from emp where mgr is not null);
--创建视图
create or replace view vw_mgr as select empno, ename, deptno, sal
from emp
where empno in (select distinct mgr from emp where mgr is not null);
select * from vw_mgr;
--按照部们分组求平均薪资
select avg(sal), deptno
from (select empno, ename, deptno, sal
from emp
where empno in (select distinct mgr from emp where mgr is not null))
group by deptno;
--求最小平均数
select min(avg(sal))
from (select empno, ename, deptno, sal
from emp
where empno in (select distinct mgr from emp where mgr is not null))
group by deptno;
--部门经理人平均薪资最低的部门编号
select deptno
from (select avg(sal) avg_sal, deptno
from (select empno, ename, deptno, 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 (select empno, ename, deptno, 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 avg(sal) avg_sal, deptno
from (select empno, ename, deptno, 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 (select empno, ename, deptno, 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 avg(sal) avg_sal, deptno
from (select * from vw_mgr)
group by deptno)
where avg_sal =
(select min(avg(sal))
from (select * from vw_mgr)
group by deptno));
--索引
--是数据库的对象之一
--当存在大批量数据的查询,可以使用索引加快查询速度
--如果频繁对索引字段做增删改效率会降低,对象需要oracle维护
--相当于目录
--是否存在索引对数据库中数据的使用没有任何区别
--oracle 创建主键时会自动在该列上创建索引
--create index 索引名 on表名 (字段列表...)
create index index_emp_sal on emp(sal);
--drop index 索引名
drop index index_emp_sal;
select * from emp where sal>800;
表设计
--设计表首先应该按需遵循三范式
--1、确定表名 2、确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
--1.创建表 不添加约束
create table student(
--学号
sid number(5),
--姓名
sname varchar2(15),
--年龄
sage number(3),
--性别
sgender char(3),
--qq号
sqq varchar2(30)
);
--2.已有表中拷贝结构 create table 表名 as select 字段列表 from 已有表 where 1!=1;
--值拷贝表结构,不拷贝数据
create table haha as select * from emp where 1!=1;
--拷贝表结构包括数据
create table haha as select * from emp;
create table haha as select empno,ename,sal,deptno from emp where sal>1500;
select * from haha;
drop table haha;
--3.创建表+约束
--3,1 字段后直接添加约束(没有约束名) 不变与调试,但是简单
create table student(
--学号 主键约束(非空+唯一)
sid number(5) primary key,
--姓名 非空约束
sname varchar2(15) not null,
--年龄 检查约束 1~150
sage number(3) check(sage>=1 and sage<=150),
--性别 检查约束 '女','男'
sgender char(3) check(sgender='女' or sgender='男'),
--qq号 唯一
sqq varchar2(30) unique
);
--3,2 字段后直接添加约束(指定约束名)
--3,3 创建表结构结束钱指定约束(执行约束名+指定添加约束的字段)
--从表,子表
create table student(
--学号 主键约束(非空+唯一)
sid number(5) constraints pk_student_sid primary key,
--姓名 非空约束
sname varchar2(15) constraints student_sname_notNull not null,
--年龄 检查约束 1~150
sage number(3),
--性别 检查约束 '女','男'
sgender char(3),
--qq号 唯一
sqq varchar2(30),
--所属班级
cid number(6) references sxt_class(cid) on delete set null,
constraints stu_sage_check check(sage>=1 and sage<=150)
--constraint fk_stu_cid_class_cid foreign key(cid) references sxt_class(cid) on delete cascade,
);
--班级表 主表,父表
create table sxt_class(
cid number(6) primary key,
object varchar2(45)
)
--3,4 创建表结构之后追加约束(指定约束名,+指定添加约束的字段)
--alter table 表名 add constraint 约束名 unique|其他约束(字段);
alter table student add constraint stu_sqq_uq unique(sqq);
alter table student add constraint fk_stu_cid_class_cid foreign key(cid) references sxt_class(cid);
--删除约束
--alter table tb_user drop constraint uq_user_email cascade;
alter table student drop constraint stu_sqq_uq;
--插入值
insert into student values(1,'zhangsan',20,'女','123456',1001);
insert into student values(2,'lisi',25,'男','123456');
insert into sxt_class values(1001,'java29班');
insert into sxt_class values(1002,'java30班');
--删除表 drop table 表名 cascade constraints
drop table student; --从表
drop table sxt_class cascade constraints; --主表
select * from student;
select * from sxt_class;
--删除数据
delete from sxt_class where cid=1001;
delete from student where cid=1001;
--如果存在主从表关系 删除表 1)先删除从表,再删除主表 2)级联删除主表以及主从表之间的主外键约束 默认
--如果存在主从表关系 删除数据 1)删除引用了主表中的这个数据从表数据,再去删除主表数据 2)删除主表数据的同时给从表中引用了数据的字段值设置为null on delete set null
--3)主表数据以及引用了的从表数据全部删除 on delete cascate
--加入注释
comment on table student is '学生表';
comment on column student.sid is '学号,主键';
comment on column student.sname is '学生姓名';
comment on column student.sage is '年龄';
comment on column student.sgender is '性别';
数据库语法
最新推荐文章于 2022-10-21 01:28:19 发布