mysql复杂sql语句大全_Mysql 复杂sql语句练习

Mysql 复杂sql语句练习

 0

 382

一枚程序猿

2019/7/16 9:32:56

下面是练习表数据和结构

[pre]

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for course

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`id` int(11) NOT NULL,

`name` varchar(22) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('30001', '物理');

INSERT INTO `course` VALUES ('30002', '政治');

INSERT INTO `course` VALUES ('30003', '语文');

INSERT INTO `course` VALUES ('30004', '高数');

INSERT INTO `course` VALUES ('30005', '英语');

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id` int(11) NOT NULL,

`name` varchar(22) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('10001', 'tom');

INSERT INTO `student` VALUES ('10002', 'json');

INSERT INTO `student` VALUES ('10003', 'ak');

INSERT INTO `student` VALUES ('10004', 'km');

INSERT INTO `student` VALUES ('10005', 'mk');

-- ----------------------------

-- Table structure for student_course

-- ----------------------------

DROP TABLE IF EXISTS `student_course`;

CREATE TABLE `student_course` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`student_id` int(11) NOT NULL,

`course_id` int(11) NOT NULL,

`teacher_id` int(11) NOT NULL,

`score` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student_course

-- ----------------------------

INSERT INTO `student_course` VALUES ('4', '10001', '30002', '20002', '82');

INSERT INTO `student_course` VALUES ('6', '10001', '30003', '20003', '82');

INSERT INTO `student_course` VALUES ('8', '10001', '30004', '20005', '82');

INSERT INTO `student_course` VALUES ('10', '10001', '30005', '20005', '82');

INSERT INTO `student_course` VALUES ('12', '10002', '30001', '20001', '90');

INSERT INTO `student_course` VALUES ('14', '10002', '30002', '20002', '92');

INSERT INTO `student_course` VALUES ('16', '10002', '30003', '20003', '62');

INSERT INTO `student_course` VALUES ('18', '10002', '30004', '20004', '82');

INSERT INTO `student_course` VALUES ('20', '10002', '30005', '20005', '82');

INSERT INTO `student_course` VALUES ('22', '10003', '30001', '20001', '69');

INSERT INTO `student_course` VALUES ('24', '10003', '30002', '20002', '89');

INSERT INTO `student_course` VALUES ('26', '10003', '30003', '20003', '99');

INSERT INTO `student_course` VALUES ('28', '10003', '30004', '20004', '82');

INSERT INTO `student_course` VALUES ('30', '10003', '30005', '20005', '82');

INSERT INTO `student_course` VALUES ('32', '10004', '30001', '20001', '92');

INSERT INTO `student_course` VALUES ('34', '10004', '30002', '20002', '93');

INSERT INTO `student_course` VALUES ('36', '10004', '30003', '20003', '73');

INSERT INTO `student_course` VALUES ('38', '10004', '30004', '20004', '82');

INSERT INTO `student_course` VALUES ('40', '10004', '30005', '20005', '82');

INSERT INTO `student_course` VALUES ('42', '10005', '30001', '20001', '95');

INSERT INTO `student_course` VALUES ('44', '10005', '30002', '20002', '75');

INSERT INTO `student_course` VALUES ('46', '10005', '30003', '20003', '79');

INSERT INTO `student_course` VALUES ('48', '10005', '30004', '20004', '82');

INSERT INTO `student_course` VALUES ('50', '10005', '30005', '20005', '82');

-- ----------------------------

-- Table structure for teacher

-- ----------------------------

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

`id` int(11) NOT NULL,

`name` varchar(22) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of teacher

-- ----------------------------

INSERT INTO `teacher` VALUES ('20001', 'su');

INSERT INTO `teacher` VALUES ('20002', 'wang');

INSERT INTO `teacher` VALUES ('20003', 'zhou');

INSERT INTO `teacher` VALUES ('20004', 'yang');

INSERT INTO `teacher` VALUES ('20005', 'liu');

[/pre]

4个表，包括student，course，student_course,teacher

详细练习：

-- 1.查询物理成绩比英语成绩高的所有学生

[pre]

select * from

(select * from student_course WHERE course_id=30001) a,

(select * from student_course WHERE course_id=30005) b

where a.score>b.score and a.student_id=b.student_id;

[/pre]

-- 2.查询平均成绩大于60分的同学的学号和平均成绩；

[pre]

select a.student_id,avg(a.score) from

student_course a

GROUP BY a.student_id

having avg(a.score)>60 ;

[/pre]

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

[pre]

select b.student_id,a.name,COUNT(b.course_id),SUM(b.score)

from student a ,student_course b

where a.id=b.student_id

GROUP BY b.student_id ;

[/pre]

-- 4、查询名字l开头的老师的个数；

[pre]

select COUNT(DISTINCT(name)) FROM teacher where name like 'l%';

[/pre]

-- -- 5,查询没学过“liu”老师课的同学的学号、姓名；

[pre]

select * from student where id not in(

select DISTINCT(student_id) from student_course where teacher_id=(

select DISTINCT(id) from teacher where name="liu")

);

[/pre]

-- 6 学过30001和30002课程的同学的学号、姓名；--

[pre]

select a.student_id,b.name

from student_course a,student b

where a.student_id=b.id and a.course_id=30001 and exists(

select* from student_course s2 where s2.student_id=a.student_id and s2.course_id=30002

)

group by a.student_id;

[/pre]

-- 7 学过30001和30002课程的同学的学号

[pre]

select a.student_id from

(select * from student_course where course_id=30001) a inner join

(select * from student_course where course_id=30002) b

on a.student_id=b.student_id;

[/pre]

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

[pre]

select * from student where id in

(

select distinct(student_id) from student_course where teacher_id=

(

select distinct(id) from teacher where name="liu"

)

);

[/pre]

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

[pre]

select*from student where id in(

select distinct(a.student_id) from

(select * from student_course where course_id=30002) a inner join (select * from student_course where course_id=30001) b

on a.score

[/pre]

-- 10 查询没有学全所有课的同学的学号、姓名

[pre]

select a.student_id,b.name

from student_course a,student b

where a.student_id=b.id

group by a.student_id

having count(a.course_id) <> (select count(*) from course);

[/pre]

-- 11查询所有课程成绩小于90分的同学的学号、姓名；

[pre]

select * from student where id not in (

select student_id from student_course where score>90

);

[/pre]

-- 12查询至少学过学号为“10001”同学所有一门课的其他同学学号和姓名

[pre]

select *from student where id in (

SELECT distinct(student_id) FROM student_course where student_id <> 10001 and course_id in

(

SELECT course_id FROM student_course where student_id=10001

));

select distinct a.id,a.name from student a, student_course b where a.id=b.student_id and b.student_id <> 10001 and b.course_id in(

SELECT course_id FROM student_course where student_id=10001

);

[/pre]

-- 13把“student_course”表中“liu”老师教的课的成绩都更改为此课程的平均成绩

[pre]

update student_course set score =(

select score1 from (

select avg(b.score) as score1 from teacher a,student_course b where a.name="liu" and b.teacher_id=a.id)

as tablename2)where teacher_id=(select id from teacher where name="liu");

select * from student_course;

update student_course set score=82.1667 where teacher_id=20004;

SET SQL_SAFE_UPDATES = 0;

show variables like 'SQL_SAFE_UPDATES';

[/pre]

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

[pre]

select b.id,b.name from student_course a,student b

where a.course_id in(select course_id from student_course where student_id=10002) and a.student_id=b.id and a.student_id <> 10002

group by b.id

having count(*)=(select count(*) from student_course where student_id=10002);

[/pre]

-- 15 删除学习“su”老师课的student_course表记录；

[pre]

delete from student_course

where teacher_id=(select id from teacher where name="su");

[/pre]

-- 16查找要求符合以下条件：没有上过编号“30003”课程的同学学号、

-- "30003"号课的平均成绩；

[pre]

select id,(select avg(score) from student_course where course_id="30003") from student where id not in(

select student_id from student_course where course_id="30003"

);

[/pre]

-- 17按平均成绩从高到低显示所有学生的“物理”、“政治”、“英语”三门的课程成绩，

-- 按如下形式显示： 学生ID,物理,政治,英语,有效课程数,有效平均分

[pre]

select t.student_id as 学生ID,

(select score from student_course where t.student_id=student_id and course_id="30001" ) as 物理,

(select score from student_course where t.student_id=student_id and course_id="30002" ) as 政治,

(select score from student_course where t.student_id=student_id and course_id="30005" ) as 英语,

count(*) AS 有效课程数, AVG(t.score) AS 平均成绩

from student_course t

group by t.student_id

order by avg(t.score);

[/pre]

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

[pre]

select course_id as 课程ID,max(score) as 最高分,min(score) as 最低分

from student_course

group by course_id;

[/pre]

-- 19 按各科平均成绩从低到高和及格率

[pre]

select course_id as 课程ID,avg(score) as 平均成绩,

100*sum(case when score>=60 then 1 else 0 end) /count(*) as 及格百分数

from student_course t

group by course_id

order by avg(score) desc;

[/pre]

-- 20.查询如下课程平均成绩和及格率的百分数(用"1行"显示):

-- 物理(30001)，政治(30002)，语文 (30003)，高数(30004)

[pre]

select

(select avg(score) from student_course where course_id="30001") as 物理,

(select avg(score) from student_course where course_id="30002") as 政治,

(select avg(score) from student_course where course_id="30003") as 语文,

(select avg(score) from student_course where course_id="30004") as 高数,

100*sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率,

avg (score) as 平均成绩

from student_course

group by course_id;

[/pre]

-- 21 查询不同老师所教不同课程平均分从高到低显示

[pre]

select avg(score)

from student_course

group by teacher_id

order by avg(score) desc;

[/pre]

-- 22统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

[pre]

select a.course_id as 课程ID,

b.name as 课程名称,

sum(case when a.score between 85 and 100 then 1 else 0 end) as "[100-85]",

sum(case when a.score between 70 and 85 then 1 else 0 end) as "[85-70]",

sum(case when a.score between 65 and 70 then 1 else 0 end) as "[70-60]",

sum(case when a.score <60 then 1 else 0 end) as "[<60]"

from student_course a,course b

where a.course_id=b.id

group by a.course_id,b.name;

[/pre]

-- 23 查询学生平均成绩

[pre]

select a.student_id as 学生ID,b.name as 姓名,avg(a.score) as 平均成绩

from student_course a,student b

where a.student_id=b.id

group by a.student_id

order by avg(a.score) desc;

[/pre]

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

[pre]

SELECT t1.student_id as 学生ID,

t1.course_id as 课程ID,

t1.score as 分数

FROM student_course t1

WHERE t1.id IN (

select t.id from(

SELECT id

FROM student_course

ORDER BY score DESC

limit 3) as t

)

ORDER BY t1.score desc;

[/pre]

-- 25查询每门课程被选修的学生数

[pre]

select count(student_id)

from student_course

group by course_id;

[/pre]

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

[pre]

select a.student_id,b.name

from student_course a,student b

where b.id=a.student_id

group by student_id

having count(course_id)=1;

[/pre]

-- 27 查询姓“t”的学生名单

[pre]

select * from student where name like "t%" ;

[/pre]

-- 28 查询同名同性学生名单，并统计同名人数

[pre]

select name ,count(name) from student group by name;

[/pre]

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

[pre]

select avg(score) ,course_id from student_course group by course_id order by avg(score),course_id desc ;

[/pre]

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

[pre]

select a.student_id,b.name,avg(a.score)

from student_course a,student b

where a.student_id=b.id

group by a.student_id

having avg(a.score)>85;

[/pre]

-- 31 查询课程名称为“政治”，且分数低于60的学生姓名和分数

[pre]

select a.student_id,a.score,b.name

from student_course a,student b

where a.student_id=b.id and a.course_id=(

select id from course where name ="政治"

)

group by a.student_id,a.score

having a.score <60;

[/pre]

-- 32查询所有学生的选课情况；

[pre]

select a.student_id,a.course_id,b.name,c.name

from student_course a,student b,course c

where a.student_id=b.id and a.course_id=c.id

group by a.student_id,a.course_id;

[/pre]

-- 33 查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

[pre]

select a.student_id,a.course_id,a.score,b.name,c.name

from student_course a,student b,course c

where a.student_id=b.id and a.course_id=c.id

group by a.student_id,a.course_id,a.score

having a.score>70;

[/pre]

-- 34 查询不及格的课程，并按课程号从大到小排列

[pre]

select course_id,score

from student_course

group by course_id,score

having score<60

order by course_id;

[/pre]

-- 35 查询课程编号为30003且课程成绩在80分以上的学生的学号和姓名；

[pre]

select a.student_id,a.score,a.course_id,b.name

from student_course a ,student b

where a.student_id=b.id and a.course_id="30003"

group by a.student_id,a.score,a.course_id

having a.score>80;

[/pre]

-- 36求选了课程的学生人数

[pre]

select count(distinct student_id) from student_course;

[/pre]

-- 37 查询选修“liu”老师所授课程的学生中，成绩最高的学生姓名及其成绩

[pre]

select b.name,a.score

from student_course a,student b,course c,teacher d

where a.course_id=c.id and a.student_id=b.id and a.teacher_id=d.id and d.name="liu"

and a.score=(select max(score) from student_course where course_id=c.id);

[/pre]

-- 38查询各个课程及相应的选修人数

[pre]

select course_id,count(student_id)

from student_course

group by course_id;

[/pre]

-- 39 查询不同课程成绩相同的学生的学号、课程号、学生成绩

[pre]

select a.student_id,a.course_id,a.score

from student_course a,student_course b

where a.score=b.score and a.course_id <> b.course_id;

[/pre]

-- 40 查询每门功成绩最好的前两名

[pre]

select * from student_course where score in(

select score from(

select score from student_course limit 2

) as a)order by course_id desc;

[/pre]

-- 41 统计每门课程的学生选修人数(超过4人的课程才统计)。

-- 要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，

-- 若人数相同，按课程号升序排列

[pre]

select course_id, count(*)

from student_course

group by course_id

having count(*)>4

order by count(*),course_id desc;

[/pre]

-- 42 检索至少选修两门课程的学生学号

[pre]

select student_id

from student_course

group by student_id

having count(*)>=2;

[/pre]

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

[pre]

select *

from course

where id in(

select course_id from student_course group by course_id having count(*)=(select count(*) from student)

);

[/pre]

-- 44 查询没学过“yang”老师讲授的课程的学生姓名

[pre]

select name from student where id not in(

select distinct a.student_id

from student_course a,teacher c

where a.teacher_id=c.id and c.name = "yang"

);

[/pre]

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

[pre]

select student_id,avg(score)

from student_course

where score <60

group by student_id

having count(*) >=2;

[/pre]

-- 46 检索“30004”课程分数小于60，按分数降序排列的同学学号

[pre]

select student_id

from student_course

where course_id="30004" and score<60

group by student_id,score

order by score desc;

[/pre]

-- 47 删除“10002”同学的“30001”课程的成绩

[pre]

delete from student_course where student_id=10002 and course_id=30002;

[/pre]