CRUD operation

In this article, we will review the basic usage for making a CRUD (Create, Read, Update, Delete) functionality and the most-used commands with MySQL that could also be run in a WHM / cPanel stack. Most of the commands in this article can be run through a phpMyAdmin interface and we will leave a reference to MySQL’s documentation.

 

Creating, editing, updating and deleting content on a website is what makes the site dynamic. That’s what we are going to be doing in this post.

 

1. Create operation:

The first letter of CRUD, ‘C’, refers to CREATE aka add, insert. In this operation, it is expected to insert a new record using the SQL insert statement. SQL uses INSERT INTO statement to create new records within the table.

 

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.

 

 

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.

select *from table_name;

 

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.

  • Alter Table Command –
    This is the DDL command (Data Definition Language) used to change the structure of the table.

syntax-

alter table table_name;

modify name;

  • Update Table Command –
    This is the DML command(Data Manipulating Language) used to alter and update the records.

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

syntax-

update table_name set

where condition;

 

 

 

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

  1. Delete Command –
    (DML command) works on the records of the table.

syntax-

delete from table_name

where condition;

 

2.Drop Command –
(DDL command) works on the structure of the table.

syntax-

  • drop table table_name;
  •  drop database database_name;