mysql8 多表联合查询、pymysql

君自故乡来，应知故乡事 / 2023-07-13 / 原文

一、多表联合查询

1、数据准备

部门表和员工表

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),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

2、多表查询

子查询

一条SQL语句的执行结果当成另外一条SQL语句的执行条件

问题：查看员工jason的部门名称:

select dep_id from emp where name='jason';

select * from dep where id=200;

把上述两条SQL语句合并为一条SQL语句

select * from dep where id= (select dep_id from emp where name='jason');

连表查询

把多张实际存在的表按照表关系连成一张虚拟表(不是实际存在的表，而是临时在内存中存的)

select * from emp,dep where emp.dep_id=dep.id;

联表的专业语法

inner join # 内连接，数据只取两张表中共有的数据

left join # 左连接，数据以左表为准，展示左表所有的数据，右表没有的数据使用NULL填充

right join # 又连接，数据以右表为准，展示右表所有的数据，左表没有的数据使用NULL填充

union # 连接多条SQL语句执行的结果

1. inner join

> select * from emp inner join dep where emp.dep_id = dep.id;

+----+-------+--------+-----+--------+-----+----------+
| id | name  | sex    | age | dep_id | id  | name     |
+----+-------+--------+-----+--------+-----+----------+
| 1  | jason | male   | 18  | 200    | 200 | 技术     |
| 2  | egon  | female | 48  | 201    | 201 | 人力资源 |
| 3  | kevin | male   | 18  | 201    | 201 | 人力资源 |
| 4  | nick  | male   | 28  | 202    | 202 | 销售     |
| 5  | owen  | male   | 18  | 203    | 203 | 运营     |
+----+-------+--------+-----+--------+-----+----------+

2. left join

>  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  | egon  | 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>   |
+----+-------+--------+-----+--------+--------+----------+

3. right join

> 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 | 技术     |
| 3      | kevin  | male   | 18     | 201    | 201 | 人力资源 |
| 2      | egon   | female | 48     | 201    | 201 | 人力资源 |
| 4      | nick   | male   | 28     | 202    | 202 | 销售     |
| 5      | owen   | male   | 18     | 203    | 203 | 运营     |
| <null> | <null> | <null> | <null> | <null> | 205 | 保洁     |
+--------+--------+--------+--------+--------+-----+----------+

4. union

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      | egon   | 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    | 保洁     |
+--------+--------+--------+--------+--------+--------+----------+

还可以起别名

select * from emp as e inner join dep as d on e.dep_id=d.id;

二、pymysql

1、如何操作MySQL？需要借助于第三方模块

pymysql
mysqlclient----->非常好用，一般情况下很难安装成功
mysqldb

2、安装模块

pip install pymysql;

3、实操

import pymysql

# 1 连接mysql服务端
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='Zjz5740##',
    db='data2',
    charset='utf8',
    autocommit=True
)
# 2 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3 执行sql语句
# sql = "insert into emp(name, sex, age, hire_date) values('kevin','male',18, NOW())"
sql = "select * from emp"
# 4 开始执行
affect_rows = cursor.execute(sql)
# print(affect_rows)

# conn.commit()  # 修改、添加数据需要commit

# 5 拿到具体数据
# print(cursor.fetchall())
for i in cursor.fetchall():
    print(i)

# 6 关闭游标、连接
cursor.close()
conn.close()

⚠️：

pymysql.cursors.DictCursor是PyMySQL库提供的一个游标（cursor）类。

它用于创建一个游标对象，该对象以字典形式返回查询结果，而不是默认的元组形式。