sql语句

完整的DQL语句：select … from … where … group by … having … order by … limit …

执行顺序                 5            1           2              3                4               6              7

1. 普通查询语句

基础语法： select 字段1，字段2，字段3,… from 表名；

select ename from emp;

select ename, sal*12 as ‘年薪’ from emp;//创建别名‘年薪’，单引号，as可省略

select * from emp; 查询所有字段

1.1 条件查询

运算符：

—————————————————————

=　　　　　　　　　　等于

<>或!=　　　　　　　　不等于

或>=　　  　　大于、小于

between…and…　　　　两个值之间，左小右大，等同>=and<=。还可以用在字符串：between ‘A’ and ‘C’,[A,C),开区间

is null或is not null　　　 为不为空

and　　　　　　　　　 并且第二个条件

or　　　　　　　　　   或者第二个条件

in　　　　　　　　　   包含，相当于多个or，in(100,1000)指是否等于100或1000这两个值，而不是100到1000的范围

not　　　　　　　　    取非，主要用在is或者in中，is not, not in

like　　　　　　　　    模糊查询，%代表任意多个字符，_代表任意单个字符。%A%代表包含A的子串，_A%代表第二个字符是A的。

若要查询包含’%’或’_’,需要转义’\%’，’\_’

————————————————————————–

语法： select 字段1，字段2，字段3,… from 表名 where 条件；

执行顺序： 先from，然后where，最后select

select sal from emp where ename = ‘SMITH’;//VARCHAR类型字符串需要加‘’

select ename, sal from emp where sal>=1000 and sal <= 3000;//查询工资在1000和3000之间

select ename, sal from emp where sal between 1000 and 3000; //功能同上，between..and是闭区间[1000,3000],左小右大

select ename, sal from emp where sal <> 3000; // !=和<>都可以表示不等于

select ename, sal from emp where sal > 1000 and (deptno = 20 or deptno = 30);//and优先级比or高，不确定就加括号

select ename, sal from emp where sal > 1000 and deptno in (20, 30);//功能同上，in等同or

select ename from emp where ename like ‘%A%’;//找出名字中含有A字符的

Note: NULL在数据库中不是一个值，做赋值运算结果总是NULL：300+NULL=NULL，使用is null或is not null

1.2 数据排序

语法： select 字段1，字段2，…from 表名 order by 规则

asc表示升序，desc表示降序。

select ename, sal from emp order by sal;//默认按工资升序排列

select ename, sal from emp order by sal asc;//同上，升序

select ename, sal from emp order by sal desc;//降序排

select ename, sal from emp order by sal desc, ename asc;//按工资降序，工资相同按名字升序

多个条件排序，优先按前面规则，后面规则可能用不上

组合： select 字段 from 表名 where 条件 order by xxx;

执行顺序： from最先，where第二，select第三，order by最后

例子： select ename, sal*12 as ‘年薪’ from emp where job = ‘salesman’ order by ‘年薪’ desc;

例子中select语句先定好年薪，order by是按select定的别名排列

1.3 单行处理函数(输入一行输出一行)

ifnull() 空处理函数

用法： ifnull(可能为NULL的数据，被当做什么)

select ename,(sal+ifnull(comm, 0))*12 from emp;//列出所有人的年薪(工资加补贴)

1.4 分组函数(多行处理函数) 一般和group by联合使用，在group by之后执行

count计数

sum求和

avg平均值

max最大值

min最小值

所有的分组函数都是对‘某一组’数据进行操作的

select sum(sal) from emp;//求和

select max(sal) from emp;//找最大值

select count(ename) from emp;//找出ename的总人数

select count(*) from emp;//找出总人数

NOTE：分组函数自动忽略NULL

select ename, sal from emp where sal > avg(sal);//语法错误！分组函数不能直接出现在where语句中

select ename, sal from emp where sal > (select avg(sal) from emp);//子查询，查询工资高于平均工资的员工

count(*)和count(字段)区别：

count(*): 不是统计某个字段个数，是统计总条数(和某个字段无关)

count(字段)： 统计某个字段中不为NULL的总条数

1.5 group by和having

group by： 按照某个字段或某些字段进行分组

having: 对分组后的数据进行再次过滤，只能在group by后联合使用

找出每个工作岗位的最高薪资：先分组再查询

select max(sal),job from emp group by job;

NOTE： 当查询语句中有group by时候，select之后只能跟分组函数和被分组的字段！！

select ename,max(sal),job from emp group by job;//错误！ ename没参加分组，此时不能加入查询

多个字段联合分组：(找出每个部门不同工作岗位的最高薪资)

select max(sal), job, deptno from emp group by job, deptno;

找出每个工作岗位的最高薪资，要求显示薪资大于2500的。

select max(sal), job from emp group by job having max(sal)>2500;//效率低

select max(sal), job from emp where sal>2500 group by job;//where先过滤，减少数据查询，效率高

能使用where过滤的不要使用having，除非where不能用。

比如：找出每个岗位的平均薪资，要求显示薪资大于2000的岗位。

select avg(sal), job from emp group by job having avg(sal)>2000;//此处where后不能跟分组函数，只能having

1.6 查询结果去重 distinct

select distinct job from emp;

Note: distinct只能出现在所有字段最前面，代表所有字段联合起来去重

select distinct deptno,job from emp;

select count(distinct job) from emp;//统计岗位的数量

2. 连接查询(多张表联合查询)

根据表的连接方式划分：

内连接 假设表A和表B连接，把A和B能够匹配的记录查询出来，AB两张表没有主副之分

等值连接

非等值连接

自连接

外链接 假设表A和表B连接，AB中有一张是主表，一张副表，主要查询主表数据，捎带查询附表。

假如副表中数据没有和主表匹配上，副表自动模拟出NULL与之匹配。

左外连接(左连接)：左边表是主表

右外连接(右连接)：右边表是主表

全连接 (很少用)

连接查询，一般用别名，来区分两张表内相同名字的字段(类似类中成员变量)

select e.ename,d.dname from emp as e, dept as d;

2.1 笛卡尔乘积现象：如果连接两张表联合查询没有条件限制，查询结果就变成了两个表乘起来

避免了笛卡尔乘积现象，不能减少匹配次数，只是显示的结果是有效记录

select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;//SQL92, 老语法

2.2 内连接–等值连接：最大特点是，条件是等量关系

SQL99： join…on…语法

语法： …from A表 (inner 可以省略)join B表 on 连接条件 where … (表连接和where条件分离，SQL99语法更清晰)

select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno;

2.3 内连接–非等值连接：最大特点是，条件是非等量关系

//找出员工工资所在的等级，员工表+工资等级表

select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

2.4 内连接–自连接： 最大特点是，一张表看做两张表，自己连自己

//找出每个员工的上级领导，要求显示员工名和对应领导名：

//可以分成两个表，一个员工表，一个领导表。关键：员工的领导编号，等于领导的员工编号

select

a.ename as ‘员工名’, b.ename as ‘领导名’

from

emp as a

join

emp as b

on

a.mgr = b.empno;

2.5 外连接：比内连接使用多

//找出每个员工的上级领导，要求显示员工名和对应领导名，King没有领导，也要查出来：

select

a.ename as ‘员工名’, b.ename as ‘领导名’

from

emp as a

left join //左外连接， outer可以省略

emp as b

on

a.mgr = b.empno;

//找出没有员工的部门

select

d.*

from

emp as e

right join

dept as d

on

e.deptno=d.deptno

where

e.ename is null;

NOTE：外连接最重要特点是：主表数据无条件查询出来

2.6 三张表联合查询

A join B on … join C on …表示A先和B连接，连接之后再和C连接

//找出每一个员工的部门名称和工资等级

select

e.ename, d.dname, s.grade

from

emp as e

join

dept as d

on

e.deptno = d.deptno

join

salgrade as s

on

e.sal between s.losal and s.hisal;

//找出每一个员工的部门名称和工资等级以及上级领导

select

e.ename ‘员工’, d.dname, s.grade, m.ename ‘领导’

from

emp as e

join

dept as d

on

e.deptno = d.deptno

join

salgrade as s

on

e.sal between s.losal and s.hisal

left join

emp as m

on

e.mgr = m.empno;

3. 子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询，可以出现在select/from/where之后

3.1 where子语句中使用子查询

select ename,sal from emp where sal > (select avg(sal) from emp);//找出工资比平均工资高的

3.2 from后面嵌套子查询

//找出每个部门平均薪水的薪资等级

select d.deptno, s.grade from

1. select avg(e.sal) from emp as e;//找出所有人平均工资

2. select deptno, avg(sal) as average from emp group by deptno;//找出每个部门平均工资

3. 把上一步查询结果当做一个表temp

select temp.*, s.grade

from temp

join salgrade as s

on temp.average between s.losal and hisal;

也就是

select temp.*, s.grade

from (select deptno, avg(sal) as average from emp group by deptno) temp

//right join dept as d

//on temp.deptno=d.deptno

join salgrade as s

on temp.average between s.losal and hisal;

//找出每个部门薪资的平均等级

select e.deptno, s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;

//select temp.deptno, avg(temp.s.grade) from temp group by temp.e.deptno;不用当做临时表。。

select

e.deptno, avg(s.grade)

from

emp as e

join

salgrade as s

on

e.sal between s.losal and s.hisal

group by

e.deptno;

3.3 在select后面嵌套子查询

//找出每个员工所在的部门名，显示员工名和部门名

原来的方法：

select e.ename, d.dname from emp join dept on e.deptno = d.deptno;

新方法：

select

e.ename, (select dname from dept as d where e.deptno=d.deptno)

from

emp as e;

4. union查询, 拼接查询结果

第一个查询列的数量和第二个查询列的数量要一致！

//找出工作岗位是salesman和manager的员工

方法1： select ename, job from emp where job=’salesman’ or job=’manager’;

方法2： select ename, job from emp where job in (‘salesman’,’manager’);

方法3： select ename, job from emp where job = ‘salesman’

union

select ename, job from emp where job = ‘manager’;

5. limit(分页查询)

5.1 limit是mysql特有的，不通用。Oracle中有个类似的rownum

5.2 limit取结果集中的部分数据

5.3 语法机制

limit startIndex，length 从起始位置取几个，startIndex不写默认从0

例子： 取出工资前5名的员工(先降序，再取)

select ename, sal from emp order by dest limit 0, 5;