MySQL | Common MySQL Queries

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.
 

G

geetanjali16

Follow

Improve

Article Tags :

• DBMS
• mysql
• SQLmysql