修改

UPDATE t_student SET password = ?,name = ?,department = ?,address = ? WHERE account = ?

统计宠物表中的宠物总数

SELECT COUNT(*) FROM pet;

统计宠物表中的每个主人拥有的宠物数

SELECT owner, COUNT(*) FROM pet GROUP BY owner;

多表查询的结果分页

SELECT * FROM t_topic LEFT OUTER JOIN t_topic_comment ON t_topic.id = t_topic_comment.id WHERE t_topic.user_id=4
UNION
SELECT * FROM t_topic RIGHT OUTER JOIN t_topic_comment ON t_topic.id = t_topic_comment.id WHERE t_topic_comment.user_id=3

左连接查询得到的结果并排序

SELECT * FROM t_topic tp LEFT OUTER JOIN t_topic_comment tc ON tp.id= tc.topic_id ORDER BY tp.post_time DESC,tc.post_time DESC

mysql按某年某月查询

SELECT COUNT(*) FROM t_photo WHERE DATE_FORMAT(createtime, '%Y-%m ')= '2015-08'

mysql按某年查询

SELECT COUNT(*) FROM t_photo WHERE year(createtime)='2015'

mysql查询某段时间

SELECT * FROM t_photo WHERE createtime BETWEEN '2015-07-01' AND '2015-09-01'

mysql查询多个id

SELECT * FROM t_model WHERE id IN (SELECT modelid FROM t_userfunction WHERE userid='0' GROUP BY modelid) ORDER BY sortno

左连接查询得到的结果添加查询条件

SELECT * FROM t_post po LEFT JOIN t_family fm ON po.`familyid`=fm.`id` WHERE fm.`name` LIKE '%豆%' AND fm.`createtime`='2015-10-12'

左连接三张表查询的结果

SELECT * FROM t_photo AS ph LEFT JOIN t_userinfo AS ui ON ph.userid = ui.userid LEFT JOIN t_family AS uf ON ph.familyid=uf.id WHERE uf.`name` LIKE '%1%'

MySQL查询表内重复记录

select * from hsm.t_family
where userid in (select userid from hsm.t_family group by userid having count(userid) > 1)