SQL SELECT is probably the most common SQL command, and is used to fetch data from the database.
This lesson discusses how to use the SQL SELECT statement using the Sakila Database downloaded in a previous lesson. If you haven't already, follow the steps to install the Sakila DB, then head back here.
First, some overarching notes about SQL syntax.
Key Points on SQL Syntax
- Keywords in SQL are often capitalized, but it is not required. It's just a formatting convention.
- All SQL commands must end with a semicolon when executed within MySQLWorkbench or the MySQL Shell (CLI). MySQL Workbench does provide some exceptions to this rule. But generally speaking, SQL commands are terminated by a semicolon.
Select the Database to Use
MySQL (and MySQL Workbench) can manage many databases at once. Therefore, the first query you'll run will select the database to be used:
USE <DATABASE_NAME>;
For example:
USE sakila;
This will tell MySQL that your queries should be executed against the Sakila database. Then, you can query directly to the tables with SQL SELECT.
How to Use the SQL Select Statement
The SELECT command is followed by the data you want to retrieve, and then from the table that contains it, denoted below:
select * from actor;
If you do not run the "use" query, you can still execute queries, but you must indicate the database name in all queries.
For example:
SELECT * FROM sakila.actor;
SQL Select Statement Examples
Below are three examples of using the SELECT command in SQL.
1. Fetch everything
Let's SELECT all rows and columns from the actor table: (In SQL, "*" means "ALL")
SELECT * FROM actor;
Output:
2. Fetch multiple columns
Let's just SELECT two distinct columns from the actor table, but all rows.
SELECT first_name, last_name FROM actor;
Output:
3. Fetch one distinct column
Let's just SELECT one distinct column from the actor table, but again, all rows.
SELECT actor_id FROM actor;
Output:
Summary: SQL Select Statement
You can use the SQL SELECT statement to retrieve data from one (or more) tables.
SQL Select Syntax Here's the syntax for the SQL SELECT statement, where you can substitute the variables in < > with your values.
SELECT <YOUR_COLUMNS> FROM <YOUR_TABLE>;
To SELECT data out of more than one table, you use JOIN, which you'll cover shortly. As you can see, the SELECT query is quite simple and intuitive to use.