mysql作业，有偿

Question

 

Answer 1

-- 3~7题
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- 查看表结构
DESC student

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1, '软件一班');
INSERT INTO `grade` VALUES (2, '软件二班');
INSERT INTO `grade` VALUES (3, '软件三班');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `gid` int(11) NOT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '王红', 1);
INSERT INTO `student` VALUES (2, '李强', 1);
INSERT INTO `student` VALUES (3, '赵四', 2);
INSERT INTO `student` VALUES (4, '郝娟', 2);
INSERT INTO `student` VALUES (5, '段然', 3);
INSERT INTO `student` VALUES (6, '李娟', 3);

-- 8
SHOW TABLES
-- 9
SELECT id FROM grade WHERE grade.name='软件一班'

-- 10
SELECT * FROM student WHERE gid=1

-- 11
DELETE FROM student WHERE sname='王红'

-- 12
UPDATE student SET gid=2 WHERE sname ='李强'

-- 13
SELECT sid,sname FROM student WHERE gid=(SELECT id FROM grade WHERE grade.name='软件二班')

-- 14
CREATE VIEW v_student as SELECT * FROM student

有偿就算了，多学点以后挣的钱都是自己的，这是最基础的知识了