join， left join , inner join ,right join 的用法详解

在 MySQL 中，LEFT JOIN、RIGHT JOIN和INNER JOIN是用于连接两个或多个表的操作

1. INNER JOIN 使用场景 🎯

核心思想：只要有关联的数据

典型场景：

• 查找有订单的用户及其订单详情

• 统计已下单客户的消费情况

• 获取有关联关系的完整数据

2. LEFT JOIN 使用场景 🎯

核心思想：主表数据必须完整，关联表数据可有可无

典型场景：

• 查看所有用户及其订单（包括没有订单的用户）

• 找出注册但从未下单的用户

• 生成完整的用户报表，无论是否有相关业务数据

• 统计用户转化率

3. RIGHT JOIN 使用场景 🎯

核心思想：关联表数据必须完整，主表数据可有可无

典型场景：

• 查看所有订单及其用户信息（包括用户已删除的订单）

• 找出"孤儿"订单（没有对应用户的订单）

• 数据清理时识别异常数据

• 审计和历史数据查询

4. JOIN (INNER JOIN) 使用场景 🎯

与 INNER JOIN 完全相同

典型场景：

• 代码简洁性要求高时

• 团队编码规范要求使用 JOIN

• 简单的关联查询

二、SQL语法大全

SQL 语句大全及用法详解

📊 目录结构

1. 数据库操作 (DDL)

2. 数据操作 (DML)

3. 数据查询 (DQL)

4. 数据控制 (DCL)

5. 事务控制 (TCL)

6. 高级查询技巧

7. 常用函数

---

1. 数据库操作 (DDL - Data Definition Language)

创建数据库

CREATE DATABASE database_name;
-- 示例
CREATE DATABASE company;
CREATE DATABASE IF NOT EXISTS company; -- 避免重复创建错误

删除数据库

DROP DATABASE database_name;
-- 示例
DROP DATABASE company;
DROP DATABASE IF EXISTS company; -- 安全删除

使用数据库

USE database_name;
-- 示例
USE company;

---

2. 数据表操作 (DDL)

创建表

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);
-- 示例
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    department_id INT,
    salary DECIMAL(10, 2) DEFAULT 0,
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

修改表结构

-- 添加列
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- 修改列类型
ALTER TABLE employees MODIFY COLUMN name VARCHAR(100);

-- 重命名列
ALTER TABLE employees CHANGE COLUMN name full_name VARCHAR(100);

-- 删除列
ALTER TABLE employees DROP COLUMN email;

-- 添加主键
ALTER TABLE employees ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE employees ADD FOREIGN KEY (dept_id) REFERENCES departments(id);

删除表

DROP TABLE table_name;
-- 示例
DROP TABLE employees;
DROP TABLE IF EXISTS employees; -- 安全删除

清空表数据

TRUNCATE TABLE table_name;
-- 示例
TRUNCATE TABLE employees; -- 快速清空，不能回滚

---

3. 数据操作 (DML - Data Manipulation Language)

插入数据

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

-- 示例
INSERT INTO employees (name, age, salary) 
VALUES ('张三', 25, 8000.00);

-- 插入多行
INSERT INTO employees (name, age, salary) VALUES
('李四', 30, 12000.00),
('王五', 28, 9500.00),
('赵六', 35, 15000.00);

更新数据

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

-- 示例
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = '技术部';

UPDATE employees 
SET salary = 10000, position = '经理' 
WHERE id = 1;

删除数据

DELETE FROM table_name WHERE condition;

-- 示例
DELETE FROM employees WHERE id = 5;
DELETE FROM employees WHERE salary < 5000;
DELETE FROM employees; -- 删除所有数据，谨慎使用！

---

4. 数据查询 (DQL - Data Query Language)

基础查询

SELECT column1, column2, ... FROM table_name;

-- 示例
SELECT * FROM employees; -- 所有列
SELECT name, salary FROM employees; -- 指定列
SELECT name AS 姓名, salary AS 薪资 FROM employees; -- 别名

条件查询 (WHERE)

SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE name LIKE '张%'; -- 张开头
SELECT * FROM employees WHERE department IN ('技术部', '销售部');
SELECT * FROM employees WHERE salary IS NOT NULL;

排序 (ORDER BY)

SELECT * FROM employees ORDER BY salary DESC; -- 降序
SELECT * FROM employees ORDER BY hire_date ASC; -- 升序
SELECT * FROM employees ORDER BY department ASC, salary DESC; -- 多列排序

分组统计 (GROUP BY)

SELECT department, COUNT(*) as 人数, AVG(salary) as 平均薪资
FROM employees 
GROUP BY department;

SELECT department, gender, COUNT(*) 
FROM employees 
GROUP BY department, gender; -- 多列分组

分组过滤 (HAVING)

SELECT department, AVG(salary) as avg_salary
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 10000; -- 对分组结果过滤

限制结果 (LIMIT)

SELECT * FROM employees LIMIT 10; -- 前10条
SELECT * FROM employees LIMIT 5, 10; -- 从第5条开始取10条

---

5. JOIN 查询详解

INNER JOIN

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN

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

RIGHT JOIN

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

FULL OUTER JOIN (MySQL不支持，用UNION实现)

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

多表连接

SELECT e.name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id;

---

6. 子查询 (Subqueries)

WHERE 子句中的子查询

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

SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = '北京');

FROM 子句中的子查询

SELECT dept_name, avg_salary
FROM (SELECT d.name as dept_name, AVG(e.salary) as avg_salary
      FROM departments d
      JOIN employees e ON d.id = e.department_id
      GROUP BY d.name) as dept_stats
WHERE avg_salary > 10000;

SELECT 子句中的子查询

SELECT name, salary, 
       (SELECT AVG(salary) FROM employees) as avg_salary,
       salary - (SELECT AVG(salary) FROM employees) as diff
FROM employees;

---

7. 常用函数

字符串函数

SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;
SELECT UPPER(name), LOWER(name) FROM employees;
SELECT LENGTH(name) as name_length FROM employees;
SELECT SUBSTRING(name, 1, 3) as short_name FROM employees;
SELECT TRIM('   hello   ') as trimmed; -- 去除空格

数值函数

SELECT ROUND(salary, 2) as rounded_salary FROM employees;
SELECT CEIL(salary), FLOOR(salary) FROM employees;
SELECT ABS(salary - 10000) as diff FROM employees;
SELECT RAND(); -- 随机数

日期函数

SELECT NOW(), CURDATE(), CURTIME();
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) as next_year;
SELECT DATEDIFF(CURDATE(), hire_date) as days_worked;
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date);
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') as formatted_date;

聚合函数

SELECT COUNT(*) as total_employees FROM employees;
SELECT SUM(salary) as total_salary FROM employees;
SELECT AVG(salary) as avg_salary FROM employees;
SELECT MAX(salary) as max_salary, MIN(salary) as min_salary;
SELECT department, COUNT(*) FROM employees GROUP BY department;

---

8. 事务控制 (TCL - Transaction Control Language)

事务处理

START TRANSACTION; -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- 提交事务
-- 或者
ROLLBACK; -- 回滚事务

保存点

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT point1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SAVEPOINT point2;

ROLLBACK TO point1; -- 回滚到保存点
COMMIT;

---

9. 数据控制 (DCL - Data Control Language)

用户权限管理

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- 授予权限
GRANT SELECT, INSERT ON database.table TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';

-- 撤销权限
REVOKE INSERT ON database.table FROM 'username'@'localhost';

-- 删除用户
DROP USER 'username'@'localhost';

---

10. 高级技巧

窗口函数 (Window Functions)

SELECT name, salary, department,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept,
       AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

公用表表达式 (CTE)

WITH dept_stats AS (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_stats d ON e.department = d.department
WHERE e.salary > d.avg_salary;

条件表达式

SELECT name, salary,
       CASE 
           WHEN salary < 5000 THEN '低'
           WHEN salary BETWEEN 5000 AND 10000 THEN '中'
           ELSE '高'
       END as salary_level
FROM employees;

---

🎯 实用查询示例

分页查询

SELECT * FROM employees 
ORDER BY id 
LIMIT 10 OFFSET 20; -- 第3页，每页10条

查找重复数据

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

随机抽样

SELECT * FROM employees 
ORDER BY RAND() 
LIMIT 5;

时间范围查询

SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

SELECT * FROM logs 
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR);

---

📝 最佳实践建议

1. 总是使用 WHERE 条件：避免意外更新或删除所有数据

2. 使用事务：重要的数据操作要放在事务中

3. 建立索引：在经常查询的列上建立索引提高性能

4. **避免 SELECT ***：只选择需要的列

5. 使用参数化查询：防止 SQL 注入攻击

6. 定期备份：重要数据定期备份