You specify your select command in the following order:
1. The columns you want to choose (SELECT)
2. Where you want to send the results (INTO)
3. The tables that contain the data (FROM)
4. How you want to connect those tables (JOIN, ON)
5. Which individual rows you want to choose (WHERE)
6. Bunching of individual rows together (GROUP)
7. Which bunches you want to choose (HAVING)
8. How to sort the records (ORDER)
9. How many output records you want (LIMIT)

The order that MySQL performs these tasks is as follows:
1. Joining the tables
2. Selecting individual records
3. Sorting the records
4. Bunching
5. Selecting bunches
6. How many output records you want
7. Choosing the columns to display

Here's an example that shows some of the less common clauses in use:

drop table if exists calltimes;
drop table if exists townnames;
create table calltimes (
  cid int primary key not null auto_increment,
  pcode text,
  calls text);
create table townnames (
  tid int primary key not null auto_increment,
  pcode text,
  fullname text);
insert into calltimes values
  (1, "MKM", "06:44"),
  (2, "MKM", "07:17"),
  (3, "MKM", "19:08"),
  (4, "MKM", "19:50"),
  (7, "CPM", "16:25"),
  (5, "CPM", "16:55"),
  (6, "CPM", "17:25"),
  (8, "TRO", "23:42"),
  (9, "PNG", "15:10");
insert into townnames values
  (1, "MKM", "Melksham"),
  (2, "CPM", "Chippenham"),
  (3, "BTH", "Bath");
select calltimes.pcode, townnames.fullname, count(cid)
  from calltimes left join townnames
  on calltimes.pcode = townnames.pcode
  group by calltimes.pcode
  order by fullname;

And the results:

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

| pcode | fullname   | count(cid) |

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

| TRO   | NULL       |          1 |

| PNG   | NULL       |          1 |

| CPM   | Chippenham |          3 |

| MKM   | Melksham   |          4 |

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

(written 2007-06-01 17:45:49)

 S157 - More MySQL Commands

Back to What brought YOU to our web site?

Previous and next or Horse's mouth home

Forward to New Serieses for the summer on TV

What are factory and singleton classes?
Five of the best - pictures from London
An update on Perl - where is it going?
New Serieses for the summer on TV
MySQL - the order of clauses and the order of actions
What brought YOU to our web site?
A lot has happened in a year
PHP header() function - uses and new restrictions
Where did the Bank Holiday go?
Meet other local businesses in Melksham
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).