一、学生表记录如下(学号 姓名 性别 年龄)

0001 xw 男  18

0002 mc 女  16

0003 ww 男  21

0004 xw 男  18

请写出实现如下功能的SQL语句

删除除了学号(自动编号)字段以外,其他字段都相同的冗(rong)余记录

 

#  创建数据库

create database day0214 charset utf8;

 

#  切进day0214

use day0214;

# 创建student表

create table student(
       id int primary key auto_increment comment "主键自增长",
       serial int not null comment "编号,序号",
       name varchar(30) not null comment "名字,非空",
       sex char(10) comment "性别",
       age int comment "年龄"
);

# 为student表插入数据

insert into student (serial,name,sex,age) values (0001, "xw", "男", 18),
(0002, "mc", "女", 16),
(0003, "ww", "男", 21),
(0004, "xw", "男", 18);

#  删除掉重复的数据, 子查询:

delete from student where id not in (select a.id from (select * from student group by name, sex, age) a);