数据库题
1、查询男生、女生的人数；
2、查询姓“张”的学生名单；
3、课程平均分从高到低显示
4、查询有课程成绩小于60分的同学的学号、姓名；
5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
6、查询出只选修了一门课程的全部学生的学号和姓名；
7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
10、查询平均成绩大于60分的同学的学号和平均成绩;
11、查询所有同学的学号、姓名、选课数、总成绩；
12、查询姓“李”的老师的个数；
13、查询没学过“张磊老师”课的同学的学号、姓名；
14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
15、查询学过“李平老师”所教的所有课的同学的学号、姓名；
1
select gender, count(sid) from student group by gender;
+--------+------------+
| gender | count(sid) |
+--------+------------+
| 女 | 6 |
| 男 | 10 |
+--------+------------+
2
select * from student where sname like = '张%'; #这个为什么不对了
select * from student where sname regexp '^张'; #用正则可以
3
select * from score order by num desc;
select * from score order by num desc,course_id asc;
4、查询有课程成绩小于60分的同学的学号、姓名；
mysql> select * from student where sid in (select student_id from score where nu
m < 60);
select * from score s inner join student s1 where s.student_id = s1.sid;
错误: (如果显示num的话,distinct是去重所有的,对所有的字段去重)
mysql> select distinct sname,student_id ,num from score s inner join student s1 on s.student
_id = s1.sid where num < 60;
select distinct sname,num from score inner join student s1 on student_id = s1.sid where num < 60;
正确:
select distinct student.sid,sname from student inner join score on student.sid=student_id where num < 60;
5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
mysql>select sid, sname from student
where sid in (select student_id from score s inner join course c on s.course_id = c.cid
where cid in (select cid from score s inner join course c on s.course_id = c.cid where student_id = 1));
6、查询出只选修了一门课程的全部学生的学号和姓名；
select * from score s inner join course c on s.course_id = c.cid;
select * from score s inner join course c on s.course_id = c.cid group by student_id having count(student_id) = 1;
mysql> select sid, sname from student where sid in (select student_id from score s inner join course c on s.course_id = c.cid group by student_id having count(student_id) = 1);
7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
mysql> select course_id, max(num) as max_num ,min(num) as min_num from score gro
up by course_id;
问题: 关于group by 只显示第一个值,而不是所有的?
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
错误:
select * from score where course_id = 1 union select * from score where course_id = 2;
select * from (select num as num1 ,student_id, course_id from score as c1 where course_id = 1 inner join select num as num2, student_id,course_id from score as c2 where course_id = 2) on c1.student_id = c2.student_id;
正确:
select * from (select student_id,num as num1 from score where course_id = 1) as c1 inner join (select student_id,num as num2 from score where course_id = 2) as c2 on c1.student_id = c2.student_id where num1>num2;
select * from (select student_id,num as num1 from score where course_id = 1) as c1 inner join (select student_id,num as num2 from score where course_id = 2) as c2 on c1.student_id = c2.student_id where num1>num2;
select sid, sname from student where sid in (select c2.student_id from (select student_id,num as num1 from score where course_id = 1) as c1 inner join (select student_id,num as num2 from score where course_id = 2) as c2 on c1.student_id = c2.student_id where num1>num2) ;
# 包括num的全部信息的
select * from score inner join (select sid, sname from student where sid in (select c2.student_id from (select student_id,num as num1 from score where course_id = 1) as c1 inner join (select student_id,num as num2 from score where course_id = 2) as c2 on c1.student_id = c2.student_id where num1>num2 )) as s on score.student_id=s.sid where course_id =1 or course_id =2;
错误:Every derived table must have its own alias（sql语句错误解决方法）
每一个派生出来的表都必须有一个自己的别名
知道了() 别名错误> 然后慢慢的改了
9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
select s.student_id from (select student_id, num num1 from score
where course_id in (select cid from course where cname = '生物')) as s
inner join
(select student_id, num num2 from score
where course_id in (select cid from course where cname = '物理')) as w
on s.student_id = w.student_id where num1 > num2;
10、查询平均成绩大于60分的同学的学号和平均成绩;
错误:
mysql> select student_id,avg(num) avg_num from score group by student_id where a
vg_num>60;
正确:
mysql> select student_id,avg(num) avg_num from score group by student_id havin
avg_num>60;
11、查询所有同学的学号、姓名、选课数、总成绩；
select * from student inner join score on student.sid = score.student_id;
mysql> select student_id, sname, count(course_id), sum(num) from student inner join score on student.sid = score.student_id group by student_id;
12、查询姓“李”的老师的个数；
mysql> select tname , count(tname) li_count from teacher where tname regexp '^李
';
13、查询没学过“张磊老师”课的同学的学号、姓名；
mysql> select cid from course where teacher_id in (select tid from teacher where t
name = '张磊老师');
mysql> select student_id from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname = '张磊老师'));
select sid,sname from student where sid not in (select student_id from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname = '张磊老师')));
14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select * from (select * from score where course_id = 1) as c1 inner join (select * from score where course_id = 2) as c2 on c1.student_id = c2.student_id;
select c1.student_id from (select * from score where course_id = 1) as c1 inner join (select * from score where course_id = 2) as c2 on c1.student_id = c2.student_id;
select sid, sname from student where sid in (select c2.student_id from (select * from score where course_id = 1) as c1 inner join (select * from score where course_id = 2) as c2 on c1.student_id = c2.student_id);
15、查询学过“李平老师”所教的所有课的同学的学号、姓名；
select sid, sname from student where sid in (select student_id from score where course_id in (select cid from course where cid in (select tid from teacher where tname = '李平老师')));
1、查询没有学全所有课的同学的学号、姓名；
2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
3、删除学习“叶平”老师课的SC表记录；
4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
8、查询各科成绩前三名的记录:(不考虑成绩并列情况)
9、查询每门课程被选修的学生数；
10、查询同名同姓学生名单，并统计同名人数；
11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
15、求选了课程的学生人数
16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
17、查询各个课程及相应的选修人数；
18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
19、查询每门课程成绩最好的前两名；
20、检索至少选修两门课程的学生学号；
21、查询全部学生都选修的课程的课程号和课程名；
22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
23、查询两门以上不及格课程的同学的学号及其平均成绩；
24、检索“004”课程分数小于60，按分数降序排列的同学学号；
25、删除“002”同学的“001”课程的成绩；
1、查询没有学全所有课的同学的学号、姓名；
select student_id , count(course_id) course_count from score group by student_id having course_count < (select count(cid) from course) ;
select sid, sname from student where sid in (select student_id from score group by student_id having count(course_id) < (select count(cid) from course) );
2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
select sc.course_id from (select * from student where sid = '2')as st inner join (select * from score) as sc on st.sid = sc.student_id ;
select * from score where course_id
select *,group_concat(course_id) course_all from score group by student_id where course_all = (select sc.course_id from (select * from student where sid = '2')as st inner join (select * from score) as sc on st.sid = sc.student_id) ;
select *,group_concat(course_id) course_all from score group by student_id having course_all = '1,3,4'; # 不是 ()
select *, group_concat(course_id) course_all from score group by student_id where course_all any (select sc.course_id from (select * from student where sid = '2')as st inner join (select * from score) as sc on st.sid = sc.student_id);
错误:
Operand should contain 1 column
原因是in条件后面有多个字段,in后面只能有一个字段。
我的错误: having course_all = (1,3,4); # '' 是字符串哦!
查课程
select sc.course_id from (select * from student where sid = '5')as st inner join (select * from score) as sc on st.sid = sc.student_id order by course_id;
mysql> select *,group_concat(course_id) course_all from score group by student_i
d having course_all = '1,2,3,4' ; # '' 是字符串哦!
#把弄成字符串 group_concat()比较
mysql> select *,group_concat(course_id) course_all from score group by student_id having course_all =
( select group_concat(sc.course_id) from (select * from student where sid = '2')as st inner join (select * from score) as sc on st.sid = sc.student_id ); # 因为2只有一种 所以为空
3、删除学习“叶平”老师课的SC表记录；
select tid from teacher where tname = '李平老师';
select cid from course where teacher_id in (select tid from teacher where tname = '李平老师');
select * from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));
delete from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));
4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
insert into score (not2) select student_id from score where course_id != 2;
select avg(num) num_avg from score where course_id = 3;
select * from (select *,avg(num) num_avg from score where course_id = 3)as s1
right join
(select * from score where course_id != 2)as s2 on s1.student_id=s2.student_id;
# 笛卡尔积
select distinct t1.num_avg, t2.student_id from (select *,avg(num) num_avg
from score where course_id = 3) as t1, (select * from score where course_id !=2) as t2;
insert into score(course_id, student_id, num) select distinct 2,t2.student_id,t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id != 2) as t2);
insert into score (course_id, student_id, num) select 2,t2.student_id,t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id != 2) as t2);
insert into score (course_id, student_id, num) select 2,t2.student_id,t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id != 2) as t2);
insert into score (course_id, student_id, num) select 2, t2.student_id ,t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id != 2) as t2);
可以的简单的处理:
insert into score ( student_id, num ) values (1,1);
insert into score (sid, student_id, course_id, num) values (53,13,3,89);
insert into score (sid, student_id, course_id) values (53,13,3);
start transaction; #开启事务,一次一启
rollback; #回退
insert into score (sid, student_id, course_id) select 53, student_id, course_id from score where sid = 52;
错误写法:
insert into score (course_id, student_id, num) select 2, t2.student_id ,t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id != 2) as t2);
后半部分: select (53,2,t1.num_avg, t2.student_id) from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id !=2) as t2;
也是这个错误 : server version for the right syntax to use near ')' at 所以是后半部分的错误
insert into score (sid, course_id, student_id, num) select 53,2,t1.num_avg, t2.student_id from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id !=2) as t2;
error:ails (homework.score, CONSTRAINT fk_score_student FOREIGN KEY (student_id ) REFERENCES student (sid))
insert into score (student_id, sid, course_id, num) select t2.student_id ,53, 2, t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id !=2) as t2;
Duplicate entry '53' for key 'PRIMARY'(关键字“primary”的条目“53”重复)
问题: sid 如何给值? 很多个sid, 别的值都写好了 2 的那一栏瞟了一下老师的,还有笛卡尔积 (看的网上的解决!)
insert into score (student_id, course_id, num) select distinct t2.student_id, 2, t1.num_avg from (select *,avg(num) num_avg from score where course_id = 3) as t1, (select * from score where course_id !=2) as t2;
# id列不要填写 一定要对应上列数
5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
排序显示 123 和课程成绩
select * from score order by student_id , course_id;
二连
select * from (select student_id, num as '生物' from score where course_id in (select cid from course where cname = '生物'))as s
inner join
(select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理')) as w
on s.student_id = w.student_id;
三连
select t.student_id, 生物, 体育, 物理 from
(select student_id, num as '生物' from score where course_id in (select cid from course where cname = '生物'))as s
inner join
(select student_id, num as '体育' from score where course_id in (select cid from course where cname = '体育')) as t
on s.student_id = t.student_id
right join
(select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理')) as w
on s.student_id = w.student_id ;
select t.student_id, 生物, 体育, 物理 from
(select student_id, num as '生物' from score where course_id in (select cid from course where cname = '生物'))as s
inner join
(select student_id, num as '体育' from score where course_id in (select cid from course where cname = '体育')) as t
inner join
(select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理')) as w
on s.student_id = t.student_id = w.student_id;
select t.student_id, 生物, 体育, 物理 from
(select student_id, num as '生物' from score where course_id in (select cid from course where cname = '生物'))as s
inner join
(select student_id, num as '体育' from score where course_id in (select cid from course where cname = '体育')) as t
inner join
(select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理')) as w
on s.student_id = t.student_id = w.student_id;
select * from (select * from (select student_id, num as '生物' from score where course_id in (select cid from course where cname = '生物'))as s
inner join
(select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理')) as w
on s.student_id = w.student_id )as sw
inner join
(select student_id, num as '体育' from score where course_id in (select cid from course where cname = '体育')) as t
on t.student_id = sw.student_id ;
# 错误: 第二次
Every derived table must have its own alias:(别名的)
select * from (select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理') )as w ;
select * from (select student_id, num as '体育' from score where course_id in (select cid from course where cname = '体育') )as t ;
select student_id, num as '物理' from score where course_id in (select cid from course where cname = '物理') ;
select student_id, num as '体育' from score where course_id in (select cid from course where cname = '体育') ;
6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id, max(num), min(num) from score group by course_id;
7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id, avg(num) avg_num from score group by course_id order by avg_num;
mysql> select course_id, count(num)/13 jige_num from score where num > 60 group by course_id order by jige_num desc;
8、查询各科成绩前三名的记录:(不考虑成绩并列情况)
9、查询每门课程被选修的学生数；
mysql> select *, count(student_id) course_people_num from score group by course_id;
10、查询同名同姓学生名单，并统计同名人数；(用数据库简单,一分组就好了)
insert into student (gender, class_id, sname) values ('女', 3, '刘四');
insert into student ( class_id, sname) values ( 3, '刘四');
mysql> select *,count(sname) same_name_num from student group by sname having same_name_num > 1;
11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
mysql> select *, avg(num) avg_num from score group by course_id order by avg_num desc;
12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
mysql> select *, avg(num) avg_num from score group by student_id having avg_num < 85;
13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select * from score where course_id in (select cid from course where cname = '生物' );
mysql> select sname, num from student inner join (select * from score where course_id in (select cid from course where cname = '生物' ) having num > 60) as s on s.student_id = student.sid;
14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select * from select * from score where course_id = 3 having num > 80;
15、求选了课程的学生人数
select count(sid) from (select distinct student_id from score ) as sc
inner join
(select * from student ) as st
on sc.student_id = st.sid;
16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select sname, max_num from student
inner join (select student_id,max(num) max_num,course_id from score where course_id in (select tid from teacher where tname = '刘海燕老师')) as max_sc on student.sid = max_sc.student_id;
17、查询各个课程及相应的选修人数；
select count(student_id) choose_course from score group by course_id;
18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select * , count(student_id) from score group by num;
19、查询每门课程成绩最好的前两名；
20、检索至少选修两门课程的学生学号；
select *,count(student_id) choose_course from score group by student_id having choose_course>2;
21、查询全部学生都选修的课程的课程号和课程名；
select *,group_concat(course_id) from score group by student_id;
22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；(做到学过的,还有没学过的)
select * from (select distinct student_id from score group by student_id) as st
left join
(select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '刘海燕老师'))) as tt
on st.student_id = tt.student_id;
23、查询两门以上不及格课程的同学的学号及其平均成绩；(不会)
select *, count(num) from score group by student_id having num<60;
24、检索“004”课程分数小于60，按分数降序排列的同学学号；#4课程没了,用1
select * from score where course_id = 1 having num < 60 order by num desc;
25、删除“002”同学的“001”课程的成绩；
select * from score where student_id = 2 and course_id = 1;
delete from score where student_id = 2 and course_id = 1;
(删除固定的字段)