https://sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key says (emphasis added):

If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

and also

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.

...

The exception mentioned above is that if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key.

These excerpts seem like they may technically conflict — what is the specified behavior for CREATE TABLE t(rowid INTEGER PRIMARY KEY)? Is this rowid column an alias for the real rowid? If so, the first excerpt may be slightly too bold to say that it "cannot be used to retrieve the integer rowid value".

In particular, I'm interested in sqlite3_blob_open(), where I need access to the true rowid, but I would also like to explicitly specify it in my schema.

If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

That means that if there is a column explicitly named rowid, oid, or _rowid_ then usages of those names refer to the declared column and not the rowid of the table (assuming that it is a rowid table).

So if you declare a table thusly:

create table x
(
   myrowid integer primary key,
   rowid integer,
   oid integer,
   _rowid_ integer
);

then references to rowid, oid, or _rowid_ refer to the columns by those names and references to myrowid are references to the table rowid.

If the table is declared thusly:

create table x
(
   myrowid integer primary key,
   rowid integer,
   oid integer
);

then references to rowid and oid refer to the columns by those names and references to myrowid or _rowid_ refer to the rowid of the table (assuming that the table is a rowid table).

Mutatis mutandis other combinations.

That is to say more clearly that references to the magical names rowid, oid, and _rowid_ refer to the column in the table by that name, or if and only if that column name does not exist in the table, then to the rowid of the table if the table is a rowid table, otherwise raise an error that the name does not exist.

This does not preclude giving an explicit name to the rowid of the table after which the rowid may be referred to by the name so explicitly given to the rowid, or such magical names rowid, oid, or _rowid_ as are not already being used for another purpose.

Furthermore, if no explicit name is given to the rowid of a rowid table then the rowid is subject to change at any time for any reason whatsoever without notice to the user and may not be used as either the source or destination of a foreign key constraint.

Yes.

It is an explicit column called rowid that gives an explicit name to the table's rowid. The magic names oid and _rowid_ will also refer to the same column since those names are not explicitly used in the table declaration and the table is a rowid table.

The rowid will also be stable (will be preserved and will not change except by your changing it like any other explicit column) because it is explicitly named and thus can be used as the source/target of a referential constraint.

Worth adding to Keith's excellent answers, the behaviour of things like VACUUM.

If you have no reference to rowid when you create the table, SQLite concludes that rowid is for internal use only, and commands like VACUUM might change values in it.

On the other hand, if your CREATE TABLE statement supplies an alias (as used in this thread) for rowid, or explicitly declares a column called rowid, then VACUUM will not change values in that column. Because you might have stored them elsewhere for future use.