Open In App

MySQL | Common MySQL Queries

Last Updated : 14 Aug, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depends on MySQL data which are designed and optimized for such purpose. For all these reasons, MySQL server becomes the default choice for web applications. 

MySQL server is used for data operations like querying, sorting, filtering, grouping, modifying and joining the tables. Before learning the commonly used queries, let us look into some of the advantages of MySQL. 

Advantages of MySQL : 

  • Fast and high Performance database.
  • Easy to use, maintain and administer.
  • Easily available and maintain integrity of database.
  • Provides scalability, usability and reliability.
  • Low cost hardware.
  • MySQL can read simple and complex queries and write operations.
  • InnoDB is default and widely used storage engine.
  • Provides strong indexing support.
  • Provides SSL support for secured connections.
  • Provides powerful data encryption and accuracy.
  • Provides Cross-platform compatibility.
  • Provides minimized code repetition.

Queries can be understood as the commands which interacts with database tables to work around with data. Some of the commonly used MySQL queries, operators, and functions are as follows :  

1. SHOW DATABASES 

This displays information of all the existing databases in the server. 

Output:

Note : The databases ‘information_schema’, ‘mysql’ and ‘performance_schema’ are system databases which are used internally by MySQL server. A ‘test’ database is meant for testing purpose which is provided during installation. 

2. USE database_name 
database_name : name of the database 
This sets the database as the current database in the MySQL server. 
To display the current database name which is set, use syntax 

SELECT DATABASE(); 

3. DESCRIBE table_name 
table_name : name of the table 
This describes the columns of the table_name with respect to Field, Type, Null, Key, Default, Extra. 

4. SHOW TABLES 
This shows all the tables in the selected database as a information. 

5. SHOW CREATE TABLE table_name 
table_name : name of the table 
This shows the complete CREATE TABLE statement used by MySQL for creating the table. 

6. SELECT NOW() 
MySQL queries mostly starts with SELECT statement. 
This query shows the current date and time. 
Output : 

2019-09-24 07:08:30 

7. SELECT 2 + 4; 

Output : 

6 

This executes SELECT statement without any table. 
SELECT can be used for executing an expression or evaluating an in-built function. 
SELECT can also be used for more than one or many columns. 

Example : 

SELECT 2+4, CURDATE(); 

Output : 

8. Comments 
Comments are of two types. Multi-line comments or single-line or end-of-line comment. 

/*  These are multi-line comments.  */ 
 #  This is single-line comment.
 -- This is also single-line comment.

9. CREATE DATABASE database_name 
database_name : name of the database 
This statement creates a new database. 

10. DROP DATABASE database_name 
database_name : name of the database 
This statement deletes the database. 
Note : User has to be very careful before deleting a database as it will lose all the crucial information stored in the database. 

11. CREATE TABLE table_name(column1, column2, column3..) 
table_name : name of the table 
column1 : name of first column 
column2 : name of second column 
column3 : name of third column 
When the developer start building an application, he needs to create database tables. 
This statement creates a new table with the given columns. 

Example : 

CREATE TABLE  employee(
   'id' INTEGER  NOT NULL AUTO_INCREMENT,
   'name' VARCHAR(30) NOT NULL,
   'profile' VARCHAR(40) DEFAULT 'engineer',
   PRIMARY KEY ('id')
)ENGINE = InnoDB; 

Note : You have ‘id’ column as AUTO_INCREMENT with a primary key constraint which ensures that each id is incremented value, avoiding duplication. Storage engine selected is ‘InnoDB’ allowing foreign key constraint and related transactions. 

12. AUTO_INCREMENT 
It is used to generate a unique identification field for new row. 

13. DROP TABLE table_name 
table_name : name of the table 
This statement deletes the mentioned table. 

14. RENAME TABLE old_table_name TO new_table_name 
old_table_name : name of the previous table. 
new_table_name : name of the new table. 
This statement renames the table to a new name. 

15. ALTER TABLE table_name ADD(column1, column2, column3..) 
table_name : name of the existing table. 
column1 : name of first column. 
column2 : name of second column. 
column3 : name of third column. 
This statement adds columns to the existing table. 

16. ALTER TABLE table_name DROP(column1) 
table_name : name of the existing table. 
column1 : name of first column. 
This statement deletes specified columns from the existing table. 

17. INSERT INTO table_name (column1, column2, column3 . . ) VALUES(value1, value2, value3 . . ) 
table_name : name of the existing table. 
column1 : name of first column. 
column2 : name of second column. 
column3 : name of third column. 
value1 : value for first column. 
value2 : value for second column. 
value3 : value for third column. 
This statement inserts a new record into a table with specified values. 

18. UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3.. WHERE condition 
table_name : name of the table. 
column1 : name of first column. 
column2 : name of second column. 
column3 : name of third column. 
value1 : value for first column. 
value2 : value for second column. 
value3 : value for third column. 
condition : the condition statement. 
This statement update records in the table with the new given values for the columns. 

Note : WHERE clause in MySQL queries is used to filter rows for a specific condition. 

19. DELETE FROM table_name WHERE condition 
table_name : name of the table. 
condition : the condition statement. 
This statement deletes records from the table. 

20. SELECT column1, column2, column3.. FROM table_name WHERE condition 
table_name : name of the table. 
column1 : name of first column. 
column2 : name of second column. 
column3 : name of third column. 
condition : the condition statement. 
This statement executes and gives records from specific columns from the table which matches the condition after WHERE clause. 

21. SELECT * FROM table_name 
table_name: name of the table. 
Instead of specifying one column or many columns, you can use an asterisk (*) which represents all columns of table. This query retrieves all records from the table. 

22. COUNT 
The COUNT function is used to return total number of records matching a condition from any table. 
It is one of the known AGGREGATE function. 

Example : 

SELECT COUNT(*) from student; 

Note: AGGREGATE functions allow you to run calculations on data and provide information by using 
a SELECT query. 

23. MAX 
It is used to get the maximum numeric value of a particular column of table. 

Example : 

SELECT MAX(marks) FROM student_report;  

Note: The MIN and MAX functions work correctly on numeric as well as alphabetic values. 

24. MIN 
It is used to get the minimum numeric value of a particular column of table. 

Example : 

SELECT MIN(marks) FROM student_report;  

Note : The above given example queries can also be nested with each other depending on the requirement. 

Example : 

SELECT MIN(marks) 
FROM student_report 
WHERE marks > ( SELECT MIN(marks) from student_report); 

25. LIMIT 
It is used to set the limit of number of records in result set. 

Example : 

SELECT * 
FROM student limit 4, 10; 

This gives 10 records starting from the 5th record. 

26. BETWEEN 
It is used to get records from the specified lower limit to upper limit. 
This verifies if a value lies within that given range. 

Example : 

SELECT * FROM employee 
WHERE age BETWEEN 25 to 45. 

27. DISTINCT 
This is used to fetch all distinct records avoiding all duplicate ones. 

Example : 

SELECT DISTINCT profile 
FROM employee; 

28. IN clause 
This verifies if a row is contained in a set of given values. 
It is used instead of using so many OR clause in a query. 

Example : 

SELECT * 
FROM employee 
WHERE age IN(40, 50, 55); 

29. AND 
This condition in MySQL queries are used to filter the result data based on AND conditions. 

Example : 

SELECT NAME, AGE 
FROM student 
WHERE marks > 95 AND grade = 7; 

30. OR 
This condition in MySQL queries are used to filter the result data based on OR conditions. 

Example : 

SELECT * 
FROM student 
WHERE address = 'Hyderabad' OR address = 'Bangalore'; 

31. IS NULL 
This keyword is used for boolean comparison or to check if the data value of a column is null. 

Example : 

SELECT * 
FROM employee 
WHERE contact_number IS NULL; 

32. FOREIGN KEY 
It is used for pointing a PRIMARY KEY of another table. 

Example : 

CREATE TABLE Customers
 (
 id INT AUTO_INCREMENT PRIMARY KEY, 
 name VARCHAR(30) NOT NULL, 
)

CREATE TABLE Orders
(
 order_id INT AUTO_INCREMENT PRIMARY KEY,
 FOREIGN KEY (id) REFERENCES Customers(id)
 ); 

Note: This is not used in the MYISAM storage engine of MySQL server. 
InnoDB storage engines supports foreign key constraints. 

33. LIKE 
This is used to fetch records matching for specified string pattern. 

Example : 

SELECT * 
FROM employee 
WHERE name LIKE 'Sh%';

SELECT * 
FROM employee  
WHERE name LIKE '%Sh%';   

Note: Percentage signs (%) in the query represent zero or more characters. 

34. JOINS 
Joins are the joining of two or more database tables to fetch data based on a common field. 
There are various types of joins with different names in different databases. 
Commonly known joins are self join, outer join, inner join and many more. 

Regular Join : 
It is the join which gets all the records from both the tables which exactly match the given condition. 

Example : 

SELECT student.name, department.name 
FROM student JOIN department ON student.department = department.name 

Left Join : 
It is the join which gets all the records that match the given condition, and also fetch all the records from 
the left table. 

Example : 

SELECT student.name, department.name 
FROM student LEFT JOIN department ON student.deptartment = department.name 

Right Join : 
It is the join which gets all the records that match the given condition, and also fetch all the records from 
the right table. 

Example : 

SELECT student.name, department.name 
FROM student RIGHT JOIN department on student.department = department.name 

35. ADD or DROP a column 
A new column can be added on a database table, if required later on. 

Example : 

ALTER TABLE employee ADD COLUMN salary VARCHAR(25); 

Similarly, any column can be deleted from a database table. 

Example : 

ALTER TABLE employee DROP COLUMN salary; 

Conclusion :

Running MySQL queries are the most commonly-performed tasks used to get data within the process of database management. There are many database management tools like phpMyAdmin, that helps to perform queries and handle transactions comfortably with visual data results. You have scrolled the most common MySQL queries, which are used in daily coding practices. Developer can customize or enhance the queries with respect to a particular requirement.
 



Previous Article
Next Article

Similar Reads

MySQL | Recursive CTE (Common Table Expressions)
What is a CTE? In MySQL every query generates a temporary result or relation. In order to give a name to those temporary result set, CTE is used. A CTE is defined using WITH clause. Using WITH clause we can define more than one CTEs in a single statement. A CTE can be referenced in the other CTEs that are part of same WITH clause but those CTEs sho
5 min read
Queries using AND ,OR ,NOT operators in MySQL
AND, OR, NOT operators are basically used with WHERE clause in order to retrieve data from table by filtering with some conditions using AND, OR, NOT in MySQL.Here in this article let us see different queries on the student table using AND, OR, NOT operators step-by-step. Step-1:Creating a database university: CREATE DATABASE university; Step-2:Usi
2 min read
MySQL - Queries on Date Manipulation
In MySQL date is a data type and by default follows the format 'YYYY-MM-DD' but by using date formatting functions we can format date and can get the required format of date in the tables and can even manipulate the date by using different date functions. In this article let us execute some queries on date formatting in detail step-by-step: Creatin
2 min read
Identify and Kill Queries with MySQL Command
In SQL, some unnecessary processes can degrade your system's performance. Over time, threads pile up and stall your server, preventing users from accessing tables and executing requests. When resource usage is extremely high, you may need to kill MySQL processes. To do this, we first need to identify the processes that are running. We will use the
3 min read
Example Queries on Relational Algebra
Given below are a few examples of a database and a few queries based on that. (1). Suppose there is a banking database which comprises following tables : Customer(Cust_name, Cust_street, Cust_city) Branch(Branch_name, Branch_city, Assets) Account (Branch_name, Account_number, Balance) Loan(Branch_name, Loan_number, Amount) Depositor(Cust_name, Acco
1 min read
Types of Spatial Queries in DBMS
Any type of spatial data that is data related to location and which represents objects defined in a geometric space, is stored and maintained by Spatial Databases. These are used to handle these Spatial Databases. Spatial database mainly contain representation of simple geometric objects such as 3D objects, topological coverage, linear networks and
3 min read
SQL queries on FILM Database
Consider the following tables in Film Database: ARTIST (Art_id, Art_Name, Art_Gender) PRODUCER (Prod_id, Prod_Name, Prod_Phone) FILMS (Film_id, Film_Title, Film_Year, Film_Lang, Prod_id) CASTING (Art_id, Film_id, Part) REVIEW (Film_id, Stars) The data content of these five tables are shown below: SELECT * FROM ARTIST;Art_idArt_NameArt_Gender101AMIT
3 min read
Flashback Queries
Flashback Query allows users to see the view of past data, If in case some data or table is being deleted by the user, then the flashback query provides us an opportunity to view that data again and perform manipulations over it. In flashback queries we have an concept of flash area, in flash area we store the deleted data which can be viewed if ne
2 min read
Spatial Operators, Dynamic Spatial Operators and Spatial Queries in DBMS
1. Spatial operators : Spatial operators these operators are applied in geometric properties of objects. It is then used in the physical space to capture them and the relation among them. It is also used to perform spatial analysis. Spatial operators are grouped into three categories : Topological operators : Topological properties do not vary when
3 min read
Production databases in SQL queries
SQL is a Structured Query Language which is a computer language for storing, manipulating, and retrieving data stored in a relational database. SQL is the most powerful data handling tool. Actionable advice to help you get the most versatile language and create beautiful, effective queries. SQL is effectively used to insert, search, update, delete,
4 min read
Article Tags :