-- mysql数据库程序设计笔记：
create database db_sp default character set gb2312 default collate gb2312_chinese_ci;
use db_sp;
create table S
(
SNO char(5) comment '供应商编号',
SNAME varchar(255) not null unique comment '供应商名称,不为空且唯一',
STATUS int(3) comment '状态',
CITY varchar(255) comment '所在城市',
primary key (SNO),
constraint ck_S check(CITY !='London' or STATUS=20)
)engine=InnoDB default charset='utf8' comment='供应商表' ;
-- show create table S\G;
create table P(
PNO char(5) comment '零件编号',
PNAME varchar(255) not null comment '零件名称',
COLOR varchar(255) comment '颜色' check(COLOR in ('Red','Yellow','Green','Blue')) ,
WEIGHT int comment '重量',
primary key (PNO)
)engine=InnoDB default charset='utf8' comment='零件表';
-- show create table P\G;
create table SP(
SNO char(5) comment '供应商编号',
PNO char(5) comment '零件编号',
JNO varchar(255) comment '供应编号',
QTY int comment '供应量',
primary key (SNO,PNO),
constraint pk_spj1 foreign key(SNO) references S(SNO),
constraint pk_spj2 foreign key(PNO) references P(PNO)
)engine=InnoDB default charset='utf8' comment '供应表';