一张存放学生学号、姓名、课程以及成绩的数据表,设计将同一学号的记录只保留一条
结果如下图所示
我的答案:
CREATE TABLE IF NOT EXISTS `student` (
`stu_no` int unsigned NOT NULL DEFAULT 0,
`stu_name` varchar(20) not null DEFAULT '',
`scheme` varchar(20) not null DEFAULT '',
`grade` int unsigned not null DEFAULT 0
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO `student` VALUES (1001, '张三', '数学', 90);
INSERT INTO `student` VALUES (1002, '李四', '英语', 80);
INSERT INTO `student` VALUES (1003, '王五', '语文', 100);
INSERT INTO `student` VALUES (1001, '张三', '数学', 90);
INSERT INTO `student` VALUES (1002, '李四', '英语', 80);
INSERT INTO `student` VALUES (1003, '王五', '语文', 100);
INSERT INTO `student` VALUES (1001, '张三', '数学', 90);
INSERT INTO `student` VALUES (1002, '李四', '英语', 80);
INSERT INTO `student` VALUES (1003, '王五', '语文', 100);
CREATE TABLE `student_2` LIKE `student`;
ALTER TABLE `student_2` ADD CONSTRAINT uk_stu_no UNIQUE(`stu_no`);
INSERT IGNORE INTO `student_2` SELECT * FROM `student`;
DROP TABLE `student`;
ALTER TABLE `student_2` RENAME TO `student`;
或者
CREATE TABLE IF NOT EXISTS `student` (
`stu_no` int unsigned NOT NULL DEFAULT 0,
`stu_name` varchar(20) not null DEFAULT '',
`scheme` varchar(20) not null DEFAULT '',
`grade` int unsigned not null DEFAULT 0
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO `student` VALUES (1001, '张三', '数学', 90);
INSERT INTO `student` VALUES (1002, '李四', '英语', 80);
INSERT INTO `student` VALUES (1003, '王五', '语文', 100);
INSERT INTO `student` VALUES (1001, '张三', '数学', 90);
INSERT INTO `student` VALUES (1002, '李四', '英语', 80);
INSERT INTO `student` VALUES (1003, '王五', '语文', 100);
INSERT INTO `student` VALUES (1001, '张三', '数学', 90);
INSERT INTO `student` VALUES (1002, '李四', '英语', 80);
INSERT INTO `student` VALUES (1003, '王五', '语文', 100);
CREATE TABLE `student_2` LIKE `student`;
#ALTER TABLE `student_2` ADD CONSTRAINT uk_stu_no UNIQUE(`stu_no`);
INSERT IGNORE INTO `student_2` SELECT * FROM `student` GROUP BY `stu_no`;
DROP TABLE `student`;
ALTER TABLE `student_2` RENAME TO `student`;