数据库作为软件系统数据的主要存取与操作中心，广泛应用于企业当中
在企业中常用的数据库管理系统有 ORACLE、MS SQL SERVER、MySQL等
其中以免费的 MySQL 最多，特别在中小型互联网公司里
因此，本文的数据库操作是基于 MySQL 数据库系统下的，我们收集了近 5 年里特斯汀学员在面试中碰到的高频出现的数据库操作及命令，供同学学习和参考，同时这些操作和命令也是企业日常工作中需要掌握的，因此希望大家尽量背下来
1、数据表1.1 学生表Student(Snum,Sname,Sage,Ssex)表字段解释：Snum：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别1.2 课程表Course(Cnum, Cname, Tnum)表字段解释：Cnum：课程编号；Cname：课程名字；Tnum：教师编号1.3 成绩表SC(Snum. Cnum,score)成绩表表字段解释：Snum：学号；Cum：课程编号；score：成绩2、查询平均成绩大于 60 分的同学的学号和平均成绩？参考答案：select Snum,avg(score) from sc group by Snum having avg(score)>60;3、查询姓“李”的老师的个数？参考答案：select count(distinct(Tname)) from Teacher where Tname like '李%';4、查询所有同学的学号、姓名、选课数、总成绩？参考答案：select Student.Snum,Student.Sname,count(SC.Cnum),sum(score)from Student left Outer join SC on Student.Snum=SC.Snumgroup by Student.Snum,Sname;5、查询没学过“锅锅”老师课的同学的学号、姓名？参考答案：select Student.Snum,Student.Sname from Student where Snum not in (select distinct( SC.Snum) from SC,Course,Teacher where SC.Cnum=Course.Cnum and Teacher.Tnum=Course.Tnum and Teacher.Tname='锅锅);6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名？参考答案：Select Student.Snum,Student.Sname from Student,SC where Student.Snum=SC.Snum and SC.Cnum='001'and exists( Select from SC as SC_2 where SC_2.Snum=SC.Snum and SC_2.Cnum='002');7、查询学过“锅锅”老师所教的所有课的同学的学号、姓名?参考答案：select Snum,Sname from Student where Snum in (select Snum fromSC ,Course ,Teacher where SC.Cnum=Course.Cnum and Teacher.Tnum=Course.Tnum and Teacher.Tname='锅锅' group by Snum having count(SC.Cnum)=(select count(Cnum) from Course,Teacher where Teacher.Tnum=Course.Tnum and Tname='锅锅'))8、查询课程编号“002"的成绩比课程编号”001”课程低的所有同学的学号、姓名?参考答案：Select Sum,Sname from (select Student.Snum,Student.Sname,score , (select score t=from SC SC_2 where SC_2.Snum=Sudet.Snum and SC_2.Cnum='002') score2 from Student,SC where Student.Snum=SC.Snum and Cnum='001') S_2 where score29、 查询所有课程成绩小于 60 分的同学的学号、姓名？参考答案：select Snum,Sname from Student where Snum not in (select Sudent.Snum from Student,SC where S.Snum=SC.Snum and score>60);10、查询没有学全所有课的同学的学号、姓名?参考答案：select Student.Snum,Student.Sname from Student,SC where Student.Snum=SC.Snum group by Student.Sname,Student.Sname having count(Cnum)<(select count(Cnum)from Course);11、查询至少有一门课与学号为“101”的同学所学相同的同学的学号和姓名?参考答案：select Snum,Sname from Student,SC where Student.Snum-SC.Snum and Cnum in select Cnum from SC where Snum='1001';12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名?参考答案：select distinct SC.Snum,Sname from Student,SC where Student.Snum=SC.Snum and Cnum in (select Cnum from SC where Snum='001');13、把“SC"表中“锅锅”老师教的课的成绩都更改为此课程的平均成绩?参考答案：update SC set score=(select avg(SC_ 2.score) from SC SC_2 where SC_ 2.Cnum=SC.Cnum ) from Course,Teacher where Course.Cnum=SC.Cnum and Course.Tnum=Teacher.Tnum and Teacher.Tname='锅锅');14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名?参考答案：select Snum from SC where Cnum in (select Cnum from SC where Snum='1002') group by Snum having count()=(select count() from SC where Snum='1002');15、删除学习“锅锅”老师课的 SC 表记录?参考答案：Delect SC from course ,Teacher where Course.Cnum=SC.Cnum and Course.Tnum= Teacher.Tnum and Tname='锅锅';16、向SC表中插入一些记录，这些记录要求符合以下条件:没有上过编号“003" 课程的同学学号、2 号课的平均成绩?参考答案：lnsert SC select Snum,'002', (Select avg(score) from SC where Cnum='002') from Student where Snum not in (Select Snum from SC where Cnum='002');最后，如果你对软件测试感兴趣，欢迎百度搜索“特斯汀软件测试腾讯课堂”或关注公众号“特斯汀软件测试”，里面涵盖很多精彩免费视频或干货知识