--创建并使用数据库------------*/
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);
- 在score表中插入一条记录(101,’9-888’,50)
- 将score表中学号为105,课程号为’3-105’的记录成绩改为60
- 查询Score表中成绩在60到80之间的所有记录,并按cno升序,degree降序排列
- 查询平均成绩大于60分的学生的学号和平均成绩
- 查询出只选修了两门课程的全部学生的学号和姓名
- 查询姓“张”的老师的个数
- 查询每门课程号的最高分和最低分,显示课程号、最高分和最低分
- 查询至少有一门课与学号为“101”的学生所学课程相同的学生的学号和姓名
- 查询学过“王萍”老师课的学生的学号、姓名
- 查询至少有2名男生的班号
- 创建一个存储过程,根据给定的课程号查询显示该课程的最高分、最低分及平均成绩
答案如下
- 在score表中插入一条记录(101,’9-888’,50)
insert into score (sno,cno,degree) value('101','9-888','50');
- 将score表中学号为105,课程号为’3-105’的记录成绩改为60
update score set degree=60 where cno='3-105' and sno='105';
- 查询Score表中成绩在60到80之间的所有记录,并按cno升序,degree降序排列
select * from score where degree between 60 and 80 order by cno ,degree desc;
- 查询平均成绩大于60分的学生的学号和平均成绩
select sno,avg(DEGREE) as avger from score group by sno having avg(DEGREE)>60;
- 查询出只选修了两门课程的全部学生的学号和姓名
select student.sno, student.sname from student inner join score on student.sno = score.sno group by sno having count(cno)=2;
- 查询姓“张”的老师的个数
select count(tname) from teacher where tname like '张%';
- 查询每门课程号的最高分和最低分,显示课程号、最高分和最低分
select cno,max(degree),min(degree) from score group by cno;
- 查询至少有一门课与学号为“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');
- 查询学过“王萍”老师课的学生的学号、姓名
select student.sno,student.sname from student,teacher where teacher.tname='王萍';
- 查询至少有2名男生的班号
select Class FROM student where Ssex='男' group by Class having COUNT(*)>1;
- 创建一个存储过程,根据给定的课程号查询显示该课程的最高分、最低分及平均成绩
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