I want to put a little form where people can just enter their email address (and maybe their name) and then click submit and then they'll be subscribed to the mailing list that I manage on campaign monitor.
Is this possible? Or would I have to just do it manually (as in, setup my own database and then manually add their email to my campaign monitor account)?
That's definitely possible and actually very easy. There are some instructions here on how to create that form code and then you'd just paste it onto a page on your site.