Trending September 2023 # Five Valuable Types Of Mysql Queries # Suggested October 2023 # Top 17 Popular

You are reading the article Five Valuable Types Of Mysql Queries updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Five Valuable Types Of Mysql Queries

Introduction to MySQL Queries

There are many kinds of SQL commands which can be categorized into the following:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

DDL (Data definition language)

DML (Data manipulation language)

DQL (Data query language)

DCL (Data control language)

TCL (Transaction control language)

In this article on MySQL Queries. We will discuss mostly DQL, which is “Data Query Language”. This comes into play when we try to fetch records from the database, starting with the “SELECT” command. Apart from this, we will also discuss the brief significance of other categories.

Types of MySQL Queries

Following are the five types of Queries are:

DDL

When we perform any changes to the physical structure of the table in the database, 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 is used to create a table or database.

Query: 

CREATE table employee;

2. ALTER: Used to modify or change values in the table.

Query:

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

Query: 

DROP TABLE IF EXISTS table_name;

5. TRUNCATE: This empties the records only and leaves the structure for future records.

Query: 

TRUNCATE TABLE employee; DML

As we can see, the name is Data Manipulation language, so once the tables/database are created, we require DML commands to manipulate something inside that stuff. The merits of using these commands are if any wrong changes happen, we can roll back/undo it.

1. INSERT: Used to insert new rows into the table.

Query: 

INSERT into employee Values(101,'abcd');

2. DELETE: Delete a single row or entire record in a table.

Query: 

DELETE TABLE employee;

3. UPDATE: Used to update existing records in a table.

Query: 

UPDATE employee SET col1 = new_col WHERE condition;

4. MERGE: Used to merge two rows.

DCL

It grants or revokes access of users to the database.

1. GRANT: Provides access to the users

Query:

GRANT CREATE table to user;

2. REVOKE: Take access back from the users

Query:

REVOKE CREATE table from user; TCL

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

Query:

ROLLBACK;

2. COMMIT: Used to deploy changes in the database

Query:

COMMIT; DQL

Data query language consists of only the SELECT command by which we can retrieve and fetch data based on 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

Query: 

SELECT * FROM table;

2. DISTINCT: Used to fetch all the unique values from a table

Query: 

SELECT DISTINCT col_name FROM table;

3. WHERE: Used forgiving conditions in the retrieval of records

Query: 

SELECT employee_id FROM employee WHERE name = 'stella';

4. COUNT: Used to get the number of records present in a table

Query: 

SELECT COUNT(*) FROM employee;

Query: 

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 wish to the top 5 students of a class, then after sorting the results, we can use this LIMIT by specifying five so that it will only fetch the top 5 records.

Query: 

SELECT first_name FROM student ORDER BY marks desc LIMIT 5;

(**ORDER BY used here for sorting value in descending order)

7. AND: If two conditions are given, and both are met for a record, then only the query will fetch that record.

Query: 

SELECT employee_id FROM employee WHERE name = 'stella' AND city = 'Bangalore';

8. OR: If two conditions are given, and one is met for a record, then that record will be fetched.

Query: 

SELECT employee_id FROM employee WHERE department = 'IT' OR city = 'Bangalore';

9. NOT: Used with conditions. Suppose we specify NOT before any conditions; records not meeting those conditions will be fetched.

Query: 

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.

Query: 

SELECT emp_id FROM employee WHERE emp_id BETWEEN 1 AND 10;

Query: 

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.

Query: 

SELECT * FROM employee WHERE employee_id IN (1001,1004,1008,1012);

12. LIKE: This operator uses the WHERE clause to search for a specified pattern in a string column. ‘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

Query: 

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

Query: 

SELECT SUBSTRING(customer_name,1,5) FROM customer_table;

(it will fetch character from the 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 based on some given conditions

Query: 

Condition always comes with a HAVING statement in the GROUP BY clause.)

Aggregated Function

16. SUM: Calculates the sum of values

Query: 

SELECT SUM(salary) FROM employee;

17. AVG: Calculates the average of a set of value

Query: 

SELECT AVG(salary) FROM employee;

18. MIN: Gets the minimum value in a set of values

Query: 

SELECT MIN(salary) FROM employee;

19. MAX: Gets the maximum value in a set of values

Query: 

SELECT MAX(salary) FROM employee; Joins

20. INNER JOIN: Returns records that have a matching value in both tables

Query: 

SELECT * FROM order INNER JOIN customer ON order.cust_id = customer.cust_id;

21. LEFT JOIN: Returns all records from the left table and the matched records from the right table

Query: 

SELECT * FROM order LEFT JOIN customer ON order.cust_id = customer.cust_id;

22. RIGHT JOIN: Returns all records from the right table and the matched records from the left table

Query: 

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 the left or right table

Query:

SELECT * FROM order FULL OUTER JOIN customer ON order.cust_id = customer.cust_id; Conclusion Recommended Articles

We hope that this EDUCBA information on “MySQL Queries” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

You're reading Five Valuable Types Of Mysql Queries

Update the detailed information about Five Valuable Types Of Mysql Queries on the Nhahang12h.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!