|
MySQL - the order of clauses and the order of actions
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)
Associated topics are indexed under S157 - More MySQL Commands
Some other Articles
What are factory and singleton classes?Five of the best - pictures from LondonAn update on Perl - where is it going?New Serieses for the summer on TVMySQL - the order of clauses and the order of actionsWhat brought YOU to our web site?A lot has happened in a yearPHP header() function - uses and new restrictionsWhere did the Bank Holiday go?Meet other local businesses in Melksham
|
2259 posts, page by page
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, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46 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).
|
|