您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 常用 SQL 操作語句大全：從建庫到多表查詢

編輯：MySQL 綜合教學

MySQL 常用 SQL 操作語句大全：從建庫到多表查詢

本文整理 MySQL 核心 DDL、DML、DQL 常用語句，涵蓋資料庫/表建立、資料增刪改查、條件篩選、別名應用、多表關聯等基礎到進階操作，所有語句經實測驗證，適合快速查閱與入門學習。

一、資料庫與表結構管理 (DDL)

-- 建立資料庫
CREATE DATABASE xuning_test;

-- 選擇資料庫
USE xuning_test;

-- 建立表
CREATE TABLE person (
    id INT NOT NULL,
    name VARCHAR(16) NOT NULL,
    sex VARCHAR(16) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(128) NOT NULL,
    remark VARCHAR(512) NOT NULL
);

二、資料操作 (DML)

1. 插入資料 (INSERT)

-- 多行插入 (推薦批次寫入)
INSERT INTO person VALUES
(1, 'name_1', 'men', 99, 'beijing', 'This is a frindsheep boy'),
(2, 'name_2', 'men', 88, 'shanghai', 'ok great'),
(3, 'name_3', 'man', 77, 'guangzhou', 'This is lickly'),
(4, 'name_4', 'men', 66, 'beijing', 'This is a frindsheep boy'),
(5, 'name_5', 'men', 55, 'beijing', 'you dont going to shool'),
(6, 'name_6', 'man', 44, 'beijing', 'This is a frindsheep boy'),
(7, 'name_7', 'men', 33, 'beijing', 'This is a frindsheep boy'),
(8, 'name_8', 'man', 22, 'beijing', ''),
(9, 'name_9', 'men', 11, 'beijing', 'This is a frindsheep boy');

2. 查詢驗證

SELECT * FROM person;

三、查詢操作 (DQL) 進階

1. 指定欄位查詢

SELECT name FROM person;

2. 條件篩選 (WHERE)

-- 等值查詢
SELECT name FROM person WHERE sex = 'men';

-- 比較運算符
SELECT name, address FROM person WHERE age > 50;

-- 範圍查詢
SELECT * FROM person WHERE age BETWEEN 20 AND 50;

-- 集合查詢
SELECT * FROM person WHERE address IN ('beijing', 'shanghai');

-- 模糊查詢
SELECT * FROM person WHERE name LIKE 'name_%';

-- 空值查詢
SELECT * FROM person WHERE remark IS NULL OR remark = '';

3. 表別名與多表關聯

-- 表別名簡化查詢
USE xuning_test;
SELECT c.name, c.sex FROM person AS c WHERE c.age > 40 AND c.address = 'beijing';

-- 跨資料庫表關聯 (需權限)
SELECT 
    xuning_test.person.name,
    xuning_test.person.id,
    xuning_test.person.age,
    xuning_test.person.address
FROM xuning_test.person, test1.test_xuning
WHERE xuning_test.person.id = test1.test_xuning.id;

四、進階查詢技巧補充

場景	語句範例
去重	SELECT DISTINCT address FROM person;
排序	SELECT * FROM person ORDER BY age DESC, name ASC;
分頁	SELECT * FROM person LIMIT 10 OFFSET 20; -- 第 3 頁，每頁 10 條
聚合統計	SELECT sex, COUNT(*), AVG(age), MAX(age), MIN(age) FROM person GROUP BY sex;
分組過濾	SELECT address, COUNT() FROM person GROUP BY address HAVING COUNT() > 2;
子查詢	SELECT * FROM person WHERE age > (SELECT AVG(age) FROM person);
內連接	SELECT p.name, o.order_no FROM person p INNER JOIN orders o ON p.id = o.user_id;
左連接	SELECT p.name, o.order_no FROM person p LEFT JOIN orders o ON p.id = o.user_id;
聯合查詢	SELECT name FROM person WHERE sex='men' UNION SELECT name FROM person WHERE age>50;

五、資料修改與刪除

-- 更新 (務必帶 WHERE)
UPDATE person SET salary = 5000 WHERE id = 1;
UPDATE person SET age = age + 1, address = 'shenzhen' WHERE name = 'name_1';

-- 刪除 (務必帶 WHERE)
DELETE FROM person WHERE id = 9;
-- 清空表 (保留結構，重置自增)
TRUNCATE TABLE person;

六、表結構修改 (DDL 進階)

-- 新增欄位
ALTER TABLE person ADD COLUMN email VARCHAR(64) AFTER name;

-- 修改欄位型別/屬性
ALTER TABLE person MODIFY COLUMN age SMALLINT UNSIGNED NOT NULL;

-- 重命名欄位
ALTER TABLE person CHANGE COLUMN sex gender VARCHAR(10) NOT NULL;

-- 刪除欄位
ALTER TABLE person DROP COLUMN remark;

-- 重命名表
RENAME TABLE person TO users;

-- 新增索引
ALTER TABLE person ADD INDEX idx_age (age);
ALTER TABLE person ADD UNIQUE INDEX uk_email (email);