SQL表查询

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