Information about the order you will get from the table sales_flat_order while information at order line level you find in sales_flat_order_item. To get informations at order level and at order line item you have to join both tables.
select o.*, oi.* from sales_flat_order o
join sales_flat_order_item oi on o.entity_id = oi.order_id where oi.parent_item_id is null and ....
With this statement you will get all information from both tables please adapt it however you need it. The condition parent_item_id is null is to avoid double listing configurale products, which are stored twice (one line for parent and one for the child).
Please note: you will get for each order as many rows as there are order lines.
If you need one row per order you have to add group by oi.order_id to your statement and sum before the numeric order_item columns you request in the statement.
Update example statement with carrier information:
Assuming you want to get the real order id (i.e. increment id), the status, ordered quantity and the total amount (incl. tax and shipping) for an order AND the skus and prices incl. tax for each order line AND a carrier_code (if the order has been shipped and the tracking information has been updated), you may use the following statement:
select o.increment_id, o.status, o.base_grand_total,
o.total_qty_ordered, oi.sku, oi.price_incl_tax, t.carrier_code
from sales_flat_order o
join sales_flat_order_item oi
on o.entity_id = oi.order_id
left join sales_flat_shipment_track t
on o.entity_id = t.order_id -- left join here if you want also orders which are not yet shipped
where oi.parent_item_id is null
Please check the database table for other appropriate columns you need according to your requirement.