MySQL − Queries

---

---

MySQL is an open-source relational management system (RDBMS) that allows us to store and manage data or information. The queries in MySQL are commands that are used to retrieve or manipulate the data from a database table.

Following are the commonly used commands in MySQL: SELECT, UPDATE, DELETE, INSERT INTO, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE DATABASE, ALTER DATABASE, CREATE INDEX, DROP INDEX, etc.

Note: These keywords are not case-sensitive. For instance, create table is the same as CREATE TABLE.

MySQL Create Database

The create database query in MySQL can be used to create a database in the MySQL server.

Syntax

Following is the syntax for the query −

CREATE DATABASE databasename;

Example

In the following query, we are creating a database named tutorials.

CREATE DATABASE tutorials;

MySQL Use Database

The MySQL use database query is used to select a database to perform operations such as creating, inserting, updating tables or views, etc.

Syntax

Following is the syntax for the query −

USE database_name;

Example

The following query selects a database named tutorials −

USE tutorials;

MySQL Create Query

The MySQL create query can be used to create databases, tables, indexes, views, etc.

Syntax

Following is the syntax for the query −

CREATE [table table_name |index index_name | view view_name];

Example

Here, we are creating a table named STUDENTS using the following CREATE query −

CREATE TABLE CUSTOMERS (
   ID int,
   NAME varchar(20),
   AGE int,
   PRIMARY KEY (ID)
);

MySQL Insert Query

The MySQL insert query can be used to insert records within a specified table.

Syntax

Following is the syntax for the query −

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example

In the following query, we are inserting some records into a table named CUSTOMERS −

INSERT INTO CUSTOMERS (ID, NAME, AGE) VALUES (1, "Nikhilesh", 28);
INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (2, "Akhil", 23);
INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (3, "Sushil", 35);

MySQL Update Query

The MySQL update query can be used to modify the existing records in a specified table.

Syntax

Following is the syntax for the query −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE CUSTOMERS SET NAME = "Nikhil" WHERE ID = 1;

MySQL Alter Query

The ALTER query in MySQL can be used to add, delete, or modify columns in an existing table.

Syntax

Following is the syntax for the query −

ALTER TABLE table_name
[ADD|DROP] column_name datatype;

Example

Here, we are trying to add a column named ADDRESS to the existing CUSTOMERS table.

ALTER TABLE CUSTOMERS
ADD COLUMN ADDRESS varchar(50);

MySQL Delete Query

The Delete query in MySQL can be used to delete existing records in a specified table.

Syntax

Following is the syntax for the query −

DELETE FROM table_name WHERE condition;

Example

In the following query, we are deleting a record from CUSTOMERS table where the ID is equal to 3.

DELETE FROM CUSTOMERS WHERE ID = 3;

MySQL Truncate Table Query

The MySQL truncate table query can be used to remove all the records but not the table itself.

Syntax

Following is the syntax for the query −

TRUNCATE [TABLE] table_name; 

Example

In the following query, we are removing all the records from the CUSTOMERS table using the truncate table query −

TRUNCATE TABLE CUSTOMERS;

MySQL Drop Query

The MySQL drop query is used to delete an existing table in a database.

Syntax

Following is the syntax for the query −

DROP TABLE table_name;

Example

Here, we are trying to delete the table named CUSTOMERS using the drop table query.

DROP TABLE CUSTOMERS;