数据库实战(shop数据库商家版)

在我们学习过数据库后,实战是必不可少的,所以给大家分享一下我的一个数据库的设计

此数据库是基于商家(商店)的经营模式来进行创建的,方便商家的数据管理和后续商品的销量分析;

接下来话不多说,开始创建

创建数据库并且切换到这个数据库

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//

如需创建高级的功能可以根据自己的思路进行设计,以上仅供参考;