常用的 SQL 语句分类整理

发布于:2025-04-16 ⋅ 阅读:(41) ⋅ 点赞:(0)

以下是常用的 ​​SQL 语句分类整理​​,覆盖数据查询、操作、表管理和高级功能,适用于大多数关系型数据库(如 MySQL、PostgreSQL、SQL Server):


目录

​​一、数据查询(DQL)​​

​​1. 基础查询​​

​​2. 条件过滤(WHERE)​​

​​3. 排序与分页​​

​​二、数据操作(DML)​​

​​1. 插入数据(INSERT)​​

​​2. 更新数据(UPDATE)​​

​​3. 删除数据(DELETE)​​

​​三、表管理(DDL)​​

​​1. 创建表(CREATE TABLE)​​

​​2. 修改表(ALTER TABLE)​​

​​3. 删除表(DROP TABLE)​​

​​四、高级查询​​

​​1. 聚合函数(GROUP BY)​​

​​2. 多表连接(JOIN)​​

​​3. 子查询​​

​​五、实用功能​​

​​1. 日期处理​​

​​2. 字符串处理​​

​​3. 窗口函数​​

​​六、性能优化技巧​​


​一、数据查询(DQL)​

​1. 基础查询​

-- 查询所有字段
SELECT * FROM employees;

-- 查询特定字段
SELECT first_name, last_name FROM employees;

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

​2. 条件过滤(WHERE)​

-- 简单条件
SELECT * FROM orders WHERE amount > 1000;

-- 多条件组合(AND/OR)
SELECT * FROM products 
WHERE price BETWEEN 50 AND 100 
AND category = 'Electronics';

-- 模糊匹配(LIKE)
SELECT * FROM customers 
WHERE email LIKE '%@gmail.com';

-- IN 运算符
SELECT * FROM employees 
WHERE department_id IN (10, 20, 30);

​3. 排序与分页​

-- 排序(ASC/DESC)
SELECT * FROM products 
ORDER BY price DESC, stock_quantity ASC;

-- 分页(MySQL)
SELECT * FROM orders 
LIMIT 10 OFFSET 20;  -- 等价于第3页(每页10条)

-- 分页(SQL Server)
SELECT * FROM orders 
ORDER BY order_date 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

​二、数据操作(DML)​

​1. 插入数据(INSERT)​

-- 插入单条数据
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', NOW());

-- 插入多条数据
INSERT INTO products (name, price)
VALUES 
  ('Laptop', 999.99),
  ('Mouse', 29.99),
  ('Keyboard', 49.99);

​2. 更新数据(UPDATE)​

-- 更新单字段
UPDATE employees 
SET salary = salary * 1.05 
WHERE department = 'Engineering';

-- 更新多字段
UPDATE orders 
SET status = 'Shipped', shipped_date = CURRENT_DATE 
WHERE order_id = 1001;

​3. 删除数据(DELETE)​

-- 删除特定记录
DELETE FROM logs 
WHERE created_at < '2023-01-01';

-- 清空表(慎用!)
TRUNCATE TABLE temp_data;

​三、表管理(DDL)​

​1. 创建表(CREATE TABLE)​

CREATE TABLE employees (
  employee_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  hire_date DATE,
  salary DECIMAL(10, 2),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

​2. 修改表(ALTER TABLE)​

-- 添加列
ALTER TABLE users 
ADD COLUMN phone_number VARCHAR(15);

-- 修改列类型
ALTER TABLE products 
ALTER COLUMN price TYPE DECIMAL(12, 2);

-- 删除列
ALTER TABLE orders 
DROP COLUMN old_reference;

​3. 删除表(DROP TABLE)​

DROP TABLE backup_data;  -- 删除表结构及数据

​四、高级查询​

​1. 聚合函数(GROUP BY)​

-- 统计每个部门的平均工资
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;  -- HAVING过滤聚合结果

​2. 多表连接(JOIN)​

-- 内连接
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- 左连接(包含未匹配的订单)
SELECT o.order_id, c.customer_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

-- 自连接(查找员工的上级)
SELECT e.employee_id, m.manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

​3. 子查询​

-- 子查询在WHERE中
SELECT product_name 
FROM products 
WHERE category_id IN (
  SELECT category_id 
  FROM categories 
  WHERE category_name = 'Books'
);

-- 关联子查询
SELECT e.first_name, e.salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary) 
  FROM employees 
  WHERE department = e.department
);

​五、实用功能​

​1. 日期处理​

-- 当前日期
SELECT CURRENT_DATE;

-- 日期格式化(MySQL)
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month
FROM orders;

-- 日期计算(PostgreSQL)
SELECT order_date + INTERVAL '7 days' AS due_date
FROM orders;

​2. 字符串处理​

-- 拼接字符串(SQL Server)
SELECT first_name + ' ' + last_name AS full_name 
FROM employees;

-- 截取子串(MySQL)
SELECT SUBSTRING(email, 1, 5) AS prefix 
FROM users;

-- 替换文本(PostgreSQL)
SELECT REPLACE(description, 'old', 'new') 
FROM products;

​3. 窗口函数​

-- 计算排名
SELECT 
  product_id,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM products;

-- 累计求和
SELECT 
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

​六、性能优化技巧​

  1. ​索引使用​

    -- 创建索引
    CREATE INDEX idx_customer_email ON customers(email);
  2. ​执行计划分析​

    EXPLAIN SELECT * FROM orders WHERE status = 'Shipped';
  3. ​避免全表扫描​

    • 对WHERE条件列添加索引
    • 避免在WHERE中对字段进行函数操作(如 WHERE YEAR(date) = 2023 → 改为范围查询)

网站公告

今日签到

点亮在社区的每一天
去签到