平时开发mysql自己遗忘的点

整理了一些平时自己遗忘的点。

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 c
WHERE o.custid = c.custid
GROUP 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 val
FROM 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 ......