Learning Mysql

+-----------+-----------+------+-----+-------------------+-------+
4 rows inset 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 inset 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 The disadvantages of this approach are that you can accidentally omit values for columns, and you need to remember and type column names. The omitted columns will beset 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 columns 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 beset 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 |
+-----------+----------+----------+---------------------+

Download 4.24 Mb.

Share with your friends: