studentsno 学号sname 姓名ssex 性别sage 年龄sdept 所在系20260701小李男23理学20260702小刘女22文学20260703小王男18工学20260704小周男19理学scsno 学号cno 课程号grade 成绩20260701182202607012912026070138920260703298202607033962026070419520260704399coursecno 课程号cname 课程名ccredit 学分1高等数学32C语言23统计学34英语一35离散数学26操作系统27数据结构2一、单表查询1.查询student表中所有年龄在20以下的学生姓名和年龄select sname,sage from student where sage20;2.查询sc表中成绩大于等于85分且小于等于90分的学号和成绩select sno,grade from sc where grade85 and grade90;3.查询student表中工学系和理学系学生的学号和姓名select sno,sname from student where sdept工学 or sdept理学;或者select sno,sname from student where sdept in (工学,理学);4.查询sc表中选修了课程号2或3的学生学号select sno from sc where cno in (2,3);5.去除4中的重复数据select distinct sno from sc where cno in (2,3);6.查询信息course表中高等数学、操作系统、C语言三门课程的学分情况select cname,ccredit from course where cname in (高等数学,操作系统,C语言);7.查询工学系年龄在20岁以下学生的student表信息select * from student where sage20 and sdept工学;8.查询以“数”开头的课程名和对应课程的学分select cname,ccredit from course where cname like 数%;9.向student表插入一条新记录(20260630小刘同学女22经管);insert into student values (20260630,小刘同学,女,22,经管);10.查询student表中姓‘小’同学的信息select * from student where sname like 小%;select * from student where sname like 小_;11.统计姓“小”的学生人数select count(sno) from student where sname like 小%;12.查询选修课程号为3的学生的学号和成绩查询结果按成绩降序排序select sno,grade from sc where cno3 order by grade desc;13.查询学生总人数select count(*) from student;14.计算选修1号课程学生的平均成绩select avg(grade) from sc where cno1;15.查询选修1号课程的学生最高分数select max(grade) from sc where cno1;16.查询选修3门及以上课程的学生学号select sno from sc group by sno having count(cno)3;二、多表查询1.用自然连接的方式查询小李同学选修的课程号、课程名、成绩select sname,sc.cno,cname,grade from student natural join sc natural join course where sname小李;2.利用左连接方式查询课程表和选修表所有记录的前3条记录select * from sc left join course on sc.cnocourse.cno limit 0,3;
