Create Table Examples for MySQL
From Free Knowledge Base- The DUCK Project: information for everyone
03. CREATE A TABLE
Example 1
. mysql> create table tablename (field1 integer,field2 char(50)); . mysql> create table tablename (field1 integer not null auto_increment,field2 integer,primary key(field1));
Example 2
create table Customer ( mcnt integer not null auto_increment, custid mediumint, timeid timestamp default now(), P1fname char(20), P1lname char(30), P1coname varchar(55), P1codept varchar(55), P1addr1 varchar(30), P1addr2 varchar(30), P1city varchar(20), P1state char(2), P1zip smallint, P1zipx smallint, P1phone char(10), P1fax char(10), P1email varchar(40), P1Avail varchar(10), P3prisrv varchar(20), P3pkg varchar(10), P3money varchar(50), P3cfax varchar(5), P2shipsrv01 boolean not null default 0, P2shipsrv02 boolean not null default 0, P2shipsrv03 boolean not null default 0, P2shipsrv04 boolean not null default 0, P2shipsrv05 boolean not null default 0, P2shipsrv06 boolean not null default 0, P2shipsrv07 boolean not null default 0, P2shipsrv08 boolean not null default 0, P2shipsrv09 boolean not null default 0, P2shipsrv10 boolean not null default 0, P2shipsrv11 boolean not null default 0, P2intsrv01 boolean not null default 0, P2intsrv02 boolean not null default 0, P2intsrv03 boolean not null default 0, P2intsrv04 boolean not null default 0, P2intsrv05 boolean not null default 0, P2intsrv06 boolean not null default 0, P2intsrv07 boolean not null default 0, P2intsrv08 boolean not null default 0, P2intsrv09 boolean not null default 0, P2specsrv01 boolean not null default 0, P2specsrv02 boolean not null default 0, P2specsrv03 boolean not null default 0, P3wknd boolean not null default 0, primary key(mcnt) );
produces:
. +-------------+--------------+------+-----+-------------------+----------------+ . | Field | Type | Null | Key | Default | Extra | . +-------------+--------------+------+-----+-------------------+----------------+ . | mcnt | int(11) | | PRI | NULL | auto_increment | . | custid | mediumint(9) | YES | | NULL | | . | timeid | timestamp | YES | | CURRENT_TIMESTAMP | | . | P1fname | varchar(20) | YES | | NULL | | . | P1lname | varchar(30) | YES | | NULL | | . | P1coname | varchar(55) | YES | | NULL | | . | P1codept | varchar(55) | YES | | NULL | | . | P1addr1 | varchar(30) | YES | | NULL | | . | P1addr2 | varchar(30) | YES | | NULL | | . | P1city | varchar(20) | YES | | NULL | | . | P1state | char(2) | YES | | NULL | | . | P1zip | smallint(6) | YES | | NULL | | . | P1zipx | smallint(6) | YES | | NULL | | . | P1phone | varchar(10) | YES | | NULL | | . | P1fax | varchar(10) | YES | | NULL | | . | P1email | varchar(40) | YES | | NULL | | . | P1Avail | varchar(10) | YES | | NULL | | . | P3prisrv | varchar(20) | YES | | NULL | | . | P3pkg | varchar(10) | YES | | NULL | | . | P3money | varchar(50) | YES | | NULL | | . | P3cfax | varchar(5) | YES | | NULL | | . | P2shipsrv01 | tinyint(1) | | | 0 | | . | P2shipsrv02 | tinyint(1) | | | 0 | | . | P2shipsrv03 | tinyint(1) | | | 0 | | . | P2shipsrv04 | tinyint(1) | | | 0 | | . | P2shipsrv05 | tinyint(1) | | | 0 | | . | P2shipsrv06 | tinyint(1) | | | 0 | | . | P2shipsrv07 | tinyint(1) | | | 0 | | . | P2shipsrv08 | tinyint(1) | | | 0 | | . | P2shipsrv09 | tinyint(1) | | | 0 | | . | P2shipsrv10 | tinyint(1) | | | 0 | | . | P2shipsrv11 | tinyint(1) | | | 0 | | . | P2intsrv01 | tinyint(1) | | | 0 | | . | P2intsrv02 | tinyint(1) | | | 0 | | . | P2intsrv03 | tinyint(1) | | | 0 | | . | P2intsrv04 | tinyint(1) | | | 0 | | . | P2intsrv05 | tinyint(1) | | | 0 | | . | P2intsrv06 | tinyint(1) | | | 0 | | . | P2intsrv07 | tinyint(1) | | | 0 | | . | P2intsrv08 | tinyint(1) | | | 0 | | . | P2intsrv09 | tinyint(1) | | | 0 | | . | P2specsrv01 | tinyint(1) | | | 0 | | . | P2specsrv02 | tinyint(1) | | | 0 | | . | P2specsrv03 | tinyint(1) | | | 0 | | . | P3wknd | tinyint(1) | | | 0 | | . +-------------+--------------+------+-----+-------------------+----------------+
Example 3
create table Credit ( mcnt integer not null auto_increment, custid mediumint, P4cardholder varchar(50), P4addr1 varchar(30), P4addr2 varchar(30), P4city varchar(20), P4state char(2), P4zip smallint, P4zipx smallint, P4ctype varchar(20), P4ccnum char(16), P4mo tinyint, P4yr smallint, P4code smallint, P4auth1 boolean not null default 0, P4auth2 boolean not null default 0, primary key(mcnt) );
produces:
. +--------------+--------------+------+-----+---------+----------------+ . | Field | Type | Null | Key | Default | Extra | . +--------------+--------------+------+-----+---------+----------------+ . | mcnt | int(11) | | PRI | NULL | auto_increment | . | custid | mediumint(9) | YES | | NULL | | . | P4cardholder | varchar(50) | YES | | NULL | | . | P4addr1 | varchar(30) | YES | | NULL | | . | P4addr2 | varchar(30) | YES | | NULL | | . | P4city | varchar(20) | YES | | NULL | | . | P4state | char(2) | YES | | NULL | | . | P4zip | smallint(6) | YES | | NULL | | . | P4zipx | smallint(6) | YES | | NULL | | . | P4ctype | varchar(20) | YES | | NULL | | . | P4ccnum | varchar(16) | YES | | NULL | | . | P4mo | tinyint(4) | YES | | NULL | | . | P4yr | smallint(6) | YES | | NULL | | . | P4code | smallint(6) | YES | | NULL | | . | P4auth1 | tinyint(1) | | | 0 | | . | P4auth2 | tinyint(1) | | | 0 | | . +--------------+--------------+------+-----+---------+----------------+ .
