知识库

Appearance

Return to top
On this page

MySQL SQL基础

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。在MySQL中，SQL用于创建、查询、更新和删除数据库中的数据。本文档将介绍MySQL中的SQL基础语法和操作。

SQL分类

SQL语句通常分为以下几类：

  1. DDL（Data Definition Language）：数据定义语言
  2. DML（Data Manipulation Language）：数据操作语言
  3. DQL（Data Query Language）：数据查询语言
  4. DCL（Data Control Language）：数据控制语言

DDL - 数据定义语言

DDL用于定义和管理数据库结构。

CREATE DATABASE

创建新的数据库：

sql
CREATE DATABASE IF NOT EXISTS mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

CREATE TABLE

创建新表：

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE

修改现有表结构：

sql
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改列
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 添加外键
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

DROP TABLE

删除表：

sql
DROP TABLE IF EXISTS users;

DML - 数据操作语言

DML用于插入、更新和删除表中的数据。

INSERT

插入新记录：

sql
-- 插入单行数据
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 25);

-- 插入多行数据
INSERT INTO users (username, email, age) 
VALUES 
    ('jane_smith', 'jane@example.com', 30),
    ('bob_johnson', 'bob@example.com', 28);

-- 从其他表插入数据
INSERT INTO archived_users SELECT * FROM users WHERE age > 65;

UPDATE

更新现有记录：

sql
-- 更新所有记录
UPDATE users SET age = age + 1;

-- 更新特定记录
UPDATE users 
SET email = 'newemail@example.com' 
WHERE username = 'john_doe';

-- 使用JOIN更新
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.created_at
WHERE o.status = 'completed';

DELETE

删除记录：

sql
-- 删除特定记录
DELETE FROM users WHERE age < 18;

-- 删除所有记录（保留表结构）
DELETE FROM users;

-- 使用JOIN删除
DELETE u FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'cancelled';

DQL - 数据查询语言

DQL用于从数据库中检索数据，主要是SELECT语句。

基本SELECT

sql
-- 选择所有列
SELECT * FROM users;

-- 选择特定列
SELECT username, email FROM users;

-- 选择去重数据
SELECT DISTINCT age FROM users;

WHERE子句

sql
-- 等值比较
SELECT * FROM users WHERE age = 25;

-- 比较操作
SELECT * FROM users WHERE age > 18 AND age < 65;

-- 字符串匹配
SELECT * FROM users WHERE username LIKE 'j%';

-- IN操作符
SELECT * FROM users WHERE age IN (25, 30, 35);

-- NULL值检查
SELECT * FROM users WHERE email IS NOT NULL;

排序和限制

sql
-- 排序
SELECT * FROM users ORDER BY age DESC, username ASC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 分页查询
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

聚合函数

sql
-- 计数
SELECT COUNT(*) FROM users;

-- 平均值
SELECT AVG(age) FROM users;

-- 最大值和最小值
SELECT MAX(age), MIN(age) FROM users;

-- 求和
SELECT SUM(age) FROM users;

-- 分组聚合
SELECT age, COUNT(*) as count FROM users GROUP BY age;

JOIN操作

sql
-- 内连接
SELECT u.username, o.order_date 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, o.order_date 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接
SELECT u.username, o.order_date 
FROM users u 
RIGHT JOIN orders o ON u.id = o.user_id;

-- 多表连接
SELECT u.username, o.order_date, p.name 
FROM users u 
JOIN orders o ON u.id = o.user_id 
JOIN products p ON o.product_id = p.id;

子查询

sql
-- 在WHERE中使用子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');

-- 在FROM中使用子查询
SELECT * FROM (
    SELECT username, COUNT(*) as order_count 
    FROM users u 
    JOIN orders o ON u.id = o.user_id 
    GROUP BY u.id
) AS user_orders 
WHERE order_count > 5;

-- 相关子查询
SELECT username, email FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

高级查询

UNION操作

sql
-- 合并结果集（去重）
SELECT username FROM users WHERE age > 30
UNION
SELECT email FROM users WHERE age < 20;

-- 合并结果集（不去重）
SELECT username FROM users WHERE age > 30
UNION ALL
SELECT email FROM users WHERE age < 20;

CASE语句

sql
SELECT 
    username,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 65 THEN '成年人'
        ELSE '老年人'
    END AS age_category
FROM users;

窗口函数

sql
-- 排名函数
SELECT 
    username, 
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) as row_num,
    RANK() OVER (ORDER BY age DESC) as rank_num
FROM users;

-- 窗口聚合
SELECT 
    username,
    age,
    AVG(age) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as avg_age_window
FROM users;

事务操作

sql
-- 开始事务
START TRANSACTION;

-- 执行多个操作
INSERT INTO users (username, email) VALUES ('new_user', 'new@example.com');
INSERT INTO profiles (user_id, bio) VALUES (LAST_INSERT_ID(), 'New user bio');

-- 提交事务
COMMIT;

-- 或回滚事务
-- ROLLBACK;

性能优化提示

  1. 使用索引：为经常查询的列创建索引
  2. **避免SELECT ***：只选择需要的列
  3. 使用LIMIT：限制结果集大小
  4. 优化WHERE条件：使用高效的过滤条件
  5. 避免在WHERE中使用函数：这会导致索引失效

总结

SQL是与MySQL数据库交互的核心语言。掌握基础的SQL语法是进行数据库操作的前提。在实际开发中，需要根据具体需求选择合适的SQL语句，并注意性能优化。后续文档将详细介绍查询优化、索引策略等高级主题。