1、普通的建表语句
CREATE TABLE test(
id int(10),
name varchar(20));
2、带主外键的建表语句
a、主键(primary key)
CREATE TABLE test(
id int(5) NOT NULL PRIMARY KEY,
name varchar(20));
或
CREATE TABLE test(
id int(5) NOT NULL,
name varchar(20),
PRIMARY KEY(id));
b、复合主键(primary key(XX,XX))
CREATE TABLE test(
id int(5) NOT NULL,
name varchar(20),
PRIMARY KEY(id,name));
c、主外键(foreign key(xx) references table_name(XX))
主键表course:
CREATE TABLE course(
cid int(10) NOT NULL,
cname varchar(20),
PRIMARY KEY(cid));
外键表students:
CREATE TABLE student(
sid int(5) NOT NULL PRIMARY KEY,
sname varchar(20),
cid int(10),
FOREIGN KEY(cid) REFERENCES course(cid));
指定外键约束名为 student_course_fk:
CREATE TABLE student(
sid int(5) NOT NULL PRIMARY KEY,
sname varchar(20),
cid int(10),
CONSTRAINT student_course_fk FOREIGN KEY(cid) REFERENCES course(cid));
3、默认值(default)
CREATE TABLE student(
id int(5) NOT NULL,
name varchar(20),
sex char(2) DEFAULT '男');
4、自增(自增字段必须为主键,auto_increment)
CREATE TABLE student(
id int(5) NOT NULL auto_increment,
name varchar(20),
sex char(2) DEFAULT '男',
PRIMARY KEY(id));