MySQL练习题 （练习表+题目+答案）

mysql练习题 （表+题目+答案）

一、创建所需要练习的表

CREATE TABLE J_TEACHER (
tno int NOT NULL PRIMARY KEY,
tname varchar(20) NOT NULL
);

INSERT INTO J_TEACHER(tno,tname)VALUES(1,‘张老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(2,‘王老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(3,‘李老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(4,‘赵老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(5,‘刘老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(6,‘向老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(7,‘李文静’);
INSERT INTO J_TEACHER(tno,tname)VALUES(8,‘叶平’);

CREATE TABLE J_STUDENT(
sno int NOT NULL PRIMARY KEY,
sname varchar(20) NOT NULL,
sage datetime NOT NULL,
ssex char(2) NOT NULL
);

INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(1,‘张三’,‘1980-1-23’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(2,‘李四’,‘1982-12-12’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(3,‘张飒’,‘1981-9-9’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(4,‘莉莉’,‘1983-3-23’,‘女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(5,‘王弼’,‘1982-6-21’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(6,‘王丽’,‘1984-10-10’,‘女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(7,‘刘香’,‘1980-12-22’,‘女’);

CREATE TABLE J_COURSE(
cno int NOT NULL PRIMARY KEY,
cname varchar(20) NOT NULL,
tno int NOT NULL
);

insert into J_COURSE(cno,cname,tno) values(1,‘企业管理’,3);
insert into J_COURSE(cno,cname,tno) values(2,‘马克思’,1);
insert into J_COURSE(cno,cname,tno) values(3,‘UML’,2);
insert into J_COURSE(cno,cname,tno) values(4,‘数据库’,5);
insert into J_COURSE(cno,cname,tno) values(5,‘物理’,8);

CREATE TABLE J_SCORE(
sno int NOT NULL,
cno int NOT NULL,
score int NOT NULL
);

ALTER TABLE J_SCORE ADD CONSTRAINT FK_SCORE_course FOREIGN KEY(cno)
REFERENCES J_COURSE (cno);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_score_student FOREIGN KEY(sno)
REFERENCES J_STUDENT (sno);

INSERT INTO J_SCORE(sno,cno,score)VALUES(1,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,2,86);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,3,83);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,4,89);

INSERT INTO J_SCORE(sno,cno,score)VALUES(2,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,3,43);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,4,59);

INSERT INTO J_SCORE(sno,cno,score)VALUES(3,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,3,73);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,4,69);

INSERT INTO J_SCORE(sno,cno,score)VALUES(4,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,3,88);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,4,99);

INSERT INTO J_SCORE(sno,cno,score)VALUES(5,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,3,98);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,4,99);

INSERT INTO J_SCORE(sno,cno,score)VALUES(6,1,70);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,2,66);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,3,58);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,4,79);

INSERT INTO J_SCORE(sno,cno,score)VALUES(7,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,2,76);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,3,68);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,4,59);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,5,89);

创建后结果（作者用的mysql）

J_TEACHER

J_STUDENT

J_COURSE

J_SCORE

二、题目和过程答案

作者用的mysql 不同数据库有些语法可能会不同，请注意。

1、查询课程1的成绩比课程2的成绩 高 的所有学生的学号。

select a.sno
from j_score as a JOIN j_score as b 
on a.sno=b.sno
where a.cno=1 and b.cno=2 and a.score>b.score]

2、查询平均成绩大于60分的同学的学号和平均成绩。

SELECT a.sno,AVG(a.score)as '平均成绩'
from j_score as a
group by a.sno
having avg(a.score)>60

3、查询所有同学的学号、姓名、选课数、总成绩。

select a.sno,a.sname,count(b.cno)as'选课数',sum(b.score)as'总成绩'
from j_student as a join j_score as b 
on a.sno