商品信息管理数据库设计

1. 数据库设计

1.1. 概要设计（E-R 图）

图 1.1 系统 E-R 图

1.2. 逻辑设计（关系模型）

(1) 管理员表（管理员 ID，姓名，密码，生日，性别）， 其中“管理员 ID”是主键；
(2) 商品表（商品 ID，商品名称，售价，单位），其中，“商品ID”是主键；
(3) 交易单号表（交易 ID，今日交易次数，管理员 ID，创建时间），其中，“交易 ID”是主键，“管理员 ID”是外键；
(4) 商品交易表（商品交易 ID，商品 ID，交易 ID，售价，数量，金额），其中，“交易 ID”是主键，“商品 ID”、“交易 ID”是外键；
(5)仓库表（仓库 ID，商品I
D，数量，创建时间，管理员 ID），其中，“仓库 ID”是主键，“商品 ID”、“管理员 ID”是外键。

1.3. 物理设计（数据库表）

1.3.1. 管理员表（user）
字段 数据类型 是否为空 是否为主键 其他约束 描述
id int 否 是 无 管理员 ID
user_name varchar 否 否 无 姓名
password varchar 否 否 无 密码
birthday date 是 否 无 生日
sex char 是 否 无 性别
1.3.2. 商品表（goods）
字段 数据类型 是否为空 是否为主键 其他约束 描述
id int 否 是 无 商品 ID
goods_name varchar 否 否 无 商品名称
price double 否 否 无 售价
unit varchar 否 否 无 单位
1.3.3. 交易单号表（transaction）
字段 数据类型 是否为空 是否为主键 其他约束 描述
id int 否 是 无 交易单号ID
user_id int 否 否 外键 管理员 ID
create_time timestamp 否 否 无 创建时间
number_of
_transactions int 否 否 无 今日交易次数
1.3.4. 商品交易表（goods_transaction）
字段 数据类型 是否为空 是否为主键 其他约束 描述
id int 否 是 无 商品交易ID
goods_id int 否 否 外键 商品 ID
price double 是 否 无 售价
quantity int 是 否 无 数量
money double 是 否 无 金额
transaction_id int 否 否 外键 交易ID
1.3.5. 仓库表（ware）
字段 数据类型 是否为空 是否为主键 其他约束 描述
id int 否 是 无 仓库ID
goods_id int 否 否 外键 商品 ID
quantity int 否 否 无 商品数量
create_time timestamp 否 否 无 创建时间
user_id int 否 否 外键 管理员 ID

2. 数据库实现

2.1. 建立数据库

(1) 效果截图

建立数据库

创建数据库表

插入数据

(2) 数据库语句
数据库语句***
drop database if exists goodssys;

create database goodssys;
use goodssys;
alter database goodssys character set utf8mb4 collate utf8mb4_unicode_ci;

create table user(
id int primary key auto_increment not null,
user_name varchar(50) not null,
password varchar(50) not null,
birthday date default null,
sex char(10) default null
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

create table goods(
id int primary key auto_increment not null,
goods_name varchar(150) not null,
price double,
unit varchar(10)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

create table transaction(
id int primary key auto_increment not null,
user_id int not null,
number_of_transactions int not null,
create_time timestamp not null default current_timestamp,
FOREIGN KEY(user_id) REFERENCES user(id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

create table goods_transaction(
id int primary key auto_increment not null,
goods_id int not null,
price double,
quantity int,
money double,
transaction_id int not null,
FOREIGN KEY(transaction_id) REFERENCES transaction(id),
FOREIGN KEY(goods_id) REFERENCES goods(id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

create table ware(
id int primary key auto_increment,
goods_id int not null,
quantity int default 0,
create_time timestamp not null default current_timestamp,
user_id int not null,
FOREIGN KEY(user_id) REFERENCES user(id),
FOREIGN KEY(goods_id) REFERENCES goods(id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

---

数据库语句***
insert into user(user_name,password,birthday,sex) values
(“小明”,“123456”,“2002-9-15”,“女”),
(“曾今”,“123456”,“2002-9-15”,“男”),
(“章晓红”,“123456”,“2002-9-15”,“女”);

insert into goods(goods_name,unit,price) values
(“巧克力”,“个”,200),
(“薯片”,“个”,10),
(“饼干”,“个”,20),
(“腰果”,“个”,110),
(“红枣”,“包”,50),
(“糯米糍”,“盒”,20),
(“开心果”,“个”,60),
(“每日坚果”,“箱”,150),
(“蛋黄酥”,“箱”,45),
(“虎皮鸡爪”,“包”,59);

insert into ware(goods_id,quantity,user_id) values
(1,100,1),
(2,100,1),
(3,100,2),
(4,100,2),
(4,100,3);

insert into goods_transaction(goods_id,price,quantity,money,transaction_id) values
(1,100,2,200,1),
(2,10,2,20,1),
(3,20,2,40,1),
(4,100,2,200,1),
(5,10,2,20,1),
(6,20,2,40,1),
(1,100,2,200,2),
(2,10,2,20,2),
(3,20,2,40,2);

insert into transaction(number_of_transactions,user_id) values
(1,1),
(2,1),
(3,1);

---

2.2. 单表查询

(1) 数据库语句

– 查询 goods 表中 goods_name 列所有商品名
SELECT goods_name FROM goods;

– 对 ware 表的user_id 字段进行去重
SELECT DISTINCT user_id FROM ware;

– 查询 goods 表，为 goods_name 指定别名 ‘商品名’
SELECT goods_name AS ‘商品名’, unit AS ‘单位’ FROM goods;

– 查询 goods 表的所有记录，并对price 字段进行排序,默认是升序asc
SELECT * FROM goods ORDER BY price;

– 查询 goods 表的所有记录，并对price 字段进行排序，降序desc
SELECT * FROM goods ORDER BY price desc;

– 在 user 数据表中查询账户和密码
SELECT * FROM user WHERE user_name=“小明” AND password=“123456”;

– 在 user 数据表中查询姓“”的用户
SELECT * FROM user WHERE user_name LIKE ‘小%’;

– 在表 user 中查询birthday 在 2000-12-11 到 2002-12-25 之间的信息
SELECT * FROM user
WHERE birthday BETWEEN ‘2000-12-11’ AND ‘2002-12-25’;

– 查询用户id数量大于等于2的用户id
SELECT user_id FROM ware GROUP BY user_id HAVING count(user_id)>=2;

---

2.3. 多表查询

(1) 数据库语句
多表查询

– 在 ware 表和 user 表中查询所有用户名和相对应的商品id和数量，且商品数量大于等于100
SELECT u.user_name,w.goods_id,w.quantity FROM ware w
LEFT JOIN user u ON w.user_id=u.id
WHERE w.quantity>=100;

– 查询所有用户的 name（user表） 、商品名 ( goods 表) 和 仓库中商品数量（ware表）
SELECT u.user_name,g.goods_name,w.quantity FROM ware w
LEFT JOIN user u ON u.id=w.user_id
LEFT JOIN goods g ON g.id=w.goods_id;

– 查询 用户id为1的每个交易金额的平均数。
SELECT transaction_id,AVG(money) FROM goods_transaction
WHERE transaction_id IN (SELECT id FROM transaction WHERE user_id = 1)
GROUP BY transaction_id;

---

2.4. 索引

(1) 效果截图

(2) 数据库语句
数据库语句***
– 在商品表goods的商品名称goods_name上创建一个索引idx_goodsname
– DROP INDEXES idx_goodsname ON goods;
CREATE INDEX idx_goodsname ON goods (id,goods_name);
– SHOW INDEXES FROM goods;

---

2.5. 视图

(1) 数据库语句
数据库语句***
创建视图
– 创建一个只包含商品名称、数量、单位、售价和金额字段数据的商品交易信息视图
CREATE VIEW v_goods_transaction AS
SELECT g.goods_name 商品名称,t.quantity 数量,g.unit 单位,t.price 售价,t.money 金额
FROM goods_transaction AS t
LEFT JOIN goods AS g
ON g.id=t.goods_id;

– 创建一个只包含仓库id、商品名称、数量、创建者和创建时间字段数据的商品交易信息视图
CREATE VIEW v_ware AS
SELECT w.id 仓库id,g.goods_name 商品名称,w.quantity 数量,u.user_name 创建者,w.create_time 创建时间
FROM ware AS w
LEFT JOIN goods AS g ON g.id=w.goods_id
LEFT JOIN user AS u ON u.id=w.user_id;

---

2.6. 存储函数

(1) 数据库语句
数据库语句***
创建函数
– 创建输入用户id返回用户姓名的查询函数语句
– DROP FUNCTION IF EXISTS sales_grade;
DELIMITER //
CREATE FUNCTION find_username_byid(u_id int)
RETURNS VARCHAR(20)
BEGIN
RETURN(SELECT * FROM user WHERE id=u_id);
END //
DELIMITER;
SELECT find_username_byid(1);

– 创建判断金额等级的函数语句
DELIMITER //
CREATE FUNCTION sales_grade(total_money double)
RETURNS VARCHAR(20)
BEGIN
CASE
WHEN total_money>=3000 THEN RETURN ‘金牌销量’;
WHEN (total_money<3000 and total_money>=2000) THEN RETURN ‘银牌销量’;
WHEN (total_money<2000 and total_money>=1000) THEN RETURN ‘铜牌销量’;
ELSE RETURN ‘普通销量’;
END CASE;
END //
DELIMITER ;
SELECT sales_grade(5000);

---

2.7. 存储过程

(1) 数据库语句
数据库语句***
– 创建查找管理员管理的交易单号的过程语句
DROP PROCEDURE sales_grade_proc;
DELIMITER //
CREATE PROCEDURE user_transaction_byid(u_id int)
BEGIN
SELECT u.user_name, t.id,t.create_time
FROM transaction AS t
LEFT JOIN user AS u
ON u.id=t.user_id WHERE user_id=u_id;
END //
DELIMITER ;
CALL user_transaction_byid(1);

– 创建判断销量等级的过程语句
DELIMITER //
CREATE PROCEDURE sales_grade_proc(total_money int)
BEGIN
DECLARE t int;
SET t=total_money;
SELECT t;
IF t >= 3000 THEN
SELECT t,‘金牌销量’;
ELSEIF t < 3000 AND t >= 2000 THEN
SELECT t,‘银牌销量’;
ELSEIF t < 2000 AND t >= 1000 THEN
SELECT t,‘铜牌销量’;
ELSE
SELECT t,‘普通销量’;
END IF;
END //
DELIMITER ;
CALL sales_grade_proc(800);

---

2.8. 游标

(1) 数据库语句
数据库语句***
触发事件-游标
DROP TABLE IF EXISTS user_nv;
CREATE TABLE user_nv(
id int PRIMARY KEY NOT NULL,
user_name VARCHAR(50) NOT NULL,
password VARCHAR(50) DEFAULT NULL,
sex CHAR(10) NOT NULL,
birthday DATE DEFAULT NULL
);

delimiter ;;
CREATE DEFINER=root@localhost PROCEDURE usernvdata()
begin
#创建局部变量声明(搞不明白可以用desc student;查看结构)
#定义变量
declare v_id int default null;
declare v_user_name varchar(20) default null;
declare v_password varchar(20) default null;
declare v_sex CHAR(10) default null;
declare v_birthday varchar(20) default null;
#定义错误触发条件
declare v_curs int default 0;
#定义游标
declare mycursor cursor for select id,user_name,password,sex,birthday from user where sex=‘女’;
#错位处理程序的说明
declare continue handler for not found set v_curs=1;
#打开游标，会将查询记录集放进内存中
open mycursor;
#开始循环
repeat
#抓起一条记录存放在相应变量中
fetch mycursor into v_id,v_user_name,v_password,v_sex,v_birthday;
if v_curs<>1 then
#显示抓取的记录
select v_id,v_user_name,v_password,v_sex,v_birthday;
INSERT INTO user_nv VALUES(v_id,v_user_name,v_password,v_sex,v_birthday);
end if;
until v_curs=1
end repeat;
#结束循环
close mycursor;
end
;;
delimiter ;

– 调用过程
CALL usernvdata();
SELECT * FROM user_nv;

---

2.9. 触发器

(1) 数据库语句
数据库语句***
– 对ware表的数量进行insert、update和delete事件触发。
drop table if exists ware_operation;
create table ware_operation(
id int primary key auto_increment not null,
ware_id int not null,
goods_id int not null,
quantity int not null,
user_id int not null,
operation char(10) not null,
operation_time timestamp not null default current_timestamp
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

– --insert 事件触发
CREATE TRIGGER trig_ware_insert
AFTER insert ON ware FOR EACH ROW
INSERT INTO ware_operation(ware_id,goods_id,quantity,user_id,operation)
VALUES(NEW.id,NEW.goods_id,NEW.quantity,NEW.user_id,“INSERT”);

– --update 事件触发
CREATE TRIGGER trig_ware_update
AFTER update ON ware FOR EACH ROW
INSERT INTO ware_operation(ware_id,goods_id,quantity,user_id,operation)
VALUES(OLD.id,NEW.goods_id,NEW.quantity,NEW.user_id,“UPDATE”);

– --delete 事件触发
CREATE TRIGGER trig_ware_delete
AFTER delete ON ware FOR EACH ROW
INSERT INTO ware_operation(ware_id,goods_id,quantity,user_id,operation)
VALUES(OLD.id,OLD.goods_id,OLD.quantity,OLD.user_id,“DELETE”);

---