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)
|
|
