数据库概述《实验报告》

根据第五版数据库概述见表如下：

 

 

创建表语句如下：

创建数据库 
 2 create table student(
    Sno int auto_increment primary key,
    Sname varchar(45),
    Ssex varchar(45),
    Sage int,
    Sdept varchar(45)
)engine = InnoDB default charset=utf8;
create table Course(
    Cno int auto_increment primary key,
    Cname varchar(45),
    Cpno int,
    Ccredit int
) engine = InnoDB default charset=utf8;

create table SC(
    Sno int ,
    Cno int ,
    Grade int,
    primary key(Sno,Cno),
    constraint c_fk foreign key(Sno)references student(Sno),
    constraint c_fk21 foreign key(Cno) references Course(Cno)
        
) engine = InnoDB default charset=utf8;

向表中插入数据：

（1）Course表插入数据：
27 SELECT * FROM Student.Course;
28 insert into Course values(1,"数据库",5,4);
29 insert into Course (Cno,Cname,Ccredit) values(2,"数学",2);
30 insert into Course values(3,"信息系统",1,4);
31 insert into Course values(4,"操作系统",6,3);
32 insert into Course values(5,"数据结构",7,4);
33 insert into Course (Cno,Cname,Ccredit) values(6,"数据处理",2);
34 insert into Course values(7,"PASCAL语言",6,4);
35 （2）SC表插入数据：
36 SELECT * FROM Student.SC;
37 insert into SC values(95001,1,92);
38 insert into SC values(95001,2,85);
39 insert into SC values(95001,3,88);
40 insert into SC values(95002,2,90);
41 insert into SC values(95002,3,85);
42 insert into SC values(95003,3,59);
43 （3）student表插入数据
44 SELECT * FROM Student.student;
45 insert into student values(95001,"李勇","男",20,"CS");
46 insert into student values(95002,"刘晨","女",21,"lS");
47 insert into student values(95003,"王敏","女",18,"MA");
48 insert into student values(95004,"张力","男",19,"lS");

 用select语句查询：

•  1.查询信息系（IS）的所有学生信息。

  1.select * from student where Sdept="lS"

• 2.查询所有姓王的学生。

  2.select Sname from Student where Sname like "王%";

• 3.查询至少选修了一门其直接先行课为5号课程的学生的姓名。

  3.select Sname from student,SC,Course where Course.Cpno="5" and SC.Cno=Course.Cno and SC.Sno=student.Sno;

• 4.查询全体学生的姓名和出生年份。

  4.select Sname,2018-Sage from student;

• 5.查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。

  5.select Sname,Grade from student ,SC where Cno='3'and SC.Sno=student.Sno order by Grade desc;

• 6.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

  6.select * from student order by Sdept asc ,Sage desc;

• 7.计算2号课程的平均成绩。

  7.select avg(Grade) avg2 from SC where SC.Cno='2';

  8.查询选修了2号课程的学生的最高成绩。

• 8.select max(Grade) from SC where SC.Cno='2';

• 9.求各个课程号及相应的选课人数。

  9.select Cno,count(Sno) from SC group by Cno;

• 10.查询至少选修了3门课程以上的学生学号。

  10.select Sno from SC group by Sno having count(Cno)>=3;

• 11.查询“数据库”的间接先行课。

  11.select Cname from Course where Cno=(select Cpno from Course where Cname='数据库');

• 12.查询平均成绩最高的学生的学号和姓名。

  12.select student.Sno,Sname from student,SC
  where SC.Sno=student.Sno and Grade in (select max(Grade) from SC);

• 13.查询数学成绩最高的学生的学号和姓名。

  13.select student.Sno,Sname from SC,student where SC.Sno=student.Sno and
  Grade in (select max(Grade) from SC,Course where SC.Cno=Course.Cno and Course.Cname="数学") ;

  14.查询出成绩最低学号最大的学生学号。

• 14.select Sno from SC where Grade in (select min(Grade) from SC) and Sno in (select max(Sno) from SC);

  15.查询成绩高于学生平均成绩的记录。

• 15.select * from SC where Grade > (select avg(Grade) from SC);

• 16.查询至少选修了1号课程和3号课程的学生学号。

  16.select Sno from SC where Sno in (select Sno from SC where Cno=1 ) and Cno=3;

• 17.查询只选修了1号课程和3号课程的学生学号。

  17.select Sno from SC where Cno = 1 and 3 and not exists (select Sno from SC where Cno=2) ;

• 18.查询没有选修1号课程的学生姓名。

  18. SELECT Sname from student where not exists 
  (select * from SC WHERE Sno=student.Sno and Cno='1');

  19.查询选修了全部课程的学生姓名。

  19.select Sname from student where Sno IN (select Sno 
  from SC group by Sno having count(*) = (select count(*) from Course ));

• 20.查询至少选修了95002所选修的全部课程的学生学号。

  20. select distinct Sno 
  from SC where Sno in(select Sno 
  from SC s1 where not exists
  (select * from SC s2 where s2.Sno='95002' and not exists
  (select * from SC s3 where s1.Sno=s3.Sno and s2.Cno=s3.Cno)));

• 21.查询没有不及格课程的学生的学号和姓名。

  21.select distinct SC.Sno,Sname from SC ,student where Grade>60 and SC.Sno =student.Sno;

• 22.查询没有不及格学生的课程的课程号和课程名。

  22.select distinct SC.Cno, Cname 
  from SC ,Course where Grade>60 and SC.Cno = Course.Cno;

• 23.建立信息系学生视图，并从视图中查询年龄最大的学生记录。

  23.create view is_student(a_Sno,a_Sname,a_Sage)as select Sno ,Sname ,Sage 
  from student
  where Sdept ='lS';
  select * from is_student where a_Sage in (select max(a_Sage) from is_student);

•  24.求每门课程学生的平均成绩

  SELECT AVG(grade)
  FROM sc
  GROUP BY cno

• 25.查询学号比刘晨大，而年龄比他小的学生姓名。

  SELECT s1.sname
  FROM student s1, student s2
  WHERE s1.sno > s2.sno
      AND s1.sage < s2.sage
      AND s2.sname = '刘晨'