萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 基礎操作入門全攻略:建庫建表、增刪改查、編碼、條件查詢

連線 MySQL

mysql -u username -p
# 輸入密碼後進入 mysql> 提示符

資料庫級操作

-- 建庫(指定編碼)
CREATE DATABASE ceshi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 刪庫
DROP DATABASE ceshi;

-- 切換庫
USE ceshi;

-- 查看建庫語句
SHOW CREATE DATABASE ceshi;

資料表級操作

建表範例

CREATE TABLE students (
    sid INT(10) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    course VARCHAR(255),
    score INT(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • AUTO_INCREMENT:自增主鍵。
  • ENGINE=InnoDB:支援事務、外鍵、行鎖。
  • CHARSET=utf8mb4:完整 Unicode 支援(含 Emoji)。

修改表結構

-- 修改表預設編碼
ALTER TABLE students DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

-- 修改欄位型別/屬性
ALTER TABLE students MODIFY COLUMN score DECIMAL(5,2);

-- 刪除欄位
ALTER TABLE students DROP COLUMN email;

-- 重命名表
RENAME TABLE students TO stu_info;

刪表

DROP TABLE students;

資料操作 (DML)

插入資料

-- 單條
INSERT INTO students (name, course, score) VALUES ('張三', '數學', 95);

-- 多條(高效批次)
INSERT INTO students (name, course, score) VALUES 
('李四', '英文', 88),
('王五', '物理', 92),
('趙六', '化學', 76);

-- 從另一表複製資料
INSERT INTO students (name, course, score) 
SELECT name, course, score FROM old_students WHERE score > 60;

查詢資料

-- 全欄位
SELECT * FROM students;

-- 指定欄位、別名、排序
SELECT sid AS id, name AS student_name, score 
FROM students 
ORDER BY score DESC;  -- DESC 降序,ASC 升序(預設)

-- 條件查詢
SELECT * FROM students WHERE score >= 90;
SELECT * FROM students WHERE course IN ('數學', '物理');
SELECT * FROM students WHERE name LIKE '張%';  -- % 任意長度,_ 單字元

-- 分組聚合
SELECT course, COUNT(*) AS cnt, AVG(score) AS avg_score
FROM students
GROUP BY course
HAVING AVG(score) > 80;  -- HAVING 過濾聚合後結果

-- 分頁
SELECT * FROM students ORDER BY score DESC LIMIT 10 OFFSET 20;  -- 第 3 頁,每頁 10 筆

更新資料

UPDATE students SET score = 100 WHERE name = '張三';
-- ⚠️ 沒有 WHERE 會更新全表!

刪除資料

DELETE FROM students WHERE score < 60;
-- ⚠️ 沒有 WHERE 會清空全表!
-- TRUNCATE TABLE students;  -- 更快,重置自增 ID,不可回滾

進階查詢技巧

UNION / UNION ALL

SELECT name FROM class_a WHERE score > 90
UNION        -- 去重
SELECT name FROM class_b WHERE score > 90;

SELECT name FROM class_a WHERE score > 90
UNION ALL    -- 不去重,效能更好
SELECT name FROM class_b WHERE score > 90;

子查詢

-- WHERE 子查詢
SELECT * FROM students WHERE course = (SELECT course FROM courses WHERE difficulty = 'hard');

-- FROM 子查詢(衍生表)
SELECT t.name, t.avg_score FROM (
    SELECT name, AVG(score) AS avg_score FROM students GROUP BY name
) t WHERE t.avg_score > 85;

常用函數速查

分類函數範例
字串CONCAT, SUBSTRING, REPLACE, TRIM, UPPER/LOWERCONCAT('Hello', ' ', 'World')
日期時間NOW(), CURDATE(), DATE_FORMAT(), DATEDIFF()DATE_FORMAT(NOW(), '%Y-%m-%d')
數學ROUND, CEIL, FLOOR, RAND, ABSROUND(3.14159, 2)
流程控制IF, CASE, COALESCE, NULLIFIF(score>=60, 'Pass', 'Fail')
聚合COUNT, SUM, AVG, MAX, MIN, GROUP_CONCATGROUP_CONCAT(name SEPARATOR ',')

新手必知十大避坑指南

  1. **永遠帶 WHERE 執行 UPDATE/DELETE**。
  2. **主鍵用 BIGINT AUTO_INCREMENT**,預留足夠空間。
  3. **字串欄位預設 NOT NULL DEFAULT ''**,避免 NULL 處理麻煩。
  4. **時間戳用 TIMESTAMP/DATETIME(3)**,毫秒級精度。
  5. **金額用 DECIMAL(10,2)**,嚴禁 FLOAT/DOUBLE。
  6. **索引建在 WHERE/JOIN/ORDER BY 欄位**,但不濫建。
  7. **EXPLAIN 分析慢 SQL**,關注 type、key、rows。
  8. **備份先測試恢復**,未測試的備份等於沒備份。
  9. **嚴格 SQL_MODE**:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION。
  10. **監控慢查詢、連線數、緩存命中率**,建立基線告警。
copyright © 萬盛學電腦網 all rights reserved