How to write a SELECT statement in MySQL with an example?. The MySQL SELECT statement is to retrieve data from one or more tables present in the database. To explain the MySQL SELECT, we are going to use the world database that came along with MySQL installation.
MySQL SELECT Statement Syntax
The SELECT Statement in MySQL can be written as:
SELECT [Column Name 1], [Column Name 2]....,[Column Name N] FROM Source;
- Column Names: Depending upon the requirement, choose the columns from the tables. It may be one or more.
- Source: Choose one or more tables from the Database. MySQL Joins are used to join multiple tables.
MySQL SELECT Statement Example
You can use the SELECT statement in MySQL to retrieve rows without referencing. For example, the below statement will return 2 as output.
In the above case, MySQL allows you to use DUAL as the dummy table name. Again it is just an option.
SELECT 1+1 FROM DUAL;
MySQL SELECT All Columns
In this example, we are going to select all the existing Columns present in the country table using an asterisk (*). In MySQL SELECT statement, * is the shortcut to represent all the columns that are available in the source tables. Remember USE world will change the default database to the world.
USE world; SELECT * FROM country;
Or You can also try using the schema name before the table name.
SELECT * FROM world.country;
Problems with SELECT * in MySQL
- It retrieves all the columns from the specified table. Most of the time, we don’t require all the columns in a table.
- It retrieves the columns in default order. Some times we may need to alter the order of the columns while displaying them.
- If there are any changes made in the underlying table (Reordering Columns, Adding, or Removing Columns) will not reflect the Views created using SELECT *.
NOTE: Please don’t use MySQL SELECT * FROM statement in real-time even if you want to retrieve all the columns present in a table.
The ideal approach, Placing all (or required) column names after the SELECT keyword. For instance, the above statement is written as:
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2 FROM country;
MySQL SELECT Statement Select Few Columns
In real-time, selecting all the present columns in a table is not the case. It is because there will be some columns such as IDs, Bar codes, Modified dates, Rowguid, Photos, etc., which might not require in the select.
For this, MySQL SELECT statement allows us to select a few or any specific columns from table or tables. We can do it by placing the required Column Names, followed by the SELECT keyword.
USE world; SELECT Name, Continent, Region, SurfaceArea, Population FROM country;
NOTE: If you forget to place the comma between the columns, the MySQL assumes the second column as the Alias Column for the first column. So, Please be careful
MySQL SELECT Statement using Workbench GUI
If you don’t know the syntax or you don’t want to write a SELECT query, then use workbench Intellisense to write the SELECT Statement for you.
To do so First, Navigate to the required Database (under schemas) and select the table needed. Right Click on the Table will open the Context Menu with different options. Please choose Select Rows – Limit 1000. Here, the limit value is taken from the query window
This option will generate the SELECT Statement for us.
NOTE: If you want to see all the records, the change the Limit to 1000 rows in the query window to Don’t Limit option.
MySQL SELECT Statement Command prompt
In this example, we are going to write a select statement in MySQL command prompt to select a few columns from the country table.
SELECT Code, Name, Continent, Region, SurfaceArea, Population, Capital FROM country;
Select statement points To Remember
- The SELECT statement returns all the records present in the table. If you want to restrict the Rows, then please use WHERE Clause.
- Please avoid using SELECT * for selecting all the column’s names.
- Use Joins to SELECT from Multiple Tables.