### 插入 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