Create a Database
To create a database:
CREATE DATABASE database_name |
Create a Table
To create a table in a database:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
.......
) |
Example
This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":
CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
) |
This example demonstrates how you can specify a maximum length for some columns:
CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
) |
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:
| Data Type | Description |
integer(size)
int(size)
smallint(size)
tinyint(size) | Hold integers only. The maximum number of digits are specified in parenthesis. |
decimal(size,d)
numeric(size,d) | Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d". |
| char(size) | Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. |
| varchar(size) | Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. |
| date(yyyymmdd) | Holds a date |
Create Index
Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.
Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.
A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.
CREATE UNIQUE INDEX index_name
ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
A Simple Index
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.
CREATE INDEX index_name
ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
Example
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
CREATE INDEX PersonIndex
ON Person (LastName) |
If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
CREATE INDEX PersonIndex
ON Person (LastName DESC) |
If you want to index more than one column you can list the column names within the parentheses, separated by commas:
CREATE INDEX PersonIndex
ON Person (LastName, FirstName) |
