MySQL table creation

1. Table name:Customer

Properties: ID character type maximum of ten characters--Customer number

Name character type maximum of three characters--Customer name

Sex character type Max 2 characters--gender

Mobile character type maximum one character--mobile phone

Address character type maximum of three characters--Home address

constraints: ID--main code; NAME--Non-empty attribute; SEX--The value "male" or "female";

MOBILE-Uniqueness; ADDRESS-default is unkown;

2. Table name:orderbook

Properties: CSID character type maximum of ten characters--Customer number

CMID character type maximum of nine characters--product number

Count integer--Order quantity

Bookdate Date Type--order date

Takedate Date Type-delivery date

constraints: CSID,CMID--main code; Order quantity is greater than 0; Order date is less than delivery date;

csid--External code, referring to the Customer table ID;

CMID--External code, referring to The ID of the commodity table ;

3. Table name:Commodity

Properties: ID character type maximum of nine characters--product number

Name character type maximum of three characters--product name

Manufacture character type maximum of three characters-- manufacturer

Price decimal Type maximum no more than 4 digits, reserved 2 decimal Places--Commodity prices

constraints: ID--main code; NAME--Non-null;

1. CREATE TABLE Customer (

ID varchar (primary key),
Name varchar (16),
Sex varchar (2),
Mobile varchar (11),
Address varchar (50),

);

2. CREATE TABLE Orderbook (

CSID varchar (10),
Cmid varchar (12),
Count Int (11),
Bookdate datetime,
Takedate datetime,
Check (takedate>bookdate),
Check (count>0),
Foreign KEY (CSID) references Customer (ID),
Foreign KEY (CMID) references commodity (ID),
Primary KEY (Csid,cmid)

);

3. CREATE TABLE Commodity (

ID varchar (primary key),
Name varchar () is not NULL,
Manufacture varchar (20),
Price Decimal (4,2)

);

MySQL table creation