SQL语法速通

速通SQL语法

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

1. 数据库和表的基本利用

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

• 创建数据库
  

1. CREATE DATABASE my_database;

复制代码

• 创建表
  

1. CREATE TABLE users (
2.    id INT AUTO_INCREMENT PRIMARY KEY,
3.    username VARCHAR(50) NOT NULL,
4.    email VARCHAR(100) NOT NULL,
5.    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
6. );

复制代码

• 插入数据
  

1. INSERT INTO users (username, email)
2. VALUES ('Alice', 'alice@example.com'),
3.        ('Bob', 'bob@example.com');

复制代码

• 查询数据
  

1. SELECT * FROM users;

复制代码

• 更新数据
  

1. UPDATE users
2. SET email = 'new_email@example.com'
3. WHERE username = 'Alice';

复制代码

• 删除数据
  

1. DELETE FROM users WHERE username = 'Bob';

复制代码

2.查询数据的基本利用

• 选择特定列
  

1. SELECT username, email FROM users;

复制代码

• 条件查询
  

1. SELECT * FROM users WHERE username = 'Alice';

复制代码

• 排序
  

1. SELECT * FROM users ORDER BY created_at DESC;

复制代码

• 限定返回效果
  

1. SELECT * FROM users LIMIT 5;

复制代码

• 聚合函数
  

1. SELECT COUNT(*) FROM users;
2. SELECT AVG(age) FROM users;

复制代码

3.常见的数据类型

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

1.联接（JOIN）

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

• 内联接（INNER JOIN）
  

1. SELECT orders.id, users.username
2. FROM orders
3. INNER JOIN users ON orders.user_id = users.id;

复制代码

• 左联接（LEFT JOIN）
  

1. SELECT users.username, orders.id
2. FROM users
3. LEFT JOIN orders ON users.id = orders.user_id;

复制代码

• 右联接（RIGHT JOIN）
  

1. SELECT users.username, orders.id
2. FROM users
3. RIGHT JOIN orders ON users.id = orders.user_id;

复制代码

• 全联接（FULL JOIN）
  

1. SELECT users.username, orders.id
2. FROM users
3. FULL JOIN orders ON users.id = orders.user_id;

复制代码

2. 子查询（Subqueries）

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

• 在SELECT中使用子查询
  

1. SELECT username, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
2. FROM users;

复制代码

• 在WHER中使用子查询
  

1. SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)

复制代码

• 在FROM中使用子查询
  

1. SELECT * FROM (SELECT * FROM users WHERE active = 1) AS active_users;

复制代码

3. 公用表表达式（CTE）

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

• 基本CTE示例
  

1. WITH RecentOrders AS (
2.     SELECT user_id, COUNT(*) AS order_count
3.     FROM orders
4.     WHERE order_date >= '2024-01-01'
5.     GROUP BY user_id
6. )
7. SELECT users.username, RecentOrders.order_count
8. FROM users
9. INNER JOIN RecentOrders ON users.id = RecentOrders.user_id;

复制代码

CTE 可以简化嵌套查询，使查询逻辑更清晰，而且可以大概在查询中多次引用暂时表。
4.索引

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

• 创建索引
  

1. CREATE INDEX idx_username ON users(username);

复制代码

• 删除索引
  

1. DROP INDEX idx_username ON users;

复制代码

• 唯一索引
  

1. CREATE UNIQUE INDEX idx_email ON users(email);

复制代码

5.事件

事件用于确保数据库利用的原子性，保证利用的同等性、隔离性和持久性（ACID）。

• 开始事件
  

1. START TRANSACTION;

复制代码

• 提交事件
  

1. COMMIT;

复制代码

• 回滚事件
  

1. ROLLBACK;

复制代码

6.视图（Views）

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

• 创建视图
  

1. CREATE VIEW active_users AS
2. SELECT username, email FROM users WHERE active = 1;

复制代码

• 查询视图
  

1. SELECT * FROM active_users;

复制代码

• 删除视图
  

1. DROP VIEW active_users;

复制代码

三、SQL性能优化技巧

1.避免使用SELECT *

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

1. SELECT username, email FROM users;

复制代码

2. 使用适当的索引

确保在查询条件中使用的列上创建了索引，尤其是经常用作过滤条件或连接条件的列。
3.避免在 WHERE 子句中使用函数

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

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

复制代码

4.避免返效果

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

1. SELECT * FROM users LIMIT 100;

复制代码

5.避免重复的盘算

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

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

复制代码

6.分析查询筹划

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

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

复制代码

7. 分区表（Partitioning）

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

1. CREATE TABLE orders (
2.     id INT,
3.     order_date DATE,
4.     total_amount DECIMAL
5. )
6. PARTITION BY RANGE (YEAR(order_date)) (
7.     PARTITION p2019 VALUES LESS THAN (2020),
8.     PARTITION p2020 VALUES LESS THAN (2021),
9.     PARTITION p2021 VALUES LESS THAN (2022)
10. );

复制代码

8.批量利用

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

• 批量插入
  

1. INSERT INTO orders (user_id, order_date, total_amount)
2. VALUES
3. (1, '2024-01-01', 100),
4. (2, '2024-01-02', 150),
5. (3, '2024-01-03', 200);

复制代码

• 批量更新
  

1. UPDATE orders
2. SET total_amount = total_amount * 1.1
3. WHERE order_date >= '2024-01-01';

复制代码

9.慎用DISTINCT和GROUP BY

DISTINCT和GROUP BY和需要对整个数据集进行排序和去重，通常会增加查询的复杂性。避免不必要的去重利用。
10. 缓存常用查询效果

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。