Databases Reference
In-Depth Information
don't have certain privileges for all columns. You can get a similar result by using
mysqlshow
with the database and table name:
$
mysqlshow --user=root --password=
the_mysql_root_password
music track
You can see the statement used to create a particular table using the
SHOW CREATE
TABLE
statement; creating tables is a subject of Chapter 6. Some users prefer this output
to that of
SHOW COLUMNS
, since it has the familiar format of a
CREATE TABLE
statement.
Here's an example for the
track
table:
mysql>
SHOW CREATE TABLE track;
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| track | CREATE TABLE `track` ( |
| | `track_id` int(3) NOT NULL default '0', |
| | `track_name` char(128) default NULL, |
| | `artist_id` int(5) NOT NULL default '0', |
| | `album_id` int(4) NOT NULL default '0', |
| | `time` decimal(5,2) default NULL, |
| | PRIMARY KEY (`artist_id`,`album_id`,`track_id`) |
| | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------+
We've reformatted the output slightly so it fits better in the topic.
Exercises
All exercises here concern the
music
database. You'll find the table structures in “The
Music Database” are a useful reference, or you can practice using the
SHOW
statement
as you work your way through the tasks:
1. Use one or more
SELECT
statements to find out how many tracks are on New Order's
Brotherhood
album.
2. Using a join, list the albums that we own by the band New Order.
3. With
INSERT
statements, add the artist
Leftfield
to the database. For this new
artist, add the album
Leftism
that has the following tracks:
a.
Release the Pressure
(Time: 7.39)
b.
Afro-Melt
(Time: 7.33)
c.
Melt
(Time: 5.21)
d.
Song of Life
(Time: 6.55)
e.
Original
(Time: 6.00)
f.
Black Flute
(Time: 3.46)
g.
Space Shanty
(Time: 7.15)
h.
Inspection Check One
(Time: 6.30)
i.
Storm 3000
(Time: 5.44)
