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