【SQL】SQL 命令大全

原创于 2025-09-25 16:47:48 发布 · 811 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #oracle

MySQL 专栏收录该内容

2 篇文章

订阅专栏

该文章已生成可运行项目，

SQL 命令大全，涵盖从基础到高级的各种 SQL 操作：

SQL 分类概览

数据定义语言 (DDL)
数据操作语言 (DML)
数据查询语言 (DQL)
数据控制语言 (DCL)
事务控制语言 (TCL)

一、数据定义语言 (DDL)

数据库操作

-- 创建数据库
CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS company;

-- 选择数据库
USE database_name;

-- 删除数据库
DROP DATABASE database_name;
DROP DATABASE IF EXISTS company;

-- 显示所有数据库
SHOW DATABASES;

表操作

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    salary DECIMAL(10,2),
    department_id INT,
    hire_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

-- 删除表
DROP TABLE employees;
DROP TABLE IF EXISTS employees;

-- 清空表数据
TRUNCATE TABLE employees;

-- 修改表结构
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees MODIFY COLUMN name VARCHAR(150);
ALTER TABLE employees RENAME COLUMN name TO full_name;
ALTER TABLE employees RENAME TO staff;

-- 显示表结构
DESCRIBE employees;
DESC employees;
SHOW COLUMNS FROM employees;

索引操作

-- 创建索引
CREATE INDEX idx_name ON employees(name);
CREATE UNIQUE INDEX idx_email ON employees(email);
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- 删除索引
DROP INDEX idx_name ON employees;

二、数据操作语言 (DML)

插入数据

-- 插入单条记录
INSERT INTO employees (name, email, age, salary, department_id)
VALUES ('张三', 'zhangsan@email.com', 25, 5000.00, 1);

-- 插入多条记录
INSERT INTO employees (name, email, age, salary, department_id)
VALUES 
    ('李四', 'lisi@email.com', 30, 6000.00, 1),
    ('王五', 'wangwu@email.com', 28, 5500.00, 2);

-- 插入查询结果
INSERT INTO employee_backup 
SELECT * FROM employees WHERE salary > 5000;

更新数据

-- 更新记录
UPDATE employees 
SET salary = salary * 1.1 
WHERE department_id = 1;

UPDATE employees 
SET salary = 6000, age = 31 
WHERE name = '张三';

-- 使用子查询更新
UPDATE employees 
SET salary = (SELECT AVG(salary) FROM employees) 
WHERE salary < 5000;

删除数据

-- 删除记录
DELETE FROM employees WHERE id = 5;
DELETE FROM employees WHERE salary < 3000;

-- 删除所有记录
DELETE FROM employees;

-- 使用子查询删除
DELETE FROM employees 
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '北京'
);

三、数据查询语言 (DQL)

基础查询

-- 查询所有列
SELECT * FROM employees;

-- 查询特定列
SELECT name, email, salary FROM employees;

-- 去重查询
SELECT DISTINCT department_id FROM employees;

-- 列别名
SELECT name AS 姓名, salary AS 月薪 FROM employees;
SELECT name 姓名, salary 月薪 FROM employees;

WHERE 条件查询

-- 比较运算符
SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE name LIKE '张%';
SELECT * FROM employees WHERE email LIKE '%@gmail.com';
SELECT * FROM employees WHERE name LIKE '_三%';

-- IN 操作符
SELECT * FROM employees WHERE department_id IN (1, 3, 5);
SELECT * FROM employees WHERE name IN ('张三', '李四');

-- NULL 值判断
SELECT * FROM employees WHERE email IS NULL;
SELECT * FROM employees WHERE email IS NOT NULL;

-- 组合条件
SELECT * FROM employees 
WHERE salary > 5000 AND age < 35;
SELECT * FROM employees 
WHERE department_id = 1 OR department_id = 2;
SELECT * FROM employees 
WHERE NOT (salary < 3000);

排序和限制

-- 排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department_id ASC, salary DESC;

-- 限制结果集
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 5, 10;  -- 跳过5条，取10条
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

聚合函数

-- 常用聚合函数
SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees;
SELECT AVG(salary) AS 平均工资 FROM employees;
SELECT MAX(salary) AS 最高工资, MIN(salary) AS 最低工资 FROM employees;
SELECT SUM(salary) AS 工资总额 FROM employees;

-- 分组统计
SELECT department_id, COUNT(*) AS 人数, AVG(salary) AS 平均工资
FROM employees 
GROUP BY department_id;

-- HAVING 子句
SELECT department_id, AVG(salary) AS 平均工资
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) > 5000;

连接查询

-- 内连接
SELECT e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 左连接
SELECT e.name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 右连接
SELECT e.name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- 全外连接
SELECT e.name, d.name AS department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- 自连接
SELECT e1.name AS 员工, e2.name AS 经理
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- 多表连接
SELECT e.name, d.name AS department, p.name AS project
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON e.department_id = p.department_id;

子查询

-- 标量子查询
SELECT name, salary, 
       (SELECT AVG(salary) FROM employees) AS 平均工资
FROM employees;

-- IN 子查询
SELECT name FROM employees 
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '北京'
);

-- EXISTS 子查询
SELECT name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e 
    WHERE e.department_id = d.id AND e.salary > 8000
);

-- 比较子查询
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

四、数据控制语言 (DCL)

用户和权限管理

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

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

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

-- 撤销权限
REVOKE INSERT ON database.table FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON company.* FROM 'admin'@'%';

-- 查看权限
SHOW GRANTS FOR 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

五、事务控制语言 (TCL)

事务管理

-- 开始事务
START TRANSACTION;
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 事务示例
START TRANSACTION;

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

-- 如果发生错误
-- ROLLBACK;

COMMIT;

六、高级 SQL 功能

视图 (Views)

-- 创建视图
CREATE VIEW high_salary_employees AS
SELECT name, salary, department_id
FROM employees
WHERE salary > 5000;

-- 使用视图
SELECT * FROM high_salary_employees;

-- 修改视图
CREATE OR REPLACE VIEW high_salary_employees AS
SELECT name, salary, department_id, hire_date
FROM employees
WHERE salary > 6000;

-- 删除视图
DROP VIEW high_salary_employees;

存储过程 (Stored Procedures)

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeeCount(IN dept_id INT, OUT emp_count INT)
BEGIN
    SELECT COUNT(*) INTO emp_count 
    FROM employees 
    WHERE department_id = dept_id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetEmployeeCount(1, @count);
SELECT @count;

函数 (Functions)

-- 创建函数
DELIMITER //
CREATE FUNCTION GetDepartmentName(emp_id INT) 
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
    DECLARE dept_name VARCHAR(100);
    SELECT d.name INTO dept_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE e.id = emp_id;
    RETURN dept_name;
END //
DELIMITER ;

-- 使用函数
SELECT name, GetDepartmentName(id) AS department FROM employees;

触发器 (Triggers)

-- 创建触发器
DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    END IF;
END //
DELIMITER ;

-- 删除触发器
DROP TRIGGER before_employee_insert;

七、窗口函数 (Window Functions)

-- 排名函数
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- 分区窗口函数
SELECT name, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary
FROM employees;

-- 移动平均
SELECT name, hire_date, salary,
       AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

八、常用实用查询

分页查询

-- MySQL 分页
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

-- SQL Server 分页
SELECT * FROM employees 
ORDER BY id 
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

-- Oracle 分页
SELECT * FROM (
    SELECT e.*, ROWNUM rn FROM employees e WHERE ROWNUM <= 30
) WHERE rn > 20;

递归查询 (CTE)

-- 通用表表达式
WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚点成员
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归成员
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

条件查询

-- CASE 表达式
SELECT name, salary,
       CASE 
           WHEN salary < 3000 THEN '低'
           WHEN salary BETWEEN 3000 AND 6000 THEN '中'
           ELSE '高'
       END AS salary_level
FROM employees;

-- COALESCE 和 NULLIF
SELECT name, COALESCE(email, '无邮箱') AS email,
       NULLIF(salary, 0) AS salary  -- 如果salary为0则返回NULL
FROM employees;

九、数据库维护

备份和恢复

-- 备份表
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- 优化表
OPTIMIZE TABLE employees;

-- 分析表
ANALYZE TABLE employees;

-- 检查表
CHECK TABLE employees;