SQL之常用的查询语句

首先需要注意的是：查询语句select再怎么操作都不会改变表中的数据！！

1、查询语句的语法格式

select 列名1 ，列名2，etc from 表名称

where 行名称;

位于select关键词之后的列名用来决定哪些列作为查询结果返回。还可使用*来设定返回表格中的所有列。如：select * from emp      -查询emp表格中的所有列。

位于from关键字之后的表格名称用来决定将要查询操作的目标表格

where可选从句用来规定哪些数据值或者哪些行将被作为查询结果返回或显示。

在where从句中可以使用以下一些运算符来设定查询标准

=等于  >大于

SQL之常用的查询语句

2、简单查询语句

查看表结构：desc emp；

查询所有列：select * from emp；

查询指定列：select 列名1，列名2，列名3 from emp；

查询指定行：select * from emp  where city= ‘HeNan’；

select distinct mgr from emp; 只显示结果不同的项，其中关键字distinct表示去除部分列中的重复数据，只是在查询的时候去除，而该表内部数据纹丝不动。

修改结果集的列名（为列增加别名）：select code as '代号'，name as ‘姓名’ from info

条件查询：select * from info where code = ‘p003’;

多条件查询：查询info表中code为p003或者nation为n001的所有数据，select * from info where code = 'p003' or nation = 'n001'; 如果查询的是和，就把or改为and即可；

范围查询：select * from car where price>=40 and price <=60; 或者 select * from car where price between 40 and 60;

离散查询：查询汽车价格在（10,20,30,40,50,60）中出现的汽车信息  select * from car where price=10 or price=20 or price=30 or price=40 or price=50 or price=60； 或者  select * from car where price in(10,20,30,40,50,60)；

模糊查询（关键字查询）：查询car表里面名称包含奥迪的    select * from car where name like '%奥迪%'；                % 任意n个字符      查询car名字中第二个字符为马的汽车    select * from car where name like '_马%'；        _任意一个字符

排序查询：select * from car order by price asc ;        asc 升序      desc 降序  如果不带asc 或者desc，默认为asc 。先按brand升序排，再按照price降序排  select * from car order by brand,price desc

去重查询：select distinct from car

分页查询：一页显示10条，当前是第3页  select * from chinastates limit 20,10                                一页显示m条 当前是n 页 limit (n-1)*m,m

聚合函数：（统计函数）select count(areacode) from chinastates #查询数据总条数                        select sum(price) from car #求和        select avg(price) from car #求平均值                                        select max(price)from car #求最大值      select min(price) from car #求最小值

分组查询： 查询汽车表中每个系列下有多少个汽车  select brand,count(*) from car group by brand          查询汽车表中卖的汽车数量大于1的系列  select brand from car group by having count(*)>1          group by（用于对查询结果的分组统计） 和 having子句（用于限制分组显示结果）

连接运算符：可以通过“||”将两列查询的结果连接在一起  select price || count from car

使用算术表达式：select ename, sal*13+nvl(comm,0)  from emp;      nvl(comm,1)的意思是，如果comm中有值，则nvl(comm,1)=comm; comm中无值，则nvl(comm,1)=0。

select ename, sal*13+nvl(comm,0) year_sal from emp; （year_sal为别名，可按别名排序）

select * from emp where hiredate>'01-1月-82';

使用like操作符（%，_）: %表示一个或多个字符，_表示一个字符，[charlist]表示字符列中的任何单一字符，[^charlist]或者[!charlist]不在字符列中的任何单一字符。select * from emp where ename like 'S__T%';

在where条件中使用In :  select * from emp where job in ('clerk','analyst');

查询字段内容为空/非空的语句:select * from emp where mgr is/is  not null;

使用逻辑操作符号:select * from emp where (sal>500 or job='manage') and ename like 'J%';

3、复杂查询

数据分组(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;

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;

对于数据分组的总结：

a. 分组函数只能出现在选择列表、having、order by子句中（不能出现在where中）

b. 如果select语句中同时包含有group by, having, order by，那么它们的顺序是group by, having, order by。

c. 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须出现在group by子句中，否则就是会出错。

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

多表查询：select e.name,e.sal,d.dname from emp e, dept d where e.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;

自连接（指同一张表的连接查询）：select er.ename, ee.ename mgr_name from emp er, emp ee where er.mgr=ee.empno;

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

1 单行子查询

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

3 使用ALL

select ename,sal,deptno from emp where sal> ALL (select sal from emp where deptno=30);或select ename,sal,deptno from emp where sal> (select max(sal) from emp where deptno=30);查询工资比部门号为30号的所有员工工资都高的员工的姓名、薪水和部门号。以上两个语句在功能上是一样的，但执行效率上，函数会高 得多。

4 使用ANY

select ename,sal,deptno from emp where sal> ANY (select sal from emp where deptno=30);或select ename,sal,deptno from emp where sal> (select min(sal) from emp where deptno=30);查询工资比部门号为30号的任意一个员工工资高（只要比某一员工工资高即可）的员工的姓名、薪水和部门号。以上两个语句在功能上是 一样的，但执行效率上，函数会高得多。

5 多列子查询

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

6 在from子句中使用子查询

select emp.deptno,emp.ename,emp.sal,t_avgsal.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;

7 分页查询

数据库的每行数据都有一个对应的行号，称为rownum.

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

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

8 用查询结果创建新表

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

9 合并查询（union 并集, intersect 交集, union all 并集+交集, minus差集)

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

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

10 使用子查询插入数据

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 from emp where deptno=10; 再将emp表中部门号为10的数据插入到新表myEmp中，实现数据的批量查询。

11 使用子查询更新表中的数据

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