首页 专题 文章 代码 归档
Mysql 连接查询
2020.02.28 17:20 2020.03.02 14:20

1. 连接查询

连接查询也称多表查询,当涉及到多个表时,需要进行连接查询;

特点:

1、多表等值连接的结果为多表的交集部分;

2、n表连接、至少需要n-1个条件;

3、多表的顺序没有要求;

4、一般需要为表取别名;

5、可以搭配前面所有子句使用,如:排序、分组、筛选;


连接查询有两个版本,SQL92和SQL99,下面先讲SQL92版本;

1.1. 教程SQL

本次需要以下数据库:

-- ----------------------------
-- Table structure for beauty
-- ----------------------------
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `boyfriend_id` int(10) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for boys
-- ----------------------------
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `userCP` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Insert1:

INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (1, '柳岩', '女', 8);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (2, '苍老师', '女', 9);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (3, 'Angelababy', '女', 3);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (4, '热巴', '女', 2);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (5, '周冬雨', '女', 9);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (6, '周芷若', '女', 1);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (7, '岳林珊', '女', 9);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (8, '小昭', '女', 1);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (9, '双儿', '女', 9);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (10, '王语嫣', '女', 4);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (11, '夏雪', '女', 9);
INSERT INTO `girls`.`beauty`(`id`, `name`, `sex`, `boyfriend_id`) VALUES (12, '赵敏', '女', 1);

insert2:

INSERT INTO `girls`.`boys`(`id`, `boyName`, `userCP`) VALUES (1, '张无忌', 100);
INSERT INTO `girls`.`boys`(`id`, `boyName`, `userCP`) VALUES (2, '鹿晗', 800);
INSERT INTO `girls`.`boys`(`id`, `boyName`, `userCP`) VALUES (3, '黄晓明', 50);
INSERT INTO `girls`.`boys`(`id`, `boyName`, `userCP`) VALUES (4, '段誉', 300);

1.2. 笛卡尔乘积现象

原因:没有有效的连接条件;

避免:添加有效的连接条件;

2. SQL92语法

2.1. 等值连接

1、查询各自男女朋友名

select name, boyName
from boys,
     beauty
where beauty.boyfriend_id = boys.id;

结果:

Angelababy  黄晓明
热巴  鹿晗
周芷若 张无忌
小昭  张无忌
王语嫣 段誉
赵敏  张无忌

2、查询员工名和对应的部门名

select last_name,department_name

from employees,departments

where employees.departments.department_id = employees.employees.department_id;

3、查询员工名、工种号、工种名(起别名

select last_name, j.job_id, job_title
from employees e,
     jobs j
where e.job_id = j.job_id

3、查询有奖金的员工名和部门名(有筛选条件

select last_name, j.job_id, job_title, e.commission_pct
from employees e,
     jobs j
where e.job_id = j.job_id
  and e.commission_pct is not null;

4、查询每个城市的部门个数(有分组

select count(*) c, city
from employees e,
     locations l
group by city;

5、查询每个工种的名称和员工的个数,并按员工个数降序:

select job_title, count(*)

from employees e,
     jobs j
where e.job_id = j.job_id
group by job_title
order by count(*) desc

2.2. 非等值连接

非等值连接就是使用除了=以外的条件表达式;

2.3. 自连接

相当于等值连接,但是是:自己连接自己;

这一般需要某张表里有特殊的字段;

1、查询员工名和上级的名称:

select e.employee_id, e.last_name, m.employee_id, m.last_name
from employees e,
     employees m
where e.manager_id = m.employee_id;

3. SQL99语法

语法:

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
[where ...]
[group by ...]
[having ...]
[order by ...]

上面的【连接类型】有:

  • 内连接:inner
  • 外连接
    • 左外:left [outer]
    • 右外:right [outer]
    • 全外:full [outer]
  • 交叉连接:cross

3.1. 内连接

内连接理解:指连接结果仅包含符合连接条件的行,参与连接的两个表都应该符合连接条件。

语法:

select 查询列表
from 表 别名
inner join 表2 别名
on 连接条件;

这又分为等值、非等值、自连接;

1、查询员工名、部门名

select last_name, department_name
from employees e
         inner join departments d on e.department_id = d.department_id;

2、查询名字中含e的员工名和工种名(条件筛选)

select last_name, job_title
from employees e
         inner join jobs j on e.job_id = j.job_id
where e.last_name like '%e%';

3、查询部门个数>3的城市名和部门个数(添加分组和筛选)

select city, count(*) c
from departments d
         inner join locations l on d.location_id = l.location_id
group by city
having c > 3;

4、查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(排序)

select department_name, count(*) c
from departments d
         inner join employees e on d.department_id = e.department_id
group by department_name
having c > 3
order by c desc;

5、查询员工名、部门名、工种名,并按部门名降序(2表以上连接);

select last_name, department_name, job_title
from employees e
         inner join departments d on e.department_id = d.department_id
         inner join jobs j on e.job_id = j.job_id
order by department_name desc;

自连接

1、查询员工的名字、上级的名字

select e.last_name, m.last_name
from employees e
         join employees m
where e.manager_id = m.employee_id;

3.2. 外连接

应用场景:查询一个表中有的,而另一个表中没有的;

理解:

连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

左外连接:左边表数据行全部保留,右边表保留符合连接条件的行。

右外连接:右边表数据行全部保留,左边表保留符合连接条件的行。

全外连接:左外连接 union 右外连接。

特点:

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

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

2、主从关系

  • 左外连接,left join左边的是主表
  • 右外连接,right join右边的是主表

3、左外和右外交换两表的位置,是同样的效果;


1、查询没有男朋友的女神名

分析:此要求中,查询的结果是女神名,这来自beauty表,所以这时主表

select b.name, bo.*
from beauty b
         left outer join boys bo
                         on b.boyfriend_id = bo.id;

上面语句查询结果是:

截图-1582880734

这证明:外连接查询结果是主表的所有记录;

如果要去掉空余,再加条件:

select b.name, bo.*
from beauty b
         left outer join boys bo
                         on b.boyfriend_id = bo.id
where bo.id is not null;

如果用右外连接:

select b.name, bo.*
from boys bo
         right outer join beauty b
                          on b.boyfriend_id = bo.id
where bo.id is not null;

2、查询哪个部门没有员工

use employees;
select d.*, e.employee_id
from departments d
         left outer join employees e on d.department_id = e.department_id
where e.employee_id is null;

3.3. 全外连接

MySQL是不支持全外连接的,但是可以使用UNION来模拟;

全外连接:左表和右表都不做限制,所有的记录都显示,两表不足的地方用null 填充;

也就是:

左外连接=左表全部记录+相关联结果

右外连接=右表全部记录+相关联结果

全外连接=左表全部记录+右表全部记录+相关联结果=左外连接+右外连接-相关联结果(即去重复)

那么在MYSQL中谁可以做到呢?UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

3.4. 交叉连接

这就是笛卡尔乘积:

select b.*,bo.*
from beauty b
cross join boys bo;
本节阅读完毕! (分享
二维码图片 扫描关注我们哟