萬盛學電腦網

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

MySQL SQL 語法完整參考手冊:DDL、DML 與進階查詢

一、資料定義語言 (DDL)

1. 建立資料表

CREATE TABLE table_name (
  column1 datatype [NOT NULL] [PRIMARY KEY],
  column2 datatype [NOT NULL],
  ...
);

datatype 常用類型:INT、BIGINT、VARCHAR(n)、TEXT、DATE、DATETIME、DECIMAL(p,s)、ENUM、JSON

2. 修改資料表

-- 新增欄位
ALTER TABLE table_name ADD COLUMN column_name datatype;

-- 設定主鍵
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 移除主鍵
ALTER TABLE table_name DROP PRIMARY KEY;

3. 建立索引

CREATE INDEX index_name ON table_name (column_name);

4. 刪除物件

DROP TABLE table_name;
DROP INDEX index_name ON table_name;

二、資料操作語言 (DML)

1. 新增資料 (INSERT)

-- 指定欄位
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- 依表定義順序(不指定欄位)
INSERT INTO table_name VALUES (val1, val2, ...);

-- 從子查詢插入
INSERT INTO table_name (col1, col2)
SELECT colx, coly FROM another_table;

2. 查詢資料 (SELECT)

基本查詢

SELECT col1, col2 FROM table_name;
SELECT * FROM table_name WHERE col1 = 'xxx' AND col2 > 100;
SELECT col1, col2 FROM table_name ORDER BY col2 DESC;

群組與聚合

SELECT COUNT(*) FROM table_name WHERE col = 'xxx';
SELECT SUM(col1), AVG(col2), MAX(col3), MIN(col4) FROM table_name;
SELECT col1, AVG(col2) FROM table_name GROUP BY col1 HAVING AVG(col2) > 50;

多表連接

-- 內連接
SELECT * FROM t1 JOIN t2 ON t1.col = t2.col;

-- 左連接
SELECT * FROM t1 LEFT JOIN t2 ON t1.col = t2.col;

-- 交叉連接
SELECT * FROM t1, t2 WHERE t1.col = t2.col;

子查詢與集合運算

-- EXISTS 相關子查詢
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE condition);

-- IN 子查詢
SELECT * FROM t1 WHERE col1 IN (SELECT col1 FROM t2 WHERE condition);

-- BETWEEN 範圍
SELECT * FROM t1 WHERE col1 BETWEEN 10 AND 20;

-- LIKE 模糊比對
SELECT * FROM t1 WHERE col1 LIKE 'prefix%';

3. 更新資料 (UPDATE)

UPDATE table_name SET col1 = 'new_val' WHERE condition;
/* 無 WHERE 則更新全表,極度危險! */

4. 刪除資料 (DELETE)

DELETE FROM table_name WHERE condition;
/* 無 WHERE 則清空全表,建議改用 TRUNCATE TABLE */

三、日期比較跨資料庫差異

資料庫語法範例
AccessWHERE mydate > #2000-01-01#
OracleWHERE mydate > TO_DATE('2000-01-01','YYYY-MM-DD')
MySQLWHERE mydate > '2000-01-01' 或 CAST('2000-01-01' AS DATE)
SQL ServerWHERE mydate > '2000-01-01' 或 CAST('2000-01-01' AS DATE)
PostgreSQLWHERE mydate > DATE '2000-01-01'

四、Delphi 程式碼中嵌入 SQL 範例

theDate := '2000-01-01';
Query1.SQL.Add('SELECT * FROM abc WHERE mydate > CAST(' + QuotedStr(theDate) + ' AS DATE)');

總結

掌握 DDL/DML 核心語法、索引策略、子查詢模式與跨資料庫日期處理差異,是資料庫開發與維運的基本功。

copyright © 萬盛學電腦網 all rights reserved