mysql查询语句练习

-- 学生表

CREATE TABLE STUDENT(

sno VARCHAR(3) NOT NULL,

sname VARCHAR(4) NOT NULL,

ssex VARCHAR(2) NOT NULL,

SBIRTHDAY DATETIME,

CLASS VARCHAR(5));

 

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

 

-- 课程表

CREATE TABLE COURSE(

CNO VARCHAR(5) NOT NULL,

CNAME VARCHAR(10) NOT NULL,

TNO VARCHAR(10) NOT NULL);

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 ('6-106' ,'高等数学' ,831);

 

-- 成绩表

CREATE TABLE SCORE (

SNO VARCHAR(3) NOT NULL,

CNO VARCHAR(5) NOT NULL,

DEGREE DEC(10, 1) NOT NULL);

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

 

-- 老师表

 

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

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列。

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 BETWEEN 60 AND 80;

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

SELECT * FROM score WHERE degree IN (86,85,88);

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

SELECT * FROM student WHERE (class='95031' OR ssex='女');

SELECT * FROM student WHERE (class='95031' || ssex='女');

-- 7、 以Class降序查询Student表的所有记录。

SELECT * FROM student ORDER BY class DESC;

-- 8、 以Cno升序、Degree降序查询Score表的所有记录。

SELECT * FROM score ORDER BY cno,degree DESC;

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

SELECT COUNT(*) 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 GROUP BY cno HAVING cno LIKE '3%' AND COUNT(cno)>5;

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

SELECT sno FROM score WHERE degree BETWEEN 70 AND 90;

-- 14、查询所有学生的Sname、Cno和Degree列。

SELECT s.`sname`,c.`cno`,c.`degree` FROM student s RIGHT JOIN score c ON s.`sno`=c.`sno`;

-- 15、查询所有学生的Sno、Cname和Degree列。

SELECT s.`sno`,s.`sname`,c.`degree` FROM student s RIGHT JOIN score c ON s.`sno`=c.`sno`;

-- 16、查询所有学生的Sname、Cname和Degree列

SELECT s.`sno`,c.`cname`,sc.`degree` FROM student s RIGHT JOIN score sc ON s.`sno`=sc.`sno` LEFT JOIN course c ON sc.`cno`=c.`cno` ;

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

SELECT AVG(degree) FROM score sc RIGHT JOIN (SELECT sno FROM student WHERE class='95033') s ON sc.`sno`=s.`sno` GROUP BY sc.`cno`;

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

CREATE TABLE grade(

low INT,upp INT,rank CHAR(1));

SELECT * FROM grade;

INSERT INTO grade(low,upp,rank) VALUES(90,100,'A');

INSERT INTO grade(low,upp,rank) VALUES(80,89,'B');

INSERT INTO grade(low,upp,rank) VALUES(70,79,'C');

INSERT INTO grade(low,upp,rank) VALUES(60,69,'D');

INSERT INTO grade(low,upp,rank) VALUES(0,59,'E');

-- 现查询所有同学的Sno、Cno和rank列。Between …. And …在某个范围区间内，包含区间的两端 Order by columnName asc/desc

SELECT sc.sno,sc.cno,g.rank FROM score sc,grade g WHERE sc.`degree` BETWEEN g.low AND g.upp;

SELECT sc.sno,sc.cno,g.rank FROM score sc,grade g WHERE sc.`degree` BETWEEN g.low AND g.upp ORDER BY g.`rank`;

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

SELECT * FROM score WHERE cno = '3-105' AND degree>(SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');

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

SELECT * FROM score WHERE sno IN (SELECT sno FROM score GROUP BY sno HAVING COUNT(sno)>1) AND degree<(SELECT MAX(degree) FROM score);

SELECT * FROM score a WHERE sno IN(SELECT sno FROM score GROUP BY sno HAVING COUNT(*)>1) AND degree<(SELECT MAX(degree) FROM score b WHERE b.cno = a.cno);

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

SELECT * FROM score WHERE cno = '3-105' AND degree>(SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');

-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sno,sname,sbirthday FROM student WHERE sbirthday = (SELECT sbirthday FROM student WHERE sno = '108');

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

SELECT s.`sno`,s.`sname`,c.`cno`,sc.`degree` FROM course c RIGHT JOIN teacher t ON c.`tno`=t.`tno` LEFT JOIN score sc ON sc.`cno` = c.`cno` LEFT JOIN student s ON s.`sno` = sc.`sno` WHERE t.`tname` = '张旭';

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

SELECT t.tname FROM course c RIGHT JOIN teacher t ON c.`tno`=t.`tno` LEFT JOIN score sc ON sc.`cno` = c.`cno` GROUP BY c.`cno` HAVING COUNT(c.`cno`)>=5;

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

SELECT * FROM student WHERE class IN ('95033','95031') ORDER BY class;

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

SELECT cno FROM score WHERE degree>85 GROUP BY cno;

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

SELECT * FROM course c RIGHT JOIN score sc ON sc.`cno` = c.`cno` LEFT JOIN teacher t ON t.`tno` = c.`tno` WHERE t.`depart` = '计算机系';

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

SELECT tname,prof FROM teacher GROUP BY prof HAVING COUNT(prof)<2;

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

 SELECT cno,sno,degree FROM score  WHERE degree > (SELECT MIN(degree) FROM score WHERE cno = '3-245') AND cno = '3-105' ORDER BY degree DESC;

-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

SELECT cno,sno,degree FROM score  WHERE degree > (SELECT MAX(degree) FROM score WHERE cno = '3-245') AND cno = '3-105';

-- 31、查询所有教师和同学的name、sex和birthday.

(SELECT s.sname NAME,s.ssex sex,s.sbirthday birthday FROM student s) UNION

(SELECT t.tname NAME,t.tsex sex,t.tbirthday birthday FROM teacher t)

-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.

(SELECT s.sname NAME,s.ssex sex,s.sbirthday birthday FROM student s WHERE s.ssex = '女') UNION

(SELECT t.tname NAME,t.tsex sex,t.tbirthday birthday FROM teacher t WHERE t.tsex = '女')

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

SELECT sc.`cno`,sc.`degree`,sc.`sno` FROM score sc LEFT JOIN (SELECT cno,AVG(degree) avf FROM score GROUP BY cno) sc1 ON  sc.`cno` = sc1.cno WHERE sc.`degree`<sc1.avf;

-- 34、查询所有任课教师的Tname和Depart.

SELECT * FROM teacher t LEFT JOIN course c ON t.`tno` = c.`tno` WHERE !ISNULL(c.`cno`);

-- 35  查询所有未讲课的教师的Tname和Depart.

SELECT * FROM teacher t LEFT JOIN course c ON t.`tno` = c.`tno` WHERE ISNULL(c.`cno`);

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

SELECT class FROM student WHERE ssex = '男' GROUP BY class HAVING COUNT(class)>1;

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

SELECT * FROM student WHERE sname NOT IN (SELECT sname FROM student WHERE sname LIKE '王%');

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

SELECT YEAR(NOW())-YEAR(sbirthday) FROM student;

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

SELECT MAX(sbirthday),MIN(sbirthday) FROM student;

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

SELECT *,(2018-LEFT(sbirthday,4)) 年龄 FROM student ORDER BY class DESC,年龄 DESC;

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

SELECT * FROM course WHERE tno IN (SELECT tno FROM teacher WHERE tsex = '男');

-- 42、查询最高分同学的Sno、Cno和Degree列。

SELECT * FROM score GROUP BY cno HAVING MAX(degree);

SELECT * FROM score GROUP BY cno;

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

SELECT sname FROM student WHERE ssex = (SELECT ssex FROM student WHERE sname='李军');

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

SELECT sname FROM student WHERE class = (SELECT class FROM student WHERE sname='李军') AND ssex = (SELECT ssex FROM student WHERE sname='李军');

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

SELECT * FROM score WHERE cno = (SELECT cno FROM course WHERE cname = '计算机导论') AND sno IN (SELECT sno FROM student WHERE ssex = '男');

SELECT * FROM score sc LEFT JOIN student s ON sc.`sno` = s.`sno` RIGHT JOIN course c ON sc.`cno` = c.`cno` WHERE s.`ssex`='男' AND c.`cname` = '计算机导论';