Mysql分组，多表，连接查询

Mysql分组

1. 数据处理函数

• 什么是数据处理函数

  • 也被称之为单行处理函数
  • 单行处理函数就是一行数据对应一个输出，也可以理解操作一行就返回一行，操作10行就返回10行

• 单行处理函数

  • lower()；转换小写

    -- 转换小写：lower()传入的是某一个字段名
    
    

  select name , LOWER(name) from user

  - upper();转换大写
  
  

  – 转换大写： upper()

  - substr()；截取字符串
  
  

  – substr：截取字符串，3个参数：1需要截取的字段 ， 2截取的开始下标(下标从1开始) 3一共要截取多少长度

• concat()；拼接字符串，拼接字段

    -- concat()：可以拼接字段
    -- 将id和name拼接在一起
    
  
  

• ceil():向上取整，floor():向下取整，round()=>直接使用四舍五入，第二个参数可以决定保留位数

• – abs():取绝对值

2. 分组函数

• 什么是分组函数
  • 也叫做多行处理函数
  • 多行处理函数就是对多行进行处理，返回一行，也就是说不管操作多少行，只返回一行
  • 对于分组函数来说必须是在分组的情况下进行使用，如果没有分组，则整个表是一组
• 分组函数
  • max()；求最大值
  • min();求最小值
• avg();求平均值
  • sum();求和
• count();求总条数

3. 分组查询(重点)

• 什么是分组查询

  • 按照某一个字段进行分组，每一组(相同字段值为一组)返回对应的结果

  • sql使用group by对表的某一字段进行分组

  • having可以对分组之后的数据进行过滤

• DQL的关键字的执行顺序

  • select

    • 需要查询的字段的数据

  • from

    • 从那张表里进行查询

  • where

    • 对表中获取数据的时候进行过滤

  • group by

    • 对表中的数据进行分组

  • having

    • 在分组之后，对数据进一步筛选（对组里面的数据进行筛选）

  • order by

    • 对查询出来的数据进行排序

  • sql的执行顺序

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

4. 92多表连接查询

• 什么是连接查询

  • 当我们需要的数据不仅限一张表时，需要从两张或者多张表获取数据，那么则需要多表进行联合查询，比如我们需要知道每个员工的工作位置，则需要将员工表和部门表进行连接查询。
  • 连接查询分为两种方式
    • 92多表查询
    • 99多表查询

• 92多表查询的语法

  • select 字段名… from 表名1 ， 表名2

     -- 92多表查询
     -- 多表查询：当我们需要的数据在两张或者以上的表时，我们需要量多张表关联在一起进行查询 
    

  – 92多表查询的语法：select 字段… from 表1 , 表2
  – 关联查询的总条数 = 表1的条数*表2的条数（笛卡尔积现象）

  - 注意的点
  
  - 当两张表进行连接查询时，在没有任何条件限制的情况下，最终查询出的结果是表1的条数*表2的条数，也就是笛卡尔积现象,在工作中，我们尽量减少连接表的数量,尽量不要使用多表连接查询
  - 正常连接下可以通过加入筛选条件获取正确的数据，也就是对两张表的连接后的表进行条件筛选，加入where条件也不会减少连接次数
  - 当两张表出现相同字段时，可以通过给表取别名的方式区分。我们可以将查询的字段也加上表的别名，也可以增加查询效率
  
  - 92的缺点：将表的连接条件放在where后面，和筛选条件放在一起，结构不清晰。
  
  

5. 99多表连接查询

• 可以根据表的连接方式分为

  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接

• 内连接

  • 语法格式

    • select 字段名… from 表1 join 表2 on 连接条件 where 筛选条件

  • 当连接条件相等时，叫做等值连接

     -- 99多表查询
     -- 内连接 select 字段名.. from 表1 join 表2 on 连接条件 where 筛选条件
     
    -- 等值连接：连接条件为相等 
    

  • 当连接条件不相等时，叫做非等值连接

    -- 不等值连接：连接条件不是相等的
    

  • 自连接，也就是当我们需要查询的两个字段在同一张表时，可以将一张表当作具有不同意思的两张表去处理

     -- 自连接：自己和自己连接，把一张表当作两张不同意义的表
    

• 外连接

  • 语法格式

    • select * from 表1 left(right) join 表2 on 连接条件 where 筛选条件

  • 右外连接：将join右边的表中所有的数据都显示出来，也就是右边主表

    • select * from 表1 right join 表2 on 连接条件 where 筛选条件

  • 左外连接：将join左边的表中所有的数据都显示出来，也就是左边是主表

    • select * from 表1 left join 表2 on 连接条件 where 筛选条件

     -- 外连接：将一个表当作主表不管其连接的另一个表有没有数据和他对应，他都回现在全部自己的内容
    

• 三表查询

  • 语法格式

    • select 字段名… from A join B on A和B的连接条件 left join C on A和C的连接条件

    • 内连接和外连接可以混合使用

       -- 三表查询：多张表进行查询
      

• 子查询

  • 子查询就是在select中嵌套select

  • 可以在where，from后面进行使用

  • where子句后面的子查询：将查询出的数据当作条件

  • from中的子查询：将查询出来的数据当作一张表（伪表查询）

• union连接：将两次查询的结果集相加，本身具有去重的效果，如果不需要可以使用union all(不去重)

对于select有不太了解的可以查看我之前的博客

点击查看

对于这一部分博客也有相对应的练习，大家可以尝试尝试，然后在另外一篇博客中会有答案以及具体的解析

6. 具体案例

/*部门表*/
CREATE TABLE dept(
    deptnu      INT  PRIMARY KEY comment '部门编号',
    dname       VARCHAR(50) comment '部门名称',
    addr        VARCHAR(50) comment '部门地址'
);

/*员工表*/
CREATE TABLE emp(
    empno       INT  PRIMARY KEY comment '雇员编号',
    ename       VARCHAR(50) comment '雇员姓名',
    job         VARCHAR(50) comment '雇员职位',
    mgr         INT comment '雇员上级编号',
    hiredate    DATE comment '雇佣日期',
    sal         DECIMAL(7,2) comment '薪资',
    deptnu      INT comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*工资等级表*/
CREATE TABLE sal(
    grade       INT  PRIMARY KEY comment '等级',
    lowsal      INT comment '最低薪资',
    higsal      INT comment '最高薪资'
);



/*插入dept表数据*/
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');


/*插入emp表数据*/
INSERT INTO emp VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000,  10);
INSERT INTO emp VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO emp VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO emp VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO emp VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO emp VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO emp VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO emp VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO emp VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500,  30);
INSERT INTO emp VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO emp VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO emp VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);

/*插入salgrade表数据*/
INSERT INTO sal VALUES (1, 7000, 12000);
INSERT INTO sal VALUES (2, 12010, 14000);
INSERT INTO sal VALUES (3, 14010, 20000);
INSERT INTO sal VALUES (4, 20010, 30000);
INSERT INTO sal VALUES (5, 30010, 99990);

-- 1查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数

-- 2列出薪资比安琪拉高的所有员工

-- 3列出所有员工的姓名及其直接上级的姓名。

-- 4列出入职日期早于直接上级的所有员工的编号、姓名、部门编号

-- 5列出所有文员的姓名及其部门名称，所在部门的总人数

-- 6列出最低薪资大于15000的各种工作及从事此工作的员工人数

-- 7列出与诸葛亮从事相同工作的所有员工信息。

-- 8列出薪资比在30部门工作的员工的平均薪资还高的员工姓名和薪资、部门编号。

-- 9查询出所有员工的姓名，部门名称以及薪资等级

-- 10查询所有员工的年薪，并按年薪从高到低排序

点击下面链接查看具体解析

例题解析