Oracle SQL语句大全(二)

avg(sal) desc; 
   查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列 
49.select ename from emp where sal>(select avg(sal) from emp);    查找出员工中薪水位于部门平均薪水之上的所有员工 
50.select ename,sal from emp join(select max(sal) max_sal from emp group by deptno) t on(emp.sal=t,max_sal and emp.deptno=t.deptno);    查找每个部门中薪水最高的 
51.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;           表的自连接 
52.select dname,ename from emp cross join dept         交叉连接，笛卡尔 SQL99中的新语法 
53.select ename,dname from emp join dept on(emp.deptno=dept.deptno); 
54.select ename,dname from emp join dept using(deptno);            查找emp和dept表中deptno相同的部分。 
55.select ename,dname,grade from emp e join dept d on(e.deptno=d.depno) 
                                       join salgrade s(e.sal between s.losal and s.hisal)   (三表查找） 
                                       where ename not like '_%A%'; 
56.select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr=e2.deptno); 表的自连接 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<e2.sal); 自连接(不用组函数求出最高薪水） 
   select distinct sal from emp where not in (select ename from e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal)); 
62.select deptno from (select avg(sal) max_sal deptno from emp group by deptno) where max_sal=(select max(avg_sal) from (select 
avg(sal) avg_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); 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            删除语句的用法 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), email varchar2(50), 
constraint stu_name_uui unique(email,name) 组合性约束 ); 
主键约束方法二 
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, 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; 索引读的速度快了，插入速度变慢 view 视图 
视图赠加了维护的量 序列： 
create table arcticle (id number, 
title varchar2(1024), cont long 
); 
序列的创建sequence产生独一无二的序列，而且是oracle独有的 create sequence seq; 
select seq.nextval from dual; 查找序列号 
insert into arcticle values(seq.nextval,'a','b');往表中插入序列 数据库设计的3范式 
第一范式： 设计任何表都要有主键，列不可分 第二范式： 如果有2个主键的话，不能存在部分依赖 第三范式， 不能存在传递依赖 PL-sql 例子1： 
SQL> set serveroutput on; 
SQL> begin(必要的--程序开始执行) 2 dbms_output.put_line('hello world'); 3 end;(结束) 4 /