–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