整理了一些平时自己遗忘的点。
1、Mysql的substring(str,pos,len)截取与java中substring(beginIndex,endIndex)定义不同
改成
区别在与:
pos在mysql所有的substring()函数中都是以1为开始位,pos可以是负数,即从后倒数,也是从-1开始,到字符串结束,len就是指截取长度。
java的substring中beginIndex为索引,因此不可为负数,默认从0开始,到endIndex-1结束。
官方文档中都有例子:
http://tool.oschina.net/apidocs/apidoc?api=jdk_7u4
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring
mysql的截取字段函数比较多,比较方便,也可以查阅。
?
2、按数字大小对varchar字段进行排序:
基本思路是将字段类型转换为整型类型->
1、在order by的字段前添加减号 '-';
2、在排序的字段加上数字 '1'
例子:
select * from fi_audit where 1=1 order by -user_id desc
或者
select * from fi_audit where 1=1 order by (user_id + 1) desc
备注:user_id是varchar类型。
?
3、关于group by与having
http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html
group by:
在典型的SQL中一个带有group by子查询中,若查询的非聚集列(聚集列包括Count等聚集函数)并没有出现在group by子句之后,但却出现在我们所要查询的列中,那么则会出错。
例子:
SELECT o.custid, c.name, MAX(o.payment)FROM orders AS o, customers AS cWHERE o.custid = c.custidGROUP BY o.custid;
如这里的c.name,则会出错。可以修改将c.name加到group by子句中或者去掉c.name即可。
?
但是在mysql中允许这样的语句出现,
MySQL extends the standard SQL use of?GROUP BY
?so that the select list can refer to nonaggregated columns not named in the?GROUP BY
?clause.
当没有在group by子句中的非聚集列,他们各自对应的所有值对每个分组都是相同的时候,这是非常实用的。因为可以从任意分组选择任何值,除非它们(分组-ques)是一样的,那么选择的值是不确定的。而且上述情况并不会影响order by子句。仅仅是在值被选定之后,查询的结果排序,order by不影响服务选择每个分组中的值。
?
简单来说,当考虑到某些列的值是一样的时候,比较方便使用。也有局限性:
在查询的列中,非聚集列没有写到GROUP BY子句,其值是唯一的(只会查到一行结果),但GROUP BY省略的列值实际上并不唯一。?
可以通过group_concat()获取分组的组合连接值(加上distinct可以去重):
?
当然为了维护SQL的正统地位,也可以使用ONLY_FULL_GROUP_BY的模式不允许MYSQL的这种扩展修改。可以在my.cnf文件中添加:sql_mode = 'ONLY_FULL_GROUP_BY'
设置模式链接:http://blog.csdn.net/wyzxg/article/details/8787878
(这种模式设置对于mysql的having和order by的非常规SQL做法同样适用。)
?
group by的例子:
采用了上面的模式,那么下面的语句:
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)with no GROUP columns is illegal if there is no GROUP BY clause
会出错,因为没有group by,name值是不确定的。
?
另外:
传统的sql不允许在group by中使用表达式(解决办法是通过给表达式起别名的方式):
SELECT id, FLOOR(value/100) AS valFROM tbl_name
GROUP BY id, val;
而mysql中是可以的,不需要起别名。
?
having:
典型的SQL,也不允许不在group by中的列出现在having子句中(order by也是)。Mysql同样对这种情况做出了放行。在这种扩展中,假定没有分组的列有着相同的分组基准(暂时这样看),不然的话,会得到不确定的结果。
?
其他用法参考传统的SQL:
where与having:
where在分组前(位于group by之前)进行条件查询,但条件中不能包含聚合函数,可以使用where显示某一具体行;
having(聚合)则是筛选符合条件的分组,即分组后(位于group by之后)条件查询,条件中经常包含聚合函数,可以用having条件(也可多个)显示某些具体的分组。
?
自己的想法倾向于使用传统的SQL,这样便于自己以后工作跨数据库管理平台编写code(熟练使用者除外)。?
?
4、添加某一列以及备注
ALTER TABLE?表名 ADD?列名 varchar(12) NOT NULL comment‘备注’
?
5、连接查询时优化
对于经常使用的数据字典表,可优化为子查询(对应的code_type)-换表,提高效率:
LEFT JOIN (select code,code_name from da_dictionary where code_type = '045') AS dd1 ON ......
?
6、mysql中的日期比较:
>、<、=且DATE_FORMAT(po.create_time,'%Y-%m-%d %H:%i:%s')
<!--EndFragment-->?
?
?
?
?
?