MySQL 建表语法

1、最简单的：

CREATE TABLE t1(
   id int not null,
   name char(20)
);

2、带主键的：

a：
CREATE TABLE t1(
   id int not null primary key,
   name char(20)
);

b：复合主键
CREATE TABLE t1(
   id int not null,
   name char(20),
   primary key (id,name)
);

3、带默认值的：

CREATE TABLE t1(
   id int not null default 0 primary key,
   name char(20) default '1'
);

-----------------------------------------------------------
CREATE TABLE PLAYERS
       (PLAYERNO      INTEGER NOT NULL PRIMARY KEY,
        NAME          CHAR(15) NOT NULL,
        INITIALS      CHAR(3) NOT NULL,
        BIRTH_DATE    DATE,
        SEX           CHAR(1) NOT NULL
                     CHECK(SEX IN ('M','F')),
        JOINED        SMALLINT NOT NULL
                     CHECK(JOINED > 1969) ,
        STREET        CHAR(30) NOT NULL,
        HOUSENO       CHAR(4),
        POSTCODE      CHAR(6) CHECK(POSTCODE LIKE '______'),
        TOWN          CHAR(10) NOT NULL,
        PHONENO       CHAR(13),
        LEAGUENO      CHAR(4))
;
CREATE TABLE TEAMS
       (TEAMNO        INTEGER NOT NULL PRIMARY KEY,
        PLAYERNO      INTEGER NOT NULL,
        DIVISION      CHAR(6)   NOT NULL
                     CHECK(DIVISION IN ('first','second')),
        FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE MATCHES
       (MATCHNO       INTEGER NOT NULL PRIMARY KEY,
        TEAMNO        INTEGER NOT NULL,
        PLAYERNO      INTEGER NOT NULL,
        WON           SMALLINT NOT NULL
                     CHECK(WON BETWEEN 0 AND 3),
        LOST          SMALLINT NOT NULL
                     CHECK(LOST BETWEEN 0 AND 3),
        FOREIGN KEY (TEAMNO) REFERENCES TEAMS (TEAMNO),
        FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE PENALTIES
       (PAYMENTNO     INTEGER NOT NULL PRIMARY KEY,
        PLAYERNO      INTEGER NOT NULL,
        PAYMENT_DATE DATE NOT NULL
                     CHECK(PAYMENT_DATE >= DATE('1969-12-31')),
        AMOUNT        DECIMAL(7,2)   NOT NULL
                     CHECK (AMOUNT > 0),
        FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE COMMITTEE_MEMBERS
       (PLAYERNO      INTEGER NOT NULL,
        BEGIN_DATE    DATE NOT NULL,
        END_DATE      DATE,
        POSITION      CHAR(20),
        PRIMARY KEY   (PLAYERNO, BEGIN_DATE),
        FOREIGN KEY   (PLAYERNO) REFERENCES PLAYERS (PLAYERNO),
        CHECK(BEGIN_DATE < END_DATE),
        CHECK(BEGIN_DATE >= DATE('1990-01-01')))
;