sql-info.de

CREATE TABLE examples

Basic CREATE TABLE statement

A very basic CREATE TABLE statement which should work in any SQL database:


mysql> CREATE TABLE example (
id INT,
data VARCHAR(100)
);
Query OK, 0 rows affected (0.03 sec)

Creating a table with a particular storage engine

MySQL provides a variety of different table types with differing levels of functionality. The usual default, and most widely used, is MyISAM. Other storage types must be explicitly defined:


mysql> CREATE TABLE example_innodb (
id INT,
data VARCHAR(100)
) TYPE=innodb;
Query OK, 0 rows affected (0.03 sec)

Note that beginning with MySQL 4.1 ENGINE=innodb is the preferred method of defining the storage type.

Use SHOW CREATE TABLE (see below) to check that MySQL has created the table as you defined it.

Creating a table with auto_increment

Often you'll want to be able to automatically assign a sequential value to a column:


mysql> CREATE TABLE example_autoincrement (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100)
);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO example_autoincrement (data)
-> VALUES ('Hello world');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM example_autoincrement;
+----+-------------+
| id | data |
+----+-------------+
| 1 | Hello world |
+----+-------------+
1 row in set (0.01 sec)

Creating a table with the current timestamp

Often it's useful to have an automatic timestamp on each record. The MySQL special datatype TIMESTAMP enables you to keep track of changes to a record:



mysql> CREATE TABLE example_timestamp (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100),
cur_timestamp TIMESTAMP(8)
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO example_timestamp (data)
VALUES ('The time of creation is:');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM example_timestamp;
+----+--------------------------+---------------------+
| id | data | cur_timestamp |
+----+--------------------------+---------------------+
| 1 | The time of creation is: | 2004-12-01 20:37:22 |
+----+--------------------------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE example_timestamp
SET data='The current timestamp is: '
WHERE id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM example_timestamp;
+----+---------------------------+---------------------+
| id | data | cur_timestamp |
+----+---------------------------+---------------------+
| 1 | The current timestamp is: | 2004-12-01 20:38:55 |
+----+---------------------------+---------------------+
1 row in set (0.01 sec)

The column cur_timestamp is automagically updated every time the record is changed.

Creating a table with TIMESTAMP DEFAULT NOW()

MySQL supports the construct TIMESTAMP DEFAULT NOW() only from verson 4.1:


CREATE TABLE example_default_now (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
data VARCHAR(100),
created TIMESTAMP DEFAULT NOW()
);

In this case the column created retains its initial value and is not changed during subsequent updates.

For versions prior to 4.1, the only workaround is to create two timestamp columns in a table, and explicitly set the second one when inserting the record. Remember: the first TIMESTAMP will be automagically updated on each record update.

Viewing a table definition

For basic information on table columns, use DESC tablename:


mysql> DESC example;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| data | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Exact definition of the table:


mysql> SHOW CREATE TABLE example;
+---------+------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------+
| example | CREATE TABLE `example` (
`id` int(11) default NULL,
`data` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------+
1 row in set (0.00 sec)

(this example from MySQL 4.1)

See also


Comments
Thanks, this helped a lot. This was a great refresher on the basics.
Posted by: Jim | 2006-11-13 17:06
I want to create a table called 'Administrator' with primary key 'AdminID' which is a varchar.The table should have the following fields:surname,firstname,department.can you please help me with the Mysql statement to do so.

Thanks,
Posted by: Kevin Maika | 2006-11-20 16:15
create table Administrator
(
adminId varchar(20) not null,
surname varchar(20),
firstname varchar(20) not null,
departement varchar(20),
primary key(adminId));

Hi, Use this query to create table, which one you want.I hope this is ok for you.
Posted by: Pradeepkumar K | 2006-11-21 11:02
Thanks for the help, great reference. I love the useful examples that you've posted!
Posted by: Tom Wilson | 2007-01-11 20:21
how to add default value when creating table for example:

create table name ( quantity smallint(2) );

how do i set default value to "ZERO" so whenever i do select * form name it should display ZERO.
Posted by: hlp | 2007-03-11 23:59
great stuff, how about default value and a base auto_increment number?
Posted by: armsribo | 2007-05-20 05:53
hi, i want to create an online admission test, i have 3 tables, Examiners, Questionnaires, and Results table. can you help on this. thanks...
Posted by: sonny | 2007-05-25 10:37
Hi,

I'm looking to have two tables where I manually input the data, and a third table which automatically pulls data from the two manual tables.

If there are duplicates, then to only pull the record which has the newest time stamp.

Thanks
Posted by: Neeraj | 2007-09-17 01:05
Hi,
I don't think it's a good idea to let others to figure out solutions for you, right? especially just giving name of tables and hoping others will give everything
Posted by: Cool | 2007-09-17 09:22
CREATE TABLE sample.taceledger (
`runno` INT(10) unsigned not null default '0',
`doc_no` DECIMAL(6,0) unsigned NOT null ,
`doc_date` DATE,
`gl` DECIMAL(4,0) unsigned not null ,
`slcode` VARCHAR(6) not null ,
`tr` VARCHAR(1) not null ,
`cr_db` VARCHAR(1) not null,
`amount` DECIMAL(13,2)unsigned not null ,
`revcode` DECIMAL(4,0)unsigned not null ,
`narration` VARCHAR(30)unsigned not null,
`cramt` DECIMAL(13,2) unsigned not null,

`indi` VARCHAR(1)not null ,

`dbamt` DECIMAL(13,2) unsigned not null,
`acctype` VARCHAR(1) unsigned not null,
PRIMARY KEY (`runno`)
)ENGINE=InnoDB;

I am getting error #1064 on `indi` line
I am unable to clear it
can u find some solution
Posted by: Himanshu | 2007-09-17 12:02
Himanshu,

you need to remove the "unsigned" attribute from the VARCHAR columns.

HTH

Ian
Posted by: Ian | 2007-09-24 09:37
I'm making a table with employees for a store.
Only one employee can be the general manager for the store.
What code do i write so that if i try to add another employee as a general manager, i get an error.
Thank you
Posted by: Nima | 2007-10-04 21:35
hi guys,

if im going to create a table with derived column(meaning it's value is automatically created based from the value of another column from the table). for example i have a base column named birth_date and the derive column is age (where age is automatically computed based on the value entered in the birth_date). is there a way where in i can do this in the create table clause? thanks so much!
Posted by: frevy | 2008-01-14 02:51
CREATE TABLE ABHISHEK (NAME CHAR(20), addres number(10) not null DEFAULT '0'
)
Posted by: abhishek | 2008-02-10 08:15
hi.. i want to create a table called CUSTOMER having a coloum named CUSTOMERID which should be in between 1 -1000..how can i declare this..
plz help me
Posted by: deepak | 2008-02-28 07:54
Hi,

I'm busy creating a database for store staff which will be managed by a web page written in PHP.

My question is if I have the following columns in the table:
Branch_Code, Name, Password, Position, Menu_Level

I want to create templates for say Managers, Admin Managers, Front line Managers, Cashiers, Dispatch and Receiving etc and all these will have different Menu_levels, Auth_Levels and Stores. How will I go about using information from the templates to create a new user without overwriting the original template?

Is there a way that I can duplicate the required row, insert the the correct info i.e. Name and Store then update the row without touching information in the template, basically populating a new row with data from the template and then updating the necessary columns?
Posted by: Janco | 2008-04-15 12:06
Hi Frns,
I want to learn MYSQL from basics. i cant purchase any book. so can u suggest v gud book which is free in downloading and tel best site where v can find various examples on queries.
Thank you.
Posted by: Karthik | 2008-04-23 13:06
i am verymuch interested in using mysql...................
Posted by: deepika | 2008-05-07 05:29
Hey, moron, your CREATE TABLE statement gives a "no database selected" error.
Posted by: Frustrated | 2008-05-24 20:38
um, frustrated... you're the moron... you're supposed to select your own database before going around creating tables.

idiot.
Posted by: notfrustrate | 2008-06-01 22:47