MySQL的增删改查（CRUD）、左右连接查询及子查询

sugar-T

已于 2024-03-12 09:01:52 修改

阅读量272

点赞数 5

文章标签：后端 mysql

于 2024-03-12 08:52:02 首次发布

本文链接：https://blog.csdn.net/qq_43536788/article/details/136640941

版权

MySQL的增删改查（CRUD）操作是数据库管理的基础。下面是这些操作的详细语法：

1. 查询（Read）

SELECT 语句

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT 后面跟要查询的列名。
FROM 后面跟要查询的表名。
WHERE 是可选的，用于过滤结果。

示例

SELECT id, name FROM users WHERE age > 25;

2. 插入（Create）

INSERT INTO 语句

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO 后面跟要插入数据的表名。
括号内是列名，VALUES 后面是对应的数据值。

示例

INSERT INTO users (name, age, email)
VALUES ('John Doe', 30, 'john@example.com');

3. 更新（Update）

UPDATE 语句

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE 后面跟要更新的表名。
SET 用于指定要更新的列和新的值。
WHERE 是必需的，用于指定哪些行需要更新。

示例

UPDATE users
SET age = 31
WHERE name = 'John Doe';

4. 删除（Delete）

DELETE 语句

DELETE FROM table_name WHERE condition;

DELETE FROM 后面跟要删除的表名。
WHERE 是必需的，用于指定哪些行需要删除。

示例

DELETE FROM users WHERE id = 1;

注意：使用 DELETE 语句时要特别小心，因为它会永久删除数据。在执行删除操作之前，最好先备份数据或确认条件正确。

这些是最基本的CRUD操作，MySQL还支持更复杂的查询和操作，如连接多个表、子查询、聚合函数等。为了充分利用MySQL的功能，建议深入学习其官方文档和相关教程。
MySQL 的子查询（Subquery）是嵌套在其他 SQL 查询中的查询。子查询可以出现在 SELECT、FROM 或 WHERE 子句中，并且可以用于返回数据供外部查询使用，或在 WHERE 子句中作为条件来过滤结果。子查询使得 SQL 查询更加灵活和强大。

在MySQL中，当我们谈论“左右查询”时，我们实际上是指的“左连接”（LEFT JOIN）和“右连接”（RIGHT JOIN）。这两种连接类型是SQL中用于结合两个或多个表中行的关系型操作。尽管在实际应用中，左连接比右连接更为常见，但理解两者的概念和区别是很重要的。

5. 左连接（LEFT JOIN）

左连接返回左表（即JOIN关键字左边的表）中的所有行，即使在右表（JOIN关键字右边的表）中没有匹配的行也是如此。如果右表中没有匹配的行，则结果集中对应的列将包含NULL。

语法：

SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.matching_column = right_table.matching_column;

示例：
假设我们有两个表，一个是employees（员工表），另一个是departments（部门表）。我们想要列出所有员工以及他们所在的部门（如果有的话）。

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

在这个查询中，即使某些员工没有分配到任何部门（即department_id在departments表中没有匹配项），他们的名字仍然会出现在结果集中，而department_name列将包含NULL。

6. 右连接（RIGHT JOIN）

右连接返回右表中的所有行，即使在左表中没有匹配的行也是如此。如果左表中没有匹配的行，则结果集中对应的列将包含NULL。尽管右连接在SQL中是有效的，但由于其可读性和一致性问题，它通常不如左连接常用。在大多数情况下，你可以通过调换表的位置和使用左连接来达到相同的效果。

语法：

SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.matching_column = right_table.matching_column;

示例：
使用与左连接相同的employees和departments表，如果我们想要列出所有部门以及每个部门中的员工（如果有的话），我们可以使用右连接。但是，更常见的做法是将表的位置调换并使用左连接。

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

或者，通过调换表的位置并使用左连接：

SELECT employees.name, departments.department_name
FROM departments
LEFT JOIN employees
ON employees.department_id = departments.id;

这两种查询将产生相同的结果集，但使用左连接通常更直观。

在实际应用中，根据查询的具体需求和所处理的数据，你可以选择使用左连接或右连接。然而，由于左连接更为常用和直观，建议优先使用左连接，并在必要时通过调整表的位置来达到期望的结果。

以下是一些常见的子查询类型及其示例：

7. 子查询在 SELECT 子句中

示例： 从 employees 表中选择每个员工的名字和他们的经理的名字（假设 managers 表包含经理的信息）。

SELECT employee_name, 
       (SELECT manager_name FROM managers WHERE managers.manager_id = employees.manager_id) AS manager_name
FROM employees;

8. 子查询在 FROM 子句中（内联视图或派生表）

示例： 计算每个部门的员工数量，并返回部门名称和员工数量。

SELECT department_name, COUNT(*) as employee_count
FROM (
    SELECT employee_id, department_id
    FROM employees
) AS employee_departments
JOIN departments ON employee_departments.department_id = departments.department_id
GROUP BY department_name;

9. 子查询在 WHERE 子句中

a. 比较子查询

示例： 选择工资高于平均工资的员工。

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

b. EXISTS 子查询

示例： 选择有订单的客户。

SELECT customer_name
FROM customers
WHERE EXISTS (
    SELECT 1 FROM orders
    WHERE orders.customer_id = customers.customer_id
);

c. IN 子查询

示例： 选择在特定部门工作的员工。

SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

d. ANY/ALL 子查询

示例： 选择工资高于任何经理的员工。

SELECT *
FROM employees
WHERE salary > ANY (SELECT salary FROM managers);

10. 相关子查询（Correlated Subqueries）

相关子查询是引用外部查询列的子查询。每次外部查询处理一行时，子查询都会重新计算。

示例： 对于每个员工，选择其工资是否高于其所在部门的平均工资。

SELECT employee_name, salary, 
       (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) as avg_salary_in_dept
FROM employees e1;

11. 嵌套子查询

子查询也可以嵌套在其他子查询中，形成多层嵌套。

示例： 选择工资高于其所在部门中工资高于平均工资的员工。

SELECT employee_name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id 
    AND e2.salary > (
        SELECT AVG(salary) 
        FROM employees 
        WHERE department_id = e1.department_id
    )
);