Databases Reference
In-Depth Information
parentheses; if you omit the column names, all columns in the destination table are
assumed in the order they appear in a
DESCRIBE TABLE
or
SHOW CREATE TABLE
statement.
The
SELECT
statement outputs a list of columns that must match the type and order of
the list provided for the
INSERT INTO
statement (or the implicit, complete list if one isn't
provided). The overall effect is that the rows output from the
SELECT
statement are
inserted into the destination table by the
INSERT INTO
statement. In our example,
artist_id
,
album_id
, and
track_id
values from the
track
table are inserted into the three
columns with the same names and types in the
shuffle
table; the
sequence_id
is auto-
matically created using MySQL's
AUTO_INCREMENT
feature, and so isn't specified in the
statements.
Our example includes the clause
ORDER BY RAND( )
; this orders the results according to
the MySQL function
RAND( )
. The
RAND( )
function returns a pseudorandom number
in the range 0 to 1:
mysql>
SELECT RAND();
+------------------+
| RAND() |
+------------------+
| 0.34423927529178 |
+------------------+
1 row in set (0.00 sec)
A pseudorandom number generator doesn't generate truly random numbers, but rather
generates numbers based on some property of the system, such as the time of day; this
is sufficiently random for most applications. A notable exception is cryptography ap-
plications that depend on the true randomness of numbers for security.
If you ask for the
RAND( )
value in a
SELECT
operation, you'll get a random value for each
returned row:
mysql>
SELECT *, RAND() FROM artist;
+-----------+---------------------------+------------------+
| artist_id | artist_name | RAND() |
+-----------+---------------------------+------------------+
| 1 | New Order | 0.866806439 |
| 2 | Nick Cave & The Bad Seeds | 0.66403617492322 |
| 3 | Miles Davis | 0.71976158834972 |
| 4 | The Rolling Stones | 0.60669944771258 |
| 5 | The Stone Roses | 0.8742125042474 |
| 6 | Kylie Minogue | 0.55096420883291 |
+-----------+---------------------------+------------------+
6 rows in set (0.00 sec)
Since the values are effectively random, you'll almost certainly see different results than
we've shown here. Let's return to the
INSERT
operation. When we ask that the results
be ordered by
RAND( )
, the results of the
SELECT
statement are sorted in a pseudorandom
order.
