1)、mysql按两个条件,时间排序:
select *,count(distinct msid),count(distinct mdate) from matterinfo group by mdate,msid order by UNIX_TIMESTAMP(mdate) desc
2)、为了提高效率,把两个字段加上联合索引:
create index mt on matterinfo(msid,mdate)
3)、提示错误:
PHP提示:#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = 1
LIMIT 0, 30' at line 1
Java提示:com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
以上是在不同的环境提示的错误。这种错误都是由于SQL语句的某个地方写错了,比如字段、表明、关键词写错了。
4)、在原有数据基础上增加:update tablename set column=column+1 where whe = x
5)、批量修改字段局部内容(类似替换):UPDATE tb_name SET tb_column= REPLACE ( tb_column,'http://sz.zxyida.com/', 'http://www.zxyida.com/sz/')
tb_name(表名)、 tb_column(字段名)、http://sz.zxyida.com/(有待替换的内容)、http://www.zxyida.com/sz/(替换成的内容)
6)、mysql查询时字段的截取:select *,left(rgnid, 9) as rgns from sys_region where rgnid > 999999999(可以使用left(),right(),substr()函数)
7)、mysql查询数据库表的数量:SELECT count(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='数据库名';