Learning Mysql

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 automatically 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 inset 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 applications 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 inset 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.

Download 4.24 Mb.

Share with your friends: