mysql语句 增删改查

插入

1. insert into table (c1,c2) values(c1,c2),(c1,c2);

2. 将查询结果插入到表中 insert into table1(c1,c2) select c1,c2 from ...

更新 update table_name set c1='' ,c2='' where ...

删除 delete from table_name where...

查询 select c1  from table1 where ...

between a AND b

LIKE %通配符，匹配任意长度的字符 _通配符 匹配任意一个字符

IS NULL 为空 NOT IS NULL 不为空

DISTINCT 消除重复字段   SELECT DISTINCT id ,c2 from ...

ORDER BY  c1_name ,c2_name  先按c1_name排序，再按c2_name排序 . DESC降序排序，默认升序ASC

分组查询：MAX() MIN() COUNT() SUM() AVG()

                SELECT c1_name , COUNT(*) AS countnum from table_name GROUP BY c2_name;

                

WITH ROLLUP   跟在GROUP BY 后面 ，在查询结果后添加一条记录，计算总值

LIMIT startindex,count  从startindex 开始取count条记录

连接查询 ：

        内连接：select t1.id,t1.name,t2.value from t1 ,t2 where t1.id=t2.id;

         等同于   select t1.id,t1.name,t2.value from t1 INNER JOIN t2 ON t1.id=t2.id;

        外连接：

子查询：ANY SOME 任一个,ALL 所有

                EXISTS 子查询是否返回行 有为真 否则为假