SQL语法速通
速通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）来减少数据库的查询压力，提升性能。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]