MySQL 建表语法
来源:互联网 发布:k线公式源码 编辑:程序博客网 时间:2024/03/29 09:39
1、最简单的:
CREATE TABLE t1(
id int not null,
name char(20)
);
id int not null,
name char(20)
);
2、带主键的:
a:
CREATE TABLE t1(
id int not null primary key,
name char(20)
);
id int not null primary key,
name char(20)
);
b:复合主键
CREATE TABLE t1(
id int not null,
name char(20),
primary key (id,name)
);
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')))
;
- MySQL 建表语法
- MySQL 建表语法
- mysql建表语法 --转载
- mysql建表语句
- mysql建表语句
- MySQL建表语句
- mysql 建表语句
- mysql导出建表语句
- MySQL 查看建表语句
- mysql必知必会--建表语句
- mysql的建表语句
- MySql简单建表语句
- MySql 之建表语句
- mysql导出建表语句
- mysql建表语句二
- 【mysql】mysql 常用建表语句
- 【mysql】mysql 常用建表语句
- 【mysql】mysql 常用建表语句
- ORACLE 11G 在 RHEL 5下的安装
- 大型Web2.0站点构建技术初探
- 跑记~~(二)
- 软件开发中的注意事项
- 如果你不能从回忆中走出,我只能离开你
- MySQL 建表语法
- Makefile符号说明
- 把杯子放下
- 对文件进行二进制的循环处理时byte[ ]比blob更有效率
- 绘制透明按钮的另外一法
- 脚本解释器构架(三)
- 爱你却无缘牵手
- 唐僧师徒用了手机之后
- AWK 常用功能