SQL 学生、课程、选课，查询选择所有/3门以上课程的学生
1、学生s、课程c、选课sc，创建表，插入数据

/*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、查询选择所有课程的学生

#查询选择了所有课程的学生(一)SELECT s.sid,s.sname FROM s,      (SELECT sc.sid FROM sc GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM c))       AS tmpWHERE 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门以上课程的学生

#查询选择了超过3门以上课程的学生（一）SELECT s.sid,s.sname FROM s,      (SELECT sc.sid FROM sc GROUP BY sid HAVING COUNT(*)>3)       AS tmpWHERE 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);