整理一份 MySQL 常用 SQL 語句大全，從基礎操作到進階查詢，都涵蓋。方便日常開發和學習參考。

---

1. 數據庫操作

-- 查看所有數據庫
SHOW DATABASES;-- 創建數據庫
CREATE DATABASE db_name;-- 刪除數據庫
DROP DATABASE db_name;-- 使用數據庫
USE db_name;-- 查看當前數據庫
SELECT DATABASE();

---

2. 數據表操作

-- 查看所有表
SHOW TABLES;-- 查看表結構
DESCRIBE table_name;
-- 或
SHOW COLUMNS FROM table_name;-- 創建表
CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,age INT,create_time DATETIME DEFAULT NOW()
);-- 刪除表
DROP TABLE table_name;-- 修改表
ALTER TABLE user ADD email VARCHAR(100);
ALTER TABLE user DROP COLUMN age;
ALTER TABLE user MODIFY COLUMN name VARCHAR(100) NOT NULL;
ALTER TABLE user RENAME TO new_user;

---

3. 數據操作（增刪改查）

-- 插入數據
INSERT INTO user (name, age) VALUES ('Alice', 25);
INSERT INTO user SET name='Bob', age=30;-- 更新數據
UPDATE user SET age=26 WHERE name='Alice';-- 刪除數據
DELETE FROM user WHERE age < 20;-- 查詢數據
SELECT * FROM user;
SELECT name, age FROM user WHERE age > 20;
SELECT name AS username, age FROM user;

---

4. 條件查詢與排序

-- 條件查詢
SELECT * FROM user WHERE age BETWEEN 20 AND 30;
SELECT * FROM user WHERE name LIKE 'A%';
SELECT * FROM user WHERE age IN (20, 25, 30);-- 排序
SELECT * FROM user ORDER BY age DESC, name ASC;

---

5. 聚合函數與分組

-- 聚合函數
SELECT COUNT(*) FROM user;
SELECT AVG(age) FROM user;
SELECT SUM(age) FROM user;
SELECT MAX(age), MIN(age) FROM user;-- 分組
SELECT age, COUNT(*) FROM user GROUP BY age;
SELECT age, AVG(salary) FROM user GROUP BY age HAVING AVG(salary) > 3000;

---

6. 多表查詢（JOIN）

-- 內連接
SELECT u.name, d.department_name
FROM user u
JOIN department d ON u.department_id = d.id;-- 左連接
SELECT u.name, d.department_name
FROM user u
LEFT JOIN department d ON u.department_id = d.id;-- 右連接
SELECT u.name, d.department_name
FROM user u
RIGHT JOIN department d ON u.department_id = d.id;-- 自連接
SELECT a.name AS employee, b.name AS manager
FROM user a
JOIN user b ON a.manager_id = b.id;

---

7. 子查詢

-- 單行子查詢
SELECT name FROM user WHERE age = (SELECT MAX(age) FROM user);-- 多行子查詢
SELECT name FROM user WHERE age IN (SELECT age FROM user WHERE age > 25);-- EXISTS 判斷
SELECT name FROM user u WHERE EXISTS (SELECT 1 FROM department d WHERE d.id = u.department_id);

---

8. 排序與分頁

-- 排序
SELECT * FROM user ORDER BY age DESC;-- 分頁
SELECT * FROM user LIMIT 0, 10;  -- 從第1條開始取10條
SELECT * FROM user LIMIT 10 OFFSET 10; -- 跳過前10條取后10條

---

9. 索引操作

-- 創建索引
CREATE INDEX idx_name ON user(name);-- 創建唯一索引
CREATE UNIQUE INDEX idx_email ON user(email);-- 刪除索引
DROP INDEX idx_name ON user;

---

10. 事務操作

-- 開啟事務
START TRANSACTION;-- 提交事務
COMMIT;-- 回滾事務
ROLLBACK;-- 自動提交設置
SET autocommit=0; -- 關閉自動提交

---

11. 數據庫函數（常用）

-- 字符串函數
SELECT CONCAT(name,'_',age) FROM user;
SELECT LENGTH(name) FROM user;
SELECT SUBSTRING(name,1,3) FROM user;
SELECT REPLACE(name,'A','B') FROM user;-- 日期函數
SELECT NOW();  -- 當前時間
SELECT CURDATE();  -- 當前日期
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');-- 數學函數
SELECT ROUND(12.345,2);
SELECT CEIL(12.3);
SELECT FLOOR(12.9);
SELECT POW(2,3);

---

12. 權限管理

-- 創建用戶
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';-- 授權
GRANT ALL PRIVILEGES ON db_name.* TO 'test'@'localhost';-- 查看權限
SHOW GRANTS FOR 'test'@'localhost';-- 刪除用戶
DROP USER 'test'@'localhost';

---