數據庫級操作
-- 創建數據庫 (指定字符集)
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 切換數據庫
USE `mydb`;
-- 刪除數據庫
DROP DATABASE `mydb`;
表結構操作 (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 COMMENT '創建時間',
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
-- 修改表
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` DROP INDEX `uk_username`;
-- 刪除表
DROP TABLE `users`;
TRUNCATE TABLE `users`; -- 保留結構,清空數據並重置自增ID
-- 查看表結構
SHOW CREATE TABLE `users`;
DESC `users`;
SHOW COLUMNS FROM `users`;
數據操作 (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 `status` = 0 WHERE `created_at` < '2022-01-01';
-- 刪除
DELETE FROM `users` WHERE `id` = 1;
DELETE FROM `users` WHERE `status` = 0; -- 慎用無 WHERE 刪除全表
權限與用戶管理
-- 創建用戶
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';
-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app_user'@'%';
GRANT ALL PRIVILEGES ON `mydb`.* TO 'admin'@'%';
-- 刷新權限
FLUSH PRIVILEGES;
-- 查看權限
SHOW GRANTS FOR 'app_user'@'%';
-- 回收權限
REVOKE DELETE ON `mydb`.* FROM 'app_user'@'%';
-- 修改密碼
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass456!';
-- 刪除用戶
DROP USER 'app_user'@'%';
備份與恢復
邏輯備份
# 全庫備份
mysqldump -u root -p --single-transaction --routines --triggers --events mydb > mydb_$(date +%F).sql
# 單表備份
mysqldump -u root -p mydb users > users.sql
# 只備份結構
mysqldump -u root -p -d mydb > mydb_structure.sql
# 壓縮備份
mysqldump -u root -p mydb | gzip > mydb_$(date +%F).sql.gz
恢復
# 進入 MySQL 創建空庫
mysql -u root -p -e "CREATE DATABASE mydb CHARSET utf8mb4;"
# 導入
mysql -u root -p mydb < mydb_2024-01-15.sql
# 或在 MySQL 客戶端
SOURCE /path/to/mydb_2024-01-15.sql;
事務控制
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;
實用查詢技巧
SELECT VERSION(); 版本號
SELECT NOW(); 當前時間
SELECT DATABASE(); 當前庫
SHOW PROCESSLIST; 當前連接/查詢
KILL 12345; 終止會話
SHOW ENGINE INNODB STATUS\G InnoDB 內部狀態