简单数据库设计

创建5张表：

CREATE TABLE `activity` (
  `a_id` int NOT NULL AUTO_INCREMENT COMMENT '活动序号',
  `m_integral` bigint DEFAULT NULL COMMENT '活动积分（1元积一分，满1000积分可用,抵10元，)',
  `a_gift` varchar(255) DEFAULT NULL COMMENT '活动中可以兑换的奖品',
  PRIMARY KEY (`a_id`),
  KEY `m_integral` (`m_integral`),
  KEY `a_gift` (`a_gift`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `member` (
  `m_id` int NOT NULL AUTO_INCREMENT,
  `m_name` varchar(255) NOT NULL,
  `m_integral` bigint DEFAULT NULL COMMENT '活动积分（1元积一分，满1000积分可用,抵10元，)',
  PRIMARY KEY (`m_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `mobliephone` (
  `p_id` bigint NOT NULL AUTO_INCREMENT COMMENT '序号',
  `p_brand` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '手机品牌',
  `p_5G` int NOT NULL COMMENT '是5G ''0'';不是5G ''1''',
  `p_start` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '明星联名',
  `p_color` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '颜色',
  `p_cid` int NOT NULL COMMENT '手机品牌的序号\r\n',
  `pay_account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'wangjunkai' COMMENT '管理员操作账号',
  `pay_password` bigint NOT NULL DEFAULT '123456' COMMENT '管理员操作密码',
  `p_stock` int NOT NULL DEFAULT '1' COMMENT '在架子上是1，下架是0',
  PRIMARY KEY (`p_id`,`p_brand`) USING BTREE,
  KEY `p_brand` (`p_brand`),
  KEY `p_cid` (`p_cid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `payment` (
  `s_id` int NOT NULL COMMENT '手机品牌的序号\r\n',
  `pay_account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'wangjunkai' COMMENT '付款账号',
  `pay_password` bigint NOT NULL DEFAULT '123456' COMMENT '付款密码',
  PRIMARY KEY (`pay_account`) USING BTREE,
  UNIQUE KEY `p_cid` (`pay_password`) USING BTREE,
  UNIQUE KEY `a_gift` (`pay_account`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `shoppingcart` (
  `s_id` int NOT NULL AUTO_INCREMENT COMMENT '购物车序号',
  `s_quantity` int DEFAULT NULL,
  `s_money` bigint NOT NULL COMMENT '购物车里商品价钱',
  `p_cid` int NOT NULL COMMENT '手机品牌的序号\r\n',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

输入信息：
INSERT INTO `activity` VALUES (1,3000,'蓝牙耳机'),(2,5000,'充电宝'),(3,10000,'旅行箱'),(4,60000,'王俊凯亲手签名');

INSERT INTO `member` VALUES (1,'wangjunkai',40000),(2,'baijingting',20000),(3,'laoyatang',4000),(4,'bujiatang',2000),(5,'wenwenerya',100);

INSERT INTO `mobliephone` VALUES (1,'iphone13',1,NULL,'马卡紫',1,'wangjunkai',123456,1),(2,'小米key',1,NULL,'星空蓝',2,'wangjunkai',123456,1),(3,'三星18',1,NULL,'梦幻绿',3,'wangjunkai',123456,0),(4,'华为sky',1,NULL,'星耀黄',4,'wangjunkai',123456,1),(5,'oppo88',0,NULL,'魅彩白',5,'wangjunkai',123456,1),(6,'iphoneDOX',1,NULL,'域空兰',6,'wangjunkai',123456,1);

INSERT INTO `payment` VALUES (5,'baijingting',931015),(2,'laoyatang',980122),(3,'wangjunkai',990921);

INSERT INTO `shoppingcart` VALUES (1,2,6299,1),(2,4,7899,3),(3,8,4199,2),(4,2,3459,5),(5,16,6709,4);

增删改查：

-- 1.用管理员账户查看已经下架的手机信息（包含手机品牌序号，品牌，颜色，5G以及价格）
SELECT m.p_cid,m.p_brand,m.p_color,m.p_5G,s.s_money FROM shoppingcart s JOIN mobliephone m ON pay_account='wangjunkai' && pay_password='123456'&& p_stock=0 && s.p_cid=m.p_cid;

-- 2.查看购物车里面的全部小米key的总价格  
SELECT (s_quantity * s_money)  AS '总价'FROM shoppingcart WHERE p_cid=2;

-- 3.预算只有7000，查看5G全部的手机(品牌，颜色)
SELECT m.p_brand,m.p_color FROM mobliephone m JOIN shoppingcart s WHERE s.s_money<7000 && m.p_cid=s.p_cid && m.p_5G=1;

-- 4.查看会员wangjunkai在现有的积分下可以兑换哪些商品
SELECT if(m.m_integral>a.m_integral,a.m_integral,NULL) AS'此积分下可以兑换的赠品',a.a_gift
FROM member m  JOIN activity a WHERE m.m_name ='wangjunkai';

-- 5.把会员baijingting分别兑换了1个旅行箱和充电宝，求剩余积分
SELECT m_integral-((SELECT m_integral FROM activity WHERE a_gift='旅行箱' )+(SELECT m_integral FROM activity WHERE a_gift='充电宝')) AS '剩余积分'FROM member WHERE m_name='baijingting';

-- 6.登陆laoyatang的支付宝账号下的支付界面（要求显示出购买的商品cid，数量）
SELECT s.p_cid,s.s_quantity FROM payment p JOIN shoppingcart s
WHERE p.pay_account='laoyatang' && p.pay_password='980122' && 
p.s_id = s.s_id;

-- 7.上架iphoneDOX(信息正常填写即可)
INSERT INTO mobliephone(p_brand,p_color,p_cid,p_5G
) VALUES('iphoneDOX','域空兰',6,1);

-- 8.修改购物车里的oppo88的数量类型；
ALTER TABLE shoppingcart MODIFY s_quantity INT(50);
-- 9.会员wenwenerya买了个iphone13，(原来积分并未兑换过)求它现在的积分是多少
SELECT m_integral+(SELECT s_money
FROM shoppingcart WHERE p_cid=1 ) FROM member WHERE m_name='wenwenerya';
-- 10.清空购物车
truncate TABLE shoppingcart;