MySQL面试题 数据库MySQL经典面试题之SQL语句

1.根据emp数据表编写SQL查出所有name重复的记录且按照age降序。

+--------+--------+-----+

| emp_no | name | age |

+--------+--------+-----+

| 1001 | 小明 | 18 |

| 1002 | 王强 | 20 |

| 1003 | 小雪 | 20 |

| 1004 | 小明 | 19 |

+--------+--------+-----+

select emp_no, name,age 
from emp 
where name in (
    select t.name from
     (    select name,count(name) as num 
         from emp 
         group by name 
         having num > 1
     )t
) 
order by age desc;

（测试数据添加SQL见文章末尾）

1.学生表

Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

2.课程表

Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号

3.教师表

Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名

4.成绩表

SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数

1. 平均成绩及格了的学生

select avg(score) avg_score,SID 
from SC 
group by SID 
having avg_score >= 60;

2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.SID,a.Sname,b.avg_score from Student a join  
(
    select avg(score) avg_score,SID 
    from SC 
    group by SID 
    having avg_score >= 60
) b on a.SID = b.SID;

3. 查询学过"张三"老师授课的同学的信息

select distinct a.SId,a.Sname,a.Sage,a.Ssex
from Student a
join SC b on a.SID = b.SID
join Course c on c.CID = b.CID
join Teacher d on d.TID = c.TID
where d.Tname = '张三';

4. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

--方法1

select m.* 
from Student m 
where SID in
(
    select SID from
    (
        select distinct SID from SC where CID = '01'
        union all
        select distinct SID from SC where CID = '02'
    ) t 
    group by SID 
    having count(1) = 2
)
order by m.SID

--方法2

select Student.* 
from Student 
join SC on Student.SID = SC.SID
where CID = '02' 
and exists (select 1 from SC sc_2 where sc_2.SID = SC.SID and sc_2.C);

--方法3

select Student.* 
from Student 
join SC on Student.SID = SC.SID
where CID = '01' 
and exists (select 1 from SC sc_2 where sc_2.SID = SC.SID and sc_2.C);

5. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select Student.* from Student 
join SC on Student.SID = SC.SID where CID = '01' 
and not exists (select 1 from SC sc_2 where sc_2.SID = SC.SID and sc_2.C);

6. 查询两门及其以上不及格课程的同学的学号

select SID from SC where score < 60 group by SID having count(1) >= 2;

7. 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩

select Student.SID,Student.sname,avg(SC.score) as avg_score
from student join SC on student.SID = SC.SID
where Student.SID in(select SID from SC where score < 60 group by SID having count(1) >= 2) 
group by Student.SID,Student.sname;

查询没有学全所有课程的同学的信息

select Student.*
from Student left join SC
on Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)

附录》添加测试数据

1.学生表

create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

2.课程表

create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

3.教师表

create table Teacher(TID varchar(10),Tname nvarchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

4.成绩表

create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);