Creating Databases and Tables in MySQL

Creating Databases and Tables in MySQL

In this MySQL Series, we first have been taking a look at MySQL and SQL in general from a high level view. With the vocabulary, acronyms, and theory now out of the way, we can not focus on putting pen to paper so to speak. This is where the MySQL Rubber hits the road. We’re going to start working with databases and tables in MySQL, for without them, we’re not going to be able to store or retrieve much data. Let’s dig in.


Creating a MySQL Database

Creating a database in MySQL is very straightforward. All you have to do is use the create database command followed by the name you would like to assign to your database. Don’t forget to terminate you command with a semicolon. Here we’ll create a database.

There we go, we’ve created a database to work with. If you made any errors, MySQL will provide an audible beep and associated error messages to the console. Now what happens if we try to create a database and the name we choose is already in use? Well, let’s find out.

Ok, that didn’t go so well. Have no fear, MySQL provides a syntax for dealing with exactly this scenario. If you’d rather have MySQL not bark at you for you’re errors on creating a database, just add the if not exists clause like so.

Interesting. Now check it out. If we get a warning as the result of executing a MySQL command, MySQL does not display the actual warning by default. Notice it just says 1 warning, but not what it is. We can dig deeper on our own by issuing the show warnings command.

Character Sets in MySQL

When creating a database you can specify a character set if you choose. A database itself is just a collection of tables, so if you assign a character set to a database, what that means is that once any tables get created, they will have that character set. Be advised! If you change the database character set after there are already many tables present, those tables will not be affected! Only new tables going forward will get that character set applied. You can go ahead and modify the character set manually on existing tables if need be however.

In MySQL the default character set is latin1, however it may make more sense to choose ut8-f when creating your databases. This will allow for a much more flexible storage option as you build out your application. To do this at the command line, simply follow these steps.

Collation in MySQL

Collation encodes the rules that govern the correct use of characters for the character set. It also defines the sort order to use on the data as well as the case sensitivity. Character sets usually have a corresponding collation. The MySQL documentation will give you the full overview, or you can find this valuable information at the command line like so.

From the output above, we can see that latin1 uses the latin1_swedish_ci collation, and utf8 uses the utf8_general_ci collation.

Drop a database in MySQL

It is easy to drop a database in MySQL. Maybe a little too easy. Let’s imagine for a moment that one of the databases we created earlier has now been in used for a year or two, and it is storing all kinds of super important data about our business. Customer names and contact information, sales records, leads and prospects, you name it. Now some junior dev joins the team and is practicing his MySQL syntax and doesn’t realize he is on the production database, not the QA database. Our soon to be unemployed dev friend enters this at the command line.

and just like that, poof, the database and all associated mission critical data is irreversibly lost. Of course in real life, there would be backups in place and so on, however this fictional drama about dropping a database drives the point home. MySQL doesn’t ask you if you are sure or anything like that, it just happily drops the database along with all associated tables and data within it. Be careful with dropping databases.

If you try to drop a database that doesn’t exist, you’ll get an error. Let’s see.

We can apply the if exists clause to dropping databases, and get a warning rather than an error like so.


Creating Tables in MySQL

We see how easy it is to create and drop databases in MySQL, now we want to create some tables. One way to look at how to create tables in a database is to reverse engineer it. How so you say? By using the show create table command. First we’ll complete some steps in our terminal, and then review what we just did.

Ok, so what just happened? Well, first we changed the database we were currently using to laravelblog by using the use command. Thanks to a prior tutorial, we already have this database to work with. Ok, once we changed databases, we need to see the tables contained in that database. Easy enough, we’ll just use show tables to do so. Now we see which tables we have. Let’s consider that maybe we want to be able to create a table just like migrations in a different database. How can we do that? Well first we’ll describe it, and then we’ll apply show create table to it. This gives us the actual syntax we need to create the table if we want to. Let’s change databases and create this table then!

Excellent! By describing and reverse engineering our table, we were able to recreate that table in an entirely different database.

It is worth noting that table creation usually has 3 parts.

  • 1. The first part is the table name, this is what comes directly after the create table command.
  • 2. The second part is the column and index definition. These are the fields of the table. As if this stuff is not confusing enough, somehow it was decided that you can refer to these things as either columns or fields. Just know they mean the same thing. This section goes in between parentheses ( ) and multiple column definitions are separated by commas.
  • 3. The third part contains table level options such as the storage engine type, charset, and comments.

data type definitions

So we can see that the varchar in our table creation code has a numeric value associated with it of 255. For text based fields, we would expect this. The number signifies this field can hold a string with a maximum length of 255 characters. Why then do we see a number associated with an integer data type? Good question. What this signifies is the minimum display width of the field. So if an INT data type field has an 11 associated with it, data retrieved will always have at least 11 digits. So what if you store the number 51501 in the field. What will be returned is 11 digits, left padded with 6 space characters. For example ‘ 51501’. In between the tics here is 11 character spaces, with padding on the left. For integers, you do not have to specify the number, but it will show up in any create table statements you use. Also note that depending on the application that is interacting with the MySQL database, you may or may not see the padding on the left. A key point to remember is that the range of the data type and the size of the storage are not changed from their usual values no matter what the minimum display width is set to.

per column options

After the declaration of the data type for a given field in the table comes the column options such as whether it is null, not null, auto increment, or any other default attributes. Primary and Unique key definitions come in section 2 in between the ( ) once all fields have been defined with their respective data types and column options. The primary key is an important one to remember as this is how MySQL stores rows in the database. Note that every single value in a primary key field must be unique, meaning no to rows within that field can have the same value.

optional statements

At the very end of the create table statement after the closing parenthesis is where you can specify various optional values for the table. This includes things like the storage engine to use, the charset, collation, and any comments you would like to have associated with the table.


Create Database and Table Practice

The information up to here has been to get us up to speed with what we need to create databases and tables in MySQL. Let’s now create a more real world scenario. Here we’ll pretend we would like to create a table that would hold information about our friends. So first we think about what it is we would like to store in the table. We probably want to store things like our friends first and last names, their cell phone number, and maybe their birthday. Let’s see how we can do it.

Great! That seemed to work pretty well. Note that when using the command line we can simply end each field definition with a comma, then hit enter to bring us to the next line for the next field definition. Once you are finished, just add the closing parenthesis along with a closing semi colon, then hit enter. This will tell MySQL that you are finished with your declaration and to execute the commands you have entered.

Conclusion

Nice work friends. We’re making a lot of progress in this MySQL series and hitting all the stops, soup to nuts. Some of this very well may be a lot of review for many of you. For others, some of these concepts may be the first time you’ve seen them. Either way, its good to revisit the basics since foundation level knowledge is key to our development.