|

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.

mysql> create database vegibit;
Query OK, 1 row affected (0.00 sec)

mysql>

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.

mysql> create database vegibit;
ERROR 1007 (HY000): Can't create database 'vegibit'; database exists
mysql>

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.

mysql> create database if not exists vegibit;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

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.

mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message                                          |
+-------+------+--------------------------------------------------+
| Note  | 1007 | Can't create database 'vegibit'; database exists |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql>

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.

mysql> create database vegibit2 default character set utf8;
Query OK, 1 row affected (0.04 sec)

mysql>

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.

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)

mysql>

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.

mysql> drop database vegibit2;
Query OK, 0 rows affected (0.07 sec)

mysql>

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.

mysql> drop database vegibit2;
ERROR 1008 (HY000): Can't drop database 'vegibit2'; database doesn't exist
mysql>

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

mysql> drop database if exists vegibit2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

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.

mysql> use laravelblog;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_laravelblog |
+-----------------------+
| blogpost_tag          |
| blogposts             |
| migrations            |
| tags                  |
+-----------------------+
4 rows in set (0.00 sec)

mysql> describe migrations;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| migration | varchar(255) | NO   |     | NULL    |       |
| batch     | int(11)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> show create table migrationsG
*************************** 1. row ***************************
       Table: migrations
Create Table: CREATE TABLE migrations (
  migration varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  batch int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql>

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!

mysql> use vegibit
Database changed

mysql> create table migrations (
    -> migration varchar(255) collate utf8_unicode_ci not null,
    -> batch int(11) not null
    -> ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
Query OK, 0 rows affected (0.19 sec)

mysql> describe migrations;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| migration | varchar(255) | NO   |     | NULL    |       |
| batch     | int(11)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

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.

mysql> create table friends (
    -> first_name varchar(40) not null default '',
    -> last_name varchar(40) not null default '',
    -> cell_phone varchar(20) not null default '',
    -> birthday date default null,
    -> primary key (first_name, last_name, birthday)
    -> );
Query OK, 0 rows affected (0.03 sec)

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.