2,187
edits
(→JOIN: Added furthetr JOIN examples) |
(→SELECT Rows: Added COUNT) |
||
Line 149: | Line 149: | ||
=== JOIN === | === JOIN === | ||
<source lang="mysql"> | <source lang="mysql"> | ||
SELECT make, model FROM | SELECT make, model FROM hware JOIN hosts ON table.id=hosts.id WHERE hosts.ip=INET_ATON('10.10.255.253'); # Join with ON | ||
SELECT make, model FROM | SELECT make, model FROM hware JOIN hosts USING (id) WHERE hosts.ip=INET_ATON('10.10.255.253'); # Join with USING | ||
SELECT make, model, info FROM | SELECT make, model, info FROM hware JOIN hosts USING (id) JOIN notes ON (id) WHERE hosts.ip=INET_ATON('10.10.255.253'); # Multiple join | ||
</source> | </source> | ||
Different types of join will yield differing results, depending how different rows match up. It can be a bit flummoxing to start with, but its actually fairly simple once you've got the basic idea straight in your head, see - http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html | Different types of join will yield differing results, depending how different rows match up. It can be a bit flummoxing to start with, but its actually fairly simple once you've got the basic idea straight in your head, see - http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html | ||
=== COUNT === | |||
<source lang="mysql"> | |||
SELECT COUNT(*) FROM hware WHERE make='IBM'; # Counts number of rows in selection | |||
SELECT COUNT(*) AS total, SUM(IF(model='x336',1,0)) AS x336 FROM hware WHERE make='IBM'; # Additional counts number of rows where model='x336' | |||
</source> | |||
== Migrate Databases == | == Migrate Databases == |