【MySQL】数据库、数据表的基本操作

个人主页：Guiat
归属专栏：MySQL

正文

1. MySQL基础命令

MySQL是一种流行的关系型数据库管理系统，掌握基本的数据库和表操作命令是使用MySQL的基础。

1.1 连接MySQL

# 连接本地MySQL服务器
mysql -u username -p

# 连接远程MySQL服务器
mysql -h hostname -u username -p -P port

1.2 基本命令概览

-- 显示MySQL版本
SELECT VERSION();

-- 显示当前日期时间
SELECT NOW();

-- 显示当前用户
SELECT USER();

-- 显示可用的命令
HELP;

-- 退出MySQL客户端
EXIT;
-- 或
QUIT;

2. 数据库操作

2.1 创建数据库

-- 基本语法
CREATE DATABASE database_name;

-- 指定字符集和排序规则
CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 仅当数据库不存在时创建
CREATE DATABASE IF NOT EXISTS database_name;

2.2 查看数据库

-- 显示所有数据库
SHOW DATABASES;

-- 显示创建数据库的SQL语句
SHOW CREATE DATABASE database_name;

-- 显示数据库状态
SHOW STATUS;

2.3 选择数据库

-- 切换到指定数据库
USE database_name;

-- 查看当前选中的数据库
SELECT DATABASE();

2.4 修改数据库

-- 修改数据库字符集
ALTER DATABASE database_name 
CHARACTER SET = utf8mb4;

-- 修改数据库排序规则
ALTER DATABASE database_name 
COLLATE = utf8mb4_unicode_ci;

2.5 删除数据库

-- 删除数据库
DROP DATABASE database_name;

-- 仅当数据库存在时删除
DROP DATABASE IF EXISTS database_name;

2.6 数据库备份与恢复

# 使用mysqldump备份数据库
mysqldump -u username -p database_name > backup_file.sql

# 恢复数据库
mysql -u username -p database_name < backup_file.sql

3. 表操作基础

3.1 创建表

-- 基本表创建语法
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

-- 示例：创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    birth_date DATE,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE
);

3.2 查看表信息

-- 显示当前数据库的所有表
SHOW TABLES;

-- 显示表结构
DESCRIBE table_name;
-- 或
DESC table_name;

-- 显示创建表的SQL语句
SHOW CREATE TABLE table_name;

-- 显示表状态
SHOW TABLE STATUS LIKE 'table_name';

3.3 创建临时表

临时表在会话结束时自动删除：

-- 创建临时表
CREATE TEMPORARY TABLE temp_table (
    id INT,
    name VARCHAR(50)
);

3.4 创建表的复制

-- 复制表结构
CREATE TABLE new_table LIKE original_table;

-- 复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM original_table;

-- 复制表结构和部分数据
CREATE TABLE new_table AS 
SELECT * FROM original_table WHERE condition;

4. 表结构修改

4.1 添加列

-- 添加新列
ALTER TABLE table_name 
ADD COLUMN column_name datatype constraints;

-- 在特定位置添加列
ALTER TABLE table_name 
ADD COLUMN column_name datatype constraints 
AFTER existing_column;

-- 添加列到表的第一个位置
ALTER TABLE table_name 
ADD COLUMN column_name datatype constraints 
FIRST;

-- 添加多列
ALTER TABLE table_name
ADD COLUMN column1 datatype constraints,
ADD COLUMN column2 datatype constraints;

4.2 修改列

-- 修改列的数据类型
ALTER TABLE table_name 
MODIFY COLUMN column_name new_datatype;

-- 修改列名和数据类型
ALTER TABLE table_name 
CHANGE COLUMN old_column_name new_column_name new_datatype constraints;

-- 修改列的默认值
ALTER TABLE table_name 
ALTER COLUMN column_name SET DEFAULT default_value;

-- 删除列的默认值
ALTER TABLE table_name 
ALTER COLUMN column_name DROP DEFAULT;

4.3 删除列

-- 删除单列
ALTER TABLE table_name 
DROP COLUMN column_name;

-- 删除多列
ALTER TABLE table_name 
DROP COLUMN column1,
DROP COLUMN column2;

4.4 重命名表

-- 重命名表
RENAME TABLE old_table_name TO new_table_name;

-- 或使用ALTER TABLE
ALTER TABLE old_table_name 
RENAME TO new_table_name;

4.5 删除表

-- 删除表
DROP TABLE table_name;

-- 仅当表存在时删除
DROP TABLE IF EXISTS table_name;

-- 删除多个表
DROP TABLE table1, table2, table3;

5. 约束与键

5.1 主键约束

-- 创建表时定义主键
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- 为已有的表添加主键
ALTER TABLE table_name 
ADD PRIMARY KEY (column_name);

-- 删除主键
ALTER TABLE table_name 
DROP PRIMARY KEY;

5.2 外键约束

-- 创建表时定义外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 为已有的表添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- 添加带有删除和更新行为的外键
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- 删除外键约束
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

5.3 唯一约束

-- 创建表时定义唯一约束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 为已有的表添加唯一约束
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email);

-- 删除唯一约束
ALTER TABLE users
DROP INDEX uq_email;

5.4 检查约束

MySQL 8.0及以上版本支持CHECK约束：

-- 创建表时定义检查约束
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18)
);

-- 为已有的表添加检查约束
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 删除检查约束
ALTER TABLE employees
DROP CHECK chk_salary;

5.5 默认值约束

-- 创建表时定义默认值
CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 为已有的列添加默认值
ALTER TABLE articles
ALTER COLUMN published SET DEFAULT TRUE;

-- 删除默认值
ALTER TABLE articles
ALTER COLUMN published DROP DEFAULT;

6. 索引操作

6.1 创建索引

-- 创建普通索引
CREATE INDEX idx_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_name_email ON users (name, email);

-- 创建前缀索引
CREATE INDEX idx_title ON articles (title(50));

-- 在表创建时定义索引
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    INDEX idx_name (name)
);

-- 使用ALTER TABLE添加索引
ALTER TABLE customers
ADD INDEX idx_email (email);

6.2 查看索引

-- 查看表的所有索引
SHOW INDEX FROM table_name;

6.3 删除索引

-- 删除索引
DROP INDEX index_name ON table_name;

-- 使用ALTER TABLE删除索引
ALTER TABLE table_name
DROP INDEX index_name;

7. 表数据操作

7.1 插入数据

-- 插入单行数据
INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

-- 插入多行数据
INSERT INTO table_name (column1, column2, ...) 
VALUES 
    (value1, value2, ...),
    (value1, value2, ...),
    (value1, value2, ...);

-- 插入所有列的数据
INSERT INTO table_name 
VALUES (value1, value2, ...);

-- 从另一个表插入数据
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ... 
FROM source_table
WHERE condition;

7.2 更新数据

-- 更新所有行
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

-- 使用子查询更新数据
UPDATE table_name
SET column1 = (SELECT column2 FROM another_table WHERE condition)
WHERE condition;

-- 多表更新
UPDATE table1 t1
JOIN table2 t2 ON t1.id = t2.id
SET t1.column1 = t2.column2
WHERE condition;

7.3 删除数据

-- 删除满足条件的行
DELETE FROM table_name
WHERE condition;

-- 删除所有行
DELETE FROM table_name;

-- 截断表（更快但不可回滚）
TRUNCATE TABLE table_name;

-- 多表删除
DELETE t1 FROM table1 t1
JOIN table2 t2 ON t1.id = t2.ref_id
WHERE condition;

7.4 查询数据

-- 基本查询
SELECT column1, column2
FROM table_name
WHERE condition;

-- 查询所有列
SELECT * FROM table_name;

-- 查询唯一值
SELECT DISTINCT column FROM table_name;

-- 使用条件查询
SELECT * FROM table_name
WHERE column = value AND/OR another_column > value;

-- 使用排序
SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC;

-- 使用限制
SELECT * FROM table_name
LIMIT 10 OFFSET 20;

-- 分组查询
SELECT column1, COUNT(*) as count
FROM table_name
GROUP BY column1
HAVING count > 5;

8. 高级表操作

8.1 表分区

-- 创建分区表（按范围分区）
CREATE TABLE sales (
    id INT,
    amount DECIMAL(10,2),
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- 查看分区信息
SHOW CREATE TABLE sales;
SELECT * FROM information_schema.PARTITIONS 
WHERE TABLE_NAME = 'sales';

8.2 视图操作

-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

-- 查看视图
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

-- 更新视图
ALTER VIEW view_name AS
SELECT column1, column2, column3
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW IF EXISTS view_name;

8.3 存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    -- 存储过程体
    SELECT column INTO param2 FROM table WHERE id = param1;
END //
DELIMITER ;

-- 调用存储过程
CALL procedure_name(5, @result);
SELECT @result;

-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name;

9. 事务控制

9.1 事务基本操作

-- 开始事务
START TRANSACTION;

-- 执行SQL语句
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
INSERT INTO transactions (account_id, amount) VALUES (1, -500);

-- 提交事务
COMMIT;

-- 或回滚事务
-- ROLLBACK;

9.2 设置保存点

-- 开始事务
START TRANSACTION;

-- 执行操作
INSERT INTO table1 VALUES (1, 'value');

-- 设置保存点
SAVEPOINT point1;

-- 执行更多操作
INSERT INTO table2 VALUES (2, 'value');

-- 回滚到保存点
ROLLBACK TO SAVEPOINT point1;

-- 提交事务（只提交保存点之前的操作）
COMMIT;

9.3 事务隔离级别

-- 查看当前事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

-- 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

10. 实际应用案例

10.1 电子商务数据库设计

-- 创建数据库
CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce;

-- 创建客户表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_name (last_name, first_name)
);

-- 创建地址表
CREATE TABLE addresses (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    address_type ENUM('billing', 'shipping') DEFAULT 'shipping',
    address_line1 VARCHAR(100) NOT NULL,
    address_line2 VARCHAR(100),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(50),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(50) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    INDEX idx_customer (customer_id)
);

-- 创建类别表
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

-- 创建产品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_name (name)
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address_id INT NOT NULL,
    billing_address_id INT NOT NULL,
    shipping_fee DECIMAL(10, 2) DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('credit_card', 'paypal', 'bank_transfer'),
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    FOREIGN KEY (shipping_address_id) REFERENCES addresses(address_id) ON DELETE RESTRICT,
    FOREIGN KEY (billing_address_id) REFERENCES addresses(address_id) ON DELETE RESTRICT,
    INDEX idx_customer (customer_id),
    INDEX idx_date (order_date)
);

-- 创建订单明细表
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

-- 创建产品评价表
CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    UNIQUE KEY unique_review (product_id, customer_id),
    INDEX idx_product (product_id)
);

-- 创建产品库存历史表
CREATE TABLE inventory_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity_change INT NOT NULL,
    reason ENUM('purchase', 'sale', 'return', 'adjustment'),
    reference_id INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_date (changed_at)
);

-- 创建订单状态历史视图
CREATE VIEW order_status_history AS
SELECT 
    orders.order_id,
    customers.email,
    orders.status,
    orders.total_amount,
    orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- 创建存储过程：处理订单
DELIMITER //
CREATE PROCEDURE process_order(IN order_id_param INT)
BEGIN
    DECLARE current_status VARCHAR(20);
    
    -- 获取当前订单状态
    SELECT status INTO current_status FROM orders WHERE order_id = order_id_param;
    
    -- 只处理待处理订单
    IF current_status = 'pending' THEN
        START TRANSACTION;
        
        -- 更新订单状态
        UPDATE orders SET status = 'processing' WHERE order_id = order_id_param;
        
        -- 更新产品库存
        UPDATE products p
        JOIN order_items oi ON p.product_id = oi.product_id
        SET p.stock_quantity = p.stock_quantity - oi.quantity
        WHERE oi.order_id = order_id_param;
        
        -- 记录库存变更
        INSERT INTO inventory_history (product_id, quantity_change, reason, reference_id)
        SELECT product_id, -quantity, 'sale', order_id_param
        FROM order_items
        WHERE order_id = order_id_param;
        
        COMMIT;
        
        SELECT 'Order processed successfully' AS message;
    ELSE
        SELECT CONCAT('Cannot process order. Current status: ', current_status) AS message;
    END IF;
END //
DELIMITER ;

10.2 数据库维护操作

-- 分析表
ANALYZE TABLE customers, orders, products;

-- 检查表
CHECK TABLE customers, orders, products;

-- 优化表
OPTIMIZE TABLE customers, orders, products;

-- 修复表
REPAIR TABLE customers, orders, products;

10.3 常见查询和操作示例

-- 插入客户
INSERT INTO customers (first_name, last_name, email, password, phone)
VALUES ('John', 'Doe', 'john.doe@example.com', SHA2('password123', 256), '555-123-4567');

-- 插入产品
INSERT INTO categories (name, description) VALUES ('Electronics', 'Electronic devices and accessories');
INSERT INTO products (name, description, price, stock_quantity, category_id)
VALUES ('Smartphone X', 'Latest smartphone with advanced features', 699.99, 50, 1);

-- 创建订单（简化版）
INSERT INTO addresses (customer_id, address_type, address_line1, city, postal_code, country)
VALUES (1, 'shipping', '123 Main St', 'New York', '10001', 'USA');

INSERT INTO orders (customer_id, shipping_address_id, billing_address_id, total_amount, payment_method)
VALUES (1, 1, 1, 699.99, 'credit_card');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 699.99);

-- 处理订单
CALL process_order(1);

-- 复杂查询：查找畅销产品
SELECT 
    p.product_id,
    p.name,
    p.price,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY p.product_id, p.name, p.price
ORDER BY total_sold DESC
LIMIT 10;

-- 创建每日销售报表视图
CREATE VIEW daily_sales AS
SELECT 
    DATE(o.order_date) AS sale_date,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(o.total_amount) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE(o.order_date)
ORDER BY sale_date DESC;

掌握数据库和表的基本操作是使用MySQL的基础。这些操作包括数据库的创建、修改和删除，表的设计、修改和管理，以及数据的增删改查。随着经验的积累，您可以进一步探索更高级的功能，如存储过程、触发器、视图和事务管理，以构建更复杂、更高效的数据库应用程序。

结语
感谢您的阅读！期待您的一键三连！欢迎指正！