oracle笔记

sqlplus sys/password  as sysdba;系统管理员登录

alter user scott account unlock; 更改用户

desc （表名）
select * from (table name);

select distinct ename from emp;

select ename,sal from emp;

select ename,sal*12 'annual_sal' from emp;

select ename,sal from emp where sal>1000;

select ename,sal from emp where sal<>1000;

select ename,sal from emp where ename>'CBA';

select ename,sal from emp where sal between 800 and 1500;

select ename,sal from emp where ename like 'CBA';

select ename,sal from emp where ename like '%$%%' escape '$';

select ename,sal from emp where sal in (800,1000);

select ename,sal from emp where sal in 800 or/and ename='KING';

select ename,enumber from emp order by enumber desc;

select ename,enumber from emp order by enumber asc,sal asc;

select ename,sal*12 annual_sal from emp
where ename not like '_A%' and sal > 800
order by sal desc;

select substr (ename,2,3) from emp;

select ename from emp where lower(ename) like '_a%';

select chr(65) from dual;

select ascii('A') from dual;

select round (23.598,2) from dual;   23.60

select round (23.598,-1) from dual;  20

select to_char(sal,'$99,999.9999') from emp;

select to_char(sal,'L0000.0000') from emp;

select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;

select ename,hiredate from emp where hiredate >to_date('1981-02-13 12:00:00','YYYY-MM-DD HH:MI:SS');

select ename,sal from emp where sal>to_number('$1,250.00','$9,999,99');

select ename,sal*12+nvl(comm,0) from emp;

select max(sal) from emp;

select min(sal) from emp;

select avg(sal) from emp;

select to_char(avg(sal),'9999.99') from emp;

select round(max(sal),2) from emp;

select sum(sal) from emp;

select count(*) from emp where deptno=10;

select count(distinct deptno) from emp;

selcet avg(sal) from emp;

select deptno,avg(sal) from emp group by deptno;

select ename from emp where sal=(select max(sal) from emp);

select deptno,max(sal) from emp group by deptno;

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

select deptno,avg(sal) from emp where avg(sal)>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;

select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno ) t
on(emp.sal=t.max_sal,emp.deptno=t.deptno);

select grade_sal,deptno from emp
join(select avg(sal) avg_sal,deptno from emp group by deptno)a
on(emp.sal=a.avg_sal,emp.deptno=a.deptno);

select e1.ename,e2.ename from e1.emp,e2.emp where e1.mgr=e2.empno;

select ename,dname,grade from emp e,dept d,salgrade s
where e.deptno=d.deptno
salgrade between losal and hisal
and job<> 'clerk';

select ename,dname from emp cross join dept;

select ename,dname from emp, dept where emp.deptno=dept.deptno;

select ename,dname from emp join dept on (emp.deptno=dept.deptno);

select ename,dname,grade from emp e join dept d on (e.deptno=d.deptno)
join salgrade s on(s.sal between s.losal and s.hisal)
where ename not like '_A%';

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,deptno,sal from emp
join(select max(sal) max_sal,deptno from emp group by deptno) t
on(t.max_sal=emp.sal and emp.deptno=t.deptno);

求部门平均薪水的等级：

select ename,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno)t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
求部门平均的薪水等级：

select avg(grade) from (select ename,deptno,grade from emp
join salgrade s on (emp.sal between s.losal and s.hisal))
group by deptno;
雇员中有哪些是经理人：

select ename from emp where empno in(select distinct mgr from emp);
不准用组函数求薪水最高值
select  distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));

//select ename,sal from emp where mgr=null;

求平均薪水最高的部门编号：
//select deptno max(avg(sal)) from emp  group by deptno
select deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(     select max(avg_sal) from
           (select avg(sal) avg_sal,deptno from emp group by deptno)
)

求平均薪水最高的部门名称：
select dname from dept where deptno=
(
  select deptno from
   (select avg(sal) avg_sal,deptno from emp group by deptno)
  where avg_sal=
   (select max(avg_sal) from
           (select avg(sal) avg_sal,deptno from emp group by deptno)
   )
)
求平均薪水等级最低的部门的部门名称：
 
select dname from dept where deptno=
  (select deptno from (select avg(grade) avg_grade from (select ename,deptno,grade from emp
     join salgrade s on (emp.sal between s.losal and s.hisal))
    (select min(avg_grade) from
        (select avg(grade) avg_grade from (select ename,deptno,grade from emp
           join salgrade s on (emp.sal between s.losal and s.hisal))
             group by deptno)
    )
  )

insert into dept value(100,'JANE','game');
insert into dept (deptno,dname) values (60,'game2');
insert into dept select * from dept2;
rollback   //回退

取薪水最高的员工从6到10的员工名称和薪水（重点）
select ename，sal from(select ename，sal，rownum r from
(     select ename,sal from emp order by sal desc)
)where r>=6 and r<=10;
面试题：
select sname from s join sc on(s.snum=sc.snum) join c on(sc.cnum=c.cnum) where c.cteacher<>'lingming';

select sname from s where sno in
(select sno from sc where scgrade<60 group by sno having count(*)>=2);

select sname from s where sno in
(select cno from sc where con=1 and con in(select cno from sc where con=2);
更新语句：
update emp2 set sal=sal*2,ename=ename||'_' where deptno=10;
删除语句：
delete from emp where deptno<25;

transaction 事务：
rollback 回退
commit   提交

create table stu
(
id number(3) primary key,
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age numbet(3),
class number(3),
grade number(3),
emial varchar(50),
constraint stu_class_fk foreign key(class) references class(id),
constraint stu_name_email_uni unique(name,email)
constraint stu_id_pk primary key(id);
);

create table class
(
id number(3) primary key,
name varchar2(20)
);