How to query the number of records in oracle

Oracle, as a relational database management system, is very commonly used in our daily work. When using Oracle to query data, you often need to know how many records there are in the query results. This article will introduce how to query the number of records in Oracle.

There are several ways to query the number of records in Oracle:

1. Use the COUNT function

The COUNT function is a statistical function in Oracle. Can be used to count the number of values ​​or rows in a column that are not NULL. The number of records in the query result can be obtained through the COUNT function.

Syntax:

SELECT COUNT(*) FROM 表名 WHERE 条件;

Among them, COUNT(*) means counting the number of all records, FROM followed by the table name, WHERE followed by the query conditions.

Example:

Suppose we have a table named student and need to query the number of records of male students in the table. You can use the following code:

SELECT COUNT(*) FROM student WHERE sex='男';

2. Using the ROWNUM method

ROWNUM is a virtual column in a table in Oracle, which can be used to obtain the number of record rows in the query result set.

Syntax:

SELECT ROWNUM, 字段1, 字段2,… FROM 表名 WHERE 条件;

Among them, ROWNUM represents the line number. When querying, Oracle first queries the records that meet the conditions, then assigns a row number to the records, sorts them in ascending order starting from 1, and finally outputs the number of rows of records that meet the conditions.

Example:

SELECT ROWNUM, name, sex FROM student WHERE age>20;

3. Use COUNT(*) OVER() method

COUNT(*) OVER() is an aggregate function in Oracle , can be used to count the number of rows in the query result set.

Syntax:

SELECT 字段1,字段2,..., COUNT(*) OVER() FROM 表名 WHERE 条件;

Among them, COUNT(*) OVER() means summing the number of records in the entire result set.

Example:

To query the name, gender, age of all students in the student table, and the total number of rows in the table, you can use the following code:

SELECT name, sex,age, COUNT(*) OVER() AS 总记录数 FROM student;

Through the above three Method to easily query the number of records in Oracle. In actual use, you can choose an appropriate method to query based on the actual situation.

The above is the detailed content of How to query the number of records in oracle. For more information, please follow other related articles on the PHP Chinese website!