SQL 什么是 rowID & rowNum (ROWID vs ROWNUM)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34282583/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is rowID & rowNum (ROWID vs ROWNUM)
提问by inityk
I'd like to know difference between rowIDand rowNUM
我想知道rowID和之间的区别rowNUM
And how to see both of these in our table.
以及如何在我们的表格中看到这两者。
when I execute this:
当我执行此操作时：
SELECT * FROM emp WHERE rownum=1
It returns one query but when I do the same for rowid it says
它返回一个查询，但是当我对 rowid 执行相同操作时，它说
inconsistent datatypes: expected ROWID got NUMBER
不一致的数据类型：预期的 ROWID 得到 NUMBER
And even in some of the tables, rownum returns null . Why so?
即使在某些表中， rownum 也返回 null 。为什么这样？
Please clarify this: rowid vs rownum?(Demo query)
请澄清这一点：rowid vs rownum？（演示查询）
Thank you
谢谢
EDIT: Require to use alias to display ROWIDand ROWNUM(as they're pseudocolumn)
like:
编辑：需要使用别名来显示ROWID和ROWNUM（因为它们是伪列）如：
SELECT rownum r1, rowid r2 FROM emp
SELECT rownum r1, rowid r2 FROM emp
回答by blckbird
Both, rownum and rowed are pseudo columns.
rownum 和 rowed 都是伪列。
Rowid
罗伊德
For each row in the database, the ROWID pseudocolumn returns the address of the row.
对于数据库中的每一行，ROWID 伪列返回该行的地址。
An example query would be:
一个示例查询是：
SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;
More info on rowid here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm
有关 rowid 的更多信息，请访问：https: //docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm
Rownum
行数
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
对于查询返回的每一行，ROWNUM 伪列返回一个数字，指示 Oracle 从表或连接行集中选择行的顺序。选定的第一行的 ROWNUM 为 1，第二行的 ROWNUM 为 2，依此类推。
You can limit the amount of results with rownum like this:
您可以使用 rownum 限制结果数量，如下所示：
SELECT * FROM employees WHERE ROWNUM < 10;
More info on rownum here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
有关 rownum 的更多信息：https: //docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
Difference
区别
The actual difference between rowidand rownumis, that rowid is a permanent unique identifier for that row. However, the rownum is temporary. If you change your query, the rownum number will refer to another row, the rowid won't.
rowid和之间的实际区别在于rownum，rowid 是该行的永久唯一标识符。但是，rownum 是临时的。如果您更改查询，rownum 编号将引用另一行，而 rowid 则不会。
So the ROWNUM is a consecutive number which applicable for a specific SQL statement only. In contrary the ROWID, which is a unique ID for a row.
所以 ROWNUM 是一个连续的数字，只适用于特定的 SQL 语句。与 ROWID 相反，它是一行的唯一 ID。
回答by Ruelos Joel
Rownum (numeric)= Generated Sequence Number of your output.
Rowid (hexadecimal)= Generated automatically at the time of insertion of row.
Rownum（数字）= 生成的输出序列号。
Rowid（十六进制）= 在插入行时自动生成。
SELECT rowid,rownum fROM EMP
ROWID ROWNUM
----- ----------------------
AAAR4AAAFAAGzg7AAA, 1
AAAR4AAAFAAGzg7AAB, 2
AAAR4AAAFAAGzg7AAC, 3
AAAR4AAAFAAGzg7AAD, 4
AAAR4AAAFAAGzg7AAE, 5
回答by Shiv Ratan Kumar
- Rowid gives the address of rows or records. Rownum gives a count of records
- Rowid is permanently stored in the database. Rownum is not stored in the database permanently
- Rowid is automatically assigned with every inserted into a table. Rownum is a dynamic value automatically retrieved along with select statement output.
- It is only for display purpose.
- Rowid 给出行或记录的地址。Rownum 给出记录数
- Rowid 永久存储在数据库中。Rownum 不会永久存储在数据库中
- 每次插入表时都会自动分配 Rowid。Rownum 是一个与 select 语句输出一起自动检索的动态值。
- 它仅用于显示目的。
回答by Hanuman
row id shows the unique identificationfor row
rownumshows the unique default series of numbers.
row id 显示唯一identification的行
rownum显示唯一的默认数字系列。
select * from emp
where rownum<=5; (it will execute correctly and gives output first 5 rows in your table )
select * from emp
where rowid<=5; (wrong because rowid helpful to identify the unique value)
