准备数据

  1. -- use Stds;
  2. -- CREATE TABLE Student(
  3. -- s_id VARCHAR(20),
  4. -- s_name VARCHAR(20) NOT NULL DEFAULT'',
  5. -- s_birth VARCHAR(20) NOT NULL DEFAULT'',
  6. -- s_sex VARCHAR(10) NOT NULL DEFAULT'',
  7. -- PRIMARY KEY(s_id)
  8. -- );
  9. -- CREATE TABLE `Course`(
  10. -- `c_id` VARCHAR(20),
  11. -- `c_name` VARCHAR(20) NOT NULL DEFAULT '',
  12. -- `t_id` VARCHAR(20) NOT NULL,
  13. -- PRIMARY KEY(`c_id`)
  14. -- );
  16. -- CREATE TABLE `Teacher`(
  17. -- `t_id` VARCHAR(20),
  18. -- `t_name` VARCHAR(20) NOT NULL DEFAULT'',
  19. -- PRIMARY KEY(`t_id`)
  20. -- );
  23. -- CREATE TABLE `Score`(
  24. -- `s_id` VARCHAR(20),
  25. -- `c_id` VARCHAR(20),
  26. -- `s_score` INT(3),
  27. -- PRIMARY KEY(`s_id`,`c_id`)
  28. -- );
  31. -- insert into Student values('01','赵擂','1990-01-01','男');
  32. -- insert into Student values('02','钱电','1990-12-21','男');
  33. -- insert into Student values('03','孙风','1990-05-20','男');
  34. -- insert into Student values('04','李云','1990-08-06','男');
  35. -- insert into Student values('05','周梅','1991-12-01','女');
  36. -- insert into Student values('06','吴兰','1992-03-01','女');
  37. -- insert into Student values('07','郑竹','1989-07-01','女');
  38. -- insert into Student values('08','王菊','1990-01-20','女');
  39. -- 
  40. -- insert into Course values('01','语文','02');
  41. -- insert into Course values('02','数学','01');
  42. -- insert into Course values('03','英语','03');
  44. -- insert into Teacher values('01','张三');
  45. -- insert into Teacher values('02','李四');
  46. -- insert into Teacher values('03','王五');
  48. -- insert into Score values('01','01',80);
  49. -- insert into Score values('01','02',90);
  50. -- insert into Score values('01','03',99);
  51. -- insert into Score values('02','01',70);
  52. -- insert into Score values('02','02',60);
  53. -- insert into Score values('02','03',80);
  54. -- insert into Score values('03','01',80);
  55. -- insert into Score values('03','02',80);
  56. -- insert into Score values('03','03',80);
  57. -- insert into Score values('04','01',50);
  58. -- insert into Score values('04','02',30);
  59. -- insert into Score values('04','03',20);
  60. -- insert into Score values('05','01',76);
  61. -- insert into Score values('05','02',87);
  62. -- insert into Score values('06','01',31);
  63. -- insert into Score values('06','03',34);
  64. -- insert into Score values('07','02',89);
  65. -- insert into Score values('07','03',98);
  1. -- 1.查询01课程比02课程成绩高的学生的信息及课程分数
  2. select a.*,b.s_score as 01_score,c.s_score as 02_score from
  3. Student a
  4. join Score b on a.s_id=b.s_id and b.c_id='01'
  5. left join Score c on a.s_id=c.s_id and c.c_id='02' or c.c_id=NULL where b.s_score>c.s_score;
  6. # Student下所有列,Score下的成绩列s_score作为01_score Scores下的s_score列作为02_score
  7. # join 设置子查询条件 Student的id与Score的id相同并c_id为01
  8. # left join 关联查询 Student的id与Score的id相同并c_id为02 或者 Score的c_id为空 score大于
  10. -- 2.查询01课程比02课程成绩低的学生的信息及课程分数
  11. select a.*,b.s_score as 01_score,c.s_score as 02_score from
  12. Student a
  13. left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=Null
  14. join Score c on a.s_id=b.s_id and c.c_id='02' where b.s_score<c.s_score;
  17. -- 3.查询平均成绩大于等于60分的同学编号和学生姓名和平均成绩
  18. select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from
  19. Student b
  20. join Score a on b.s_id = a.s_id
  21. GROUP BY b.s_id,b.s_name HAVING avg_score>=60;
  22. # 四舍五入(求成绩平均数,取后两位)
  23. # 成绩表和学生表id相同的
  24. # 对查询信息进行分组 成绩表中的id和名称 筛选出 平均数大于60的
  26. -- 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
  27. select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from Student b
  28. left join Score a on b.s_id = a.s_id
  29. GROUP BY b.s_id ,b.s_name HAVING avg_score<60
  30. union
  31. select a.s_id,a.s_name ,0 as avg_score from
  32. Student a
  33. where a.s_id not in(
  34. select DISTINCT s_id from Score
  35. );
  37. -- 5.查询所有学生的编号，姓名，选课总数，所有课程的总成绩。
  38. SELECT a.s_id,a.s_name,count(b.c_id)as sum_course,sum(b.s_score)as sum_score from Student a
  39. left join Score b on a.s_id=b.s_id
  40. GROUP BY a.s_id,a.s_name;
  43. -- 6.查询李姓老师的数量
  44. select count(t_id) from Teacher where t_name like '李%';
  46. -- 7.查询学过“张三”老师授课的同学信息
  47. select a.* from
  48. Student a
  49. join Score b on a.s_id=b.s_id where b.c_id in(
  50.     select c_id from Course where t_id = (
  51.         select t_id from Teacher where t_name='张三'
  52.     )
  53. );
  55. -- 8.查询没学过"张三"老师授课的同学的信息
  56. select * from
  57.  Student c
  58.  where c.s_id not in(
  59.     select a.s_id from Student a join Score b on a.s_id=b.s_id where b.s_id in(
  60.         select c_id from Course where t_id=(
  61.             select t_id from Teacher where t_name = '张三'
  62.         )
  63.     )
  64.  );
  67. -- 9.查询学过编号为01并且学过编号为02的课程的同学的信息
  69. select a.* from 
  70.  Student a,Score b,Score c
  71.  where a.s_id=b.s_id and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';
  74. -- 10.查询学过编号为01 但是没有学过编号为02的课程的同学的信息
  75. select a.* from
  76. Student a
  77. where a.s_id in(
  78. select s_id from Score where c_id='01'
  79. ) and a.s_id not in(select s_id from Score Where c_id='02');