Please note:
This material is adapted from Build Your Own Database Driven Website Using PHP and MySQL, by Kevin Yank. 2nd Edition, published by Sitepoint Pty. Ltd. ISBN 0-9579218-1-0. The book includes much more information, and you are encouraged to buy it!
Chapter 2 Notes, page 3
Viewing Stored Data
The command "select" is the most complex in the SQL language. This is due to its use in taking advantage of a database's flexibility in data retrieval and presentation, which is its key strength. Here we will use only the simpler forms.
mysql> select * from songs; +----+--------------------------+----------------+------------+ | id | songtitle | artist | songdate | +----+--------------------------+----------------+------------+ | 1 | Light My Fire | The Doors | 2004-08-07 | | 2 | We Can Talk About It Now | The Band | 2004-08-07 | | 3 | Bob Dylan's 115th Dream | Bob Dylan | 2004-08-08 | | 4 | In Another Land | Rolling Stones | 2004-08-08 | +----+--------------------------+----------------+------------+ 4 rows in set (0.02 sec) mysql>
This selects "everything", or *, from the table. If we want to limit the data, we could use the select command as in the following examples:
mysql> select id, songtitle, artist from songs;
+----+--------------------------+----------------+
| id | songtitle | artist |
+----+--------------------------+----------------+
| 1 | Light My Fire | The Doors |
| 2 | We Can Talk About It Now | The Band |
| 3 | Bob Dylan's 115th Dream | Bob Dylan |
| 4 | In Another Land | Rolling Stones |
+----+--------------------------+----------------+
4 rows in set (0.00 sec)
mysql>
mysql> select id, songtitle, artist, songdate from songs where songdate < "2004-
08-08";
+----+--------------------------+-----------+------------+
| id | songtitle | artist | songdate |
+----+--------------------------+-----------+------------+
| 1 | Light My Fire | The Doors | 2004-08-07 |
| 2 | We Can Talk About It Now | The Band | 2004-08-07 |
+----+--------------------------+-----------+------------+
2 rows in set (0.00 sec)
mysql>
mysql> select id, songtitle, artist, songdate from songs where artist like "%Band%";
+----+--------------------------+----------+------------+
| id | songtitle | artist | songdate |
+----+--------------------------+----------+------------+
| 2 | We Can Talk About It Now | The Band | 2004-08-07 |
+----+--------------------------+----------+------------+
1 row in set (0.01 sec)
mysql>
mysql> select id, songtitle, artist, songdate from songs where
-> artist like "%d%" or
-> artist like "%b%";
+----+--------------------------+-----------+------------+
| id | songtitle | artist | songdate |
+----+--------------------------+-----------+------------+
| 1 | Light My Fire | The Doors | 2004-08-07 |
| 2 | We Can Talk About It Now | The Band | 2004-08-07 |
| 3 | Bob Dylan's 115th Dream | Bob Dylan | 2004-08-08 |
+----+--------------------------+-----------+------------+
3 rows in set (0.00 sec)
mysql>
Modifying and Deleting Stored Data
mysql> update songs set
-> songdate = "2004-08-09"
-> where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
As we see, only the song date with the ID of 1 was changed.
mysql> select * from songs;
+----+--------------------------+----------------+------------+
| id | songtitle | artist | songdate |
+----+--------------------------+----------------+------------+
| 1 | Light My Fire | The Doors | 2004-08-09 |
| 2 | We Can Talk About It Now | The Band | 2004-08-07 |
| 3 | Bob Dylan's 115th Dream | Bob Dylan | 2004-08-08 |
| 4 | In Another Land | Rolling Stones | 2004-08-08 |
+----+--------------------------+----------------+------------+
4 rows in set (0.00 sec)
mysql>
mysql> delete from songs where artist like "%rolling stones%";
Query OK, 1 row affected (0.00 sec)
mysql> select * from songs;
+----+--------------------------+-----------+------------+
| id | songtitle | artist | songdate |
+----+--------------------------+-----------+------------+
| 1 | Light My Fire | The Doors | 2004-08-09 |
| 2 | We Can Talk About It Now | The Band | 2004-08-07 |
| 3 | Bob Dylan's 115th Dream | Bob Dylan | 2004-08-08 |
+----+--------------------------+-----------+------------+
3 rows in set (0.00 sec)
mysql>