How to write a MySQL SELECT statement with an example?. It is used to retrieve data from one or more tables present in the database. To explain the MySQL SELECT statement, we are going to use the world database that came along with the installation.
MySQL SELECT Statement Syntax
The MySQL SELECT Statement can be written as:
-- Syntax SELECT [ColumnName1], [ColumnName2]....,[ColumnNameN] 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. Joins are used to join multiple tables.
MySQL SELECT Statement Example
You can use the MySQL SELECT statement 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 choose 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 MySQL SELECT * Statement
- It retrieves all the columns from the specified table. Most of the time, we don’t require all of them.
- It retrieves the columns in default order. Sometimes 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 the *.
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 can rewrite as:
USE world; SELECT Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2 FROM country;
MySQL SELECT Statement for Few Columns
In real-time, choosing 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.
For this, the MySQL SELECT statement allows us to pick a few or any specific columns from the tables. We can do it by placing the required col names, followed by the keyword.
USE world; SELECT Name, Continent, Region, SurfaceArea, Population FROM country;
NOTE: If you forget to place the comma between the columns, it assumes the second column as the Alias for the first. 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 query, then use workbench Intellisense to write the SELECT Statement for you.
To do so, First, Navigate to the required Database (under schemas) and choose 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 was taken from the query window
This option will generate the MySQL 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.
Command prompt Example
In this example, we are going to write a MySQL select statement in command prompt to pick up a few columns from the country table.
SELECT Code, Name, Continent, Region, SurfaceArea, Population, Capital FROM country;
Points To Remember
- It 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 work from Multiple Tables.