MySQL SELECT statement

➔ This is a very versatile command, allowing for data filtering, sorting, and complex calculations.

➔ The data retrieved by the SELECT statement is stored in a temporary location called the result-set, which is displayed as a table.

➔ MySQL allows you to use SELECT to perform mathematical calculations (such as SELECT 500*10/100;) or system functions (such as SELECT VERSION();) without referring to a table.

➔ If the SELECT clause is used with other clauses, the clauses must follow a specific order, otherwise the statement will fail.

SELECT syntax

SELECT column1, column2, ...
FROM table_name;

Non-Table SELECT syntax

SELECT expression1, expression1, ...;

Example: Sort employees by their salary from highest to lowest

SELECT employee_id, first_name, salary 
FROM hr.employees 
ORDER BY salary DESC;

MySQL SELECT clause with other clauses

Standard MySQL SELECT queries can generally be used with other clauses in the following order.

SELECT: Specifies the columns to be retrieved, and the * character is used to select all columns.

FROM: Specifies the name of the table from which data will be taken.

WHERE: Applies specific conditions to filter rows using operators such as =, >, LIKE, or BETWEEN.

GROUP BY: Groups the retrieved data according to one or more columns.

HAVING: Applies conditions using aggregate functions to filter data. Unlike WHERE, it uses aggregate functions such as SUM() or AVG().

ORDER BY: Sorts the final retrieved data in a predictable manner, either ascending (ASC) or descending (DESC) order.

LIMIT: Limits the number of rows returned, often used with OFFSET.

DISTINCT or Unique Values

DISTINCT: Excludes duplicate rows from the results.

Example: Find unique department_id from employees table.

SELECT DISTINCT department_id
FROM employees;

Column alias names in SELECT statement

An alias is an alternative, temporary, and descriptive name used for a column or table in output.

The AS keyword is used to give a column or table a name called an alias for better readability and reference purposes.

Example: Create a query to select first_name, last_name, and department_id from the employees table, where each selected column must have an alternate name.

SELECT first_name AS FirstName, last_name AS "Last Name", department_id Department_ID
FROM employees;

SELECT clause with WHERE clause to filter data

Example:

SELECT first_name, last_name, department_id 
FROM employees 
WHERE department_id= 50;

SELECT clause with ORDER BY clause to sort data

Example:

SELECT first_name, last_name,salary, department_id 
FROM employees 
WHERE department_id= 80
ORDER BY salary DESC;

SELECT with LIMIT

Select a specific number of records

Example: The query will return only the first 5 records from the table named employees.

SELECT first_name, last_name,job_id, department_id  
FROM employees LIMIT 5;

SELECT with LIMIT OFFSET

Example:

SELECT first_name, job_id, salary
FROM employees
LIMIT 5 OFFSET 10;

SELECT with LIMIT OFFSET (shorthand)

Example:

Here The first number is the offset (how many rows to skip), and the second is the count (how many rows to return)

SELECT first_name, job_id, salary 
FROM employees 
LIMIT 10, 5;

SELECT statement without a table name.

MySQL can use simple calculations, string or numeric operations and function calls as expressions in its SELECT clause, and it does not require specifying the table name.

MySQL can perform any calculation, arithmetic, or string operation directly within the query.

Example:

SELECT 512 + 1024;  /* Simple mathematical calculation */
SELECT CONCAT('Steven', ' ', 'King') AS FullName; /* String operation */
SELECT NOW();  /* Function call */

CREATE TABLE ... SELECT syntax:(Create new table with data)

Use CREATE TABLE ... SELECT to create a new table and add data to it.

This SQL code will create a new table from an existing table and populate the new table with data from the existing table.

Example:

CREATE TABLE employees_clone 
SELECT * FROM employees

INSERT INTO ... SELECT: (Insert data into a table from existing table)

Use INSERT INTO ... SELECT syntax structure to insert data into an table from an existing table.

This SQL code will load data from the existing table to the new table.

Example:

INSERT INTO table_name (first_name, email)
SELECT first_name, email FROM existing_table_name WHERE department_id = 80; /* Filter is optional */

Create a table from an existing table: (Create a new table without data)

This SQL code will create a new table from the existing table, but will not populate data from the previous table to the new table.

Example:

/* -- The WHERE 1=0 clause is a "false" condition, which ensures that
the SELECT statement will return zero rows. Since no rows are
returned, no data is inserted into the new table, leaving the table empty. */
CREATE TABLE new_table_name AS SELECT * FROM existing_table_name WHERE 1=0;
-- OR
CREATE TABLE new_table_name AS SELECT * FROM existing_table_name LIMIT 0;
-- OR
CREATE TABLE new_table_name LIKE existing_table_name;
-- OR
/* This command will show create table code for new table */
SHOW CREATE TABLE existing_table_name \G

SELECT ... INTO @userVariable syntax

This code will copy data from a query directly to a file or user-defined variable.

Example:

Step1: /* User defined variable: */
SET @userName = '';  

Step2: /* Copy data to User defined variable: */
SELECT first_name INTO @userName 
FROM employees 
WHERE employee_id = 100;

Step3: /* Show user defined variable: */
SELECT @userName;

SELECT ... INTO OUTFILE syntax (Data Backup)

The following code will create a backup file of the data.

Note: For windows system use forward slashes (/) or doubled backslashes (\\) in the file path to prevent escape character errors.

Example:

Step1:
SHOW VARIABLES LIKE "secure_file_priv";
C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\

Step2:
SELECT * FROM employees
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\employees_backup.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Alternative Method (Data Backup)

If the INTO OUTFILE command cannot be used due to path restrictions, piped output from the command line can be used instead.

For windows file system Use forward slashes (/) or double backslashes (\\) in file paths to avoid escape character errors.

Example:

/*  All of the syntaxes below are valid, so use any of them. */
mysql -u root -p -e "SELECT * FROM hr.employees" > "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employees_backup.csv"  /* valid */
mysql -u root -p -e "SELECT * FROM employees" hr > "C:/Users/devops/Desktop/output2.csv" /* valid */
mysql -u root -p -e "SELECT * FROM hr.employees" > "C:/Users/devops/Desktop/output.csv"  /* valid */
mysql -u root -p -e "SELECT * FROM hr.employees" > "C:\\Users\\devops\\Desktop\\output.csv"  /* valid */

/*  If you need a complete backup of the table structure and data, use mysqldump to create an .sql file. */
mysqldump -u root -p hr employees > "C:/Users/devops/Desktop/output.sql"  /* valid */