MySQL Syntax

MySQL is a relational database management system provided by Oracle Corporation that keeps data in the form of tables. Developers prefer MySQL database because of its robustness, security, flexibility & scalability.

SQL is the programming language particularly designed to run commands that let you create and manipulate the MySQL database.

Knowing the correct MySQL syntax for anybody who works with MySQL databases is essential. In this article, we will understand the MySQL syntax and see the required statements for performing common database operations.

What is Syntax?

The syntax of a programming language is a set of rules that describes its structure.

What is MySQL Syntax?

MySQL syntax is the collection of rules that describes the structure of SQL language in the context of MySQL database management system. There are many statements, clauses, and functions within MySQL syntax that are used for tasks such as creating, retrieving, updating, and deleting data in MySQL.

Remember that the syntax may differ slightly depending on the MySQL version you are using. There are a variety of tools available that you can use for executing MySQL commands, such as the MySQL command line client, MySQL Workbench, phpMyAdmin, etc.

MySQL syntax comprises several types of statements that permit you to perform different operations.

Main Components of MySQL Syntax

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)

Let us understand the components of MySQL syntax one by one.

I. Data Definition Language (DDL):

DDL is the part of MySQL syntax that helps the user to define and change data structures and objects, such as tables, constraints, indexes, etc. It is also used to store metadata information such as the number of tables, table names, column names, columns within each table, etc.

Some of the frequently used commands which come under DDL with their respective syntax are as follows:

1. CREATE STATEMENT

As its name suggests, it is utilized for the creation of databases and database objects such as tables.

For creating a database, it is necessary to use the following syntax:

It would help if you used the following syntax for creating a new table in the database:

CREATE TABLE tableName(
columnName1 data_type,
columnName2 data_type,
columnName3 data_type,
......
);

Example:

The command given below will create a table called "Employees". The table has fields for employeeId, employeeName, employeeAge, and employeeDepartment.

CREATE TABLE Employees (
    employeeId INT PRIMARY KEY,
    employeeName VARCHAR(40),
    employeeAge INT,
    employeeDepartment VARCHAR(90)
);

2. ALTER STATEMENT

It is used for altering the structure of database entities, such as adding or deleting columns from the table.

It would help if you used the given syntax for adding another column in the table:

ALTER TABLE name_of_table
ADD name_of_column data_type;

Example:

The below command adds a new column named "employeeAddress" to the "Employees" table, whose data type is VARCHAR(81).

ALTER TABLE Employees
ADD employeeAddress VARCHAR(81);

For removing a column from the table, you can use the given syntax:

ALTER TABLE name_of_table
DROP COLUMN name_of_column;

Example:

The below command drops a column named "employeeAge" from the "Employees" table.

ALTER TABLE Employees
DROP COLUMN employeeAge;

3. DROP TABLE STATEMENT

It is used for deleting the whole table from the MySQL database.

You can use the given statement to drop a table from the MySQL database:

4. CREATE INDEX STATEMENT

It is used to create an index on the columns of the table.

For creating an index on a table, you can use the given syntax:

CREATE INDEX name_of_index
ON name_of_table (columnName);

5. DROP INDEX STATEMENT

It is used to remove the index from the MySQL database.

You can use the given syntax to remove an index:

DROP INDEX name_of_index
ON name_of_table;

6. TRUNCATE STATEMENT

It is utilized to clear all records from the table while preserving the structure of the table.

You can use the given syntax to remove all records:

7. RENAME STATEMENT

It is used to give a new name to a table in the database.

You can use the given command to rename a table:

II. Data Manipulation Language (DML):

DML is the component of MySQL syntax that is used to handle the data present in the database. It is also known as Query Language. It allows users to perform operations like inserting, updating, and deleting data. Some common DML statements with their syntax are as follows:

1. INSERT INTO STATEMENT

It is used for inserting a new record into a table.

You can use the given syntax to insert a new record:

INSERT INTO name_of_table(Column1, Column2, ...)
VALUES (Value1, Value2, ...);

2. SELECT STATEMENT:

It is used for retrieving data from the MySQL database.

You can use the given syntax to retrieve the data:

SELECT Column1, Column2, ... 
FROM name_of_table WHERE Condition;

3. UPDATE STATEMENT:

It is utilized to update records in a table.

You can use the given syntax to update a record:

UPDATE name_of_table
SET Column1 = Value1, Column2 = Value2, ...
WHERE Condition;

4. DELETE STATEMENT:

It is used to clear a record from a table.

You can use the given syntax to delete a record:

III. Data Control Language (DCL):

DCL is the component of MySQL syntax that is used to control the granting and revoking of user access to the database. We need certain privileges to perform any operation, such as creating a table, inserting data in a table, etc.

The authorizations that can be granted or revoked from a user apply to both the DDL and DML. Some common DCL statements with their syntax are as follows:

1. GRANT STATEMENT

It is used to give specific privileges to a user to access and handle the database.

You can use the given syntax to grant user access:

GRANT name_of_privilege
ON name_of_object TO username;

2. REVOKE STATEMENT

It is utilized to take back specific privileges from the user.

You can use the given syntax to revoke user access:

REVOKE name_of_privilege
ON name_of_object FROM username;

IV. Transaction Control Language (TCL):

TCL is the component of MySQL syntax that is used to handle the transactions in the database. Some common TCL statements with their syntax are as follows:

1. COMMIT STATEMENT

It is used for permanently saving all the changes made during the current transaction.

You can use the given syntax to save the transaction permanently:

2. ROLLBACK STATEMENT

It is used to revert the database to its previous state before initiating a transaction.

You can use the given syntax to restore the database:

3. SAVEPOINT STATEMENT

It is used to save the transactions temporarily, which you can roll back later if necessary.

You use the given syntax to save the transaction temporarily:

4. RELEASE SAVEPOINT STATEMENT

It is used to remove a previously defined SAVEPOINT.

You can use the given syntax to do so:

5. ROLLBACK TO SAVEPOINT STATEMENT

It is used to restore changes made after a specific SAVEPOINT.

You can use the given command to do so:

Conclusion

In this article, we have learned MySQL Syntax. It is vital to understand the MySQL syntax if you want to interact with the MySQL database. MySQL syntax has four main components: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). We have understood these four components, the commands that come under them, and their syntax.

Next TopicMySQL 8.0

← prev next →