……………………………………………………………精品资料推荐…………………………………………………
表的自连接 57.select
e1.ename,e2.ename
from
emp
e1
left
join
emp
e2
on(e1.mgr=e2.deptno) 左外表连接
select ename,dname from emp e right join dept d on(e.deptno=d.deptno)右外连接
select ename,dname from emp e full join dept d on(e.deptno=d.deptno)全连接
58.求部门中薪水最高的
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); 59.求部门中薪水等级的平均值
select deptno,avg(grade) from(select deptno,ename,grade,from emp join salgrade s on(emp.sal between s.losal and s.hisal))t group by deptno; 60.查找雇员中哪些是经理人
select ename from emp where empno in(select mgr from emp);
61.select distinct e1.sal from emp e1 join emp e2 on(e1.sal where sal not in (select e1.sal from emp e1 join emp e2 on(e1.sal from (select avg(sal) avg_sal ,deptno from emp group by deptno)); select deptno from (select sum(sal) sum_sal,deptno from emp group by deptno) where sum_sal=(select max(sum_sal) from (select sum(sal) sum_sal ,deptno from emp group by deptno)); 查找部门中部门薪水最大的部门号 63.求平均薪水最大的部门的部门编号 select deptno,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno)where avg_sal=(select max(avg(sal)) from emp group by deptno); 6 ……………………………………………………………精品资料推荐………………………………………………… DML语句：更、删、改、查 创建权限， conn sys/admin as sysdba grant create table,create view to scott; 首先在C:下面建个文件夹备份文件 1.createNewUser方法 1.--backup scott exp 2.create user(创建用户）用超级管理员模式进入 create user yun identified by kang1234 default tablespace users quota 10M on users; grant create session,create table,create view to kafei（给kafei这个用户授予权限） 3.import the data(导入备份数据） imp 2.insert insert into dept values (50,'game','bj') 插入一条记录 insert into dept2 (deptno,dname) values (78,'games'); 插入指定的几条记录 insert into dept2 select *from dept 插入指定的表（表结构要一样） rollback; 回退 create table emp2 as select * from emp; 创建数据库表2来备份emp这张表 3.update emp2 set sal=sal*12 where deptno=10; update的用法 4.delete from dept2 where deptno<25 ; 删除语句的用法 7 ……………………………………………………………精品资料推荐………………………………………………… --28 DDL语言 1.创建表:create table t(a varchar2(10)); 2.drop table t 删除表 3.commit 所有的提交，所有修改都结束了。对于rollback无效，一个事务开始于第1条DML语句 碰到执行DDL DCL语句事务自动提交 对于rollback无效 建表语句 建学生信息表： create table stu (id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) unique (唯一约束) ); 非空 唯一 主键 外键 chick create table stu (id number(6) primary key,(主键约束) name varchar2(20) constraint stu_name_nn not null,(非空约束) sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), 8 ……………………………………………………………精品资料推荐………………………………………………… email varchar2(50), constraint stu_name_uui unique(email,name) 组合性约束 ); --29 主键约束方法二 create table stu (id number(6), name varchar2(20) constraint stu_name_nn not null,(非空约束) sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4) references class(id),(参考class 这张表，参考字段) email varchar2(50), constraint stu_id_pk primary key(id), constraint stu_name_uui unique(email,name) 组合性约束 ); 外键约束 create table class (id number(4) primary key,(id为被参考字段,被参考的字段必须是主键) name varchar2(20) not null ) create table stu ( id number(6), name varchar2(20) constraint stu_name_nn not null,(非空约束) sex number(1), age number(3), sdate date, grade number(2) default 1, 9 ……………………………………………………………精品资料推荐………………………………………………… class number(4) email varchar2(50), constraint stu_class_fk foreign key(class) references class(id), constraint stu_id_pk primary key(id), constraint stu_name_uui unique(email,name) 组合性约束 ); 像外键中插入关键字， 1.insert into class values(1000,'c1'); 2.insert into stu(id,name,class,email) values(1,'a',1000,'a'); 3.alter table stu add(addr varchar(20));添加表的结构 4.alter table stu drop(addr); 删除表结构 5.alter table stu modify(addr varchar2(150));修改精度 6.alter table stu drop constraint stu_class_fk; 删除约束条件 7.alter table stu add constraint stu_class_fk forengn key(class) references class(id),添加约束条件 查找当前用户下有哪些表和哪些视图及哪些约束 8.select table_name from user_names 9.select view_name from view_names 10.select constraint_name,table_name from user_constraints; desc dictionary数据字典表 desc user_tables当前用户下面有多少张表 select table_name from user_tables; 查找当前用户有多少张表 索引： 创建索引 create index idx_stu_email on stu(email); drop index idx_stu_email; 查找索引 select index_name from user_indexes; 索引读的速度快了，插入速度变慢 10