MySQL - Select Query - Display Records
A SELECT query is used to retrieve data from a database in MySQL. It allows you to specify the columns you want to retrieve, the table or tables to retrieve them from, and any filtering or sorting criteria to apply to the data.
You can use SELECT queries in MySQL for a variety of purposes, such as:
- Retrieving specific data from one or more tables.
- Filtering data based on certain criteria.
- Sorting data in ascending or descending order.
- Calculating aggregate values like sum, average, count, etc.
- Joining multiple tables to combine related data.
Suppose we have a table named "users" with the following columns: id, name, email, and age.
To retrieve all the data from the table "users", we can use the following query:
SELECT * FROM users;
This will return all the columns and rows from the "users" table.
If we only want to retrieve specific columns, we can specify them in the SELECT statement. For example, to retrieve only the name and email columns from the "users" table, we can use the following query:
SELECT name, email FROM users;
This will return only the name and email columns for all the rows in the "users" table.
We can also filter the results by using a WHERE clause. For example, to retrieve only the users who are 18 years old, we can use the following query:
SELECT * FROM users WHERE age = 18;
This will return all the columns and rows where the age column is equal to 18.
These are just some basic examples of how to use the SELECT statement in MySQL. The SELECT statement is a powerful tool and can be used in many different ways to retrieve and manipulate data from a database.
AND and OR: These operators allow you to combine multiple conditions in a WHERE clause. The AND operator returns only the rows that meet all of the conditions, while the OR operator returns the rows that meet any of the conditions.
This query retrieves all the rows from the "users" table where the "age" column is greater than 25 and the "name" column is equal to 'John'.
SELECT * FROM users WHERE age > 25 AND name = 'John';
This query retrieves all the rows from the "users" table where the "age" column is greater than 25 or the "name" column is equal to 'John'.
SELECT * FROM users WHERE age > 25 OR name = 'John';
IN: The IN operator allows you to specify multiple values for a column in a single query. For example, you can use the IN operator to retrieve all rows where the value in a specific column matches one of several specified values.
This query retrieves all the rows from the "users" table where the "age" column is equal to 25, 30, or 35.
SELECT * FROM users WHERE age IN (25, 30, 35);
NOT: The NOT operator can be used to negate a condition. For example, you can use NOT to retrieve all the rows where a specific column does not
This query retrieves all the rows from the "users" table where the "age" column is not equal to 25.
SELECT * FROM users WHERE NOT age = 25;
NOT IN: The NOT IN operator allows you to exclude rows that match a list of values.
This query retrieves all the rows from the "users" table where the "age" column is not equal to 25, 30, or 35.
SELECT * FROM users WHERE age NOT IN (25, 30, 35);
NULL: The NULL operator allows you to match rows where a column has a null value.
This query retrieves all the rows from the "users" table where the "email" column is null.
SELECT * FROM users WHERE email IS NULL;
NOT NULL: The NOT NULL operator allows you to match rows where a column has a non-null value.
This query retrieves all the rows from the "users" table where the "email" column is not null.
SELECT * FROM users WHERE email IS NOT NULL;
LIKE: The LIKE operator is used to perform pattern matching against text data in a column.
In this query, we're selecting all rows from the "users" table where the "name" column starts with the letters "John". The % symbol is a wildcard character that matches any number of characters. So, in this case, any name that starts with "John" followed by any number of characters will be returned.
SELECT * FROM users WHERE name LIKE 'John%';
In this query, we're selecting all rows from the "users" table where the "name" column contains the letters "John" anywhere in the name. The % symbol is used before and after the search term to match any characters before or after "John".
SELECT * FROM users WHERE name LIKE '%John%';
In this query, we're selecting all rows from the "users" table where the "email" column matches any single character followed by "@gmail.com". The underscore (_) is a wildcard character that matches any single character.
SELECT * FROM users WHERE email LIKE '[email protected]';
The LIMIT clause in MySQL is used to restrict the number of rows returned by a SELECT query. This can be useful when you have a large number of rows in your table and you only want to display a certain number of them at a time, or when you want to limit the amount of data transferred between the server and the client.
This query retrieves the first 10 rows from the "users" table.
SELECT * FROM users LIMIT 10;
This query retrieves 10 rows starting from the 6th row (offset 5) from the "users" table. The first number in the LIMIT clause is the offset, and the second number is the number of rows to return.
SELECT * FROM users LIMIT 5, 10;
This query retrieves the first 5 rows from the "users" table, ordered by the "name" column in ascending order.
SELECT * FROM users ORDER BY name ASC LIMIT 5;
This query retrieves 10 rows starting from the 6th row (offset 5) from the "users" table where the "age" column is greater than 30.
SELECT * FROM users WHERE age > 30 LIMIT 5, 10;
This query retrieves the first 10 distinct values from the "city" column of the "users" table.
SELECT DISTINCT city FROM users LIMIT 10;
The ORDER BY clause in MySQL is used to sort the rows returned by a SELECT query. It allows you to specify one or more columns to sort by, and whether to sort in ascending or descending order.
This query retrieves all rows from the "users" table and orders them by the "name" column in ascending order.
SELECT * FROM users ORDER BY name;
This query retrieves all rows from the "users" table and orders them by the "age" column in descending order.
SELECT * FROM users ORDER BY age DESC;
This query retrieves all rows from the "users" table and orders them by the "city" column in ascending order, and then by the "name" column in descending order. If two rows have the same value for the "city" column, they will be ordered by the "name" column.
SELECT * FROM users ORDER BY city ASC, name DESC;
This query retrieves the "name" and "age" columns from the "users" table where the "age" column is greater than 30, and orders the results by the "age" column in descending order.
SELECT name, age FROM users WHERE age > 30 ORDER BY age DESC;
DISTINCT: The DISTINCT keyword in MySQL is used to retrieve unique values from a column in a SELECT query. It filters out duplicate values and only returns the unique values.
This query retrieves all the unique values from the "city" column of the "users" table.
SELECT DISTINCT city FROM users;
COLUMN ALIAS: Column aliases in MySQL are used to rename a column in the output of a SELECT query. This can be useful when you want to change the name of a column to something more meaningful or when you want to make the column names more readable.
This query retrieves the "name" and "age" columns from the "users" table, but renames them to "full_name" and "years_old" in the output.
SELECT name AS full_name, age AS years_old FROM users;
NUMERIC CALCULATIONS: You can perform numeric calculations on columns in MySQL by using mathematical operators such as +, -, *, /, and %.
This query retrieves the "name" and "age" columns from the "users" table, but also creates a new column called "double_age" that is calculated as the "age" column multiplied by 2.
SELECT name, age, age * 2 AS double_age FROM users;
This query retrieves the "name" and "age" columns from the "users" table, but only returns the rows where the "age" column multiplied by 2 is greater than 50.
SELECT name, age FROM users WHERE age * 2 > 50;
