基础的sql练习，全都理解你就是高手了！

　　select e.empno as "雇员编号",e.ename as "雇员姓名",e.job as "雇员职位" from emp e

　　select e.job as "雇员职位" ,e.* from emp e;

　　select e.empno as "雇员编号",e.ename as "雇员姓名",(e.sal+IFNULL(comm,0)*12) as "基本年薪" from emp e

　　select e.empno as "雇员编号",e.ename as "雇员姓名",(e.sal+IFNULL(comm,0)+200+300)*12 as "基本年薪" from emp e;

　　select e.* from emp e where e.sal>2000;

　　select e.* from emp e where e.ename="SMITH";

　　select e.* from emp e where e.job != "CLERK";

　　select e.* from emp e where e.job <> "CLERK";

　　select e.* from emp e WHERE e.job not in("CLERK");

　　select e.* from emp e where e.job = "SALESMAN" && e.sal>1300;

　　select e.* from emp e where e.job in ("SALESMAN") and e.sal>1300;

　　select e.* from emp e where e.sal>=1500 and e.sal<= 3000;

　　select e.* from emp e where e.sal BETWEEN 1500 and 3000;

　　select e.* from emp e WHERE (e.job=‘SALESMAN‘ or e.job=‘manager‘) and e.sal>1500;

　　select * from emp e where e.HIREDATE between(‘1981-1-01‘) and(‘1981-12-31‘);

　　select * from emp e where e.hiredate >= ‘1981-1-01‘ and e.hiredate <= ‘1981-12-31‘;

　　select * from emp e where e.comm is not null;

　　select * from emp e where e.comm="";

　　select e.* from emp e WHERE e.comm > 100;

　　select e.* from emp e where e.empno in (7369,7566,9999);

　　select * from emp e where e.ename like "A%";

　　select * from emp e where SUBSTR(e.ename,2,1)=‘m‘;

　　select * from emp e where e.ename like "%_m";

　　select * from emp e WHERE e.ename like "%a%";

　　select * from emp e where regexp_like(first_name,‘[,a]+‘);（正则表达式）

select * from emp e order by e.sal asc;（默认是asc,是升序）

select * from emp e order by e.sal desc;（desc,是降序）

　　select * from emp e order by e.sal desc,e.hiredate asc;

　　(select * from emp e where e.job=‘manager‘ and e.DEPTNO=10 )union all(SELECT * from emp b where b.job=‘clerk‘ and b.DEPTNO=20);

        select * from emp e where e.deptno = 10 and e.job = ‘Manager‘ union select * from emp e where e.deptno = 20 and e.job = ‘clerk‘;

　　(select * from emp e where e.job!=‘manager‘ and e.DEPTNO=10 and e.SAL>=2000 and e.job != ‘clerk‘ )union (select * from emp b where b.job!=‘clerk‘ and b.job!= ‘manager‘and b.DEPTNO=20 and b.sal>=2000);

28.查询出收取奖金的员工的不同工作。

　　select a.empno as "员工编号",a.ename as "员工姓名" , b.empno"上司编号",b.ename as "上司姓名", (B.SAL+IFNULL(B.COMM,0))*12 "领导年工资"
　　from emp a,emp b
　　where a.mgr= b.empno
　　orderby by (b.sal+IFNULL(b.comm,0))*12 desc;

　　select e.ename as "员工姓名",e.empno as "员工编号",e.sal as "员工薪资",e.hiredate as "雇佣日期",d.dname as "部门名称" from emp e,dept d where e.job="salesman" and e.deptno = d.deptno;

　　select emp.ename as "雇员姓名",dept.dname as "雇员职位",emp.sal as "雇员薪资" from emp,dept WHERE emp.deptno = dept.deptno;

　　select (e.sal+IFNULL(e.comm,0))*12 as allYearSales,d.dname as "部门名称",e.* from emp e,dept d where e.deptno = d.deptno order by allYearSales desc;

　　select distinct
　　e2.ename,d.dname
　　from emp e1,emp e2,dept d
　　where e1.mgr = e2.empno && e2.deptno = d.deptno && e2.sal > 300;

　　select MAX(e.sal) as "最高工资",MIN(e.sal) as "最低工资" from emp e;

　　select deptno as "部门编号",count(e.deptno) as "部门员工数量",avg(e.sal) as "平均薪资" from emp e group by e.deptno;

　　SELECT job as "职位", MAX(sal) as "最高薪资",MIN(sal) as "最低薪资" FROM emp group by job;

　　select job as "职业",count(job) as "从事的职业数量",AVG(sal) as "平均薪资" from emp group by job having AVG(sal)>2000;

41查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。　　

　　select
　　e.deptno as "部门编号", d.dname as "部门名称", AVG(sal) as "部门平均薪资",MIN(sal) as "最低薪资",MAX(sal) as "最高薪资"
　　from
　　emp e,dept d
　　where
　　e.deptno = d.deptno
　　group by
　　d.dname;

　　select p.deptno as "部门编号",p.dname as "部门名称",p.loc as "部门地址",count(job)as "部门人数",IFNULL(AVG(e.sal),0) as "部门平均工资" from emp e
　　right join dept p
　　on e.deptno = p.deptno
　　group by p.dname;   （右连接）