一、简单查询：
select * from userinfo; select username,salary from userinfo; select username,salary+200 from userinfo; select username from userinfo where salary>800 and salary <>1500.5; select * from userinfo where username='aaa'; select * from userinfo where not(username='aaa'); select * from userinfo where username like '%a%'; select * from userinfo where username like '_a%'; select * from userinfo where salary between 800 and 2000; select * from userinfo where salary not(between 800 and 2000); select * from userinfo where salary in(800,2000,3000); select * from userinfo where salary not in(800,2000,3000); select * from userinfo order by id desc/asc; select * from userinfo order by id desc,username asc; select username,case username when 'aaa' then '计算机部门' when 'bbb' then '市场部' else '其他部门' end as 部门 from userinfo;或者：
select username,case when username= 'aaa' then '计算机部门' when username= 'bbb' then '市场部' else '其他部门' end as 部门 from userinfo;设置别名： select id as 编号,username as 用户名,salary as 工资 from userinfo; -设置字段别名 select u.id,u.username,u.salary from userinfo u; -设置表别名 二、高级查询： 1、分组查询：(分组函数会自动过滤空值) 1）常用的分组函数：AVG,SUM,MIN,MAX,COUNT,WM_CONCAT SELECT AVG(SALARY),SUM(SALARY) FROM EMPLOYEE; -求表中工资（字段）的平均值和和 select max(salary),min(salary) from employee; --求最大最小值 select count(*) from employee; --查询表中总记录数 select count(distinct deptno) from employee; -查询不重复部门号的个数 select sum(sal)/count(*) as 一,sum(sal)/count(sal) as 二,avg(sal) as 三 from emp; select avg(nvl(sal,0)) from emp; --nvl函数表示如果sal为空，则返回0，否则返回sal 2）group by语句 select deptno,avg(salary) from emp group by depno; -求各个部门的平均工资 select sum(salary) from emp group by deptno,job; -求各个部门、各个职位的工资总和 select avg(salary) from emp group by deptno,job having avg(salary)>2000; -having过滤结果，where子句中不能使用分组函数 select avg(salary) from emp group by deptno,job order by avg(salary); -按照部门、职位查询平均工资，并按照平均工资排序 3）group by语句的增强：语法 group by (a,b); 主要用在报表功能中 select deptno,job,sum(salary) from emp group by rollup(deptno,job); 2、多表查询：（笛卡尔集的应用！！！） select e.eid,e.username.e.salary,d.deptname from emp e,dept d where e.deptno=d.deptno; -等值连接（连接条件） 外连接：（核心：通过外连接，可以把对于连接条件不成立的记录，仍然包含在最后的结果中） 左外连接：（当连接条件不成立的时候，等号左边的表仍然被包含在记录当中）写法：在等号的右边写 (+) 例如:where e.deptno=d.deptno(+); 右外连接：（当连接条件不成立的时候，等号右边的表仍然被包含在记录当中） 写法：在等号的左边写 (+) 例如:where e.deptno(+)=d.deptno; select d.name,d.job from emp e,dept d where e.deptno(+)=d.deptno; 自连接：（核心：通过别名，将同一张表视为多张表）（注意：自连接不适合操作大表） select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno; 3、子查询：(select语句的嵌套) select salary from emp where salary > (select salary from emp where ename = 'scott'); 注：子查询可以放在select、where、having、from后面，但不能放在group by后面 select * from (select eno,ename,salary,salary*12 年薪 from emp); 1）单行子查询(使用单行操作符：=，>,<等)：返回一条记录的子查询 select * from empwhere job = (select job from emp where empno = 7566) and salary > (select salary from emp where empno = 7782); 2）多行子查询（使用多行操作符：in，any，all）：返回多条记录的子查询 select * from emp where deptno in (select deptno from dept where dname = 'sales' or dname = 'accounting'); select * from emp where salary > any (select salary from emp where dno = 30); 3）多行子查询的空值问题： select * from emp where eno not in (select mgr from emp); -子查询中有空值，所以返回不了记录 select * from emp where eno not in (select mgr from emp where mgr is not null); -去掉子查询里面的空值，就可以了 三、综合案例： 1、分页查询 显示员工信息：员工号、姓名、月薪 -每页显示四条记录 -显示第二页的员工 -按照月薪排序
select r,eno,ename,salary from( select rownum r,eno,ename,salary from (select rownum,eno,ename,salary from emp order by salary desc) e1 where rownum <= 8) e2 where r>=5
