数据库简单查询

• 数据库

• 数据库文件

/*Navicat MySQL Data TransferSource Server         : MysqlSource Server Version : 50704Source Host           : localhost:3306Source Database       : csTarget Server Type    : MYSQLTarget Server Version : 50704File Encoding         : 65001Date: 2015-12-08 21:47:54*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`courseId`  int(11) NOT NULL AUTO_INCREMENT ,`courseName`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,PRIMARY KEY (`courseId`))ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=6;-- ------------------------------ Records of course-- ----------------------------BEGIN;INSERT INTO `course` VALUES ('1', '数据结构'), ('2', '计算机网络'), ('3', '编译原理'), ('4', '操作系统'), ('5', '数据库原理');COMMIT;-- ------------------------------ Table structure for sc-- ----------------------------DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc` (`stuId`  int(11) NOT NULL ,`courseId`  int(11) NOT NULL ,`score`  double NULL DEFAULT NULL ,PRIMARY KEY (`courseId`, `stuId`),FOREIGN KEY (`courseId`) REFERENCES `course` (`courseId`) ON DELETE RESTRICT ON UPDATE RESTRICT,FOREIGN KEY (`stuId`) REFERENCES `student` (`stuID`) ON DELETE RESTRICT ON UPDATE RESTRICT,INDEX `FK_STU` (`stuId`) USING BTREE )ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;-- ------------------------------ Records of sc-- ----------------------------BEGIN;INSERT INTO `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-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`stuID`  int(11) NOT NULL AUTO_INCREMENT ,`stuName`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,`stuGender`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,`stuAge`  int(11) NULL DEFAULT NULL ,PRIMARY KEY (`stuID`))ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=6;-- ------------------------------ Records of student-- ----------------------------BEGIN;INSERT INTO `student` VALUES ('1', 'jay', '男', '18'), ('2', 'Mary', '女', '20'), ('3', 'Kate', '女', '17'), ('4', 'Moon', '女', '32'), ('5', 'Bush', '男', '22');COMMIT;-- ------------------------------ Auto increment value for course-- ----------------------------ALTER TABLE `course` AUTO_INCREMENT=6;-- ------------------------------ Auto increment value for student-- ----------------------------ALTER TABLE `student` AUTO_INCREMENT=6;

• 简单查询

1.查询选修了所有选修课程的学生select student.stuID ,student.stuName from studentwhere  (select count(*) from Sc where student.stuID=sc.stuId)=(select count(*) from course)2.查询选修了"数据结构"的学生学号和姓名连接查询SELECT student.stuID,student.stuNamefrom studentinner JOIN SC on sc.stuId=student.stuIDINNER JOIN courseon sc.courseId=course.courseIdwhere course.courseName="数据结构"嵌套查询SELECT student.stuID,student.stuNamefrom studentwhere student.stuID in(select sc.stuId from scWHERE sc.courseId =(select course.courseId from coursewhere courseName="数据结构"))3.查询’jay’同学选修了的课程名字连接查询SELECT course.courseId,course.courseNamefrom courseINNER JOIN scon sc.courseId=course.courseIdINNER JOIN studenton student.stuID=sc.stuIdwhere student.stuName="jay"嵌套查询SELECT course.courseId,course.courseNamefrom coursewhere courseId in(select sc.courseId FROM scwhere sc.stuId =(select student.stuID from studentwhere student.stuName="jay"))4.分组    1).每门课选修的人数和课程名  SELECT count(*),course.courseName  from sc  left JOIN course  on course.courseId=sc.courseId  GROUP BY sc.courseId  2).查询每门课程选修学生的平均年龄  不对，我操操草丛阿聪  select avg(student.stuAge),course.courseName  from sc    left JOIN course  on course.courseId=sc.courseId  left JOIN student  on student.stuID=sc.stuId   ORDER BY sc.courseId // 我擦，我真是日了狗  对  SELECT avg(student.stuAge),course.courseName  from sc  left JOIN course  on course.courseId=sc.courseId   LEFT JOIN student   on student.stuID=sc.stuId  GROUP BY sc.courseId  3).每门课程的课程名和最高工资  SELECT max(sc.score),course.courseName  from sc  left JOIN course  on course.courseId=sc.courseId  GROUP BY sc.courseId