SQL实战案例-优快云博客

为什么80%的码农都做不了架构师？>>>


-- 导出 score 的数据库结构
CREATE DATABASE IF NOT EXISTS `score` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `score`;

-- 导出  表 score.course 结构
CREATE TABLE IF NOT EXISTS `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 正在导出表  score.course 的数据：~4 rows (大约)
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` (`id`, `cname`) VALUES
	(1, '计算机原理'),
	(2, '操作系统'),
	(3, '计算机网络'),
	(4, '数据结构');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;

-- 导出  表 score.sc 结构
CREATE TABLE IF NOT EXISTS `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sno` int(11) DEFAULT '0',
  `cno` int(11) DEFAULT '0',
  `score` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_sc_student` (`sno`),
  KEY `FK_sc_course` (`cno`),
  CONSTRAINT `FK_sc_course` FOREIGN KEY (`cno`) REFERENCES `course` (`id`),
  CONSTRAINT `FK_sc_student` FOREIGN KEY (`sno`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

-- 正在导出表  score.sc 的数据：~24 rows (大约)
/*!40000 ALTER TABLE `sc` DISABLE KEYS */;
INSERT INTO `sc` (`id`, `sno`, `cno`, `score`) VALUES
	(1, 1, 2, 80),
	(3, 1, 1, 87),
	(4, 1, 3, 99),
	(5, 1, 4, 67),
	(6, 5, 1, 56),
	(7, 5, 2, 67),
	(8, 5, 3, 78),
	(9, 5, 4, 58),
	(11, 3, 1, 89),
	(12, 3, 2, 67),
	(13, 3, 3, 35),
	(14, 3, 4, 78),
	(16, 6, 1, 73),
	(17, 6, 2, 93),
	(18, 6, 3, 92),
	(19, 6, 4, 98),
	(21, 4, 1, 89),
	(23, 4, 2, 93),
	(25, 4, 3, 73),
	(27, 4, 4, 77),
	(28, 2, 1, 79),
	(29, 2, 2, 76),
	(30, 2, 3, 67),
	(31, 2, 4, 89);

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `dept` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;


INSERT INTO `student` (`id`, `name`, `dept`, `age`) VALUES
	(1, '小丁', '财务部', 26),
	(2, '小贾', '研发部', 29),
	(3, '小李', '人事部', 24),
	(4, '小王', '研发部', 25),
	(5, '小家', '财务部', 23),
	(6, '小牛', '研发部', 28);

1查找选修和计算机的学生信息及其成绩
select a.*,c.cname,b.score from student a,course c,sc b where b.sno=a.id and b.cno=c.id   and c.cname="计算机原理"
2,查询周舟同学选修了的课程名字及其成绩
select a.name,c.cname,b.score from student a,course c,sc b where b.sno=a.id and b.cno=c.id and a.name="小丁"

3查询选修了四门课程的学生的姓名和学号
select a.*,b.sno from student a,sc b where b.sno=a.id group by b.sno having count(*)=4
4查询各组织中选修了数据结构的学生数量
select a.dept,count(b.cno) from student a,course c,sc b where b.sno=a.id and b.cno=c.id and c.cname="数据结构" group by a.dept 

5查询选学修了计算机原理没有选修数据结构的的学生数量并计算计算机原理的科目的平均分
select count(a.name),avg(b.score) from student a,course c,sc b where b.sno=a.id and b.cno=c.id  and c.cname="计算机原理"  and b.cno not in (select b.cno from student 
a,course c,sc b where b.sno=a.id and b.cno=c.id  and c.cname="数学")

更多详细资料请看这个帖子：

传送门：http://www.cnblogs.com/qixuejia/p/3637735.html

转载于:https://my.oschina.net/mifans/blog/679946