Learning Mysql

You can then use the output as input to another query to find the track name:
mysql> SELECT track_name FROM track INNER JOIN played
-> USING (artist_id, album_id, track_id)
-> WHERE played = "2006-08-15 14:33:57";
+------------+
| track_name |
+------------+
| New Blues |
+------------+
1 row inset sec)
In User Variables later in this chapter, we’ll show how you can use variables to avoid having to type in the value in the second query.
With a nested query, you can do both steps in one shot:
mysql> SELECT track_name FROM track INNER JOIN played
-> USING (artist_id, album_id, track_id)
-> WHERE played = (SELECT MAX(played) FROM played);
+------------+
| track_name |
+------------+
| New Blues |
+------------+
1 row inset sec)
You can seethe nested query combines the two previous queries. Rather than using the constant date and time value discovered from a previous query, it executes the query directly as a subquery. This is the simplest type of nested query, one that returns a
scalar operand—that is, a single value.
The previous example used the equality operator, the equals sign,
=
. You can use all types of comparison operators
<
(less than),
<=
(less than or equal to),
>
(greater than),
>=
(greater than or equal to, and not equals) or
<>
(not equals).
The ANY, SOME, ALL, IN, and NOT IN Clauses
Before we start to show some more advanced features of nested queries, we need to create two new tables to use in our examples. Unfortunately, our music database is a little too simple to effectively demonstrate the full power of nested querying. So, let’s extend the database to give us something to play with.
We’ll create two new tables that share common data, but store different types of facts.
The first table we’ll create contains information about producers—that is, the people who oversee the music recording process. Here’s the structure and some data:
mysql> CREATE TABLE producer (

Download 4.24 Mb.

Share with your friends: