It is encrypted.
TDE performs a real time encryption and decryption of data at the page level. Data is encrypted before it is written into the page and decrypted and read into memory. The advantage here is that your files are encrypted (you cannot access the DB by restoring it onto another machine without restoring the certificate there) while any applications that connect to your DB work as usual without the need for any changes.
If you want to encrypt your data (wherein a SELECT query will return an encrypted value) then you'll need to use column encryption (lots of information on this blog - http://blogs.msdn.com/b/lcris/ with some starter information available here and here)