select ename, sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
join表连接，on后面是连接条件，此语句等于如下语句
select ename, sal from emp where (deptno,sal) in (select deptno, max(sal) from emp group by deptno);
35.把自己的名字和他经理人的名字取出来(自链接)
select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno;
36.SQL1999 cross join 交叉连接
select ename,dname from emp, dept; 1992版的
select ename,dname from emp cross join dept;1999版的
36.SQL1999等值连接 老版用where难分辨哪个是过滤条件哪个是表连接条件，用新版的on，后可加where过滤
select ename,dname from emp,dept where emp.deptno = dept.deptno; 1992版
select ename,dname from emp join dept on (emp.deptno = dept.deptno); 1999版
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
37.sql1999三表链接加where过滤
select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s on (e.sal between s.losal aand s.hisal) where ename not like '_A%';
38.外链接 左外链接 left join 会把左边这张表多余的数据显示出来（和另外一张表对应不上的数据）同理right join右外连接，full join 全外连接
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
39.部门平均薪水的的等级
select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);
40.部门平均的薪水等级
select deptno, avg(grade) from(select deptno, sal, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) group by deptno;
41.雇员中哪些人是经理人
select ename from emp where empno in (select mgr from emp);
42.不用组函数求最高薪水
select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 left join emp e2 on (e1.sal < e2.sal));
43.平均薪水最高的部门编号与名称
select dname from dept where deptno = (
select deptno from (select deptno avg(sal) avg_sal from emp group by deptno) where avg_sal = (sel ect max(avg_sal) from (select deptno avg(sal) avg_sal from emp group by deptno)));
44.平均薪水的等级最低的部门的部门名称
select dname from dept where deptno = (
select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) where grade = (
select min(grade) from (select grade, deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal betwe en s.losal and s.hisal))));
45.创建视图 create view v$name as 重复使用的语句. 视图就是一个子查询，就是一张表
select * from v$name;
46.插入语句
insert into tbname values (50, 'ganbe', 'bj');
insert into tbanme (zd1, zd2) values (50, 60);
insert into dept2 select * from dept;
47.备份表
create table emp2 as select * from emp;
48.伪字段 rownum 默认从第一行往后排列序号1，2，3等，必须< = 号；
select empno, ename from emp where rownum <=5;
select roenum r, ename from emp where r > 10; 这样可 〉 于号；
49.薪水最高的前5人
select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5;
50.部门为10的员工薪水翻一倍
update emp set sal = sal*2, name ename = ename||"-" where deptno = 10;
51.还原没提交的修改 rollback
rollback;
52.提交 commit 遇到ddl语句事物自动执行 commit，正常断开连接时自动提交
commit;
53.Oracle有事物回滚机制
事物 transaction
54.约束 自定义名字 非空约束
create table stu (id number(6), name varchar2(20) constraint stu_name_ nn not null);
55.唯一 约束 unique 可以插空置
标级约束 几个字段的组合唯一约束 不在字段后面，另起一行
constraint syu_name)email_uni unique(email,name)
56.主键约束 非空唯一 primary key，可以组合 主键
id number(4),
另起一行的话 primary key(id)
57.外键约束 牵扯到两张表、两个字段references(参考),被参考的字段必须是主键
表级别：constraint ysname foreign key (benziduan) references ckbiao(waizd)
58.修改现有表的表结构
alter table tbname add(ziduan varcha