SELECT statement in sql

What is a SELECT query in MySQL?

SELECT QUERY is used to retrieve data from a MySQL database. Databases store data for later retrieval. The purpose of MySQL Select is to return one or more rows from database tables that match given criteria. The select query can be used in a scripting language such as PHP, Ruby, or you can execute it from the command line.

SQL SELECT statement syntax

This is the most commonly used SQL command and has the following general syntax

SELECT [DISTINCT|ALL ] { * | [fieldExpression [AS newName]} FROM tableName [alias] [WHERE condition][GROUP BY fieldName(s)] [HAVING condition] ORDER BY fieldName(s)

HERE

• SELECT is an SQL keyword that lets the database know that you want to retrieve data.
• [DISTINCT | ALL] are optional keywords that can be used to fine-tune the results returned by the SQL SELECT statement. If nothing is specified, the default is ALL.
• {* | [fieldExpression [AS newName]} must have at least one part specified, "*" selects all fields from the specified table name, fieldExpression performs some calculation on the specified fields, such as adding numbers or concatenating two string fields into one.
• FROM tableName is required and must contain at least one table, multiple tables must be separated by commas or joined using the JOIN keyword.
• If a condition is optional, it can be used to specify criteria in the result set returned by the query.
• GROUP BY is used to combine records that have the same field values.
• The HAVING clause is used to specify criteria when working with the GROUP BY keyword.

• ORDER BY is used to specify the sort order of the result set.

*

The star symbol is used to select all columns in a table. An example of a simple SELECT statement looks like the one below.

SELECT * FROM `members`;

The above statement selects all fields from the members table. A semicolon completes this statement. This is not required, but it is considered good practice to end your statements this way.

Practical examples

Click to download the myflix database used for practical examples.

You can learn how to import a .sql file into MySQL WorkBench.

Examples run in the following two tables

Table 1: table of participants

Table 2: movie table

Getting a list of participants

Let's assume that we want to get a list of all registered members of a library from our database, we will use the script shown below to do this.

SELECT * FROM `members`;

Running the above script in MySQL Workbench gives the following results.

Our query above returned all rows and columns from the members table.

Let's say we're only interested in getting full names, gender, physical address, and email address only. The following script will help us achieve this.

SELECT `full_names`,`gender`,`physical_address`, `email` FROM `members`;

Running the above script in MySQL Workbench gives the following results.

Getting a list of movies

Remember in our discussion above that we are referring to expressions used in SELECT statements. Let's say we want to get a list of movies from our database. We want the movie title and director's name to be in the same field. The director's name should be in brackets. We also want to know the year the movie was released. The following script helps us with this.

SELECT Concat(`title`, ' (', `director`, ')') , `year_released` FROM `movies`;

HERE

• The MySQL Concat() function is used to concatenate column values.
• The line "Concat (` title`, '(', Director ", ')') gets the title, adds an opening parenthesis followed by the director's name, then adds a closing parenthesis.

String parts are separated by commas in the concat() function.

Running the above script in MySQL Workbench results in the following set of results.

Alias ​​field names

The example above returned the concatenation code as the field name for our results. Let's say we want to use a more descriptive field name in our result set. We would use a column alias to achieve this. Following is the basic syntax for a column alias.

SELECT `column_name|value|expression` [AS] `alias_name`;

HERE

• "SELECT` column_name | value | expression `" is a regular SELECT statement, which can be a column name, value, or expression.
• "[AS]" is an optional keyword before the alias name denoting an expression, value, or field name to be returned as.
• "alias_name" is the alias we want to return in our result set as the field name.

The above query with a more meaningful column name

SELECT Concat(`title`, ' (', `director`, ')') AS 'Concat', `year_released` FROM `movies`;

We get the following result

Getting a list of participants with their year of birth

Suppose we want to get a list of all members with their member number, full names, and year of birth, we can use the LEFT string function to extract the year of birth from the date of birth field. The script shown below helps us to do this.

SELECT `membership_number`,`full_names`,LEFT(`date_of_birth`,4) AS `year_of_birth` FROM members;

HERE

• The "LEFT(` date_of_birth`, 4)" string function LEFT takes a date of birth as a parameter and returns only 4 characters from the left.

• "AS` year_of_birth`" is the column alias that will be returned in our results. Note that the AS keyword is optional , you can leave it out and the query will still work.

Running the above query in MySQL Workbench against myflixdb gives us the results shown below.

SQL using MySQL Workbench

We will now use MySQL Workbench to generate a script that will display all the field names from our category table.

1. Right-click the category table. Click "Select Rows - Limit 1000"

2. MySQL Workbench will automatically generate the SQL query and paste it into the editor.

3. Query results will be shown

Note that we didn't write the SELECT statement ourselves. MySQL workbench generated this for us.

Why use SELECT SQL command when we have MySQL Workbench?

Now you might be thinking why learn the SQL SELECT command to query data from a database when you can just use a tool like the MySQL workbench to get the same results without knowing the SQL language. Of course it's possible, but learning how to use the SELECT command gives you more flexibility and control over SQL SELECT statements .

MySQL Workbench belongs to the " Query by Example " category of QBE tools . It is designed to help generate SQL statements faster to improve user productivity.

Learning the SQL SELECT command can allow you to create complex queries that cannot be easily generated with Query by Example utilities such as MySQL Workbench.