1.增
INSERT INTO tb VALUES(v1,v2,v3...);
INSERT INTO tb (field1,field2...) VALUES(v1,v2...);
INSERT INTO tb SET field1=v1, field2=v2....;
INSERT INTO tb (field1,field2,field3...)
VALUES( v11,v12,v13...)
(v21,v22,v23...)
......;
2.删
2.1 删除部分数据
DELETE FROM tb WHERE condition_expr;
2.2 删除所有数据
DELETE FROM tb;
TRUNCATE [TABLE] tb;
两者区别是TRUNCATE删除更彻底,自增的字段在删除以后会重新从1开始,DELETE删除所有数据以后,自增字段依然会延续以前的值继续往后增加。
3.改
UPDATE tb SET field1=v1 [,field2=v2...] [WHERE condition_expr];
4.查(单表)
SELECT * FROM tb [WHERE condition_expr];
SELECT DISTINCT field1[,field2...] FROM tb [WHERE condition_expr]; ##去重复
SELECT field1,field2...FROM tb [WHERE condition_expr];
条件判断相关的关键词和符号:
= 等于
<>, != 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
IN 枚举 (例如: WHERE field1 IN (a,b,c))
BETWEEN ... AND ... (例如: WHERE age BETWEEN 2 AND 8;)
IS [NOT] NULL 空值判断 (例如: WHERE age IS [NOT] NULL;)
AND 与
OR 或
[NOT] LIKE 通配符 (%百分号通配符,任意长度字符串;_ 下划线通配符,单个字符)
查询中可能用到的聚合函数
COUNT() SELECT COUNT(*) FROM tb;
SUM() SELECT SUM(field) FROM tb;
AVG()
MAX()
MIN()
排序
SELECT field1,field2... FROM tb [WHERE cond_expr] ORDER BY field1 [ASC|DESC] [, field2[ACS| DESC];
分组
SELECT COUNT(*),field1 FROM tb GROUP BY field1;
SELECT SUM(field1),field2 FROM tb GROUP BY field2 HAVING SUM(field1) condition_expr; ## HAVING 关键字的与WHERE的区别就是前者可以配合聚合函数使用。
限制查询结果的数量
SELECT * FROM tb LIMIT [offset] num; ##输出结果从offset开始,总共输出num个。
为表取别名
SELECT * FROM tb [AS] alias WHERE alias.field condition_expr;
为属性取别名
SELECT field1 [AS] alias1 [, field2 [AS] alias2 ...] FROM tb ...;