Generally, I've understood this to mean that Y is the default, and if I hit something other than a Y or an N (such as hitting the spacebar, Enter, or even Q), it will use the Y option.
You are correct that this implies that Y is the default, on pressing the Enter key. An input other then Y, N or Enter would generally produce an error or (sometimes) be equivalent to the non-default/non-destructive action.
Is this display & behavior actually standard, or at least common? Or is it more typical to wait for a Y or N explicitly?
The practice is at least common, or understood. A new user may ponder the difference, but the use is common enough that they should quickly come to understand the metaphor.
It is not "standard", however. I am unfamiliar with any command-line environment with a helper function that automatically pushes a confirmation line to the screen. It is the responsibility of each application to produce their own confirmation message and, as a result, their own default key logic.
Looking in the GNU Standard for Command Line Interfaces, and IEEE UNIX Specification brings up no information on a command line prompt "standard".
rm -i foo.tar.bz2 will simply ask
remove foo.tar.bz2?, with any key other than Y canceling the operation.
I've hunted for other examples, especially ones that use a default, but have not come up with any so far. They are surprisingly difficult to remember when you don't hit the command line very often anymore.
Would the reversed form: Are you sure you want to ______? (y/N) be as understandable that the default is to not perform the action, and that Y must be explicitly hit to do so? And would (N/y) be clearer or more confusing?
The capitalization of the N in this case would indeed imply "No" is the default, on pressing Enter. While "(N/y)" would be recognized, the common order (again, no "standards") is to provide the options as "y/n" - no matter the default.
Taking notes from the GUI side, here are a few HIGs that have sections discussing confirm/deny button order:
Also, Jakob Nielsen wrote an article on the subject: OK-Cancel or Cancel-OK?. Regardless of which option is default, the orders are still the same.
Regarding the order, Nielsen says:
If you're designing a Web-based application, the decision is harder, but you should probably go with the platform preferred by most of your users.
... replace "web-based application" with "command-line interface" and you have as good a recommendation as any.
Regarding default, he says:
Make the most commonly selected button the default and highlight it (except if its action is particularly dangerous; in those cases, you want users to explicitly select the button rather than accidentally activating it by hitting Enter).
... again, just replace the verbiage discussing "buttons" with a "y/n" prompt.