面试经常考的五个Sql查询

不管是面试什么，数据库都是必考的，今天给大家整理下几个重要的sql查询。所有的语句都是在此语句上扩展的。学会了基本可以无忧了。话不多说，直接上干货：

– 一、学生表记录如下(学号 姓名 性别 年龄)
– 0001 xw 男 18
– 0002 mc 女 16
– 0003 ww 男 21
– 0004 xw 男 18
– 请写出实现如下功能的SQL语句
– 删除除了学号(自动编号)字段以外,其他字段都相同的冗(rong)余记录

方便windows命令行,sql语句中文支持

set character_set_client=gbk;
set character_set_results=gbk;

create database day0509 charset utf8;
create table student(
  id int auto_increment primary key comment "主键自增",
  xuehao varchar(10) not null comment "学号",
  name  varchar(10) not null comment "姓名",
  gender varchar(10) not null comment "性别",
  age int not null comment "年龄"
);

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

按照性别，对数据分组统计

 select gender,count(*) as gender_count from student group by gender;

查询出数据不重复的记录

select * from student group by name,gender,age;

查询出数据不重复的记录, id最小的

select min(id) as min_id from student group by name,gender,age;

删除1，子查询删除(效率低)

delete from student where id not in ( select min_id from (select min(id) as min_id from student group by name,gender,age) a);

删除2，中间临时表，删除冗余记录, (处理大数据量的时候)

create table tmp (select min(id) as id from student group by name,gender,age);
delete from student where id not in (select id from tmp);
drop table tmp;

– 二、数据库有三个表 teacher表, student表, tea_stu关系表,
– teacher表 teaID name age ,
– student 表 stuID name age ,
– teacher_student表 teaID stuID

– 要求用一条SQL查询出这样的结果:
– 1.显示的字段要有老师 id age 每个老师所带的学生人数

– 2.只列出老师age为40 以下, 学生age为12以上的记录

<