MySQL多表查询

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)

子查询分类：

子查询类别	子查询结果	支持查询字段数量
标量子查询	单个值	一个字段
列子查询	一列	一个字段
行子查询	一行	多个字段
表子查询	多行多列	多个字段

子查询位置：

1. WHERE 之后
2. FROM 之后
3. 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])