Data Definition Language

Creating and Dropping Databases

First, let's see what databases already exist in the MySQL server. We can view this information by using the SHOW command. Enter the following (except the mysql> part) into the MySQL console client, followed by a return:

mysql> SHOW DATABASES;

The following should be visible in the console display:

Figure 15-12: Viewing existing databases

As you can see, there are two databases already created in the MySQL server. The mysql database contains administration information for the MySQL server and should not be modified. The other database, test, is exactly what it sounds like: a test for the MySQL server. The test database does not contain anything, and it can be removed safely if required.

Creating a Database

So how do we add our own database in the MySQL server? To do this, we use the CREATE command. Let's say we wish to create a database called mydata; we would use the following syntax.

Note 

The following table lists the length and character restrictions that are imposed on the names of databases, tables, columns, and aliases.

Identifier	Max Length	Valid Characters
Database	64	All valid directory name characters except "." and "/".
Table	64	All valid directory name characters except "." and "/".
Column	64	All are valid.
Alias	15	All are valid.

mysql> CREATE DATABASE mydata;

Notice that the semicolon is added after every command in the SQL language. When we press Return after entering this command, the console informs us that the query was okay. This is shown below.

Figure 15-13: Creating a database

Now that we have created the database, we can ensure it is on the server by again using the SHOW command, as follows:

mysql> SHOW DATABASES;

When we press Return with this command, we can see our database has been added to the list (note that the list is in alphabetical order, not the order in which the databases were created), as seen in the following figure:

Figure 15-14: The mydata database has been added to the list.

Dropping a Database

Now we will remove the database from the server. Note that when we do this, all data (if any) will be lost. To remove a database, we "drop" it from the server by using the DROP command. So to drop our new mydata database, we would use the following command:

mysql> DROP DATABASE mydata;

When we execute this command by pressing Return, the query will be reported as okay. This can be seen in the following figure:

Figure 15-15: Dropping a database

Now, if we again list the databases using the SHOW command:

mysql> SHOW DATABASES;

...we can see that our mydata database is no longer visible on the list. This can be seen in the following figure.

Figure 15-16: Database listing after the DROP command

Column (Field) Types in MySQL

Before we discuss the creation of tables within databases, now is a good time to mention the different column types that we can have in tables.

Each column in a table must be assigned a type, which represents the type of information that field is going to hold. Here is a complete list of available types you can use:

Type	Description
TINYINT	A very small integer. Signed range is –128 to 127. Unsigned range is 0 to 255.
SMALLINT	A small integer. Signed range is –32768 to 32767. Unsigned range is 0 to 65535.
MEDIUMINT	A medium sized integer. Signed range is –8388608 to 8388607. Unsigned range is 0 to 16777215.
INT	A normal sized integer. Signed range is –2147483648 to 2147483647. Unsigned range is 0 to 4294967295.
BIGINT	A large sized integer. Signed range is –9223372036854775808 to 9223372036854775807. Unsigned range is 0 to 18446744073709551615.
FLOAT	A small, single-precision floating-point number that cannot be unsigned. Signed range is –3.402823466E+38 to –1.755494351E–38, 0, and 1.755494351E–38 to 3.402823466E+38.
DOUBLE	A double-precision floating-point number that cannot be unsigned. Signed range is –1.7976931348632157E+308 to –2.2250738585072014E–308, 0, and 2.2250738585072014E–308 to 1.7976931348632157E+308.
DECIMAL	An unpacked floating-point number that cannot be unsigned. Works like a "char" column in that the number is stored as a string (i.e., each number uses one character in the string).
DATE	A date. Range is 1000-01-01 to 9999-12-31 and is in the format YYYY-MM-DD.
TIME	A time. Range is –838:59:59 to 838:59:59 and is in the format HH:MM:SS.
DATETIME	A combination of date and time. Range is 1000-01-01 00:00:00 to 9999-12-31 21:59:59 and is in the format YYYY-MM-DD HH:MM:SS.
YEAR[(2|4)]	A year in 2- or 4-digit format (default is 4). Range is 1901 to 2155 and also 0000.
TIMESTAMP	A timestamp. Range is 1970-01-01 00:00:00 to sometime in the year 2037 in the format YYYYMMDDHHMMSS.
CHAR(length)	A fixed-length string that is always right-padded with spaces to the specified length when stored. The range is 1 to 255 characters depending on the "length" specified.
VARCHAR	A variable-length string.
TINYBLOB/ TINYTEXT	A tiny binary object. Maximum length of 255 characters. *See NOTE below table.
BLOB/TEXT	A binary object. Maximum length of 65535 characters. *See NOTE below table.
MEDIUMBLOB/ MEDIUMTEXT	A medium binary object. Maximum length of 16777215 characters. *See NOTE below table.
LONGBLOB/ LONGTEXT	A large binary object. Maximum length of 4294967295 characters. *See NOTE below table.
ENUM('val1', 'val2'...)	An enumeration. A list of string values of which only one can be selected. Maximum of 65535 distinct values.
SET('val1', 'val2'...)	A set. A string object that can have zero or more values, each of which must be chosen from the list (i.e., 'val1', 'val2', etc.). Maximum of 64 characters.

Note 

The only difference between the BLOB and TEXT types is that for sorting and comparisons, a BLOB is case-sensitive, whereas the TEXT type is not case-sensitive.

Creating, Modifying, and Dropping Tables

Creating Tables

Now that we know the possible types for the columns in our tables, let's look at how we actually go about creating a table.

Let's say that we wish to create a table to hold some user details within a database. In fact, we will be using a similar table later in the book.

We want to store the user's title, first name, surname, age, e-mail address, and the date the user was added to the database. So we will require the following columns in our table:

Title Firstname Surname Age EmailAddress DateAdded

Before we get into how to actually add it, let's first think how we are going to store the information—or more to the point, what types we require for each of the columns.

For the title, first name, and surname, we can use the TEXT type, as it contains plenty of characters to allow for all possibilities.

Note 

VARCHAR can work faster and is maybe a better choice if the string length can be restricted to less than 255 characters.

For age, an unsigned TINYINT would be an obvious choice, as ages are numerical and no one has ever been known to live past 255. For e-mail address, we can again use a TEXT type, as it will give us substantial storage space for the address. Finally, for the date that the user was added to the table, we can use a TIMESTAMP.

Note 

With Java, it is possible to use an INT data type and store System.currentTimeMillis() / 1000 as the value. This value can later be fetched from the database. Then multiply it by 1000 and convert it to the DATE type (i.e., date = new Date(value);). This is useful when doing localization and/or conversion between different databases

The TIMESTAMP also has a great property in that the time and date can be retrieved automatically into the database. This is discussed later in the chapter.

Now that we know which types we want for our columns, we need to create a database to add the table into. This goes back to what we learned in the previous section. Let's create a database called myinfo with the following command:

mysql> CREATE DATABASE myinfo;

When we execute this command, the console should report that the query was okay. We can now check that our database has been created with the following command:

mysql> SHOW DATABASES;

When we execute this, the following should be visible in the console.

Figure 15-17: The myinfo database is now visible in the console after using the SHOW DATABASES command.

Now we need to tell MySQL that we wish to perform actions on the myinfo database. This is accomplished by using the USE command:

mysql> USE myinfo; 

Note 

Without USE, all tables in the myinfo database should be referred to as myinfo.mytable, which is of course quite inconvenient.

After executing this command, any DDL (Data Definition Language) and DML (Data Manipulation Language) statements that are executed will affect the database in use, which in this case is our myinfo database.

Now that we have our database set up and ready to accept commands, we can create our table (which we will name userinfo) with the following statement:

mysql> CREATE TABLE userinfo (     -> id INT auto_increment,     -> title TEXT,     -> firstname TEXT,     -> surname TEXT,     -> age TINYINT,     -> email TEXT,     -> dateadded TIMESTAMP,     -> PRIMARY KEY(id));

Note 

auto_increment is not a standard SQL option. MSSQL (Microsoft SQL) has a similar option and so does Postgre, but Oracle does not have a way to do this as a create table option. It must be done with sequences and triggers.

Let's break this up a little so we can see what is happening. First we declare that we wish to create a table by entering CREATE TABLE. Next we specify the name that we wish to call the table; in this case, the name is userinfo. Then we use parentheses to contain all of the columns that we require in our table and simply list all of the column names and types that we require. Note how we have added an extra field named id. This makes it easier to handle data in a relational way, as we will discuss later in this chapter. Finally, note the addition of the primary key as the last parameter. This is used to determine how the table is optimized within the database. Again, we will discuss the use of keys later in this chapter.

We can now check that our table was created successfully by executing the following command:

mysql> SHOW TABLES;

When this is executed, the following output should be visible in the console.

Figure 15-18: Here the userinfo table can be seen as part of our database.

Note you can also view the columns in a table by using the following command:

mysql> DESCRIBE userinfo;

When you execute this command, the console will display all the details for each of the columns in the userinfo table. This can be seen in the following screen shot of the console:

Figure 15-19: Describing the userinfo table

This information can be useful for both ensuring the table was created as you envisioned and to recap the columns a table contains at a later date.

Modifying Tables

So now that we know how to create a table, let's look at how we go about modifying it. Modifying a table can range from simply changing the type of one of the columns to adding a completely new column (or removing an existing column).

Let's first look at how we change the name of an existing column. In our userinfo table, we have a column called firstname, but let's now change this to read forename, a synonym for a person's first name.

To make this change, we need to use the following syntax:

mysql> ALTER TABLE userinfo CHANGE firstname forename TEXT;

Tip 

It is always highly recommended to design the database before creating it because there may be problems modifying/altering database tables or structure after there is data inserted in the tables.

Note we also must supply the data type for the column as well as its old and new names. Here is how this should look in the MySQL console client:

Figure 15-20: Modifying a column name

If we describe the userinfo table with the following command:

mysql> DESCRIBE userinfo;

...we can see that the column firstname has been renamed to forename. Here is how it looks in the console:

Figure 15-21: Description of the updated userinfo table

We can also change the data types of columns in tables. Let's say that we want to change the age column from a TINYINT to an INT. We would use the following command:

mysql> ALTER TABLE userinfo MODIFY age INT;

After executing this command, if we describe the table, we can see the type has changed to INT. This can be seen in the following figure:

Figure 15-22: Now the age column is of type INT rather than TINYINT.

Finally, it is good to know how to remove fields from a table (for example, if they are no longer required). Let's now say that we no longer require the e-mail field in our userinfo table. What we want to do is "drop" the field from our table, just as we did earlier in the chapter when we dropped the database. Here is the syntax for removing the e-mail field.

mysql> ALTER TABLE userinfo DROP email;

Here is how this looks in the MySQL console:

Figure 15-23: Dropping a field from a table

Once this command is executed, we describe the table with the following command:

mysql> DESCRIBE userinfo;

We can see in the following figure that the e-mail field has been removed (or in other words, dropped) from our userinfo table.

Figure 15-24: As you can see, the e-mail field has now been removed.

Dropping (Removing) Tables

Removing tables from a database is very simple, but without careful use it can have disastrous effects. The main thing to note is that when dropping a table, you also lose all the data contained within the table. Therefore, it is always wise to back up a database before executing any DROP commands. We will look into how to back up a database later in this chapter.

Let's now look at how we drop the userinfo table from our myinfo database. To do this, we need to execute the following command in the MySQL console client.

Note 

You cannot drop the table if there are actual relations to other tables that could break the integrity of the database. If relations are not "real," tables can be removed without errors, but the integrity is then compromised. If the administrator is not careful, the database can be permanently corrupted.

mysql> DROP TABLE userinfo;

Once this command is executed, we can check that the table has been removed by listing what tables are currently in our myinfo database by executing the following command:

mysql> SHOW TABLES;

As you can see from Figure 15-25, the table no longer exists in the database.

Figure 15-25: After dropping the userinfo table, we have an empty database.