mysql limit if_mysql function和limit的使用

1、mysql自定义函数(貌似不能返回多个值，可以选择返回表)：

删除函数：

DROP FUNCTION if exists function_name;

定义函数：

CREATE FUNCTION function_name(N INT) RETURNS INT

BEGIN

RETURN (

# Write your MySQL query statement below.

);

END

声明变量：

CREATE FUNCTION function_name(N INT) RETURNS INT

BEGIN

# 声明变量并赋默认值

DECLARE m INT default N-1;

RETURN (

# Write your MySQL query statement below.

);

END;

数值操作：

CREATE FUNCTION function_name(N INT) RETURNS INT

BEGIN

# 数值操作

SET N = N - 1;

RETURN (

# Write your MySQL query statement below.

);

END;

2、limit的使用：

# limit带一个参数：最多的记录数

SELECT * FROM table_name ORDER BY value LIMIT 3;

# limit 参数：最多的记录数；offset记录数：偏移的记录数

SELECT * FROM table_name ORDER BY value LIMIT 3 OFFSET 1;

# limit 带两个参数：第一个参数：偏移的记录数；第二个参数：最多的记录数

SELECT * FROM table_name ORDER BY value LIMIT 1,3;

# limit n 等价于 limit 0, n

# 分页查询：

# limitNum 为偏移量，(当前页-1)*每一页记录数

limitNum = (pageNum-1)*singlePageNum;

SELECT * FROM table_name ORDER BY value LIMIT limitNum, singlePageNum

# 数据量大适用的分页查询

# limitNum 为偏移量，(当前页-1)*每一页记录数

SELECT * FROM table_name WHERE value >

(SELECT value FROM table_name ORDER BY value LIMIT limitNum,1) LIMIT singlePageNum;

SELECT * FROM table_name AS t1 JOIN

(SELECT * FROM table_name ORDER BY value DESC LIMIT limitNum, 1) AS t2

WHERE t1.value <= t2.value ORDER BY t1.value DESC LIMIT singlePageNum;