Skip to content
Related Articles

Related Articles

CRUD Operations in MySQL
  • Difficulty Level : Expert
  • Last Updated : 24 Nov, 2020

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 Interger

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
My Personal Notes arrow_drop_up
Recommended Articles
Page :