数据准备:install.bat
@ECHO OFF
::未配置环境变量下
cd C:\Program Files\MySQL\MySQL Server 5.7\bin\
SET dbhost=127.0.0.1
SET dbuser=root
SET dbpasswd=123456
SET dbName=onlinedb
::设置sql文件路径
set sqlfile=C:\Users\Administrator\Desktop\drugs.sql
::执行SQL脚本
mysql -h%dbhost% -u%dbuser% -p%dbpasswd% < %sqlfile% --default-character-set=utf8
ECHO 完成!
PAUSE
@ECHO Done!
数据准备:drugs.sql
/*
Navicat MySQL Data Transfer
Source Server : test
Source Server Type : MySQL
Source Server Version : 80026
Source Host : localhost:3306
Source Schema : onlinedb
Target Server Type : MySQL
Target Server Version : 80026
File Encoding : 65001
Date: 29/10/2021 15:05:26
*/
USE ONLINEDB;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for drugs
-- ----------------------------
DROP TABLE IF EXISTS `drugs`;
CREATE TABLE `drugs` (
`drid` int NOT NULL,
`gtid` int NOT NULL,
`drname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`drspec` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`drunit` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`drcount` int NULL DEFAULT NULL,
`drprice` decimal(8, 2) NULL DEFAULT NULL,
PRIMARY KEY (`drid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of drugs
-- ----------------------------
INSERT INTO `drugs` VALUES (11, 1, '复方甘草口服溶液', '100ml', '支', 65, 2.50);
INSERT INTO `drugs` VALUES (125, 5, '泻立停小儿', '13s', '卷', 22, 2.50);
INSERT INTO `drugs` VALUES (1031, 5, '*头孢羟氨苄颗粒(葵花)', '10袋', '盒', 45, 10.00);
INSERT INTO `drugs` VALUES (1042, 1, '硝苯地平缓释片(扬子江)', '10ml*16s', '合', 83, 6.00);
INSERT INTO `drugs` VALUES (10010, 2, '维生素E胶丸', '100mg*30s', '盒', 12, 85.00);
INSERT INTO `drugs` VALUES (10011, 1, '*阿莫西林胶囊(南国药业)A3', '0.25*24s', '瓶', 32, 10.00);
INSERT INTO `drugs` VALUES (10012, 1, '阿奇霉素胶囊A3(四川)', '6S*1板', '合', 45, 8.00);
INSERT INTO `drugs` VALUES (10013, 4, '罗红霉素胶囊(山东天顺)', '150MG*12S', '合', 45, 12.00);
INSERT INTO `drugs` VALUES (10015, 4, '硝苯地平缓释片(扬子江)', '10ml*16s', '合', 82, 6.00);
INSERT INTO `drugs` VALUES (10016, 1, '仁和可立克', '160ml', '瓶', 54, 12.00);
INSERT INTO `drugs` VALUES (10017, 4, '手套', NULL, '瓶', 45, 1.00);
INSERT INTO `drugs` VALUES (10018, 1, '仁和可立克', '10s', '盒', 75, 7.00);
INSERT INTO `drugs` VALUES (10019, 4, '硫软膏', '1', '盒', 2, 1.50);
INSERT INTO `drugs` VALUES (10020, 1, '*风湿安泰片A2', NULL, '支', 23, 10.00);
INSERT INTO `drugs` VALUES (10021, 4, '*小儿氨酚黄那敏颗粒(小葵花)', 'l0.75G', '盒', 45, 10.00);
INSERT INTO `drugs` VALUES (10022, 1, '*宝宝肤专家软膏', '15mg', '支', 120, 20.00);
INSERT INTO `drugs` VALUES (10023, 1, '二甲双肌缓释片(悦康)', NULL, '盒', 12, 8.00);
INSERT INTO `drugs` VALUES (10024, 1, '小胶布(华联)', '1s', '瓶', 56, 1.00);
INSERT INTO `drugs` VALUES (10026, 5, '*宝宝肤专家软膏', '6s', '盒', 45, 16.00);
INSERT INTO `drugs` VALUES (10027, 5, '阿奇霉素分散片', '6s', '盒', 21, 11.00);
INSERT INTO `drugs` VALUES (10028, 5, '奥美拉唑胶囊(海口奇力)', '20MG*14S', '瓶', 13, 8.00);
INSERT INTO `drugs` VALUES (10029, 5, '珍菊降压片', '60S', '瓶', 18, 9.50);
INSERT INTO `drugs` VALUES (10030, 5, '诺氟沙星胶囊(山东鲁抗)A3', '2os', '瓶', 22, 3.00);
INSERT INTO `drugs` VALUES (10032, 1, '仁和可立克', '12s3板', '盒', 78, 16.00);
INSERT INTO `drugs` VALUES (10033, 5, '阿莫西林颗粒(葵花)', '0.125G*10代', '盒', 64, 10.00);
INSERT INTO `drugs` VALUES (10034, 1, '脑心舒口服液(步长)', '10ml*10支', '盒', 79, 12.00);
INSERT INTO `drugs` VALUES (10035, 1, '*布洛芬缓释胶囊(珠海)A2', '28s', '粒', 64, 1.00);
INSERT INTO `drugs` VALUES (10036, 1, '*宁心宝胶囊', '32s', '盒', 31, 24.00);
INSERT INTO `drugs` VALUES (10037, 2, '维生素E胶丸', '100mg*30s', '盒', 45, 85.00);
INSERT INTO `drugs` VALUES (10038, 5, '*阿莫西林胶囊(南国药业)A3', '0.25*24s', '瓶', 65, 10.00);
INSERT INTO `drugs` VALUES (10039, 1, '阿奇霉素胶囊A3(四川)', '6S*1板', '合', 23, 8.00);
INSERT INTO `drugs` VALUES (10040, 1, '罗红霉素胶囊(山东天顺)', '150MG*12S', '合', 78, 12.00);
INSERT INTO `drugs` VALUES (10041, 2, '复方甘草口服溶液', '100ml', '支', 94, 2.50);
INSERT INTO `drugs` VALUES (10043, 1, '仁和可立克', '160ml', '瓶', 82, 12.00);
INSERT INTO `drugs` VALUES (10044, 2, '手套', NULL, '瓶', 42, 1.00);
INSERT INTO `drugs` VALUES (10045, 1, '仁和可立克', '10s', NULL, 42, 7.00);
INSERT INTO `drugs` VALUES (10046, 3, '硫软膏', '1', '盒', 43, 1.50);
INSERT INTO `drugs` VALUES (10047, 1, '*风湿安泰片A2', NULL, '支', 145, 10.00);
INSERT INTO `drugs` VALUES (10048, 1, '*小儿氨酚黄那敏颗粒(小葵花)', 'l0.75G', '盒', 45, 10.50);
INSERT INTO `drugs` VALUES (10049, 1, '*宝宝肤专家软膏', '15mg', '支', 72, 20.00);
INSERT INTO `drugs` VALUES (10050, 2, '二甲双肌缓释片(悦康)', NULL, '盒', 92, 8.00);
INSERT INTO `drugs` VALUES (10051, 3, '小胶布(华联)', '1s', '瓶', 82, 1.00);
INSERT INTO `drugs` VALUES (10052, 2, '泻立停小儿', '13s', '卷', 55, 2.50);
INSERT INTO `drugs` VALUES (10053, 3, '仁和可立克', '6s', '盒', 21, 16.00);
INSERT INTO `drugs` VALUES (10054, 2, '阿奇霉素分散片', '6s', '盒', 32, 9.80);
INSERT INTO `drugs` VALUES (10055, 1, '奥美拉唑胶囊(海口奇力)', '20MG*14S', '瓶', 12, 8.60);
INSERT INTO `drugs` VALUES (10056, 1, '珍菊降压片', '60S', '瓶', 8, 8.20);
INSERT INTO `drugs` VALUES (10057, 1, '诺氟沙星胶囊(山东鲁抗)A3', '2os', '瓶', 55, 3.00);
INSERT INTO `drugs` VALUES (10058, 1, '*头孢羟氨苄颗粒(葵花)', '10袋', '盒', 44, 10.00);
INSERT INTO `drugs` VALUES (10059, 1, '*前列舒乐胶囊', '12s3板', '盒', 61, 16.00);
INSERT INTO `drugs` VALUES (10060, 4, '阿莫西林颗粒(葵花)', '0.125G*10代', '盒', 20, 10.00);
INSERT INTO `drugs` VALUES (10061, 4, '脑心舒口服液(步长)', '10ml*10支', '盒', 71, 12.00);
INSERT INTO `drugs` VALUES (10062, 1, '*布洛芬缓释胶囊(珠海)A2', '28s', '粒', 52, 1.00);
INSERT INTO `drugs` VALUES (10063, 1, '*宁心宝胶囊', '32s', '盒', 62, 24.00);
INSERT INTO `drugs` VALUES (10064, 1, '云南白药胶囊(A1)', 'l16s', '合', 455, 19.00);
INSERT INTO `drugs` VALUES (10065, 2, '*益母草颗粒(A1)(哈尔滨)', '10包', '合', 20, 8.00);
INSERT INTO `drugs` VALUES (10066, 2, '*五加生化胶囊(A1)(多多)', '24S', '合', 12, 30.00);
INSERT INTO `drugs` VALUES (10067, 1, '*鸟鸡口服液(A1)(半边天)', '10ML*12支', '盒', 77, 38.00);
INSERT INTO `drugs` VALUES (10068, 1, '脑心舒口服液(步长)', '0.4*12S*3', '盒', 78, 30.00);
INSERT INTO `drugs` VALUES (10069, 1, '*宁心宝胶囊', '0.52*24', '盒', 54, 25.00);
INSERT INTO `drugs` VALUES (10070, 1, '脑心舒口服液(步长)', '0.3G*36S', '盒', 64, 25.00);
INSERT INTO `drugs` VALUES (10071, 1, '*鸟鸡白凤丸(半边天)(A1)', '6g*8袋', '盒', 61, 18.00);
INSERT INTO `drugs` VALUES (10072, 3, '*妇科调经片(吉林万通)(A1)', '48S', '盒', 62, 12.00);
INSERT INTO `drugs` VALUES (10073, 3, '*血府逐瘀颗粒(贵州联盛)(A1)', '.5G*10袋', '盒', 82, 15.00);
INSERT INTO `drugs` VALUES (10074, 1, '*乳宁片(长春银若克)(A1)', '18s*3小盒', '盒', 102, 0.00);
INSERT INTO `drugs` VALUES (10075, 1, '三维制霉素栓(一半天)(A1)', '7s', '盒', 122, 15.00);
INSERT INTO `drugs` VALUES (10076, 3, '酮康唑发用洗剂(康王)A1', '5ml', '盒', 142, 2.50);
INSERT INTO `drugs` VALUES (10077, 3, '早早孕试条(润和)', 'ls', '袋', 162, 2.00);
INSERT INTO `drugs` VALUES (10078, 1, '*消糜栓(通化万通)(A1)', '5s', '袋', 182, 28.00);
INSERT INTO `drugs` VALUES (10079, 1, '双唑泰栓(安徽天洋)(A1)', '7s', '盒', 202, 5.00);
INSERT INTO `drugs` VALUES (10080, 1, '*双唑泰栓(通化万通)(A1)', '7s', '盒', 222, 18.00);
INSERT INTO `drugs` VALUES (10081, 1, '毓婷(A1)', '2s', '盒', 242, 6.50);
INSERT INTO `drugs` VALUES (10082, 1, '云南白药粉(A1)', '4g*1瓶', '盒', 45, 11.50);
INSERT INTO `drugs` VALUES (10083, 3, '*柏栀祛湿洗液(A1)', '90ml', '瓶', 21, 25.00);
INSERT INTO `drugs` VALUES (10084, 3, '*苦参洗液(山东王氏)(A1>', '260ml', '瓶', 21, 15.00);
INSERT INTO `drugs` VALUES (10085, 3, '妇炎洁洗液(仁和)(A1)', '180ml', '瓶', 2, 6.00);
INSERT INTO `drugs` VALUES (10086, 1, '妇炎洁洗液(仁和)(A1)', '380ml', '瓶', 0, 10.00);
INSERT INTO `drugs` VALUES (10087, 1, '桑菊感冒片(万通)', '24s', '盒', 12, 9.00);
INSERT INTO `drugs` VALUES (10088, 1, '*感冒灵胶囊(江西半边天)(A2)', '24s', '袋', 20, 12.00);
INSERT INTO `drugs` VALUES (10089, 1, '*感冒清片(通化万通)(A2)', '3os', '袋', 10, 8.00);
INSERT INTO `drugs` VALUES (10090, 3, '*化痰平喘片(通化万通)(A2)', '30s', '盒', 52, 10.00);
INSERT INTO `drugs` VALUES (10091, 1, '咳特灵胶囊(通化万通)(A2)', '36s', '盒', 12, 8.00);
INSERT INTO `drugs` VALUES (10092, 3, '*消炎止咳片(山西同达)(A2)', '24s', '盒', 10, 10.00);
INSERT INTO `drugs` VALUES (10093, 1, '*清热解毒口服液(湖北同德堂)(A', 'O支', '盒', 9, 10.00);
INSERT INTO `drugs` VALUES (10094, 1, '*口腔溃疡散(吉林万通)(A2)', '3g*1瓶', '盒', 5, 7.00);
INSERT INTO `drugs` VALUES (10095, 2, '胖大海含片(安徽珠峰)(A2)', NULL, '盒', 45, 0.80);
SET FOREIGN_KEY_CHECKS = 1;
练习题:
一、复习库和表的基本操作:
1、创建数据库“onlinedb”(使用show命令查看结果并截图)
2、在onlinedb数据库中创建数据“商品类型”表Goodstype,结构如下:
序号 | 字段名 | 数据类型 | 主键标识 | 允许为空 | 默认值 | 说明 |
1 | gtid | int | 是 | 否 | 类型ID | |
2 | gtname | varchar(50) | 否 | 类型名称 | ||
4 | gtmarks | Varchar(500) | 是 | 类型说明 | ||
3 | edittime | datetime | 否 | now() | 修改时间 |
使用describe 命令查看结果并截图。
3、将下表数据插入到“商品类型”表Goodstype中。
gtid | gtname | gtmarks | edittime |
1 | 处方药 | 须出具医嘱 | null |
2 | 营养品 | 可以刷卡 | (系统默认时间) |
使用select * from Goodstype查看数据结果并截图。
4、修改“商品类型”表中gtid为1的记录,将其edittime修改为“2021-10-29 10:30:22”;修改“商品类型”表中,gtid为2的记录,将其gtmarks修改为“不可刷医保卡”。修改完成后,使用select * from Goodstype查看数据结果并截图。
二、单表查询语句的灵活使用:
执行随作业一起下发的install.bat文件,补充数据库表及相关数据后执行以下查询语句。
1、查询drugs表中所有规格型号(drspec)为空的药品编号(drid)、药品名称(drname)、库存数量(drcount)、计量单位(drunit)。
2、查询drugs表中所有库存(drcount)数量小于10的药品名称(drname)、库存数量(drcount),并按库存数量从高到低排序。
3、查询drugs表中所有单价(drprice)在10元到20元之间的药品编号(drid)、药品名称(drname)、单价(drprice),并按单价从低到高排序。
4、查询drugs表中所有药品名称(drname)以“*”开头的药品名称(drname)、规格型号(drspec)。
5、查询drugs表中所有药品名称(drname)包含“霉素”的药品编号、名称、规格型号、库存数量、计量单位、单价,并按单价从高到低排序。
6、查询drugs表中所有药品名称(drname)、库存数量(drcount),并按分类编号(gtid)顺序、药品单价(drprice)逆序排序。
7、查询drugs表中分类编号(gtid)为1 的所有药品名称、库存数量、单价、库存总价(库存drcount ✖单价drprice,请重命名列名称为totalprice),并按总价从高到低排序。
8、假如所有药品单价八折销售,请查询drugs表中分类编号(gtid)为2的所有药品名称(drname)、单价(drprice)、八折后的价格信息(drprice*0.8,请重命名列名称为drsaleprice),并按折后价格从低到高排序。
9、查询drugs表中,分类编号(gtid)为1,3,5的所有药品分类编号、药品名称,并按分类编号进行排序。
10、查询drugs表中,药品编号(drid)不符合规范(非5为数字)的药品编号、药品名称等信息。
11、按照药品类别编号(gtid)进行分类汇总,计算不同分类下各有多少种药品信息。
12、按照药品类别(gtid)进行分类汇总,查询不同分类下单价(drprice)最高的药品价格。
13、按照药品类别进行分类汇总,查询不同分类下所有药品的库存总量。
14、按照药品类别进行分类汇总,统计不同类别下所有药品的总价值。
15、查询drugs表中,药品名称(drname)存在重复的药品记录。
16、查询drugs表中,药品单价最低的前5种药品信息(编号、名称、单价)。
17、查询drugs表中,药品单价最高的第6到第10种药品信息(编号、名称、单价)。
18、查询drugs表中,药品单价小于20,且计量单位(drunit)为“盒”的药品信息,并按照单价(drprice)从低到高、库存(drcount)从低到高的顺序排列。
19、*查询drugs表中,所有单价相同的药品名称(drname)。