SQL 我不知道的Tips

1、FOUND_ROWS()

题目：

SELECT * FROM `table` LIMIT 20,10;

在上边sql中使用什么选项可以使 SELECT FOUND_ROWS()忽略LIMIT子句，返回总数？

*答案* : SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS * FROM `table`  LIMIT 20,10;

SELECT FOUND_ROWS();

分析：

老实讲，在看到这个题目之前，我不知道found_rows()的用法。

那么既然我们接触到了，就来简单了解一下，found_rows()究竟是什么。

首先，我们看一下手册中对这个函数的描述：

For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again.

推荐阅读>>

13.14 Information Functions

SQL语句中 SQL_CALC_FOUND_ROWS 的使用与否

MYSQL的FOUND_ROWS()函数

mysql SELECT FOUND_ROWS()与COUNT(*)用法区别

2.mysql null 默认值

题目：

SELECT a.id,b.name FROM tab1 AS a LEFT JOIN tab2 AS b ON(a.id=b.id) WHERE a.id>10;

以上sql的返回结果中,name列也许会出现null的情况，那么在name字段中使用什么函数将出现null改为一个默认值？

 

*答案*

ifnull

用法如下：

SELECT a.id,ifnull(b.name,'默认值') FROM tab1 AS a LEFT JOIN tab2 AS b ON(a.id=b.id) WHERE a.id>10;