All new Macs come with iMovie, which is good for home movies.
For more professional you're looking at using some more expensive (or free, if you know where to look) software. I always use Adobe Premiere Pro, mainly because it is integrates REALLY well with creative suite.
For example Premiere Pro can directly put photoshop PSDs on the timeline and you can do effects on them. It just works really well.
However, I know lots of people use Sony Vegas also, and final cut pro as someone said, but I've never used these. I just know that Premiere Pro is very good.
Final Cut Pro is awesom, but i agre with thewakeupcall, if you have a pc no point gettin a mac. because adobe prem pro is a very very good application and it does go well with all the packages available on Adobe CSS. Ive made a few vids the effects range is good aswell it has all the standard effects like motion blur zoom and transitions, but there is many advance ones aswell but you can configure them all to your standards.
Also theres TONS of tuts out there to make class vids and the gap between mac and pc are small now days