SQL - CREATE TABLE Statement

CIW Course in a Nutshell

SQL, CREATE Statement

This can look complex, particularly for tables with many columns, but mostly it is just repetitive. The examples I will use are for small, simple tables, larger one just contain more columns and therefore, more lines.

The Create statement for a MySQL table:

CREATE TABLE YetAnotherTable (
ID int AUTO_INCREMENT NOT NULL PRIMARY KEY,
FirstName varchar (20) ,
LastName varchar (20)
)

and the same table with Microsoft SQL:

CREATE TABLE YetAnotherTable (
ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
FirstName varchar (20) ,
LastName varchar (20)
)

As you can see, he only difference is down to the auto increment field. The MS version is called an identity column and has two parameters, the seed and the increment. This is the starting number and the values to increase it by with each new record.

CREATE INDEX Statement

I think by the time you have digested this lot you will see why I say it is much easier to use one of the GUI's like MySQL Administrator to create tables and indexes. But, that said, if you wish to do anything clever or even remotely advanced, the GUI is not the answer and you will need to become familiar with these statements, much more than I can explain here.

CREATE INDEX LastNameIdx
ON MyTable (Lastname ASC)

The general syntax is:

CREATE INDEX IndexName
ON TableName (Column ASC | DESC)

This is the basic use of the Create Index statement. It can be used to create unique, clustered indexes but I don't intend to go into that here, especially as I don't know how these feature will relate to MySQL.

This should be enough to get you started and begin you SQL journey, hopefully, without getting stranded, sans paddles, on the way.

See the MySQL manual for the full CREATE TABLE syntax and/or the full CREATE INDEX syntax.