Creating a Database with Perl and DBI - The DESCRIBE Command (
Page 2 of 4 )
The
DESCRIBE
command shows all the fields in the table and their types. This will show us if the
musicians
table 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_play
and
instruments
:
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 the
musicians_db
database:
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 named
musicfan
with a password “CrimsonKing”5 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 the
GRANT
command.
We are going to start inserting data into our
musicians_db
database, so we need to log out as the
root
user and log back into MySQL as the newly created
musicfan
user:
mysql> quit
Bye
$ mysql -u musicfan -p
Enter password: CrimsonKing
mysql>
