Tampilkan postingan dengan label MySQL. Tampilkan semua postingan
Tampilkan postingan dengan label MySQL. Tampilkan semua postingan

Rabu, 13 Januari 2010

HeidiSQL

MySQL is the world's most popular open source multithreaded, multi-user relational database management system. The basic program runs as a server providing multiuser access to a number of databases. for accessing MySQL databases are available in all major programming languages with language-specific APIs. In addition, an ODBC interface called MyODBC allows additional programming languages that support the ODBC interface to communicate with a MySQL database, such as ASP or ColdFusion. The MySQL server and official libraries are mostly implemented in ANSI C/ANSI C++.
HeidiSQL
is an easy-to-use interface and a "working-horse" for web-developers using the popular MySQL-Database. It allows you to manage and browse your databases and tables from an intuitive Windows® interface. Download

Rabu, 07 Oktober 2009

MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN

mysql> select * from demo_people;

+------------+--------------+------+

| name | phone | pid |

+------------+--------------+------+

| Mr Brown | 01225 708225 | 1 |

| Miss Smith | 01225 899360 | 2 |

| Mr Pullen | 01380 724040 | 3 |

+------------+--------------+------+

3 rows in set (0.00 sec)


mysql> select * from demo_property;

+------+------+----------------------+

| pid | spid | selling |

+------+------+----------------------+

| 1 | 1 | Old House Farm |

| 3 | 2 | The Willows |

| 3 | 3 | Tall Trees |

| 3 | 4 | The Melksham Florist |

| 4 | 5 | Dun Roamin |

+------+------+----------------------+

5 rows in set (0.00 sec)

f I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:

mysql> select name, phone, selling

from demo_people join demo_property

on demo_people.pid = demo_property.pid;

+-----------+--------------+----------------------+

| name | phone | selling |

+-----------+--------------+----------------------+

| Mr Brown | 01225 708225 | Old House Farm |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

+-----------+--------------+----------------------+

If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every PERSON gets a mention:

mysql> select name, phone, selling

from demo_people left join demo_property

on demo_people.pid = demo_property.pid;

+------------+--------------+----------------------+

| name | phone | selling |

+------------+--------------+----------------------+

| Mr Brown | 01225 708225 | Old House Farm |

| Miss Smith | 01225 899360 | NULL |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

+------------+--------------+----------------------+

If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join - in my example, that means that each property gets a mention even if we don't have seller details:

mysql> select name, phone, selling

from demo_people right join demo_property

on demo_people.pid = demo_property.pid;

+-----------+--------------+----------------------+

| name | phone | selling |

+-----------+--------------+----------------------+

| Mr Brown | 01225 708225 | Old House Farm |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

| NULL | NULL | Dun Roamin |

+-----------+--------------+----------------------+