MySQL基础学习2

标签（空格分隔）： MySQL

进阶五：分组查询

语法：
select 分组函数，列（要求出现在group by 的后面）
from 表名
【where 筛选条件】
group by 分组的列表
【order by 子句】

注意:
查询列表必须特殊，group by后面的字段

特点：1、分组查询中的筛选条件分为两类

|空格|数据源|位置|关键字
|-|-|-|
|分组前筛选|原始表|group by字句前|where
|分组后筛选|分组后的结果集|group by字句后|having

分组函数做条件肯定是放在having子句中
能用分组前筛选换的，优先分组前筛选
3。group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有循序要求），表达式或函数（用得较少）
4.也可以添加排序（排序放在整个分组查询的最后）

一、简单的分组查询

案例1：查询每个工种的最高工资

  SELECT MAX(`salary`) ,`job_id`FROM `employees`
  GROUP BY `job_id` ;

案例2：查询每个位置上的部门个数

  SELECT COUNT(*),`location_id`
  FROM `departments`
  GROUP BY `location_id` ;

二、添加筛选条件

案例1：查询邮箱中包含a字符的，每个部门的平均工资

  SELECT AVG(`salary`),`department_id`
  FROM `employees`
  WHERE `email` LIKE '%a%'
  GROUP BY `department_id` ;

案例2：查询有奖金的每个领导手下员工的最高工资

  SELECT MAX(`salary`),`manager_id`
  FROM `employees`
  WHERE `commission_pct` IS NOT NULL
  GROUP BY `manager_id` ;

三、添加复杂的筛选条件

案例一：查询哪个部门的员工个数>2

    1、查询每个部门的员工个数

    SELECT COUNT(*),`department_id`
FROM `employees`
    GROUP BY `department_id` ;
    
    2、根据1、的结果进行筛选(having)，查询哪个部门的员工个数>2

     SELECT COUNT(*),`department_id`
FROM `employees`
     GROUP BY `department_id`
     HAVING COUNT(*)  > 2;

案例二：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT MAX(`salary`),`job_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `job_id`
HAVING MAX(`salary`) >12000;

案例三：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，及其最低工资

SELECT MIN(`salary`),`manager_id`
FROM `employees`
WHERE `manager_id`>102
GROUP BY `manager_id`
HAVING MIN(`salary`) >5000;

按表达式或函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

1、查询每个长度的员工个数 
SELECT COUNT(*),LENGTH(`last_name`)  last_name 
FROM `employees` 
GROUP BY
LENGTH(`last_name`) ;
         
2、添加筛选条件 
SELECT COUNT(*),LENGTH(`last_name`)  las_name 
FROM`employees` 
GROUP BY LENGTH(`last_name`) 
HAVING COUNT(*) >5 ;

按多个字段分组

案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(`salary`),`department_id`,`job_id`
FROM `employees`
GROUP BY `job_id` ,`department_id`;

添加排序

案例：查询每个部门每个工种的平均工资，并按平均工资的高低排序

1、
SELECT AVG(`salary`),`department_id`,`job_id`
FROM `employees`
WHERE `department_id` IS NOT NULL
GROUP BY `department_id`,`job_id`
ORDER BY AVG(`salary`) DESC;

2、在1、的条件下输出平均工资>10000的
    SELECT AVG(`salary`),`department_id`,`job_id`
FROM `employees`
WHERE `department_id` IS NOT NULL
GROUP BY `department_id`,`job_id`
HAVING AVG(`salary`) > 10000
ORDER BY AVG(`salary`) DESC;

进阶六：连接查询

含义：多表查询，当查询的字段来自多个表时，就会用到连接查询

语法：selsct name,boyName from beauty,boy;

示例：

SELECT `name`,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id` ;

笛卡尔乘积现象：表1 有m行，表2 有n行，结果是 = m * n 行
发生原因：没有有效的连接条件
如可避免：添加有效的连接条件

分类：

  按年代分类：
    sql92标准：仅仅只支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外） +交叉连接

  按功能分类：
     内连接：
          等值链接
          非等值连接
          自连接
     外连接：
          右外连接
          左外连接
          全外连接
     交叉连接

一、SQL92标准

     1、等值连接
        1. 多表等值1连接的结果为多表的交集部分
        2.n表连接，至少需要n-1个连接条件
        3.多表的顺序没有要求
        4.一般需要为表起别名
        5.可以搭配前面介绍的所有子句使用，比如排序，分组，筛选

1、等值连接

案例1：查询女神名和对应的男神名

SELECT `name`,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id` ;

案例2：查询员工名和对应的部门名

SELECT `last_name`,`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id` = `departments`.`department_id` ;

案例3：查询每个工种、每个部门的部门名、工种名和最低工资（二度分组，先按第一个分，再在的一个分组的前提下进行第二次分组）

SELECT `department_name`,`job_title`, MIN(`salary`) AS 最低工资
FROM `departments` AS d,`employees` AS e, `jobs` AS j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY `department_name`, `job_title` ;

2、为表起别名

好处：
     提高简洁度
     区分多个重名
注意：如果为表起了别名，则我们查询的字段就不能使用原来的表名去限定

案例：查询员工名，工种号，工种名
常规写法：

SELECT `last_name`,`employees`.`job_id`,`job_title`
FROM `employees`,`jobs`
WHERE `employees`.`job_id` = `jobs`.`job_id` ;

起别名写法：

SELECT e.`last_name`,e.`job_id`,`job_title`
FROM `employees` AS e,`jobs`
WHERE e.`job_id` = `jobs`.`job_id` ;

3、两个表的循序可以调换

 SELECT e.`last_name`,e.`job_id`,`job_title`
FROM `jobs` AS j,`employees` AS e
WHERE e.`job_id` = j.`job_id` ;

4、可以加筛选

案例：查询有奖金的员工名，部门名

SELECT `last_name`,`department_name`, `commission_pct`
  FROM `employees` AS e, `departments` AS d
  WHERE e.`department_id` = d.`department_id`
  AND e.`commission_pct` IS NOT NULL ;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT `department_name`, `city`
  FROM `departments` AS d, `locations` AS l
  WHERE d.`location_id` = l.`location_id`
  AND city LIKE '_o%' ;

5、可以加分组

案例一：查询每个城市的部门个数

SELECT COUNT(*)  个数,city
  FROM `departments` AS d, `locations` AS l
  WHERE d.`location_id` = l.`location_id`
  GROUP BY city ;

案例二：查询有奖金的每个部门的部名和部门的领导编号和该部门的最低工资

SELECT `department_name`,d.`manager_id`,MIN(`salary`)
  FROM `departments` AS d, `employees` AS e
  WHERE d.`department_id` = e.`department_id`
  AND `commission_pct` IS NOT NULL
  GROUP BY `department_name`, d.`manager_id` ;

6、可以加排序

案例：查询每个工种的工种名和员工个数，并且按员工个数降序

  ORDER BY COUNT(*) DESC ;
  SELECT `job_title`, COUNT(*)
  FROM `employees` AS e, `jobs` AS j
  WHERE e.`job_id` = j.`job_id`
  GROUP BY `job_title` ;

7、可以实现三表连接

案例：查询员工名，部门名和所在的城市

   SELECT `last_name`,`department_name`,`city`
FROM `employees` AS e, `departments` AS d,`locations` AS l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND `city` LIKE '%s%'
ORDER BY department_name DESC ;

2、非等值连接

案例1：查询员工的工资和工资级别

SELECT `salary`,`grade_level`
FROM `employees` AS e, `job_grades` AS j
WHERE `salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` ;
AND j.`grade_level` = 'A' ;

3、自连接

案例：查询员工名和上级的名称

SELECT e.`last_name`,e.`employee_id`,e.`employee_id`,e.`last_name`
FROM `employees` AS e,`employees` AS m
WHERE m.`employee_id` = e.`manager_id` ;

二、SQL99标准

语法：
    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名
    on 连接条件
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排列列表】

分类：
 内连接（▼）：inner
 外连接：
    左外（▼）：left【outer】
    右外（▼）：right【outer】
    全外：full【outer】
交叉连接：cross

1、内连接

语法：
    select 查询列表
    from 表1 别名 【连接类型】
    join 表2 别名
    on inner
    【where 筛选条件】
    【group by 分组】
    【having 筛选条件】
    【order by 排列列表】
    
分类:
     等值
     非等值
     自连接
     
特点：
    1、可添加排序、分组、筛选
    2、inner可以忽略
    3、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
    4、inner join连接和SQL92语法中的等值连接效果是一样的，都是查询多表的交集

1. 等值连接

案例1：查询员工名、部门名

SELECT `last_name`,`department_name`
FROM `employees` AS e INNER
JOIN `departments` AS d
ON e.`department_id` = d.`department_id` ;

案例2：查询名字中包含e的员工名和工种名（调价筛选）

SELECT `last_name`,`job_title`
 FROM `employees` AS e
 INNER
 JOIN `jobs` AS j
 ON e.`job_id` = j.`job_id`
WHERE `last_name` LIKE '%e%' ;

案例3：查询部门个数>3的城市名和部门个数，（添加分组+筛选）

SELECT `city`, COUNT(*) AS 部门个数
FROM `departments` AS d
INNER
JOIN `locations` AS l
ON d.`location_id` = l.`location_id`
GROUP BY `city`
HAVING COUNT(*) > 3;

案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序排列（添加排序）

SELECT COUNT(*),`department_name`
FROM `employees` AS e
INNER
JOIN `departments` AS d
ON e.`department_id` = d.`department_id`
GROUP BY `department_name`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

案例5:查询员工名、部门名、工种名，并按部门名降序（三表连接）

SELECT `last_name`,`department_name`,`job_title`
FROM `employees` AS l
INNER 
JOIN `departments` AS d ON l.`department_id` = d.`department_id`
INNER
JOIN `jobs` AS j ON j.`job_id` = l.`job_id`
ORDER BY `department_name` DESC ;

2、非等值连接

案例：查询员工的工资级别

SELECT salary,`grade_level`
FROM `job_grades` AS j
INNER
JOIN `employees` AS e
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` ;

案例：查询员工的工资级别的个数>20的个数，并按工资级别降序

SELECT salary,`grade_level`,COUNT(*)
FROM `job_grades` AS j
INNER
JOIN `employees` AS e
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` 
GROUP BY `grade_level`
HAVING COUNT(*)>20
ORDER BY COUNT(*) DESC ;

3、自连接

案例：查询姓名中包含k的员工的名字、上级的名字

SELECT e.`last_name`,m.`last_name`
FROM `employees` AS e
INNER
JOIN `employees` AS m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%' ;

4、外连接

应用场景：用于查询一个表中有，另一个表中没有的记录

特点： 
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接，left join左边的是主表
   右外连接，right join右边的是主表
3、左外和右外交换两个表的循序，可以实现同样的效果
4、全外连接 = 内连接的结果+表1中有单表2中没有的+表2中有但表1中没有的

引入：查询没有男朋友的女神名

SELECT b.name,bo.*
FROM `beauty` AS b
LEFT OUTER
JOIN boys AS bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL ;

案例;查询哪个部门没有员工

左外
SELECT d.*,e.`employee_id`
FROM `departments` AS d
LEFT OUTER
JOIN `employees` AS e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;

右外
SELECT d.*,e.`employee_id`
FROM `employees`AS e
RIGHT OUTER
JOIN `departments` AS d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;

案例：查询部门名为SAl或IT的员工信息

SELECT e.*,d.`department_name`
FROM `departments` AS d
LEFT OUTER
JOIN `employees` AS e
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` IN('SAL','IT') ;

in常用于where表达式中，其作用是查询某个范围内的数据。
用法：select * from where field in (value1,value2,value3,…)

5、全外连接（MySQL不支持）

全外连接 = 内连接的结果+表1中有单表2中没有的+表2中有但表1中没有的

SELECT b,*,bo.*
FROM `beauty` AS b
FULL OUTER
JOIN `boys` AS bo
ON b.`boyfriend_id` = bo.id ;

交叉连接

交叉连接的结果是一个笛卡尔乘积

SELECT b.*,bo.*
FROM `beauty` AS b
CROSS JOIN boy

posted @ 2024-08-08 11:23 我的十四行诗在哪里阅读(4) 评论(0) 编辑收藏举报

刷新页面返回顶部

fengmian13wl

MySQL基础学习2

进阶五：分组查询

一、简单的分组查询

二、添加筛选条件

三、添加复杂的筛选条件

按表达式或函数分组

按多个字段分组

添加排序

进阶六：连接查询

一、SQL92标准

1、等值连接

2、为表起别名

3、两个表的循序可以调换

4、可以加筛选

5、可以加分组

6、可以加排序

7、可以实现三表连接

2、非等值连接

3、自连接

二、SQL99标准

1、内连接

1. 等值连接

2、非等值连接

3、自连接

4、外连接

5、全外连接（MySQL不支持）

交叉连接

公告