I recently spent some time replacing all of the alerts and ringtones on my iPhone to be sound FX and music from my favorite NES games, and the quality of my life improved dramatically. 

I wanted to do this for two reasons, the first being to break the Pavlovian stress reaction I have when my phone makes a sound ("Ugh. Someone's calling me." "Ugh. What NOW??"). 

The second is that I actually know it's MY phone alerting in a room full of iPhone users. ("Is that me or you?")

The process of making your own ringtones and alerts is easy, but it's unfortunately more labor intensive than it should be. (Thanks, Apple.) I've found that it's the least painful when you grab all of the sound FX you want to convert first, and then batch process them in one sitting. 

Start by finding the sounds/music you want to use

Spend some time hunting down sounds you want to turn into ringtones/alerts. Ideally you want the downloads to be in mp3 format. They can be in other audio formats, but it can mean an additional conversion step that's a pain in the ass. 

Songs or game music can be fun for ringtones, but I tend to prefer the SFX. Mario grabbing a coin SFX for a text message alert, Mario dying for a ringtone, etc. Your mileage may vary, of course, but full songs need to be trimmed to 30 seconds or less, and I'm very lazy.

Some great sources for movie/TV/video game sound FX and music:

• Sound FX Center - good selection of core SFX, not just music
• The Sounds Resource (clunky UX, but huge selection, unfortunately sometimes in .wav format)
• Super Luigi Brothers (huge collection, Mario Brothers only)
• Mr. P's Castlevania Realm (Castlevania only)
• GameThemeSongs.Com and TelevisionTunes.Com
• MarioMayhem 
• Legend of Zelda Sound FX 

There are tons more resources out there (and if you can't find the one you're looking for, reply to this post and I'll try to help you find it).

Once you've downloaded the sounds/music you want, you can trim if necessary them in iTunes, Garageband, Quicktime, or any other audio editor.

Convert the MP3 to a M4R (MPeg-4 Ringtone)

You have a bunch of different options here, so choose whichever works for you. 

Via iTunes

Start iTunes and find the song you want to convert. (It must be an MP3.)

1. Click OK, then select the song in your songs listing, and go to File > Convert > Create AAC Version.  
2. Click "Show in Finder" to view the new file in your filesystem. Drag the AAC version out of the iTunes folder and into the temp folder of your choice.
3. Change the file extension from .m4a to .m4r. 
4. Repeat with as many files as you'd like to convert.

Via Terminal using Audio File Convert:

afconvert -f m4af /path/to/file.mp3 /path/to/file.m4r

Via Music Converter (Mac Only)

Music Converter is a free MacOS app that lets you drag mp3s into the app and convert them to m4r files. There is a pro version that lets you bulk convert, but it's not necessary if you only have a few files, or if you have a little patience

Import into iTunes

Once you've converted all of your files, drag the new .m4r files into iTunes, and it should show up in the ringtones section of your iTunes library. From here, you can sync it with your iPhone or iPad, and then access it in the Sounds section of your iPhone's settings to use it for various notifications.

Note: If they don't sync automatically, connect your phone to your computer, launch iTunes, and make sure Sync Over Wifi is enabled. If they still don't sync, click on your device in iTunes, then click on Ringtones, and make sure "Sync ringtones" is enabled.