個人主頁：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的基礎。這些操作包括數據庫的創建、修改和刪除，表的設計、修改和管理，以及數據的增刪改查。隨著經驗的積累，您可以進一步探索更高級的功能，如存儲過程、觸發器、視圖和事務管理，以構建更復雜、更高效的數據庫應用程序。

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！