In this article, you will learn about the SQL SELECT statement. We'll discuss its syntax, how to use it, and how to use the SELECT statement with the WHERE clause. You will also learn how to use it with the ORDER BY clause.
Introduction to the SQL SELECT Statement
The SQL Select statement is a statement that you use to select data from a database.
The result of the SELECT statement is stored in a result table, also known as a result-set. The result-set is a virtual table that has no physical existence. You use the result-set to display the data in a tabular format.
Syntax of the SQL SELECT Statement
The syntax of the SQL SELECT statement is as follows:
SELECT column_name(s)
FROM table_name;
An example of the SQL SELECT statement is as follows:
SELECT * FROM Customers;
How to Use the SQL SELECT Statement in MySQL Workbench
We are going to use a visual database design tool called MySQL Workbench.
MySQL Workbench enables us to create a database, create a table, insert data into the table, and run the SQL SELECT statement.
In order to use the SQL SELECT statement in MySQL Workbench, we need to follow the following steps:
- Open the MySQL Workbench.
- Connect to the MySQL server.
- Create a database.
- Create a table.
- Insert data into the table.
- Run the SQL SELECT statement.
- View the result-set.
Open the MySQL Workbench
In order to open the MySQL Workbench, we need to follow these steps:
First, install MySQL Workbench on your computer, if you have not already installed it. You can download MySQL Workbench from the following link: https://dev.mysql.com/downloads/workbench/
Then install MySQL server on your computer, if you have not already installed it. You can download MySQL server from the following link: https://dev.mysql.com/downloads/mysql/
Now you'll open MySQL Workbench. To do so, click on the Start button and then click on the MySQL Workbench icon.
Connect to the MySQL server by clicking on the MySQL Connections icon and then click on the Local instance 3306 icon.
Enter the password for MySQL server in the Password field and then click on the OK button.
Next, you'll need to create the database by clicking on the New Schema icon and then entering the name of the database in the Name field.
Then click on the Apply button and click on the Close button.
Now you'll create a table. To do this, enter the following SQL statement in the SQL Editor and then click on the Execute button:
CREATE TABLE Customers (
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
ContactName varchar(255) NOT NULL,
Address varchar(255) NOT NULL,
City varchar(255) NOT NULL,
PostalCode varchar(255) NOT NULL,
Country varchar(255) NOT NULL
);
Make sure that you have selected the database in the Database Navigator. To get to the SQL Editor, click on the SQL Editor icon.
Let's now create the table Customers. To do this, enter the following SQL statement in the SQL Editor and then click on the Execute button:
CREATE TABLE Customers (
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
ContactName varchar(255) NOT NULL,
Address varchar(255) NOT NULL,
City varchar(255) NOT NULL,
PostalCode varchar(255) NOT NULL,
Country varchar(255) NOT NULL
);
Now you'll execute the SQL statement. After you have entered the SQL statement, click on the Execute button.
To insert data into the table, enter the following SQL statement in the SQL Editor and then click on the Execute button:
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany');
Now run the SQL SELECT statement by entering the following SQL statement in the SQL Editor and then clicking on the Execute button:
SELECT * FROM Customers;
How to Use the SQL SELECT Statement with the WHERE Clause
You can use the SQL SELECT statement with the WHERE clause. You use the WHERE clause to filter records. The WHERE clause extracts only those records that fulfill a specified condition.
The syntax of the SQL SELECT statement with the WHERE clause is as follows:
SELECT column_name(s)
FROM table_name
WHERE condition;
An example of using the SQL SELECT statement with the WHERE clause is as follows:
SELECT * FROM Customers
WHERE Country='Germany';
Let's insert another record with a different country and test out the SQL SELECT statement with the WHERE clause.
To insert another record, enter the following SQL statement in the SQL Editor and then click on the Execute button:
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitucion 2222', 'Mexico D.F.', '05021', 'Mexico');
Now let's run the SQL SELECT statement with the WHERE clause. Enter the following SQL statement in the SQL Editor and then click on the Execute button:
SELECT * FROM Customers
WHERE Country='Germany';
How to Use the SQL SELECT Statement with the ORDER BY Clause
You can also use the SQL SELECT statement with the ORDER BY clause. The ORDER BY clause sorts the result-set in ascending or descending order. It sorts the records in ascending order by default. If you want to sort the records in descending order, you can use the DESC keyword.
The syntax of the SQL SELECT statement with the ORDER BY clause is as follows:
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC/DESC;
An example of using the SQL SELECT statement with the ORDER BY clause is as follows:
SELECT * FROM Customers
ORDER BY Country DESC;
Conclusion
In this article, we learned about the SQL SELECT statement. We learned about the syntax of the SELECT statement, how to use it, and how it works with the WHERE clause. We also learned about the SQL SELECT statement with the ORDER BY clause.