Introduction to MySQL Queries
There are many kinds of SQL commands which can be categorized into the following:
- DDL (Data definition language)
- DML (Data manipulation language)
- DQL (Data query language)
- DCL (Data control language)
- TCL (Transaction control language)
In this article of MySQL Queries. We are going to discuss mostly DQL which is “Data Query Language”. This comes to play when we try to fetch records from the database and it starts with the “SELECT” command. Apart from this, we will discuss the brief significance of other categories as well.
Types of MySQL Queries
Following are the 5 types of Queries are:
When we perform any changes with the physical structure of the table in the database, then we need DDL commands. CREATE, ALTER, RENAME, DROP, TRUNCATE, etc commands come into this category. Those commands can’t be rolled back.
1. CREATE: It used to create a table or database.
CREATE table employee;
2. ALTER: Used to modify or change values in the table.
ALTER TABLE table_name ADD COLUMN col_name;
3. RENAME: Rename the table or database name.
ALTER TABLE table_name RENAME COLUMN col_name TO new_col_name;
4. DROP: This removes records of a table as well as the structure of a table. This can’t be rolled back/undo
DROP TABLE IF EXISTS table_name;
5. TRUNCATE: This empties the records only, leaves the structure for future records.
TRUNCATE TABLE employee;
As we can see the name Data Manipulation language, so once the tables/database are created, to manipulate something inside that stuff we require DML commands. Merits of using these commands are if incase any wrong changes happened, we can roll back/undo it.
1. INSERT: Used to insert new rows into the table.
INSERT into employee Values(101,'abcd');
2. DELETE: Used to delete a single row or entire records in a table.
DELETE TABLE employee;
3. UPDATE: Used to update existing records in a table.
UPDATE employee SET col1 = new_col WHERE condition;
4. MERGE: Used to merge two rows.
It grants or revokes access of users to the database.
1. GRANT: Provides access to the users
GRANT CREATE table to user;
2. REVOKE: Take access back from the users
REVOKE CREATE table from user;
This manages the issues related to the transaction in any database. This is used to rollback or commit in the database.
1. ROLLBACK: Used to cancel or undo changes made in the database
2. COMMIT: Used to deploy changes in the database
Data query language consists of only SELECT command by which we can retrieve and fetch data on the basis of some conditions provided. Many clauses of SQL are used with this command for retrieval of filtered data.
1. SELECT: Used to fetch all the records from a table
SELECT * FROM table;
2. DISTINCT: Used to fetch all the unique values from a table
SELECT DISTINCT col_name FROM table;
3. WHERE: Used forgiving conditions in the retrieval of records
SELECT employee_id FROM employee WHERE name = 'stella';
4. COUNT: Used to get the number of records present in a table
SELECT COUNT(*) FROM employee;
5. ORDER BY: Used to sort both numeric and string values either in ascending or descending manner. But by default, it sorts in an ascending manner. If we want in descending, then we need to specify it after using the ORDER BY clause.
SELECT first_name FROM student ORDER BY marks desc;
6. LIMIT: This is used to specify the number of records we want after executing the query. If we want the top 5 students of a class, then after sorting the results, we can use this LIMIT by specifying 5. So that it will only fetch the top 5 records.
SELECT first_name FROM student ORDER BY marks desc LIMIT 5;
(**ORDER BY used here for sorting value in descending order)
7. AND: If 2 conditions are given and both are met for a record, then only the query will fetch that records.
SELECT employee_id FROM employee WHERE name = 'stella' AND city = 'Bangalore';
8. OR: If 2 conditions are given and one of them is met for a record, then that record will be fetched.
SELECT employee_id FROM employee WHERE department = 'IT' OR city = 'Bangalore';
9. NOT: Used with conditions. If we specify NOT before any conditions, records that are not meeting that conditions will be fetched.
SELECT employee_id FROM employee WHERE NOT BETWEEN 1 AND 10;
10. BETWEEN: This operator selects records within a given range. Mostly we use this where we want to specify a range of dates.
SELECT emp_id FROM employee WHERE emp_id BETWEEN 1 AND 10;
SELECT * FROM employee WHERE join_date BETWEEN '2007-01-01' AND '2008-01-01';
11. IN: This operator allows us to specify multiple values in a WHERE clause.
SELECT * FROM employee WHERE employee_id IN (1001,1004,1008,1012);
12. LIKE: This operator is used with the WHERE clause to search for a specified pattern in a column containing a string. ‘A%’ – string starts with A
- ‘&A’: ends with A
- ‘%A%’: A will be in between the string
- ‘_A%’: Here the 2nd letter will be A
- ‘%A_’: The 2nd from the last letter will be A
SELECT first_name FROM table WHERE first_name LIKE 'A%';
13. SUBSTRING: Used to pick a specific character from a string by specifying a position
SELECT SUBSTRING(customer_name,1,5) FROM customer_table;
(it will fetch character from 1st to 5th position of a string)
14. INSTR: This returns a position of a string in another string
SELECT INSTR('independence', 'pen');
(it will find the position of ‘pen’ in the word ‘independence’)
15. GROUP BY: This is used to segregate records on the basis of some given conditions
SELECT employee_id FROM employee GROUP BY department HAVING salary > 100000;
(Here group by segregated employees on the basis of their department and whose salary more than 100k.
Condition always comes with HAVING statement in GROUP BY clause.)
16. SUM: Calculates sum of values
SELECT SUM(salary) FROM employee;
17. AVG: Calculates the average of set of value
SELECT AVG(salary) FROM employee;
18. MIN: Gets the minimum value in a set of values
SELECT MIN(salary) FROM employee;
19. MAX: Gets the maximum value in a set of values
SELECT MAX(salary) FROM employee;
20. INNER JOIN: Returns records that have a matching value in both tables
SELECT * FROM order INNER JOIN customer ON order.cust_id = customer.cust_id;
21. LEFT JOIN: Returns all records from left table and the matched records from the right table
SELECT * FROM order LEFT JOIN customer ON order.cust_id = customer.cust_id;
22. RIGHT JOIN: Returns all records from right table and the matched records from the left table
SELECT * FROM order RIGHT JOIN customer ON order.cust_id = customer.cust_id;
23. FULL OUTER JOIN: Returns all the records when there is a match in either left or right table
SELECT * FROM order FULL OUTER JOIN customer ON order.cust_id = customer.cust_id;
These commands and clauses we have discussed above are very useful in real-time scenarios as it provides the basic concepts of how to use SQL queries to fetch and manipulate data in the database. Apart from this, while using advance and analytical queries like window function etc, these clauses are very important.
This is a guide to MySQL Queries. Here we discuss the basic concept, types of Queries in MySQL which include DDL, DML, DQL, and DCL, TCL. You can also go through our other suggested articles to learn more –