SQL面试题

本文中的环境都是基于Navicat。

分析流程 ：建立连接 mysql -u用户名 -p密码
选择数据库
分析数据构成（分析有哪些表 那些字段 都是什么类型的 有什么约束条件 是否设立索引）
数据插入
数据查询

数据插入

Student(SId,Sname,Sage,Ssex) 
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 
 
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); 
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'), 
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-12-20' , '男'),
('04' , '李云' , '1990-12-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-01-01' , '女'),
('07' , '郑竹' , '1989-01-01' , '女'),
('09' , '张三' , '2017-12-20' , '女'),
('10' , '李四' , '2017-12-25' , '女'),
('11' , '李四' , '2012-06-06' , '女'), 
('12' , '赵六' , '2013-06-13' , '女'),
('13' , '孙七' , '2014-06-01' , '女'); 
 
Course(CId,Cname,TId) 
--CId 课程编号,Cname 课程名称,TId 教师编号 
 
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'); 
 
 
 
Teacher(TId,Tname) 
--TId 教师编号,Tname 教师姓名 
 
create table Teacher(TId varchar(10),Tname varchar(10)); 
insert into Teacher values('01' , '张三'), 
('02' , '李四'),
('03' , '王五'); 
 
 
SC(SId,CId,score) 
--SId 学生编号,CId 课程编号,score 分数 
 
create table SC(SId varchar(10),CId varchar(10),score float); 
insert into SC values('01' , '01' , 80), 
('01' , '02' , 90),
('01' , '03' , 99), 
('02' , '01' , 70), 
('02' , '02' , 60), 
('02' , '03' , 80), 
('03' , '01' , 80), 
('03' , '02' , 80), 
('03' , '03' , 80), 
('04' , '01' , 50), 
('04' , '02' , 30), 
('04' , '03' , 20), 
('05' , '01' , 76), 
('05' , '02' , 87), 
('06' , '01' , 31), 
('06' , '03' , 34), 
('07' , '02' , 89), 
('07' , '03' , 98);

题目详解

1、	查询生日都是1号出生的学生
SELECT * from student WHERE day(sage)=1;
#模糊查询regexp
SELECT * from student WHERE  sage REGEXP '\\-01';

2、	查询每门课的选修学生数,分别是那些学生？ --不要偷懒写select * ，会报错 Duplicate column name 'SId'
SELECT cid,
cname,
count(sname),
GROUP_CONCAT(sname)
FROM
(SELECT
sc.cid,
c.cname,
sc.sid,
st.sname
FROM
	SC
	LEFT JOIN student st ON sc.sid = st.sid
LEFT JOIN Course c on sc.cid= c.cid)a GROUP BY cid;

3、	英语的总成绩是多少 --子查询
SELECT
	cid,
	sum( score ) 
FROM
	sc 
WHERE
	cid = ( SELECT cid FROM course WHERE cname = '英语' );
4、	班里有哪几个学生跟老师同名 (找出老师名 看学生中重名的)
SELECT
	sname,
	count( sname ) 
FROM
	student 
WHERE
	sname IN ( SELECT concat( tname ) FROM Teacher ) 
GROUP BY
	sname;
5、	教师王五是什么科目的老师，教了多少学生（王五老师tid-》cid-》sc中的学生数） 
SELECT
	sc.cid,
	a.cname,
	count( sid ) 
FROM
	sc
	LEFT JOIN ( SELECT cid, cname FROM course c LEFT JOIN teacher t ON c.tid = t.tid WHERE tname = '王五' ) a ON sc.cid = a.cid 
WHERE
	sc.cid = a.cid;
6、	查询各科成绩最高分和最低分（连接-》分组）
SELECT
	sc.cid,
	c.cname,
	max( Score ),
	min( score ) 
FROM
	sc
	LEFT JOIN course c ON sc.cid = c.cid 
GROUP BY
	cid;
7、	查询男女生各有多少人？
SELECT ssex,count(sid) from student GROUP BY ssex;
8、	查询成绩大于60分学生的学号和平均成绩（找出>60对应学号，筛选，分组）
SELECT
	sid,
	avg( score ) 
FROM
	sc 
WHERE
	sid = ANY ( SELECT DISTINCT sid FROM sc WHERE score > 60 ) 
GROUP BY
	sid;
9、	查询至少选修两门课程的学生学号 (分组-》having)
SELECT
	sid,
	count( cid ) 
FROM
	sc 
GROUP BY
	sid 
HAVING
	count( cid ) >= 2;
10、	查询同名同姓学生名单并统计同名人数（分组-》筛选）
SELECT
	sname,
	count( sname ) 
FROM
	student 
GROUP BY
	1 
HAVING
	count( sname ) > 1;
11、	查询“90后”的学生有多少个？分别谁？（生日含199X，计数，合并）
SELECT sid,sname,sage from student WHERE sage REGEXP'199';
12、	查询所有学生的学号、姓名、选课、总成绩
SELECT
	sc.sid,
	s.sname,
	GROUP_CONCAT( cid ),
	sum( sc.score ) 
FROM
	sc
	LEFT JOIN student s ON sc.sid = s.sid 
GROUP BY
	sc.sid;
13、	查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT
	sc.sid,
	s.sname,
	avg( score ) 
FROM
	sc
	LEFT JOIN student s ON sc.sid = s.sid 
GROUP BY
	sc.sid 
HAVING
	avg( score ) > 85;
14、	查询学生的选课情况：学号、姓名、课程号、课程名称（连接-分组-合并）
SELECT
sc.sid,
s.sname,
	GROUP_CONCAT(sc.cid,":",c.cname)
FROM
	sc
	LEFT JOIN student s ON sc.sid = s.sid 
	LEFT JOIN course c on sc.cid = c.cid
GROUP BY
	sc.sid ;

15.查询“01”课程比“02”课程成绩高的所有学生的学号及对应成绩

SELECT
	a.sid,
	a.score_1,
	b.score_2 
FROM
	( SELECT sid, score AS score_1 FROM sc WHERE cid = 1 ) a
	LEFT JOIN ( SELECT sid, score AS score_2 FROM sc WHERE cid = 2 ) b ON a.sid = b.sid 
WHERE
	a.sid = b.sid 
	AND a.score_1 > b.score_2;

16、查询所有课程成绩小于60的同学的学号、姓名：

逻辑：找出成绩>60,学号不在这之内的就是存在<60的学生
SELECT
	sid,
	sname 
FROM
	student 
WHERE
	sid NOT IN ( SELECT DISTINCT sc.sid FROM sc, student WHERE sc.sid = student.sid AND score > 60 );
	逻辑二
	select sid,sname
from student s
where not EXISTS (
select s.sid from sc 
where sc.sid = s.sid and sc.score>60);

17、查询至少有一门课与学号为“1001”同学所学相同的同学的学号和姓名：

在这里插入代码片

18、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
知识点脑图

总结
1、接到需求的时候，一定先去分析，要什么，怎么做，与什么要注意的。
2、实现的方法很多，可以多去实践，是否可行。
3、注意SQL语句的优化，万万不可有偷懒思想。