MySQL 建表语法
时间：2010-09-05 来源：kennychang05
MySQL 建表语法
1、最简单的：
CREATE TABLE t1(
id int not null,
name char(20)
);
2、带主键的：
a：
id int not null,
name char(20)
);
2、带主键的：
a：
CREATE TABLE t1(
id int not null primary key,
name char(20)
);
b：复合主键
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、带默认值的：
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'
);
转贴的：
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')))
;
(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')))
;
相关阅读 更多 +
