### 插入 INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); ### 更新 UPDATE table_name SET field1=new-value1, field2=new-value2 ### 刪除 DELETE FROM table_name [WHERE Clause] ### 查詢 select * from table ### 查詢某列 SELECT Title FROM table ### where SELECT * FROM table WHERE id = 1 ### 條件查詢分多種 ### 一、比較運算和邏輯運算符符 SELECT * FROM table WHERE age >= 20 && age <=30 SELECT * FROM table WHERE age >= 20 AND age <=30 SELECT * FROM table WHERE age BETWEEN 20 AND 30 SELECT * FROM table WHERE age = 22 OR age = 18 OR age = 25; SELECT * FROM table WHERE age IN (22,18,25) ; ### 二、范圍運算符 SELECT * FROM table WHERE age between (22,60) ### 三、列表運算符 SELECT * FROM table WHERE age in (22,60) ### 四、模糊匹配符 配合like使用的占位符： _：單個任意字符 %：多個任意字符 SELECT * FROM table WHERE NAME LIKE '馬%';-- 查詢姓名第二個字是化的人 SELECT*FROM table WHERE NAME LIKE '_化%';-- 查詢姓名是3個字的人 SELECT * FROM table WHERE NAME LIKE '___';-- 查詢姓名中包含馬的人 SELECT * FROM table WHERE NAME LIKE '%馬%'; ### 五、空值判斷符 SELECT * FROM table WHERE english = NULL; -- 不對的。null值不能使用= (!=) 判斷 SELECT * FROM table WHERE english IS NULL; ### 排序 單字段排序 SELECT uid, name, FROM staffs ORDER BY id ASC 多字段排序 SELECT * FROM table ORDER BY age DESC, salary ASC ### 分頁 SELECT * FROM table LIMIT 5 OFFSET 10 可以簡寫為 SELECT * FROM staffs LIMIT 5, 10; ### group by 分組查詢 SELECT COUNT(Age), id FROM table GROUP BY age HAVING COUNT(Age) > 1 ### 聚合查詢 count統計 SELECT COUNT(id) FROM table 計算平均值 SELECT AVG(score) FROM table sum 求和 SELECT SUM(score) FROM table 獲取最大值max() select max(goods_price) from goods 最小值 min() select min(goods_price) from goods ### join 連接 * INNER Join – 如果連接條件為真，則合并來自不同表的行。 * FULL Join – 當左表或右表記錄匹配時返回所有行。 * LEFT Join – 檢索左表中的所有行和右表中的匹配記錄。 ### 內連接 SELECT table1.Title, table2.id FROM table1 INNER JOIN table2 ON table1.id = table1.id ### 全連接 SELECT * FROM table1 FULL JOIN table2 ON table1.id = table1.id ### 左連接 SELECT table1.Name, table2.Age FROM table1 LEFT JOIN table2 ON table1.id = table1.id ### 去重查詢 select distinct height from table where age = 18