mysql 减少库存 mysql库存管理

学习目标：

MYSQL数据库的增删改查

---

学习内容：

1. 创建数据库
2. 选择数据库
3. 删除数据库
4. 创建数据表
5. 删除数据表
6. 插入数据
7. 更新数据
8. 查找数据

---

示例：

以超市管理系统为例，包含员工表，库存表，仓库表
数据库：db_supermarket
数据表：s_employee #员工表；s_stock #库存表；s_storehouse #仓库表

# 创建数据库
# CREATE DATABASE 数据库名;
CREATE DATABASE db_supermarket;

---

# 选择数据库
# USE 数据库名;
USE db_supermarket;

---

# 删除数据库
# DROP DATABASE 数据库名； 
DROP DATABASE db_market；

---

# 创建数据表
# CREATE TABLE table_name (column_name column_type); 

#创建员工表
CREATE TABLE `s_employee`(
   `employee_id` INT UNSIGNED AUTO_INCREMENT,
   `employee_name` VARCHAR(40) NOT NULL,
   `employee_sex` VARCHAR(40) NOT NULL,
   `employee_age` VARCHAR(100) NOT NULL,
   `employee_title` VARCHAR(40) NOT NULL,
   `entry_date` DATE,
   PRIMARY KEY ( `employee_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

#创建库存表
CREATE TABLE `s_stock`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `goods_id` VARCHAR(100) NOT NULL,
   `goods_name` VARCHAR(40) NOT NULL,
   `goods_price` DECIMAL(8,4),
   `store_goods_num` VARCHAR(40) NOT NULL,
   `storehouse_id` VARCHAR(100) NOT NULL,
   `storage_time` DATE,
   `delivery_time` DATE,
   `delivery_num` VARCHAR(40),
   PRIMARY KEY ( `id`,`goods_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建仓库表
CREATE TABLE `s_storehouse`(
   `storehouse_id` INT UNSIGNED AUTO_INCREMENT,
   `storehouse_name` VARCHAR(40) NOT NULL,
   PRIMARY KEY ( `storehouse_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

# 删除数据表
# DROP TABLE table_name ;
DROP TABLE s_employee;

---

# 插入行数据
# INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

# 在员工表插入数据
INSERT INTO `s_employee` VALUES (629001, '劳拉', '女', 28, '登记员','2022-06-29');
INSERT INTO `s_employee` VALUES (629002, '杰克', '男', 35, '仓管','2021-05-23');
INSERT INTO `s_employee` VALUES (629003, '露娜', '女', 30, '财务','2021-08-17');
INSERT INTO `s_employee` VALUES (629004, '马丁', '男', 42, '货运','2019-04-07');
INSERT INTO `s_employee` VALUES (629005, '保罗', '男', 37, '搬运','2022-03-18');

# 在库存表插入数据
INSERT INTO `s_stock` VALUES (1,11000, '海飞丝洗发水', 65.00, 87, 22000,'2022-04-07','2022-04-27',20);
INSERT INTO `s_stock` VALUES (2, 11000,'海飞丝洗发水', 65.00, 52, 22001,'2022-04-07','2022-04-19',10);
INSERT INTO `s_stock` VALUES (3, 11001,'佳洁士牙膏', 18.00, 20, 22003,'2021-12-12','2022-01-17',20);
INSERT INTO `s_stock` VALUES (4, 11002,'潘婷护发素', 29.00, 103, 22003,'2020-11-07','2020-11-11',88);
INSERT INTO `s_stock` VALUES (5, 11003,'蓝月亮洗衣液', 49.00, 64, 22002,'2021-05-01','2021-06-18',50);
INSERT INTO `s_stock` VALUES (6, 11004,'洁柔抽纸', 69.00, 200, 22004,'2022-01-01','2022-04-03',180);
INSERT INTO `s_stock` VALUES (7, 11005,'洗面奶', 38.00, 370, 22005,'2022-02-16','2022-05-27',174);
INSERT INTO `s_stock` VALUES (8, 11006,'护手霜', 25, 123, 22002,'2021-10-01','2021-11-11',108);

# 在仓库表插入数据
INSERT INTO `s_storehouse` VALUES (22000, '华中仓库1号');
INSERT INTO `s_storehouse` VALUES (22001, '华东仓库2号');
INSERT INTO `s_storehouse` VALUES (22002, '华南仓库3号');
INSERT INTO `s_storehouse` VALUES (22003, '华西仓库4号');
INSERT INTO `s_storehouse` VALUES (22004, '华北仓库5号');

插入数据之后生成表如下：

# 插入一列数据
# alter table TABLE_NAME add column NEW_COLUMN_NAME 限制条件;
# 在员工表员工id后面再添加一列任职仓库id
alter table `s_employee` add column storehouse_id VARCHAR(100) NOT NULL
after employee_id;

添加一列之后的员工表如下：

补全数据后：

---

# 更新数据
# UPDATE table_name SET field1=new-value1, field2=new-value2 
# [WHERE Clause]
# 如将洁柔抽纸的价格修改为9折后的价格
UPDATE `s_stock` SET goods_price=ROUND(goods_price*0.9,4) WHERE goods_name='洁柔抽纸'
# 把潘婷护发素的出库数量修改为3
UPDATE `s_stock` SET delivery_num=3 WHERE goods_name='潘婷护发素'

修改价格后洁柔抽纸的价格如下：

修改潘婷护发素的出库数量后：

---

# 查找数据
# SELECT column_name,column_name
# FROM table_name
# [WHERE Clause]
# [LIMIT N][ OFFSET M]

# 熟练count,sum, avg, group by, order by, having, desc等函数用法
# 理解连接查询left join, right join

test1.显示各商品的出库数量，按出库数量由多到少排列

#order by 排序
SELECT s.goods_name,SUM(s.delivery_num) AS num 
FROM s_stock AS s 
GROUP BY s.goods_id 
ORDER BY num DESC

test2.查找有存货的商品分别都在多少个仓库中有货

#count 统计条目
SELECT s.goods_name,COUNT(s.storehouse_id) AS num 
FROM s_stock AS s 
WHERE s.delivery_num < s.store_goods_num
GROUP BY s.goods_id

test3.查找每个仓库中价格低于20元的商品信息

#having 子句伴随着group by使用，相当于group by分组查询之后，再进行一轮条件筛选
SELECT s.* 
FROM s_stock AS s 
GROUP BY s.storehouse_id
HAVING s.goods_price < 20

test4.查找每个仓库年龄大于30岁的员工信息

#left jion,right join连接操作
SELECT e.employee_id,e.employee_name,s.storehouse_name,e.employee_age
FROM s_employee AS e 
LEFT JOIN s_storehouse AS s ON e.storehouse_id = s.storehouse_id
WHERE e.employee_age > 30
GROUP BY s.storehouse_id

---

以上是学习MySQL过程中的一些学习心得与练习，后面继续补充