Exploring Databases and Tables with SHOW and mysqlshow | 175

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 seethe 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) NOT NULL default '0', |
| | `track_name` char) default NULL, |
| | `artist_id` int) NOT NULL default '0', |
| | `album_id` int) NOT NULL default '0', |
| | time decimal) 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 book.
Exercises
All exercises here concern the music database. You’ll find the table structures in “The
Music Database area useful reference, or you can practice using the
SHOW
statement as you work your way through the tasks. Use one or more
SELECT
statements to find out how many tracks are on New Order’s
Brotherhood album. Using a join, list the albums that we own by the band New Order. 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 Time 5.44)

Download 4.24 Mb.

Share with your friends: