News & UpdatesProgrammingWeb programming Store My Projects
Links
Affiliates

SQL Tutorial – 03 – Create

The various SQL statements can be grouped into several subset languages. The first of which will be looked at is the Data Definition Language (DDL), which allows you to add, alter, and delete databases, tables, and indexes.

Creating databases

First, create a database called “mydatabase”.

CREATE DATABASE mydatabase

When you execute a statement such as this the database system won’t return any confirmation unless there was an error, for example if the database already existed. To see that a new database has been created you can type SHOW DATABASES.

SHOW DATABASES

Next, you can select the database with the USE statement. This will make it the default database for the following SQL statements.

USE mydatabase

Creating tables

With the database selected, create a table in it called “mytable” with three different columns: id, name, and history. Each column name is followed by the data type of that column and its length in parenthesis. For example, this id integer below can hold 3 digits for a maximum value of 999.

CREATE TABLE mytable(id int(3), name varchar(20), history text(50))

Datatypes

The exact datatypes available and their names varies for different database systems, but there are 4 general types – numbers, strings, dates, and binary objects. They also come in a variety of subtypes depending on how much data you need to store. For example, a tinyint in MySQL is 1 byte large and a bigint can store up to 8 bytes.

Data typeDescription
bigint(size)
int(size)
smallint(size)
tinyint(size)
Holds integers
Size is the maximum number of digits
real(size,dec)
double(size,dec)
float(size,dec)
Holds numbers with fractions (floating-point)
Size is the number of digits before the decimal sign
Dec is the number of decimals
decimal(size,dec)
numeric(size,dec)
Holds numbers with fractions (fixed-point)
char(size)
varchar(size)
Holds a fixed length string
Holds a variable length string
date
time
Holds date
Holds time
binary(size)
varbinary(size)
blob(size)
text(size)
Holds binary data (ex. pictures)

The tables in the current database can be viewed by typing SHOW TABLES.

SHOW TABLES

To see the columns in a table you can type DESCRIBE followed by the table name.

DESCRIBE mytable

If you hadn’t selected the database with the USE keyword, you would have to explicitly specify what database to use, like this:

DESCRIBE mydatabase.mytable

With the description of the table you can see the column name, data type, and max length of each column. You can also see some additional parameters that could have been defined. For example, adding a DEFAULT value or disallowing null values with the NOT NULL modifier. A null value indicates that a field’s value is undefined and is allowed by default.

CREATE TABLE mytable(id int(3), name varchar(20) DEFAULT 'John', history text(50) NOT NULL)

Primary key

You could have made the integer column numbered automatically with the AUTO_INCREMENT attribute. This modifier requires the column to be the primary key of the table. A table can only have one primary key and it must have unique values for each row. Keys are used to uniquely identify the records in a table so that tables can relate to each other. The key field also indicates that the column is indexed, which means it will locate rows more quickly and efficiently than other columns. Indexed columns require more disk space and processing time to update, so they should only be used on columns that are searched often.

CREATE TABLE mytable(id int(3) AUTO_INCREMENT PRIMARY KEY, name varchar(20), history text(50))

Creating indexes

The CREATE INDEX statement can be used to add keys after the table has been created. For example, to create a simple key named “myindex” on the “name” column, you send the following command:

CREATE INDEX myindex ON mytable (name)

When you describe the table the index shows up as MUL, meaning it allows multiple rows to have the same value. You could have added the UNIQUE keyword to not allow duplicate values.

CREATE UNIQUE INDEX myindex ON mytable (name)
Recommended additional reading:
Sams - Teach Yourself SQL in 10 Minutes