【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)
BEGINDECLARE current_status VARCHAR(20);-- 获取当前订单状态SELECT status INTO current_status FROM orders WHERE order_id = order_id_param;-- 只处理待处理订单IF current_status = 'pending' THENSTART TRANSACTION;-- 更新订单状态UPDATE orders SET status = 'processing' WHERE order_id = order_id_param;-- 更新产品库存UPDATE products pJOIN order_items oi ON p.product_id = oi.product_idSET p.stock_quantity = p.stock_quantity - oi.quantityWHERE oi.order_id = order_id_param;-- 记录库存变更INSERT INTO inventory_history (product_id, quantity_change, reason, reference_id)SELECT product_id, -quantity, 'sale', order_id_paramFROM order_itemsWHERE order_id = order_id_param;COMMIT;SELECT 'Order processed successfully' AS message;ELSESELECT 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的基础。这些操作包括数据库的创建、修改和删除，表的设计、修改和管理，以及数据的增删改查。随着经验的积累，您可以进一步探索更高级的功能，如存储过程、触发器、视图和事务管理，以构建更复杂、更高效的数据库应用程序。

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