sql 复杂查询 以teacher student course多对多关…
2018-06-18 01:37:43来源:未知 阅读 ()
数据库表和值
/* 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="关羽")))
结果
-- 5、查询没有学三门课以上的同学的学号、姓名
--前期准备步骤
SELECT * FROM student
SELECT * FROM course
SELECT * FROM student_course
--学了几门课
SELECT COUNT(*) "course_nums",student_id
FROM student_course GROUP BY student_id
--几门课少于3门的是谁
SELECT t.*
FROM (SELECT COUNT(*) "course_nums",student_id
FROM student_course GROUP BY student_id) t
WHERE t.course_nums<3
--方法一
SELECT s.id,s.name
FROM student s,
(SELECT t.*
FROM (SELECT COUNT(*) "course_nums",student_id
FROM student_course GROUP BY student_id) t
WHERE t.course_nums<3) a
WHERE s.id=a.student_id
--方法二 多表的内连接查询
SELECT s.id,s.name
FROM student s,
(SELECT COUNT(*) "course_nums",student_id
FROM student_course GROUP BY student_id HAVING course_nums<3) t
WHERE s.id=t.student_id
-- 6、查询各科成绩最高和最低的分及课程名称
--前期准备步骤
SELECT * FROM student
SELECT * FROM course
SELECT * FROM student_course
--直接在student_course查询最高分与最低分是不可取的,因为没有分组
SELECT MAX(score),MIN(score) FROM student_course
--按照couse_id进行分组
SELECT MAX(score),MIN(score),course_id FROM student_course
GROUP BY course_id
--把课程名称字段也显示出来,需要内连接查询(这个子查询实际上是VIEW视图的概念--视图就是一张虚表)
--方法一显示全部字段方法
SELECT *
FROM course c,
(SELECT MAX(score),MIN(score),course_id FROM student_course
GROUP BY course_id) t
WHERE c.id=t.course_id
--方法二显示指定字段需要起别名
SELECT t.max_score,t.min_score,course_id,c.name
FROM course c,
(SELECT MAX(score) AS "max_score",MIN(score) "min_score",course_id FROM student_course
GROUP BY course_id) t
WHERE c.id=t.course_id
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- MySQL replace函数怎么替换字符串语句 2020-03-09
- PHP访问MySQL查询超时怎么办 2020-03-09
- mysql登录时闪退 2020-02-27
- MySQL出现1067错误号 2020-02-27
- mysql7.x如何单独安装mysql 2020-02-27
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash