The bulk of the fermentable sugars in beer come from malts and malt extracts - that's what makes it beer. However, there are a number of other sugars that, when used in smaller quantities, can impart their own distinct characteristics to your brew. Here, we'll discuss the major alternative sugars, the effects they may have on the finished product, and the best ways to use them.
First, a little bit about sugar chemistry. Sugar is a simple carbohydrate. That is, it is a single molecule made up of some configuration of carbon, hydrogen and oxygen. Starches are complex carbohydrates, which means that they are made up of chains of sugar molecules bonded together. The breaking of these chains by various processes or enzymes converts the starches into their component sugars. The amylase enzyme in saliva is a good example of this. A common gradeschool science experiment is to take a saltine, place it in your mouth and chew it slowly 100 times. By the time you're done, you'll notice that the normal starchy, floury cracker taste has been replaced in part by a sweeter flavor. This is your saliva breaking down the starch chain into sugars.
The mashing process affects malted barley in the same way, which is how malt extracts are made. These extracts are composed primarily of a sugar called maltose. Chemically, maltose is one of five major types of sugars. Sucrose is the name for common table sugar, which is usually derived from beets or sugar cane. Fructose is the sweetest tasting of the sugars, and occurs naturally in fruit, and to a lesser degree in malt. Glucose and dextrose are molecularly the same, and most commonly sold as syrup and dry crystals, respectively. Finally, lactose is a sugar which is naturally present in milk. Fructose, dextrose and sucrose are all very easily and rapidly fermentable by beer yeast. Maltose is obviously fermentable as well, but the process takes somewhat longer, and lactose is not fermentable at all by normal beer yeasts alone. Certain wild yeasts can ferment lactose, and various enzymes can be introduced which will help beer yeast ferment it as well.
While there are a wide variety of sugars available to the homebrewer, it should be kept in mind that excessive use of any non-malt sugar will detract from its characteristic flavor and make your beer considerably less beer-like. In general, these sugars should not make up any more than 25% or so of the total sugar content of your wort. Some sugars also contribute strong flavors to your beer, or have other special considerations. These will be noted below.
Corn sugar/syrup: Probably the most common of the sugars we'll be discussing, corn sugar is made up almost entirely of glucose/dextrose. It will ferment completely, contributing more alcohol content than a similar amount of malt extract, and will lighten the body and flavor of the brew. Corn sugar will also ferment very rapidly, and will thus shorten the time your beer will need to spend fermenting. The most common use of corn sugar is as a priming sugar during the bottling process. For more details, see Homebrewing 104: Bottling and Carbonation. If you're using corn syrup, make sure it is pure corn syrup, and doesn't have any flavorings or preservatives added (as storebought corn syrup often does).
Table sugar: As mentioned previously, table sugar is 100% refined sucrose, derived from beets or sugar cane. Unlike malt extracts, which contain a variety of non-sugars and have a strong flavor component, table sugar is completely fermentable and will contribute no flavor at all to your beer. For this reason, its most common use is to boost the alcohol content of the finished brew. Impure or unrefined beet sugars should not be used, as they contribute flavors which are decidedly unpleasant (we're not making borscht here). Impure or unrefined cane sugar, such as cane syrup, sugar cane juice, or whole sugar cane can be used, but in large quantities will contribute a dry, cidery acidity to your final product.
Malto-dextrin: Sold to homebrewers in powder form, malto-dextrin is a combination of malt extract and dextrin, a complex sugar consisting of a chain of dextrose molecules. This chain cannot be broken by beer yeast without the assistance of enzymes, and so is often used commercially when brewers want to sweeten the finished beer. It also adds a little body and contributes to head retention, and many homebrewers I know will add between a quarter- and a half-pound of malto-dextrin to every beer they brew for these reasons alone. You will also find it as an ingredient on many "imitation" or "clone" recipes, which attempt to recreate the character of one commercially-produced beer or another.
Lactose: As mentioned earlier, lactose is not fermentable by normal beer yeasts. This means that its flavor will not change when it is used in beer. Lactose is the primary sugar in milk, and has a characteristic milky or creamy taste as a result. It is also the least sweet of all the sugars. It is most commonly used in certain varieties of stout, such as sweet stouts, milk stouts and cream stouts. Usually, about half a pound is enough for a five-gallon batch of any of these, although I have seen recipes that use as much as a full pound. Remember that, because it is not fermentable, lactose should be added above and beyond the normal complement of sugars in your beer. Some people prefer to add the lactose at the time of priming, although because of its non-fermentable nature, I fail to see what possible difference this could make.
Brown sugar: Table sugar is made by refining sugar cane syrup. When the refining process is complete, the stuff that has been removed is molasses. Brown sugar is simply cane syrup that has been incompletely refined. That is, the process was halted before all of the molasses was removed from the syrup. This means that, while brown sugar does possess similar characteristics to table sugar, it also retains some unfermentable sugars and other compounds which will lend their own characteristic flavor to your beer. Brown sugar is often used by homebrewers in stouts, alts and other dark beers that require a long fermentation, as it is difficult to ferment. Brown sugar should never be used for priming, as it distributes itself fairly unevenly, and can result in some beers being flat, while others explode in the bottle.
Molasses: As already discussed, molasses is made up of the byproducts of the refinement of cane syrup. As a result, about 25-40% of molasses is completely unfermentable. This means that, more so than any of the other sugars we'll be discussing here, molasses will contribute a very strong flavor to your brew. This flavor is not unpleasant, and actually goes quite well in some stouts, porters, and brown ales, but it is very potent. One cup will contribute a noticeable flavor to 5 gallons of beer, while more than 1.5 or 2 cups will threaten to overpower it. An excess of molasses will also add a large amount of body to your beer, making it heavy and undrinkable in quantity. Molasses should never be used as a priming sugar.
Sorghum: While it is often labeled "sorghum molasses", sorghum is not molasses. It is a syrup derived from the sorghum plant, and while its flavor is similar, it is unique in its own way and is slightly more fermentable. It can be used in somewhat larger quantities, but be conservative with it. Like molasses, do not use it for priming.
Rice syrup: Instead of being made from malted barley, rice syrup is made from malted rice. The resulting syrup has a high concentration of glucose, with smaller amounts of maltose and fructose. Unlike malt, rice has very little inherent flavor, and a beer heavy in rice syrup will have a lighter color and a lighter, crisper flavor. Most of the commercial American pilseners such as Budweiser use a significant quantity of rice syrup to brew their beer.
Maple syrup: I have personally never had the cojones to brew a beer using maple syrup, although I can envision situations in which it might work very well. In Charlie Papazian's "New Complete Joy of Home Brewing", he advocates the use of at least one gallon of maple syrup in a five-gallon batch of beer. Most commercial storebought maple syrup is less than 5% actual maple syrup, the rest being made up of corn syrup. This is fairly cheap stuff, while pure maple syrup can run $10-12 US per quart. Papazian doesn't make it clear which variety he's talking about, but if it's the latter, that's an expensive batch of beer. In either case, the amount seems high to me, and I can only imagine a very strongly maple-flavored brew as a result. Other recipes I've encountered utilizing maple syrup have used as little as 12 ounces, and I've never seen one that used more than two quarts (half a gallon). This range seems more reasonable to me, and if you're going to brew a maple-augmented beer, I'd suggest starting smaller and increasing the amount in future batches if it works well.
Honey: Honey is a very popular ingredient in beer, and rightfully so. It also has a number of special considerations which the other sugars we've discussed do not. Honey contains a variety of sugars, mostly glucose and fructose, but smaller amounts of maltose and sucrose as well. In addition to the sugars, honey is likely to contain other ingredients, which can include pollen, enzymes, wild yeast, beeswax and even tiny fragments of the bees themselves. These will be present in greater concentrations in raw honey, which you might get at a roadside stand or farmer's market. None of these are necessarily bad things, but honey should always be boiled for the full duration of your wort, at least 60 minutes, to neutralize any potentially harmful ingredients (harmful to your beer, not to you). Honey is very fermentable, and will lend a dry, crisp sweetness to your beer. It's a very tasty addition to weiss beers, lagers, and lighter ales. One pound of honey is usually enough, and more than two pounds will detract noticeably from your beer's malty character. There are several types of honey available. The two most popular among homebrewers are clover honey, which has the most "traditional" honey flavor, and orange blossom honey, which contributes pleasant citrus undertones. Alfalfa honey is probably the lightest and least flavorful variety, making it popular for those who want to avoid the often saccharine finish that honey can lend to a beer. Some farms and markets may also sell wildflower honey, which has a light, flowery, almost herbal aroma and flavor which can be a wonderful complement to certain types of aromatic hops when used in pale ales and other light brews. See Homebrewing 202: Hop Selection and Use for more information on hops.
That about covers the major types of sugars available for use by the homebrewer. There are many more sources of sugar in the world, of course, which you may wish to experiment with as time goes on. When experimenting with other sugars, it's best to do some research first. Look around and see if you can find other recipes which use it. It's also a good idea to search the Home Brewing Digest's archives at http://www.hbd.org/. There, people will knowledge bases ridiculously superior to yours and mine discuss every possible aspect of brewing on a daily basis. You will undoubtedly find some reference there to someone who has already tried what you're thinking of and you can learn from their results. As they say, your radical ideas about homebrewing have already occurred to others. The most important thing is that you're brewing what you like and having fun doing it.