Learning Mysql

Removing Tables
Removing tables is as easy as removing a database. Let’s create and remove a table from the music database:
mysql> CREATE TABLE temp (temp INT, PRIMARY KEY (temp));
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE temp;
Query OK, 0 rows affected (0.00 sec)
Don’t worry the
0 rows affected message is misleading. You’ll find the table is definitely gone.
You can use the
IF
EXISTS
phrase to prevent errors. Let’s try dropping the temp table again:
mysql> DROP TABLE IF EXISTS temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Again, you can investigate the warning indicates with the SHOW WARNINGS
statement:
mysql> SHOW WARNINGS;
+-------+------+----------------------+
| Level | Code | Message |
+-------+------+----------------------+
| Note | 1051 | Unknown table 'temp' |
+-------+------+----------------------+
1 row inset sec)
You can drop more than one table in a single statement by separating table names with commas:
mysql> DROP TABLE IF EXISTS temp, temp, temp2;
Query OK, 0 rows affected, 3 warnings (0.00 sec)
You can see three warnings because none of these tables existed.
Exercises
All exercises here concern the music database. You’ll find that the CREATE TABLE
state- ments in The Sample Music Database area useful reference. You’ve decided to store more information about artists and albums. Specifically,
for artists, you want to store the names of people who have worked with the artist
(for example, vocalists, guitarists, trumpeters, and drummers, when they began working with the artist, and when they stopped working with the artist (if they have done so).
For albums, you want to store the name of the album producer, when the album was released, and where the album was recorded. Design tables or columns that can store this information, and explain the advantages and disadvantages of your
Exercises | 221

design. Choose the column types you need, explaining the advantages and disadvantages of your choices. There are five types for storing temporal data
DATETIME
, DATE, TIME, YEAR, and
TIMESTAMP
. Explain what each is used for, and give an example of a situation in which you would choose to use it. You’ve decided to use the
AUTO_INCREMENT
feature. List the three requirements that must be met by the column you’re applying it to. Why can only one column in a table have the
AUTO_INCREMENT
feature?
5. Using the monitor, create a table with the following statement:
mysql> CREATE TABLE exercise (field INT(3));
Using the ALTER TABLE
statement, make field1
the primary key, carrying out any additional steps you need to make this possible. Add a second column, field, of type
CHAR(64)
with a DEFAULT clause. Create an index on a prefix of 10 characters from field2

Download 4.24 Mb.

Share with your friends: