MySQL tables, create table, drop table, auto increment

Conventions used in this FAQ.

Any mysql statements or commands in this FAQ will appear in
```
gray colored text using the monospace font.
```
mySQL keywords will be in UPPERCASE, while variables such as tablenames will appear in lowercase.
How do I create a mysql table?

You first need the right to create tables, usually assigned to all users. A simple example is as follows :
```
CREATE TABLE employees ( 
    id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(255)
)
```
The above statement, once executed, would create a table named "employees". The table would contain 2 columns, id and fname. The first column, id, is the primary key and is auto incremented. That is, each time a new record is created, the value of that column, can not be null and increments by 1. When adding a new record, you would leave the value for the id column null and mySQL would automatically generate a unique value. The value would be treated as an integer that can be up to 11 digits in length.
The second column, can contain null of blank values or values of up to 255 characters.
How do I add a new column to an existing table?
```
ALTER TABLE employees ADD lname VARCHAR(250)
```
The above command would add a new column to the employees table. The new column can contain up to 250 characters, which would cover most if not all last names. Each time you add a new column, the tablename, column name and column definition must be specified.
How do I drop an existing column?
```
ALTER TABLE employees DROP lname 
```
When dropping columns, all data within that column is permanently erased. Unless you have the table backed up, the data is irretrievable. Every row/record within the table loses the column you drop.
Can I rearrange the structure of a table, change column positions by shifting it from one position to another?

Yes, the simplest way is to use the following statement :
```
ALTER TABLE tablename MODIFY colname definition AFTER colname
```
Or, to move it to the first position in the structure :
```
ALTER TABLE tablename MODIFY colname definition FIRST
```
How do I rename a column?
```
ALTER TABLE employees CHANGE oldcolname newcolname OLDDEFINITION
```
oldcolname becomes newcolname. The employees table would no longer have a column named "oldcolname". You must also retain and include the old definition. e.g. VARCHAR(255)
How do I repair a corrupted or damaged table?

Tables, especially on busy sites can often be corrucpted. Serv crashes, or any other unexpected termination of a process before it's completed can and does corrupt tables. When this happens, it's nice to know the following can quickly repair the damage.
```
REPAIR TABLE tablename
```
How do I redefine a column's definition?
```
ALTER TABLE employees MODIFY fname NEWDEFINITION
```
Modifies the type of data stored in specified column. You may want to change it from VARCHAR(255) to INT(20) for example.
How do I drop a table?

Dropping tables is not a common practice unless you're in the development or wind up stage. Once dropped, all data stored with the table is permanently lost, including the table structure itself. Use wisely.
```
DROP TABLE tablename
```

What are the types of data that can be stored?

See our new FAQ on Data types and Definitions for more information.

Types are specified using a definition when either creating or altering a table. The most common are VARCHAR(length) and INT(length) where length is a number that specifies how many characters can be stored within the column. Below is (or will soon be) a full list and a short description for each. Remember, when using INT, that any value inserted into the database that begins with a 0 (zero) will have the zero dropped. In such cases where you will need the preceding zeros to be stored, you should use VARCHAR() instead.

Type Description

TINYINT(length) An integer with a value range of 0 - 255. And a signed range of -128 to +127. Can also be INT1(length)

SMALLINT(length) An integer with a value range of 0 - 65535. And a signed range of -32768 to +32767. Can also be INT2(length)

Page 1 Page 2

Was this page helpful?

Plesk Hosting

Linux servers with Plesk Control Panel, cgi-bin, crontab manager, Mysql, PHP and ASP.

Linux Hosting Plans from $9.12 per month, includes Plesk Control Panel, MySQL databases, cgi-bin, crontab manager and 50 email accounts.

Global Warming

View results

View all Polls

Run Polls on your site. Click here

Proudly hosted by LinuxHostingPlans.com

Subscribe to our periodical newsletter and receive notifications of Perl Script updates, new CGI Script releases and Special discounted offers. Existing Subscribers log in here.
		HTML	Plain text