Consoles enhance ships by boosting skills, resistances, abilities, and other things. There are four categories of consoles: Engineering, Tactical, Science, and Universal. They each have their own slots on a ship, except Universal which can go into any of the console slots.
I'd like to briefly talk about consoles that boost skills. For example it could say +5 Starship Shield Emitters which means that this console increases that skill. Now that skill, in turn, will improve something else. So in this case, Starship Shield Emitters improves abilities that heals and repairs shields like Science Team. It's like a chain reaction of bonuses.
Let's quickly go over each console type and what they are generally used for.
Engineering consoles enhance power settings on your ship (weapons, shields, engines, and auxiliary), as well as providing resistance to different damage types. They can also enhance flight turn rate, crewman available to repair the ship, hull repair, and power transfer rates. Basically they handle anything engineering related to the ship. These are fairly straight forward and if you read the description of the item you should be able to understand it.
Science consoles generally improve the abilities of Science officers or improves other science related things such as crew recovery and shield regeneration. They sometimes boost a specific skill (from your skill tree) so read the description of that skill to see which abilities it improves.
Tactical consoles improve Tactical systems. They generally improve specific damage types or a type of weapon. So for example, it can improve Phaser weapons or it can improve all Beam weapons. Read the description to see exactly what it improves.
Universal consoles can do a variety of things. They can take on the responsibility of any of the other 3 types of consoles, but they can also provide a brand new ability for your ship.