MySQL之SQL训练（一）

一、模型

二、表操作

• 1、创建表结构

  CREATE TABLE `dept` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `dept_name` varchar(32),
    PRIMARY KEY (`id`)
  ) ;
  
  CREATE TABLE `teacher` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) ,
    `dept_id` int(11) ,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
  ) ;
  
  CREATE TABLE `course` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `course_name` varchar(32) ,
    `teacher_id` int(11),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
  ) ;
  
  CREATE TABLE `students` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) ,
    `gender` tinyint(4)  COMMENT '0:male,1:female',
    `birthday` date ,
    `dept_id` int(11),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
  ) ;
  
  CREATE TABLE `score` (
    `sid` int(11),
    `course_id` int(11) ,
    `score` tinyint(4),
    FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
    FOREIGN KEY (`sid`) REFERENCES `students` (`id`)
  ) ;
  

• 2、插入测试数据

  INSERT INTO `dept` VALUES (1,'Education'),(2,'Computer Science'),(3,'Mathematics');
  
  INSERT INTO `teacher` VALUES (1,'Zhang san',1),(2,'Li si',1),(3,'Wang wu',2),(4,'Liu liu',3),(5,'Ding \nqi',3),(6,'Zhang yang',2);
  
  INSERT INTO `course` VALUES (1,'math',3),(2,'english',2),(3,'chinese',4),(4,'history',1),(5,'biology',5),(6,'physical',2);
  
  INSERT INTO `students` VALUES (1,'Andrew',0,'1985-08-05',1),(2,'Andy',0,'1983-02-09',1),(3,'Bob',0,'1983-01-03',1),(4,'Ruth',1,'1984-12-23',2),(5,'Mike',0,'1985-01-02',2),(6,'John',0,'1984-02-23',3),(7,'Cindy',1,'1984-12-04',3),(8,'Susan',1,'1983-11-11',3),(9,'John',1,'1984-09-18',2),(10,'Esther',1,'1988-12-25',3);
  
  INSERT INTO `score` VALUES (1,1,56),(1,2,90),(1,3,82),(1,5,56),(2,2,78),(2,4,92),(2,3,77),(2,5,65),(3,1,48),(3,2,95),(3,3,75),(3,4,89),(3,5,92),(4,3,78),(4,4,67),(5,1,75),(5,3,90),(5,4,82),(6,2,58),(6,4,88),(7,1,46),(7,2,65),(7,3,63),(7,4,68),(7,5,32),(8,4,88),(8,5,100),(1,6,76),(2,6,54),(4,6,98),(5,6,87),(8,6,76),(9,1,90),(9,2,59),(10,3,67);
  

三、练习题

• 1、查询85年之后出生的学生人数

  SELECT COUNT(*) FROM `students` WHERE `birthday` >'1985-01-01'
  

• 2、查询名字相同的学生名单和个数

  SELECT `name`, COUNT(*) FROM `students` GROUP BY `name` 
  

• 3、将所有学生姓名中前后的空格去掉、

  SELECT TRIM(`name`) FROM `students`
  

• 4 、查看每门课程被选修的学生数

  SELECT `course_id`,COUNT(`sid`) FROM `score` GROUP BY `course_id`
  

• 5、查询姓zhang的老师的个数

  SELECT COUNT(*) FROM `teacher` WHERE SUBSTRING_INDEX(`name`,' ',1) = 'Zhang'
  

• 6 、将所有老师的名字拆分成姓和名两个字段显示

  SELECT
    SUBSTRING(`name`, 1, LOCATE(' ', `name`)) '姓', -- locate()返回子串位置
    SUBSTRING_INDEX(`name`, ' ', - 1) '名' -- 推荐使用SUBSTRING_INDEX()
  FROM
    `teacher`
  

• 7、把所有学生的生日格式转换成年月日的格式，并计算每个学生年龄

  SELECT
    `name`,
    DATE_FORMAT(`birthday`, '%Y%m%d'),
    FLOOR(DATEDIFF(NOW(), `birthday`) / 365) '年龄'
  FROM
    `students`
  

• 8、查询有不及格学生的课程和不及格学生个数

  SELECT
    b.`course_name`,
    COUNT(*)
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  WHERE a.`score` < 60
  GROUP BY a.`course_id`
  

• 9、查询每门课程的平均成绩，按升序排序，如果平均成绩相同按课程ID降序排序

  SELECT
    b.`course_name`,
    AVG(a.`score`)
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  GROUP BY a.`course_id`
  ORDER BY AVG(a.`score`),a.`course_id` DESC
  

• 10、查询有学生的单科成绩小于60的姓名和课程名称

  SELECT
    b.`name`,
    c.`course_name`
  FROM
    `score` a
    JOIN `students` b
      ON a.`sid` = b.`id`
    JOIN `course` c
      ON a.`course_id` = c.`id`
  WHERE a.`score` < 60
  

• 11、查看平均成绩大于等于60的所有学生的姓名和平均成绩

  SELECT
    any_value (b.`name`),
    AVG(a.`score`)
  FROM
    `score` a
    JOIN `students` b
      ON a.`sid` = b.`id`
  GROUP BY b.`id`
  HAVING AVG(a.`score`) > 60
  

• 12、将所有学生的考试成绩展示为课程名：成绩样式

  SELECT
    a.`sid`,
    CONCAT(b.`course_name`, ':', a.`score`)
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  

• 13、查询不同老师所教不同课程平均分从高到低

  SELECT
    b.`teacher_id`,
    b.`id` -- 课程ID
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  GROUP BY b.`teacher_id`, b.`id`
  ORDER BY AVG(a.`score`) DESC
  

• 14、查询英语课程成绩排名第3到第5位的学生姓名和成绩

  SELECT
    b.`name`,
    a.`score`
  FROM
    `score` a
    JOIN `students` b
      ON a.`sid` = b.`id`
    JOIN `course` c
      ON a.`course_id` = c.`id`
  WHERE c.`course_name` = 'english'
  ORDER BY a.`score` DESC
  LIMIT 2, 3
  

• 15、查询所有同学的学号，姓名，选课数和总成绩

  SELECT
    a.`sid`,
    b.`name`,
    COUNT(a.`course_id`),
    SUM(a.`score`)
  FROM
    `score` a
    JOIN `students` b
      ON a.`sid` = b.`id`
  GROUP BY b.`id`
  

• 16、查询各科成绩中的最高分和最低分

  SELECT
    b.`course_name`,
    MAX(a.`score`),
    MIN(a.`score`)
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  GROUP BY b.`course_name`
  

• 17、计算各科平均成绩和及格率百分比

  SELECT
    b.`course_name`,
    AVG(a.`score`),
    SUM(IF(a.`score`>=60,1,0))/COUNT(*)
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  GROUP BY b.`course_name`
  

• 18、查看只学习了一门课程的学生的姓名和学号

  SELECT
    `id`,
    `name`
  FROM
    `students`
  WHERE `id` = (SELECT `sid` FROM `score` GROUP BY `sid` HAVING COUNT(`course_id`)=1)
  

• 19、查询没学过zhangsan老师课程的学生的学号和姓名

  SELECT
    `id`,
    `name`
  FROM
    `students`
  WHERE `id` NOT IN
    (SELECT DISTINCT
      a.`sid`
    FROM
      `score` a
      JOIN `course` b
        ON a.`course_id` = b.`id`
      JOIN `teacher` c
        ON b.`teacher_id` = c.`id`
    WHERE c.`name` = 'Zhang san')
  

• 20、查询既学过英语也学过语文的学生的学号和姓名

  SELECT
    b.`id`,
    b.`name`,
    GROUP_CONCAT(c.`course_name`)
  FROM
    `score` a
    JOIN `students` b
      ON a.`sid` = b.`id`
    JOIN `course` c
      ON a.`course_id` = c.`id`
  WHERE c.`course_name` IN ('english', 'chinese')
  GROUP BY b.`id`
  HAVING COUNT(*) >1
  

• 21、按平均成绩从高到低显示所有学生的姓名和语文，数学，英语三科成绩

  SELECT
    any_value(b.`name`),
    SUM(CASE WHEN c.`course_name`='chinese' THEN a.score ELSE 0 END) 'chinese',
    SUM(IF(c.`course_name`='math',a.`score`,0)) 'math',
    SUM(IF(c.`course_name`='english',a.`score`,0)) 'english'
  FROM
    `score` a
    JOIN `students` b
      ON a.`sid` = b.`id`
    JOIN `course` c
      ON a.`course_id` = c.`id`
  GROUP BY a.`sid`
  ORDER BY AVG(a.`score`) DESC
  

• 22、统计按照各科成绩，分段统计每个课程在90分以上、80-90的、60~80、低于60分的人数

  SELECT
    b.`course_name`,
    SUM(IF(a.`score`>=90,1,0)) '90分以上',
    SUM(IF(a.`score`<90 AND a.`score`>=80,1,0)) '80~90分',
    SUM(IF(a.`score`<80 AND a.`score`>=60,1,0)) '60~80分',
    SUM(IF(a.`score`<60,1,0)) '不及格'
  FROM
    `score` a
    JOIN `course` b
      ON a.`course_id` = b.`id`
  GROUP BY b.`course_name`
  

• 23、查看所有英语成绩超过数学成绩的学生的学号和姓名

  SELECT
    a.`id`,a.`name`,c.`score`, b.`score` 
  FROM
    `students` a
    JOIN (SELECT a.`sid`,a.`score` FROM `score` a JOIN `course` b ON a.`course_id` = b.`id` WHERE b.`course_name` = 'math') b
      ON a.`id` = b.`sid`
    JOIN (SELECT a.`sid`,a.`score` FROM `score` a JOIN `course` b ON a.`course_id` = b.`id` WHERE b.`course_name` = 'english') c  
      ON a.`id` = c.`sid`
  WHERE c.`score`> b.`score`