The most important statement
SELECT reads data. You will write it more than any other SQL statement, and it is safe: a query only looks, it never changes anything. The simplest form is:
SELECT * FROM categories;+----+-------------+
| id | name |
+----+-------------+
| 1 | Electronics |
| 2 | Books |
| 3 | Home |
| 4 | Sports |
| 5 | Toys |
+----+-------------+Reading from the sample database, this says "give me every column (*) from the table categories". The result is a small table:
If you ever want to know what a statement does, read it from the FROM clause backwards: first decide which table, then which rows, then which columns.
Choose the columns you need
Listing columns is clearer and faster than *, because the server and the network carry less data. Separate the names with commas:
SELECT first_name, last_name, city FROM customers;+------------+-----------+-----------+
| first_name | last_name | city |
+------------+-----------+-----------+
| Asha | Verma | Delhi |
| Ben | Carter | London |
| Chloe | Dubois | Paris |
| Daniel | Kim | Seoul |
| Elena | Rossi | Rome |
| Farid | Khan | Mumbai |
| Grace | Okafor | Lagos |
| Hiro | Tanaka | Tokyo |
| Isabel | Santos | Lisbon |
| Jack | Miller | New York |
| Kavya | Nair | Bengaluru |
| Liam | O'Brien | Dublin |
+------------+-----------+-----------+The columns appear in the order you list them, which does not have to match the order in the table. You can repeat a column or change the order freely:
SELECT city, first_name FROM customers;+-----------+------------+
| city | first_name |
+-----------+------------+
| Delhi | Asha |
| London | Ben |
| Paris | Chloe |
| Seoul | Daniel |
| Rome | Elena |
| Mumbai | Farid |
| Lagos | Grace |
| Tokyo | Hiro |
| Lisbon | Isabel |
| New York | Jack |
| Bengaluru | Kavya |
| Dublin | Liam |
+-----------+------------+Use SELECT * for quick exploration. In application code, name the columns. If someone later adds a column to the table, a query written with * suddenly returns more data than your code expects.
Calculated columns and aliases
A select list can contain expressions, not just column names. MySQL calculates them for every row:
SELECT name, price, price * 0.9 AS discounted_price FROM products;+-----------------------------+--------+------------------+
| name | price | discounted_price |
+-----------------------------+--------+------------------+
| Wireless Mouse | 24.99 | 22.491 |
| Mechanical Keyboard | 89.50 | 80.550 |
| USB-C Hub | 39.00 | 35.100 |
| Noise-Cancelling Headphones | 199.99 | 179.991 |
| SQL Basics Handbook | 29.95 | 26.955 |
| Web Design Patterns | 34.50 | 31.050 |
| Data Structures Illustrated | 45.00 | 40.500 |
| Desk Lamp | 32.00 | 28.800 |
| Ceramic Mug Set | 18.75 | 16.875 |
| Standing Desk Mat | 55.00 | 49.500 |
| Yoga Mat | 27.50 | 24.750 |
| Resistance Bands | 14.99 | 13.491 |
| Water Bottle | 12.00 | 10.800 |
| Building Blocks Set | 49.99 | 44.991 |
| Puzzle 1000 Pieces | 19.99 | 17.991 |
+-----------------------------+--------+------------------+AS discounted_price gives the new column a name. This is an alias. Without it, the header would be the raw expression price * 0.9. The word AS is optional, but writing it makes the intent obvious.
You can also do arithmetic with no table at all, and use functions:
SELECT 10 / 4 AS division, 10 DIV 4 AS whole_part, 10 % 4 AS remainder, CONCAT('Hello', ' ', 'MySQL') AS greeting;+----------+------------+-----------+-------------+
| division | whole_part | remainder | greeting |
+----------+------------+-----------+-------------+
| 2.5000 | 2 | 2 | Hello MySQL |
+----------+------------+-----------+-------------+Note that / gives a decimal answer, DIV throws the fraction away and % gives the remainder. Later lessons cover many more functions.
Limit the output while you explore
Tables can have millions of rows. When you are experimenting, add LIMIT to see only the first few rows:
SELECT * FROM products LIMIT 3;+----+---------------------+-------------+-------+-------+
| id | name | category_id | price | stock |
+----+---------------------+-------------+-------+-------+
| 1 | Wireless Mouse | 1 | 24.99 | 150 |
| 2 | Mechanical Keyboard | 1 | 89.50 | 60 |
| 3 | USB-C Hub | 1 | 39.00 | 80 |
+----+---------------------+-------------+-------+-------+You will learn how to choose which rows come first in the lesson on sorting.
Comments
Comments are notes for people. MySQL ignores them. A line starting with -- (the space after the two dashes is required) runs to the end of the line, and MySQL also accepts # for the same purpose, although -- is the portable choice. Block comments use /* */:
-- A comment to the end of the line (the space after the two dashes is required)
/* A comment that
can span several lines */
SELECT COUNT(*) AS total_products FROM products; -- trailing comment+----------------+
| total_products |
+----------------+
| 15 |
+----------------+The order of clauses
A query can have several parts, called clauses. When you use them, they must appear in this order:
SELECT columns
FROM table
WHERE row filter
GROUP BY grouping columns
HAVING group filter
ORDER BY sort order
LIMIT row countYou do not need all of them. Only SELECT is required for a calculation, and FROM for reading a table. You will meet each clause in turn over the next lessons.
One surprising detail is that MySQL does not run the clauses in the order you write them. Conceptually it finds the table (FROM), filters rows (WHERE), groups, filters groups (HAVING), computes the select list, sorts and finally limits. This explains why, for example, a column alias created in SELECT cannot be used in WHERE.
Common mistakes
- A trailing comma before FROM:
SELECT name, price, FROM productsis a syntax error. - Forgetting a comma between columns:
SELECT name price FROM productsis read as "the columnnamewith the aliasprice", which silently returns the wrong thing. - Using
SELECT *in application code and breaking when the table changes. - Quoting column names as strings:
SELECT 'name' FROM productsreturns the textnameon every row, not the column.
Exercise
Write a query that shows each product's name, its stock, and a column called stock_value equal to price * stock. Then change the query so that it only shows the first five rows.
Show one possible answer
SELECT name, stock, price * stock AS stock_value
FROM products
LIMIT 5;+-----------------------------+-------+-------------+
| name | stock | stock_value |
+-----------------------------+-------+-------------+
| Wireless Mouse | 150 | 3748.50 |
| Mechanical Keyboard | 60 | 5370.00 |
| USB-C Hub | 80 | 3120.00 |
| Noise-Cancelling Headphones | 25 | 4999.75 |
| SQL Basics Handbook | 200 | 5990.00 |
+-----------------------------+-------+-------------+Key points
- SELECT columns FROM table; is the basic shape of every query. It returns a result set and never changes the data.
- SELECT * returns every column. It is fine for exploring, but name the columns you need in real code.
- You can select expressions, such as price * 0.9, and give them a name with AS.
- Clauses must appear in a fixed order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.