Structured Query Language (SQL) tutorial
Creating tables
In this section of the SQL course we will explore the commands to create database tables and select the database. These are, of course, generic and apply to all RDBMS.
Databases store data in tables. Tables consist of rows and columns, in which each column (field) defines data of a particular type. Rows (records) contain the information about a particular individual thing.
Consider the following:
| Name | Age | Country | |
|---|---|---|---|
| May East | 28 | USA | may@kasabsoftware.net |
| Jane Doe | 52 | Australia | jane@kasabsoftware.net |
| John Smythers | 21 | Australia | johns@kasabsoftware.net< |
| Antoni Person | 49 | USA | antoni@kasabsoftware.net |
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 Jane Doe, you'd find the name in the first column and then look in the third and fourth columns of the same row.
The SQL command for creating tables follows. Do not type this yet – just read and familiarize yourself with the syntax.
CREATE TABLE booking
(
booking_id int unsigned not null auto_increment primary key,
trip_date datetime,
trip_duration int,
destination varchar(15),
bus_id int,
driver varchar(8),
customer varchar(15),
deposit int,
);
Note: In SQL, 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 therefore use create table instead of CREATE TABLE.
The CREATE TABLE keywords are followed by the name of the table we want to create, ie bus.
Each line inside the parenthesis represents one column. These columns store the booking id (as booking), trip date (as trip_date) and duration (as trip_duration), destination, bus (as bus_id), names of the driver (as driver) and customer (as customer) and the amount of any deposit paid (as deposit).
Each column name is followed by the column type. Column types define the sort of data the column is set to contain. In our example,
- columns destination, driver and customer will contain small text strings, so we set the column type to VARCHAR, which means VARiable CHARacters. The maximum number of characters for varchar columns is specified by the number enclosed in parenthesis immediately following the column name.
- Columns trip_duration, bus_id and deposit will contain numbers (integers), so we set the column type to INT.
- The trip_date column will contain date and time values, so we set the column type to DATETIME.
- Our first column (booking_id) contains an id number for each booking. Its column type consists of:
- int: specifies that the column type is an integer (a number).
- unsigned: determines that the number will be a positive integer.
- not null: specifies that the value cannot be empty. Each row in the column must 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. Also, it follows that each value in this column would be unique.
- primary key: helps in indexing the column to help in faster searches. Each value has to be unique. ;
Why have a column with unique values?
Our company XYZ Transport has grown tremendously over the past two years. We've seen thousands of customers, some with the same name. When that happens, we can distinguish the records of these customers by giving 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!
Creating your table
Now let’s start actually creating tables. Once you've selected the employees database, run the following CREATE TABLE command at the mysql prompt. If you entered the CREATE TABLE command at the top of page 5, you will not need to type the following. If you did not do this previously,
- enter the following command at the mysql prompt:
CREATE TABLE booking
(
booking_id int unsigned not null auto_increment primary key,
trip_date datetime,
trip_duration int,
destination varchar(12),
bus_id int,
driver varchar(8),
customer varchar(15),
deposit int,
);
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.
Your screen should look similar to:
mysql> CREATE TABLE bus
-> (
-> booking_id int unsigned not null auto_increment primary key,
-> trip_date datetime,
-> trip_duration int,
-> destination varchar(12),
-> bus_id int,
-> driver varchar(8),
-> customer varchar(15),
-> deposit int,
-> );
Query OK, 0 rows affected (0.01 sec)
You have just made your first table.