MySQL 语句练习

---恢复内容开始---

在cmd中备份：数据表结构+数据

　　　　　　　　mysqldump -u root db1 > d: est est.sql -p

　　　　　　　数据表结构

　　　　　　　　mysqldump -u root -d db1 > d: est est.sql -p

再cmd中将备份的执行文件写入数据库

　　　　　　创建表：  create database test default charset utf8 collate utf8_general_ci

　　　　　　写入：mysql -u root test < d: eat est.sql -p  同样，加-d也知识写入结构

navicat 的而error 1055问题：

在MySQL数据库下，执行sql语句，报错（虽然sql也执行成功了，但是看着可不爽）。错误信息如下：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方案：

　　一、去MySQL的根目录下找my-default.ini配置文件，打开后将sql_mode中的ONLY_FULL_GROUP_BY删掉



　　二、执行SELECT @@sql_mode语句



　　三、若存在ONLY_FULL_GROUP_BY，将其删除后更新记录，如下



　　四、问题解决，但是上述方法只是当前会话的，重新进入MySQL后问题依然存在，彻底解决如下

set sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

表结构数据创建文件如下：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

表结构和数据

一、表关系

请创建如下表，并创建相关约束

二、操作表

1、自行创建测试数据   上面有创建代码。

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

select aa.student_id,aa.num,bb.num from     首先写出要选择的内容，后面想办法
(select student_id,num from score left join course on score.course_id=course.cid where cname="生物") as aa     创建临时表，取到生物课程的学号和分数信息
left JOIN　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　连表
(select student_id,num from score left join course on score.course_id=course.cid where cname="物理") as bb　　再创建临时表，取到物理课程的学号和分数信息
on
aa.student_id=bb.student_id　　　　　　按照学号相同进行连接
where
aa.num>if(isnull(bb.num),0,bb.num)　　　　　　根据分数进行比较，切记要按照临时表的表明进行槽错，如果学了生物课但是没有选物理课的话，会出现null,则处理null。

3、查询平均成绩大于60分的同学的学号和平均成绩； 

select student_id,avg(num) from score group by student_id having avg(num)>60   按照学号分组，然后将分数求平均值，因为有聚合函数，所以用having来筛选

注意select 后面的内容必须要和 group by 相照应

4、查询所有同学的学号、姓名、选课数、总成绩；

select student_id,sname,count(course_id),sum(num) from score 
left JOIN
student　　　　　　　　因为要有学生姓名，所以必须连表
on score.student_id=student.sid　　　　按照学生id进行连接
group by student_id　　　　　　　　按照id分组

5、查询姓“李”的老师的个数；

select count(tname) from teacher　　　　
where tname like "李%"

select count(1) from 
(select tid from teacher where tname like '李%') as b　　　　必须有这个b,因为这代表了这个临时表的存在，作为临时表供选择的时候必须有as，但是6题中没有as，注意这一点

6、查询没学过“李平”老师课的同学的学号、姓名；

select sid,sname from student 　　　　从学生表中选择学号和姓名
where sid not in　　　　　　　　　　　　让学号不在  学过李平老师课程的学号名单中
(select student_id from score　　　　从分数表中选择学生id
WHERE score.course_id in　　　　　　　　让该学生的课程只要在李平老师的课程列表中就可以了
(select cid from course　　　　　　　　以下几行是找到李平老师所教的课程
left JOIN
teacher
on course.teacher_id=teacher.tid
where tname="李平老师")
GROUP BY　　　　　　　　　　　　　　　　从分数表中选择学生id，因为有同时选择几门李平老师课程的学生，按照学号分组
student_id)　　　　　　　　　　

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select student_id,sname from
(select student_id from score
where course_id in (1,2)
group by student_id　　　　　　　　　　选过两门课程的人，进行分组按照id
having count(course_id)=2) as aa
left JOIN
student
on aa.student_id=student.sid;
 先分组再匹配姓名


先匹配姓名再分组
select student_id,sname from
(select student_id,course_id from score where course_id = 1 or course_id = 2) as B     学号的来源，当该学生学过两门课程中的一门的时候，作为一个临时表
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1;   对应上姓名后，最后分组，然后看该学号下课程的个数


直接根据学生表进行操作，避免连表的麻烦

select sid,sname from student where sid in
(select student_id from score where course_id in (1,2) GROUP BY student_id having count(course_id)=2)

8、查询学过“李平”老师所教的所有课的同学的学号、姓名；

select sid,sname from student where sid in
(select student_id from score
where course_id in
(select cid from course
left join teacher
on course.teacher_id=teacher.tid
where tname="李平老师")
group by student_id)　　　　　　　　　　　　可以将group by 去掉。

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

　　　　同第二题，只要改成course_id=1，2 即可。

10、查询有课程成绩小于60分的同学的学号、姓名；

　

select sid,sname from student where sid in      
(select student_id from score
where num<60
group by student_id)

select sid,sname from student where sid in (
select distinct student_id from score where num < 60        distinct   效率不高，也有去重作用
)

11、查询没有学全所有课的同学的学号、姓名；

select sid,sname from student where sid not in 
(select student_id from score        　　　　　　　　　　这种情况下distinct是没有作用的，因为后面还要用到分组后的信息来作出筛选，可与上面对比
group by student_id
having count(course_id)=(select count(cid) from course))

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select sid,sname from student where sid in      　　　　　　还是避免连表
(select DISTINCT student_id from score where course_id in
(select course_id from score where student_id=1))

13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

select sid,sname from student where sid in
(select DISTINCT student_id from score where course_id in
(select course_id from score where student_id=1))
and sid != 1

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

select sid,sname from student where sid in
(select student_id from score where student_id in
(select student_id from score where course_id in
 4 (select course_id from score where student_id=2)      找到2号学生所学的课程，取到这些学生的学号
 5 group by student_id having count(course_id)=(select count(course_id) from score where student_id=2))  找到学了三门的人，但是还有比三门多的人 
group by student_id having count(course_id)=(select count(course_id) from score where student_id=2))  然后再到score中找，找这些id中的人学了3门的
and sid != 2  　　剔除自己


老师的方法贼鸡巴烂
select student_id,sname from score
left join student 
on score.student_id = student.sid       连表，按照学号
where student_id in 　　　　　　　　　　　　加判断条件，学号必须满足以下条件
15 (　　　　　　　　　　　　　　　　　　　　　　　　　　　　学了3门课程数得人
16 select student_id from score  where student_id != 2 　　　　　　　得到不是2号学生得所有学生，并且将其按照学号分组，再作出筛选
17 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)　　　　　　　　　　　选出学得课程数和2号学生得课程数相同的人
18 ) 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　得到2号学生的科目数
19 and　　　　　　　　　　　　　　　　　　　　　　　　　　 学得课程中有3门课程课2号学生一样得人
20 course_id in (select course_id from score where student_id = 2) 　　　　　　　　　2号学生所选择的课程　
group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)    根据id分组，筛选出课程数量和2号学生一样的人

红色字体为得到选了3门课程的其他人，并且所学的课程中有2号学生选的
　　　　　　

15、删除学习“叶平”老师课的SC表记录；

delete from score where course_id in (
        select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '李平老师'   当课程在里面的话，就删除
    )

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 

insert into sc(student_id,avg_score) 
select student_id,avg(num) from score where student_id not in
(select student_id FROM score where course_id=2 group by student_id)
group by student_id 

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

以下两种写法：请看第一种非常牛逼的写法！！！

select 
student_id as 学号,
(select num from score where student_id=aa.student_id and course_id=(select cid from course where cname="物理")) as 物理,
(select num from score where student_id=aa.student_id and course_id=(select cid from course where cname="生物")) as 生物,
(select num from score where student_id=aa.student_id and course_id=(select cid from course where cname="体育")) as 体育,
one as 有效课程数,
two as 平均成绩
 from
(select student_id,count(course_id) as one,avg(num) as two from score group by student_id order by avg(num) desc) as aa;

下面是一种非常垃圾的写法。。。
select sc.student_id,
        (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
        (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
        (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
        count(sc.course_id),
        avg(sc.num)
    from score as sc
    group by sc.student_id 
        order by avg(sc.num) desc        

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course_id,max(num),min(num) from score group by course_id

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select course_id,avg(num),sum(case when num>60 then 1 else 0 end)/count(1) as percent FROM 按照课程分组，用聚合函数sum(如果分数大于60,1,其他的话0，结束)/数量
score group by course_id    order by avg(num) asc,percent desc　　　　　　通过课程分组，得到上面的合格率，后面的排名是有先后顺序的。

select sum(case when num>60 then 1 else 0 end) ,sum(1) from score group by course_id

20、课程平均分从高到低显示（现实任课老师）；

select tname,avg(num) from score 
left JOIN course
on score.course_id=course.cid
left join teacher
on course.teacher_id=teacher.tid
group by 
course_id
ORDER BY
avg(num) desc

21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

　　以下是考虑并列情况的：只不过将成绩进行分组后再排序的！！！

实际上是进行遍历循环的（内部）

select course_id,
(select num from score WHERE course_id=aa.course_id group by num order by num desc limit 0,1) as 第一名,    让课程id等于aa的，然后取到所有成绩，将成绩进行分组，
(select num from score WHERE course_id=aa.course_id group by num order by num desc limit 1,1) as 第二名,　　 然后排序，取得名次。
(select num from score WHERE course_id=aa.course_id group by num order by num desc limit 2,1) as 第三名
FROM
score as aa
group by 
course_id

select score.course_id,num from score
left join
(select course_id, 
 4 (select num from score where course_id=aa.course_id GROUP BY num ORDER BY num desc limit 0,1) as one,
 5 (select num from score where course_id=aa.course_id GROUP BY num ORDER BY num desc limit 3,1) as four
 6 from 
 7 score as aa
 8 group BY
 9 course_id) as ins　　　　　　　　连了一个临时表，里面有成绩为1和4的信息
on score.course_id=ins.course_id
where score.num<=ins.one and score.num>=ins.four
这是显示各科分数为前四的人的信息。

22、查询每门课程被选修的学生数；

select course_id,count(1)
from 
score
group by course_id

23、查询出只选修了一门课程的全部学生的学号和姓名；

select sid,sname from student
where sid in
(select student_id from score
group by student_id 
having count(1)=1)

24、查询男生、女生的人数；

select gender,count(1) from student
group by gender

25、查询姓“张”的学生名单；

select sname from student where sname like "张%"

26、查询同名同姓学生名单，并统计同名人数；

select sname,count(1) from student
GROUP BY sname
having count(1)>1

27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

select course_id,avg(if(isnull(num),0,num)) from score
GROUP BY course_id ORDER BY avg(if(isnull(num),0,num)) desc,course_id asc;

28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

SELECT student_id,sname,avg(if(ISNULL(num),0,num)) from score       有鸡巴必要加红色字体的那部分
left join student on score.student_id=student.sid
GROUP BY student_id HAVING avg(if(isnull(num),0,num))>85

29、查询课程名称为“生物”，且分数低于60的学生姓名和分数；

select student.sid,sname from score
LEFT JOIN course on score.course_id=course.cid
LEFT JOIN student on score.student_id=student.sid
where cname='生物' and num<60

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 

select sid,sname
from student where sid in
(select student_id from score
where course_id=3 and num>80)

31、求选了课程的学生人数

select count(sid)
from student where sid in (select student_id from score)

32、查询选修“李平老师”所授课程的学生中，成绩最高的学生姓名及其成绩；

select bb.student_id,sname,aa.course_id,bb.num from 
(select course_id,max(num) as big from score where course_id in
3 (select cid from course left join teacher on course.teacher_id=teacher.tid where tname="李平老师")　　　　找到李平老师所教的课程id
4 group by course_id) as aa　　　　　　　　从成绩表中找到有课程id为2，4的学生，并且按照id分组，取出最大的成绩    至此有了两项 1、课程id  2、最高成绩
left join score as bb　　　　　　　　　　再连表成绩表
on aa.course_id=bb.course_id　　　　　　筛选出有课程2，4的学生
left join student　　　　　　　　　　　　再连学生表
on bb.student_id=student.sid　　　　　　让学生id对应上
where aa.big=bb.num　　　　　　　　　　最后筛选成绩是最高分的

33、查询各个课程及相应的选修人数；

select course_id,count(1)
from score
group by course_id

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select aa.course_id,aa.student_id,aa.num,bb.student_id,bb.num  from score as aa,score as bb 
where aa.student_id != bb.student_id and aa.course_id != bb.course_id and aa.num=bb.num

35、查询每门课程成绩最好的前两名；

select aa.course_id,
(select num from score WHERE course_id=aa.course_id group by num ORDER BY num desc limit 0,1),
(select num from score WHERE course_id=aa.course_id group by num ORDER BY num desc limit 1,1)
from score as aa
group by aa.course_id

36、检索至少选修两门课程的学生学号；

select student_id from score group by student_id having count(1)>2

37、查询全部学生都选修的课程的课程号和课程名；

select count(1),cname from score group by course_id having count(1)=
(select count(1) from student)

38、查询没学过“李平”老师讲授的任一门课程的学生姓名；

SELECT sid,sname from student where sid not in
(
select student_id from score WHERE course_id in
(select cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid where tname='李平老师')
GROUP BY student_id
)

39、查询两门以上不及格课程的同学的学号及其平均成绩；

select student_id,sum(case when num<60 then 1 else 0 end) as aa,avg(num) 
from score group by student_id having aa>1

40、检索“004”课程分数小于60，按分数降序排列的同学学号；

select student_id,num from score where course_id=4 and num<60 ORDER BY num DESC

41、删除“002”同学的“001”课程的成绩；

DELETE from score where student_id=2 and course_id=1;

---恢复内容结束---