Introduction

MySQL is a top and popular database used by large to small businesses in the corporate world. It is an easy peasy database and beginner’s first choice as it is simple to maintain and handle. The commands to query the database are very straightforward. This database software is quite efficient and faster than other databases, due to which the statistics of this MySQL is increasing almost daily.

Types of MySQL Queries

In the MySQL database, there are two types of statements in which the commands are segregated accordingly.

1. DDL (Data Definition Language)

Data Definition Language statements are nothing but the set of statements used to construct a schema.

Some examples: CREATE, ALTER, DROP, TRUNCATE.

2. DML (Data Manipulation Language)

The Data Manipulation Language is used to retrieve, insert and modify database information.

Here is a list of commonly used queries to command the database:

1) MySQL Create Database

Syntax:

mysql>
CREATE DATABASE database_name;

 

2) MySQL Select/Use Database

Syntax:

mysql> USE database_name;

 

3) MySQL Create Query

Syntax:

mysql>
CREATE TABLE table_name
(
column_name1 data-type1,
column_name2 data-type2,
column_name3 data-type3,
.
.
,
column_nameN data-typeN);

 

4) MySQL Alter Query

Syntax:

mysql>
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
ALTER TABLE table_name
CHANGE COLUMN oldcolumn newcolumn datatype;
ALTER TABLE table_name
RENAME TO new_table_name;

 

5) MySQL Insert Query

syntax:

mysql>
INSERT INTO table_name
VALUES (value list); -------> single value into the table
INSERT INTO table_name
VALUES (& value1, &value2,....,valueN);

 

6) MySQL Update Query

syntax:

mysql>
UPDATE table_name
SET value
WHERE condition;

 

7) MySQL Delete Query

syntax:

mysql>
DELETE FROM table_name
WHERE condition;

 

8) MySQL Select Query

syntax:

mysql>
SELECT field1, field2,....fieldN
FROM table_name1, table_name2
WHERE condition;

 

To retrieve all the data at a time

syntax:

mysql> SELECT * FROM table_name;

 

 

9) MySQL Truncate Table Query

syntax:

mysql>
TRUNCATE TABLE table_name;

 

10) MySQL Drop Query

syntax:

mysql>
DROP TABLE table_name;

 

These are a few frequently used commands and basic operations that are performed on databases or tables.

Examples of Important MySQL Queries

The following is a list of commonly used MySQL queries for creating databases, using databases, creating tables, inserting records, updating records, deleting records, selecting records, truncating tables, and dropping tables.

INSERT Record

The INSERT statement in MySQL is used to store or add data to a MySQL table. In MySQL, we can insert entries in one of two ways using a single query:

1. Insert a single row of records.
2. Insert several rows of records

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )    
VALUES    
( value1, value2,...valueN );

 

Use the following statement if we wish to insert numerous records in a single command:

Syntax:

INSERT INTO table_name VALUES  
( value1, value2,...valueN )  
( value1, value2,...valueN )  
...........  
( value1, value2,...valueN );

 

Example:

INSERT into customers values(101,'raju','Mumbai');

 

Example:

INSERT INTO People VALUES  
(102, 'Joseph', 'Developer', 30),  
(103, 'Mike', 'Leader', 28),  
(104, 'Stephen', 'Scientist', 45);

 

Output

UPDATE Record

The following is a generic syntax for the UPDATE command, which is used to edit data in MySQL.

Syntax:

UPDATE table_name     
SET column_name1 = new-value1,   
        column_name2=new-value2, ...    
[WHERE Clause] ;

 

A MySQL update query is used to update table records.

Example:

UPDATE trainer    
SET email = 'mike@tutorialandexamples.com'    
WHERE course_name = 'Java';

 

Output

 

DELETE Record

We won’t be able to recover the records we deleted with this query. As a result, it’s a good idea to back up your database before deleting any records from the table. We can restore data from database backups anytime we need it in the future.

The syntax that demonstrates how to perform the DELETE command is as follows:

DELETE FROM table_name WHERE condition;

 

Example:

mysql> DELETE FROM Employees WHERE emp_id=107;

 

Output

 

SELECT Record

In MySQL, the SELECT command is used to get data from one or more tables. Using this statement, we can obtain records from all fields or specified fields that match defined criteria. It also supports a variety of programming languages, including PHP, Ruby, and others.

Syntax:

SELECT field_name1, field_name 2,… field_nameN

FROM table_name1, table_name2…

[WHERE condition] [GROUP BY field_name(s)] [HAVING condition] [ORDER BY field_name(s)] [OFFSET M ][LIMIT N];

Example:

SELECT * FROM table_name;

mysql> SELECT Name, Email, City FROM employee_detail;

Output

mysql> SELECT * FROM employee_detail;