I vaguely remember building a code lock kit back in the mid 90's. It used a ASIC to drive a piezo buzzer for the alarm, at 4kHz. It also gave a brief "beep" (less than 100ms I think) of the same frequency when a key was pressed. It wasn't particularly loud but wasn't unpleasant.
As previous replies stated, you can get "buzzers" that contain driving electonics to create the tone, so you just apply power and it makes a single tone as long as power is applied. If you have an arduino you could get plain piezo sounders (these don't have any electronics, just a piezo speaker element) and use Arduinos built in libraries to test tones of various lengths and frequency to hear which one you prefer.
Arduino isn't exactly efficient in its use of hardware, but its great for very quickly (and cheaply) testing ideas like this.
Note almost all piezo buzzers/sounds/elements are round, the square/rectangular buzzers tend to be electromagnetic and work at a lower frequency. There are round electromagnetic buzzers/sounds, but these work in a similar frequency range to the piezos 2-4kHz.