What are the CRUD Operations in SQL with Examples using MySql

23:24

What are the CRUD operations?

CRUD stands for create, read, update and delete. These are the four basic functions in any database.

We can start with creating database in MySql.

# Syntax
CREATE DATABASE database_name;

Example:
CREATE DATABASE finallygot_db;

How you can delete your created database

# Syntax
DROP DATABASE database_name;

Example:
DROP DATABASE finallygot_db;

This command will delete your database and like this only we can delete our table also with its schema.

How you can use your database

# Syntax
USE finallygot_db;

As above your created database name is finallygot_db. Now you can create your table and
Implement your CRUD operations on that table such as: - CREATE, READ, UPDATE, DELETE operations on your tables in selected database. i.e;

How to Create Table Schema for your Database

# Syntax
CREATE TABLE tablename (column1 datatype, column2 datatype, .....);

Example:
CREATE TABLE student (roll_no int, name varchar(10), marks int);

It will create a table with three columns: roll_no of int datatype then name of varchar type and last is marks of int datatype.

How to Save Data into your Table of Selected Database

The INSERT statement is used to save new records in a table.

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

Example
INSERT INTO student (roll_no, name, marks) VALUES (1, 'name', 70);

How to Read Data from your table of Selected Database

The SELECT statement is used to read data from a table.

# Syntax
SELECT column1, column2,… FROM table_name;

Example:
SELECT * FROM student;  

Here *(star) we are using to read all columns of table student, but you can also specify your column_names separated by commas instead of *(star).

You can also use WHERE clause of SQL to filter your data such as:

SELECT * FROM student WHERE roll_no=1;

So, this SQL query will read columns of student having roll number 1.

How to UPDATE Data of your Table

The UPDATE statement is used to modify the existing records in a table.

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

Note*: The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated.

Example:
UPDATE student SET name = 'abc', marks= 100 WHERE roll_no = 1;
We can also update multiple records. It is the WHERE clause that determines how many records that will be updated.

How to DELETE Data of your Table

The DELETE statement is used to delete existing records in a table.

# Syntax
DELETE FROM table_name WHERE condition;

Note: The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records in the table will be deleted.

Example:
The following SQL statement deletes the customer "abc" from the "student" table:
DELETE FROM STUDENT WHERE name='abc;

If you come so far that means you were engaged in reading my article.
Please Share with your friends to make them understand and feel us confident to write more for you.
Thank you.

Author : Bharti Bhati (B.sc. (H) Computer Science)


You Might Also Like

0 comments

If you have any questions or suggestions, you are free to ask, i will appreciate that and, i will try my best...

Check Out