MySQL 建表语法
来源:互联网 发布:d3.js v3中文api 编辑:程序博客网 时间:2024/04/29 02:26
1、最简单的:
2、带主键的:
a:
b:复合主键
3、带默认值的:
转贴的:
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 常用建表语句
- 堆排序原理及算法实现
- Link-local Multicast Name Resolution
- Linux 上的基础网络设备详解
- 一个合格的程序员应该读过哪些书
- HDU 2524 矩形A + B
- MySQL 建表语法
- Dijkstra算法 用C++面向对象写
- 总有一天我会绽放属于自己的光彩
- mysql表设计----列类型介绍
- Hadoop 1.x 版本中的Task Controller 介绍
- maven国内镜像配置(Ubuntu)
- 函数返回的过程
- Hibernate根据查询内容封装成对象(DTO)
- Oracle MERGE语句