oracle学习2

……………………………………………………………精品资料推荐…………………………………………………

表的自连接 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

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4