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