CRUD Operations in MySQL

As we know that we can use MySQL to use Structure Query Language to store the data in the form of RDBMS. SQL is the most popular language for adding, accessing, and managing content in a database. It is most noted for its quick processing, proven reliability, ease, and flexibility of use. The application is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications. The most common use for MySQL, however, is for the purpose of a web database. 

MySQL provides a set of some basic but most essential operations that will help you to easily interact with the MySQL database and these operations are known as CRUD operations. 

1. Create Table Command :

Syntax :

CREATE TABLE table_name (column_name column_type constraints);

Parameters :

1. column_name – 
   Name of the particular column with any space.
2. column_type – 
   Datatype of the column. Datatype depends upon the data of the reference column. Datatype can be – char(), varchar(), int(), float(), etc.
3. constraints – 
   In order to give restrictions to particular column constraints are used. Constraints can be – not null, primary key, foreign key, etc. These are the keywords which give set of restriction to the particular column.

Database – GFG 
Table – Student 
Student –

• name Varchar(30) NOT NULL
• marks Integer

Example : 
use <database> command must be used before any operation on the table. 

use gfg;
Create table student(name Varchar(30) NOT NULL, marks Integer);

Output :

Field	Type	Null	Default
name	varchar(30)	No	Null
marks	int(11)	YES	Null

2. Read Operation :
The Read operations are used to retrieve the content of the table from a particular database. Read operation is done by DDL commands. 

Example :

use gfg;
select * from student;

name	marks
ravi	23
swati	33
kranti	12

3. Update Operation :
Altering the content of the table or the structure of the table is done with the help of Update Operations. Two Commands are mostly used for Update Operation – 

1. Alter Table Command –
   This is the DDL command (Data Definition Language) used to change the structure of the table. 
2. Update Table Command –
   This is the DML command(Data Manipulating Language) used to alter the records. 

Alter Table Command that change the size of name column from varchar(40) to varchar(50) for the Student table : 

Alter table student 
modify name varchar(50) not null;

Original Table – 

desc student;

Field	Type	Null	Default
name  marks	varchar(40)  int(11)	YES  YES	Null  Null

After altering the table –

desc student;

Field	Type	Null	Default
name  marks	varchar(50)  int(11)	YES  YES	Null  Null

Update Command that update the marks of the student from 23 to 100 whose name is ravi using the update command : 

Update student set marks = 100 
where name = "ravi";

Original Table – 

select * from student;

name	marks
ravi	23
swati	33
kranti	12

After updating the table –

select * from student;

name	marks
ravi	100
swati	33
kranti	12

4. Delete Operation : 
Two commands are mostly used for the Delete operations – 

1. Delete Command –
   (DML command) works on the records of the table. 
2. Drop Command –
   (DDL command) works on the structure of the table. 
   

Delete Command that delete the records of students having marks equal to 100 : 

delete from student 
where marks = 100;

Original Table –

select * from student;

name	marks
ravi	100
swati	33
kranti	12

After deleting the student records – 

select * from student;

name	marks
swati	33
kranti	12

Drop Command that drop the table student : 

drop table student;

Original Structure –

use gfg;
show tables;

Tables_in_gfg
student

After dropping the student table – 

use gfg;
show tables;

Tables_in_gfg