MySQL数据库表结构设计与SQL插入语句示例

本文介绍了如何使用SQL语句创建数据库表结构，涵盖了图书、图书类别、顾客、商品评价、订单、订单细节和收货地址列表等多个实体，并提供了一个向顾客表中插入两条记录的示例。

表结构设计

以下SQL语句用于创建数据库表结构：sql-- 图书表CREATE TABLE books ( cBookID char(10) NOT NULL, vBookName varchar(100) NOT NULL, vPressHouse varchar(100) DEFAULT NULL, vAuthor varchar(50) DEFAULT NULL, vISBN varchar(50) DEFAULT NULL, mPrice decimal(10,1) DEFAULT NULL, mSalePrice decimal(10,1) DEFAULT NULL, dPublicationDate date DEFAULT NULL, siQuantity smallint DEFAULT '1', vIntroduction varchar(1000) DEFAULT NULL, vPictureUrl varchar(1000) DEFAULT NULL, cTypeID char(5) NOT NULL, cSaleStatus char(1) DEFAULT '1', PRIMARY KEY ( cBookID ), KEY RefBooktype2 ( cTypeID ), CONSTRAINT RefBooktype2 FOREIGN KEY ( cTypeID ) REFERENCES booktype ( cTypeID )) ;

-- 图书类别表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 )) ;

SQL插入语句示例

以下SQL语句演示了如何向顾客表 (customer) 中插入两条记录：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-1', '四川', '成都', 99, '四川省成都市双流航空港成都信息工程大学'), ('b665a29c-136e-11ec-b124-7897d484c920', '心田', '13578912389', 'xintian@163.com', 'Cuit', '张田田', '男', '2003-10-1', '四川', '成都', 100, '四川省成都市双流航空港成都信息工程大学');

请注意：

• 执行此SQL语句之前，请确保已连接到数据库，并且具有足够的权限执行插入操作。* 根据实际情况，你可能需要修改表名、字段名和值的部分内容。