MySQL 子查询

1. 子查询

SELECT r.studentno,
  r.subjectid,
  r.studentresult,
  (SELECT student.studentname FROM student WHERE student.studentno = r.studentno) stuname,
  (SELECT subjects.subjectname FROM subjects WHERE subjects.subjectid = r.subjectid) subname 
FROM result r;

2. 查询最高分的学生信息

• 最高分

 SELECT MAX(studentresult) FROM result; 

• 最高分的成绩信息

SELECT studentno FROM result  WHERE studentresult = (SELECT MAX(studentresult) FROM result) ;

• 根据最高分的学号找学生信息

 SELECT * FROM studentWHERE studentno = ( 
      SELECT studentno FROM result  WHERE studentresult = (SELECT MAX(studentresult) FROM result)) ;

• 学生表Student
  – 学号 姓名 性别 出生年月日 所在班级

 CREATE TABLE student(
	sno VARCHAR(20) PRIMARY KEY,
        sname VARCHAR(20) NOT NULL,
        ssex VARCHAR(10) NOT NULL,
        sbirthday DATETIME,
        class VARCHAR(20)
    );

• 课程表Course
  – 课程号 课程名称 教师编号

    CREATE TABLE course(
    cno VARCHAR(20) PRIMARY KEY,
    cname VARCHAR(20) NOT NULL,
    tno VARCHAR(20) NOT NULL,
    -- 设置外键，与教师表的编号关联
    FOREIGN KEY(tno) REFERENCES  teacher(tno)
);

• 成绩表Score
  – 学号 课程号 成绩

CREATE TABLE score(
	sno VARCHAR(20) NOT NULL,
        cno VARCHAR(20) NOT NULL,
        degree DECIMAL,
        FOREIGN KEY(sno) REFERENCES student(sno),
        FOREIGN KEY(cno) REFERENCES course(cno),
        PRIMARY KEY(sno,cno)
);

• 教师表Teacher
  – 教师编号 教师姓名 性别 出生年月日 职称 所在部门

CREATE TABLE teacher(
	tno VARCHAR(20) PRIMARY KEY,
        tname VARCHAR(20) NOT NULL,
        tsex VARCHAR(10) NOT NULL,
        tbirthday DATETIME,
        prof VARCHAR(20),
        depart VARCHAR(20) NOT NULL
);

• 学生表添加数据

INSERT INTO student VALUES( '101','曾华','男','1977-09-01','95033'); INSERT INTO student VALUES( '102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES( '103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES( '104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES( '105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES( '106','陆君','男','1974-06-03','95031');
INSERT INTO student VALUES( '107','张三','男','1976-07-04','95032');
INSERT INTO student VALUES( '108','李四','女','1979-12-05','95031');
INSERT INTO student VALUES( '109','王五','女','1971-05-06','95031');

• 教师表添加数据

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','助教','电子工程系');

• 课程表添加数据

INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

• 成绩表添加数据

INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('109','6-166','81');