程序员必须知道的数据库增删改查

–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