Database management using mysql (advance) Part 1

Okay this is what i know, and what i understand.... so just see this and enjoy it :)

JOIN

Join command is used to combine two or more tables. Join clause there are several kinds:
1. inner join
2. left outer join
3. right outer join
Inner join command, function combines two tables by using the commands on tertentu.Seleksi selection followed by seleksi.Misalnya conditions:

Select customers .*, .* from customer transactions
Inner join transactions on pelanggan.id_pelanggan = transaksi.id_pelanggan
The result is a table of records of customer transactions coupled with a table with each filtering id_pelanggan
table is the same.

Select customers .*, transaksi.id_transaksi from customers
left outer join transactions on pelanggan.id_pelanggan = transaksi.id_pelanggan

This command has resulted in a merger between customer tables and table data provided id_pelanggan transactions on the transaction table and the table should be the same customers and the entire record in the customer table should appear.

Select customers .*, transaksi.id_transaksi from customers
Right outer join transaction
On pelanggan.id_pelanggan = transaksi.id_pelanggan

The above query produces a combination of customer table and the table data requirements transaksidengan id_pelanggan on transaction tables and user tables must be equal and all transaction records in the table should appear. "Where" in the join order is justified, "Where" is placed after the join command, function is to perform filtering records the results of the join. As an example consider the following query:

select customers .*, transaksi.id_transaksi from customers
left outer join transactions on pelanggan.id_pelanggan = transaksi.id_pelanggan
where transaksi.ID_transaksi is not null

The result as in the left outer join command before but no reduction in the number of records displayed. From the original records of all selected transactions into records that are not null ID_transaksi are selected.

GROUP BY
This function is used to categorize data based on the group by one or more than one coulom.use to facilitate aggregate functions, eg sum, avg, max, min, dancount. Group by is used when we will calculate a value of entity attributes (ex : counting number) to classify the data based on other attributes. Example:Counting the number of transactions made each regular customer at a given toko.so the query is as follows:
Select A.nama_pelanggan, sum (B.total_belanja) as totalFrom customers as A, B deals asA. Where ID_pelanggan = B. ID_pelangganGroup by nama_pelanggan
HAVING
Having a complementary function of the aggregate and is used with group by.Its function is to explain the condition of a particular aggregate.Example:Counting the number of transactions valued at more than 200000, which made every disuatu shop regulars. Then the query is given as follows:
Select A.nama_pelanggan, sum (B.total_belanja) as jumlah_nilaiFrom customers as A, B deals asA. Where ID_pelanggan = B. ID_pelangganGroup by A.nama_pelangganHaving sum (B.total_belanja)> 200 000In other words having similar functions to Nowhere, which serves untukmengiris SELECT results into smaller members with certain conditions. Example: calculate the total spending of each customer who've been more than 2 times.Select A.nama_pelanggan, sum (B.total_belanja) as jumlah_nilaiFrom customers as A, B deals asA. Where ID_pelanggan = B. ID_pelangganGroup by A.nama_pelangganHaving count (B.total_belanja)> 2

NESTED QUERY
Nested query or subquery is also called a minimum have two SELECT commands in one query. The following form:
select id_pelanggan, nama_pelangganfrom customerswhere (id_pelanggan) in(Select id_pelangganfrom the transaction)
The above command like the following SELECT command:
select distinct a.id_pelanggan, a.nama_pelangganfrom customers as a, b as the transactionwhere a.id_pelanggan = b.id_pelanggan
Ie selecting the records of customers who had dealings in other toko.Bentuk example:
select b.jumlah, b.berat, b.total_biaya, a.ID_transaksi, a.alamat, a.tanggalfrom the transaction as a, as b detail_transaksiwhere a.id_transaksi in(Select id_transaksi from transactions where transaksi.id_pelanggan = "123")AND b.id_transaksi in(Select id_transaksi from transactions where transaksi.id_pelanggan = "123")
The above command is to select the records where id_transaksi daritabel transaction data and transaction details of the transaction is owned by the customer id_transaksi id123.
select distinct nama_pelanggan, id_pelangganfrom customerswhere id_pelanggan in(Select id_pelangganfrom the transactionwhere id_transaksi in(Select id_transaksifrom detail_transaksigroup by id_transaksihaving avg (total_biaya)> = 10000))
The above command is to choose recordrecorddata customers who have an average total cost of the transaction detail tables> = 10000. Form of the command above memangkurang effective, but here are just for an example of a nested query is more complex.
select id_transaksifrom detail_transaksiwhere total_biaya> some (select total_biayafrom detail_transaksiwhere total_biaya> = 20000)
Nested in the above command is also called the set of comparison. The word used as a characteristic set of comparison is> some,> = some, <some, <= some = some, <> some,> all,> = all, <all, <= all, = all, and, <> all.
Subquery SELECT total_biayafrom detail_transaksiwhere total_biaya> = 20000
generate detailed records of transactions total_biaya are worth more than equal to 20000. Then the command> some in the parent query total_biaya function compares the value of the transaction details. If the value is greater equal to at least one record the results of the subquery, then the value is selected.
select id_transaksifrom detail_transaksiwhere total_biaya <all (select total_biayafrom detail_transaksiwhere total_biaya> = 20000)
The difference between some and all can be seen by comparing the previous query with the query above. If some, the value will be true if eligible (in this case>) at least one record the results of the subquery. But for all, would be true if the condition (in this case <) are met at all the records the results of the subquery.


okay,, enough for this post :) i'll continue it next week :)