系统实例图如下
一、建表
我们先确定了大概需要哪些表和对应的字段:需要用户信息、订单信息、产品信息、产品类别这几个表。其中user和order是一对多的关系,order和product是多对多的关系。
关于class:我们参考了这个系统,决定添加一个产品类的表,原因一是增加了系统功能,二是参考的其他建表语句都是这样的(无所谓,我会依样画葫芦),感觉这样要便于管理一点?
由于之前没有sql的基础,作了一定了解之后我们学会了外键、多表连接来解决一对多和多对多的关系。通过建立一个中间表orderproduct来连接订单和产品
CREATE TABLE `USER` (
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50),
`password` varchar(50),
create_time datetime comment'创建时间'
)comment'用户表';
CREATE TABLE `order` (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
order_number VARCHAR(20)comment'订单数量',
total_price varchar(20)comment'订单中商品总价',
order_state varchar(10)comment'订单状态',
FOREIGN KEY (user_id) REFERENCES User(id)
)comment'订单表';
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
productclass_id int,
product_name VARCHAR(100),
product_price DECIMAL(10, 2)
)comment'产品表';
CREATE TABLE OrderProduct (
id int PRIMARY KEY AUTO_INCREMENT,
order_id INT,
product_id INT,
FOREIGN KEY (order_id) REFERENCES `Order`(id),
FOREIGN KEY (product_id) REFERENCES Product(id)
)comment'订单产品中间表';
后续又改进了一下字段,增加了productclass表,并在product表里添加了一个连接productclass表的外键
CREATE TABLE productclass(
id int PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(50)
)comment'体检类型';
ALTER table productclass MODIFY class_name VARCHAR(10);
ALTER table product add exam_item VARCHAR(10)comment'在product表中增加体检项目信息';
ALTER table product add suit_crowds VARCHAR(50)comment'在product表中增加适宜人群信息';
ALTER table product add mec_grade VARCHAR(20)comment'在product表中增加机构等级信息';
ALTER TABLE product add CONSTRAINT FOREIGN KEY(productclass_id) REFERENCES productclass(id);
这样我们的表就初步建好了
二、填充数据
这里就不多赘述了
INSERT INTO productclass VALUES(NULL,'老年体检'),(NULL,'专项体检'),(NULL,'常规体检');
insert into product values (null,1,'男性健康标准体检套餐',3018.50,'25项','男','公立三甲');
insert into product values (null,1,'女性健康标准体检套餐',3899.99,'28项','女','公立三甲');
insert into product values (null,1,'关爱父母基础体检卡',768.80,'36项','老年','公立三甲');
insert into product values (null,1,'关爱父母升级体检卡',3000.00,'25项','老年','公立三甲');
insert into product values (NULL,2,'心脑血管基本套餐',3495.55,'15项','男','公立三甲');
insert into product values (NULL,2,'支气管哮喘套餐',895.00,'8项','不限','公立三甲');
insert into product values (NULL,2,'贫血筛查',220.00,'16项','女','公立三甲');
insert into product values (NULL,2,'肝功能异常检查套餐',599.99,'15项','不限)','公立三甲');
insert into product values (NULL,2,'消化系统体检套餐',1017.00,'10项','青年(20-30)/中年(30-50)/中老年(50-70)','公立三甲');
insert into product values (NULL,3,'基础型体检套餐',168.33,'12项','不限','公立三甲');
insert into product values (NULL,3,'经济型体检套餐',1013.00,'24项','不限','公立三甲');
insert into product values (NULL,3,'至尊型体检套餐',5346.66,'30项','不限','公立三甲');
alter table product drop foreign key product_ibfk_1;
show create table product;
接下来就准备写查询语句。