【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`addcolumn 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.显示各商品的出库数量，按出库数量由多到少排列

#orderby 排序
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岁的员工信息

#leftjion,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过程中的一些学习心得与练习，后面继续补充
参考文档：https://www.runoob.com/mysql/mysql-tutorial.html