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

MySQL 核心知識圖譜：從基礎操作到進階特性全覽

編輯：MySQL 綜合教學

MySQL 核心知識圖譜：從基礎操作到進階特性全覽

本文系統整理 MySQL 核心知識點，涵蓋連線管理、DDL/DML 操作、視圖、索引、存儲過程、事務、用戶權限、SELECT 運算與邏輯操作，適合作為開發者的速查手冊。

一、連線與資訊查看

-- 本地登入
mysql -u root -p123456

-- 遠端登入
mysql -h 192.168.0.111 -u admin -padmin

-- 查看服務狀態
STATUS;

-- 列出所有資料庫
SHOW DATABASES;

-- 切換資料庫
USE db_name;

-- 列出所有表
SHOW TABLES;

-- 查看表結構
DESC table_name;

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

二、建庫建表與結構變更

-- 建庫（指定字符集）
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb DEFAULT CHARSET=utf8;
USE mydb;

-- 建表
DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest (
    Id INT(11) NOT NULL AUTO_INCREMENT,
    type_name VARCHAR(10) DEFAULT NULL,
    color_name VARCHAR(10) DEFAULT NULL,
    PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 修改表結構
ALTER TABLE mytest CHANGE color_name description VARCHAR(50);  -- 修改欄位名與型別
ALTER TABLE mytest ADD extra_name VARCHAR(20) NOT NULL DEFAULT '豆豆'; -- 新增欄位
ALTER TABLE mytest DROP extra_name;                              -- 刪除欄位
ALTER TABLE mytest RENAME TO test01;                             -- 修改表名
ALTER TABLE test01 MODIFY COLUMN type_name CHAR(10);             -- 修改欄位型別

-- 查看以 t 開頭的欄位
DESC test01 't%';

三、字符集與編碼管理

-- 查看編碼設定
SHOW VARIABLES LIKE 'character%';

-- 臨時修改連線編碼
SET NAMES 'utf8';

關鍵變數：character_set_client、character_set_connection、character_set_results、character_set_database、character_set_server。

四、DML 核心操作 (CRUD)

-- 插入
INSERT INTO test01(type_name, description) VALUES('綜藝', '最近更新');

-- 更新
UPDATE test01 SET type_name='電影' WHERE type_name='綜藝';

-- 查詢
SELECT * FROM test01 WHERE Id=1;
SELECT COUNT(*) FROM test01;          -- 統計總行數（含 NULL）
SELECT COUNT(1) FROM test01;          -- 效能最佳
SELECT COUNT(column) FROM test01;     -- 不含 NULL

-- 刪除
DELETE FROM test01 WHERE Id=1 AND type_name='電影';
DELETE FROM test01;                   -- 全表刪除（保留自增計數器）

-- 清空表（重置自增計數器）
TRUNCATE TABLE user;

五、資料匯入匯出

-- 表級匯出
SELECT * FROM test01 INTO OUTFILE 'f:\\test.txt';

-- 表級匯入
LOAD DATA INFILE 'f:\\test.txt' INTO TABLE test01;

-- 資料庫級匯出
mysqldump -uroot -p [-d|-t] dbname [table] > e:\mybk.sql
-- -d 僅結構，-t 僅資料，預設全量

-- 資料庫級匯入
mysql -uroot -p new_dbname < e:\mybk.sql

六、視圖

虛擬表，不儲存資料，引用時即時派生。優點：簡化複雜查詢、權限控制（隱藏敏感欄位）。

-- 建立
CREATE VIEW avgPrice AS SELECT cat_id, AVG(shop_price) FROM goods GROUP BY cat_id;
CREATE VIEW baseGoods AS SELECT goods_id, goods_name FROM goods;  -- 隱藏價格

-- 查詢
SELECT * FROM avgPrice;

-- 修改
ALTER VIEW avgPrice AS SELECT cat_id, goods_name, AVG(shop_price) FROM goods GROUP BY cat_id;

-- 刪除
DROP VIEW avgPrice;

七、索引

參考文章 174085、174086 詳細說明。核心類型：普通、唯一、主鍵、全文、組合、空間索引。

八、存儲過程

-- 刪除舊過程
DROP PROCEDURE IF EXISTS pro_test1;

-- 建立（批量插入 10000 筆）
DELIMITER //
CREATE PROCEDURE pro_test1()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i < 10000 DO
        INSERT INTO test1(name, value) VALUES(CONCAT('name', i), CONCAT('value', i));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 呼叫
CALL pro_test1();

-- 使用用戶變數
CREATE PROCEDURE pro_bigworld() SELECT CONCAT(@greeting, ' world');
SET @greeting='Hi';
CALL pro_bigworld();  -- 輸出 "Hi world"

-- IF-THEN-ELSE 範例
DELIMITER //
CREATE PROCEDURE pro_test2(IN para INT)
BEGIN
    DECLARE var1 INT;
    SET var1 = para + 1;
    IF var1 = 0 THEN
        INSERT INTO test1(name, value) VALUES('var1name', 'var1value0');
    END IF;
    IF para = 0 THEN
        UPDATE test1 SET value=CONCAT(value, '0');
    ELSE
        UPDATE test1 SET value=CONCAT(value, '1');
    END IF;
END //
DELIMITER ;

九、事務 (ACID)

-- 方式一：顯式控制
BEGIN;
-- SQL 語句...
COMMIT;      -- 提交
ROLLBACK;    -- 回滾

-- 方式二：關閉自動提交
SELECT @@autocommit;  -- 1=開啟
SET autocommit=0;     -- 關閉，後續所有 SQL 納入同一事務
-- 執行 SQL...
COMMIT;               -- 提交並開啟新事務
SET autocommit=1;     -- 恢復自動提交

注意： SET autocommit=0 影響整個會話，建議使用 BEGIN/COMMIT 精確控制單一事務。

十、用戶與權限管理

-- 建立用戶
CREATE USER 'yy' IDENTIFIED BY '123';

-- 授權
GRANT ALL PRIVILEGES ON dbname.* TO 'yy'@'localhost' IDENTIFIED BY '123';
GRANT SELECT ON dbname.* TO 'yy';

-- 回收權限
REVOKE SELECT ON dbname.* FROM 'yy';

-- 刪除用戶
DROP USER 'yy';

十一、SELECT 支援的運算操作

函數/運算子	範例	說明
CASE WHEN	`SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END;`	條件選擇
IF	`SELECT IF(1<2, 'yes', 'no');`	三元判斷
CHAR_LENGTH	`SELECT CHAR_LENGTH('qwert');`	字符長度
CONCAT	`SELECT CONCAT('my', 'sql');`	字串連接
LOCATE	`SELECT LOCATE('bar', 'foobarhat');`	子串位置（0=未找到）
SUBSTRING	`SELECT SUBSTRING('yichun university', 5, 6);`	擷取子串
TRIM	`SELECT TRIM(' bar ');`	去除首尾空格
REPEAT	`SELECT REPEAT('mysql', 3);`	重複字串
REVERSE	`SELECT REVERSE('abcd');`	反轉字串
ROUND	`SELECT ROUND(2.5);`	四捨五入
RAND	`SELECT RAND()*10;`	0-10 隨機數
HEX	`SELECT HEX('cat');`	ASCII 十六進位
TRUE/FALSE	`SELECT TRUE, FALSE;`	輸出 1, 0
日期時間	`NOW()`, `CURDATE()`, `CURTIME()`, `CURRENT_TIMESTAMP()`	當前日期/時間
系統資訊	`DATABASE()`, `USER()`, `VERSION()`	當前庫/用戶/版本
LEFT/RIGHT	`SELECT LEFT(type_name,2) FROM test;`	取左/右 N 字元

十二、邏輯運算操作

SELECT 2>1 && 3>2;      -- 邏輯與 (1)
SELECT 2>1 AND 3>2;    -- 邏輯與 (1)
SELECT 1>2 || 3>2;     -- 邏輯或 (1)
SELECT 1>2 OR 3>2;     -- 邏輯或 (1)
SELECT !(1>2);         -- 邏輯非 (1)
SELECT NOT(1>2);       -- 邏輯非 (1)
SELECT 15 ^ 2;         -- 異或 (13)  1111 ^ 0010 = 1101
SELECT 15 XOR 2;       -- 異或 (13)

十三、忘記 root 密碼重置

關閉 MySQL 服務
啟動跳過權限模式：mysqld --skip-grant-tables
新開終端連線：mysql

修改密碼：

USE mysql;
UPDATE user SET authentication_string=PASSWORD('newPassword') WHERE user='root';
FLUSH PRIVILEGES;

重啟 MySQL 正常模式

十四、修改密碼兩種方式

-- 方法一：UPDATE（需 FLUSH PRIVILEGES）
USE mysql;
UPDATE user SET authentication_string=PASSWORD('newPassword') WHERE user='name';
FLUSH PRIVILEGES;

-- 方法二：SET PASSWORD
SET PASSWORD FOR 'name'@'host' = PASSWORD('new password');