Take this article as a brief introduction to video visual effects making. Just an intro because there are so many stuff to learn and for that you have plenty of tutorials all around the Internet.
What are special effects?
Ever wanted to make lightning coming out of your hand just like in Star Wars? Or even lightsaber? Or some explosions, magic like in Harry Potter or robots like in Transformers? Well it's not that hard actually when you know what to do and have enough patience (yea this is very important here, patience). So what are the special effect in video? It's something you want to add on your already existing video. Imagine your video as one layer or sheet of paper. Now imagine another video with explosion as another layer or sheet. Now just place the explosion one on top of the first one and here you go...special effect!!! So the whole point here is just creating many layers with stuff you want to have in your video and placing them over and over the previous ones.
Of course you can't do it just like that. We need a proper software. The Bible of special effects is called Adobe After Effects. This one is the most common program for special fx that most of the artists use. You have a variety of options here and this is the place where you put all the effects together like placing the papers over and over until you have a nice book ^^. You have tons of tutorials for AAE. Just look for something like ''Basics with After Effects tutorial'' or ''After Effects for beginners tutorial'' on Google or YouTube. Once you learn the basics, you can do just anything with this program.
Sometimes you'll need some 3D effects like for creating the buildings, some debris and explosions, robots, monsters, texts for logos etc. The best option is to learn with an awesome tool called Blender. At first it's hard to do so cause the interface is a bit complex but again, once you learn with it, you have already won! Look for some Blender tutorials by yourself. There are many again. You can use also some more professional stuff like Autodesk's 3Ds Max or Maya but prepare a lot of money.There are also some free alternatives for AAE. However prepare for some trouble. AAE offers a lot of functionality and effects which are possible to create in the other programs but it's not that easy as in AAE. A great piece of open-source software is Cinelerra. It offers a great variety of options and similar functions to AAE. The interface is a bit different and one needs to get used to it. Another similar one is Kdenlive. Worth mentioning is also Fusion by BlackMagic which is getting closer to AAE. Need to say that with some skill you can do almost every effect with just Blender itself (which I prefer most).
When you finished the effects it's time to put everything together, add some sounds and music. For that people use some video editing software such as Sony Vegas or Final Cut Pro. If you're looking for a freeware you can use Windows Movie Maker for really basic video edit (simple but powerful tool, if you just need to put multiple videos together and add music it's ideal to use WMM) or if you need multiple audio/video tracks you have built-in video editor in Blender.
Just a short note. You can even compose music. There are many programs for that like FL Studio or free LMMS. You can connect your pc with electronic piano/keyboard or use just your mouse and keyboard to compose music. Choose some instruments, add notes etc. There are many ways to import the instruments, be it VST ones or sound fonts.
Here is a short tutorial on how to make some eye effect in Blender made by me.