Table of Contents
Now you have your data populated (if you haven’t, please go back to the previous post) to complete the setup.
In this post, I’m going to show you various way to query your data using the SELECT command. When using in combination with other clauses (such as WHERE, LIMIT), you can get exactly what you want from your data.
Let’s get started.
Basic SELECT usage
Select all data from a table
When you don’t have a lot of data, you can use
SELECT * to get all the data from a table:
-- Select all columns from a table SELECT * FROM customers; SELECT * FROM orders;
The data returns from
SELECT * FROM customers is:
Select certain columns
There are times you only want to get data from specific columns. For example, you only need to customer’s id, first name and email from the customers table, here is the query you need:
-- Select specific columns from a table SELECT customer_id, first_name, email FROM customers;
You can set the columns’ names to different names if you want/need. For example, I want to rename the column email to customer_email, here is the query:
-- Select specific columns from a table SELECT customer_id, first_name, email as customer_email FROM customers;
Select only distinct values
There are times you want to get only the distinct values in a column. In such cases, use
DISTINCT. For example, you want to know how many unique first name in your customers table:
-- Select distinct values from a column SELECT DISTINCT first_name FROM customers;
Filter result using WHERE
When your data grows bigger, select * could cause serious problems. Doing so could cause our database server runs out of memory. In addition, you are only interested in a subset of your data. In such case, you can use WHERE to create conditions that your data must match.
-- Select only the records that have customer_id < 10 SELECT * FROM customers WHERE customer_id < 10;
Of course, you can have many conditions in your WHERE statement. In such case, chain them with AND
-- Multiple conditions in WHERE SELECT * FROM customers WHERE customer_id < 10 AND last_name = 'Doe' AND phone LIKE '%1234'
ORDER the results
You can order your result by specific columns (plural). For example, you want to see the largest number of items in a single order, this is the query:
-- SELECT with ORDER BY single column SELECT * FROM order_items ORDER BY quantity DESC;
Take a look at the column price for line 4 and 5. As you can see, the quantity for these two items are the same. If you also want to order the price, add the price column to the ORDER BY clause:
-- SELECT with ORDER BY multiple columns SELECT * FROM order_items ORDER BY quantity DESC, price DESC;
LIMIT number of record
In the ORDER BY example above, if you are only interested in the top 3 items, you can use LIMIT:
-- SELECT with ORDER BY multiple columns and LIMIT SELECT * FROM order_items ORDER BY quantity DESC, price DESC LIMIT 3;
In this post, I’ve shown you the most common SELECT queries. With these knowledge, you can perform some useful queries against your database. HOwever, the SELECT statement could be much more powerful than this. I’ll introduce you to some advanced SELECT queries in the next post.
I build softwares that solve problems. I also love writing/documenting things I learn/want to learn.