Okay it's now week 3 of the course and it's time to read hours 6 to 8 of the MySQL course book. So whats these 3 hours about?
- Hour 6 - Planning and creating your database
- Hour 7 - Creating your database tables - Part I
- Hour 8 - Creating your database tables - Part II
Hour 6 - Planning and creating your database
Okay, lets see about creating our database. One way to do this is via the mysqladmin using the following command:
mysqladmin -u username -p create datebasename
I have not added the password for the user, instead it will be prompted once the command has been entered. Alternatively, if you want to include the password within the command use the following syntax:
mysqladmin -u username -ppassword create datebasename
So lets see the whole command with dummy username, password and database we want to create:
mysqladmin -u root -pA51gA23 create chelseafc
Once the database has been created it needs to have its permissions and privileges set. Its advised to create a new user other than the main root user account who can administer your newly created database. We can do this using the MySql monitor using a command similar to the following:
mysql -h localhost -u root -p
You will be prompted for the root user password, enter it and press enter - Remember that its case sensetive. We the need to use the mysql database which holds all the servers permission and accounts etc.
use mysql;
Now we are ready to enter the command to create our new database admin user who we will call supercontact for the chelseafc database.
GRANT ALL ON chelseafc.*
TO 'supercontact'@'localhost'
IDENTIFIED BY 'password';
Hour 7 - Creating your database tables - Part IThis hour of the book is about the common data types that you can define within your MySQL tables and creating tables. So lets create our table:
CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);
The above statement would create our table but we can improve this which is what we will do in the next hour. To issue the command to create the above table as it stands you would issue it via the MySQL monitor, remember you need to login to the monitor first.
mysql -u supercontact -p
Enter the password when prompted. Now we need to tell MySQL what database we wish to create the tables in:
USE chelseafc;
We can now enter the above statment into the monitor. Alternatively, we could use an external file, that contained the statement. This method is my prefered method of issuing commands, as it allows me to create them in advanced or via E-R diagram applications (E-R diagrams will be a subject for a later blog entry). To create the external file I use Notepad and enter the command(s) that I want to issue. The file is then saved to a suitable location with a meaningful name and the following file extension .sql. At the top of the file you should add a line that tells the server what database you want this table to be created in. So following our previous table creation example the new file would read:
USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED,
player_dateadded DATETIME,
player_datemodified DATETIME,
firstname VARCHAR (75),
lastname VARCHAR (75)
);
To use this file to create our database table use the following syntax:
mysql -u supercontact -p
Hour 8 - Creating your database tables - Part IIThe aim of this hour is to define our data types for the table fields, add keys, indexes and to create our final table creation statement. First of our table needs a primary key which can not contain a NULL value. This is done by adding the following to the chosen table field:
NOT NULL PRIMARY KEY AUTO_INCREMENT
The next step is to think about what fields in the table should be indexed, indexing helps improve searches BUT care should be given as you can degrade a servers overall performance if you index all data fields. The syntax for indexing a field is as follows:
INDEX idx_fn (firstname);
So lets put this all together and create are full table creation statement ready for use.
USE chelseafc;
CREATE TABLE players (
player_id SMALLINT UNSIGNED NOT NULL
PRIMARY KEY AUTO_INCREMENT,
player_dateadded DATETIME,
player_datemodified DATETIME,firstname VARCHAR (75),
lastname VARCHAR (75),
INDEX idx_fn (firstname)
);
Thats all that needs to be entered in the text file, enter this and save it as myfirsttable.sql The time has arrived to create the table. This is done via the MySQL monitor using the following syntax:
mysql -u supercontact -p
You will be prompted for the supercontact password, enter this and press ENTER. To check that the table was created we can use the following syntax from within the MySQL monitor:
SHOW tables;
My Conclusion Well this concludes the main areas for Week 3. Obviously the book goes into more detail, but hopefully you will get the main points from this summary. Please feel free to comment, or post questions relating to any of the areas discussed in this Blog entry.
All the best Jayson
