多表连查

--92连表查询




--查询员工信息和部门信息
select * from emp e, dept d where e.deptno = d.deptno;
--查询员工姓名，部门名称
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
--查询员工所有信息和部门名称
select e.*, d.dname from emp e, dept d where e.deptno = d.deptno
--查询工资大于3000的员工姓名，工资和部门名称
select e.ename, e.sal, d.dname
  from emp e, dept d
 where e.deptno = d.deptno
   and e.sal > 3000;
--非等值查询
--查询员工的工资等级
select *
  from emp e, salgrade s
 where e.sal <= s.hisal
   and e.sal > s.losal
--外连接
--左外连
select * from emp e, dept d where e.deptno = d.deptno(+)
--右外连
select * from emp e, dept d where e.deptno(+) = d.deptno
--自连接 自己连自己
--查询员工姓名和员工的经理姓名
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;
--查询员工姓名和员工的经理姓名和其经理的姓名
select e1.ename, e2.ename, e3.ename
  from emp e1, emp e2, emp e3
 where e1.mgr = e2.empno
   and e2.mgr = e3.empno;




--99连表查询




--cross join交叉连接（笛卡尔集）
select * from emp e cross join dept d;
-- natural join 自然连接
select * from emp natural join dept;
--using
select e.ename, deptno from emp e join dept d using (deptno);
--on 自定义连接条件 join 后紧跟 on
select * from emp e join dept d on e.deptno = d.deptno;
select * from emp e1 join emp e2 on e1.mgr = e2.empno;
--查询员工的姓名和经历的姓名和其经理的姓名
select e1.ename, e2.ename, e2.ename
  from emp e1
  join emp e2
    on e1.mgr = e2.empno
  join emp e3
    on e2.mgr = e3.empno
--inner join 两边都合法的数据（相对比较精准）内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
--left join 以左表为主
select * from emp e left join dept d on e.deptno = d.deptno;
--right join 以右表为主
select * from emp e right join dept d on e.deptno = d.deptno;
--full join 全连 取两个表的所有数据
select * from emp e full join dept d on e.deptno = d.deptno;
--查询员工姓名和部门姓名和其经理的名称和经理的部门名称
select e1.ename, e2.ename, d1.dname, d2.dname
  from emp e1
  left join dept d1
    on e1.deptno = d1.deptno
  left join emp e2
    on e1.mgr = e2.empno
  left join dept d2
    on e2.deptno = d2.deptno;
select sal from emp where ename='CLARK';
select *from emp where sal >2450;
--子查询
--查询工资大于CLARK工资的员工信息
select * from emp where sal > (select SAL from emp where ename = 'CLARK');
--思考：查询工资高于平均工资的雇员名字和工资。
select ename, sal from emp where sal > (select avg(sal) from emp);
--思考：查询和SCOTT同一个部门且比他工资低的雇员名称和工资
select ename, sal
  from emp
 where deptno = (select deptno from emp where ename = 'SCOTT')
   and sal < (select sal from emp where ename = 'SCOTT');
--多行子查询查询和'SCOTT','SMITH','CLARK'在同一个部门的员工信息并不包含'SCOTT','SMITH','CLARK'；
select *
  from emp
 where deptno in
       (select deptno from emp where ename in ('SCOTT', 'SMITH', 'CLARK'))
   and ename not in ('SCOTT', 'SMITH', 'CLARK');
--思考：查询工资最高的雇员名字和工资。
select ename, sal from emp where sal = (select max(sal) from emp);
-- 查询职务和SCOTT相同，比SCOTT 雇佣时间早的员工信息
select *
  from emp
 where job = (select job from emp where ename = 'SCOTT')
   and hiredate < (select hiredate from emp where ename = 'SCOTT')
--查询工资比SCOTT 高或者雇用时间比SCOTT 早的雇员的编号和姓名
select empno,ename from emp where SAL>(select sal from emp where ename ='SCOTT')OR HIREDATE<
(SELECT HIREDATE FROM EMP WHERE ENAME ='SCOTT');
--多行查询
--查询工资低于任何一个"CLERK"的工资的雇员信息
SELECT *
  FROM EMP
 WHERE SAL < ANY (SELECT sal from emp where JOB = 'CLERK');
--查询工资比所有的“SALESMAN”都高的雇员的编号、名称和工资
select empno, ename, sal
  from emp
 where sal > all (select sal from emp where job = 'SALESMAN');
--查询部门20中职务同部门10的雇员一样的雇员信息
select * from emp where job in(select job from emp where deptno=10)and deptno=20; 
--查询在雇员中有哪些人是经理人
select * from emp where empno in (select  distinct mgr from emp where mgr is not null or mgr!='');
--找出部门编号为20的所有员工中收入最高的职员
select * from emp where sal=(select max(sal) from emp where deptno='20');
--查询每个部门平均薪水的等级 
select a.deptno, grade
  from salgrade, (select deptno, avg(sal) b from emp group by deptno) a
 where losal < a.b
   and hisal >= a.b

用户名：user 
创建用户：
create user 用户名 identified by 密码;
create user sxt identified by cat123;
给用户授予权限
grant 权限名 to 用户名;
grant connect,resource to sxt;
收回权限
revoke 权限名 from 用户名;
rovoke connect from sxt;
table 对象
创建表
create table 表名(字段名 字段属性 约束,...)
create table student(
	sid int primary key,
	sname varchar2(20) not null,
	sex char(2) default '男',
	age int check(age>=18 && age<=30),
	email varchar2(50) unique
)
oracle  常用数据类型

  
  
数值型 
number(p,s) p是整数位，s小数位

  
  
字符型
char 不可变长度字符型
varchar2 可变长度字符型

  
  
日期型
date 日期类型
timestamp 时间戳类型

  
  
大文件文类lob
blob 以二进制存储
clob 以字符进行存储

约束
为了保证数据的完整性和一致性而创建约束
域完整性约束 非空 not null 检查 check
实体完整性约束 唯一 unique 主键 primary key
参照完整性约束 外键 foreign key
事务

从头到尾的去做一件事情，要么做好，要么不做。
Oracle分页算法
totalRows 总条数(必须有)
pageRows 每页数量(必须有)
一共多少页：totalRows%pageRows==0？totalRows/pageRows:totalRows/pageRows+1;
第n页的开始行和结束行
开始行 ：(n-1)*pageRows+1
结束行 ：n*pageRows
在Mysql中：
select * from 表名 limit 开始行，结束行

--创建一个用户
create user zxx identified by 123987;
--分配权限
grant connect,resource to zxx;
--收回权限
revoke connect from zxx;
--创建一个学生表
--字段包含学号，姓名，性别，年龄，入学日期，班级EMAIL等信息
create table student(
       sno number ,
       sneme varchar2(30) not null,
       sex varchar2(2) default '男',
       age number check(age>10 and age<30),
       enterdate date,
       cno number,
       --clazz varchar2(50),--使用class会出问题
       email varchar(100),
       foreign key(cno)references clazz(cno)
)
insert into student values(1,'小四','女',18,sysdate,'3班','1@qq.con');
insert into student values(2,'小懒','男',19,sysdate,'1班','2@qq.com');
insert into student values(3,'赢红','女',20,sysdate,'2班','3@qq.com');
insert into student values(4,'楠楠','男',20,sysdate,'3班','4@qq.com');
select *from student
--修改表添加一列
alter table student add addr varchar2(200);
--修改表修改一列
alter table student modify addr varchar(300);
--修改表删除一列
alter table student drop column addr;
--删除表
drop table student;
--表的重命名 
rename student to newstudent
--描述表
desc student
create table calzz(
       cno number primary key ,
       cname varchar2(50),
       chead varchar(20)
)
drop table calzz
insert into clazz values(1,'1班','王老师');
insert into clazz values(2,'2班','田老师');
insert into clazz values(3,'3班','样老师');
update clazz set cname ='sxt3班'where cname='3班'; 
--添加约束
alter table student add constraint ck_student_sex check(sex='男' or sex='女');
--删除约束
alter table student drop constraint ck_student_sex;
--创建一个序列
create sequence seq_student;
--获取一个序列的下一个值
select seq_student.nextval from dual;
--获取一个序列当前的值
select seq_student.currval from dual;
--插入一个数据
insert into student values(seq_student.nextval,'香兰','男',29,sysdate,1,'11@qq.com');
select *from student;
--删除序列
drop sequence seq_student;
select*from student
--创建一个索引
create index idx_student_sname on student(sneme);
--删除一个索引
drop index idx_student_sname;
--创建一个视图
grant dba to scott
create view vi_student
as select*from student;
--查询视图
select *from vi_student;
--修改视图
create or replace view vi_student
as select sneme,sex,email from student;
--删除视图
--创建一个多表查询
create view vi_student_clazz
as 
select s.*,c.chead,c.cname from student s left join clazz c
on s.cno=c.cno
select *from vi_student_clazz;
--rowid 系统自定义行号
select rowid,calzz.* from calzz
--rownum 查询用的伪列，行号
select rownum,calzz.* from calzz;
--查询员工表中前三行的数据
select *from emp where rownum<=3;
--查询员工表中第三行到第五行的数据
select *from emp where rownum<=5;
select sal from emp
order by sal desc
--top-n
select rownum,t.*from(select sal from emp order by sal desc)t
where rownum<=3;
--oracle分页查询（重点面试题）
select*from (select rownum r,t.* from(select sal from emp order by sal desc)t where rownum<=15)a
where a.r>=11