【MySQL】数据库、数据表的基本操作
个人主页：Guiat
归属专栏：MySQL
文章目录
- 1. MySQL基础命令
- 1.1 连接MySQL
- 1.2 基本命令概览
- 2. 数据库操作
- 2.1 创建数据库
- 2.2 查看数据库
- 2.3 选择数据库
- 2.4 修改数据库
- 2.5 删除数据库
- 2.6 数据库备份与恢复
- 3. 表操作基础
- 3.1 创建表
- 3.2 查看表信息
- 3.3 创建临时表
- 3.4 创建表的复制
- 4. 表结构修改
- 4.1 添加列
- 4.2 修改列
- 4.3 删除列
- 4.4 重命名表
- 4.5 删除表
- 5. 约束与键
- 5.1 主键约束
- 5.2 外键约束
- 5.3 唯一约束
- 5.4 检查约束
- 5.5 默认值约束
- 6. 索引操作
- 6.1 创建索引
- 6.2 查看索引
- 6.3 删除索引
- 7. 表数据操作
- 7.1 插入数据
- 7.2 更新数据
- 7.3 删除数据
- 7.4 查询数据
- 8. 高级表操作
- 8.1 表分区
- 8.2 视图操作
- 8.3 存储过程
- 9. 事务控制
- 9.1 事务基本操作
- 9.2 设置保存点
- 9.3 事务隔离级别
- 10. 实际应用案例
- 10.1 电子商务数据库设计
- 10.2 数据库维护操作
- 10.3 常见查询和操作示例
正文
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
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的基础。这些操作包括数据库的创建、修改和删除，表的设计、修改和管理，以及数据的增删改查。随着经验的积累，您可以进一步探索更高级的功能，如存储过程、触发器、视图和事务管理，以构建更复杂、更高效的数据库应用程序。
结语
感谢您的阅读！期待您的一键三连！欢迎指正！
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。
