/*
Navicat MySQL Data Transfer
Source Server : Mysql
Source Server Version : 50704
Source Host : localhost:3306
Source Database : cs
Target Server Type : MYSQL
Target Server Version : 50704
File Encoding : 65001
Date: 2015-12-08 21:47:54
*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for course-- ----------------------------DROPTABLEIFEXISTS`course`;CREATETABLE`course` (
`courseId`int(11) NOTNULL AUTO_INCREMENT ,
`courseName`varchar(255) CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL ,
PRIMARYKEY (`courseId`)
)
ENGINE=InnoDB
DEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=6
;-- ------------------------------ Records of course-- ----------------------------BEGIN;INSERTINTO`course`VALUES ('1', '数据结构'), ('2', '计算机网络'), ('3', '编译原理'), ('4', '操作系统'), ('5', '数据库原理');COMMIT;-- ------------------------------ Table structure for sc-- ----------------------------DROPTABLEIFEXISTS`sc`;CREATETABLE`sc` (
`stuId`int(11) NOTNULL ,
`courseId`int(11) NOTNULL ,
`score`doubleNULLDEFAULTNULL ,
PRIMARYKEY (`courseId`, `stuId`),
FOREIGNKEY (`courseId`) REFERENCES`course` (`courseId`) ONDELETERESTRICTONUPDATERESTRICT,
FOREIGNKEY (`stuId`) REFERENCES`student` (`stuID`) ONDELETERESTRICTONUPDATERESTRICT,
INDEX `FK_STU` (`stuId`) USING BTREE
)
ENGINE=InnoDB
DEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ci
;-- ------------------------------ Records of sc-- ----------------------------BEGIN;INSERTINTO`sc`VALUES ('1', '1', '89'), ('5', '1', null), ('1', '2', '90'), ('2', '2', '76'), ('4', '2', '43'), ('1', '3', '58'), ('1', '4', '78'), ('5', '4', '98'), ('1', '5', '54');COMMIT;-- ------------------------------ Table structure for student-- ----------------------------DROPTABLEIFEXISTS`student`;CREATETABLE`student` (
`stuID`int(11) NOTNULL AUTO_INCREMENT ,
`stuName`varchar(255) CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL ,
`stuGender`varchar(255) CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL ,
`stuAge`int(11) NULLDEFAULTNULL ,
PRIMARYKEY (`stuID`)
)
ENGINE=InnoDB
DEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=6
;-- ------------------------------ Records of student-- ----------------------------BEGIN;INSERTINTO`student`VALUES ('1', 'jay', '男', '18'), ('2', 'Mary', '女', '20'), ('3', 'Kate', '女', '17'), ('4', 'Moon', '女', '32'), ('5', 'Bush', '男', '22');COMMIT;-- ------------------------------ Auto increment value for course-- ----------------------------ALTERTABLE`course` AUTO_INCREMENT=6;-- ------------------------------ Auto increment value for student-- ----------------------------ALTERTABLE`student` AUTO_INCREMENT=6;
简单查询
1.查询选修了所有选修课程的学生
select student.stuID ,student.stuName from student
where
(
selectcount(*) from Sc where student.stuID=sc.stuId
)=
(selectcount(*) from course)
2.查询选修了"数据结构"的学生学号和姓名
连接查询
SELECT student.stuID,student.stuName
from student
innerJOIN SC
on sc.stuId=student.stuID
INNERJOIN course
on sc.courseId=course.courseId
where course.courseName="数据结构"
嵌套查询
SELECT student.stuID,student.stuName
from student
where student.stuID in
(select sc.stuId from sc
WHERE sc.courseId =
(select course.courseId from course
where courseName="数据结构")
)
3.查询’jay’同学选修了的课程名字
连接查询
SELECT course.courseId,course.courseName
from course
INNERJOIN sc
on sc.courseId=course.courseId
INNERJOIN student
on student.stuID=sc.stuId
where student.stuName="jay"
嵌套查询
SELECT course.courseId,course.courseName
from course
where courseId in
(select sc.courseId FROM sc
where sc.stuId =
(select student.stuID
from student
where student.stuName="jay"))
4.分组
1).每门课选修的人数和课程名
SELECTcount(*),course.courseName
from sc
leftJOIN course
on course.courseId=sc.courseId
GROUPBY sc.courseId
2).查询每门课程选修学生的平均年龄
不对,我操操草丛阿聪
selectavg(student.stuAge),course.courseName
from sc
leftJOIN course
on course.courseId=sc.courseId
leftJOIN student
on student.stuID=sc.stuId
ORDERBY sc.courseId // 我擦,我真是日了狗
对
SELECTavg(student.stuAge),course.courseName
from sc
leftJOIN course
on course.courseId=sc.courseId
LEFTJOIN student
on student.stuID=sc.stuId
GROUPBY sc.courseId
3).每门课程的课程名和最高工资
SELECTmax(sc.score),course.courseName
from sc
leftJOIN course
on course.courseId=sc.courseId
GROUPBY sc.courseId