mysql数据库代码怎么实现

Question

根据几个表进行实现，根据资料中的数据库各表的结构，在market数据库中使用sql语句创建卖家表，卖家表，商品表，订单表，订单明细表的表结构代码

Answer 1

--创建Market数据库
CREATE DATABASE IF NOT EXISTS Market;

--使用Market数据库
USE Market;

--创建customers表
CREATE TABLE IF NOT EXISTS customers (
  customerid INT(11) NOT NULL AUTO_INCREMENT,
  firstname VARCHAR(50) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  address VARCHAR(100) DEFAULT NULL,
  city VARCHAR(50) DEFAULT NULL,
  state VARCHAR(2) DEFAULT NULL,
  zip VARCHAR(10) DEFAULT NULL,
  country VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (customerid)
);

--创建vendors表
CREATE TABLE IF NOT EXISTS vendors (
  vendorid INT(11) NOT NULL AUTO_INCREMENT,
  vendorname VARCHAR(50) NOT NULL,
  address VARCHAR(100) DEFAULT NULL,
  city VARCHAR(50) DEFAULT NULL,
  state VARCHAR(2) DEFAULT NULL,
  zip VARCHAR(10) DEFAULT NULL,
  country VARCHAR(50) DEFAULT NULL,
  contactname VARCHAR(50) DEFAULT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  email VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (vendorid)
);

--创建products表
CREATE TABLE IF NOT EXISTS products (
  productid INT(11) NOT NULL AUTO_INCREMENT,
  productname VARCHAR(50) NOT NULL,
  vendorid INT(11) NOT NULL,
  category VARCHAR(50) DEFAULT NULL,
  price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (productid),
  CONSTRAINT fk_vendorid FOREIGN KEY (vendorid) REFERENCES vendors(vendorid)
);

--创建orders表
CREATE TABLE IF NOT EXISTS orders (
  orderid INT(11) NOT NULL AUTO_INCREMENT,
  customerid INT(11) NOT NULL,
  orderdate DATE NOT NULL,
  shippeddate DATE DEFAULT NULL,
  shipname VARCHAR(50) DEFAULT NULL,
  shipaddress VARCHAR(100) DEFAULT NULL,
  shipcity VARCHAR(50) DEFAULT NULL,
  shipstate VARCHAR(2) DEFAULT NULL,
  shipzip VARCHAR(10) DEFAULT NULL,
  shipcountry VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (orderid),
  CONSTRAINT fk_customerid FOREIGN KEY (customerid) REFERENCES customers(customerid)
);

--创建orderitems表
CREATE TABLE IF NOT EXISTS orderitems (
  orderitemid INT(11) NOT NULL AUTO_INCREMENT,
  orderid INT(11) NOT NULL,
  productid INT(11) NOT NULL,
  quantity INT(11) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (orderitemid),
  CONSTRAINT fk_orderid FOREIGN KEY (orderid) REFERENCES orders(orderid),
  CONSTRAINT fk_productid FOREIGN KEY (productid) REFERENCES products(productid)
);

以上代码完成了Market数据库的创建和买家表(customers)、卖家表(vendors)、商品表(products)、订单表 (orders)订单明细表 (orderitems)的表结构的创建。