mysql普通语句

需求：查询最大值

select MAX(time) from table;

需求：一条mysql语句查询出男女的人数

select count(case when mz ='汉族' then mz end) as h,count(case when mz ='维吾尔族' then mz end) as w from tb_r_jbxx where addressid ='652901101204';

需求：mysql中将多行数据合并成一行数据

SELECT am.activeId,GROUP_CONCAT(m.modelName SEPARATOR ',') modelName
 FROM activemodel am 
 JOIN model m 
 ON am.modelId=m.modelId
 WHERE m.valid=1
 GROUP BY am.activeId

需求：union all

(SELECT aid,title FROM article) 
UNION ALL
(SELECT bid,title FROM blog)
LIMIT 2

需求：time和datetime

DateTime  YYYY-MM-DD HH:mm:ss
Time 		  HH:mm:ss

需求：获取某年某月某日的数据

select * from `user` where date_format(birthday,'%Y-%m-%d')='2017-07-07';

需求：获取当月多少天

DAYOFMONTH(LAST_DAY('20${year}-10-01'))

需求：数据排序 asc、desc

指定按照薪水由大到小排序（降序关键字desc）
指定按照薪水由小到大排序（升序关键字 asc） 

需求：查询数据=>时间范围的条件

在这里插入代码片

需求：合并两个查询记录

select column_name(s) from table_name1     |     select column_name(s) from table_name1

        UNION                              |     UNION ALL

select column_name(s) from table_name2;    |     select column_name(s) from table_name2;
 1、select语句列的数量、顺序必须相同，列的类型也要求相似；

 2、UNION和UNION ALL结果集中的列名总是等于第一个select语句中的列名称相同。
 union 去除重复行，union all 不去除

需求：插入多个记录

insert into persons 

(id_p, lastname , firstName, city )

values

(200,'haha' , 'deng' , 'shenzhen'),

(201,'haha2' , 'deng' , 'GD'),

(202,'haha3' , 'deng' , 'Beijing');

删除

DELETE FROM table_name [WHERE Clause]
delete FROM  maintain_archive where id = #{id}

添加

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

编辑

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
UPDATE table_name SET field1=new-value1, field2=new-value2
where id = #{id}

查询

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]