几条面试的sql 和mysql

CREATE TABLE `course` (
  `cid` int(11) NOT NULL,
  `cname` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `teacher` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '自然科学', '3');
INSERT INTO `course` VALUES ('4', '人文科学', '4');

CREATE TABLE `student` (
  `sid` int(11) DEFAULT NULL,
  `sname` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `student` VALUES ('1', ' 张三', '2000-03-28');
INSERT INTO `student` VALUES ('2', '李四', '1999-12-02');
INSERT INTO `student` VALUES ('3', '王五', '2001-01-02');
INSERT INTO `student` VALUES ('4', '赵六', '2000-02-20');
INSERT INTO `student` VALUES ('5', '梅西', '1987-09-08');


CREATE TABLE `sc` (
  `scid` int(11) DEFAULT NULL,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `grade` decimal(3,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `sc` VALUES ('1', '1', '1', '90.0');
INSERT INTO `sc` VALUES ('2', '1', '2', '30.0');
INSERT INTO `sc` VALUES ('3', '1', '3', '60.0');
INSERT INTO `sc` VALUES ('4', '2', '1', '90.0');
INSERT INTO `sc` VALUES ('5', '2', '2', '90.0');
INSERT INTO `sc` VALUES ('6', '2', '3', '90.0');
INSERT INTO `sc` VALUES ('7', '3', '1', '30.0');
INSERT INTO `sc` VALUES ('8', '3', '2', '20.0');
INSERT INTO `sc` VALUES ('9', '3', '3', '40.0');
INSERT INTO `sc` VALUES ('10', '4', '1', '100');
INSERT INTO `sc` VALUES ('11', '1', '4', '90.0');

1、行专列的问题：

select sid ,
max(case sc.cid when '1' then sc.grade else 0 end  ) '1',
max(case sc.cid when '2' then sc.grade else 0 end  ) '2',
max(case sc.cid when '3' then sc.grade else 0 end  ) '3'
from sc 
group by sc.sid 

select s.sname,
max(case sc.cid when '1' then sc.grade end )as '语文',
max(case sc.cid when '2' then sc.grade end )as '数学',
max(case sc.cid when '3' then sc.grade end )as '自然科学'
from student  s 
left join 
sc
on s.sid = sc.sid
group by s.sname

新建数据

CREATE TABLE `student_grade` (
  `sname` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `english` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `chinese` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `math` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `student_grade`  VALUES ('messil', '100', '90', '99');

行转列成

select sname as '姓名' ,'数学' as '科目' ,math as 分数 from student_grade
union all
select sname as '姓名' ,'英语' as '科目' ,english as 分数 from student_grade
union all
select sname as '姓名' ,'语文' as '科目' ,chinese as 分数 from student_grade

2、exists的用法区别

#查询没有选课的人

select * from student where  not EXISTS (select sid from sc where sc.sid = student.sid )

#查询学号2没有选的课程
select cid from course where  not EXISTS (select cid from sc where sc.cid = course .cid and sc.sid = 2)

#查询选修了所有课程的人

（表示该学生  没有  没有选的课程  双重否定）

select * from student s where not exists
 ( select cid from course where  not EXISTS (select cid from sc where sc.cid = course .cid and sc.sid = s.sid)
  )

3、group以及聚合函数

平均分最高的人的信息

select sid ,avg(grade)as num  from sc
group by sid 
order by num desc   limit 1

两者结合：

查询选修了平均分最高的学生选修的所有课程的学生信息

select * from student  
where not EXISTS
(
					select  s_cid  from (
					select cid as s_cid  from  sc ,(
					select sid   from sc
					group by sid 
					order by AVG(grade) desc limit 1
					)s
					where sc.sid = s.sid 

					)course_two
					#上面这部分是查出平均分最高者选修了哪些课程	
			where not EXISTS
			(
			select cid from sc where course_two.s_cid = sc.cid  and  student.sid = sc.sid
			)
)
#所有的问题都可以通过双重not EXISTS解决

比较多的问题聚合函数  、exists  行转列之类的

4聚合函数分组前先去除条件在聚合在去除

全部课程都及格的学生的平均成绩

select sid,avg(grade) from sc s1
where not EXISTS 
(
select sid from sc s2  where grade <60  and s1.sid = s2.sid
)
group by sid 

#2号课程在60以上且平均分在60以上的同学

select sid,avg(grade) as num from sc where grade >50 and cid = 2
group by sid 
HAVING num >60

聚合函数 前排除、 exists一起使用

所有课程都在50分以上的学生的成绩

#所有课程都在50分以上的学生成绩
select * from sc 
where sid not in (
select sid from sc where grade <50
)
#所有课程都在50分以上的学生成绩
select * from sc s1
where  not exists (
select sid from sc s2 where grade <50 and s1.sid = s2.sid
)

选了两门课以上且所选课程都在50分以上的平均分在前两位的学成成绩

#选了两门以上课程的且所选课都在30以上的同学成绩
select sid,avg(grade) from sc s1
where  not exists (
select sid from sc s2 where grade <50 and s1.sid = s2.sid
)
group by s1.sid
having count(sid)>=2 
order by avg(grade) desc 
limit 2

sql优化：

https://www.cnblogs.com/yunfeifei/p/3850440.html