MySQL 连接query

1. 连接queryoverview

连接query is MySQLin用于 from many 个表in获取data important techniques. through连接query, 可以将 many 个表in 相关data关联起来, 形成一个完整 data集.

MySQLsupport many 种class型 连接query:

• in 连接 (INNER JOIN) : 只返回两个表in匹配 行
• left 连接 (LEFT JOIN) : 返回 left 表in 所 has 行, 以及 right 表in匹配 行
• right 连接 (RIGHT JOIN) : 返回 right 表in 所 has 行, 以及 left 表in匹配 行
• 全连接 (FULL JOIN) : 返回两个表in 所 has 行, 无论 is 否匹配
• 交叉连接 (CROSS JOIN) : 返回两个表 笛卡尔积
• 自连接 (SELF JOIN) : 表 and 自身for连接

1.1 exampledata准备

-- creationexampledatalibrary
CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

-- creation部门表
CREATE TABLE IF NOT EXISTS departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

-- creation员工表
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 插入部门data
INSERT INTO departments (department_name, location) VALUES
('techniques部', '北京'),
('销售部', ' on 海'),
('人力resource部', '广州'),
('财务部', '深圳');

-- 插入员工data
INSERT INTO employees (name, department_id, salary, hire_date) VALUES
('张三', 1, 9000.00, '2023-01-15'),
('李四', 1, 10000.00, '2023-02-20'),
('王五', 2, 8000.00, '2023-03-10'),
('赵六', 2, 8500.00, '2023-04-05'),
('钱七', 3, 7000.00, '2023-05-18'),
('孙八', NULL, 6000.00, '2023-06-22'); -- 没 has 部门 员工

2. in 连接 (INNER JOIN)

in 连接 is 最常用 连接class型, 只返回两个表in匹配 行. such as果两个表in没 has 匹配 行, 则不会返回任何data.

2.1 basic语法

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

2.2 in 连接example

-- query员工及其所属部门information
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- usingWHERE子句  in 连接 ( old 语法) 
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e, departments d
WHERE e.department_id = d.id;

-- 添加额 out 条件  in 连接
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary > 8000;

-- 按部门group, 计算每个部门 平均工资
SELECT d.department_name, COUNT(*) AS employee_count, AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
ORDER BY avg_salary DESC;

3. left 连接 (LEFT JOIN)

left 连接返回 left 表in 所 has 行, 以及 right 表in匹配 行. such as果 right 表in没 has 匹配 行, 则返回NULL值.

3.1 basic语法

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

3.2 left 连接example

-- query所 has 员工及其所属部门information, including没 has 部门 员工
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- query没 has 部门 员工
SELECT e.id, e.name, e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

--  left 连接带条件
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.location = '北京'
ORDER BY e.id;

4. right 连接 (RIGHT JOIN)

right 连接返回 right 表in 所 has 行, 以及 left 表in匹配 行. such as果 left 表in没 has 匹配 行, 则返回NULL值.

4.1 basic语法

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

4.2 right 连接example

-- query所 has 部门及其员工information, including没 has 员工 部门
SELECT d.id, d.department_name, d.location, e.name, e.salary
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- query没 has 员工 部门
SELECT d.id, d.department_name, d.location
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

--  right 连接带条件
SELECT d.id, d.department_name, d.location, e.name, e.salary
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id AND e.salary > 7000
ORDER BY d.id;

5. 全连接 (FULL JOIN)

全连接返回两个表in 所 has 行, 无论 is 否匹配. such as果没 has 匹配 行, 则返回NULL值. MySQL不直接supportFULL JOIN, 但可以throughUNIONoperation符implementation.

5.1 implementation全连接

-- usingLEFT JOIN and RIGHT JOIN结合UNIONimplementation全连接
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.id, e.name, e.salary, d.department_name, d.location
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL; -- 避免重复

6. 交叉连接 (CROSS JOIN)

交叉连接返回两个表 笛卡尔积, 即 left 表in 每一行 and right 表in 每一行都组合一次.

6.1 basic语法

SELECT columns
FROM table1
CROSS JOIN table2;

6.2 交叉连接example

-- 交叉连接example (生成所 has 员工 and 部门 组合) 
SELECT e.name AS employee_name, d.department_name
FROM employees e
CROSS JOIN departments d;

-- using逗号分隔 交叉连接 ( old 语法) 
SELECT e.name AS employee_name, d.department_name
FROM employees e, departments d;

7. 自连接 (SELF JOIN)

自连接 is 表 and 自身for连接, 通常用于processing层次structure or 比较表in 行.

7.1 自连接example

-- creation员工表 (package含经理ID) 
CREATE TABLE IF NOT EXISTS employees_with_manager (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    manager_id INT,
    salary DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (manager_id) REFERENCES employees_with_manager(id)
);

-- 插入data
INSERT INTO employees_with_manager (name, manager_id, salary) VALUES
('CEO', NULL, 20000.00),
('techniques总监', 1, 15000.00),
('销售总监', 1, 14000.00),
('advanced工程师', 2, 10000.00),
('工程师', 2, 8000.00),
('销售经理', 3, 9000.00),
('销售人员', 6, 7000.00);

-- query员工及其经理information
SELECT e.id AS employee_id, e.name AS employee_name, e.salary AS employee_salary,
       m.id AS manager_id, m.name AS manager_name, m.salary AS manager_salary
FROM employees_with_manager e
LEFT JOIN employees_with_manager m ON e.manager_id = m.id;

-- query工资 high 于经理 员工
SELECT e.name AS employee_name, e.salary AS employee_salary,
       m.name AS manager_name, m.salary AS manager_salary
FROM employees_with_manager e
INNER JOIN employees_with_manager m ON e.manager_id = m.id
WHERE e.salary > m.salary;

8. many 表连接

MySQLsupport many 个表 连接, 可以through链式连接implementation.

8.1 many 表连接example

-- creationproject表
CREATE TABLE IF NOT EXISTS projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE
);

-- creation员工project关联表
CREATE TABLE IF NOT EXISTS employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50) NOT NULL,
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

-- 插入projectdata
INSERT INTO projects (project_name, start_date, end_date) VALUES
('网站Development', '2023-01-01', '2023-06-30'),
('moveapplicationDevelopment', '2023-02-01', '2023-07-31'),
('dataanalysis', '2023-03-01', '2023-08-31');

-- 插入员工project关联data
INSERT INTO employee_projects (employee_id, project_id, role) VALUES
(1, 1, 'project经理'),
(1, 2, 'techniques顾问'),
(2, 1, 'advancedDevelopment'),
(2, 2, 'techniques负责人'),
(3, 3, '销售负责人'),
(4, 3, '销售人员');

-- 三表连接query
SELECT e.id, e.name, d.department_name, p.project_name, ep.role
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN employee_projects ep ON e.id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.id
ORDER BY e.id, p.id;

--  left 连接 many 表
SELECT e.id, e.name, d.department_name, p.project_name, ep.role
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN employee_projects ep ON e.id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.id
ORDER BY e.id, p.id;

9. 连接queryoptimization

连接query可能会导致performanceissues, 特别 is in processing big 型表时. 以 under is 一些optimization连接query techniques:

9.1 optimizationtechniques

• usingindex: 确保连接列 on has index, 这可以显著improving连接query performance
• 选择合适 连接class型: 根据practicalrequirements选择合适 连接class型, 避免不必要 全表扫描
• 限制结果集: usingWHERE子句filterdata, reducing需要连接 行数
• 避免SELECT *: 只选择需要 列, reducingdata传输量
• using子queryoptimization: for 于 complex query, 可以考虑using子query分解 complex 逻辑
• analysis执行计划: usingEXPLAIN语句analysisquery执行计划, 找出performance瓶颈

9.2 indexoptimizationexample

--  for 连接列creationindex
CREATE INDEX idx_employee_department ON employees(department_id);

-- analysisquery执行计划
EXPLAIN SELECT e.id, e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- optimization many 表连接
EXPLAIN SELECT e.id, e.name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN employee_projects ep ON e.id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.id
WHERE e.salary > 8000;

10. 完整example

-- creation完整 exampledatalibrary
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

-- creation学生表
CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender ENUM('male', 'female') NOT NULL,
    age INT NOT NULL,
    grade VARCHAR(10) NOT NULL
);

-- creation课程表
CREATE TABLE IF NOT EXISTS courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credits INT NOT NULL
);

-- creation成绩表
CREATE TABLE IF NOT EXISTS scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score DECIMAL(5, 2) NOT NULL,
    exam_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    UNIQUE (student_id, course_id)
);

-- 插入学生data
INSERT INTO students (student_id, name, gender, age, grade) VALUES
('S2023001', '张三', 'male', 18, ' high 一'),
('S2023002', '李四', 'female', 17, ' high 一'),
('S2023003', '王五', 'male', 18, ' high 二'),
('S2023004', '赵六', 'female', 17, ' high 二'),
('S2023005', '钱七', 'male', 18, ' high 三');

-- 插入课程data
INSERT INTO courses (course_code, course_name, credits) VALUES
('C001', '数学', 4),
('C002', '语文', 4),
('C003', '英语', 4),
('C004', '物理', 3),
('C005', '化学', 3);

-- 插入成绩data
INSERT INTO scores (student_id, course_id, score, exam_date) VALUES
(1, 1, 95.5, '2023-06-15'),
(1, 2, 88.0, '2023-06-16'),
(1, 3, 92.5, '2023-06-17'),
(2, 1, 85.0, '2023-06-15'),
(2, 2, 90.0, '2023-06-16'),
(2, 3, 87.5, '2023-06-17'),
(3, 1, 90.0, '2023-06-15'),
(3, 4, 85.5, '2023-06-18'),
(3, 5, 82.0, '2023-06-19'),
(4, 1, 88.5, '2023-06-15'),
(4, 4, 92.0, '2023-06-18'),
(4, 5, 89.5, '2023-06-19');

-- 1. query所 has 学生 所 has 课程成绩
SELECT s.student_id, s.name, s.gender, s.grade, c.course_name, sc.score, sc.exam_date
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.id
ORDER BY s.id, c.id;

-- 2. query每个学生 平均成绩
SELECT s.student_id, s.name, s.grade, AVG(sc.score) AS avg_score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.student_id, s.name, s.grade
ORDER BY avg_score DESC;

-- 3. query每门课程 平均成绩
SELECT c.course_code, c.course_name, c.credits, AVG(sc.score) AS avg_score
FROM courses c
LEFT JOIN scores sc ON c.id = sc.course_id
GROUP BY c.id, c.course_code, c.course_name, c.credits
ORDER BY avg_score DESC;

-- 4. query成绩 high 于90分 学生 and 课程
SELECT s.student_id, s.name, c.course_name, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
WHERE sc.score > 90
ORDER BY sc.score DESC;

-- 5. query没 has 成绩记录 学生
SELECT s.student_id, s.name, s.grade
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
WHERE sc.id IS NULL;

-- 6. query没 has 学生选修 课程
SELECT c.course_code, c.course_name, c.credits
FROM courses c
LEFT JOIN scores sc ON c.id = sc.course_id
WHERE sc.id IS NULL;

-- 7. query每个年级 平均成绩
SELECT s.grade, c.course_name, AVG(sc.score) AS avg_score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
GROUP BY s.grade, c.course_name
ORDER BY s.grade, avg_score DESC;