数据库中常用的SQL

最新推荐文章于 2026-06-10 08:06:29 发布

原创最新推荐文章于 2026-06-10 08:06:29 发布 · 339 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

第一部分、学习中涉及的SQL语句

数据库操作

-- 创建数据库 my_database，如果该数据库不存在则创建
-- 此数据库用于存储学习相关的测试数据
CREATE DATABASE IF NOT EXISTS my_database COMMENT '用于学习测试的数据库';

-- 使用名为 my_database 的数据库
-- 执行此语句后，后续的 SQL 操作都会在这个数据库上进行
USE my_database;

-- 删除数据库 my_database，如果该数据库存在则删除
-- IF EXISTS 用于避免在尝试删除一个不存在的数据库时抛出错误
DROP DATABASE IF EXISTS my_database;

表操作

-- 创建一个名为 users 的表，用于存储用户信息
CREATE TABLE users (
    -- 定义一个名为 id 的整数类型列，该列作为主键，并且自动递增
    -- AUTO_INCREMENT 会让数据库在插入新记录时自动为该列生成一个唯一的递增值
    -- PRIMARY KEY 表示该列是表的主键，用于唯一标识表中的每一行
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户的唯一标识',
    -- 定义一个名为 username 的可变长度字符串列，最大长度为 50，且不能为空
    -- NOT NULL 约束确保该列在插入数据时必须有值
    username VARCHAR(50) NOT NULL COMMENT '用户的用户名',
    -- 定义一个名为 email 的可变长度字符串列，最大长度为 100，且该列的值必须唯一
    -- UNIQUE 约束保证该列中不会有重复的值
    email VARCHAR(100) UNIQUE COMMENT '用户的电子邮箱地址',
    -- 定义一个名为 age 的整数类型列，用于存储用户的年龄
    age INT COMMENT '用户的年龄'
) COMMENT '存储用户基本信息的表';

-- 查看 users 表的结构
-- 此语句会显示表的列名、数据类型、是否允许为空等信息
DESCRIBE users;

-- 修改 users 表的结构，添加一个名为 phone 的可变长度字符串列，最大长度为 20
ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT '用户的电话号码';

-- 修改 users 表的结构，删除名为 phone 的列
ALTER TABLE users DROP COLUMN phone;

-- 删除名为 users 的表，如果该表存在则删除
-- IF EXISTS 避免在表不存在时抛出错误
DROP TABLE IF EXISTS users;

数据操作

-- 向 users 表中插入一条新记录
-- 只插入 username、email 和 age 列的值，id 列会因为 AUTO_INCREMENT 自动生成
INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25);

-- 从 users 表中查询所有列的数据
-- * 表示选择表中的所有列
SELECT * FROM users;

-- 从 users 表中查询 username 和 email 列的数据，并且只返回 age 大于 20 的记录
-- WHERE 子句用于筛选满足特定条件的记录
SELECT username, email FROM users WHERE age > 20;

-- 更新 users 表中 username 为 'john_doe' 的记录的 age 列为 26
-- SET 子句用于指定要更新的列和新的值
UPDATE users SET age = 26 WHERE username = 'john_doe';

-- 从 users 表中删除 username 为 'john_doe' 的记录
-- WHERE 子句指定了要删除的记录的条件
DELETE FROM users WHERE username = 'john_doe';

索引操作

-- 在 users 表的 username 列上创建一个名为 idx_username 的索引
-- 索引可以加快对该列的查询速度，特别是在使用 WHERE、JOIN 和 ORDER BY 时
CREATE INDEX idx_username ON users (username);

-- 删除 users 表上名为 idx_username 的索引
DROP INDEX idx_username ON users;

约束操作

-- 创建一个名为 departments 的表，用于存储部门信息
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门的唯一标识',
    dept_name VARCHAR(100) NOT NULL COMMENT '部门的名称'
) COMMENT '存储公司部门信息的表';

-- 创建一个名为 employees 的表，包含外键约束，用于存储员工信息
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工的唯一标识',
    emp_name VARCHAR(100) NOT NULL COMMENT '员工的姓名',
    dept_id INT COMMENT '员工所属部门的标识',
    -- 定义外键约束，将 employees 表的 dept_id 列关联到 departments 表的 dept_id 列
    -- ON DELETE CASCADE 表示当 departments 表中的一条记录被删除时，employees 表中关联的记录也会被自动删除
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE
) COMMENT '存储公司员工信息的表，关联部门表';

高级查询操作

-- 使用 JOIN 操作从 employees 表和 departments 表中查询数据
-- 将 employees 表和 departments 表通过 dept_id 列进行关联
-- 只返回 emp_name 和 dept_name 列的数据
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 使用子查询查询 age 大于平均年龄的用户
-- 子查询先计算出 users 表中所有用户的平均年龄
-- 外层查询再筛选出 age 大于平均年龄的用户
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

查询优化技巧

-- 避免使用 SELECT *，只选择需要的列
-- 这样可以减少数据传输量，提高查询效率
SELECT id, username FROM users WHERE age > 20;

-- 使用 JOIN 代替子查询
-- 通常 JOIN 操作比子查询更高效，尤其是在处理大量数据时
SELECT u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20;

第二部分、举例单体项目

-- 创建名为 single_project_db 的数据库，如果该数据库不存在则创建
-- 此数据库用于单体项目的数据存储
CREATE DATABASE IF NOT EXISTS single_project_db COMMENT '单体项目数据库';
-- 使用 single_project_db 数据库
USE single_project_db;

-- 创建管理员表 admins，存储项目的管理员信息
CREATE TABLE admins (
    -- 管理员的唯一标识，自动递增的整数类型，作为主键
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '管理员的唯一标识',
    -- 管理员的用户名，最大长度为 50，不能为空且必须唯一
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '管理员的用户名',
    -- 管理员的密码，最大长度为 255，不能为空
    password VARCHAR(255) NOT NULL COMMENT '管理员的密码'
) COMMENT '单体项目管理员信息表';

-- 创建商品表 products，存储项目中的商品信息
CREATE TABLE products (
    -- 商品的唯一标识，自动递增的整数类型，作为主键
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品的唯一标识',
    -- 商品的名称，最大长度为 100，不能为空
    name VARCHAR(100) NOT NULL COMMENT '商品的名称',
    -- 商品的价格，使用 DECIMAL 类型，总长度为 10，小数部分为 2 位，不能为空
    price DECIMAL(10, 2) NOT NULL COMMENT '商品的价格'
) COMMENT '单体项目商品信息表';

-- 创建订单表 orders，存储项目中的订单信息
CREATE TABLE orders (
    -- 订单的唯一标识，自动递增的整数类型，作为主键
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单的唯一标识',
    -- 下单的管理员的标识，关联到 admins 表的 id 列
    admin_id INT NOT NULL COMMENT '下单的管理员的标识',
    -- 订单所包含的商品的标识，关联到 products 表的 id 列
    product_id INT NOT NULL COMMENT '订单所包含的商品的标识',
    -- 订单中商品的数量，整数类型，不能为空
    quantity INT NOT NULL COMMENT '订单中商品的数量',
    -- 订单的创建日期和时间，使用 TIMESTAMP 类型，默认值为当前时间
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '订单的创建日期和时间',
    -- 定义外键约束，将 admin_id 列关联到 admins 表的 id 列
    FOREIGN KEY (admin_id) REFERENCES admins(id),
    -- 定义外键约束，将 product_id 列关联到 products 表的 id 列
    FOREIGN KEY (product_id) REFERENCES products(id)
) COMMENT '单体项目订单信息表，关联管理员和商品表';

第三部分、举例微服务项目（分库分表）

-- 创建管理员服务数据库 admin_service_db，如果该数据库不存在则创建
-- 此数据库专门用于管理员服务的数据存储
CREATE DATABASE IF NOT EXISTS admin_service_db COMMENT '管理员服务数据库';
-- 使用 admin_service_db 数据库
USE admin_service_db;

-- 创建管理员表 admins，存储管理员服务的管理员信息
CREATE TABLE admins (
    -- 管理员的唯一标识，自动递增的整数类型，作为主键
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '管理员的唯一标识',
    -- 管理员的用户名，最大长度为 50，不能为空且必须唯一
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '管理员的用户名',
    -- 管理员的密码，最大长度为 255，不能为空
    password VARCHAR(255) NOT NULL COMMENT '管理员的密码'
) COMMENT '管理员服务管理员信息表';

-- 创建商品服务数据库 product_service_db，如果该数据库不存在则创建
-- 此数据库专门用于商品服务的数据存储
CREATE DATABASE IF NOT EXISTS product_service_db COMMENT '商品服务数据库';
-- 使用 product_service_db 数据库
USE product_service_db;

-- 创建商品表 products，存储商品服务的商品信息
CREATE TABLE products (
    -- 商品的唯一标识，自动递增的整数类型，作为主键
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品的唯一标识',
    -- 商品的名称，最大长度为 100，不能为空
    name VARCHAR(100) NOT NULL COMMENT '商品的名称',
    -- 商品的价格，使用 DECIMAL 类型，总长度为 10，小数部分为 2 位，不能为空
    price DECIMAL(10, 2) NOT NULL COMMENT '商品的价格'
) COMMENT '商品服务商品信息表';

-- 创建订单服务数据库 order_service_db，如果该数据库不存在则创建
-- 此数据库专门用于订单服务的数据存储
CREATE DATABASE IF NOT EXISTS order_service_db COMMENT '订单服务数据库';
-- 使用 order_service_db 数据库
USE order_service_db;

-- 创建订单表 orders，存储订单服务的订单信息
CREATE TABLE orders (
    -- 订单的唯一标识，自动递增的整数类型，作为主键
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单的唯一标识',
    -- 下单的管理员的标识
    admin_id INT NOT NULL COMMENT '下单的管理员的标识',
    -- 订单所包含的商品的标识
    product_id INT NOT NULL COMMENT '订单所包含的商品的标识',
    -- 订单中商品的数量，整数类型，不能为空
    quantity INT NOT NULL COMMENT '订单中商品的数量',
    -- 订单的创建日期和时间，使用 TIMESTAMP 类型，默认值为当前时间
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '订单的创建日期和时间'
) COMMENT '订单服务订单信息表';

标签