MySQL基礎(chǔ)增刪查改操作詳解

更新時間：2026年01月29日 14:28:00 作者：君愛學(xué)習(xí)

本文詳細(xì)介紹了MySQL數(shù)據(jù)庫的基礎(chǔ)增刪查改（CRUD）操作,包括數(shù)據(jù)庫和數(shù)據(jù)表的創(chuàng)建、刪除和修改,以及數(shù)據(jù)的插入、查詢、更新和刪除,結(jié)合實例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

一、數(shù)據(jù)庫操作

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE db_name;
CREATE DATABASE IF NOT EXISTS db_name;  -- 不存在時才創(chuàng)建
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

刪除數(shù)據(jù)庫

DROP DATABASE db_name;
DROP DATABASE IF EXISTS db_name;  -- 存在時才刪除

選擇/切換數(shù)據(jù)庫

USE db_name;

二、數(shù)據(jù)表操作

創(chuàng)建表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,        -- 主鍵，自增
    username VARCHAR(50) NOT NULL UNIQUE,     -- 非空，唯一
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    age INT DEFAULT 18,                       -- 默認(rèn)值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

刪除表

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

修改表結(jié)構(gòu)

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 修改列
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
-- 重命名列
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20);
-- 刪除列
ALTER TABLE users DROP COLUMN mobile;
-- 添加索引
CREATE INDEX idx_username ON users(username);
-- 刪除索引
DROP INDEX idx_username ON users;

三、數(shù)據(jù)操作（CRUD）

插入數(shù)據(jù)（Create）

-- 插入單條數(shù)據(jù)（指定列）
INSERT INTO users (username, password, email, age) 
VALUES ('john_doe', '123456', 'john@example.com', 25);
-- 插入單條數(shù)據(jù)（所有列）
INSERT INTO users VALUES (NULL, 'jane_doe', '654321', 'jane@example.com', 30, NOW(), NOW());
-- 插入多條數(shù)據(jù)
INSERT INTO users (username, password, email) 
VALUES 
    ('alice', 'pass123', 'alice@example.com'),
    ('bob', 'pass456', 'bob@example.com'),
    ('charlie', 'pass789', 'charlie@example.com');

查詢數(shù)據(jù)（Read）

-- 查詢所有列
SELECT * FROM users;
-- 查詢指定列
SELECT id, username, email FROM users;
-- 條件查詢
SELECT * FROM users WHERE age > 20;
SELECT * FROM users WHERE username = 'john_doe';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE email LIKE '%@example.com';
-- 邏輯運算符
SELECT * FROM users WHERE age > 20 AND email LIKE '%@example.com';
SELECT * FROM users WHERE age < 18 OR age > 60;
-- 排序
SELECT * FROM users ORDER BY age DESC;        -- 降序
SELECT * FROM users ORDER BY created_at ASC;  -- 升序
-- 限制返回數(shù)量
SELECT * FROM users LIMIT 10;                 -- 前10條
SELECT * FROM users LIMIT 5, 10;              -- 跳過5條，取10條
-- 去重
SELECT DISTINCT age FROM users;
-- 分組統(tǒng)計
SELECT age, COUNT(*) as count FROM users GROUP BY age;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
-- 聚合函數(shù)
SELECT COUNT(*) as total_users FROM users;
SELECT AVG(age) as avg_age FROM users;
SELECT MAX(age) as max_age FROM users;
SELECT MIN(age) as min_age FROM users;
SELECT SUM(age) as total_age FROM users;

更新數(shù)據(jù)（Update）

-- 更新單個字段
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
-- 更新多個字段
UPDATE users SET 
    email = 'updated@example.com',
    age = 26,
    updated_at = NOW()
WHERE username = 'john_doe';
-- 基于現(xiàn)有值更新
UPDATE users SET age = age + 1 WHERE age < 30;
-- 批量更新
UPDATE users SET status = 'inactive' WHERE last_login < '2024-01-01';

刪除數(shù)據(jù)（Delete）

-- 刪除指定記錄
DELETE FROM users WHERE id = 1;
-- 刪除多條記錄
DELETE FROM users WHERE age > 100;
-- 清空表（刪除所有記錄）
DELETE FROM users;
TRUNCATE TABLE users;  -- 更快，不能回滾

四、高級查詢技巧

連接查詢

-- 內(nèi)連接
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左連接
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 右連接
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

子查詢

-- WHERE子句中的子查詢
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);
-- FROM子句中的子查詢
SELECT avg_age_table.age_group, COUNT(*)
FROM (SELECT 
    CASE 
        WHEN age < 20 THEN '少年'
        WHEN age BETWEEN 20 AND 40 THEN '青年'
        ELSE '中老年'
    END as age_group
    FROM users) as avg_age_table
GROUP BY age_group;

聯(lián)合查詢

SELECT username, email FROM active_users
UNION
SELECT username, email FROM inactive_users;

五、事務(wù)處理

-- 開始事務(wù)
START TRANSACTION;
-- 執(zhí)行操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 提交或回滾
COMMIT;  -- 確認(rèn)更改
-- 或
ROLLBACK;  -- 撤銷更改

六、實用技巧

備份數(shù)據(jù)

-- 創(chuàng)建表備份
CREATE TABLE users_backup AS SELECT * FROM users;
-- 插入備份數(shù)據(jù)
INSERT INTO users_backup SELECT * FROM users WHERE created_at > '2024-01-01';

批量操作

-- 批量插入（提高性能）
INSERT INTO users (username, email) VALUES 
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

使用CASE表達(dá)式

SELECT 
    username,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 60 THEN '成年'
        ELSE '老年'
    END as age_group
FROM users;

注意事項