CREATE TABLE student( Sno VARCHAR(20) NOT NULL PRIMARY KEY, Sname VARCHAR(20) NOT NULL, Ssex VARCHAR(20) NOT NULL, Sbirthday DATETIME, Class VARCHAR(20) ) DESC student; SELECT * FROM student; INSERT INTO student VALUES(108,'曾华','男','1977-09-01','95033'); INSERT INTO student VALUES(105,'匡明','男','1975-10-02','95031'); INSERT INTO student VALUES(107,'王丽','女','1976-01-23','95033'); INSERT INTO student VALUES(101,'李军','男','1976-02-20','95033'); INSERT INTO student VALUES(109,'王芳','女','1975-02-10','95031'); INSERT INTO student VALUES(103,'陆君','男','1974-06-03','95031'); CREATE TABLE Coures( Cno VARCHAR(20)PRIMARY KEY, Cname VARCHAR(20) NOT NULL, Tno VARCHAR(20) NOT NULL, CONSTRAINT Coures_Teacher_fk FOREIGN KEY(Tno) REFERENCES Teacher(Tno) ) DESC Coures; SELECT * FROM Coures; INSERT INTO Coures VALUES('3-105','计算机导论','825'); INSERT INTO Coures VALUES('3-245','操作系统','804'); INSERT INTO Coures VALUES('6-166','数字电路','856'); INSERT INTO Coures VALUES('9-888','高等数学','831'); CREATE TABLE Score( Sno VARCHAR(20) NOT NULL, Cno VARCHAR(20) NOT NULL, Degree DECIMAL(4,1), CONSTRAINT Score_student_fk FOREIGN KEY(Sno) REFERENCES student(Sno), CONSTRAINT Score_Coures_fk FOREIGN KEY(Cno) REFERENCES Coures(Cno) ) DESC Score; SELECT * FROM Score; INSERT INTO Score VALUES('103','3-245','86'); INSERT INTO Score VALUES('105','3-245','75'); INSERT INTO Score VALUES('109','3-245','68'); INSERT INTO Score VALUES('103','3-105','92'); INSERT INTO Score VALUES('105','3-105','88'); INSERT INTO Score VALUES('109','3-105','76'); INSERT INTO Score VALUES('101','3-105','64'); INSERT INTO Score VALUES('107','3-105','91'); INSERT INTO Score VALUES('108','3-105','78'); INSERT INTO Score VALUES('101','6-166','85'); INSERT INTO Score VALUES('107','6-166','79'); INSERT INTO Score VALUES('108','6-166','81'); CREATE TABLE Teacher( Tno VARCHAR(20) NOT NULL PRIMARY KEY, Tname VARCHAR(20) NOT NULL, Tsex VARCHAR(20) NOT NULL, Tbirthday DATETIME, Prof VARCHAR(20), Depart VARCHAR(20) NOT NULL ) DESC Teacher; SELECT * FROM Teacher; INSERT INTO Teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机'); INSERT INTO Teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系'); INSERT INTO Teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系'); INSERT INTO Teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系'); /*查询Student表中的所有记录的Sname、Ssex和Class列*/ SELECT Sname,Ssex,Class FROM student; /*查询教师所有的单位即不重复的Depart列*/ SELECT DISTINCT Depart FROM Teacher; /*查询Student表的所有记录。*/ SELECT * FROM student; /*查询Score表中成绩在60到80之间的所有记录。*/ SELECT * FROM Score WHERE Degree BETWEEN 60 AND 80; /*查询Score表中成绩为85，86或88的记录*/ SELECT * FROM Score WHERE Degree='85' OR Degree='86' OR Degree='88'; /*查询Student表中“95031”班或性别为“女”的同学记录*/ SELECT * FROM student WHERE Class='95031' OR Ssex='女'; /*以Class降序查询Student表的所有记录*/ SELECT * FROM student ORDER BY Class DESC; /*以Cno升序、Degree降序查询Score表的所有记录*/ SELECT * FROM Score ORDER BY Degree DESC,Cno ASC; /* 查询“95031”班的学生人数。*/ SELECT * FROM student WHERE Class=95031; /*查询Score表中的最高分的学生学号和课程号。*/ SELECT Sno,Cno FROM Score WHERE Degree=( SELECT MAX(Degree) FROM Score ); /*查询每门课的平均成绩*/ SELECT CNO,AVG(Degree) FROM Score GROUP BY Cno /*查询Score表中至少有5名学生选修的并以3开头的课程的平均分数*/ SELECT AVG(Degree) FROM Score WHERE Cno=( SELECT Cno FROM Score GROUP BY Cno HAVING COUNT(*)>5 AND Cno LIKE '3%' ); /*查询分数大于70，小于90的Sno列*/ SELECT Sno FROM Score WHERE Degree BETWEEN 70 AND 90; /*查询所有学生的Sname、Cno和Degree列*/ SELECT Sname,Cno,Degree FROM student INNER JOIN Score ON student.`Sno`=Score.`Sno`; /*查询所有学生的Sno、Cname和Degree列*/ SELECT Sno,Cname,Degree FROM Coures INNER JOIN Score ON Coures.`Cno`=Score.`Cno`; /*查询所有学生的Sname、Cname和Degree列。*/ SELECT Sname,Cname,Degree FROM student INNER JOIN Score ON student.`Sno`=Score.`Sno` JOIN Coures ON Coures.`Cno`=Score.`Cno`; /*查询“95033”班学生的平均分*/ SELECT AVG(Degree) FROM (student INNER JOIN Score ON student.`Sno`=Score.`Sno`)WHERE Class='95033'; /*查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。*/ SELECT * FROM (student INNER JOIN Score ON student.`Sno`=Score.`Sno`) WHERE Degree>(SELECT Degree FROM Score WHERE Cno='3-105' AND Sno='109')AND Cno='3-105'; /*20、查询score中选学多门课程的同学中分数为非最高分成绩的记录*/ SELECT * FROM Score GROUP BY sno HAVING Degree NOT IN ( SELECT MAX(Degree) FROM Score GROUP BY Cno ); /*查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录*/ SELECT * FROM Score WHERE Degree>(SELECT Degree FROM Score WHERE Sno='109' AND Cno='3-105')AND Cno='3-105' /*查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。*/ SELECT Sno,Sname,Sbirthday FROM student WHERE Sbirthday=( SELECT Sbirthday FROM student WHERE Sno='108' ) /*查询“张旭“教师任课的学生成绩*/ SELECT Degree FROM Score INNER JOIN Coures ON Coures.`Cno`=Score.`Cno`JOIN Teacher ON Teacher.`Tno`=Coures.`Tno` WHERE Tname='张旭'; /*从成绩表 查询学生成绩*/ SELECT Degree FROM Score WHERE Cno=( SELECT Cno FROM Coures WHERE Tno=( SELECT Tno FROM Teacher WHERE Tname='张旭') ); /*从老师表 中查名字叫 张旭 编号*/ SELECT Tno FROM Teacher WHERE Tname='张旭'; /*根据老师编号 在课程表中找到对应的 课程号*/ SELECT Cno FROM Coures WHERE Tno=( SELECT Tno FROM Teacher WHERE Tname='张旭' ); /*查询考计算机导论的学生成绩*/ SELECT Degree FROM Score WHERE Cno=( SELECT Cno FROM Coures WHERE Cname='计算机导论' ); /*查询 所有的列/字段 从 成绩表(score) 条件是课程表(course)中 课程名(cname)称为计算机导论 的课程编号*/ SELECT * FROM Score WHERE Cno=( SELECT Cno FROM Coures WHERE Cname='计算机导论' ); /*查询李诚老师教的课程名称*/ SELECT Cname FROM Coures WHERE Tno=( SELECT Tno FROM Teacher WHERE Tname='李诚' ); /*教高等数学的老师是哪个系的*/ SELECT Depart FROM Teacher WHERE Tno=( SELECT Tno FROM Coures WHERE Cname='高等数学' ); /*三表连接Coures、Score、Teacher*/ Score INNER JOIN Coures ON Score.`Cno`=coures.`Cno` JOIN Teacher ON Coures.`Tno`=Teacher.`Tno` /*查询选修某课程的同学人数多于5人的教师姓名*/ SELECT Tname FROM Teacher WHERE Tno=( SELECT Tno FROM coures WHERE cno=( SELECT cno FROM Score GROUP BY Cno HAVING COUNT(*)>5 ) ); /*查询95033班和95031班全体学生的记录*/ SELECT * FROM student WHERE class='95033' OR class='95031'; /*查询存在有85分以上成绩的课程Cno.*/ SELECT Cno FROM Score WHERE Degree>85; /*查询出“计算机系“教师所教课程的成绩表*/ SELECT Degree FROM Score WHERE Cno=( SELECT cno FROM coures WHERE Tno=( SELECT Tno FROM Teacher WHERE Depart='计算机系' ) ); /*查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学 的Cno、Sno和Degree,并按Degree从高到低次序排序。*/ SELECT * FROM Score WHERE Cno='3-105' AND Degree>( SELECT MAX(Degree) FROM Score WHERE Cno='3-245' GROUP BY Cno ) ORDER BY Degree DESC /*查询所有教师和同学的name、sex和birthday.*/ SELECT Tname,Tsex,Tbirthday,Sname,Ssex,Sbirthday FROM student INNER JOIN Score ON student.`Sno`=Score.`Sno` JOIN Coures ON Coures.`Cno`=Score.`Cno` JOIN Teacher ON teacher.`Tno`=coures.`Tno` ; /*查询所有“女”教师和“女”同学的name、sex和birthday.*/ SELECT Tname,Tsex,Tbirthday,Sname,Ssex,Sbirthday FROM student INNER JOIN Score ON student.`Sno`=Score.`Sno` JOIN Coures ON Coures.`Cno`=Score.`Cno` JOIN Teacher ON teacher.`Tno`=coures.`Tno` WHERE Tsex='女' AND Ssex='女'; /*查询成绩比该课程平均成绩低的同学的成绩表*/ SELECT Degree FROM Score WHERE Degree<( SELECT AVG(Degree) FROM Score WHERE cno IN( SELECT cno FROM Score GROUP BY Cno ) ) /*查询所有任课教师的Tname和Depart.*/ SELECT Tname,Depart FROM Teacher; /*查询所有未讲课的教师的Tname和Depart. */ SELECT Tname,Depart FROM Teacher WHERE Tno NOT IN( SELECT Coures.Tno FROM Coures INNER JOIN Teacher ON Coures.`Tno`=Teacher.`Tno` ); /*查询至少有2名男生的班号*/ SELECT class FROM student GROUP BY class,Ssex HAVING COUNT(*)>=2 AND COUNT('男')>=2; /*查询Student表中不姓“王”的同学记录*/ SELECT * FROM student WHERE Sname NOT LIKE '王%' /*查询Student表中每个学生的姓名和年龄*/ SELECT Sname,YEAR(NOW())-YEAR(Sbirthday)AS '年龄' FROM student; /*查询Student表中最大和最小的Sbirthday日期值*/ SELECT MAX(Sbirthday), MIN(Sbirthday) FROM student ; /*以班号和年龄从大到小的顺序查询Student表中的全部记录*/ SELECT * FROM student ORDER BY class DESC,Sbirthday DESC; /*查询“男”教师及其所上的课程*/ SELECT Tname,Cname FROM Coures INNER JOIN Teacher ON Coures.`Tno`=Teacher.`Tno` WHERE Tsex='男' /*查询最高分同学的Sno、Cno和Degree列。*/ SELECT Sno,Cno,Degree FROM Score WHERE Degree=( SELECT MAX(Degree)FROM Score ); /*查询和“李军”同性别的所有同学的Sname*/ SELECT Sname FROM student WHERE Ssex=( SELECT Ssex FROM student WHERE Sname='李军' ) /*查询和“李军”同性别并同班的同学Sname.*/ SELECT Sname FROM student WHERE Ssex=( SELECT Ssex FROM student WHERE Sname='李军' )AND class=( SELECT class FROM student WHERE Sname='李军' ); /*查询所有选修“计算机导论”课程的“男”同学的成绩表*/ SELECT Degree FROM score WHERE Cno=( SELECT Cno FROM coures WHERE Cname='计算机导论' )AND Sno IN( SELECT Sno FROM student WHERE Ssex='男' ); /*大于括号里面的任意一个用ANY*/ SELECT * FROM class0328 WHERE heml >ANY (22,33,44); /*大于括号里面的全部条件（数）用All*/ SELECT * FROM class0328 WHERE heml >ALL(22,33,44);