SQL语法速通

时间：2024-12-16 07:13:27

速通SQL语法

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。它用于查询、插入、更新和删除数据库中的数据。在本教程中，我们将分别介绍 SQL 的基础和进阶知识，涵盖 SQL 查询的基本操作以及如何进行 SQL 性能优化，包括常见的优化技术如 CTE（公用表表达式）和其他优化手段。

一、SQL 基础

1. 数据库和表的基本操作

SQL 的基础操作通常包括数据库的创建、删除和表的操作。

  • 创建数据库
CREATE DATABASE my_database;
  • 创建表
CREATE TABLE users (
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(50) NOT NULL,
   email VARCHAR(100) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • 插入数据
INSERT INTO users (username, email) 
VALUES ('Alice', 'alice@example.com'), 
       ('Bob', 'bob@example.com');
  • 查询数据
SELECT * FROM users;
  • 更新数据
UPDATE users
SET email = 'new_email@example.com'
WHERE username = 'Alice';
  • 删除数据
DELETE FROM users WHERE username = 'Bob';

2.查询数据的基本操作

  • 选择特定列
SELECT username, email FROM users;
  • 条件查询
SELECT * FROM users WHERE username = 'Alice';
  • 排序
SELECT * FROM users ORDER BY created_at DESC;
  • 限制返回结果
SELECT * FROM users LIMIT 5;
  • 聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;

3.常见的数据类型

数值类型：INT, FLOAT, DECIMAL
字符类型：VARCHAR, CHAR, TEXT
日期时间类型：DATE, TIMESTAMP, DATETIME
布尔类型：BOOLEAN

二、进阶SQL

1.联接（JOIN）

SQL 中的联接（JOIN）用于将多个表中的数据组合在一起。

  • 内联接（INNER JOIN）
SELECT orders.id, users.username 
FROM orders 
INNER JOIN users ON orders.user_id = users.id;
  • 左联接（LEFT JOIN）
SELECT users.username, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
  • 右联接（RIGHT JOIN）
SELECT users.username, orders.id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
  • 全联接（FULL JOIN）
SELECT users.username, orders.id
FROM users
FULL JOIN orders ON users.id = orders.user_id;

2. 子查询（Subqueries）

子查询是嵌套在其他查询中的查询，用于在查询中动态计算值。

  • 在SELECT中使用子查询
SELECT username, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;
  • 在WHER中使用子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)
  • 在FROM中使用子查询
SELECT * FROM (SELECT * FROM users WHERE active = 1) AS active_users;

3. 公用表表达式（CTE）

CTE（Common Table Expression）是 SQL 的一种临时结果集，可以提高复杂查询的可读性和性能。

  • 基本CTE示例
WITH RecentOrders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY user_id
)
SELECT users.username, RecentOrders.order_count
FROM users
INNER JOIN RecentOrders ON users.id = RecentOrders.user_id;

CTE 可以简化嵌套查询，使查询逻辑更清晰，并且能够在查询中多次引用临时表。

4.索引

索引可以显著提高查询性能，特别是在查询条件中包含大数据量的表时。SQL 索引通过减少扫描表的行数来加速查询。

  • 创建索引
CREATE INDEX idx_username ON users(username);
  • 删除索引
DROP INDEX idx_username ON users;
  • 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

5.事务

事务用于确保数据库操作的原子性，保证操作的一致性、隔离性和持久性（ACID）。

  • 开始事务
START TRANSACTION;
  • 提交事务
COMMIT;
  • 回滚事务
ROLLBACK;

6.视图（Views）

视图是一个虚拟表，它是从数据库中的一个或多个表中检索的数据的集合。视图本身不存储数据，而是存储查询。

  • 创建视图
CREATE VIEW active_users AS
SELECT username, email FROM users WHERE active = 1;
  • 查询视图
SELECT * FROM active_users;
  • 删除视图
DROP VIEW active_users;

三、SQL性能优化技巧

1.避免使用SELECT *

尽量只选择需要的列，减少不必要的数据传输。

SELECT username, email FROM users;

2. 使用适当的索引

确保在查询条件中使用的列上创建了索引，尤其是经常用作过滤条件或连接条件的列。

3.避免在 WHERE 子句中使用函数

如果在查询条件中使用了函数，数据库可能无法有效利用索引，导致全表扫描。

-- 不推荐的查询方式
SELECT * FROM users WHERE YEAR(created_at) = 2024;

4.避免返结果

对于大数据集，使用LIMIT限制查询结果的数量，可以避免查询过多的数据。

SELECT * FROM users LIMIT 100;

5.避免重复的计算

使用 CTE 或临时表将计算移到查询外部，减少重复计算的次数。

WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT user_id, COUNT(*) FROM recent_orders GROUP BY user_id;

6.分析查询计划

使用EXPLAIN分析查询的执行计划，查看数据库是如何执行查询的，从而找出潜在的性能瓶颈。

EXPLAIN SELECT * FROM users WHERE username = 'Alice';

7. 分区表（Partitioning）

分区表是将大表的数据分割成多个较小的物理存储部分，可以提高查询和维护的效率。

CREATE TABLE orders (
    id INT,
    order_date DATE,
    total_amount DECIMAL
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

8.批量操作

对于大量数据的插入、更新或删除操作，使用批量操作而非单条逐条执行，可以显著提高效率。

  • 批量插入
INSERT INTO orders (user_id, order_date, total_amount)
VALUES 
(1, '2024-01-01', 100),
(2, '2024-01-02', 150),
(3, '2024-01-03', 200);
  • 批量更新
UPDATE orders
SET total_amount = total_amount * 1.1
WHERE order_date >= '2024-01-01';

9.慎用DISTINCT和GROUP BY

DISTINCT和GROUP BY和需要对整个数据集进行排序和去重，通常会增加查询的复杂性。避免不必要的去重操作。

10. 缓存常用查询结果

对于一些频繁查询的数据，可以使用缓存技术（如 Redis）来减少数据库的查询压力，提升性能。

相关文章