萬盛學電腦網

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

SQL 經典語句大全:從基礎增刪改查到高級查詢、分頁、跨庫操作

一、基礎 DDL/DML

-- 建庫/刪庫
CREATE DATABASE db_name;
DROP DATABASE db_name;

-- 建表/刪表
CREATE TABLE tab (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
DROP TABLE tab;

-- 根據舊表建新表
CREATE TABLE tab_new LIKE tab_old;           -- 僅結構
CREATE TABLE tab_new AS SELECT * FROM tab_old; -- 結構+數據

-- 增刪改查
INSERT INTO tab (id, name) VALUES (1, 'a');
DELETE FROM tab WHERE id = 1;
UPDATE tab SET name = 'b' WHERE id = 1;
SELECT * FROM tab WHERE name LIKE '%a%';

二、進階查詢運算符

-- UNION:合併去重
SELECT * FROM t1 UNION SELECT * FROM t2;
-- UNION ALL:合併不去重
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

-- EXCEPT:差集(t1 有、t2 無)
SELECT * FROM t1 EXCEPT SELECT * FROM t2;

-- INTERSECT:交集
SELECT * FROM t1 INTERSECT SELECT * FROM t2;

三、外連接詳解

-- LEFT JOIN:左表全保留
SELECT a.*, b.name FROM a LEFT JOIN b ON a.id = b.a_id;

-- RIGHT JOIN:右表全保留
SELECT a.*, b.name FROM a RIGHT JOIN b ON a.id = b.a_id;

-- FULL JOIN:MySQL 不直接支持,用 UNION 模擬
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

四、分組聚合

SELECT dept, COUNT(*), AVG(salary), MAX(salary), MIN(salary)
FROM emp
GROUP BY dept
HAVING COUNT(*) > 5;

注意:SELECT 列表只能包含分組列或聚合函數;TEXT/NTEXT/IMAGE 不能分組。

五、子查詢與關聯查詢

-- IN 子查詢
SELECT * FROM a WHERE id IN (SELECT a_id FROM b WHERE type=1);

-- 關聯子查詢:每組最大值
SELECT a.* FROM tab a
WHERE a.val = (SELECT MAX(val) FROM tab b WHERE b.grp = a.grp);

六、分頁查詢(SQL Server 風格)

-- 通用模板:每頁 10 條,第 3 頁
DECLARE @start INT = 21, @end INT = 30;
SELECT * FROM (
    SELECT TOP (@end - @start + 1) * FROM (
        SELECT TOP (@end) * FROM tab ORDER BY id DESC
    ) t ORDER BY id ASC
) t2 ORDER BY id DESC;

MySQL 直接用 LIMIT 20, 10(偏移 20,取 10)。

七、實用技巧

  • 前 N 行:SELECT TOP 10 * FROM tab (SQL Server) / LIMIT 10 (MySQL)
  • 刪除主表中副表無對應行:DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.id=t2.id)
  • 備份/還原:BACKUP DATABASE ... TO DISK / RESTORE DATABASE ... FROM DISK
  • 重命名庫:sp_renamedb 'old', 'new'
copyright © 萬盛學電腦網 all rights reserved