SQL Create Database and Table

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:

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 can not see the indices, they are just used to speed up queries. 

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)

Drop Index

You can delete an existing index in a table with the DROP statement.

DROP INDEX table_name.index_name

Delete a Database or Table

To delete a database:

DROP DATABASE database_name

To delete a table:

DROP TABLE table_name

To delete the data in a table without deleting the table:

DELETE TABLE table_name

Jump to: Top of Page or HOME or Printer friendly page

Search W3Schools:

What Others Say About Us

Does the world know about us? Check out these places:

Dogpile Alta Vista MSN Google Excite Lycos Yahoo Ask Jeeves

We Help You For Free. You Can Help Us!

• Tell your newsgroup or mailing list
• Link to us from your pages
• Help us correct errors and broken links
• Help us with spelling and grammar
• Validate the XHTML code of this page

W3Schools is for training only. We do not warrant its correctness or its fitness for use. The risk of using it remains entirely with the user. While using this site, you agree to have read and accepted our terms of use and privacy policy.

Copyright 1999-2002 by Refsnes Data. All Rights Reserved

How we converted to XHTML