Today we will take a look at the different options you have when you want to create a new database. We are going to use the MySQL Tools we presented earlier here on the site. We will look at two approaches on creating and setting up a database:
1. Using the MySQL Administrator
2. Using the MySQL Query Browser
The two approaches are very different. Using the first option you don’t need any SQL knowledge at all, everything is done in an interface meanwhile the section option is all about SQL and writing your own scripts. Depending on your knowledge of writing SQL code you should pick the option best suited for you.
We start with option 1, creating and setting up a database using the MySQL Administration Tool. If you have read the post about the MySQL Tools you know that the software is available for free on the Internet. OK, we begin by starting the MySQL Administrator. The very first screen that pops up is about which server you want to connect to. If you are using your own WAMP server you simple add localhost as your hos, enter your username and password you supplied when you installed the MySQL Toolkit. I think default is username = root and password is set to nothing. When you are done, click OK. You might get another popup about an error, just click OK on that for now. Now we are up and running! If you are brand new to the MySQL Administrator I suggest you take a look around on the various menus and links so you feel familiar with the tool we are using. I will not go through each section here.
At the very bottom of the left menu section there is a link called ‘Catalogs’. Simple press that link once and another set of links will be reveal further down that section. Here you will find all your current databases found in your MySQL installation. information_schema, mysql and in some cases a test database is the default databases found in the list. To create a new database:
1. Right click in the window listing the database.
2. In the drop down menu, click Create New Schema.
You could also just hit Ctrl-N. A database schema name box will popup, enter a new and press OK. Now the list of databases should be updated with your newly created database. We continue by clicking on that database and the main window will be filled with an empty set of tables. To create a new table, simple click on the ‘Create Table’ button at the bottom of the screen. A new window will come in letting you enter your data about each table. If you have done a good job designing your database schema these next steps will be easy.
You start off by naming the table itself. When that is done, you double click on the top left corner of the big open white space, under the topic ‘Column Name’. Now we are able to add new columns. You are also able to reach this editing view by clicking at the tab ‘Column Details’ a few lines down. Under that tab we are able to set up everything we want: name, data type, primary key, not null, flags, comments, charsets and much more. You simple activate them by clicking or enter values into them. If you are using a InnoDB engine you are also able to apply foreign keys and stored procedures.
When you have assigned each table you are pretty much done. Your database is created and it’s filled with tables, still no data inside your tables though. One more important feature in the MySQL Administration tool that is useful and good to know is the Backup and Restore links in the main menu. It is very simple and straightforward to do backups and restore databases with the Administration toolkit. As soon as you are done and happy with your database to a backup, try to restore it and learn it by heart. When your hard drive crashes down and everything disappears, it is very nice and calming to know that you do have a backup or backups.
Next up is option two: creating and setting up a database using the MySQL Query Browser. The setup of a database using the MySQL Query Browser requires a lot of SQL and MySQL syntax. I will not go through the syntax itself in such details, if you want to read more about SQL syntax click here. First we start with opening the MySQL Query Browser. The look of the window popping up is similar to the first popup we got with the MySQL Administration Tool. Enter your own settings and click OK. Next press the ‘File’ tab at the top of window and lookup and click on ‘New Script Tab’. The main window will change a bit and now we are able to enter SQL code to create our database.
We start with creating a database. Simply write:
CREATE DATABASE databaseName;
To execute the line you look at your top right corner. There is a green button called ‘execute’, the one with a lightning on it. Press on that button and the code should execute and you should have a new database visible. Next up we can create a table inside our database by adding:
CREATE TABLE test (id int auto_increment, name varchar(45), CONSTRAINT id_key PRIMARY KEY(id));
If you try to run this code you get an error. Database already exists. When writing scripts it is useful to use the DROP IF EXISTS statement. What this does is simple: We drop the database, table or method if it already exists. We can do this if we know we are re-creating the database, table or method later on. If we don’t want to test run our SQL code after each new line we write we don’t need this. Although, if we encounter any errors we have some problems. So, our final SQL script for creating a database and adding one table would look like this:
DROP DATABASE IF EXISTS databaseName;
CREATE DATABASE databaseName;
USE databaseName;
DROP TABLE IF EXISTS databaseTableName;
CREATE TABLE databaseTableName(
id int auto_increment,
name varchar(45),
CONSTRAINT id_key PRIMARY KEY(id)
);
Explanation: We drop the database if it exists before we try to create a new one. We name it databaseName, this is where you add your own name. We then use the keyword USE databaseName which tells the MySQL software that this is the database we want to use and work on. We drop the databaseTableName if it exists before we create a new table with the same name. We add two columns: id and name. Id is an int with a auto_increment flag and name is a varchar with the length of 45. Lastly we add a constraint by using the CONSTRAINT keyword. We supply the constraint with a name, id_key, and the constraint type which in this case is a PRIMARY KEY and finally the column in the table this constraint should be applied to. Clicking on execute this will create a new database and a new table.
This last option with using SQL code to create databases and tables is good if we want to use PHP and modify tables in anyway. We can’t do that when we use the graphic interface the MySQL Administration tool provide. The SQL code can be added in the same way a SELECT, INSERT, UPDATE or DELETE statement is executed.
OK, we are done. I personally enjoy writing SQL code when I have the time. For me writing the SQL makes me more in control over the tables and I know what I’m doing, although it takes a lot more time then using the Administration tool. As for viewing a table’s or database’s schema I always use the Administration tool even though you can get the same information from the MySQL Query Browser by opening a new script tab, select your table and drag it into the scripting window and drop it. Although you will see a lot of other default SQL code added. Enjoy! Remember to check the SQL syntax if you are unsure before you start!
