品牌手机网站开发哪家好,h5怎么制作下一页,多用户 开源oa 系统,深圳世展建设公司一、SQL中的表连接方式#xff1a;
内连接#xff08;INNER JOIN#xff09;#xff1a;返回两个表中符合连接条件的交集。外连接#xff08;OUTER JOIN#xff09;#xff1a; 左外连接#xff08;LEFT JOIN#xff09;#xff1a;返回左表中所有记录#xff0c;以…一、SQL中的表连接方式
内连接INNER JOIN返回两个表中符合连接条件的交集。外连接OUTER JOIN 左外连接LEFT JOIN返回左表中所有记录以及右表中符合连接条件的记录。右外连接RIGHT JOIN返回右表中所有记录以及左表中符合连接条件的记录。完全外连接FULL JOIN返回左表和右表中的所有记录如果没有匹配的记录则用NULL填充。交叉连接CROSS JOIN返回两个表的笛卡尔积即左表中的每一行都与右表中的每一行组合。自连接SELF JOIN将表与自身进行连接常用于比较表中不同行之间的数据。
这些连接方式允许在不同的条件下将多个表关联起来以满足查询的需求。 二、创建两个表
employees员工信息和departments部门信息:
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_idmanager_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 | Finance2.左外连接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 | Finance3.右外连接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 | Finance4.交叉连接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;这将返回每个员工及其对应的经理的姓名。
