1.分页:
table_name:数据表名;
column_name:数据表头名;
使用LIMIT和OFFSET关键字进行分页查询。例如,从第10条记录开始,每页显示5条记录:
SELECT * FROM table_name
ORDER BY column_name
LIMIT 5 OFFSET 10;
2.统计:
table_name:数据表名;
column_name:数据表头名;
使用**COUNT()**函数进行计数。例如,计算表中的记录数:
SELECT COUNT(*) FROM table_name;
// 统计表中id的个数,并作为total为表头
SELECT COUNT(id) as total FROM table_name;
3.排序:
table_name:数据表名;
column_name:数据表头名;
使用ORDER BY子句对结果进行排序。
例如,按照某个字段升序排列:
SELECT * FROM table_name
ORDER BY column_name ASC;
或者按照某个字段降序排列:
SELECT * FROM table_name
ORDER BY column_name DESC;
node.js中实例:
在Node.js中,你可以使用一个数据库驱动(如mysql、pg等)来执行这些SQL语句。以下是一个使用mysql驱动的示例:
const mysql = require('mysql');
//连接数据库
const connection = mysql.createConnection({
host: 'localhost',
user: 'your_username',
password: 'your_password',
database: 'your_database'
});
connection.connect();
// 分页查询
const pageSize = 5;
const offset = 10;
const orderByColumn = 'column_name';
const query = `SELECT * FROM table_name ORDER BY ${orderByColumn} LIMIT ${pageSize} OFFSET ${offset}`;
connection.query(query, (error, results, fields) => {
if (error) throw error;
console.log('Results:', results);
});
// 统计记录数
const countQuery = 'SELECT COUNT(*) as total FROM table_name';
connection.query(countQuery, (error, results, fields) => {
if (error) throw error;
console.log('Total records:', results[0].total);
});
// 按某个字段升序排列
const orderByAscQuery = `SELECT * FROM table_name ORDER BY ${orderByColumn} ASC`;
connection.query(orderByAscQuery, (error, results, fields) => {
if (error) throw error;
console.log('Results ordered by column ascending:', results);
});
// 按某个字段降序排列
const orderByDescQuery = `SELECT * FROM table_name ORDER BY ${orderByColumn} DESC`;
connection.query(orderByDescQuery, (error, results, fields) => {
if (error) throw error;
console.log('Results ordered by column descending:', results);
});
connection.end();