Oracle入门3，selectrowi

Oracle入门3，selectrowi

oracle数据库三

rowid

​ 数据库中，每一张表的每一行数据，都拥有一个独一无二的rowid，默认情况下，rowid是不显示的，必须在select之后添加rowid才可以查询到

​ rowid作用：应用于后面所学的索引，数据去重。

select rowid,e.* from emp e;
# emp表的别名为e，e.*为emp表的所有字段

​ oralce去重的方法

# 使用 distinct 去重
select distinct id, name from jerk order by id;
# 使用 group by 分组去重
select id, name from jerk group by id, name;
# 使用 rowid 去重
# 基于 id 或者 name 对数据斤西瓜分组，是重复的数据被划分在同一个数组中，这些重复的数据，id 和 name 都相同，但是 rowid 一定不同，所以只需要从每一组中提取一个 rowid ，再利用子查询将这些 rowid 队形的信息查出即可
select * from jerk where rowid in
(select max(rowid) from jerk group by id);
# rowid 可以用于重复数据的删除,先分组去重取每组最大的 rowid 然后删除剩下的数据
delete from jerk where rowid not in
(select max(rowid) form jerk group by id);

rownum 伪列

​ rownum 是表中每一行数据所对应的行号

select rownum, e.* from emp e;

# 查询emp表的前十行数据
select * form emp where rownum < 11;
# rownum 不同于一般的字段，再遍历表进行比对的过程中，只要有一次条件不符合，就会停止比对
# 可以使用子查询的特性，使用 rownum 的特性，规避他的弊端
# 把rownum的查询变成一个子查询的语句，rownum 使用别名r，对外部查询而言，r就是一个普通字段，只不过具备了 rownum 的属性
select * from
(select rownum r,e.* from emp e)
where r < 11 and r > 5;

# 查询公司工资前十高的员工信息
select * from 
(select * from emp order by sal desc)
where rownum < 11;
# 查询公司工资第六到第十高的与员工信息
select * from
(select rownum r,a.* from
(select * from emp order by sal des) a)
where r > 5 and r < 11;

# 基于 rownum 查询，一般也称为分页查询

多表关联查询

笛卡尔积

​ 数学中的笛卡尔积是将两个集合中所有元素的匹配可能性

​ A={a,b,c} B={1,2,3}

​ A*B = {(a,1),(a,2),(a,3),(b,1),(b,2),(b,3),(c,1),(c,2),(c,3)}

# 对两张表做笛卡尔积(以 emp 和 dept 举例)
# 两张表做笛卡尔积的前提：两张表必须有关联的字段

# 直接笛卡尔积（会出现冗余数据）
select * from emp, dept;
# 在多表查询中，每一个表都必须定义别名
# 如果做了笛卡尔积，select 之后的所有字段必须通过别名来声明这个字段属于哪一张表
# 通过筛选去除冗余数据
select * from emp e, dept d where e.deptno = d.deptno;

# 三张表做笛卡尔积，先将前两张表做笛卡尔积，将结果于第三张表再做笛卡尔积即可

# 直接笛卡尔积的写法，也叫92sql范式，这种写法存在弊端
# join连接查询写法，也叫97sql范式
# 两种sql范式的区别在于书写的区别和性能差异

# join连接查询
select * from emp e join dept d on e.deptno = d.deptno;
# join查询的特点，等值连接写在on之后，而不是where之后

# 97sql范式它提供了五种连接查询的方式，每一种的使用是不一样的
# 实例使用emp1和dept1表
# 	emp1中有部门50，dept1表中没有
# 	dept1中有部门40，emp1表中没有
# 1. cross join 直接笛卡尔积，不可以使用on，只能使用where
# 	部门编号为40，50的信息都没有出现
#	cross join 如果使用的是等值连接，没有匹配数据的表记录会被剔除掉
select * from emp1 e cross join dept1 d where e.deptno = d.deptno;
# 2. inner join 内连接，一般来讲inner 可以省略
# 	部门编号为40，50的信息都没有出现
#	同样不会显示没有匹配的表记录
select * from emp1 e inner join dept1 d on e.deptno = d.deptno;
# 3. left join 左连接
#	部门编号为40的信息没有出现
#	会以左表的信息为主，强制显示左表中没有匹配的信息
select * from emp1 e left join dept1 d on e.deptno = d.deptno;
# 4. right join 右连接
#	部门编号为50的信息没有出现
#	会以右表的信息为主，强制显示右表中没有匹配的信息
select * from emp1 e right join dept1 d on e.deptno = d.deptno;
# 5. full outer join 满外连接
#	部门编号全部显示
#	强制显示所有的信息，不管有没有匹配
select * from emp1 e full outer join dept1 d on e.deptno = d.deptno;

# 一般使用较多的是 inner join 和 left join

​ 如果sql查询的字段来自于同一张表，尽可能使用子查询（子查询不会产生笛卡尔积）

​ 如果sql查询的字段来自于多张表，就无法使用子查询，优先使用连接查询

直接笛卡尔积的写法：先做笛卡尔积，然后再筛选（产生大量的笛卡尔积）

连接查询：先做筛选，再做笛卡尔积（产生笛卡尔积的数量会大大减少）

自连接和非等值连接

# 自连接的本质依旧是笛卡尔积
# 使用不同的别名即可实现自连接
select wc1.fname,wc2.fname from worldcup wc1 
join worldcup wc2 on wc1.fid!=wc2.fid;

建立复制表

​ 指挥复制表的结构和数据，不会复制约束和所有的键

create table emp1 as select * from emp;
create table dept1 as select * from dept;