SQL查询语句总结

SQL查询语句总结

一、基本查询

1. 查询所有列

   select * from emp;

2. 查询指定列

   select empmo,ename,mgr from emp;select distinct mgr from emp; 只显示结果不同的项

3. 查询指定行

   select * form emp where job='clerk';

4. 使用算术表达式

   select ename,sal*13+nvl(comm,0) from emp;

   nvl(comm,0)的意思是，如果comm中有值，则nvl(comm,0)=comm;comm中无值，则nvl(comm,0)=0。

   select ename,sal*13+nvl(comm,0) as year_sal from emp;select * from emp where hiredata>'01-1月-82';

5. 使用like操作符（%，_）

   %表示一个或多个字符，_ 表示一个字符，[charlist]表示字符列中的任何单一字符，[^charlist] 或者[!charlist] 不在字符列中的任何单一字符。

   select * from emp where ename like 'S_T';

6. 在where条件中使用in

   select * from emp where job in ('clerk','analyst');

7. 查询字段内容为空/非空的语句

   select * from emp where mgr is/(is not) NULL;

8. 使用逻辑操作符号

   select * from emp where (sal>500 or job='manage') and ename like 'j%'

9. 将查询结果按字段的值进行排序

   select * from emp order by deeptno,sal desc;(按部门升序，并按薪酬降序)

二、复杂查询

1. 数组分组（max,min,avg,sum,count）

   select max(sal),min(age),avg(sal),sum(sal) from emp;select * from emp where sal=(select max(sal) from emp);select count(*) from emp;

2. group by（用于对查询结果的分组统计）和having子句（用于限制分组显示结果）

   select deptno,max(sal),avg(sal) from emp group by deptno;select deptno,job,avg(sal),min(sal) from emp group by deptno,job having avg(sal)<2000;

   • 对于数组分组的总结

     1. 分组函数只能出现在选择列表，having、order by 子句（不能出现在where中）
     2. 如果select语句中同时包含group by，having，order by，那么它们的顺序是group by，having，order by。
     3. 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须出现在group by子句中，否则会出错。

     使用group by 不是使用having的前提条件

3. 多表查询

   select e.name,e.sal,d.dname from emp e,dept d where d.deptno=d.deptno order by d.deptno;select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s. hisal;

4. 自连接（指同一张表的连接查询）

   select er.ename,ee.ename m_name from emp er,emp ee where er.mgr=ee.empno

5. 子查询（嵌入到其他sql语句中的select语句，也叫嵌入查询）

   1. 单行子查询

      select ename form emp where deptno=(select deptno from emp where ename='smith');

      查询表中于smith同部门的人员名字。因为返回结果只有一行，所以用”=”连接子查询语句

   2. 多行子查询

      select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);

      查询表中与部门号为10的工作相同的员工的姓名、工作、薪水、部门号。因为返回结果有多行，所以用”in”连接查询语句。

   in 与 exists 的区别：

   exists()后面的子查询被称作相关子查询，它是不返回列表的值的。只是返回一个true或false的结果，其运行方式是先运行主查询一次，再去子查询里查询与其对应的结果。如果是true则输出，反之不输出。再根据主查询中的每一行去子查询里查询。in() 后面的子查询，是返回结果集的，换句话说执行次序和exists() 不一样。子查询先产生结果集，然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出，反之不输出。

   1. 使用all

      select ename,sal,deptno form emp where sal > all(select sal form emp where deptno=30);select ename,sal,deptno form emp where sal>(select max(sal) from emp where deptno=30);

      查询工资比部门号为30号的所有员工工资都高的员工的姓名、薪水和部门号。以上两个语句在功能上是一样的，但是执行效率上，函数会高得多。

   2. 使用any

      select ename,sal,deptno from emp where sal> any(select sal form emp where deptno=30);select eename,sal,deptno form emp where sal>(select min(sal) from emp where deptno=30);

      查询工资比部门号为30号的任意一个员工工资高（只要比某一员工工资高即可）的员工的姓名、薪水和部门号。

   3. 多列子查询

      select *from emp where(job,deptno)=(select job,deptno form emp where ename='smith');

   4. 在from子句中使用子查询

      select emp.deptno,emp.ename,emp.sal,t_avgsal from emp,(select emp.deptno,avg(emp.sal) avgsal from emp group by emp.deptno) t_avgsal where emp.deptno=t_avgsal.deptno and emp.sal>t_avgsal.avgsal order by emp.deptno;

   5. 分页查询

      数据库的每行数据都有一个对应的行号，成为ROWNUM

      select a2.* from (select a1.*,ROWNUM rn from(select *from emp order by sal) a1 where ROWNUM<=10)a2 where rn>=6

      指定查询列、查询结果排序等，都只需要修改最里层的子查询即可。

   6. 用查询结果创建新表

      create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

   7. 合并查询（union 并集，intersect 交集，union all 并集+交集，minus 差集）

      select ename,sal,job from emp where sal>2500 union(intersect/union all/minus) select ename,sal job form emp where job='manager'

      合并查询的执行效率远高于and，or 等逻辑查询。

   8. 使用子查询插入数据

      create table myEmp(empID number(4),name varchar2(20),sal number(6),job varchar2(10),dept number(2));先建一张空表insert into myEmp(empID,name,sal,job,dept) select empno,ename,sal,job,deptno form emp where deptno=10;再将emp表中部门号为10的数据插入到新表myEmp中，实现数据的批量查询。

   9. 使用了查询更新表中的数据

      update emp set(job,sal,comm)=(select job,sal,comm form emp where ename='smith') where ename='scott'

转载地址