Creating a Database with Perl and DBI - The DESCRIBE Command
(Page 2 of 4 )

TheDESCRIBEcommand shows all the fields in the table and their types. This will show us if themusicianstable was created correctly:

mysql> DESCRIBE musicians;

Field	Type	Null	Key	Default	Extra
player_id	int(11)		PRI	0
name	char(50)	YES		NULL
phone	char(12)	YES		NULL

3 rows in set (0.00 sec)

This looks OK so far. Let’s create the other two tables:what_they_playandinstruments:

mysql> CREATE what_they_play (
    ->   player_id INT,
   ->    inst_id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE instruments (
    ->  inst_id INT PRIMARY KEY,
    ->  instrument CHAR(40),
    ->  type CHAR(20),
    ->  difficulty INT);
Query OK, 0 rows affected (0.00 sec)

Creating a Non-root User with the GRANT Command

It is important to create a non-root user to access the database—performing normal non-MySQL-admin activities using the root user is a bad idea for security reasons. So let’s create a user that will be allowed to perform basic queries on themusicians_dbdatabase:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE
    ->   ON musicians_db.*
    ->  TO musicfan@localhost
    ->  IDENTIFIED BY "CrimsonKing";
Query OK, 0 rows affected (0.03 sec)

You can trust us when we say that this command creates a user namedmusicfan with a password “CrimsonKing”⁵ and grants this user permission to select, insert, update, and delete records from the database. Or, you can check out the documentation and read all about theGRANTcommand.

We are going to start inserting data into ourmusicians_dbdatabase, so we need to log out as therootuser and log back into MySQL as the newly createdmusicfanuser:

mysql> quit
Bye
$ mysql -u musicfan -p
Enter password: CrimsonKing

mysql>

Next: The INSERT Command >>
 

More Perl Articles
More By Apress Publishing

Comments On: Creating a Database with Perl and DBI

· This article is an excerpt from the book "Beginning Perl," published by Apress. We...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 15 of the book Beginning Perl by James Lee (Apress; ISBN: 159059391X). Check it out today at your favorite bookstore. Buy this book now.