萬盛學電腦網

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

MySQL 命令行完整操作手冊:連接、建庫建表、增刪改查、備份、權限管理

一、連接與基礎操作


# 連接本地 (無密碼初始)
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;
copyright © 萬盛學電腦網 all rights reserved