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)
);