Oracle day04

嵌套查询 = 子查询

连表查询：
内连接：inner join on = join on
    *：两张表有关联关系的数据
    *：from 表 join 表 on 关联关系

    select student.name stuname,school.name schname from student
    join school on student.sid = school.id
    where student.name = '葫芦娃';

    select student.name stuname,school.name schname,class.name cname     from student
    join school on student.sid = school.id
    join class on student.cid = class.id
    where student.name = '葫芦娃';

练习：
--2查询所有工种为CLERK的员工的工号、员工名和部门名。
--25查询所有员工的姓名及其直接上级的姓名
--面试题第二题（纸）
1.展示学校名称，老师姓名，班级名称，学生姓名（一条语句）
2.每个班级的学生平均工资是多少，要求展示班级名称
3.每个班级有多少人，分别都是哪些同学，要求展示班级名称，按照人数降序排列

外连接：
左外连接：left join on
右外连接：right join on
全外连接：full join on

交叉连接：cross join

自查询：

嵌套查询 = 子查询

连表查询:

内连接:inner join on = join on

        *:两张表有关联关系的数据

        *:from 表 join 表 on 关联关系

        select student.name stuname,school.name schname from student

        join school on student.sid = school.id

        where student.name = '葫芦娃';

        select student.name stuname,school.name schname,class.name cname from student

        join school on student.sid = school.id

        join class on student.cid = class.id

        where student.name = '葫芦娃';

外连接:不仅包含有关联关系的数据,还包含没有关联关系的数据

左外连接:left join on

        *:以左表为主,左表的所有数据都展示,右表对应不上的以空展示

        *:左表关联上右表的数据 + 关联不上的左表的数据

        *:from 左表 left join 右表 on 关联关系

        select school.name 市场那么,student.name stuname

        from school        

        left join student on student.sid = school.id;

右外连接:right join on

        *:以右表为主,右表的所有数据都展示,坐标对应不上的以空展示

        *:左表关联上右表的数据 + 关联不上的右表的数据

        *:from 左表 right join 右表 on 关联关系

        select school.name schname,student.name stuname

        from student

        right join school on student.sid = school.id;

        

        select school.name schname , student.name stuname,class.name cname

        from student

        left join school on student.sid  = school.id

        right join class on student.cid = class.id;

全外连接:full join on

        *:两张表的数据都展示

        select student.name stuname,school.name schname

        from STUDENT

        full join school on student.sid = school.id;

交叉连接：cross join
    *：两张表的数据一一对应，交叉形成最后结果
    select student.name stuname,book.bname BNAME
    from STUDENT
    cross join book;

自查询：
select student.name stuname,book.bname BNAME
from STUDENT,book;

select student.name stuname,school.name schname
from STUDENT,school
where student.sid = school.id;

select student.name stuname,school.name schname
from STUDENT,school
where student.sid(+) = school.id;

伪列：
rownum：行号
*：Oracle分页的依据，一个有序的整数列值，每多一条自动加一
*：不能使用表名.rownum的写法，如果用在where之后，rownum >= 1   rownum <= 任意值

1）第一页数据，编号1-10
select content from lyric where rownum between 1 and 10;
2）第二页数据，编号11-20
select content from
(select rownum rn,content from lyric where rownum <= 20) lyrics
where lyrics.rn >= 11;
3）第三页数据，编号21-30
select content from
(select rownum rn,content from lyric where rownum <= 30) lyrics
where lyrics.rn >= 21;
4）带有排序效果的分页
select content from
(select rownum rn,content from
(select * from lyric order by content desc)
where rownum <= 30) lyrics
where lyrics.rn >= 21;

***：
curpage：当前页        2
pagesize：每页条数   10
当前页的开始数：(curpage - 1) * pagesize + 1   11
当前页的截止数：curpage * pagesize                 20

rowid：数据库会给每条数据增加物理地址唯一标识
*：适用于删除完全重复的数据

delete from lyric where rowid not in
(select min(rowid) from lyric group by content);

in：表示字段是查询结果中的任意一个值
字段 in (值1,值2...)
相当于：
字段 = 值1 or 字段 = 值2 or ...

select id,name from school where id in (select sid from student);

select id,name from school where (id,name) in (select id,name from student);

not in：表示字段不能是查询结果中的任意一个值
字段 not in (值1,值2...)
相当于：
字段 <> 值1 and 字段 <> 值2 and ...

select id,name from school where id not in (select sid from student);

复制表：
1）复制表结构和表数据
create table emp2 as select * from emp;
2）复制表结构
create table emp3 as select * from emp where 1 = 2;
3）复制指定列的表结构和表数据
create table emp4 as select empno,ename from emp;
4）在复制表中给列重命名
create table emp5(id,name) as select empno,ename from emp;
5）一次性添加多条数据
insert into emp3 select * from emp;