MySQL training course - Creating tables

In this section of the mysql training course we will explore the MySQL commands to create database tables and selecting the database.

Databases store data in tables. So what are these tables?
In simplest terms, tables consist of rows and columns. Each column defines data of a particular type. Rows contain individual records.
Consider the following:

The table above contains four columns that store the name, age, country and email. Each row contains data for one individual. This is called a record. To find the country and email of Alexander, you'd first pick the name from the first column and and then look in the third and fourth columns of the same row.

A database can have many tables; it is tables, that contain the actual data. Hence, we can segregate related (or unrelated) data in different tables. For our employees database we'll have one table that stores company details of the employees. The other table would contain personal information. Let's make the first table.

The SQL command for creating tables looks complex when you view it for the first time. Don't worry if you get confused, we'll be discussing this in more detail in later sessions.

CREATE TABLE employee_data
(
emp_id int unsigned not null auto_increment primary key,
f_name varchar(20),
l_name varchar(20),
title varchar(30),
age int,
yos int,
salary int,
perks int,
email varchar(60)
);

Note: In MySQL, commands and column names are not case-sensitive; however, table and database names might be sensitive to case depending on the platform (as in Linux). You can thus, use create table instead of CREATE TABLE.
The CREATE TABLE keywords are followed by the name of the table we want to create, employee_data. Each line inside the parenthesis represents one column. These columns store the employee id, first name, last name, title, age, years of service with the company, salary, perks and emails of our employees and are given descriptive names emp_id, f_name, l_name, title, age, yos, salary, perks and email, respectively.
Each column name is followed by the column type. Column types define the type of data the column is set to contain. In our example, columns, f_name, l_name, title and email would contain small text strings, so we set the column type to varchar, which means varriable characters. The maximum number of characters for varchar columns is specified by a number enclosed in parenthesis immediately following the column name. Columns age, yos, salary and perks would contain numbers (integers), so we set the column type to int.
Our first column (emp_id) contains an employee id. Its column type looks really mean, yeh?. Let's break it down.
int: specifies that the column type is an integer (a number).
unsigned: determines that the number will be unsigned (positive integer).
not null: specifies that the value cannot be null (empty); that is, each row in the column would have a value.
auto_increment: When MySQl comes across a column with an auto_increment attribute, it generates a new value that is one greater than the largest value in the column. Thus, we don't need to supply values for this column, MySQL generates it for us! Also, it follows that each value in this column would be unique. (We'll discuss the benefits of having unique values very shortly).
primary key: helps in indexing the column that help in faster searches. Each value has to be unique.

Why have a column with unique values?
Our company Bignet has grown tremendously over the past two years. We've recruited thousands. Don't you think there is a fair chance that two employees might have the same name? Now, when that happens, how can we distinguish the records of these two employees unless we give them unique identification numbers? If we have a column with unique values, we can easily distinguish the two records. The best way to assign unique numbers is to let MySQL do it!

Using a database
We've already created our employees database. Now let's start the mysql client program and select our database. Once at the mysql prompt, issue the command:

SELECT DATABASE();

The system responds with

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

The above shows that no database has been selected. Actually, everytime we work with mysql client, we have to specify which database we plan to use. There are several ways of doing it.

Specifying the database name at the start; type the folowing at the system prompt:
mysql employees (under Windows)
mysql employees -u manish -p (under Linux)

Specifying the database with the USE statement at the mysql prompt:
mysql>USE employees;

Specifying the database with \u at the mysql prompt:
mysql>\u employees;

It's necessary to specify the database we plan to use, else MySQL will throw an error.

Creating tables

Once you've selected the employees database, issue the CREATE TABLE command at the mysql prompt.

CREATE TABLE employee_data
(
emp_id int unsigned not null auto_increment primary key,
f_name varchar(20),
l_name varchar(20),
title varchar(30),
age int,
yos int,
salary int,
perks int,
email varchar(60)
);

Note: When you press the enter key after typing the first line, the mysql prompt changes to a ->. This means that mysql understands that the command is not complete and prompts you for additional statements. Remember, each mysql command ends with a semi-colon and each column declaration is separated by a comma. Also, you can type the entire command on one line if you so want.
You screen should look similar to:

mysql> CREATE TABLE employee_data
    -> (
    -> emp_id int unsigned not null auto_increment primary key,
    -> f_name varchar(20),
    -> l_name varchar(20),
    -> title varchar(30),
    -> age int,
    -> yos int,
    -> salary int,
    -> perks int,
    -> email varchar(60)
    -> );
Query OK, 0 rows affected (0.01 sec)

Okay, we just made our first table.