SQL查询案例

最新推荐文章于 2021-02-14 14:30:33 发布

原创最新推荐文章于 2021-02-14 14:30:33 发布 · 330 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#SQL查询

MySQL 同时被 2 个专栏收录

7 篇文章

订阅专栏

数据库

7 篇文章

订阅专栏

本文详细介绍了如何使用SQL查询来获取学生信息、课程选择情况及成绩分析，包括复杂联表查询和条件筛选，展示了如何通过SQL语句解决实际问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表准备
主要是如下四张表，表与表之间的关联比较简单，就不再赘述他们之间的关系了，看一下表结构就清晰了。

/*
SQLyog Job Agent v12.3.1 (64 bit) Copyright(c) Webyog Inc. All Rights Reserved.


MySQL - 5.7.8-rc-log : Database - school
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `school`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `cid` bigint(11) NOT NULL,
  `tid` bigint(11) NOT NULL,
  `cname` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`cid`,`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `sc` */

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (
  `sid` bigint(11) NOT NULL,
  `cid` bigint(11) NOT NULL,
  `course` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sid` bigint(20) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `tid` bigint(11) NOT NULL,
  `tname` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

数据准备：

INSERT INTO `student` VALUES (101, '张三');
INSERT INTO `student` VALUES (102, '李四');
INSERT INTO `student` VALUES (103, '王五');
INSERT INTO `student` VALUES (104, '赵六');
INSERT INTO `student` VALUES (105, '调皮学生');
INSERT INTO `student` VALUES (106, '捣蛋学生');

INSERT INTO `teacher` VALUES (301, '刘德华', 30);
INSERT INTO `teacher` VALUES (302, '宋茜', 30);
INSERT INTO `teacher` VALUES (303, '王健林', 32);
INSERT INTO `teacher` VALUES (304, '胡海泉', 35);

INSERT INTO `course` VALUES (201, 301, 'Java');
INSERT INTO `course` VALUES (202, 302, 'BigData');
INSERT INTO `course` VALUES (203, 303, 'Linx网络编程');

INSERT INTO `sc` VALUES (101, 201, 80);
INSERT INTO `sc` VALUES (101, 202, 70);
INSERT INTO `sc` VALUES (101, 203, 50);
INSERT INTO `sc` VALUES (102, 201, 90);
INSERT INTO `sc` VALUES (102, 202, 85);
INSERT INTO `sc` VALUES (102, 203, 70);
INSERT INTO `sc` VALUES (103, 201, 100);
INSERT INTO `sc` VALUES (103, 203, 90);
INSERT INTO `sc` VALUES (104, 201, 55);
INSERT INTO `sc` VALUES (104, 202, 60);
INSERT INTO `sc` VALUES (104, 203, 58);
INSERT INTO `sc` VALUES (105, 201, 59);
INSERT INTO `sc` VALUES (105, 204, 59);
INSERT INTO `sc` VALUES (106, 201, 59);
INSERT INTO `sc` VALUES (106, 202, 59);

练习题目：

查出所有学生的基本信息，选了几门课程，和总成绩。
查出所有平均成绩大于60分的学生信息和平均成绩和所选课程名称。
查出所有没有选王健林老师课程的学生及其基本信息。

-- 查出所有学生的基本信息，选了几门课程，和总成绩。
SELECT
	s.`sid`,
	s.`sname`,
	COUNT(DISTINCT sc.`cid`),
	SUM(sc.`course`),
FROM
	student s
LEFT JOIN sc ON s.`sid` = sc.`sid`
GROUP BY
	s.`sid`;

-- 查出所有平均成绩大于60分的学生信息和平均成绩和所选课程名称。
SELECT
	s.`sid`,
	s.`sname`,
	AVG(sc.`course`),
	GROUP_CONCAT(c.`cname`) AS '课程名称'
FROM
	student s
LEFT JOIN sc ON s.`sid` = sc.`sid`
LEFT JOIN course c ON sc.`cid` = c.`cid`
GROUP BY
	s.`sid` -- 看一下sql语法，记住每个语法的位置
HAVING
	AVG(sc.`course`) > 60;

-- 查出所有没有选王健林老师课程的学生
-- 此题，有小坑！
SELECT
	t.*
FROM
	student t
WHERE
	t.`sid` NOT IN (
		-- 		SELECT DISTINCT
		-- 			s.`sid`
		-- 		FROM
		-- 			student s
		-- 		INNER JOIN sc ON s.`sid` = sc.`sid`
		-- 		INNER JOIN course c ON sc.`cid` = c.`cid`
		-- 		INNER JOIN teacher t ON c.`tid` = t.`tid`
		SELECT DISTINCT
			sc.sid
		FROM
			sc
		INNER JOIN course c ON sc.`cid` = c.`cid`
		INNER JOIN teacher t ON c.`tid` = t.`tid`
		WHERE
			t.`tname` = '王健林'
	);