一张存放学生学号、姓名、课程以及成绩的数据表,设计将同一学号的记录只保留一条
结果如下图所示
我的答案:
- 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`;