MySQL 建表语法

最新推荐文章于 2022-06-30 00:41:10 发布

iteye_10770

最新推荐文章于 2022-06-30 00:41:10 发布

阅读量142

点赞数

分类专栏：数据库开发文章标签： MySQL F#

数据库开发专栏收录该内容

27 篇文章

订阅专栏

本文介绍了使用SQL语句创建不同类型表结构的方法，包括带有不同约束条件的基本表、复合主键表、外键引用表等，并详细展示了如何定义字段类型及约束。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >




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')))
;