MySQL常用练习题

#创建数据库EXAM

CREATE DATABASE EXAM;

#使用数据库

USE EXAM;

#创建表STUDENT

CREATE TABLE STUDENT

(SNO VARCHAR(3) NOT NULL,

SNAME VARCHAR(4) NOT NULL,

SSEX VARCHAR(2) NOT NULL,

SBIRTHDAY DATETIME,

CLASS VARCHAR(5))ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建表COURSE

CREATE TABLE COURSE

(CNO VARCHAR(5) NOT NULL,

CNAME VARCHAR(10) NOT NULL,

TNO VARCHAR(10) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建表SCORE

CREATE TABLE SCORE

(SNO VARCHAR(3) NOT NULL,

CNO VARCHAR(5) NOT NULL,

DEGREE NUMERIC(10, 1) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建表TEACHER

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)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#向表中添加数据

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('108','曾华','男','1977-09-01','95033');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('105','匡明','男','1975-10-02','95031');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('107','王丽','女','1976-01-23','95033');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('101','李军','男','1976-02-20','95033');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('109','王芳','女','1975-02-10','95031');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('103','陆君','男','1974-06-03','95031');

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105','计算机导论','825');

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245','操作系统','804');

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166','数据电路','856');

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888','高等数学','100');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('103','3-245','86');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('105','3-245','75');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('109','3-245','68');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('103','3-105','92');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('105','3-105','88');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('109','3-105','76');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('101','3-105','64');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('107','3-105','91');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('108','3-105','78');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('101','6-166','85');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('107','6-106','79');

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES ('108','6-166','81');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES ('804','李诚','男','1958-12-02','副教授','计算机系');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES ('856','张旭','男','1969-03-12','讲师','电子工程系');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES ('825','王萍','女','1972-05-05','助教','计算机系');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES ('831','刘冰','女','1977-08-14','助教','电子工程系');

完成如下操作：

1、 查询STUDENT表中的所有记录的SNAME、SSEX和CLASS列。

2、 查询教师所有的单位即不重复的DEPART列。

3、 查询STUDENT表的所有记录。

4、 查询SCORE表中成绩在60到80之间的所有记录。

5、 查询SCORE表中成绩为85，86或88的记录。

6、 查询STUDENT表中“95031”班或性别为“女”的同学记录。

7、 以CLASS降序查询STUDENT表的所有记录。

8、 以CNO升序、DEGREE降序查询SCORE表的所有记录。

9、 查询“95031”班的学生人数。

10、查询SCORE表中的最高分的学生学号和课程号。

11、查询‘3-105’号课程的平均分。

12、查询SCORE表中至少有5名学生选修的并以3开头的课程的平均分数。

13、查询最低分大于70，最高分小于90的SNO列。

14、查询所有学生的SNAME、CNO和DEGREE列。

-15、查询所有学生的SNO、CNAME和DEGREE列。

--16、查询所有学生的SNAME、CNAME和DEGREE列。

--17、查询“95033”班所选课程的平均分。

--18、假设使用如下命令建立了一个GRADE表：

CREATE TABLE GRADE

(LOW NUMERIC(3,0),

UPP NUMERIC(3),

RANK CHAR(1))ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO GRADE VALUE(90,100,'A');

INSERT INTO GRADE VALUE(80,89,'B');

INSERT INTO GRADE VALUE(70,79,'C');

INSERT INTO GRADE VALUE(60,69,'D');

INSERT INTO GRADE VALUE(0,59,'E');

--现查询所有同学的SNO、CNO和RANK列。

--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

--20、查询SCORE中选学一门以上课程的同学中分数为非最高分成绩的记录。

--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

--22、查询和学号为108的同学同年出生的所有学生的SNO、SNAME和SBIRTHDAY列。

--23、查询“张旭“教师任课的学生成绩。

--24、查询选修某课程的同学人数多于5人的教师姓名。

--25、查询95033班和95031班全体学生的记录。

--26、查询存在有85分以上成绩的课程CNO。

--27、查询出“计算机系“教师所教课程的成绩表。

--28、查询“计算机系”与“电子工程系“不同职称的教师的TNAME和PROF。

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的CNO、SNO和DEGREE,并按DEGREE从高到低次序排序。

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的CNO、SNO和DEGREE。

--31、查询所有教师和同学的NAME、SEX和BIRTHDAY。

--32、查询所有“女”教师和“女”同学的NAME、SEX和BIRTHDAY。

--33、查询成绩比该课程平均成绩低的同学的成绩表。

--34、查询所有任课教师的TNAME和DEPART。

--35、查询所有未讲课的教师的TNAME和DEPART。

--36、查询至少有2名男生的班号。

--37、查询STUDENT表中不姓“王”的同学记录。

--38、查询STUDENT表中每个学生的姓名和年龄。

--39、查询STUDENT表中最大和最小的SBIRTHDAY日期值。

--40、以班号和年龄从大到小的顺序查询STUDENT表中的全部记录。

--41、查询“男”教师及其所上的课程。

--42、查询最高分同学的SNO、CNO和DEGREE列。

--43、查询和“李军”同性别的所有同学的SNAME。

--44、查询和“李军”同性别并同班的同学SNAME。

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

参考答案：

use exam;

select * from student;

select * from teacher;

select * from course;

select * from score;

/*

1、 查询STUDENT表中的所有记录的SNAME、SSEX和CLASS列。

*/

select sname,ssex,class from student;

/*

2、 查询教师所有的单位即不重复的DEPART列。

*/

select distinct depart from teacher;

/*

3、 查询STUDENT表的所有记录。

*/

select * from student;

/*

4、 查询SCORE表中成绩在60到80之间的所有记录。

*/

select * from score where degree>60 and degree<80;

select * from score where degree between 60 and 80;

/*

5、 查询SCORE表中成绩为85，86或88的记录。

*/

select * from score where degree = 85 or degree=86 or degree=88;

select * from score where degree in (85,86,88);

select * from score where 1=1

/*

6、 查询STUDENT表中“95031”班或性别为“女”的同学记录。

*/

select * from student where class = '95031' or ssex = '女'

/*

7、 以CLASS降序查询STUDENT表的所有记录。

*/

select * from student order by class desc;

/*

8、 以CNO升序、DEGREE降序查询SCORE表的所有记录。

*/

select * from score order by cno asc,degree desc;

/*

9、 查询“95031”班的学生人数。

*/

select count(*) as '95031班的人数为' from student where class='95031'

/*

10、查询SCORE表中的最高分的学生学号和课程号。

*/

select sno,cno from score where degree = (select max(degree) from score);

/*

11、查询‘3-105’号课程的平均分。

*/

select avg(degree) from score where cno='3-105';

/*

12、查询SCORE表中至少有5名学生选修的并以3开头的课程的平均分数。

*/

select avg(degree) from score where cno=(select cno from score group by cno having count(sno)>=5 and cno like '3%')

/*

13、查询最低分大于70，最高分小于90的SNO列。

*/

select * from score;

select sno from score group by sno having min(degree)>70 and max(degree)<90;

--14、查询所有学生的SNAME、CNO和DEGREE列。

SELECT STUDENT.SNAME,SCORE.CNO,SCORE.DEGREE FROM STUDENT,SCORE WHERE STUDENT.SNO=SCORE.SNO;

--15、查询所有学生的SNO、CNAME和DEGREE列。

SELECT T1.SNO,T2.CNAME,T1.DEGREE FROM SCORE T1,COURSE T2 WHERE T1.CNO=T2.CNO;

--16、查询所有学生的SNAME、CNAME和DEGREE列。

SELECT T1.SNAME,T2.CNAME,T3.DEGREE FROM STUDENT T1,COURSE T2,SCORE T3 WHERE T1.SNO=T3.SNO AND T3.CNO=T2.CNO;

--17、查询“95033”班所选课程的平均分。

SELECT T2.CNO,AVG(T2.DEGREE) AS '平均分' FROM STUDENT T1,SCORE T2 WHERE T1.SNO=T2.SNO AND T1.CLASS='95033' GROUP BY T2.CNO;

--18、假设使用如下命令建立了一个GRADE表：

CREATE TABLE GRADE

(LOW NUMERIC(3,0),

UPP NUMERIC(3),

RANK CHAR(1))ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO GRADE VALUE(90,100,'A');

INSERT INTO GRADE VALUE(80,89,'B');

INSERT INTO GRADE VALUE(70,79,'C');

INSERT INTO GRADE VALUE(60,69,'D');

INSERT INTO GRADE VALUE(0,59,'E');

--现查询所有同学的SNO、CNO和RANK列。

SELECT SNO,CNO,RANK FROM SCORE,GRADE WHERE DEGREE BETWEEN LOW AND UPP ORDER BY RANK;

--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT T1.CNO,T1.SNO,T1.DEGREE FROM SCORE T1,SCORE T2 WHERE T1.CNO='3-105' AND T1.DEGREE>T2.DEGREE AND T2.SNO='109'AND T2.CNO='3-105';

--20、查询SCORE中选学一门以上课程的同学中分数为非最高分成绩的记录。

SELECT * FROM SCORE T1 WHERE T1.DEGREE<(SELECT MAX(T2.DEGREE) FROM SCORE T2 WHERE T1.CNO=T2.CNO);

--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT T1.CNO,T1.SNO,T1.DEGREE FROM SCORE T1,SCORE T2 WHERE T1.DEGREE>T2.DEGREE AND T2.SNO='109'AND T2.CNO='3-105';

--或

SELECT CNO,SNO,DEGREE FROM SCORE WHERE DEGREE>(SELECT DEGREE FROM SCORE WHERE SNO='109' AND CNO='3-105');

--22、查询和学号为108的同学同年出生的所有学生的SNO、SNAME和SBIRTHDAY列。

SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT WHERE DATE_FORMAT(SBIRTHDAY,'%Y')=(SELECT DATE_FORMAT(SBIRTHDAY,'%Y') FROM STUDENT WHERE SNO='108');

--23、查询“张旭“教师任课的学生成绩。

SELECT CNO,SNO,DEGREE FROM SCORE WHERE CNO=(SELECT T1.CNO FROM COURSE T1,TEACHER T2 WHERE T1.TNO=T2.TNO AND T2.TNAME='张旭');

--24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT TNAME FROM TEACHER WHERE TNO IN(SELECT T1.TNO FROM COURSE T1,SCORE T2 WHERE T1.CNO=T2.CNO GROUP BY T1.TNO HAVING COUNT(T1.TNO)>5);

--25、查询95033班和95031班全体学生的记录。

SELECT * FROM STUDENT WHERE CLASS IN('95033','95031');

--26、查询存在有85分以上成绩的课程CNO。

SELECT DISTINCT CNO FROM SCORE WHERE DEGREE IN(SELECT DEGREE FROM SCORE WHERE DEGREE>85);

--27、查询出“计算机系“教师所教课程的成绩表。

SELECT * FROM SCORE WHERE CNO IN(SELECT T1.CNO FROM COURSE T1,TEACHER T2 WHERE T2.TNO=T1.TNO AND T2.DEPART='计算机系');

--28、查询“计算机系”与“电子工程系“不同职称的教师的TNAME和PROF。

SELECT TNAME,PROF FROM TEACHER WHERE DEPART='计算机系' AND PROF NOT IN(SELECT PROF FROM TEACHER WHERE DEPART='电子工程系');

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的CNO、SNO和DEGREE,并按DEGREE从高到低次序排序。

SELECT * FROM SCORE WHERE CNO='3-105' AND DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER BY DEGREE desc;

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的CNO、SNO和DEGREE。

SELECT * FROM SCORE WHERE CNO='3-105' AND DEGREE>ALL(SELECT DEGREE FROM SCORE WHERE CNO='3-245');

--31、查询所有教师和同学的NAME、SEX和BIRTHDAY。

SELECT TNAME,TSEX,TBIRTHDAY FROM TEACHER UNION SELECT SNAME,SSEX,SBIRTHDAY FROM STUDENT;

--32、查询所有“女”教师和“女”同学的NAME、SEX和BIRTHDAY。

SELECT TNAME,TSEX,TBIRTHDAY FROM TEACHER WHERE TSEX='女' UNION SELECT SNAME,SSEX,SBIRTHDAY FROM STUDENT WHERE SSEX='女';

--33、查询成绩比该课程平均成绩低的同学的成绩表。

SELECT * FROM SCORE T1 WHERE T1.DEGREE<(SELECT AVG(T2.DEGREE) FROM SCORE T2 WHERE T1.CNO=T2.CNO);

--34、查询所有任课教师的TNAME和DEPART。

SELECT T1.TNAME,T1.DEPART FROM TEACHER T1 WHERE EXISTS(SELECT * FROM COURSE T2 WHERE T1.TNO=T2.TNO);

--35、查询所有未讲课的教师的TNAME和DEPART。

SELECT T1.TNAME,T1.DEPART FROM TEACHER T1 WHERE NOT EXISTS(SELECT * FROM COURSE T2 WHERE T1.TNO=T2.TNO);

--36、查询至少有2名男生的班号。

SELECT CLASS FROM STUDENT WHERE SSEX='男'GROUP BY CLASS HAVING COUNT(*)>=2;

--37、查询STUDENT表中不姓“王”的同学记录。

SELECT * FROM STUDENT WHERE SNAME NOT LIKE'王_';

--38、查询STUDENT表中每个学生的姓名和年龄。

SELECT SNAME AS '姓名',(DATE_FORMAT(now(),'%Y')-DATE_FORMAT(SBIRTHDAY,'%Y')) AS '年龄' FROM STUDENT;

--39、查询STUDENT表中最大和最小的SBIRTHDAY日期值。

SELECT SNAME,SBIRTHDAY AS '最大' FROM STUDENT WHERE SBIRTHDAY=(SELECT MIN(SBIRTHDAY) FROM STUDENT)UNION SELECT SNAME,SBIRTHDAY AS '最小' FROM STUDENT WHERE SBIRTHDAY=(SELECT MAX(SBIRTHDAY) FROM STUDENT);

--40、以班号和年龄从大到小的顺序查询STUDENT表中的全部记录。

SELECT CLASS,SNAME,SBIRTHDAY FROM STUDENT ORDER BY CLASS DESC,SBIRTHDAY DESC;

--41、查询“男”教师及其所上的课程。

SELECT T1.TNAME,T2.CNAME FROM TEACHER T1,COURSE T2 WHERE T1.TNO=T2.TNO AND T1.TSEX='男';

--42、查询最高分同学的SNO、CNO和DEGREE列。

SELECT * FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);

--43、查询和“李军”同性别的所有同学的SNAME。

SELECT SNAME FROM STUDENT WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='李军');

--44、查询和“李军”同性别并同班的同学SNAME。

SELECT SNAME FROM STUDENT WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='李军') AND CLASS=(SELECT CLASS FROM STUDENT WHERE SNAME='李军');

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

SELECT * FROM SCORE WHERE SNO IN(SELECT SNO FROM STUDENT WHERE SSEX='男') AND CNO=(SELECT CNO FROM COURSE WHERE CNAME='计算机导论');