Internet

Working with Tables

Creating a Table

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:

emp_id	Name	Age	Country	Email
11001	Rakesh	22	India	raka@ebizel.com
11002	Vishwa	25	USA	vishwa@ebizel.com
11003	Naresh Thapa	48	Nepal	nareshthapa@yahoo.com
11004	Pushpa	23	Pakistan	pushpa@pakmail.com

The table above contains five columns that store the employee id, name, age, country and email. Each row contains data for one individual. This is called a record. To find the country and email of Naresh Thapa , 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_details
(
emp_id int unsigned not null auto_increment primary key,
name varchar(20),
age int,
country varchar(30),
email varchar(60)
)TYPE=MYISAM AUTO_INCREMENT=11000;

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_details. Each line inside the parenthesis represents one column. These columns store the employee id, name, age, country of origin and emails of our employees and are given descriptive names emp_id, name, age, country 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, name, country 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 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 eBIZ.com 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();
or mysql>show databases;

+------------+
| 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.

Provisional Table Creation

To create a table only if it doesn't already exist, use CREATE TABLE IF NOT EXISTS. This feature is available as of MySQL 3.23.0. You can use it for an application that makes no assumptions about whether a table that it needs has been set up in advance.

The application can go ahead and attempt to create the table as a matter of course. The IF NOT EXISTS modifier is particularly useful for scripts that you run as batch jobs with mysql. In this context, a regular CREATE TABLE statement doesn't work very well.

The first time the job runs, it creates the table, but the second time an error occurs because the table already exists. If you use IF NOT EXISTS, there is no problem. The first time the job runs, it creates the table, as before. For the second and subsequent times, table creation attempts are silently ignored without error. This allows the job to continue processing as if the attempt had succeeded.

For Animated Presentation Click Here