MySQL（四）----基础三

于 2020-10-29 19:18:11 发布
阅读量324 收藏 2
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/mischievous_boy/article/details/109367020

6.连接查询

笛卡尔积：表1m行 ， 表2n行 结果是 m*n行

发生的原因：没有有效的连接条件

连接分类：

  1. 按年代

    1. sql92:支持内连接

    2. sql99：内连接+外连接（只支持左右连接）+交叉连接

  2. 按功能

    1. 内连接

      • 等值连接

      • 非等值连接

      • 自连接

    2. 外连接

      • 左外连接

      • 右外连接

      • 全外连接

    3. 交叉连接

6.1.sql92(内连接)

6.1.1.等值连接

现进行笛卡尔积 ， 然后保留beauty.id = boys.id 的记录

 

  1. 如果表名过长， 可以给表名起别名 但是要注意的是from table是最先执行的 ， 那么就是，在起完别名之后，之后执行的语句不能使用原表名

  2. 多个表交换不会影响等值连接的结果

  3. 可以添加筛选条件 ， 分组 ， 排序等

  4. 多表等值连接

    • 多表等值连接的结果为交集的部分

    • n个表，至少有n-1个条件

    • 对表的顺序没有要求

-- 等值连接
-- 案例
-- 1. 查询女神名和对应的男神名
SELECT beauty.id ,  `name` , boyName
FROM  boys,beauty
WHERE beauty.boyfriend_id = boys.id;
​
-- 查询员工名对应的部门名
SELECT last_name , department_name
FROM employees , departments
WHERE employees.department_id = departments.department_id;
​
-- 如果表名过长， 可以给表名起别名 ， 如上面的一个
-- 但是要注意的是from table是最先执行的 ， 那么就是，在起完别名之后，不能使用原表名
-- 这里是错误的 , employees.last_name应该换成e.last_name , employees.department_id = departments.department_id换成e.department_id = d.department_id
SELECT employees.last_name , department_name
FROM employees AS e , departments AS d
WHERE  employees.department_id = departments.department_id;
​
-- 正确方式
SELECT e.last_name , department_name
FROM employees AS e , departments AS d
WHERE  e.department_id = d.department_id;
​
​
​
-- 添加筛选
--  查询有奖金的员工名和部门名
SELECT last_name , department_name
FROM employees  e, departments d
WHERE e.department_id = d.department_id AND commission_pct IS NOT NULL;
​
-- 查询城市中第二个字符为o的部门名和城市名
SELECT department_id , city
FROM departments d , locations l
WHERE d.location_id = l.location_id AND city LIKE '_o%';
​
​
-- 添加分组
-- 查询每个城市的部门个数
SELECT count(*) , city 
FROM departments, locations
WHERE departments.`location_id` = locations.`location_id`
GROUP BY city;
​
-- 查询有奖金的每个部门的部门名和部门的领导编号 和该部门的最低工资
SELECT d.department_name , d.manager_id , MIN(salary)
FROM departments d , employees e
WHERE d.`department_id` = e.`department_id` AND commission_pct IS NOT NULL
GROUP BY d.department_id;
​
-- 添加排序
-- 查询每个工种的工种名和员工的个数，并且按照员工的个数降序
SELECT job_title , COUNT(*)
FROM  employees , jobs
WHERE jobs.`job_id` = employees.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
​
-- 可以实现多表连接
-- 查询员工名 ， 部门名和所在的城市
​
SELECT last_name , department_name , city
FROM employees  e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id

 

6.1.2.非等值连接

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

案例：

-- 案例1：查询员工的工资和工
-- 因为工资等级是一个范围控制的 ， 所以查询的时候是判断salary所在的范围，然后才能确定等级
SELECT salary AS 工资 , grade_level
​
FROM employees , job_grades
​
WHERE salary <=  highest_sal AND salary >= lowest_sal;
​

6.1.3.自连接

连接的表是同一张表，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

 

从下面的案例中可以看出，能够做自连接的是要有条件的，就是存在两个拥有包含关系的两个字段

案例：

-- 查询员工名和上级的名称
-- 分析：首先要知道员工的名称和老板的上级的id，然后根据上级的id去找上级的名字，如果是上级的id和name存在另一张表中的话，那么查询就只要连接两个表了。
-- 但是，问题是上级的id ， 包含在员工id中的，毕竟上级也是员工。所以要在这个表中查两遍。所以用到自连接，就是当上级id和员工id通向的时候，就确定了上级的名称
SELECT e1.last_name , e1.manager_id, e2.last_name , e2.employee_id
FROM employees e1 , employees e2
WHERE e1.manager_id = e2.employee_id

6.1.4.测试

-- 显示员工的最大工资，平均工资
SELECT MAX(salary) 最大工资  , AVG(salary) 平均工资
FROM employees;
-- 查询员工表的employee_id , job_id , last_name , 按照department_id降序， salary升序
SELECT employee_id , job_id , last_name 
FROM employees
ORDER BY department_id DESC , salary;
--  查询员工的job_id中包含a和e的 ， 并且a在e的前面
SELECT DISTINCT job_id
FROM employees
WHERE job_id LIKE "%a%e%";
-- 显示所有的员工的姓名 ， 部门号和部门名称
SELECT  last_name , e.department_id , department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
-- 查询90号部门员工的job_id 和90号部门的location_id
SELECT job_id , location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id AND e.department_id = 90;
​
-- 选择所有有奖金的员工的last_name， department_name , location_id , city
SELECT last_name , department_name , l.location_id , city
FROM employees e , departments d , locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND
salary IS NOT NULL;
-- 选择city再Toronto工作的员工的last_name， job_id， department_id ， department_name
SELECT last_name , job_id , l.location_id , department_name
FROM employees e , departments d , locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND
city = 'Toronto';
-- 查询每个工种，每个部门的部门名 ， 工种名和最低工资
SELECT 
FROM employees e , departments d
WHERE e.department_id = d.department_id
GROUP BY job_id , e.department_id;
-- 查询每个国家下的部门个数大于2的国家编号
-- 查询每个国家下的部门个数大于2的国家编号
SELECT country_id , COUNT(*)
FROM departments d  , locations l
WHERE  d.location_id = l.location_id
GROUP BY country_id
HAVING COUNT(*) > 2;
-- 选择指定员工的姓名，员工号，以及他的管理者的姓名，员工号，结果类似于下面的公式
-- employees         Emp--       manager Mgr-- 
-- kobe  0001        king       100
​
SELECT CONCAT(e1.last_name,"    ",e1.employee_id) employees , e2.last_name  "Emp-- " , e1.manager_id "Mgr-- "
FROM employees e1 , employees e2
WHERE e1.manager_id = e2.employee_id and e1.last_name = "Ernst"

6.2.sql99

  1. 语法：

    select 查询列表

    from table1 别名 【连接类型】

    join table2 别名

    on 连接条件

    【where 筛选条件】

    【group by 分组】

    【having 筛选条件】

    【order by 排序列表】

  2. 分类

    1. 内连接：inner

    2. 外连接:

      • 左外：left【outer】

      • 右外：right【outer】

      • 全外：full【outer】

    3. 交叉连接：cross

6.2.1.内连接

  1. 语法

    select 查询列表

    from table1 别名 inner

    join table2 别名

    on 连接条件

    【where 筛选条件】

    【group by 分组】

    【having 筛选条件】

    【order by 排序列表】

  2. 分类

    1. 等值连接

    2. 非等值连接

    3. 自连接

  3. 特点

    1. 可以跟where , group by , order by , having等

    2. inner可以省略

    3. 筛选条件放在where之后，连接条件使用on ， 提高分离性 ， 方便阅读

    4. inner join连接和sql92语法中的等值连接效果一样，都是查询多表的交集

  4. 等值连接

    -- 等值连接
​
-- 查询部门名 ， 员工名
​
-- 查询名字中包含e的员工名和工种名
​
-- 查询部门个数>3的城市名和部门个数
​
-- 查询哪个部门的部门员工个数>3的部门名和员工个数，并按照个数降序
​
-- 查询员工名，部门名 ，工种名，并按照部门名降序

  5. 非等值连接

    -- 查询员工的工资级别
​
-- 查询工资级别个数>2的个数 ， 并按照工资级别降序

     

  6. 自连结

    -- 查询员工的名字和上级的名字
​
-- 查询姓名中包含字符k的员工的名字 ， 上级的名字

     

6.2.2.外连接

  1. 定义

    在内连接查询中，返回的结果只包含符合查询条件和连接条件的数据，

    然而有时还需要包含没有关联的数据，即返回结果不仅包含符合条件的数据，

    而且还包含左表(左连接，左外连接)或右表(右连接或右外连接)或两个表(全外连接)中的所有数据，此时就要使用外连接查询。

  2. 特点

    1. 外连接的查询结果为主表中所有的记录

      如果从表中有和他匹配的 ，则显示从表的值

      如果从表中没有和他匹配的 ， 则显示null值

      外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录

    2. left jion 则左边的是主表 ， right join 则右边的是主表

    3. 左外连接和右外连接互换 ， 可以实现同样的效果

    4. 全外连接 = 内连接的结果 + 表1中有但表二中没有的结果 + 表二中有但表一中没有的结果

  3. 示例

  1. 案例

    -- 查询男朋友  不在男神表的女神名 ， boys表中的信息
SELECT name , b.*
FROM boys b
RIGHT JOIN beauty 
ON b.id = beauty.boyfriend_id;

    从下图看，当没有男朋友的 id , boyName , userCP都会置为null

    但是匹配到的就会真正的显示，

    但是我们需要的是没有男朋友的 ， 那么匹配出来的id的是null

    所以最终的应该是

    -- 查询男朋友  不在男神表的女神名
SELECT name , b.*
FROM boys b
RIGHT JOIN beauty 
ON b.id = beauty.boyfriend_id
WHERE b.id IS  NULL;
​
-- 用left join代替right join
SELECT name , b.*
FROM  beauty 
left JOIN boys b
ON b.id = beauty.boyfriend_id
WHERE b.id IS NULL;

    如果没有id is not null的条件 。 我们可以清晰地看出，到底谁么有男朋友 和 到底谁有男朋友

     

     

    -- 查询哪个部门没有员工s
SELECT d.department_name
FROM departments d
LEFT JOIN employees e
on d.department_id = e.department_id
WHERE e.employee_id IS NULL;

     

6.2.3.交叉连接

就是笛卡尔积

 

6.3.sql92和sql99的比较

  1. sql99的种类更加丰富

  2. 实现了连接类型和筛选条件的分离

 

 

 

 

6.4.案例

-- 查询标号>3的女神的男朋友的信息 ， 如果有则详细列出 ， 如果没有，就用null填充
​
​
-- 查询哪个城市没有部门
​
​
-- 查询部门为SAL或IT的员工的信息

7.子查询（嵌套查询）

  1. 含义

    出现再其他语句中的select语句 ， 被称为子查询或内查询

  2. 分类

    出现的位置：

    • select后面

      支持标量子查询（一个值）

    • from后面

      支持表子查询（一个表）

    • where或having后面

      支持标量子查询（一个值）

      列子查询（一个字段的所有值 ，可以判定某个字段的范围）

      很少使用行子查询

    • exists后面（相关子查询）

      支持表子查询

    结果集进行分类

    • 标量子查询（结果集只能有一行一列）

    • 列子查询（结果集只有一列多行）

    • 行子查询（结果集有一行多列）

    • 表子查询（多行多列)

 

7.1.where和having的后面

特点：

  1. 子查询放在小括号内

  2. 子查询 一般放在条件的右侧

  3. 标量子查询：配合单行操作符使用

    < ,> >= , <= ,<>

  4. 列子查询 , 一般配合着多行操作符使用

    in ,any/some ,all ,

  5. 子查询的优先级大于父查询

     

7.2.案例

​


-- 案例1.谁的工资比Abel高
-- 先选出来Abel的工资
--  SELECT salary 
--  FROM employees
--  WHERE last_name = "Abel";
-- 再所有的人中选
    SELECT last_name , salary
    FROM employees
    WHERE salary > (
​
    SELECT salary 
    FROM employees
    WHERE last_name = "Abel"
    )
​
-- 返回job_id和141号相同,aslary比143号员工多的员工姓名 , job_id,和工资
-- 返回job_id和141号相同,aslary比143号员工多的员工姓名 , job_id,和工资
-- job_id和141相同    
    SELECT job_id
    FROM employees
    WHERE employee_id = 141;
    -- 143的salary
    SELECT salary 
    FROM employees 
    WHERE employee_id = 143;
    -- 结合上面的两个
    SELECT job_id , salary
    FROM employees
    WHERE job_id = (
        SELECT job_id
        FROM employees
        WHERE employee_id = 141
    ) AND salary > (
        SELECT salary 
        FROM employees 
        WHERE employee_id = 143
    );
-- 查询最低工资大于50号部门的最低工资的部门id和其最低工资
SELECT MIN(salary) , department_id
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees e 
    WHERE department_id = 50
)
​
​
​
-- 列子查询
-- 查询location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees  e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN locations l
ON d.location_id = l.location_id
WHERE l.location_id in (1400 , 1700);
-- 返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的：工号，姓名，job_id， 以及salary
SELECT employee_id , last_name , job_id ,salary
FROM employees
WHERE salary < ANY(
    SELECT salary
    FROM employees
    WHERE job_id = "IT_PROG"
);
-- 返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的：工号，姓名，job_id， 以及salary
SELECT employee_id , last_name , job_id ,salary
FROM employees
WHERE salary <  ALL(
    SELECT salary
    FROM employees
    WHERE job_id = "IT_PROG"
);

7.3.select之后的

案例：

-- 案例1.查询每个部门的员工个数

7.4.from之后

 

注意：

  1. 因为from之后是充当表名，所以必须要有别名

  2. 把这个子查询充当临时的表，要注意，需要包括和下面的表有相同的字段，不然不能做表的连接

案例：

​
-- 查询每个部门的平均工资的工资等级
​
    SELECT AVG(salary) , department_id
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id;
​
    SELECT av.* ,  grade_level
    FROM (
        SELECT AVG(salary) avg_salary , department_id
        FROM employees
        WHERE department_id IS NOT NULL
        GROUP BY department_id
    ) av
    left JOIN job_grades j
    ON av.avg_salary BETWEEN j.lowest_sal AND j.highest_sal;

7.5.exists后面

用法：

exists(完整的查询语句)

返回的是1或者是0 ， 当完整的查询语句里面为空的话是显示0 ， 不为空显示1

 

7.6.测试

-- 查询和Zlotkey相同部门的员工姓名和工资
​
SELECT last_name , salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = "Zlotkey"
);
​
--  查询工资比公司平局工资高的员工的员工号，姓名和工资
​
SELECT  department_id , last_name , salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);
​
--  查询各部门中比本部门平均工资高的员工的员工号，姓名和工资
​
​
SELECT employee_id , last_name , salary , dv.av_salary
FROM (
    SELECT department_id , AVG(salary) av_salary
    FROM employees
    GROUP BY department_id
) dv 
INNER JOIN employees e
ON dv.department_id = e.department_id
WHERE e.salary > dv.av_salary;
​
--  
--  查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
-- 1.找到包含u的人的所在的部门 ，查找这些部门做所有的人，除了含u的
SELECT employee_id , last_name 
FROM employees 
WHERE department_id in (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
) AND last_name NOT LIKE '%u%';
​
​
--  查询在部门的location_id为1700的部门工作的员工的员工号
SELECT e.employee_id
FROM employees e
INNER JOIN departments d  ON e.department_id = d.department_id
INNER JOIN locations  l    ON  d.location_id = l.location_id
WHERE l.location_id = 1700;
--  
--  查询管理者是K_ing的员工姓名和工资
SELECT last_name , salary , manager_id
FROM employees
WHERE manager_id  IN (
    SELECT employee_id
    FROM employees 
    WHERE last_name = "K_ing"
)
--  
--  查询工工资最高的员工的姓名 ， 要求first_name和last_name显示为一列，列名为姓.名
--  SELECT MAX(salary)
--  FROM employees 
​
SELECT CONCAT(last_name,'.' , last_name) '姓.名'
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees 
)
​
​
​
​
--子查询经典案例
​
-- 查询工资最低的员工信息：last_name , salary
    SELECT last_name , salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees
    )
​
-- 查询平均工资最低的部门信息
#第一种方案
     -- 4.根据department_id求出部门信息
    SELECT *
    FROM departments
    WHERE department_id = (
        -- 3.以部门分组求平均工资 ，留下平均工资和最低工资相同的department_id
        SELECT department_id
        FROM employees
        GROUP BY department_id
        HAVING AVG(salary) = (
        -- 2.获取最低工资
            SELECT MIN(avg_salary) mav_salary
                FROM (
                    --  1.获取平均工资        
                    SELECT AVG(salary) avg_salary , department_id
                    FROM employees
                    WHERE department_id is not null
                    GROUP BY department_id
                    ) ad
        )
    )
#第二种方案
#像这种最大值，最小值的结果，都是可以通过进行升序或者是降序，然后选出第一个就可以了，所以在获取最低工资的时候，也是可以获取到department_id的
    SELECT *
    FROM departments
    WHERE department_id = (
            -- 平均工资最低的department_id
            SELECT department_id
            FROM employees
            GROUP BY department_id
            ORDER BY AVG(salary)
            LIMIT 0 ,1
    )
​
--查询平均工资最低的部门信息和该部门的平均工资
   SELECT * 
    FROM departments d
        INNER JOIN (
        -- 平均工资最低的department_id
            SELECT department_id ,AVG(salary) av_salary
            FROM employees
            GROUP BY department_id
            ORDER BY AVG(salary)
            LIMIT 0 ,1
        ) adv
        ON adv.department_id = d.department_id
-- 查询平均工资最高的job信息
​
-- 查询平均工资高于公司平均工资的部门有哪些
​
-- 查询出公司中所有manager的详细信息
​
-- 各个部门中最高工资中最低的那个部门的最低工资是多少
​
-- 查询平均工资最高的部门的mananger的详细信息：last_name， department_id , email , salary
​

8.分页查询

  1. 语法

    SELECT 查询列表 ------------------------------------------------- 7

    FROM TALBE1----------------------------------------------------1

    [JOIN TYPE JOIN TABLE2 ---------------------------------------2

    ON 连接条件----------------------------------------------------3

    WHERE 筛选条件--------------------------------------------------4

    GROUP BY 分组列表----------------------------------------------5

    HAVING 分组后的筛选-------------------------------------------6

    ORDER BY 排序字段]---------------------------------------------8

    LIMIT offset , size;-------------------------------------------------9

    offset代表起始索引（从0开始） ， size代表长度

  2. 应用场景

    当请求的数据，一页显示不全的时候

  3. 特点：

    1. limit放在最后

    2. 要显示的页数公式

      page代表页数

      则offset = (page - 1) * size

  4. 案例

    -- 查询前五条员工信息
​
-- 查询第11---第25条
​
-- 有奖金的员工信息，并且较高的前十名显示出来

     

9.联合查询

union 联合 合并：将多条查询语句的结果合成一个结果

使用方法：

select  字段1 ， 字段二 from  table1
union
select  字段1 ， 字段二 from table2
union
。。。。。。。。

 

使用场景

查询多张表，且查询的内容具有共性 ， 别切多张表可以没有关系

 

特点：

  1. 总是以刚开始的一张表属性作为字段

  2. 列数必须相同

  3. 如果列数相同，但是内容和相对应的字段没有关系 ， 虽然不报错，但是没有意义

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值