Once you have a plan for the database, a design, and understand programming concepts, you start by creating a new database on your MySQL server. You can create several databases on one server, but you must be able to understand how to then create tables and manage the databases. In larger corporations, each application has its own database, which means you're responsible for maintaining several database objects. This article discusses how to create databases, tables, and then altering those tables for future changes.

Creating an Using Databases

Creating a database is probably the easiest part of the database administration process. Maintaining that database is far more complex and difficult.

To create a database, type the following command:

CREATE DATABASE mydatabase;

CREATE DATABASE is the required SQL commands, and "mydatabase" is the name of the database you want to configure. Of course, you can name your database any string value that fits your business rules.

One issue to remember when naming a database is that Windows is case insensitive, but Unix is case sensitive. This means that "mydatabase," "MyDatabase," and "MYDATABASE" are all different objects. For this reason, most database administrators stick to all lowercase letters for naming schemes.

To create a database, your user name must have the CREATE privilege. If you receive an error from MySQL that indicates access is denied, you should check your privileges. To view privileges, type the following command:

SHOW GRANTS FOR ‘me'@'domain.com';

Replace "me" with your user name and "domain.com" with the host name. If you don't see the CREATE privilege, you must ask an administrator to give you permissions. If you are logged in as root, you have these privileges.

After you've created the database, it isn't automatically selected as the active database. To start creating tables and managing the new database, you need the USE statement. The USE statement makes the database active.

Type the following command to activate your new database:

USE mydatabase;

Again, replace "mydatabase" with your own database name. At this point, if you have access to the database, MySQL sets mydatabase as the currently active database and assumes that each subsequent command should be run against mydatabase.

You can also automatically set the default database when you log in to MySQL. When you log in to MySQL, the following command is used:

mysql -h host -u user

You type this into your command line or shell to open the MySQL command shell. If you just typed the above command, you would then need to type the USE command. To save some time, you can automatically direct MySQL to mydatabase. You can use the following command:

mysql -h host -u user -p mydatabase

After you type the command, MySQL asks you for a password associated with the user name.

Creating Tables

Before you create tables, it's important to understand the different types of storage engines. Storage engines are the different types of tables you can create. The most popular are MyISAM and InnoDB, but there are several others.

MyISAM

One downside to MyISAM is that it's not ACID compliant. Automicity, Consistency, Isolation and Durability (ACID) is a transactional process for databases. With ACID, database administrators can roll back or commit transactions. ACID compliance helps with data integrity, because transactions are only committed if certain criteria are met. The MyISAM storage engine also doesn't force foreign key relationships, so it's useful for people who are new to relational databases and design.

If transactions are not a concern, then MyISAM is the preferred choice for faster queries. Your SELECT queries are usually faster. This is because MyISAM disregards a lot of the check systems incorporated into other storage engines. MyISAM databases are beneficial if you perform more SELECT statements than write statements such as UPDATE and INSERT.

For instance, if you have a reporting database, you might benefit better with MyISAM since you'll primarily use SELECT statements for analysis. Ecommerce databases would not benefit from this storage engine, because write statements are a common part of the process.

InnoDB

InnoDB is the second most common storage engine, because it has much more checks and balances for constraints, better data integrity, and offers compression. InnoDB supports ACID, which means developers and database administrators can roll back changes and only commit to transactions when criteria are met. The result is better data integrity for databases that run several UPDATE and INSERT statements.

With InnoDB, relationship constraints are forced, so any attempt to delete records associated with foreign records are terminated.

InnoDB also performs checks on corrupted data and attempts to fix it. While reporting databases are better with MyISAM, most businesses work with InnoDB since it supports ACID and fast queries even with several INSERT and UPDATE statements. For the most part, most tables use InnoDB unless the administrator is sure that the tables are only used for SELECT statements.

Merge

A merge storage engine is exactly what you'd think: it's merged data between two different MyISAM tables. Merge tables are somewhat similar to views. A view is a virtual table that's created from a SQL statement. A merge table combines data, and then lets you run queries against the merged results. These tables are occasionally preferred over joining tables, especially when you commonly use joins between two tables.

 Since Customer and Order tables are joined often, we could use a merge table and use it instead.

Memory

Archive

Active databases can grow to millions of records within certain tables. For instance, if you audit specific tables and use other tables to log changes, these tables can grow too large. At some point, you want to archive the data to reduce the size of the active tables. You can do this with an archive table, which lets you compress data to reduce storage space requirements. You cannot delete or change records once they become a part of an archive storage engine.

CSV

Comma-separated values (CSV) are used in flat text files. When you need to export data or import it into a new table, you usually work with CSV files. CSV files are universal, so you can use these files to import and export data across different platforms such as Microsoft SQL Server and Oracle. With CSV files, you won't be able to import NULL values unless you specify a default on the table column when no value is imported.

Now that you understand each storage engine, you can determine what type of table you want to create. The default MySQL type when you don't specify one is MyISAM, but some cloud providers overwrite this default and use InnoDB. Since most businesses use InnoDB, we'll use this as the default unless otherwise specified.

The basic syntax for a new table is the following statement:

CREATE TABLE table_name (column_name, column_type);

CREATE TABLE is the required statement, and the rest of the statement is your custom table name and column information. 

CREATE TABLE Customer (

            customer_id INT NOT NULL AUTO_INCREMENT,

            first_name VARCHAR(100),

            last_name VARCHAR(100),

            address VARCHAR(255),

            PRIMARY KEY (customer_id)

);

In the above example, we create a table named Customer. The Customer table has four columns: customer_id, first_name, last_name, and address. Notice that each column is set with a data type. The customer_id column is an auto-incrementing integer, which makes it unique with each record inserted. MySQL adds 1 to the last customer_id, so you know it will always be unique. Since a primary key column must be unique, we use customer_id as the primary key.

We did not discuss NULL values and primary keys previously. The customer_id is set to NOT NULL, because primary keys cannot contain NULL values. This is important when creating tables and assigning primary keys. If you attempt to insert a record with a NULL primary key value, MySQL returns an error.

You can use NOT NULL in other columns, but this means that any INSERT statements must contain values for each column. Since we don't know if every record will contain an address, first name and last name, we allow the other columns to contain NULL. Whether or not your columns contain NULL is a technical requirement that depends on the administrator. Some administrators refuse NULL values while others think they are easier to manage. Having NULL values in your tables makes it easier to query records with unknown values set as NULL.

If you've code the right syntax for your table, MySQL returns the following statement:

OK, 0 rows affected (0.16 sec)

If you don't get an OK response, ensure you've place commas after each column name, you don't allow NULL values for the primary key, and you've specified data types for each column. Also don't forget the semicolon at the end of the statement, because this character tells MySQL that the end of the statement has been reached.

The above example creates a MyISAM table, but suppose you decide to change the table to a different storage engine. Since a Customer table usually has several write and read statements, you want to change it to the InnoDB storage engine. You can use the ALTER TABLE SQL statement to change the engine.

The following statement changes the engine for Customer:

ALTER TABLE Customer ENGINE = InnoDB;

Now, you can test the table by adding a record to it.  The INSERT statement is used to add records. You can use the following SQL statement to add a test customer:

INSERT INTO Customer first_name, last_name, address VALUES (‘John', ‘Smith', ‘122 Somewhere St');

After you run the above statement, let's ensure that the record is inserted and view the data. Run the following SQL statement:

SELECT * FROM Customer;

The result data set looks like the following:

customer_id, first_name, last_name, address

-----------------------------------------------------

1          John   Smith 122 Somewhere St

This covers creating MyISAM and InnoDB tables, which are the most common, but you'll occasionally need to create other types of tables during database administration.

For instance, suppose you need a list of customers imported into a temporary table. Remember, temporary tables are stored in RAM, so they are useful for the current stored procedure or query. To specify a temporary table, you add TEMPORARY to the SQL statement. The following SQL statement creates a temporary table from the Customer table:

CREATE TEMPORARY TABLE IF NOT EXISTS

 temp_customer ( INDEX(customer_id) )

ENGINE=MyISAM

AS (

 SELECT customer_id, first_name, last_name

 FROM Customer

);

The IF NOT EXIST criteria first checks if the table exists. This is necessary so that you don't overwrite an existing temporary table accidentally. The temporary table named temp_customer is created directly from the customer table. The SELECT statement determines what columns are copied and created from the Customer table.

An archive table is usually the same structure as your active table. Some administrators export data into a separate table that's an exact copy of the main table. You can then use the ALTER TABLE statement to change to the archive table. It's recommended that the archive table should have the same table structure as your main table to avoid any errors or bugs.

For instance, you might want to export any data from a Customer_Log table that's more than a year old. You can use similar syntax to creating a temporary table except you eliminate the TEMPORARY statement. The following SQL statement creates a copy of Customer_Log records to a separate table.

CREATE TABLE IF NOT EXISTS

 archive_customer ( INDEX archive_index  (customer_id))

ENGINE=MyISAM

AS (

 SELECT customer_id, change_date, first_name

 FROM Customer_Log where create_date < ‘1/1/2013'

);

Before you change to the archive storage engine, you must drop indexes. You can use the ALTER TABLE statement to drop an index. For this example, run the following SQL statement:

DROP INDEX archive_index  ON archive_customer;

With the index removed, you can now change the storage engine type. Run the following statement on your database to change the storage engine:

ALTER TABLE Customer ENGINE = ARCHIVE;

Let's go back to the original Customer table. We've already changed the storage engine to InnoDB, so now it's time to check the table and insert data. You'll notice that we forgot to add a birthdate column. You could drop the table and recreate it, but that's not usually an option when you have a table with data in it. Most administrators must change the table schema using the ALTER TABLE statement.

Let's add a birthdate column to the current Customer table. Use the following statement to add a column:

ALTER TABLE Customer ADD birthdate DATETIME;

MySQL adds the birthdate column to the table, so now any current records have an additional birthdate column. We didn't give the column a default value, so they are set to NULL. To check values, run a SELECT statement on your table to view the data. Here is an example:

SELECT * FROM Customer;

The result data set looks like the following:

customer_id, first_name, last_name, address, birthdate

-----------------------------------------------------

1          John   Smith 122 Somewhere St NULL

We have all the data we need for the customer, but we're missing a field that identifies when the customer record was created. These fields are usually called "audit fields," because it lets database administrators review data and identify when records are created during system audits.

The difference between the birtdate field and the audit field is that you need the audit field to use the current date and time. It can't default to NULL, because NULL doesn't tell you when the record was created. You need a column that automatically sets a date and time on a column when the record is created. You can use the MySQL TIMESTAMP data type to stamp a record with the current date and time. One issue with this column type: you can only have one TIMESTAMP field in your table. Other options, if you've already used a TIMESTAMP data type in your table, are DATE and DATETIME. TIMESTAMP uses the UTC date and time format, which you need to know when you query records based on current date and time.

Let's add a column to the Customer table. Use the following SQL statement to add the column:

ALTER TABLE Customer ADD create_date TIMESTAMP NOT

NULL DEFAULT CURRENT_TIMESTAMP;

The statement above restricts data to only a timestamp, but it does not allow NULL values. This property is important for audit fields, so the CURRENT_TIMESTAMP value is always used.

To review your new changes, run a SELECT statement on your table.

SELECT * FROM Customer;

The result data set looks like the following:

customer_id, first_name, last_name, address, birthdate, create_date

-----------------------------------------------------

1          John   Smith 122 Somewhere St NULL 2015-08-10 10:00:00

MySQL also gives administrators the ability to change column data types using the ALTER TABLE statement. For instance, we set the first_name column to VARCHAR(100). This means that a customer's first name can contain up to 100 characters. We also set the address to VARCHAR(255), which means the column can contain 255 characters. You might figure out that the address field doesn't allow enough characters. If a customer tries to enter an address that's longer than 255 characters, an error is returned and data is truncated. This can cause data integrity issues with your database. The solution is to modify your address column to allow for longer address values.

Take a look at the following SQL statement.

ALTER TABLE Customer MODIFY address VARCHAR(500);

The above statement changes the address column to a VARCHAR(500), allowing more characters for input from your application. This change does not alter any of the data in your table. You can verify by running the SELECT statement on your table once again.

SELECT * FROM Customer;

The result data set looks like the following:

customer_id, first_name, last_name, address, birthdate, create_date

-----------------------------------------------------

1          John   Smith 122 Somewhere St NULL 2015-08-10 10:00:00

There are times when you want to remove a column from a table. Dropping a column should be done with care, because once it's dropped you need to restore from a backup if you find it's a mistake. The DROP SQL statement is used to remove a column and a table from the database.

Let's suppose you decide to keep the customer address in another table. The additional table will hold shipping and billing addresses for the customer, so the system now holds multiple addresses for the customer. Since MySQL is a relational database, you can use a foreign key relationship in the new address table that contains the customer's Id. With the relationship set up and the new address table created, you no longer need the address column in the Customer, because that creates confusion for developers and stores the same data in two tables. Remember, you want to store data in only one location and link tables using relationships to stick to normalization rules.

The following SQL statement deletes the address column.

ALTER TABLE Customer DROP COLUMN address;

Now, run a SELECT query on the table to review your data once again.

SELECT * FROM Customer;

The result data set looks like the following:

customer_id, first_name, last_name, birthdate, create_date

-----------------------------------------------------

1          John   Smith NULL 2015-08-10 10:00:00

You'll notice that the address column is now gone.

You also need to occasionally drop a table. Dropping tables is usually not done lightly. Once you drop the table, you no longer have access to the data, so it should be done only if you know that you don't need the data anymore. It's better to turn a table into an archive storage engine than drop it, but it's still useful to know how to delete a table.

Let's assume that you no longer want the archive table we made earlier. You decide to delete the table to free up some hard drive space on the database server. Use the following statement to delete the table:

DROP TABLE archive_customer;

That's it – the table is gone. You could run a SELECT query to verify, but the SQL statement will return an error since the table is no longer valid.

Repairing Your Tables

MySQL is an enterprise level database application, but occasionally data corrupts in your tables. If you recall, InnoDB does some checks and balances to fix tables during queries, but it isn't always possible for the system to fix data corruption. To avoid this situation, it's best to always keep backups of your database. However, MySQL also has a REPAIR TABLE command that recovers corrupted data and rebuilds indexes. It's not a solution that always works, but it's an option when you don't have a proper backup to restore your tables.

In its simplest form, the following is an example of the REPAIR TABLE statement:

REPAIR TABLE Customer;

This statement attempts to repair the data and the index. This can take several minutes to complete, so you also have the option to use the QUICK command. The QUICK command just rebuilds indexes but not the data.

The following statement just rebuilds indexes:

REPAIR TABLE Customer QUICK;

One final option is to repair the table ignoring the MYI file. The MYI has important meta data used to rebuild the table. If you suspect that the MYI file is corrupted, your option is to rebuild without it. This should only be done in extreme cases when the MYI file is missing or unusable. The following command ignores the MYI file:

REPAIR TABLE Customer USE_FRM;

If the command is successful, you can recover your data and put the database back online after downtime. If you are testing the statement or have a backup, it's better to avoid the REPAIR TABLE statement unless it's absolutely necessary. You also want to take a backup of your data before you run the statement against a table.

This article gave you an overview of creating databases and building tables. Database administrators and developers spend their days manipulating data and tables, so you'll need this information whenever you work with a MySQL database. To avoid ever needing the REPAIR TABLE statement, keep backups of your data whether it's daily or weekly.