一、連接與基礎操作
# 連接本地 (無密碼初始)
mysql -u root -p
# 連接遠程
mysql -h 110.110.110.110 -u root -p'password'
# 退出
EXIT; -- 或 QUIT、Ctrl+D
二、用戶與權限管理
-- 修改密碼 (5.7+)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';
-- 8.0+ 推薦 caching_sha2_password (更安全)
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'NewPass123!';
-- 創建用戶 + 授權 (一條龍)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
-- 僅本地訪問 (更安全)
CREATE USER 'app_local'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app_local'@'localhost';
-- 查看權限
SHOW GRANTS FOR 'app_user'@'%';
-- 回收權限
REVOKE DELETE ON `mydb`.* FROM 'app_user'@'%';
-- 刪除用戶
DROP USER 'app_user'@'%';
三、數據庫操作
-- 創建 (指定字符集)
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 顯示所有庫
SHOW DATABASES;
-- 刪除庫
DROP DATABASE `mydb`;
DROP DATABASE IF EXISTS `mydb`; -- 不存在不報錯
-- 切換庫
USE `mydb`;
SELECT DATABASE(); -- 確認當前庫
四、表結構操作 (DDL)
-- 創建表 (完整範例)
CREATE TABLE `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`username` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
`email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '郵箱',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
-- 查看結構
DESC `users`;
SHOW CREATE TABLE `users`;
-- 修改表
ALTER TABLE `users` ADD COLUMN `phone` VARCHAR(20) DEFAULT '' COMMENT '手機';
ALTER TABLE `users` MODIFY COLUMN `email` VARCHAR(120) NOT NULL DEFAULT '';
ALTER TABLE `users` CHANGE COLUMN `status` `account_status` TINYINT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE `users` DROP COLUMN `phone`;
-- 索引管理
ALTER TABLE `users` ADD INDEX `idx_email` (`email`);
ALTER TABLE `users` ADD UNIQUE INDEX `uk_phone` (`phone`);
ALTER TABLE `users` DROP INDEX `idx_email`;
-- 刪除表
DROP TABLE `users`;
TRUNCATE TABLE `users`; -- 保留結構,清空數據、重置自增
-- 重命名表
RENAME TABLE `users` TO `members`;
ALTER TABLE `users` RENAME TO `members`;
五、數據操作 (DML)
-- 插入單行
INSERT INTO `users` (`username`, `email`) VALUES ('zhangsan', 'zhangsan@example.com');
-- 批量插入 (高效)
INSERT INTO `users` (`username`, `email`) VALUES
('lisi', 'lisi@example.com'),
('wangwu', 'wangwu@example.com');
-- 從查詢結果插入
INSERT INTO `users_archive` (`username`, `email`, `created_at`)
SELECT `username`, `email`, `created_at` FROM `users` WHERE `created_at` < '2023-01-01';
-- 查詢
SELECT `id`, `username`, `email` FROM `users` WHERE `status` = 1 ORDER BY `created_at` DESC LIMIT 10;
SELECT * FROM `users` WHERE `id` = 1;
-- 更新
UPDATE `users` SET `email` = 'new@example.com' WHERE `id` = 1;
UPDATE `users` SET `account_status` = 0 WHERE `created_at` < '2022-01-01';
-- 刪除
DELETE FROM `users` WHERE `id` = 1;
DELETE FROM `users` WHERE `account_status` = 0; -- 慎用無 WHERE
-- 事務
START TRANSACTION;
INSERT INTO `users` (`username`, `email`) VALUES ('tx_test', 'tx@test.com');
UPDATE `accounts` SET `balance` = `balance` - 100 WHERE `user_id` = 1;
COMMIT; -- 或 ROLLBACK;
六、備份與恢復
mysqldump 邏輯備份
# 全庫備份 (生產標準參數)
mysqldump -u root -p --single-transaction --routines --triggers --events --master-data=2 --hex-blob mydb > mydb_$(date +%F).sql
# 單表備份
mysqldump -u root -p mydb users > users.sql
# 只導結構
mysqldump -u root -p -d --add-drop-table mydb > mydb_structure.sql
# 壓縮備份
mysqldump -u root -p mydb | gzip > mydb_$(date +%F).sql.gz
恢復操作
# 1. 創建空庫
mysql -u root -p -e "CREATE DATABASE mydb CHARSET utf8mb4;"
# 2. 導入 (解壓管道直導)
gunzip -c mydb_2024-01-15.sql.gz | mysql -u root -p mydb
# 3. 客戶端導入
mysql -u root -p mydb
SOURCE /backup/mydb_2024-01-15.sql;
七、字符集亂碼終極解決
-- 1. 配置文件 my.cnf/my.ini
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
-- 2. 連接字符串指定 (JDBC)
jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai
-- 3. PHP
header("Content-Type: text/html; charset=utf-8");
mysqli_set_charset($link, 'utf8mb4');
-- 4. 會話級臨時設置
SET NAMES utf8mb4;
-- 等價於:
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;
八、實用系統查詢
SELECT VERSION(); -- 版本
SELECT NOW(); -- 當前時間
SELECT CURRENT_DATE(); -- 當前日期
SELECT USER(); -- 當前用戶
SHOW PROCESSLIST; -- 當前會話
SHOW ENGINE INNODB STATUS\G -- InnoDB 內部狀態
SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- 連接數
SHOW VARIABLES LIKE 'max_connections'; -- 最大連接數
九、建庫建表完整腳本範例 (可直接執行)
DROP DATABASE IF EXISTS `school`;
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `school`;
CREATE TABLE `teacher` (
`id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '教師ID',
`name` CHAR(10) NOT NULL DEFAULT '' COMMENT '姓名',
`address` VARCHAR(50) DEFAULT '深圳' COMMENT '地址',
`hire_date` DATE COMMENT '入職日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師表';
INSERT INTO `teacher` (`name`, `address`, `hire_date`) VALUES
('Allen', '大連一中', '1976-10-10'),
('Jack', '大連二中', '1975-12-23');
-- 執行方式:
-- mysql -u root -p < school.sql
-- 或客戶端: SOURCE /path/to/school.sql;