[Step 10] Learn to command the query "arithmetic operators and time" in MySQL ss

In this post, BeCoding.tk will teach you some new query commands, that is:
1.Operator calculation
2.Date
3.DateDiff

1.Operator calculation
Arithmetic operators on mysql there are some like  +,-,*,/,DIV,and %(MOD).
"+" Is used to add more than one data.
"-" Is used to reduce the data with the other data.
"*" Is used to multiply the data with the other data.
"/" Is used to divide one data with other data.
"Div" is used to perform calculations for the division without remainder.
"Mod" is used to search for the remainder of the calculation.

Examples of its application:
"Select 1 + 1;" -> Will get results 2
"Select 1-1;" -> Will get results 0
"Select 1 * 5;" -> Will get results 5
"Select 4/2;" -> Will get results 2
"Select 5 div 2;" -> Would getting the 2 (obtained from the 5 = 2 + 2 + 1 calculated the amount of numbers 2 No 2)
"Select 5% 2;" ->Would getting the 1(obtained from the 5 = 2 + 2 + 1 calculated for the remainder of 2 is 1)
   
2.Date
date in mysql is divided into three parts, namely days, months, and years.
If we write "Date (now ());" it will mendapatakan result the current date on your computer. 
If we just wanted to get a date alone, it can be written down "Select day (now ());" 
If we just wanted to get a month only then it can be written "Select month (now ());"
If we just want to get year alone, it can be written down "Select year (now ());"
*Note: now -> Symbolizes the current time (in your computer)

3.DateDiff
Datediff used to find the difference of two dates. how writing is datediff (new_date,old_date)
For example, the difference in the date of 10 August 2016 to 11 August 2016 is the difference one day.
then the command is "select datediff ('2016-08-11', '2016-08-10');"

That is some query commands related to the operation and time calculations.Thank you for visiting and reading to the end of this post. Hopefully this article can help you all become more powerful programmer.
 

[Step 09] Learn to command the query "sum,min,max,avg, and count" in MySQL

In this post, we will teach you some new query commands. that is "sum,min,max,avg, and count".
previous reopen the database "items" that we have created in the previous post, if you do not have then you can read the previous post or make it like the screenshot below (ScreenShot: Database Becoding).
ScreenShot: Database Becoding [Click Here]
If the database is opened, we will continue our learning.

1.SUM
command "SUM" is used to calculate the overall amount of the data (summation), for example if we want to calculate the total price of all items.

then the command is: "select sum(price) from items;"

it will get results 10600000 (obtained from the calculation 1500000 + 4500000 + 1100000 + 3500000)

2.MIN
the "Min" is used to find the lowest value data of a data set. for example, we want to find the cheapest price of the item.
then the command is: "select min(price) from items;"
then we will get results 1100000 as the cheapest price. 
  
3.MAX
command "MAX" was the opposite of min. Because with this command, we can look for the highest value of a data set. for example, we want to find the price of the most expensive items.
then the command is: "select max(price) from items;"
then we will get results 4500000 as the price of the most expensive.

4.AVG
The "Avg" is the command to find the average value of a data set. For example, we want to find the average value of the item's price.
then the command is: "select avg(price) from items;"

it will get a result that is 2650000 (obtained from the (4500000 + 1500000 + 1100000 + 3500000) / 4).

 
5.COUNT
Command "cout" is a command to calculate the total amount of data in a table. For example, we want to know how much data item stored in the table "items".
then the command is: "select count(items_id) from items;"
it will get results of 4, because in the table "items" there are four pieces of data.
 
For more information you can see the screenshots below. 
ScreenShot: Process Query [Click Here] 

It was how to use the query "sum,min,max,avg, and count" in the mysql database. A few of our tutorial, we hope this article can help you all. And we thank you very much for your visit. 

How to backup and restore MySQL database using phpMyAdmin

In this post BeCoding.tk will teach you how to perform backup and restore mysql database through "Php My Admin". Mysql backup process in a very necessary because it is used to move data from the database into a file that can be saved and reused when your database gets corrupted or interference so that all the data will not be lost.
okay just to the point we are, here is a tutorial:

BACKUP DATABASE 
The first open your browser and then go to the address "http://localhost/phpmyadmin/".
ScreenShot: PhpMyAdmin [Click Here] 

Then click the database you want to backup, choose a database admin here "becoding".
then click the "export"
ScreenShot: Select DB [Click Here] 

Then click the button "GO".
ScreenShot: Confirmation Backup DB [Click Here] 

It will appear the dialog to download the database, download and store your database.
Note: the database stored in the form of file ".sql"
ScreenShot: Download Database File [Click Here]
 

RESTORE DATABASE 

To restore activity, do almost the same as the last database backup. PhpMyAdmin is open again.
then click New then you'll be taken to form a new database creation, then the contents of the database name (admin here to fill in: becoding) and click the "create".
ScreenShot: Create DB [Click Here] 

After creating the database, click the database you created the menu and then click "import".

ScreenShot: Setup restore file [Click Here] 

Then you will be asked to select the database file you click "browse" to search the database that you backup.
ScreenShot: Select DB File [Click Here]

After a selected database and click "GO", the restore process will take place and if successful it will appear earlier restore information form. 
ScreenShot: Restore Information [Click Here] 

That's how to perform backup and restore databases via phpMyAdmin. We hope that this information can be useful for you all. We would like to thank all of you who have visited and read this article until the end. 

[Step 08] How do I delete existing data in the table mysql

In today's post we (BeCoding.tk) will teach you all how to delete data stored on mysql table. Now you try to reopen a database that has been created in the learning part six and seven, go back and show all existing data on the table "items".
if you do not have the database please first read the part six and seven learners.

ScreenShot: Select DB and show data table [Click Here] 
The data we have show, so now we need to know the structure of a data delete command mysql namely:
delete from table_name where criteria;
  
 *Notes:

table_name	:	is the target table data you want to delete.
criteria	:	a requirement that the identity of the data.

*Warning:
if you do the removal without entering criteria, then all existing data in the table will be deleted.

For example, we will delete the data item "NEXUS 7" then the command is
delete from items where items_id='A001';
and then try to check whether it has been completely erased.
ScreenShot: Delete data [Click Here] 

That's how to delete a table of data in mysql, this study may be useful for you all.
thanks admin say to visitors who come and read to the end.
if you have problems you can ask us through the comments or contact us via the contact page.

[Step 07] How to update the data in the table

In this post Becoding.tk will teach you how to update or alter existing data in the database table.

Basically command is
update table_name set field_target=new_value where criteria;

*Notes

table_name	:	the name of the table whose data you want to change.
field_target	:	the value of a field you wish to edit.
new_value	:	The new value is entered
criteria	:	unique data that are characteristic of the data that we want to change (usually always code).

On learning Step 6 first we have to have one database and one table "items". re-entry into the database.
if you do not have a database and a table please visit the following link
[Step 06] how to display the data in a database table mysql 

Then we will show all existing data as follows:
ScreenShot: Database & Table [Click Here] 

Then we'll try to update the data of the item "NEXUS 7" where the price of 3.2 million will be converted into 3.5 million.
then the command is
update items set price='3500000' where items_id='A001';

*Notes

table_name	:	items
field_target	:	price
new_value	:	3500000
criteria	:	items_id='A001'(because the codec 'A001' is a unique code of the item 'NEXUS 7')

after trying, recheck whether the update was successful.
ScreenShot: Update data [Click Here] 

That's how to update the data from a table in the mysql database. I hope this study can help you gentlemen, thank you for reading until the end.

[Step 06] how to display the data in a database table mysql

In this post becoding.tk will teach you how to display the data in a database table mysql. To learn more about creating one database and then entered into the database.
(A database created by admin named "becoding")

then create a table with a structure like the one below with the name table "items".

Command:

create table items(items_id char(4) primary key, items_name char(35) not null,price int not null,reg_date date not null);

items_id	:	the unique code of an item.
items_name	:	is the name of that item.
price	:	the price of an item.
reg_date	:	is the date the item list.

then insert the following data:

Item_Id	Items_Name	Price	Reg_Date
A001	NEXUS 7	3200000	2016-07-02
A002	SAMSUNG GALAXY J7	4500000	2016-07-02
A003	ASUS ZENFONE 4C	1500000	2016-07-04
A004	ASUS ZENFONE 4	1100000	2016-08-01
A005	ASUS ZENFONE SELFIE	3500000	2016-08-02

command:
insert into items values ('A001','NEXUS 7','3200000','2016-07-02'), ('A002','SAMSUNG GALAXY J7','4500000','2016-07-02'), ('A003','ASUS ZENFONE 4C','1500000','2016-07-04'), ('A004','ASUS ZENFONE 4','1100000','2016-08-01'), ('A005','ASUS ZENFONE SELFIE','3500000','2016-08-02');
 ScreenShot: Create table and Insert data [Click Here]

Displaying all the data in the table (without criteria) 
To display the existing data ditable then the command is
select * from table_name;
notes:
* -> all field

For example, we will show all the data we have filled out earlier in the table items,
then the command is:
select * from items;
ScreenShot: Select all [Click Here] 

Whereas if we only want to show the data of field items_id and items_name ,then the command:
select items_id,items_name from items;
ScreenShot: Select all 2 [Click Here] 

Showing particular the data in the table (criteria)
Before we have to show all the data in the table, so now we will bring certain data
with a criteria or rules. for it is basically the command 'where' the structure as follows:
select * from table_name where criteria;

Several criteria that you can use is
1. The search for data with a specific name.
2. Search for the data with a certain ratio.

1. The search for data with a specific name.
Basic commands in this criterion is "like",with writing pattern:
field_name Like ('X')
X is the rule as follows:  

A%	:	the data front the letter A
%A%	:	Data containing the letters A
%A	:	the data behind the letter A

For example if we want to display the data items named "ASUS" then the command is 
select * from items where items_name like ('ASUS%');
For example if you want to display data items containing the letter "E" then the command is
select * from items where items_name like ('%E%');
For example if you want to show the data items that letter behind it is "7", the command is
select * from items where items_name like ('%7');
ScreenShot: Like command [Click Here]

2. Search for the data with a certain ratio.
Command in this criterion is an operator berbandingan as>,> =, <, <=, =, <>.
explanation:

>	:	Greater than
>=	:	Greater than or equal to
<	:	Less than
<=	:	Less than or equal to
=	:	equal to
<>	:	Not or is not equal to

For example, we will display the data items that are not items "NEXUS 7", then the command
select * from items where items_name<>'NEXUS 7'; 
For example, we will display the data items that cost over 1500000 then the command
select * from items where price>1500000;
ScreenShot: Certain ratio [Click Here]

That's how to display data from a mysql table, until the first lesson here we are today. I hope this article can be useful for you all, thank you for visiting and reading to the end.
 

[Step 05] how to enter and display data from the table mysql

 In this post we will learn to enter data into a table in the mysql database, and displays the data that has been entered.

Insert data into the table
to enter data in the mysql command used is the 'Insert'.
with formula writing:
insert into table_name (field[1],field[2],..,field[n]) values(datafield[1],datafield[2],..,datafield[n]);
or
insert into table_name values(datafield[1],datafield[2],..,datafield[n]);

a previous tutorial we already have a database 'decoding' and in it contains the table
'Friend' as follows:
ScreenShot: Desc friend [Click Here] 

 
then the presumption we will menginsert the following data:
id: 001
friend_name: Franky
id: 002
friend_name: risa kawai

when using the formula of the first to enter the first data, it can be written as follows

insert into friend (id,friend_name) values ('001','franky');
whereas if you use the second formula to enter data into two, then writing is

insert into friend values ('002','risa kawai');
ScreenShot: Insert Data [Click Here]

Displays the data in the table
to check the data has been inserted or not you can use the command 'select', By writing:
select * from table_name;
notes:
* -> all fields
then to check whether the data was already on the table to insert a friend then the command is
select * from friend; 
ScreenShot: Select table [Click Here]

That explanation on how to enter and display data in a table in mysql,we thank you for visiting and reading to the end. hopefully this article can be useful and helpful to you all.

[Step 04] create table relationships using the 'alter table' command

In this post becoding.tk will teach how to create and delete relationships between one table to another table.
What was the relation in the database?
the relation in question is the relationship between a table with another table of interlocking with one another.

What are the benefits?
Relations benefit is that you can store data from a main data on the other table as an example table member who has a relationship with a hobby table, where there is a hobby of member data stored in the table hobby.

A requirement to make the relations:1. The need to have more than one table.
2. in the second table should have the same field either the name or the data type and size.
3. the master table fields you want in a relationship should be a primary key.

the previous material we already have a database with the name becoding who may have had a friend table that contains the id and the name of a friend, so now we need a single table that stores email data. the following structure:
table name: friend_email
field:
id -> char data type (6)
email -> data type char (35)
query commands manufacture table:
create table friend_email (id char(6) not null,email char(35) not null);
ScreenShot: Create table friend_email [Click Here] 

Create relationships 
To create a relation is essentially a command: 
alter table table_01
add constraint fk_symbol foreign key (field-table_01) references table_02(field);
 explanation: 
table_01 -> is the table you want to create relationshipstable_02 -> is a master table (table that is a primary key field)
 fk_symbol -> foreign key status symbol that is useful for the elimination of the foreign key (fk_symbol free) 
field-table_01 -> field on table_01 we want to connect.
 field -> field master (the field of the primary key)Then to connect the two tables that we make our command is 
alter table friend_email
add constraint fk_email foreign key (id) references friend(id);
ScreenShot: add foreign key [Click Here] 

 
ok relationship has been successfully established. 
Remove relations 
to remove the command relationship is basically: 
alter table table_01
drop foreign key fk_symbol;
 
explanation:
table_01 -> table that has a foreign key to be removed.fk_symbol -> fk_symbol of the foreign key that we make.then if we want to remove a foreign key that we created earlier then the command is: 
alter table friend_email
drop foreign key fk_email;
ScreenShot: Drop foreign key [Click Here] 

That's the way to create and delete relationships between tables, explanation hopefully we can be useful to you. Thank you for visiting and reading to the end.motivation today: keep learning to be better :D

[Step 03] modify a table with the command 'alter table'

In previous posts we have learned how to create a table, now we will learn how to modify a table
in previous posts we have created a database with the name becoding that contains a table with namefriend that which has the following structure:
ScreenShot: Structure [Click Here] 

if you do not have a database of over please read his tutorial part 2

To modify the table structure of the command used is 'alter table table_name' followed by the command,the command in question is as follows: 
1.Change / Modify (used to change the field). 
2.Add column (used to add field).
 3.Drop column (used to delete the field). 

1.Change / Modify 
Now we will start by modifying a field with two kinds of commands that 'change' and 'modify'.then what is the difference between these two commands, the difference is the change used to change a field from the name to the data type, whereas modify only be used to change the data type of a field. 
how writing is: 
Change: change old_field_name new_field_name new_data_type; 
Modify: modify field_name new_data_type;

for example we want to change the size of the field name that was originally 12 to 30, then the command used is'Alter table friend modify name char (30) not null;', used modify because we only want to change field size.
ScreenShot: Modify [Click Here]

Tthen what if we want to change the field 'name' to 'friend_name' command is used is'Alter table friend change name friend_name char (30) not null;', used change as we change the name of this field.
ScreenShot: Change [Click Here] 

2.Add column
We've learned how to modify the field now we will learn how to add a new field to a certain table.with the command 'add column'. 
writing is 'add column field_name data_type;' 
for example, we will add a new column with the names 'grade' data type char size 1 into table 'friend', then the writing is'Alter table friend add column grade char (1) not null;'
ScreenShot: Add Column [Click Here] 

3.Drop column 
Next we will learn to remove a field from a table with a drop column command,writing is 'drop column field_name;'. 
for example, we will remove  Grade field from the table friend the , then the writing is'Alter table friend drop column grade;'.
ScreenShot: Drop Column [Click Here] 

That some commands to modify the next field we will continue with the command alter the other, i hope this post can be useful for you all. Our BeCoding.tk thank you for visiting and reading to the end.

DATA TYPE IN MYSQL

In this post BeCoding.tk will discuss the types of data in mysql, mysql data types are divided generally divided into three, that is:
1.Numeric data type
This data type is typically used to store data that is intended for the calculation of figures.The following numeric data types that you can use:
INT 
Used to store data in the form of positive and negative integers with a range between -2147483648 to 2147483647. This type of data has a size of 4 bytes (32 bits). 
TINYINT
Used to store data in the form of positive and negative integers with a range between -128 to 127. This data type has a size of 1 byte (8 bits) 
SMALLINT 
Used to store data in the form of positive and negative integers with a range between -32768 to 32,767. This type of data has a size of 2 bytes (16 bits) 
MEDIUMINT
 Used to store data in the form of positive and negative integers with a range of -8,388,608 to 8,388,607. This type of data has a size of 3 bytes (24 bits)
 BIGINT
 Used to store data in the form of positive and negative integers with a range of -8,388,608 to 8,388,607. This type of data has a size of 8 bytes (64 bits)
 FLOAT 
Used to store data in the form of positive and negative fractions single precision. This type of data has a size of 4 bytes (32 bits). 
Double
 Used to store data in the form of positive and negative fractions double precision. This type of data has a size of 8 bytes (64 bits). 
DECIMAL
 Used to store data in the form of positive and negative fractions double precision. This type of data has a size of 8 bytes (64 bits). 
REAL 
Used to store data in the form of positive and negative fractions. This type of data has a size of 8 bytes (64 bits). 
NUMERIC
 Used to store data in the form of positive and negative fractions. This type of data has a size of 8 bytes (64 bits).


2.String data type
These data types are used to store data in the form of text types.
The following string data types that you can use:
CHAR
 Used to store data character / string with a fixed size. This data type has a range between 0 to 255 characters. 
VARCHAR 
Used to store data character / string with dynamic size. This data type has a range between 0 to 255. 
BLOB
 BLOB (Binary Large Object) is a data type that is used to store binary data such as files, images, sound, etc. This data type has a range of 216-1 bytes. 
TINYBLOB
 Used to store binary data such as files, images, and sounds. This data type has a range of 255 bytes. 
MEDIUMBLOB 
Used to store binary data such as files, images, and sounds. This data type has a range of 244-1 bytes. 
LONGBLOB 
Used to store binary data such as files, images, and sounds. This data type has a range of 232-1 bytes. 
TEXT 
Used to store data text. This data type has a range between 0 to 65535 (216-1) characters. 
TINYTEXT 
Used to store data text. This data type has a range between 0 to 255. 
MEDIUMTEXT 
Used to store data text. This data type has a range between 0 to 224-1 character. 
LONGTEXT 
Used to store data text. This data type has a range between 0 to 232-1 character. 
ENUM 
Enumeration is used to store data (data set)
 SET 
Used to store the data set of data. 

3.Date&Time data type
These types are used to store the data type of the time both date and hour.The following data types Date and Time that you can use:
DATE
 Used to store data in a date format yyyy-mm-dd 
DATETIME
 Used to store date and time data in the format YY: MM: DD HH: MM: SS
 TIME
 Used to store time data in the format HH: MM: SS 
YEAR 
Used to store data years. 

Now we already know the type of data in MySQL, next we will learn how to write.when we create a table in MySQL, we have to think about the structure first.Now suppose we want to make table 'Purchase' intended to store daily sales data.then the following structure we want, with the result of his input:

Sales_code: contains the sales code (code ranging from NOTA001 - NOTA002). 
Item_code: contains the item codes (codes ranging from ITM01 - ITM99).
QTY: contains the amount purchased (1-1000).
Dates: contains the date of purchase (yyyy-mm-dd).

of the above structures we just specify the type of data you want to use, as follows: 
Sales_code: using a string data type that is 'char' with a length of 7 digits.
Item_code: using a string data type that is 'char' with a length of 5 digits. 
QTY: use the numeric data type is 'int' will be used for the calculation due to the length of 4 digits. 
Dates: using date and time data types, namely 'date' with a length of 7 digits.
* Note: for string and numeric data types can be given input constraints or digit.

from the making of the new structure we make the coding to create the table, that is:create table purchase (Sales_code char (7) primary key, Item_code char (5), QTY int (4), dates date);So our explanation of the BeCoding.tk regarding MySQL data types and their way of writing.we hope this post can help you in identifying the type of data in MySQL. We thank you for reading to the end and see you in the next post.