简单的SQL子查询语句如何写

发表于:2013-07-19来源:Csdn作者:孤云点击数: 标签:MySQL
子查询分类: 1、 相关子查询 执行依赖于外部查询的数据 外部查询返回一行,子查询就执行一次。

  子查询分类:

  1、 相关子查询

  执行依赖于外部查询的数据

  外部查询返回一行,子查询就执行一次。

  2、非相关子查询

  独立于外部查询的子查询

  子查询总共执行一次,执行完毕后将值传递给外部查询

  相关子查询通常要消耗更长的时间,当数据量增加时,执行时间会急剧增加

  [sql] view plaincopyprint?

  CREATE TABLE lovoStudent(

  id INT PRIMARY KEY AUTO_INCREMENT,

  studentName VARCHAR(20),

  SUBJECT VARCHAR(20),

  grade INT

  )DEFAULT CHARSET =utf8;

  INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('张三','java基础',97);

  INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('张三','数据库',80);

  INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('张三','java web',96);

  INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('李四','数据库',95);

  INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('李四','java基础',94);

  INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('王五','java基础',85);

  -- 查询java基础最高分及其最高分的得主

  SELECT * FROM lovoStudent t WHERE grade=(SELECT MAX(grade) FROM lovoStudent t1 WHERE SUBJECT='java基础' AND t1.SUBJECT=t.SUBJECT)

  -- 查询各科目最高分及其得主

  SELECT * FROM lovoStudent t WHERE grade=(SELECT MAX(grade) FROM lovoStudent t1 WHERE t.SUBJECT =t1.SUBJECT )

  SELECT * FROM lovoStudent t WHERE grade IN(SELECT MAX(grade) FROM lovoStudent t1 WHERE t.SUBJECT =t1.SUBJECT GROUP BY SUBJECT)

  CREATE TABLE t_lovoClass(

  id INT PRIMARY KEY AUTO_INCREMENT,

  className VARCHAR(20)

  )DEFAULT CHARSET =utf8;

  INSERT INTO t_lovoClass(className) VALUES('AT01');

  INSERT INTO t_lovoClass(className) VALUES('AT02');

  INSERT INTO t_lovoClass(className) VALUES('AT03');

  INSERT INTO t_lovoClass(className) VALUES('AT04')

  ALTER TABLE lovoStudent ADD classId INT;

  UPDATE lovoStudent SET classId=1 WHERE id<3;

  UPDATE lovoStudent SET classId=2 WHERE id>5;

  UPDATE lovoStudent SET classId=3 WHERE id>=3 AND id<=5;

  -- 查询学生表所有内容并加上对应班级信息

  SELECT e.*,(SELECT className FROM t_lovoClass c WHERE e.classId=c.id) className FROM lovoStudent e;

  -- 查询所有没有学生的班级

  SELECT className FROM t_lovoClass WHERE id NOT IN(SELECT classId FROM lovoStudent)

原文转自:http://blog.csdn.net/u010142437/article/details/8903104