数据库简单查询

数据库简单查询

• 数据库

• 数据库文件

/*
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
-- ----------------------------
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=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_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=InnoDB
DEFAULT 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=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_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 student
where  
(
select count(*) from Sc where student.stuID=sc.stuId
)=
(select count(*) from course)

2.查询选修了"数据结构"的学生学号和姓名

连接查询

SELECT student.stuID,student.stuName
from student
inner JOIN SC 
on sc.stuId=student.stuID
INNER JOIN 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
INNER JOIN sc
on sc.courseId=course.courseId
INNER JOIN 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).每门课选修的人数和课程名
  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