程式員必須知道的資料庫增删改查

–insert插入語句

–向員工表中插入資料

insert into emp values(9257,’張三’,’程式遠’,007,sysdate,20,null,10);

–建立一個臨時表

create table ttemp as select *from emp where 1=2;

–查詢結果

insert into ttemp select *from emp;

select *from ttemp;

select *from emp;

–删表

truncate table temp;

–update 更新語句

update emp set mgr=8888,hiredate=sysdate,sal=999,deptno=20 where empno=1245;

–連表查詢

–查詢員資訊，部門名稱資訊

select *from emp e, dept d where e.deptno =d.deptno;

–查詢員工姓名和部門名稱

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

–查詢員工所有資訊和部門名稱

select e.*,d.dname from emp e ,dept d where e.deptno=d.deptno;

–查詢工資大于3000的員工姓名和工資和部門名稱

select e.ename,e.sal,d.dname from emp e ,dept d where e.deptno=d.deptno and e.sal>3000;

–非等值查詢

–查詢員工的工資等級

select *from emp e,salgrade s where e.sal<=s.hisal and e.sal>s.losal;

–左外連接配接

select *from emp e,dept d where e.deptno=d.deptno(+);

select *from emp e,dept d where e.deptno(+)=d.deptno;

–自連接配接

–查詢員姓名和員工經理姓名

select e.ename, d.ename from emp e ,emp d where e.mgr=d.empno;

select a.ename,b.ename,c.ename from emp a ,emp b, emp c where a.mgr=b.empno and b.mgr=c.empno;

–cross join 笛卡爾

select *from emp cross join dept d;

–自然連接配接

select *from emp natural join dept;

–using

select e.ename ,deptno from emp e join dept d using(deptno);

–on 自定義連接配接條件

select *from emp e join dept d on e.deptno=d.deptno;

select *from emp e join emp e1 on e.mgr=e1.empno;

–查詢經理姓名和經理姓名

select e.ename,e1.ename,e2.ename from emp e join emp e1 on e.mgr=e1.empno join emp e2 on e1.mgr= e2.empno;

–inner join兩邊都合法

select *from emp e inner join dept d on e.deptno=d.deptno;

–left join左連接配接左表為主

select *from emp e left join dept d on e.deptno=d.deptno;

–right join右邊為主

select *from emp e right join dept d on e.deptno=d.deptno;

–full join 全連

select *from emp e full join dept d on e.deptno=d.deptno;

–查詢員工姓名和部門姓名和其經理的名字和經理的部門名稱

select e.ename, d.dname,e2.ename, d2.dname

from emp e left join dept d on e.deptno=d.deptno left join emp e2

on e.mgr=e2.empno left join dept d2 on e2.deptno=d2.deptno;

–子查詢

– 思考：查詢工資高于平均工資的雇員名字和工資。

select ename from emp where sal>(select avg(sal) from emp );

– 思考：查詢和SCOTT同一部門且比他工資低的雇員名字和工資。

select ename,sal from emp where deptno=(select deptno from emp where ename=’SCOTT’ )and sal<(select sal from emp where ename=’SCOTT’);

– 多行子查詢 查詢和s,s,c在同一個部門的員工資訊并不包含s,s,c;

select * from emp where deptno in(select distinct deptno from emp where ename in(‘SCOTT’,’SMITH’,’CLARK’))and ename not in(‘SCOTT’,’SMITH’,’CLARK’);

–思考：查詢工資最高的雇員名字和工資。

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

–查詢職務和SCOTT相同，比SCOTT雇傭時間早的雇員資訊

select *from emp where job=(select job from emp where ename=’SCOTT’)and hiredate<(select hiredate from emp where ename=’SCOTT’);

—- 多行查詢

–查詢工資低于任何一個“CLERK”的工資的雇員資訊。

select *from emp where sal