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);

J_TEACHER

J_STUDENT J_COURSE J_SCORE

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

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

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

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

4、查询姓“李”的学生的个数。

5、查询没学过“叶平”老师课的同学的学号、姓名。

6、查询同时学过课程1和课程2的同学的学号、姓名。

7、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名。

8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名。

9、查询所有课程成绩小于60分的同学的学号、姓名。

11、查询没有学全所有课的同学的学号、姓名

18、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩。

19、查询不同课程成绩相同的学生的学号、课程号、学生成绩。

20、查询每门课程成绩最好的前两名的学生ID