数据库(Oracle)多表查询

1、笛卡尔积

多表查询时，首先会产出笛卡尔积（重复，错误的数据），在这个基础上进行过滤就可以得到我们想要的数据

-- 笛卡尔积
-- (多表查询必须加条件)
-- 总行数：(a表的行数*b表的行数)
-- 总列数：（a表的列数+b表的列数）
-- 对笛卡尔积，进行条件过滤
-- n个表需要：n-1个条件
-- 涉及到多表查询首先就会生产笛卡尔积，在集合基础上进行条件过滤
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;

-- 查询员工信息：员工号 姓名 月薪（emp）和部门名称（dept）
-- 员工 姓名、月薪这三个字段在emp表中，部门名称在dept表中
select e.empno,e.ename,e.sal, d.dname from emp e,dept d where e.deptno=d.deptno;

-- 不等值连接（结合笛卡尔积）
-- 查询员工信息：员工号姓名，月薪（emp） 和 薪水级别（salgrade表）
select e.empno,e.ename,e.sal,s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;

-- 按部门统计员工人数，显示如下信息：部门号，部门名称，人数
select e.deptno,d.dname, count(e.empno) from emp e,dept d where e.deptno=d.deptno group by e.deptno,d.dname;

-- 部门表中40号部门没有被统计上（40号部门没有人），如何将40号部门显示出来
-- 右外连接
-- count(*)和count(e.empno) 区别：
-- count(*) 只要一行一个字段不为空就可以，count(e.empno)不为空才会被统计上
select d.deptno,d.dname, count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;
-- 自连接
-- 查询员工信息：xxx的老板是yyy
select e.ename || '的老板是' || b.ename from emp e,emp b where e.mgr=b.empno;

2、子查询

-- 子查询（sql嵌套sql）
-- 查询比scott工资高的员工信息
-- 分析：1、先查询SCOTT，2、查询工资高于SCOTT
select* from emp where sal>(select sal from emp where ename='SCOTT');

-- 查询部门名称是"SALES"的员工信息
-- 子查询完成：
select* from emp where deptno=(select deptno from dept where dname='SALES');
-- 多表查询完成：
select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';

-- 可以在主查询的where、select、having、from后都可以放置子查询
-- a、在select后使用子查询的情况
-- 查询10号部门员工号，员工姓名，部门编号，部门名称
-- 注意：在select后面使用子查询必须保证子查询结果只有一条结果
select e.empno,e.ename,e.deptno,(select dname from dept where deptno=10) from emp e where e.deptno=10;

-- b、在having后面使用子查询
-- 查询部门平均工资高于30号部门平均工资的部门和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);

-- 查询到的结果可以当作一张表用,from后面可以是表名，也可以是select查询到的结果集
select* from (select ename,sal from emp);

-- where后使用子查询的情况
-- 查询比平均工资高的员工信息
select* from emp where sal>(select avg(sal) from emp);

-- having后面使用子查询
-- 查询最低薪水高于30号部门最低薪水的部门及其最低薪水
-- 意思：求出每个部门的最低薪水，查询出超过30号部门的最低薪水
select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=30);

-- 不可以在主查询的group by 后面放置子查询
--  查询部门名称为SALES和ACCOUNTING的员工信息
select* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
select* from emp where deptno in (select deptno from dept where dname in ('SALES','ACCOUNTING'));
-- 查询薪水比30号部门任意一个（某一个ANY）员工高的员工信息(相当于就是比30部门最小的高)
-- 单行操作符（<>=）、子查询，当后面结果集是多个需要使用多行操作符有IN ANY（任意一个）ALL（所有）
select* from emp where sal>(select min(sal) from emp where deptno=30);
select* from emp where sal>any(select sal from emp where deptno=30);

-- 查询比30号部门所有人工资高的员工信息（可以理解为比30号部门最高工资高）
select* from emp where sal>(select max(sal) from emp where deptno=30);
select* from emp where sal>all(select sal from emp where deptno=30);

3、集合运算

-- 集合运算
-- union两个集合相同的部分保留一份
-- union all 两个集合相同的部分都保留
-- intersect 两个集合交集只保留相同的部分
-- minus 集合A-集合B，减去A和B都有的部分，保留A中与B不同的部分

-- DML 数据操作语言 对应增删改查
-- DDL 数据定义语言 create（创建） drop（删除）truncate（摧毁）
-- DCL 数据控制语言 grant（权限），revoke（收回权限），commit（提交），rollback（回滚）

4、插入数据(insert into)

-- 插入全部列
insert into dept values(50,'50name','50loc');
select* from dept;
--  指定列（没写字段默认就是null）
insert into dept(deptno,dname) values(60,'TBD');
-- &符号作用
insert into dept(deptno,dname,loc) values(&t1,&t2,&t3);

5、修改(update )

-- 修改update
update dept set loc='hhh' where deptno=60;

-- 修改为空
update dept set dname=null where deptno=60;

6、删除(delete、truncate)

-- delete删除
delete from dept where deptno=50;

-- delete和truncate区别
truncate table 表民（先摧毁表，然后在重建表）

-- 拷贝表结构
-- 1=2为假表示只拷贝表结构，不拷贝数据
create table 表名 as select* from 表名 where 1=2;

-- 删除表
drop table 表名

-- delete和truncate区别
truncate table 表民（先摧毁表，然后在重建表）
-- delete是逐条删除，truncate 是先摧毁表在创建
-- DML语言可以闪回（flashback），DDL语句不可以闪回
-- delete是逐条删除数据，所以会产生碎片，truncate不会产生
-- delete不会释放空间，truncate会
-- delete可以回滚rollback，truncate不可以
-- command模式下执行脚本 start 脚本路径
-- 下面命令记录时间
-- set timing on;