MySQL期末测试题数据库LongUP的博客-

--创建并使用数据库------------*/ create database 50q; use 50q; /*-------------------建表-------------------------*/  CREATE TABLE STUDENT  (   SNO       VARCHAR(3) NOT NULL,     SNAME     VARCHAR(4) NOT NULL,   SSEX      VARCHAR(2) NOT NULL,    SBIRTHDAY DATETIME,   CLASS     VARCHAR(5)   );  CREATE TABLE COURSE  (    CNO   VARCHAR(5)  NOT NULL,    CNAME VARCHAR(10) NOT NULL,   TNO   VARCHAR(3) NOT NULL   );  CREATE TABLE SCORE    (    SNO    VARCHAR(3)     NOT NULL,    CNO    VARCHAR(5)     NOT NULL,    DEGREE NUMERIC(10, 1) NOT NULL    ) ;    CREATE TABLE TEACHER    (    TNO       VARCHAR(3)  NOT NULL,     TNAME     VARCHAR(4)  NOT NULL,     TSEX      VARCHAR(2)  NOT NULL,     TBIRTHDAY DATETIME    NOT NULL,     PROF      VARCHAR(6),     DEPART    VARCHAR(10) NOT NULL    ); /*---------------添加主键--------------*/   ALTER TABLE STUDENT ADD PRIMARY KEY (SNO);   ALTER TABLE SCORE   ADD PRIMARY KEY (SNO,CNO);    ALTER TABLE COURSE  ADD PRIMARY KEY (CNO);    ALTER TABLE TEACHER ADD PRIMARY KEY (TNO); #--------主键在两张table中的数据类型须一致-------*/   ALTER TABLE SCORE   ADD CONSTRAINT FK_SCORE_STUDENT  FOREIGN KEY (SNO) REFERENCES STUDENT(SNO);    ALTER TABLE SCORE   ADD CONSTRAINT FK_SCORE_COURSE   FOREIGN KEY (CNO) REFERENCES COURSE(CNO);    ALTER TABLE COURSE  ADD CONSTRAINT FK_COURSE_TEACHER FOREIGN KEY (TNO) REFERENCES TEACHER(TNO); /*---------------输入记录-------------------*/  INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)     VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033),           (105 ,'匡明' ,'男' ,'1975-10-02',95031),            (107 ,'王丽' ,'女' ,'1976-01-23',95033),           (101 ,'李军' ,'男' ,'1976-02-20',95033),             (109 ,'王芳' ,'女' ,'1975-02-10',95031),           (103 ,'陆君' ,'男' ,'1974-06-03',95031);  INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)     VALUES (804,'李诚','男','1958-12-02','副教授','计算机系'),           (856,'张旭','男','1969-03-12','讲师','电子工程系'),           (825,'王萍','女','1972-05-05','助教','计算机系'),            (831,'刘冰','女','1977-08-14','助教','电子工程系');  INSERT INTO COURSE(CNO,CNAME,TNO)        VALUES ('3-105' ,'计算机导论',825),                 ('3-245' ,'操作系统' ,804),                ('6-166' ,'数据电路' ,856),                 ('9-888' ,'高等数学' ,831);   INSERT INTO SCORE(SNO,CNO,DEGREE)         VALUES (103,'3-245',86),(105,'3-245',75),                 (109,'3-245',68),(103,'3-105',92),                  (105,'3-105',58),(109,'3-105',46),                 (101,'3-105',64),(107,'3-105',91),                  (108,'3-105',78),(101,'6-166',85),                  (107,'6-166',79),(108,'6-166',81);  

1. 在score表中插入一条记录（101，’9-888’,50）
2. 将score表中学号为105，课程号为’3-105’的记录成绩改为60
3. 查询Score表中成绩在60到80之间的所有记录，并按cno升序，degree降序排列
4. 查询平均成绩大于60分的学生的学号和平均成绩
5. 查询出只选修了两门课程的全部学生的学号和姓名
6. 查询姓“张”的老师的个数
7. 查询每门课程号的最高分和最低分，显示课程号、最高分和最低分
8. 查询至少有一门课与学号为“101”的学生所学课程相同的学生的学号和姓名
9. 查询学过“王萍”老师课的学生的学号、姓名
10. 查询至少有2名男生的班号
11. 创建一个存储过程，根据给定的课程号查询显示该课程的最高分、最低分及平均成绩

答案如下

1. 在score表中插入一条记录（101，’9-888’,50）
   insert into score (sno,cno,degree) value('101','9-888','50');
2. 将score表中学号为105，课程号为’3-105’的记录成绩改为60
   update score set degree=60 where cno='3-105' and sno='105';
3. 查询Score表中成绩在60到80之间的所有记录，并按cno升序，degree降序排列
   select * from score where degree between 60 and 80 order by cno ,degree desc;
4. 查询平均成绩大于60分的学生的学号和平均成绩
   select sno,avg(DEGREE) as avger from score group by sno having avg(DEGREE)>60;
5. 查询出只选修了两门课程的全部学生的学号和姓名
   select student.sno, student.sname from student inner join score on student.sno = score.sno group by sno having count(cno)=2;
6. 查询姓“张”的老师的个数
   select count(tname) from teacher where tname like '张%';
7. 查询每门课程号的最高分和最低分，显示课程号、最高分和最低分
   select cno,max(degree),min(degree) from score group by cno;
8. 查询至少有一门课与学号为“101”的学生所学课程相同的学生的学号和姓名
   select distinct score.sno,sname from score inner join student on score.sno=student.sno where cno in (select cno from score where sno='101');
9. 查询学过“王萍”老师课的学生的学号、姓名
   select student.sno,student.sname from student,teacher where teacher.tname='王萍';
10. 查询至少有2名男生的班号
    select Class FROM student where Ssex='男' group by Class having COUNT(*)>1;
11. 创建一个存储过程，根据给定的课程号查询显示该课程的最高分、最低分及平均成绩
    CREATE DEFINER=`root`@`localhost` PROCEDURE `pp5`(in cn varchar(5),out av int,out ma int) BEGIN select round(avg(degree)),round(max(degree)) into av,ma from score where cno=cn; END