快速参考

这是一份 SQL 常用语句的速查表，覆盖日常开发 90% 的场景。需要时直接复制改表名和条件即可。

数据库操作：

CREATE DATABASE feixiang_db;
DROP DATABASE feixiang_db;
USE feixiang_db;
SHOW DATABASES;

表操作：

CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(50), salary DECIMAL(10,2));
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN email;
DROP TABLE employees;
TRUNCATE TABLE employees;
SHOW TABLES;
DESCRIBE employees;

索引操作：

CREATE INDEX idx_name ON employees (emp_name);
CREATE UNIQUE INDEX idx_email ON employees (email);
DROP INDEX idx_name ON employees;  -- MySQL
DROP INDEX employees.idx_name;     -- SQL Server

视图操作：

CREATE VIEW tech_employees AS
SELECT emp_id, emp_name, salary FROM employees WHERE dept_code = 2;
DROP VIEW tech_employees;

增删改查：

-- 增
INSERT INTO employees (emp_name, salary) VALUES ('航仔', 8888.88);
INSERT INTO employees VALUES (10001, '航仔', 8888.88), (10002, '翼王', 20000.00);

-- 删
DELETE FROM employees WHERE emp_id = 10001;

-- 改
UPDATE employees SET salary = 10000 WHERE emp_name = '航仔';

-- 查
SELECT * FROM employees;
SELECT emp_name, salary FROM employees WHERE dept_code = 2;
SELECT DISTINCT dept_code FROM employees;
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

别名：

SELECT emp_name AS 姓名, salary AS 工资 FROM employees;
SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_code = d.dept_id;

条件查询：

SELECT * FROM employees WHERE salary BETWEEN 8000 AND 15000;
SELECT * FROM employees WHERE dept_code IN (1, 2, 3);
SELECT * FROM employees WHERE emp_name LIKE '航%';
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM bonus_records WHERE emp_id = employees.emp_id);

CASE 表达式：

SELECT emp_name,
    CASE
        WHEN salary >= 20000 THEN '高薪'
        WHEN salary >= 10000 THEN '中薪'
        ELSE '普通'
    END AS 薪资级别
FROM employees;

聚合与分组：

SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees WHERE dept_code = 2;
SELECT dept_code, AVG(salary) FROM employees GROUP BY dept_code;
SELECT dept_code, AVG(salary) FROM employees GROUP BY dept_code HAVING AVG(salary) > 10000;

连接查询：

SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_code = d.dept_id;
SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_code = d.dept_id;

子查询：

SELECT emp_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
SELECT emp_name FROM employees WHERE dept_code IN (SELECT dept_id FROM departments WHERE dept_name = '技术部');
SELECT emp_name FROM employees WHERE EXISTS (SELECT 1 FROM bonus_records WHERE emp_id = employees.emp_id);

事务：

START TRANSACTION;
UPDATE employees SET salary = salary * 1.1 WHERE dept_code = 2;
COMMIT;
-- 或 ROLLBACK;

用户权限：

GRANT SELECT, INSERT ON feixiang_db.* TO 'aoxiang'@'localhost';
REVOKE DELETE ON feixiang_db.* FROM 'aoxiang'@'localhost';
SHOW GRANTS FOR 'aoxiang'@'localhost';

在线测试