SQL学生、课程、选课，查询选择所有/3门以上课程的学生的语句
1、学生s、课程c、选课sc，创建表，插入数据
Sql代码 www.2cto.com
/*
SQLyog Trial v10.3
MySQL - 5.5.20 : Database - mytest
*********************************************************************
*/
CREATE DATABASE `mytest`;
USE `mytest`;
/*Table structure for table `c` */
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
`cid` char(10) NOT NULL,
`cname` char(20) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `c` */
insert into `c`(`cid`,`cname`) values ('001','b1'),('002','b2'),('003','b3'),('004','b4'),('005','b5');
/*Table structure for table `s` */
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
`sid` char(10) NOT NULL,
`sname` char(20) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `s` */
insert into `s`(`sid`,`sname`) values ('001','a'),('002','b'),('003','c'),('004','d'),('005','e'),('006','f');
/*Table structure for table `sc` */
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` char(10) NOT NULL,
`cid` char(10) NOT NULL,
KEY `sid` (`sid`),
KEY `cid` (`cid`),
CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),
CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `c` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `sc` */
insert into `sc`(`sid`,`cid`) values ('001','001'),('001','002'),('001','003'),('001','004'),('001','005'),('002','002'),('002','003'),('002','005'),('003','001'),('003','002'),('003','004'),('003','005');
2、查询选择所有课程的学生
Sql代码
#查询选择了所有课程的学生(一)
SELECT s.sid,s.sname
FROM s,
(SELECT sc.sid FROM sc GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM c))
AS tmp
WHERE s.sid=tmp.sid;
#查询选择了所有课程的学生(二)
SELECT s.`sid`,s.`sname` FROM s WHERE s.`sid` IN (
SELECT sc.`cid` FROM sc GROUP BY sc.sid HAVING COUNT(cid) = (SELECT COUNT(*) FROM c)
);
3、查询选择3门以上课程的学生
Sql代码
#查询选择了超过3门以上课程的学生（一）
SELECT s.sid,s.sname
FROM s,
(SELECT sc.sid FROM sc GROUP BY sid HAVING COUNT(*)>3)
AS tmp
WHERE s.sid=tmp.sid;
#查询选择了超过3门以上课程的学生（二）
SELECT s.`sid`,s.`sname` FROM s WHERE s.`sid` IN (
SELECT sc.`sid` FROM sc GROUP BY sc.sid HAVING COUNT(cid) >3
);