SQL教程：从入门到精通（摘自网络）

SQL教程：从入门到精通
📖 创建数据库
CREATE DATABASE example_db;

📂 创建表

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
department_id INT
);

🗑️ 删除表
DROP TABLE employees;

🔄 添加列
ALTER TABLE employees ADD email VARCHAR(255);

🗑️ 删除列
ALTER TABLE employees DROP COLUMN email;

🔄 修改列
ALTER TABLE employees MODIFY COLUMN name VARCHAR(255);

🔄 重命名列
ALTER TABLE employees RENAME COLUMN name TO fullname;

🔄 创建索引
CREATE INDEX idx_employee_name ON employees (name);

🗑️ 删除索引
DROP INDEX idx_employee_name ON employees;

📂 创建视图

CREATE VIEW department_summary AS
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

🗑️ 删除视图
DROP VIEW department_summary;

🔄 创建主键
ALTER TABLE employees ADD PRIMARY KEY (employee_id);

🗑️ 删除主键
ALTER TABLE employees DROP PRIMARY KEY;

📊 插入数据

INSERT INTO employees (employee_id, name, age, department_id)
VALUES (1, 'John Doe', 30, 5);

🔄 更新数据

UPDATE employees
SET name = 'Jane Doe'
WHERE employee_id = 1;

🗑️ 删除数据
DELETE FROM employees WHERE employee_id = 1;

📊 查询所有数据
SELECT * FROM employees;

🔍 查询特定列
SELECT name, age FROM employees;

🔍 查询学过“叶平”老师所教的所有课的同学的学号、姓名
SELECT student_id, name FROM students WHERE teacher = '叶平';

🔍 条件查询
SELECT * FROM employees WHERE age > 30;

🔍 限制查询结果数量
SELECT * FROM employees LIMIT 10;

🔍 查询排序
SELECT * FROM employees ORDER BY age DESC;

🔍 分组统计

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

🔍 连接查询

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

🔍 左连接查询

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

🔍 使用条件函数

SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS status
FROM employees;

🔍 使用LIKE进行模糊查询
SELECT * FROM employees WHERE name LIKE 'J%';

🔍 使用BETWEEN查询范围
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

🔍 使用ISNULL检查空值
SELECT * FROM employees WHERE department_id IS NULL;

🔍 使用ISNOTNULL检查非空值
SELECT * FROM employees WHERE department_id IS NOT NULL;

🔍 授权用户权限
GRANT SELECT ON employees TO user 'john';

🔍 撤销用户权限
REVOKE SELECT ON employees FROM user 'john';

🔍 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

🔍 删除用户
DROP USER 'newuser'@'localhost';

🔍 授权特定权限
GRANT INSERT, UPDATE ON database_name.table_name TO 'user'@'localhost';

🔍 撤销特定权限
REVOKE INSERT, UPDATE ON database_name.table_name FROM 'user'@'localhost';

🔍 查看用户权限
SHOW GRANTS FOR 'user'@'localhost';

🔍 设置用户密码
SET PASSWORD FOR 'user'@'localhost' = newpassword;

🔍 更改用户的权限限制
ALTER USER 'user'@'localhost' WITH MAX QUERIES PER HOUR 100;

🔍 指定用户密码过期
ALTER USER 'user'@'localhost' PASSWORD EXPIRE;

🔍 允许用户从任何主机连接
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'%' IDENTIFIED BY 'password';

🔍 锁定和解锁用户账户

ALTER USER 'user'@'localhost' ACCOUNT LOCK;
ALTER USER 'user'@'localhost' ACCOUNT UNLOCK;

🔍 开始事务
START TRANSACTION;

🔍 提交事务
COMMIT;

🔍 回滚事务
ROLLBACK;

🔍 开启一个新事务
BEGIN; -- 或使用 START TRANSACTION;

🔍 标记保存点
SAVEPOINT savepoint_name;

🔍 回滚到保存点
ROLLBACK TO savepoint_name;

🔍 释放保存点
RELEASE SAVEPOINT savepoint_name;

🔍 提交当前事务
COMMIT;

🔍 回滚当前事务
ROLLBACK;

🔍 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 其他选项包括 READ COMMITTED, REPEATABLE READ, SERIALIZABLE

🔍 开启一个具有特定隔离级别的事务
START TRANSACTION WITH CONSISTENT SNAPSHOT READ WRITE;

🔍 在事务中查询当前的隔离级别
SELECT @@tx_isolation;

🔍 指定事务只读
START TRANSACTION READ ONLY;

🔍 使用锁定读取
SELECT * FROM employees FOR UPDATE;

🔍 使用GROUP BY与HAVING过滤

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

🔍 使用内部子查询
SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

🔍 使用窗口函数进行排名
SELECT name, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

🔍 使用递归查询
WITH RECURSIVE subordinates AS (
SELECT employee_id, name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

🔍 使用自连接查询
SELECT a.name AS EmployeeName, b.name AS ManagerName
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

🔍 使用全外连接查询
-- 插入全外连接查询的示例代码

🔍 使用UNION合并结果
SELECT name FROM employees UNION SELECT name FROM departments;

🔍 使用UNION ALL合并结果（包括重复值）
SELECT name FROM employees UNION ALL SELECT name FROM departments;

🔍 使用INTERSECT找到两个查询的交集
SELECT name FROM employees INTERSECT SELECT name FROM departments;

🔍 使用EXCEPT找到两个查询的差集
SELECT name FROM employees EXCEPT SELECT name FROM departments;

🔍 创建表时指定存储引擎（MySOL特有）
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
age INT,
department_id INT
) ENGINE=InnoDB;

🔍 使用FORCEINDEX提示优化查询
SELECT * FROM employees FORCE INDEX (idx_employee_name) WHERE name = 'John';

🔍 检查查询的执行计划
-- 插入检查执行计划的示例代码