欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql 复杂查询 以teacher student course多对多关系为例

程序员文章站 2022-04-06 17:56:15
数据库表和值 -- 4、查询没学过关羽老师课的同学的学号、姓名 步骤一SELECT c.id FROM teacher t,course c WHERE t.id=c.teacher_id AND t.name="关羽" 步骤二 SELECT DISTINCT s.id FROM student s ......

数据库表和值

/*
SQLyog Ultimate v8.32 
MySQL - 5.7.17-log : Database - course_dbms
*********************************************************************
*/


/*!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*/`course_dbms` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `course_dbms`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `teacher_id` (`teacher_id`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`id`,`name`,`teacher_id`) values (1,'语文',1),(2,'数学',1),(3,'生物',2),(4,'化学',2),(5,'物理',2),(6,'英语',3);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`city`,`age`) values (1,'小王','北京',20),(2,'小李','上海',18),(3,'小周','北京',22),(4,'小刘','北京',21),(5,'小张','上海',22),(6,'小赵','北京',17),(7,'小蒋','上海',23),(8,'小韩','北京',25),(9,'小魏','上海',25),(10,'小明','北京',20);

/*Table structure for table `student_course` */

DROP TABLE IF EXISTS `student_course`;

CREATE TABLE `student_course` (
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  KEY `student_id` (`student_id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `student_course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
  CONSTRAINT `student_course_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student_course` */

insert  into `student_course`(`student_id`,`course_id`,`score`) values (1,1,80),(1,2,90),(1,3,85),(1,4,78),(2,2,53),(2,3,77),(2,5,80),(3,1,71),(3,2,70),(3,4,80),(3,5,65),(3,6,75),(4,2,90),(4,3,80),(4,4,70),(4,6,95),(5,1,60),(5,2,70),(5,5,80),(5,6,69),(6,1,76),(6,2,88),(6,3,87),(7,4,80),(8,2,71),(8,3,58),(8,5,68),(9,2,88),(10,1,77),(10,2,76),(10,3,80),(10,4,85),(10,5,83);

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

insert  into `teacher`(`id`,`name`) values (1,'关羽'),(2,'张飞'),(3,'赵云');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

-- 4、查询没学过关羽老师课的同学的学号、姓名

步骤一
SELECT c.id FROM teacher t,course c WHERE t.id=c.teacher_id AND t.name="关羽"

步骤二

SELECT DISTINCT s.id FROM
student s,student_course sc WHERE s.id=sc.student_id AND course_id IN(1,2)

步骤三

SELECT id,NAME FROM student WHERE id NOT IN(SELECT DISTINCT s.id FROM
student s,student_course sc WHERE
s.id=sc.student_id AND course_id IN
((SELECT c.id FROM teacher t,course c WHERE t.id=c.teacher_id AND t.name="关羽")))

结果

sql 复杂查询 以teacher student course多对多关系为例