Learning MySql - DML

Data Manipulation Language (DML) is used to manipulate the database or we call it database maintenance. There are many syntax include, but we will discuss the most used syntax, SELECT, UPDATE, DELETE, INSERT, TRUNCATE.

• INSERT

It's used to insert the data into the table. There are 2 variant to write the insert syntax, with type the column name, and without type the column name.

//without type the column's name

insert into goods_type values('1','Pieces');

insert into goods_type values('2','Bottle');

//with type the column's name

insert into goods_type (id_type,name_type) values('3','Sack');

If we want to insert more than 1 record, the only thing to do is adding commas ','. This thing will make insert processing faster. Example :

insert into goods_type values

('4','Box'),

('5','Kilos'),

('6','Ton')

;

• SELECT

To display the data from 1 or more tables. To handle display from many tables, we use JOIN. There are many types of JOIN, but i will discuss the most used ones, INNER JOIN, LEFT JOIN, RIGHT JOIN.

//Display all column from table goods_type

SELECT * FROM `goods_type`;

//Display only name_type

SELECT name_type FROM `goods_type`;

//Inner Join - to show data of table goods and goods_type that has same value on id_type

SELECT a.id_goods, a.code_goods, a.name_goods, a.stock_goods, b.name_type FROM goods a INNER JOIN goods_type b ON(a.id_type = b.id_type);

//Left Join - to show data of table goods and goods_type that has same value on id_type

SELECT a.id_goods, a.code_goods, a.name_goods, a.stock_goods, b.name_type FROM goods a LEFT JOIN goods_type b ON(a.id_type = b.id_type);

//Right Join - to show data of table goods and goods_type that has same value on id_type

SELECT a.id_goods, a.code_goods, a.name_goods, a.stock_goods, b.name_type FROM goods a RIGHT JOIN goods_type b ON(a.id_type = b.id_type);

The differences :

Inner Join : Select data from 2 tables directly, if one table doesn't have the data, then the data won't display

Left Join : Select data from first table, if the data from the second table is not found, then the value will change to NULL

Right Join : Select data from second table, if the data from the first table is not found, then the value will change to NULL

Try it on your own, you will look the differences.

• UPDATE

To change the value in the table. When you want to update something, take a good look on clausa WHERE to avoid mistakes. Example :

UPDATE goods_type SET name_type = 'Boxes' WHERE id_type = 1; // it will update the value name_type from table goods_type that has id_type = 1

• DELETE

To delete data from table. Same with update, take a good look on cluasa where, because if you have mistake on code, it will ruin your database.

DELETE FROM goods_type where id_type = 1; // it will delete data from table goods_type only for the data that has value of id_type = 1

Thanks again for reading my blog. Hope my article helps you. Bye!