This information pertains to the “phpii_initial_data.txt” data which can be inserted using “phpii_initial_data_sql.txt”, both of which are available from http://uwwebpub.com/php2.
A database has one or more tables which have one or more columns. The table has zero or more rows each of which may or may not have data for each of the table’s columns.
Remember that spacing is arbitrary in SQL. It is “proper” to surround table names and column names in the back-tick quotes. If something is considered a string, then it should be surrounded by either single- or double-quotes.
If there is ambiguity with respect to a column name, it is proper to specify
which table the column pertains to by preceding the column name with the table
name. Since both authors and posts have a title field, if we’re
operating on data from both tables, then we would have to specify
`posts`.`title`
or
`authors`.`title`
Create new author:
INSERT INTO `authors` (`title`, `email`) VALUES ('Bill Clinton', 'bill@whitehouse.gov');
No result-set because INSERTs don’t return results! But here’s
the full authors data table after this change, which I got from
doing a simple SELECT:
+----+---------------+----------------------+ | id | title | email | +----+---------------+----------------------+ | 1 | Mark Twain | marktwain@yahoo.com | | 2 | Archimedes | | | 3 | John Gruber | johngruber@gmail.com | | 4 | Walt Mossberg | walt@wsj.com | | 5 | Bill Clinton | bill@whitehouse.gov | +----+---------------+----------------------+
Get all posts:
SELECT `title`, `id` FROM `posts` ;
Result-Set:
+-------------------+----+ | title | id | +-------------------+----+ | Secession? | 1 | | What the Book? | 2 | | I'm Awesome! | 3 | | I hate everything | 4 | | I'm still cool | 5 | +-------------------+----+
Get all posts by author with ID 11:
SELECT `title`, `id` FROM `posts` WHERE `author_id`=11 ;
(No result-set because there are no posts by author #11.)
Get all posts created in or after the year 2007:
SELECT `title`,`id` FROM `posts` WHERE `created` > '2006-12-31 23:59:59' ;
Result-Set:
+-------------------+----+ | title | id | +-------------------+----+ | I hate everything | 4 | | I'm still cool | 5 | +-------------------+----+
Update post with ID 13
UPDATE `posts` SET `title`='Limbo Mania', `text`='Disco it up!' WHERE `id` = 13 ;
No result-set because UPDATEs don’t return results! But here’s
the full, updated posts table:
+----+-----------+-------------+---------------------+
| id | author_id | category_id | created |
+----+-----------+-------------+---------------------+
| 10 | 10 | 11 | 1897-01-27 14:00:00 |
| 11 | 10 | 12 | 1987-12-24 08:27:34 |
| 12 | 13 | 12 | 1985-01-24 08:37:34 |
| 13 | 12 | 10 | 2007-10-23 08:42:34 |
| 14 | 13 | 13 | 2007-04-05 08:11:34 |
+----+-----------+-------------+---------------------+
+----------------+--------------------+
--> | title | text |
+----------------+--------------------+
| Secession? | Can haz internetz? |
| What the Book? | Robots rule |
| I'm Awesome! | So says I |
| Limbo Mania | Disco it up! |
| I'm still cool | So said I |
+----------------+--------------------+
Note that we’re operating on more than one table here, so it’s important to indicate the table in which the data you’re accessing resides.
Get all posts with author information:
SELECT `posts`.`title`, `posts`.`id`, `authors`.`title`, `authors`.`email`, `authors`.`id` FROM `posts`,`authors` WHERE `posts`.`author_id`=`authors`.`id` ;
Result-Set:
+----------------+----+---------------+----------------------+----+ | title | id | title | email | id | +----------------+----+---------------+----------------------+----+ | Secession? | 10 | Mark Twain | marktwain@yahoo.com | 10 | | What the Book? | 11 | Mark Twain | marktwain@yahoo.com | 10 | | I'm Awesome! | 12 | Walt Mossberg | walt@wsj.com | 13 | | Limbo Mania | 13 | John Gruber | johngruber@gmail.com | 12 | | I'm still cool | 14 | Walt Mossberg | walt@wsj.com | 13 | +----------------+----+---------------+----------------------+----+
Here it’s helpful to rename the fields by selecting fields “AS” some other value since the fields with the same name aren’t renamed in the result-set:
SELECT `posts`.`title` AS 'posts.title', `posts`.`id` AS 'posts.id', `authors`.`title` AS 'authors.title', `authors`.`email` AS 'authors.email', `authors`.`id` AS 'authors.id' FROM `posts`,`authors` WHERE `posts`.`author_id`=`authors`.`id` ;
Result-Set:
+----------------+----------+---------------+
| posts.title | posts.id | authors.title |
+----------------+----------+---------------+
| Secession? | 10 | Mark Twain |
| What the Book? | 11 | Mark Twain |
| I'm Awesome! | 12 | Walt Mossberg |
| Limbo Mania | 13 | John Gruber |
| I'm still cool | 14 | Walt Mossberg |
+----------------+----------+---------------+
+----------------------+------------+
--> | authors.email | authors.id |
+----------------------+------------+
| marktwain@yahoo.com | 10 |
| marktwain@yahoo.com | 10 |
| walt@wsj.com | 13 |
| johngruber@gmail.com | 12 |
| walt@wsj.com | 13 |
+----------------------+------------+
Get all posts in the ‘Business’ category:
SELECT `posts`.`title` AS 'posts.title', `posts`.`id` AS 'posts.id' FROM `posts`, `categories` WHERE `categories`.`title`='Business' AND `posts`.`category_id`=`categories`.`id` ;
Result-Set:
+----------------+----------+ | posts.title | posts.id | +----------------+----------+ | What the Book? | 11 | | I'm Awesome! | 12 | +----------------+----------+
Notice that we still had to specify the join predicate,
`posts`.`category_id` = `categories`.`id`
If we didn’t, we’d get the cross-product of all entries in posts and
categories matching the single other predicate. More on this example in
the workshop.
Example:
SELECT * FROM `posts`,`categories` WHERE `categories`.`title` = 'Business' ;
Result-Set:
+----+-----------+-------------+---------------------+
| id | author_id | category_id | created |
+----+-----------+-------------+---------------------+
| 10 | 10 | 11 | 1897-01-27 14:00:00 |
| 11 | 10 | 12 | 1987-12-24 08:27:34 |
| 12 | 13 | 12 | 1985-01-24 08:37:34 |
| 13 | 12 | 10 | 2007-10-23 08:42:34 |
| 14 | 13 | 13 | 2007-04-05 08:11:34 |
+----+-----------+-------------+---------------------+
+----------------+--------------------+----+----------+
--> | title | text | id | title |
+----------------+--------------------+----+----------+
| Secession? | Can haz internetz? | 12 | Business |
| What the Book? | Robots rule | 12 | Business |
| I'm Awesome! | So says I | 12 | Business |
| Limbo Mania | Because I pwn? | 12 | Business |
| I'm still cool | So said I | 12 | Business |
+----------------+--------------------+----+----------+
(Note that this definitely might come as a bit of a surprise if you’ve never worked with SQL before.)
Author: Ryan Timmons
Last Modified: 06 August 2008 15:23:13 PDT
URL: http://uwwebpub.com/