07、多表查询

多表查询

一、介绍

多表连接查询之连表查询
符合条件连接查询
多表连接查询之子查询

准备表

# 建表
create table dep(
    id int primary key auto_increment,
    name varchar(20)
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

# 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('tony','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

二、多表连接查询之连表查询

1、语法

select 字段列表
    from 表1 inner|left|right join 表2
    on 表1.字段 = 表2.字段;

2、交叉连接：不适用任何匹配条件。生成笛卡尔积

mysql> select * from dep,emp;
+-----+--------------+----+-------+--------+------+--------+
| id  | name         | id | name  | sex    | age  | dep_id |
+-----+--------------+----+-------+--------+------+--------+
| 200 | 技术         |  1 | jason | male   |   18 |    200 |
| 201 | 人力资源     |  1 | jason | male   |   18 |    200 |
| 202 | 销售         |  1 | jason | male   |   18 |    200 |
| 203 | 运营         |  1 | jason | male   |   18 |    200 |
| 200 | 技术         |  2 | tony  | female |   48 |    201 |
| 201 | 人力资源     |  2 | tony  | female |   48 |    201 |
| 202 | 销售         |  2 | tony  | female |   48 |    201 |
| 203 | 运营         |  2 | tony  | female |   48 |    201 |
| 200 | 技术         |  3 | kevin | male   |   18 |    201 |
| 201 | 人力资源     |  3 | kevin | male   |   18 |    201 |
| 202 | 销售         |  3 | kevin | male   |   18 |    201 |
| 203 | 运营         |  3 | kevin | male   |   18 |    201 |
| 200 | 技术         |  4 | nick  | male   |   28 |    202 |
| 201 | 人力资源     |  4 | nick  | male   |   28 |    202 |
| 202 | 销售         |  4 | nick  | male   |   28 |    202 |
| 203 | 运营         |  4 | nick  | male   |   28 |    202 |
| 200 | 技术         |  5 | owen  | male   |   18 |    203 |
| 201 | 人力资源     |  5 | owen  | male   |   18 |    203 |
| 202 | 销售         |  5 | owen  | male   |   18 |    203 |
| 203 | 运营         |  5 | owen  | male   |   18 |    203 |
| 200 | 技术         |  6 | jerry | female |   18 |    204 |
| 201 | 人力资源     |  6 | jerry | female |   18 |    204 |
| 202 | 销售         |  6 | jerry | female |   18 |    204 |
| 203 | 运营         |  6 | jerry | female |   18 |    204 |
+-----+--------------+----+-------+--------+------+--------+


mysql> select emp.name,dep.name from emp,dep where emp.dep_id = dep.id;
+-------+--------------+
| name  | name         |
+-------+--------------+
| jason | 技术         |
| tony  | 人力资源     |
| kevin | 人力资源     |
| nick  | 销售         |
| owen  | 运营         |
+-------+--------------+

"""
涉及到多表操作的时候 为了避免表字段重复 
    需要在字段名的前面加上表名限制
"""

3、内连接：只连接匹配的行

inner join  内连接:只连接两表中都存在(有对应关系)的数据
    mysql> select * from emp inner join dep on emp.dep_id = dep.id;
    +----+-------+--------+------+--------+-----+--------------+
    | id | name  | sex    | age  | dep_id | id  | name         |
    +----+-------+--------+------+--------+-----+--------------+
    |  1 | jason | male   |   18 |    200 | 200 | 技术         |
    |  2 | tony  | female |   48 |    201 | 201 | 人力资源     |
    |  3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
    |  4 | nick  | male   |   28 |    202 | 202 | 销售         |
    |  5 | owen  | male   |   18 |    203 | 203 | 运营         |
    +----+-------+--------+------+--------+-----+--------------+
    
    # 上述sql等同于
    mysql> select * from emp,dep where emp.dep_id = dep.id;

4、左连接：优先显示左表全部记录

left join   左连接:以左表为基准展示左表所有的数据没有对应则NULL填充
    mysql> select * from emp left join dep on emp.dep_id = dep.id;
    +----+-------+--------+------+--------+------+--------------+
    | id | name  | sex    | age  | dep_id | id   | name         |
    +----+-------+--------+------+--------+------+--------------+
    |  1 | jason | male   |   18 |    200 |  200 | 技术         |
    |  2 | tony  | female |   48 |    201 |  201 | 人力资源     |
    |  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
    |  4 | nick  | male   |   28 |    202 |  202 | 销售         |
    |  5 | owen  | male   |   18 |    203 |  203 | 运营         |
    |  6 | jerry | female |   18 |    204 | NULL | NULL         |
    +----+-------+--------+------+--------+------+--------------+

5、右连接：优先显示右表全部记录

right join  右连接:以右表为基准展示右表所有的数据没有对应则NULL填充
    mysql> select * from emp right join dep on emp.dep_id = dep.id;
    +------+-------+--------+------+--------+-----+--------------+
    | id   | name  | sex    | age  | dep_id | id  | name         |
    +------+-------+--------+------+--------+-----+--------------+
    |    1 | jason | male   |   18 |    200 | 200 | 技术         |
    |    2 | tony  | female |   48 |    201 | 201 | 人力资源     |
    |    3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
    |    4 | nick  | male   |   28 |    202 | 202 | 销售         |
    |    5 | owen  | male   |   18 |    203 | 203 | 运营         |
    | NULL | NULL  | NULL   | NULL |   NULL | 205 | 安保         |
    +------+-------+--------+------+--------+-----+--------------+

6、全连接：显示左右两个表全部记录

union       全连接:展示左右两表中所有的数据没有对应则NULL填充
    mysql> select * from emp left join dep on emp.dep_id = dep.id
    union 
    select * from emp right join dep on emp.dep_id = dep.id;
    +------+-------+--------+------+--------+------+--------------+
    | id   | name  | sex    | age  | dep_id | id   | name         |
    +------+-------+--------+------+--------+------+--------------+
    |    1 | jason | male   |   18 |    200 |  200 | 技术         |
    |    2 | tony  | female |   48 |    201 |  201 | 人力资源     |
    |    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
    |    4 | nick  | male   |   28 |    202 |  202 | 销售         |
    |    5 | owen  | male   |   18 |    203 |  203 | 运营         |
    |    6 | jerry | female |   18 |    204 | NULL | NULL         |
    | NULL | NULL  | NULL   | NULL |   NULL |  205 | 安保         |
    +------+-------+--------+------+--------+------+--------------+

三、子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中，即为分步查询
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

1、带 IN 关键字的子查询

# 查询部门是技术或者人力资源的员工信息
	1.先查询技术和人力资源的部门编号
    	select id from dep where name in ('技术','人力资源');
    2.根据部门编号去员工表中筛选出对应的员工数据
    	select * from emp where dep_id in (200,201);
    '''子查询:将SQL语句括号括起来即可充当查询条件'''
    mysql> select * from emp 
    where dep_id in 
    (select id from dep where name in ('技术','人力资源'));
    
# 查询平均年龄在25岁以上的部门名
	mysql> select name from dep
	where id in 
	(select dep_id from emp group by dep_id having avg(age)>25);
    +--------------+
    | name         |
    +--------------+
    | 人力资源     |
    | 销售         |
    +--------------+
    
# 查看技术部员工姓名
	mysql> select name from emp
	where dep_id in
	(select id from dep where name = '技术');
    +-------+
    | name  |
    +-------+
    | jason |
    +-------+

# 查看不足1人的部门名(子查询得到的是有人的部门id)
	mysql> select name from dep
	where id not in
	(select distinct dep_id from emp);
    +--------+
    | name   |
    +--------+
    | 安保   |
    +--------+
    
    """
    not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理
    """

2、带ANY关键字的子查询

# 在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。
# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义
例如：使用 IN 和使用 ANY运算符得到的结果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);

select * from employee where salary in (
select max(salary) from employee group by depart_id);
# 结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符

# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的

SELECT * FROM T_Book
WHERE FYearPublished < ANY (2001, 2003, 2005)

3、带ALL关键字的子查询

# all同any类似，只不过all表示的是所有，any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee 
where salary > all (
select avg(salary) from employee group by depart_id);

查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee 
where salary < all (
select avg(salary) from employee group by depart_id);

查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any ( select avg(salary) from employee group by depart_id);

4、带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select * from emp where age > (select avg(age) from emp);
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  2 | tony | female |   48 |    201 |
|  4 | nick | male   |   28 |    202 |
+----+------+--------+------+--------+

#查询大于部门内平均年龄的员工名、年龄
mysql> select t1.name,t1.age from emp t1
    -> inner join
    -> (select dep_id,avg(age) avg_age from emp group by dep_id) t2
    -> on t1.dep_id = t2.dep_id
    -> where t1.age > t2.avg_age;
    +------+------+
    | name | age  |
    +------+------+
    | tony |   48 |
    +------+------+

5、带EXISTS关键字的子查询

# EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
# 而是返回一个真假值。True或False
# 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
mysql> select * from emp
    -> where exists
    -> (select id from dep where id = 2000);
Empty set (0.00 sec)

mysql> select * from emp
    -> where exists
    -> (select id from dep where id = 200);
    +----+-------+--------+------+--------+
    | id | name  | sex    | age  | dep_id |
    +----+-------+--------+------+--------+
    |  1 | jason | male   |   18 |    200 |
    |  2 | tony  | female |   48 |    201 |
    |  3 | kevin | male   |   18 |    201 |
    |  4 | nick  | male   |   28 |    202 |
    |  5 | owen  | male   |   18 |    203 |
    |  6 | jerry | female |   18 |    204 |
    +----+-------+--------+------+--------+

四、符合条件连接查询

#示例1：以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
mysql> select emp.name,dep.name from emp inner join dep on emp.dep_id = dep.id where emp.age>25;
+------+--------------+
| name | name         |
+------+--------------+
| tony | 人力资源     |
| nick | 销售         |
+------+--------------+

#示例2：以内连接的方式查询emp和dep表，并且以age字段的升序方式显示
mysql> select * from emp inner join dep on emp.dep_id = dep.id order by age asc;
+----+-------+--------+------+--------+-----+--------------+
| id | name  | sex    | age  | dep_id | id  | name         |
+----+-------+--------+------+--------+-----+--------------+
|  1 | jason | male   |   18 |    200 | 200 | 技术         |
|  3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
|  5 | owen  | male   |   18 |    203 | 203 | 运营         |
|  4 | nick  | male   |   28 |    202 | 202 | 销售         |
|  2 | tony  | female |   48 |    201 | 201 | 人力资源     |
+----+-------+--------+------+--------+-----+--------------+

五、可视化软件

为了提高开发的效率 但是不能太过于依赖该软件
其实这些软件的底层还是执行的SQL语句来操作数据库的

该软件是收费的 但是有很多破解版本 自我百度下载即可(正式版只能使用14天)

1.链接数据库
2.创建数据库
3.创建表
4.创建数据
5.创建外键
6.逆向数据库到模型
7.转储SQL文件

posted @ 2022-05-12 14:13 vonmo 阅读(46) 评论(0) 编辑收藏举报

刷新页面返回顶部

momo先生

07、多表查询

多表查询

一、介绍

二、多表连接查询之连表查询

1、语法

2、交叉连接：不适用任何匹配条件。生成笛卡尔积

3、内连接：只连接匹配的行

4、左连接：优先显示左表全部记录

5、右连接：优先显示右表全部记录

6、全连接：显示左右两个表全部记录

三、子查询

1、带 IN 关键字的子查询

2、带ANY关键字的子查询

3、带ALL关键字的子查询

4、带比较运算符的子查询

5、带EXISTS关键字的子查询

四、符合条件连接查询

五、可视化软件

公告