MySQL多表查询
- 本文用python执行SQL语句。
导入python库
import json
import pymysql
from pymysql.converters import escape_string
import pandas as pd
连接数据库
#连接数据库
conn=pymysql.connect(
host="localhost",
port=3306,#端口号
user="root",#数据库用户
password="123456",#数据库密码
#database="world"#要连接的数据库名称
)
cur=conn.cursor()
跳转到指定数据库
sql = """USE PLJNB"""
cur.execute(sql)
cur.fetchall()
多表关系
一对多(多对一)
- 案例:部门于员工的关系,一个部门对应多个员工
- 实现:在多的一方建立外键,指向一的一方的主键
多对多
- 案例:学生与课程的关系,一个学生选多个课程,一个课程多个学生选
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
- 代码:
创建学生信息表:
sql = """
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(10) COMMENT '姓名',
no VARCHAR(10) COMMENT '学号'
)COMMENT '学生表';
"""
cur.execute(sql)
conn.commit()
cur.fetchall()
创建课程信息表
sql = """
CREATE TABLE course(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
name VARCHAR(10) COMMENT '课程名称'
)COMMENT '课程表';
"""
cur.execute(sql)
conn.commit()
cur.fetchall()
创建中间表
sql = """
CREATE TABLE student_course(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
studentid INT NOT NULL COMMENT '学生ID',
courseid INT NOT NULL COMMENT '课程ID',
CONSTRAINT fk_courseid FOREIGN KEY(courseid) REFERENCES course(id),
CONSTRAINT fk_studentid FOREIGN KEY(studentid) REFERENCES student(id)
)COMMENT '学生课程中间表';
"""
cur.execute(sql)
conn.commit()
cur.fetchall()
一对一
- 案例:拆分不同类信息
多表查询
全部类别的组合(全部笛卡尔积):
SELECT * FROM 表1,表2;
若要消除无效的笛卡尔积:
SELECT * FROM 表1,表2 WHERE 表1.主键 = 表2.外键;
- 例:
sql = """
SELECT * FROM user, dept WHERE user.dept_id = dept.id;
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()))
内连接
查询A表和B表交集,有隐式和显示两种写法,查询结果相同。
- 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件 ...;
例:
sql = """SELECT user.name,dept.name FROM user, dept WHERE user.dept_id = dept.id;"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()))
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
例:
sql = """SELECT user.name,dept.name FROM user INNER JOIN dept ON user.dept_id = dept.id;"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()))
外连接
- 左外连接
查询左表所有数据及两张表交集数据。
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
例:
sql = """SELECT user.name,dept.name FROM user LEFT OUTER JOIN dept ON user.dept_id = dept.id;"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()))
- 右外连接
查询右表所有数据及两张表交集数据。
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
例:
sql = """SELECT user.name,dept.name FROM user RIGHT OUTER JOIN dept ON user.dept_id = dept.id;"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()))
自连接
- 当前表与自身连接查询,必须使用表别名。
- 自连接可以用在类似并查集的场景。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
例:根据用户表的领导字段查找用户的领导。
sql = """SELECT u2.name '员工',u1.name '领导名' FROM user u1 RIGHT JOIN user u2 ON u1.id = u2.managerid;"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
联合查询
- 把多次查询的结果合并起来,形成新的查询结果集。
- 每个查询的字段需相同。
SELECT 字段列表 FROM 表A ...
UNION [ALL](不加ALL可去重)
SELECT 字段列表 FROM 表B ...;
例:分别查询年龄为21岁和状态为1用户,并将两次查询结果合并为一张表。
sql = """SELECT * FROM user WHERE age = 21
UNION
SELECT * FROM user WHERE status = 1;"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
子查询
- 嵌套SELECT语句称为嵌套查询,又称子查询。
- 外部SELECT可以是其他关键字(UPDATE INSERT DELETE)
SELECT 字段列表 FROM 表名 WHERE 字段名1 = (SELECT 字段名1 FROM 表2)
子查询分类:
子查询类别 | 子查询结果 | 支持查询字段数量 |
---|---|---|
标量子查询 | 单个值 | 一个字段 |
列子查询 | 一列 | 一个字段 |
行子查询 | 一行 | 多个字段 |
表子查询 | 多行多列 | 多个字段 |
子查询位置:
- WHERE 之后
- FROM 之后
- SELECT 之后
标量子查询
例:
sql = """SELECT * FROM user
WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部');
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
列子查询
- 操作符:
操作符关键字 | 作用 |
---|---|
IN | 等于指定集合范围内的任意一值即可(=)(连续OR) |
NOT IN | 不在指定集合内(!连续OR) |
ANY | 子查询返回列表中,只需满足其中任意一条件(可>,<,=)(连OR) |
ALL | 子查询返回列表全部值都必须满足(连AND) |
例1:查找研发部和产品部的员工。
sql = """SELECT * FROM user
WHERE dept_id
IN(SELECT id FROM dept WHERE name = '研发部' OR name = '产品部');
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
例2:查找年龄小于全部产品部员工的员工,即年龄小于产品部年龄最小员工的员工。
sql = """SELECT * FROM user WHERE age < ALL(
SELECT age FROM user
WHERE dept_id = (
SELECT id FROM dept WHERE name = '产品部'
)
);
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
例3:查找年龄大于任一研发部员工的员工,即年龄大于研发部最年轻员工的员工。
sql = """SELECT * FROM user WHERE age > ANY(
SELECT age FROM user WHERE dept_id = (
SELECT id FROM dept WHERE name = '研发部'
)
);
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
行子查询
- 操作符:=、<>、IN、NOT IN
- 即满足多个字段的条件
例:
sql = """
SELECT * FROM user WHERE
(age,managerid) = (SELECT age,managerid FROM user WHERE name = 'plj');
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
表子查询
- 操作符:IN
- 即多个字段的多个条件,满足其中之一
例:
sql = """
SELECT * FROM user WHERE
(age,dept_id)
IN(SELECT age,dept_id FROM user WHERE name = 'plj' OR name = 'pljnb');
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])
子查询+连接查询
- 子查询用于形成临时子表
- 可以通过子查询建立临时表,再利用多表连接查询实现条件查询。
例:
sql = """
SELECT * FROM
(SELECT * FROM user WHERE age > 21) elder_user
LEFT JOIN dept ON dept.id = elder_user.dept_id;
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])