在我们学习过数据库后,实战是必不可少的,所以给大家分享一下我的一个数据库的设计
此数据库是基于商家(商店)的经营模式来进行创建的,方便商家的数据管理和后续商品的销量分析;
接下来话不多说,开始创建
创建数据库并且切换到这个数据库
create database shop;
use shop;
表1:item(订单表)
create table item(
numeber varchar(10) primary key,
user_id varchar(10),
goods_id varchar(10),
price float(10,2),
discount_price float,
amount int(10),
date datetime);
表2:user(用户表)
create table user(
id varchar(10) primary key,
name varchar(20),
sex char(4),
age int(3),
phone int(11)
);
表3:goods(商品表)
create table goods(
id varchar(10) primary key,
name varchar(20),
type int(10),
price float(10,2),
amount int(10),
date date
);
表4:goods_type(商品类型表)
create table goods_type(
id int(10) primary key,
name varchar(20),
type_amount int(10)
);
表5:supply(供应表)
create table supply(
order int(20) primary key,
goods_id varchar(10),
bussiness_id varchar(10),
price float(10,2),
goods_amount int(20),
date date
);
表6:business(供应商表)
create table business(
id varchar(10) primary key,
name varchar(20),
address varchar(255),
phone vachar(255)
);
基础表已经创建完毕,开始创建外键的约束;
alter table item
add constraint 'userid' foreign key ('user_id') references 'user'('id') ,
add constraint 'goodsid' foreign key ('goods_id') references 'goods'('id') ;
alter table goods
add constraint 'type' foreign key ('type') references 'goods_type'('id') ;
alter table supply
add constraint 'goodsid' foreign key ('goods_id') references 'goods'('id') ,
add constraint 'businessid' foreign key ('business_id') references 'business'('id') ;
可能大家对于外键的理解不是很清楚,那么我将给大家稍微的讲解一下;
1,外键是与另一个表中的主键进行连接的,在主键列中没有添加数据的时候外键表是不能够添加数据,这样保证了数据的准确性,例如在学生表中出现了3班的学生,但是在班级表中就没有三班,这样数据不就出现了严重的错误,数据更新的时候也是一样;
2,更新的时候,方便快速多表更新,例如班级表中,三班变为了四班,那么学生表中的三班学生的信息会自动的改为四班
扩展:外键约束的几种类型:
1、RESTRICT 限制(默认)
如果有外键关联了tb_class 的id,则tb_class 不能删除被关联的记录、不能更新关联记录deptId字段的值(会报错)。
2、CASCADE 级联
删除tb_class 中的记录时,会自动删除tb_student中与之关联的记录;修改tb_class中tb_student字段的值时,会自动修改tb_student中与之关联的记录的外键字段的值(同步变化)。
3、NO ACTION 不做处理
删除、更新tb_class中的id字段的值时,tb_student中与之关联的记录不作任何处理。此种策略需要存储引擎支持,如果存储引擎不支持,会自动换为RESTRICT。
4、SET NULL 置为NULL
删除tb_student的记录,或者更新id字段的值,会自动将tb_student中与之关联的记录的外键字段的值置为NULL。
接下来看一下数据库的ER模型
大家也可以使用Navicat,效率比比较高,如果是想要练习数据库,那就老老实实的敲吧;
数据表已经建立完成,下面开始导入数据,由于我们建立了外键的约束所以数据的导入要注意一些事项:
要先对有外键约束的表进行插入数据,首先要这个约束的列在另一个表中的数据进行插入,例如:对goods表的插入,要先把goods_type这个表的数据进行完善;
插入数据的代码如下:
insert into goods_type
values
(3,'服装',50),
(4,'调味料',50);
这样插入数据往往是效率很低的,这是我们可以使用图形化界面的软件<navicat>,下面我将给大家操作这个软件的插入数据的流程;
部分excal的数据截图:
数据的字段名要与数据库的字段名相同;
选择想要插入的行,进行插入:
重复以上操作完成数据的插入;
接下来就是创建必要的触发器和存储过程和函数了;这样方便数据的维护,省时省力;
存储过程:
1,查询所有的订单
delimiter //
create procedure sales()
begin
select * from item;
end//
delimiter ;
2,输入用户的id查询所有这个用户的订单
create procedure sales_id(in id varchar(10))
begin
select *
from item
where user_id='id';
end//
3,查询获得的总利润
delimiter //
create procedure gross_profit()
begin
select sum(item.price-supply.price) 总利润
from item,supply,goods
where item.goods_id=goods.id and goods.id=supply.goods_id;
end//
触发器:
1,当用户的消费额度大于某个值的时候,设置打折
create trigger ITEM_INSERT before insert on item for each row
begin
if new.price/100>1
then set new.price=new.price-floor(new.price/100)*10;
else set new.price=new.price;
end if;
End//
2,根据商
品的生产日期与当前日期作差达到设置的最低时间进行降价处理,避免商品挤压
create trigger ITEM_INSERT before insert on item
for each row
begin
DECLARE goods_date VARCHAR(10);DECLARE item_date VARCHAR(10);
SELECT g.date INTO goods_date FROM goods g WHERE g.id = (SELECT i.goods_id FROM item i WHERE i.number=new.number);
SELECT DATE_FORMAT
(i.date,'%Y-%m-%d') INTO item_date FROM item i WHERE i.number=new.number;IF UNIX_TIMESTAMP(STR_TO_DATE(item_date,'%Y-%m-%d'))+10*24*60*60 >= UNIX_TIMESTAMP(STR_TO_DATE(goods_date,'%Y-%m-%d'))
THEN
set new.sale_price=new.price*0.7;ELSEif new.price/100>1
then
set new.sale_price=new.price-floor(new.price/100)*10;
else set new.sale_price=new.price;
end if;
END IF;End
存储函数:
1,查询商品的等级
CREATE DEFINER=`root`@`localhost` FUNCTION `goods_grade`(`id` varchar(10)) RETURNS char(2) CHARSET utf8
BEGIN
DECLARE grade char(2);
DECLARE amount int;
SELECT sum(i.amount) INTO amount FROM item i where i.goods_id = id GROUP BY i.goods_id;
CASE amount
WHEN amount>=10 THEN
set grade='A';
WHEN amount<10&&amount>=5 THEN set grade = 'B';
ELSE
set grade = 'C';
END CASE;
RETURN grade;
END
安全性分析:
防止爆仓
dilimiter //
create trigger ITEM_INSERT before insert on item
for each row
begin
declare num int;
select goods.amount into num from goods
where new.item.goods_id=goods.id
if new.item.amount>num
then set new.item.amount=num;
else update goods set goods.amount=num-new.item.amount
where goods.id=new.goods_id;
end if;
end//
如需创建高级的功能可以根据自己的思路进行设计,以上仅供参考;