sql语句4——复杂查询

1、多表查询

1.1 基础

select ｛distinct｝ */表名.列名1,表名.列名2……

from 表名1，表名2……

｛where 条件｝

｛ order by排序字段 asc/desc｝;

ps:括号中为可选项

select * from emp,dept where emp.deptid = dept.deptid order by emp.deptid DESC;

注意：排序字段必需标明所在表

查询emp表的记录数

select COUNT(*) from emp;

注意：在使用多表时会产生笛卡尔乘积，这些记录中许多是重复信息，所以在查询多表时要使用关联字段作为条件，去掉笛卡尔乘积

select * from emp,dept where emp.deptid = dept.deptid;

使用多表查询时，建议使用别名

select a.empname,a.empage,a.empaddress,b.deptname from dept b,emp a where a.deptid = b.deptid;

1.2、左右连接查询

内连接查询：

select {distinct} * | 表名.列名1 别名1.列明 ，表名.列名2，别名2.列明 ,…

from 表名称1 别名1 inner join 表名称2 别名2，…

on 关联条件

{where 条件{s}}

{ order by 排序字段 asc/desc，排序字段 asc/desc}

select * from dept inner join emp on emp.deptid = dept.deptid;

左连接查询：

select * from dept left join emp on emp.deptid = dept.deptid;

注意：以左表为基准，无论关联与否，左表全查

右连接查询：

select * from dept right join emp on emp.deptid = dept.deptid;

注意：以右表为基准，无论关联与否，右表全查

2、组函数及分组统计

2.1、分组在sql语句中的语法

select {distinct} * | 列名1 别名1 ，列名2，别名2 ,…

from 表名称1 别名1

{where 条件{s}}

group by 分组条件

{order by 排序字段asc/desc，排序字段asc/desc}

注意：group by的位置

select emp.empage from emp group by emp.empage;

注意：单独使用分组条件时，以什么字段作为分组条件，那么查询字段就必须是什么字段

2.2、sql中常用的统计函数

~COUNT() : 全部的记录数

~MAX() : 最大值

~MIN() : 最小值

~AVG() : 平均数

~SUN() : 求和

查询员工总数：

select count(1) from emp;

ps：count(1)中填1有助于提高查询效率

查询员工年龄最大/最小值：

select max(emp.empage) as 'age' from emp;<pre name="code" class="sql">select min(emp.empage) from emp;

查询员工年龄平均值：

select avg(emp.empage) as 'avg age' from emp;

查询所有部门人数之和：

select sum(dept.deptnum) from dept;

注意：sum()，括号中字段一般为数值型数据或日期类型数据

2.3、分组统计的综合运用

查询员工信息表中“男“、”女“员工的个数

  分析： |- 以性别作为标准进行分组

            |- 使用COUNT（）统计函数

select emp.empsex,count(empid) from emp group by emp.empsex;

查询每个部门的员工人数

分析： |- 查询使用到了多表

           |- 以部门编号作为分组标准

                    |- 使用COUNT（）统计函数

select a.deptid,count(b.empid) from dept as a,emp as b where a.deptid = b.deptid group by a.deptid;

查询每个部门的员工的平均年龄

  分析： |- 查询使用到了多表

           |- 以部门编号作为分组标准

           |- 使用AVG（）统计函数

select a.deptid,avg(b.empage) from dept a,emp b where a.deptid = b.deptid group by a.deptid;

注意：使用分组时不能出现分组函数和分组条件之外的字段

2.4、having 的使用

HAVING是在分组的基础上进行筛选，语法如下：

select {distinct}  * | 列名1 别名1 ，列名2，别名2 ,…

from 表名称1 别名1

{where 条件{s}}

group by 分组条件 {having 分组条件}

{ order by 排序字段 asc/desc，排序字段 asc/desc}

查询员工平均年龄在22岁之上所属部门编号

   分析 |- 以部门编号作为分组标准

    |- 分组后，条件筛选使用AVG（）函数

select emp.deptid from emp where true group by emp.deptid having avg(emp.empage) > 22;

ps：只在某列中出现重复，才需要分组，否则没有意义

3、分页语句

如果数据特别多，一次在页面中展示不完，我们就需要用到分页展示数据，这就需要分页的sql语句

mysql分页的关键字为： limit  x,y

X为起始的记录数（排除前几条记录开始）， Y为每次展示的记录个数（分页标准）

语法如下：

select {distinct} * | 列名1 别名1 ，列名2，别名2 ,…

from 表名称1 别名1

{where 条件{s}}

{order by 排序字段 asc/desc，排序字段asc/desc}

{limit  ((当前页-1) * 分页标准), 分页标准}

select * from emp limit 0,3;