Mysql

用户1076812099596
131 阅读10分钟

查询

学习目标

  • 背单表查询中七个查询命令使用规则
  • 背多表查询合并规则-链接合并查询
  • 子查询的使用规则
  • 解释字段使用规则

临时表

临时表：查询命令在内存中生存表，这个表看不到也摸不着
七个查询命令中，除了from命令以外。剩下的六个查询命令执行时操作的都是上一个命令生成的临时表

from

执行优先级：from>select 
书写顺序：一个标准查询语句必须包含一个select和一个from命令

where

 where 使用判断条件
     - 关系表达式 >  >= = < <= !=
     - 逻辑表达式 and or
     - 特殊运算符 in jon in ('','') | not in |is null |is not null
     - 关系表达式 
     

## is null 
 例子：查询comm为null的职员信息
    select ename,comm from emp where comm is null
    
## between...and..
 例子:查询工资在1000~2000范围之间的员工薪资
 select * from emp where sal between 1000 and 2000
 
 ## 模糊查询
 #分类 前置模糊查询 
        #查询姓名以S开头的职员
        select * from emp where ename like 's%';
       后置模糊查询
        #查询姓名以S结尾的职员
        select * from emp where ename like '%s';
        #查询姓名中包含S的职员
        select * from emp where ename like '%s%';
        #查询姓名中第二个字母是A的职员
        select * from emp where ename like '_A%';
        #查询第三个字母是A的职员
        select * from emp where ename like '_A%';

聚合函数

#max(字段) 查询字段中的最高值
#查询20部门中最高工资
select max(sal) from emp where deptno = 20
#min(字段) 查询字段中的最小值
#查询做销售保底工资
select min(sal) from emp where job = 'salesman'
#avg(字段) 查询字段中的平均值
#查询做销售的平均工资
select avg(sal) from emp where job = 'salesman'
#sum(字段)：得到临时表中指定字段所有数据相加之和
select sum(sal) from emp 
#count(字段) 得到临时表中指定字段下所有内容不为null的数据个数
#查询补助不为null的人数
select count(comm) from emp
#count(*):得到临时表中总行数，计算数据行时将null包含在内
select count(*) from emp
# 问题：select,from,where 谁能调用聚合函数
	# 七个查询查询命令、只有select/having

group by

作用：分组查询命令。统计临时表指定字段下数据种类个数，然后将拥有相同特征数据存入到同一个临时表 七个查询命令：group by 为一个有机会返回多个临时表查询命令

执行顺序：FROM > WHERE > GROUP BY > SELECT

# 查询部门20下各个职位的人数
select deptno,job,count(*) from emp where deptno = 20 group by job

#查询各个职位的最低薪资 可以根据执行顺序进行 from > where > group by > select
select job,min(sal) from emp group by job

多字段分组

  • 多字段分组时，分组字段声明顺序对于查询结果没有影响
  • group by 每次只能根据一个分组字段进行分组
  • 从第二个分组字段开始操作的上一个分组字段生成的临时表
#查询各个部门职位名称和职位人数（多字段分组 [deptno,job]  [job,deptno]）
 select deptno,job,count(*)   from emp  group by  deptno,job

select

如果临时表提供方时(where或者from):此时select面对临时表只有一个，将临时表 指定字段下所有的数据读取出来，并合并为一个全新的临时表

如果临时表提供方时 groupBy 此时select往往要面多个临时表，以此操作每一个临时表。在操作某一个临时表时，只会读取当前临时表的第一个值。然后将从多个临时表读取数据行在内存中合并为一个全新的临时表

select...group by ：select从当前临时表抓取的数据应该能够代表当前临时表中所有数据行共有特征。一般来说此时select抓取group by使用分组字段

having

一、执行优先级

from>where>group by >having>select

二、书写规则

having命令不能独立出现在查询语句中，只能声明在group by 后面

三、 作用

having执行时每次操作group by 生成一个临时表。根据临时表统计信息决定是否将每次临时表进行销毁处理。七个查询命令，只有having执行完毕后不会生成临时表，负责销毁临时表

#查询部门20下各个职位人数在2人以上的
	select deptno,job,count(*) from emp where deptno = 20 group by job having count(*) >= 2
	
#查询部门平均工资小于1500的部门
  select job, avg(sal) from emp group by job having avg(sal) < 1500

四、where 和 having的区别

1.功能不同 -where将满足条件的数据行抓取到新临时表 -having 将不满足的临时表进行销毁

2.操作单位 -where每次执行时只能操作一行数据 -having每次执行操作一个临时表

3.聚合函数调用权 -where没有权利调用聚合函数 -having有权利调用聚合函数而且必须通过聚合函数判断是否删除临时表

4.书写规则 -where可以独立出现在查询语句中，如果与group by 同时出现，必须写在group前面 -having不能独立出现在查询语句中，必须出现在group by 后面

order by 排序命令

一、执行优先级

from > where > group by > having >select > order by

二、作用

专门针对select生成临时表中数据进行排序


#查询各个部门的平均工资
	select avg(sal) , deptno from emp group by deptno order by avg(sal) desc
# 这里面的聚合函数使用了多少次 -- > 1次  只有select 和 having 有权力调用聚合函数

# 查询部门30下平均工资在1000以上的职位信息，要求按照职位平均工资降序排列
select deptno,job,avg(sal) from emp where deptno = 30 group by job having avg(sal)> 1000 order by avg(sal) desc

三、多字段排序

# 查询各个部门销售精英上台领奖，按照部门编号升序，对于同一个部门的职员按照工资降序，
# 对于同一个部门并且工资相同的职员，按照入职先后顺序

 select * from emp order by deptno asc,sal del, hireDate asc

limit

 # 查询最高工资的人员信息
 select * from emp order by sal desc LIMIT 0,1;

单表查询总结

一、执行顺序

from > where > group by > having > select > order by > limit

二、书写规则

select:声明抓取字段名，调用函数

where:数据行定位分析

group by: 数据分组

having: 生成临时表决定是否删除临时表

order by ：排序字段1、排序字段2 limit 1，2

多表查询

表文件之间的关系

一方表：存储【拥有者】信息

多方表: 存储【被拥有者】信息

image.png

隶属关系分类

  • 一对多隶属关系
  • 一对一隶属关系
  • 多对多隶属关系

一对多：

image.png

一对一：

image.png 多对多隶属关系判断: 两张表彼此多可以作为对方的一方表.此时将两张表都设置为一方,然后设置第三个表描述两张表之间隶属关系，称为多方表

image.png

字段种类划分

  • 【主键字段】: 存储主键值。主键值进行数据行区分。主键值要求不能重复，要求不能为null。每一张表都需要存在一个主键字段
  • 【非主键字段】：描述主键值，让主键值更加生动，更加丰满
  • 【外键字段】：外键字段一种特殊非主键字段,只存在于多方表。用于建立两张表之间隶属关系

image.png

多表查询合并方案

多表查询合并方案:

将多张表数据行合并到一个临时表，然后根据业务交给六个查询命令(WHERE>GORUP BY >HAVING>SELECT>ORDER BY >LIMIT)

多表查询合并规则:

1.连接查询合并规则: 这是使用几率最高一种合并规则。要求参与合并的两张临时表之间必须存在【隶属关系】。 合并时将两张临时表数据【沿着水平方向】进行拼接.这种合并方案被开发人员简称"连接查询"

2.联合查询合并规则：不要求两张表之间存在隶属关系；要求两张临时表字段个数，字段类型，字段类型排列顺序必须保持一致。 合并时将两张临时表数据行沿着垂直方向堆砌同一个临时表,这种合并方案被开发人员简称"联合查询"。 联合查询专门应用在“行转列”

链接合并方案

image.png 联合合并方案

image.png

链接查询合并规则

连接查询命令格式

  • from 一方表 join 多方表
  • from 多方表 join 一方表

连接合并查询过程中，多方表与一方表在join两边声明位置对于查询结果没有任何影响。对于查询执行效率会有影响 一般来说建议将数据行相对较少的表放在join左边，满足“小表驱动“大表”

连接合并规则

  • 连接合并方案生成临时表【字段个数】 = 一方表.字段个数 + 多方表.字段个数
  • 连接合并方案生成临时表【字段名称】 = 原始表明.字段名；避免出现同名字段
  • 连接合并方案生成临时表【数据行数】 = 一方表总行数 * 多方表总行数
  • 连接合并方案生成临时表【数据行拼接规则】：使用一放表每一份数据行与多方表所有数据行拼接成全新的数据行
  • 连接合并方案生成的临时表中必然存在**【脏数据行】，因此在将临时表交给六个查询命令使用之前。必须将【合法数据行】过滤出来**

内连接过滤方案

一、内连接过滤方案命令格式

from 一方表 join 多方表

ON 合法数据行定位条件(关系表达式/逻辑表达式/特殊运算符---不能使用聚合函数)

二、内连接过滤工作原理

ON工作原理与where一致.ON循环遍历临时表每一行数据行。每次只能得到一行数据行。如果这行数据满足合法数据行特征就会被ON存入到全新临时表

三、如何判断数据行是否是合法数据行：

情况一：如果多方表声明【外键字段】,此时根据如下条件判断

ON当前数据行来自一方表的主键值 = 当前数据行来自多方表.外键值

情况二：如果多方表没有声明【外键字段】，此时根据显示生活中存在隶属关系判断

ON 现实生活中真实隶属关系

image.png

image.png

外连接过滤方案

外连接过滤方案：

从JOIN生成的临时表抓取合法的数据行。在外连接过滤方案中，将两张临时表划分【需要帮助临时表】和【不需要帮助的临时表】。外连接过滤方案中确保【需要帮助临时表】所有的数据行都有机会进入到【合法临表】

工作原理： 如果需要帮助的表中数据行与不需要帮助的表中可以拼接为合法数据行此时正常处理，对于需要帮助的表中某行数据如果不需要被帮助的表所有数据行都无法匹配，此时需要这行数据添加到合法数据作为一个独立数据行

image.png

image.png

image.png

image.png

子查询

#查询工资高于平均工资的职员信息
	select EMPNO,job,sal from emp where emp.SAL > (select avg(sal) from emp)
	
#查询部门20下职员姓名、工资、以及公司平均工资（方法一）
select emp.ename,emp.sal,(select avg(sal) from emp)	as '平均工资' from emp where emp.DEPTNO = 20
#查询部门20下职员姓名、工资、以及公司平均工资（方法二）
#规则： 1. 子查询帮助from时，其提供的临时表必须时手动设置别名
#       2. 子查询帮助from时，其提供的临时表中字段名称不能以函数名来命名(起别名)
select emp.ename,emp.sal,xx.avg_sal from emp join (select avg(sal) as avg_sal from emp) as xx where emp.DEPTNO=20
# where
# 1.提供帮助
# 2.规则：子查询在帮助where时，只能返回一个字段
	select * from emp
	where sal > (select avg(sal),max(sal),from emp)
# 子查询帮助where时，如果where使用关系运算符，子查询返回的这个字段下只能有一个字段
	select * from emp
	where sal > (select sal from emp where deptNo= 30)
# 子查询帮助where时，如果where使用in或者not in，子查询返回的这个字段下可以有多个值
	select * from emp where sal in (select sal from emo where deptno = 30)
# 查询部门平均工资高于公司平均工资的部门（having）
	select DEPTNO,avg(sal) from emp group by emp.DEPTNO having avg(sal) > (select avg(sal) from emp);
# 查询部门20下职员姓名，工资，公司最高工资
select emp.ename,emp.sal,(select max(sal) from emp) as '公司最高工资' from emp where emp.DEPTNO = 20

子查询分类

一、定义：子查询帮助查询命令时(select,where,having,from)，不需要得到倍帮助的查询命令任何支持

#例子：查询工资高于公司平均工资的职员信息
    seletc * from emp where sal (select avg(sal) from emp)

二、特点:

  • 独立子查询都是在被帮助的命令执行之前执行的
  • 独立子查询只会执行一次
  • 独立子查询不会影响被帮助命令执行效率
avatar
文章
阅读
粉丝