萬盛學電腦網

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

MySQL 核心 SQL 語句大全:建庫建表、增刪改查、備份恢復一網打盡

數據庫級操作

-- 創建數據庫 (指定字符集)
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 內部狀態
copyright © 萬盛學電腦網 all rights reserved