Texture mapping is a method for adding detail, surface texture, or colour to a computer-generated graphic or 3D model. A texture map is applied (mapped) to the surface of a shape, or polygon. This process is akin to applying patterned paper to a plain white box. (Wikipedia)
In particular to thanks computer game technology, texture mapping has become very sophisticated. (Start reading from Wikipedia's texture mapping article and follow up the specialized articles)
This article is also part of the Flash tutorials
(This needs to be written ....)
For 2D artwork, you should distinguish between textures you apply as a whole to an object (e.g. door) and textures that you use to "paint" tiles. The latter should be tileable, i.e. not show artificial borders.
You can use textures as paint. Here is a short "how to"
Note:
Most high quality textures are sold commercially, but some commercial sites give out free samples. You also may dig in your computer. If you have games installed, e.g. Neverwinter Nights there are directories with lots of textures, but these are probably under copyright. Unfortunately, searching for free textures on the Internet is trouble. Most of the time you stumble on sites that just contain links and adds. But there are some excellent free and commercial resources. Below a few that include free textures.