萬盛學電腦網

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

MySQL 入門教程:從基礎查詢到資料庫操作完整速查表

一、資料查詢 (SELECT)

基礎查詢語法

-- 全欄查詢
SELECT * FROM table_name;

-- 多表查詢
SELECT * FROM table1, table2, ...;

-- 指定欄位
SELECT field1, field2 FROM table1, table2, ...;

-- 條件過濾
SELECT ... FROM ... WHERE condition;

-- 分組聚合
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;

-- 排序
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;

-- 限制筆數
SELECT ... FROM ... WHERE condition LIMIT 10;

-- 去重
SELECT DISTINCT field1 FROM ...;
SELECT DISTINCT field1, field2 FROM ...;

-- 連接查詢
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...;

常用條件運算子

運算子說明範例
=等於field = 'value'
<> / !=不等於field <> 'value'
LIKE模糊匹配field LIKE 'val_%'
IS NULL為空field IS NULL
IS NOT NULL不為空field IS NOT NULL
IN在列表中field IN (v1, v2)
NOT IN不在列表中field NOT IN (v1, v2)
AND邏輯與cond1 AND cond2
OR邏輯或cond1 OR cond2
BETWEEN範圍之間field BETWEEN 1 AND 10

二、資料操作 (DML)

新增資料 (INSERT)

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...);

刪除資料 (DELETE / TRUNCATE)

-- 刪除所有資料(保留結構,可回滾,觸發器生效)
DELETE FROM table1;

-- 刪除所有資料(重置自增ID,不可回滾,速度快)
TRUNCATE TABLE table1;

-- 條件刪除
DELETE FROM table1 WHERE condition;

-- 多表關聯刪除
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition;

更新資料 (UPDATE)

UPDATE table1 SET field1 = new_value1 WHERE condition;

-- 多表關聯更新
UPDATE table1, table2 
SET table1.field1 = new_value1, table2.field2 = new_value2 
WHERE table1.id1 = table2.id2 AND condition;

三、結構資訊查看

SHOW DATABASES;                    -- 所有資料庫
SHOW TABLES;                       -- 當前資料庫所有表
SHOW FIELDS FROM table;            -- 表結構 (等同 DESCRIBE table)
SHOW CREATE TABLE table;           -- 建表 SQL
SHOW PROCESSLIST;                  -- 當前執行緒
KILL process_id;                   -- 終止進程

四、資料庫與資料表管理 (DDL)

資料庫操作

CREATE DATABASE dbname;
CREATE DATABASE dbname CHARACTER SET utf8mb4;
DROP DATABASE dbname;
ALTER DATABASE dbname CHARACTER SET utf8mb4;

資料表操作

-- 建表
CREATE TABLE table (field1 type1, field2 type2, ...);
CREATE TABLE table (field1 type1, ..., INDEX (field));
CREATE TABLE table (field1 type1, ..., PRIMARY KEY (field1));
CREATE TABLE table (..., PRIMARY KEY (field1, field2));  -- 複合主鍵

-- 外鍵
CREATE TABLE table1 (
    fk_field1 type1, ...,
    FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
);

-- 條件建表/臨時表
CREATE TABLE IF NOT EXISTS table (...);
CREATE TEMPORARY TABLE table (...);

-- 刪表
DROP TABLE table;
DROP TABLE IF EXISTS table;
DROP TABLE table1, table2, ...;

-- 修改欄位
ALTER TABLE table MODIFY field1 type1;
ALTER TABLE table MODIFY field1 type1 NOT NULL;
ALTER TABLE table CHANGE old_name new_name type1;
ALTER TABLE table CHANGE old_name new_name type1 NOT NULL;

-- 預設值
ALTER TABLE table ALTER field1 SET DEFAULT 'value';
ALTER TABLE table ALTER field1 DROP DEFAULT;

-- 增刪欄位
ALTER TABLE table ADD new_field type1;
ALTER TABLE table ADD new_field type1 FIRST;
ALTER TABLE table ADD new_field type1 AFTER another_field;
ALTER TABLE table DROP field1;

-- 索引
ALTER TABLE table ADD INDEX (field);

-- 調整欄位順序
ALTER TABLE table MODIFY field1 type1 FIRST;
ALTER TABLE table MODIFY field1 type1 AFTER another_field;

-- 重命名表
ALTER TABLE old_name RENAME new_name;

五、權限管理

-- 授權
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON db.* TO 'user'@'localhost' IDENTIFIED BY 'password';

-- 回收權限
REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'host';           -- 單一權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';     -- 所有權限

-- 修改密碼
SET PASSWORD = PASSWORD('new_pass');
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');

-- 刪除使用者
DROP USER 'user'@'host';

六、常用資料類型速查

類型大小/範圍說明
TINYINT1 Byte (-128~127 / 0~255)小整數、布林
SMALLINT2 Bytes (±65K)短整數
MEDIUMINT3 Bytes (±16M)中整數
INT4 Bytes (±2B)標準整數
BIGINT8 Bytes (±9×10¹⁸)長整數
FLOAT4 Bytes單精度浮點
DOUBLE8 Bytes雙精度浮點
DECIMAL(M,D)M+2 Bytes精確十進位(金額)
DATE3 Bytes1000-01-01 ~ 9999-12-31
DATETIME8 Bytes1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP4 Bytes1970-01-01 ~ 2038-01-19,時區感知
VARCHAR(M)L+1/2 Bytes變長字串 (M≤65535)
TEXTL+2 Bytes長文本 (64KB)
BLOBL+2 Bytes二進制長資料 (64KB)
ENUM1-2 Bytes單選枚舉 (≤65535值)
SET1-8 Bytes多選集合 (≤64成員)

七、遺忘 Root 密碼重置

# 1. 停止服務
/etc/init.d/mysql stop

# 2. 跳過授權表啟動
mysqld_safe --skip-grant-tables &

# 3. 另開終端機登入
mysql
UPDATE mysql.user SET authentication_string = PASSWORD('newpass') WHERE User = 'root';
FLUSH PRIVILEGES;

# 4. 結束 mysqld_safe (Ctrl+C)
# 5. 正常啟動
/etc/init.d/mysql start

八、損壞表修復

# 檢查所有資料庫
mysqlcheck --all-databases

# 快速檢查
mysqlcheck --all-databases --fast

# 自動修復
mysqlcheck --all-databases --auto-repair

九、常用管理工具

  • 命令列:mysql, mysqldump, mysqladmin, mysqlcheck, myisamchk
  • 圖形介面:phpMyAdmin, MySQL Workbench, DBeaver, DataGrip, Navicat
  • 監控:SHOW PROCESSLIST, SHOW ENGINE INNODB STATUS, sys schema, Prometheus + Grafana
copyright © 萬盛學電腦網 all rights reserved