DML

There are four commands used in DML(data manipulation language).

They are – INSERT, DELETE, UPDATE & SELECT

Let’s first understand concept of CLAUSE!

A clause in SQL is a built-in function that helps to fetch required records from a relation (table).

Ex: WHERE, GROUP BY etc.

1. SELECT

Syntax –

For retrieving record from tables, we need to use SELECT command.

Note:

If you want to retrieve all records from table, then you use SELECT statement syntax like this:

SELECT * FROM table_name;

This command is used to view all the records from a table.

If you want to retrieve some specific columns record from table, then you use SELECT statement syntax like this:

SELECT column1,column2,column3,… FROM table_name;

WHERE Clause

This clause basically uses for filter records on specific condition. We can various comparison operators [>, <, >=, <= etc.] with WHERE clause. 

Let’s see uses of WHERE clause with SELECT statement

Syntax:

SELECT column1, column2,… FROM table_name WHERE condition;

SCHEMA

We first need understands what schema is! What is role of this!

Schemas are basically used to organized objects in database.

Definition: In MySQL, a schema is a synonym for a database. It is a collection of tables, views, procedures, and other database objects that are organized into a logical group. Schemas are used to group database objects together and to control access to them.

When you create a new database in MySQL, you are creating a new schema. The CREATE SCHEMA statement can be used to create a new schema in MySQL. For example, to create a new schema named “mydatabase”, you can use the following SQL statement:

CREATE SCHEMA mydatabase_name;

Once you have created a schema, you can create tables and other database objects within it using SQL statements like CREATE TABLE and CREATE VIEW. You can also grant or revoke privileges on the schema and its objects to control who can access them.

INSERT

We can insert single, as well as multiple records into table(relation) at a time using INSERT command.

Before inserting records into table, we first need to know table’s schema. Let’s consider a table:

Syntax –

INSERT INTO table_name(column1,column2,…) VALUES(value 1,value 2,…);

Let’s insert a new record into the table.

Let’s insert multiple records at a time using INSERT statement.

UPDATE

This command is used to update existing records into tables. The UPDATE command in SQL is used to modify existing records in a database table. It allows you to change the values of one or more columns for specific rows in a table. The primary reasons for using the UPDATE command are:

Syntax –

UPDATE table_name SET attribute_names=’values’  WHERE condition;

DELETE

This command is used for delete existing records from tables.

Syntax –

DELETE FROM table_name WHERE condition;