SQL Create Database, Table, and Index
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)
|
 |
|
Get Your Diploma!
W3Schools' Online Certification Program is the perfect solution for busy
professionals who need to balance work, family, and career building.
The HTML Certificate is for developers who want to document their knowledge of HTML, XHTML, and CSS.
The ASP Certificate is for developers who want to document their knowledge of ASP, SQL, and ADO.
|
|
HOME
Printer friendly page
