MySQL數據庫和數據表的基本操作有哪些

# MySQL數據庫和數據表的基本操作指南

## 一、數據庫基本操作

### 1. 創建數據庫
```sql
CREATE DATABASE database_name;
-- 示例：創建名為shop的數據庫
CREATE DATABASE shop;

創建數據庫時可指定字符集和排序規則：

CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

2. 查看數據庫

-- 查看所有數據庫
SHOW DATABASES;

-- 查看特定數據庫創建語句
SHOW CREATE DATABASE database_name;

3. 選擇數據庫

USE database_name;
-- 示例：使用shop數據庫
USE shop;

4. 修改數據庫

ALTER DATABASE database_name 
CHARACTER SET charset_name 
COLLATE collation_name;

5. 刪除數據庫

DROP DATABASE database_name;
-- 危險操作！會刪除所有數據
DROP DATABASE IF EXISTS old_database;

二、數據表基本操作

1. 創建數據表

基本語法：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

示例（創建用戶表）：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 查看數據表

-- 查看當前數據庫所有表
SHOW TABLES;

-- 查看表結構
DESCRIBE table_name;
DESC table_name;  -- 簡寫

-- 查看建表語句
SHOW CREATE TABLE table_name;

3. 修改表結構

添加列

ALTER TABLE table_name 
ADD COLUMN column_name datatype constraints;

修改列

-- 修改數據類型
ALTER TABLE table_name 
MODIFY COLUMN column_name new_datatype;

-- 重命名列
ALTER TABLE table_name 
CHANGE COLUMN old_name new_name datatype;

刪除列

ALTER TABLE table_name 
DROP COLUMN column_name;

重命名表

ALTER TABLE old_name RENAME TO new_name;
-- 或
RENAME TABLE old_name TO new_name;

4. 刪除數據表

DROP TABLE table_name;
-- 安全刪除
DROP TABLE IF EXISTS temp_table;

三、數據操作語言(DML)

1. 插入數據

-- 完整插入
INSERT INTO table_name (col1, col2, ...) 
VALUES (val1, val2, ...);

-- 批量插入
INSERT INTO users (username, email) 
VALUES 
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com');

2. 查詢數據

基礎查詢：

SELECT * FROM table_name;
SELECT col1, col2 FROM table_name;

條件查詢：

SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE username LIKE 'j%';

排序和限制：

SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

3. 更新數據

UPDATE table_name 
SET col1 = val1, col2 = val2 
WHERE condition;

-- 示例：修改用戶密碼
UPDATE users SET password = 'new_hash' WHERE id = 5;

4. 刪除數據

DELETE FROM table_name WHERE condition;

-- 清空表（不可恢復）
TRUNCATE TABLE table_name;

四、約束操作

1. 主鍵約束

-- 創建時指定
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    ...
);

-- 添加主鍵
ALTER TABLE table_name 
ADD PRIMARY KEY (column_name);

2. 外鍵約束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. 唯一約束

ALTER TABLE products 
ADD UNIQUE (product_code);

4. 檢查約束(MySQL 8.0+)

CREATE TABLE employees (
    salary DECIMAL(10,2),
    CHECK (salary > 0)
);

五、索引操作

1. 創建索引

-- 單列索引
CREATE INDEX idx_name ON table_name (column_name);

-- 復合索引
CREATE INDEX idx_name ON users (last_name, first_name);

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

2. 查看索引

SHOW INDEX FROM table_name;

3. 刪除索引

DROP INDEX index_name ON table_name;

六、視圖操作

1. 創建視圖

CREATE VIEW view_name AS
SELECT columns FROM tables WHERE conditions;

2. 使用視圖

SELECT * FROM view_name;

3. 修改視圖

ALTER VIEW view_name AS new_select_statement;

4. 刪除視圖

DROP VIEW IF EXISTS view_name;

七、存儲過程和函數

1. 創建存儲過程

DELIMITER //
CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

2. 調用存儲過程

CALL get_user(1);

3. 創建函數

DELIMITER //
CREATE FUNCTION count_users() RETURNS INT
BEGIN
    DECLARE user_count INT;
    SELECT COUNT(*) INTO user_count FROM users;
    RETURN user_count;
END //
DELIMITER ;

八、事務控制

1. 事務基本操作

START TRANSACTION;
-- SQL語句
COMMIT;  -- 或 ROLLBACK;

2. 設置自動提交

SET autocommit = 0;  -- 關閉自動提交
SET autocommit = 1;  -- 開啟自動提交

九、用戶權限管理

1. 創建用戶

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

2. 授權

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

3. 撤銷權限

REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost';

十、備份與恢復

1. 使用mysqldump備份

mysqldump -u username -p database_name > backup.sql

2. 恢復數據庫

mysql -u username -p database_name < backup.sql

總結

本文詳細介紹了MySQL數據庫和數據表的基本操作，包括： 1. 數據庫的創建、查看、修改和刪除 2. 數據表的結構操作和內容操作 3. 各種約束和索引的使用 4. 視圖、存儲過程和事務控制 5. 用戶權限管理和數據備份恢復

掌握這些基礎操作是進行MySQL數據庫開發和管理的前提，建議讀者在實際環境中多加練習，逐步深入理解各種操作的細節和適用場景。 “`

這篇文章共計約2100字，采用Markdown格式編寫，包含了MySQL數據庫和數據表操作的全面指南，從基礎操作到進階功能都有涉及，并提供了大量實用的SQL示例代碼。