Basic SQL - Learning MySQL - page 167

+-----------+-----------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
Notice that the played column has a default value of CURRENT_TIMESTAMP . This means
that if you don't insert a value for the played column, it'll insert the current date and
time by default. This is just what we want: when we play a track, we don't want to
bother checking the date and time and typing it in. Here's how you insert an incomplete
played entry:
mysql> INSERT INTO played (artist_id, album_id, track_id)
-> VALUES (7, 1, 1);
Query OK, 1 row affected (0.00 sec)
We didn't set the played column, so MySQL defaults it to the current date and time.
You can check this with a query:
mysql> SELECT * FROM played WHERE artist_id = 7
-> AND album_id = 1;
+-----------+----------+----------+---------------------+
| artist_id | album_id | track_id | played |
+-----------+----------+----------+---------------------+
| 7 | 1 | 1 | 2006-08-09 12:03:00 |
+-----------+----------+----------+---------------------+
1 row in set (0.00 sec)
You can also use this approach for bulk insertion as follows:
mysql> INSERT INTO played (artist_id, album_id, track_id)
-> VALUES (7,1,2),(7,1,3),(7,1,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
The disadvantages of this approach are that you can accidentally omit values for col-
umns, and you need to remember and type column names. The omitted columns will
be set to the default values.
All columns in a MySQL table have a default value of NULL unless another default value
is explicitly assigned when the table is created or modified. Because of this, defaults
can often cause duplicate rows: if you add a row with the default primary key values
and repeat the process, you'll get a duplicate error. However, the default isn't always
sensible; for example, in the played table, the artist_id , album_id , and track_id col-
umns all default to 0, which doesn't make sense in the context of our music collection.
Let's try adding a row to played with only default values:
mysql> INSERT INTO played () VALUES ();
Query OK, 1 row affected (0.00 sec)
The ( ) syntax is used to represent that all columns and values are to be set to their
defaults. Let's find our new row by asking for the most recent played time:
mysql> SELECT * FROM played ORDER BY played DESC LIMIT 1;
+-----------+----------+----------+---------------------+
| artist_id | album_id | track_id | played |
+-----------+----------+----------+---------------------+