The MySQL SELECT statement is used to retrieve data from one or more tables present in the database. In this article we will show you, How to write SELECT statement in MySQL with example.
In order 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, you can choose the number of columns from the tables. It may be One or more.
- Source: One or more tables present in the Database. MySQL Joins are used to join multiple table.
MySQL SELECT Statement Example
You can use the SELECT statement in MySQL to retrieve rows without referencing. For example, 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 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 world.
USE world; SELECT * FROM country;
Or, You can also try using schema name before the table name.
SELECT * FROM world.country;
Problems with SELECT * in MySQL
- It retrieve all the columns from the specified Table. Most of the times we don’t require all the column in a table.
- It retrieve the columns in default order. Some times we may need to alter the columns order while displaying them.
- If there is 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.
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 existing columns in a table is not the case. Because there will be some columns such as IDs, Bar codes, Modified dates, Rowguid, Photos etc, which might not be required in the select.
For this, MySQL SELECT statement allows us to select few or any specific column from table or tables. This can done by placing the required Column Names followed by SELECT keyword.
USE world; SELECT Name, Continent, Region, SurfaceArea, Population FROM country;
NOTE: If you forget to place the comma between the columns then MySQL assume 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 query syntax, or you don’t want to write a SELECT query then use MySQL workbench Intellisense to write the SELECT Statement for you.
To do so First, Navigate to required Database (under schemas) and select the required Table. Right Click on the Table will open the Context Menu with different options. Please select Select Rows – Limit 1000. Here, 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 then change the Limit to 1000 rows in query window to Don’t Limit option.
MySQL SELECT Statement – Command prompt
In this example, we are going to write select statement in MySQL command prompt to select few columns from country table.
SELECT Code, Name, Continent, Region, SurfaceArea, Population, Capital FROM country;
MySQL Select statement points To Remember
- The MySQL SELECT Statement return 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 columns names.
- Use Joins to SELECT from Multiple Tables.
Thank You for Visiting Our Blog