赞
踩
这些连接方式允许在不同的条件下将多个表关联起来,以满足查询的需求。
- CREATE TABLE departments (
- department_id INT PRIMARY KEY,
- department_name VARCHAR(50)
- );
-
- CREATE TABLE employees (
- employee_id INT PRIMARY KEY,
- employee_name VARCHAR(50),
- department_id INT,
- manager_id INT,
- FOREIGN KEY (department_id) REFERENCES departments(department_id),
- FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
- );
departments
表有两个列:department_id
和department_name
,其中department_id
是主键。
employees
表有四个列:employee_id
、employee_name
、department_id
和manager_id
,其中employee_id
是主键,department_id
是外键关联到departments
表的department_id
,manager_id
是外键关联到employees
表的employee_id
,表示员工的直接上级。
这样就创建了两个简单的表,可以用于存储员工和部门的信息。
插入数据:
- -- 向 departments 表插入数据
- INSERT INTO departments (department_id, department_name) VALUES
- (1, 'IT'),
- (2, 'HR'),
- (3, 'Finance'),
- (4, 'Marketing'),
- (5, 'Sales');
-
- -- 向 employees 表插入数据
- INSERT INTO employees (employee_id, employee_name, department_id, manager_id) VALUES
- (1, 'John Doe', 1, NULL),
- (2, 'Jane Smith', 2, 1),
- (3, 'David Lee', 1, 1),
- (4, 'Emily Brown', 3, 1),
- (5, 'Michael Johnson', 4, 3),
- (6, 'Lisa Wang', 1, 3),
- (7, 'Tom Davis', 5, 4),
- (8, 'Sarah White', 5, 4),
- (9, 'Kevin Martin', 2, 1),
- (10, 'Mary Wilson', 3, 1);
1.内连接(INNER JOIN):
- SELECT e.employee_name, e.employee_id, d.department_name
- FROM employees e
- INNER JOIN departments d ON e.department_id = d.department_id;
结果:
- employee_name | employee_id | department_name
- -----------------------------------------------
- John Doe | 1 | IT
- Jane Smith | 2 | HR
- David Lee | 3 | IT
- Emily Brown | 4 | Finance
- Michael Johnson | 5 | Marketing
- Lisa Wang | 6 | IT
- Tom Davis | 7 | Sales
- Sarah White | 8 | Sales
- Kevin Martin | 9 | HR
- Mary Wilson | 10 | Finance
2.左外连接(LEFT JOIN):
- SELECT e.employee_name, e.employee_id, d.department_name
- FROM employees e
- LEFT JOIN departments d ON e.department_id = d.department_id;
结果:
- employee_name | employee_id | department_name
- -----------------------------------------------
- John Doe | 1 | IT
- Jane Smith | 2 | HR
- David Lee | 3 | IT
- Emily Brown | 4 | Finance
- Michael Johnson | 5 | Marketing
- Lisa Wang | 6 | IT
- Tom Davis | 7 | Sales
- Sarah White | 8 | Sales
- Kevin Martin | 9 | HR
- Mary Wilson | 10 | Finance
3.右外连接(RIGHT JOIN):
- SELECT e.employee_name, e.employee_id, d.department_name
- FROM employees e
- RIGHT JOIN departments d ON e.department_id = d.department_id;
结果:
- employee_name | employee_id | department_name
- -----------------------------------------------
- John Doe | 1 | IT
- Jane Smith | 2 | HR
- David Lee | 3 | IT
- Emily Brown | 4 | Finance
- Michael Johnson | 5 | Marketing
- Lisa Wang | 6 | IT
- Tom Davis | 7 | Sales
- Sarah White | 8 | Sales
- Kevin Martin | 9 | HR
- Mary Wilson | 10 | Finance
4.交叉连接(CROSS JOIN):
- SELECT e.employee_name, d.department_name
- FROM employees e
- CROSS JOIN departments d;
结果会返回所有可能的员工和部门组合,共50条记录(10个员工 * 5个部门)。
5.自连接(SELF JOIN):
假设我们想要查找每个员工的经理,我们可以使用自连接:
- SELECT e.employee_name, m.employee_name AS manager_name
- FROM employees e
- LEFT JOIN employees m ON e.manager_id = m.employee_id;
这将返回每个员工及其对应的经理的姓名。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。