MySQL数据库电商数据插入SQL

这篇文章提供了创建MySQL电商数据库的SQL语句，涵盖了图书、顾客、订单等表格，并演示了如何向顾客表格插入两条数据。
创建表格

以下是创建数据库表格的SQL语句：

-- 图书类别表DROP TABLE IF EXISTS booktype ;CREATE TABLE booktype (   cTypeID char(5) NOT NULL,   vTypeName varchar(100) NOT NULL,  PRIMARY KEY ( cTypeID ));

-- 顾客表DROP TABLE IF EXISTS customer ; CREATE TABLE customer (   cCustomerID char(36) NOT NULL,   vNickName varchar(50) DEFAULT NULL,   vPhone varchar(20) NOT NULL,   vEmail varchar(100) DEFAULT NULL,   vPassword varchar(200) NOT NULL,   vCustomerName varchar(100) NOT NULL,   vSex varchar(10) DEFAULT NULL,   dBirthDay date DEFAULT NULL,   vProvince varchar(100) NOT NULL,   vCity varchar(100) NOT NULL,   score int DEFAULT NULL,   vAddress varchar(200) NOT NULL,  PRIMARY KEY ( cCustomerID ));

-- 商品评价表DROP TABLE IF EXISTS evaluation ;CREATE TABLE evaluation (   cEvaluationID char(36) NOT NULL,   iProductPoint int DEFAULT NULL,   comment varchar(2000) DEFAULT NULL,   vPictureUrl varchar(1000) DEFAULT NULL,   iPackPoint int DEFAULT NULL,   iLogisticsPoint int DEFAULT NULL,   iCourierPoint int DEFAULT NULL,   dEvaluationDate datetime DEFAULT NULL,   cOrderNo char(11) NOT NULL,   cBookID char(10) NOT NULL,  PRIMARY KEY ( cEvaluationID ),  KEY RefOrderDetail50 ( cOrderNo , cBookID ),  CONSTRAINT RefOrderDetail50 FOREIGN KEY ( cOrderNo ,  cBookID ) REFERENCES orderdetail ( cOrderNo ,  cBookID ));

-- 订单细节表DROP TABLE IF EXISTS orderdetail ;CREATE TABLE orderdetail (   cOrderNo char(11) NOT NULL,   cBookID char(10) NOT NULL,   mSalePrice decimal(10,1) DEFAULT NULL,   siQty smallint NOT NULL DEFAULT '1',   mBookCost decimal(10,1) DEFAULT NULL,  PRIMARY KEY ( cOrderNo , cBookID ),  KEY RefBooks33 ( cBookID ),  CONSTRAINT RefBooks33 FOREIGN KEY ( cBookID ) REFERENCES books ( cBookID ),  CONSTRAINT RefOrders34 FOREIGN KEY ( cOrderNo ) REFERENCES orders ( cOrderNo ));

-- 订单表DROP TABLE IF EXISTS orders ;CREATE TABLE orders (   cOrderNo char(11) NOT NULL,   dOrderDate datetime DEFAULT NULL,   mBookCost decimal(10,1) DEFAULT NULL,   mShippingCost decimal(10,1) DEFAULT NULL,   mTotalCost decimal(10,1) DEFAULT NULL,   cPayStatus char(1) DEFAULT NULL,   vPayType varchar(20) DEFAULT NULL,   dPayTime datetime DEFAULT NULL,   cShippingStatus char(1) DEFAULT NULL,   dShippingDate datetime DEFAULT NULL,   cReceiveStatus char(1) DEFAULT NULL,   dReceiveTime datetime DEFAULT NULL,   vRecipientName varchar(100) DEFAULT NULL,   vRecipientProvince varchar(100) DEFAULT NULL,   vRecipientCity varchar(100) DEFAULT NULL,   vRecipientAddress varchar(1000) DEFAULT NULL,   vRecipientPhone varchar(50) DEFAULT NULL,   cCustomerID char(36) NOT NULL,  PRIMARY KEY ( cOrderNo ),  KEY RefCustomer16 ( cCustomerID ),  CONSTRAINT RefCustomer16 FOREIGN KEY ( cCustomerID ) REFERENCES customer ( cCustomerID ));

-- 收货地址列表DROP TABLE IF EXISTS recipientlist ;CREATE TABLE recipientlist (   cRecipientID char(36) NOT NULL,   vRecipientName varchar(100) DEFAULT NULL,   vRecipientPhone varchar(50) DEFAULT NULL,   vRecipientProvince varchar(100) DEFAULT NULL,   vRecipientCity varchar(100) DEFAULT NULL,   vRecipientAddress varchar(1000) DEFAULT NULL,   cCustomerID char(36) NOT NULL,  PRIMARY KEY ( cRecipientID ),  KEY RefCustomer31 ( cCustomerID ),  CONSTRAINT RefCustomer31 FOREIGN KEY ( cCustomerID ) REFERENCES customer ( cCustomerID )) ;

### 插入数据

以下是将两条记录插入到顾客(customer)表中的SQL语句：sqlINSERT INTO customer (cCustomerID, vNickName, vPhone, vEmail, vPassword, vCustomerName, vSex, dBirthDay, vProvince, vCity, score, vAddress) VALUES ('b665a29c-136e-11ec-b124-7567d484c929', '张彦', '13589112389', 'zhangyan@163.com', 'Cuit', '张彦', '男', '2003-10-01', '四川', '成都', 99, '四川省成都市双流航空港成都信息工程大学');

INSERT INTO customer (cCustomerID, vNickName, vPhone, vEmail, vPassword, vCustomerName, vSex, dBirthDay, vProvince, vCity, score, vAddress) VALUES ('b665a29c-136e-11ec-b124-7897d484c920', '心田', '13578912389', 'xintian@163.com', 'Cuit', '张田田', '男', '2003-10-01', '四川', '成都', 100, '四川省成都市双流航空港成都信息工程大学');

希望这些SQL语句能帮助你创建电商数据库并插入数据