数据库基本操作

1、实验一一、创建数据库stu1 .直接创建（创建后直接删除）利用“向导”创建（创建后删除，用drop语句删除）用 create database 创建二、用create语句创建如下五张数据表，用insert语句插入表格数据。1）表 STUDENTSNOSNAMESSEXSAGECLASS108曾华男1995033105匡明男1795031107王丽女1695033101李军男1995033109王芳女IS95031103陆君男20950312）表 COURSEenocn：rTLetno3-105计算机导论8253-245操作系统8046-166数据电路8569-888高等数学8313）表SCOR

2、Esnoenodegree1013-105641016-166851033-105921033-245861053-1058S1053-245751073-105911079-888791083-105781086-166811093-105761093-245684）表TEACHERIMO7?JAMETSEXTAGEPROFDEPART804李诚男35副教授计耸机系856张旭男30讲师电子工程系825王萍女27助教计耸机系831刘冰女25助教电子工程系5)表GARDElowupprank059E6069D7079C8089B90100A三、用select语句完成如下查询。1、查询Studen

3、t表中的所有记录的Sname、Ssex和Class歹。select sname,ssex,classfrom student2、查询教师所有的单位即不重复的Depart列。select distinct departfrom teacher3、查询Student表的所有记录。select *from student4、查询Score表中成绩在60到80之间的所有记录。select *from scorewhere degree between 60 and 805、查询Score表中成绩为85, 86或88的记录。select *from scorewhere degree in (85,86

4、,88)6、查询Student表中“95031 ”班或性别为女”的同学记录。select *from studentwhere class =95031or ssex=女7、以Class降序查询Student表的所有记录。select *from studentorder by class desc8、以Cno升序、Degree降序查询Score表的所有记录。：select *from scoreorder by cno asc,degree desc9、查询“95031”班的学生人数。select count(distinct sno)from studentwhere class =950

5、31 select avg(degree)from scorewhere cno like 3%group by cno having count(sno) = 510、查询Score表中的最高分的学生学号和课程号。select sno,cnofrom scorewhere degree =(select max(degree)from score)11、查询3-105号课程的平均分。select avg(degree)均分from scorewhere cno=3-10512、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select avg(degree)均分from

6、scorewhere cno like 3%group by cno having count(sno)=513、查询最低分大于70，最高分小于90的Sno列。select snofrom scoregroup by sno having max(degree)7014、查询所有学生的Sname、Cno和Degree列。select sname,cno,degreefrom student,scorewhere student.sno=score.sno15、查询所有学生的Sno、Cname和Degree列。select sno,cname,degreefrom score,coursewhe

7、re o=o16、查询所有学生的Sname、Cname和Degree列。select sname,cname,degreefrom student,course,scorewhere student.sno=score.snoand o=o17、查询“95033班所选课程的平均分。select avg(degree)均分from scorewhere sno in(select snofrom studentwhere class=95033)18、查询选修“3-105”课程且成绩高于、109”号同学所有成绩的所有同学的记录。select *from scorewhere cno=3-105and degree(select max(degree)from scorewhere sno=109)19、查询score中选学一门以上课程的同学中分数为非最高分成