How to Select, Insert, Delete, Update and Delete Data in MYSQL

How to Manipulate data in MYSQL. Example to perform CRUD operation in MYSQL Database. How to Select, Insert, Delete, Update and Delete Data in MYSQL.

MySql data manipulation is used in MySql to perform the following operations on the database, with the help of a query in order to get the desired output.

Data Manipulation operation in MYSQL

  • Insert data in MYSQL table.
  • Select data from MYSQL table.
  • Delete data from the MYSQL  table.
  • Update data from MYSQL table.

How to Insert data in MYSQL table

When you want to insert any details or records, in the table MySql Insert command can be used. below is the command to insert data in MYSQL table.

Syntax:

INSERT INTO table_name(field1, field 2, field 3, fieldN)

VALUES

(value1, value2 , value3, valuen);

Example:

CREATE TABLE emp (

emp_id INT NOT NULL,

emp_Firstname VARCHAR (100),

emp_Surname VARCHAR(100),

PRIMARY KEY (emp_id)

);

INSERT INTO emp (emp_id,emp_Firstname,emp_Surname) VALUES (2, 'dick', 'cena');

Select command in MYSQL

MySQL SELECT command is used to select specific data from the database. below is the example to view all the data of the emp table.

Syntax:

SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE Clause] [OFFSET M ][LIMIT N]

Example:

SELECT * from emp;
When you will use this query you can select all fields from the table on your screen.

Update Command in MYSQL

Update command is used to update the existing record of the table. With this command, one or more fields of table can be updated together at a time and with the use of Where clause any condition can be specified.

Syntax

UPDATE table_name SET field1=new-value1, field2=new-value2
[Where clause]

Example:

UPDATE emp
SET emp_Firstname ='Jimmy'
WHERE emp_id =2;

Delete Command in MYSQL

Delete command is used to delete records from the existing table in MYSQL. When we want to delete records from MySQL table, then we use Delete command.
Syntax:-

DELETE FROM Table_name
WHERE
(condition applied);
Example:
DELETE FROM emp WHERE emp_id =2;

Where Clause in MYSQL

It is a clause, and used along with select, insert, update and delete to get the results.
Syntax:-

WHERE conditions;
Example:-
SELETC*
FROM emp
WHERE emp_id =1;

Distinct in MYSQL

When you want to fetch only unique records from the table, and want to remove the duplicate records, MySql Distinct can be used.

Syntax

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

Example:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

MySql FROM Command

This command is used in the table to select some records from the location and it can also be used with Join condition to retrieve any records.

Syntax

FROM table 1
[{INNER JOIN ! LEFT [OUTER] JOIN ! RIGHT [OUTER] JOIN} table2
ON table1.column1 =table2.column1]

Example:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

Order by Command in MYSQL

When you want to arrange your database or table in ascending or descending’ order by’ is used.

Syntax

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY Expression [ASC! DESC];

Example:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];
ORDER By DESC;

Group By clause in MySql

Group by clause is used when; you want to group and arrange many records in a table.

Syntax

SELECT expression1, expression2,….expression_n,
Aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2,…expression_n;

Example:

SELECT DISTINCT expressions
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2,…expression_n;

Having clause in MYSQL

It is used along with group by clause so that it can return the row when the condition is true.

Syntax

SELECT expression1, expression2, ….expression_n,
Aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, Expression2, ..expression_n