嵌套查询 = 子查询
连表查询:
内连接: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;