mysql连接查询测试题


Mysql毕业测试题( 一)

试题一、找出平均成绩大于60的所有学生的学号(即student_id)和姓名

试题二、查询所有学生的学号,姓名,选课数和总成绩;

试题三、查找名字中含有”大“的老师的个数

试题四、查询没有学过天老师课的同学的学号、姓名

试题五、查询学过“4”且学过编号“5”课程的同学的学号

试题六、查询编号“1”成绩比编号“2”成绩低的学生的学号

试题七、找出有一门课程低于60分的学生的学号和名字

试题八、查询选完全部课程的学生的学号

试题九、按平均成绩从高到低,显示所有学生的各科课程成绩

试题十、查询各科成绩的最高分和最低分及对应的学生姓名学号。

Mysql毕业测试题( 一)

试题一答案

Mysql毕业测试题( 一)

试题二答案

Mysql毕业测试题( 一)

试题三答案

Mysql毕业测试题( 一)

试题四答案

Mysql毕业测试题( 一)

试题五答案

Mysql毕业测试题( 一)

试题六答案

Mysql毕业测试题( 一)

试题七答案

Mysql毕业测试题( 一)

试题八答案

Mysql毕业测试题( 一)

/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50505
Source Host           : 127.0.0.1:3306
Source Database       : student

Target Server Type    : MYSQL
Target Server Version : 50505
File Encoding         : 65001

Date: 2019-08-15 11:45:16
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `class_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) NOT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '三年级一班');
INSERT INTO `class` VALUES ('2', '三年级二班');
INSERT INTO `class` VALUES ('3', '三年级三班');

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `teacher_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`course_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '生物', '1');
INSERT INTO `course` VALUES ('2', '体育', '2');
INSERT INTO `course` VALUES ('3', '物理', '3');
INSERT INTO `course` VALUES ('4', '美术', '4');
INSERT INTO `course` VALUES ('5', '音乐', '4');

-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `score_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL DEFAULT '0',
  `course_id` int(11) NOT NULL DEFAULT '0',
  `number` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`score_id`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '58');
INSERT INTO `score` VALUES ('2', '2', '1', '78');
INSERT INTO `score` VALUES ('3', '3', '1', '78');
INSERT INTO `score` VALUES ('4', '2', '2', '89');
INSERT INTO `score` VALUES ('5', '1', '2', '98');
INSERT INTO `score` VALUES ('6', '4', '3', '100');
INSERT INTO `score` VALUES ('7', '1', '3', '99');
INSERT INTO `score` VALUES ('8', '1', '4', '91');
INSERT INTO `score` VALUES ('9', '1', '5', '71');
INSERT INTO `score` VALUES ('10', '2', '3', '99');
INSERT INTO `score` VALUES ('11', '2', '4', '79');
INSERT INTO `score` VALUES ('12', '2', '5', '59');
INSERT INTO `score` VALUES ('13', '3', '2', '99');
INSERT INTO `score` VALUES ('14', '3', '3', '99');
INSERT INTO `score` VALUES ('15', '3', '4', '69');
INSERT INTO `score` VALUES ('16', '3', '5', '49');
INSERT INTO `score` VALUES ('17', '4', '1', '69');
INSERT INTO `score` VALUES ('18', '4', '2', '59');
INSERT INTO `score` VALUES ('19', '4', '4', '84');
INSERT INTO `score` VALUES ('20', '4', '5', '80');
INSERT INTO `score` VALUES ('21', '5', '1', '49');
INSERT INTO `score` VALUES ('22', '5', '2', '68');
INSERT INTO `score` VALUES ('23', '5', '3', '67');
INSERT INTO `score` VALUES ('24', '5', '4', '89');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) NOT NULL,
  `gender` varchar(20) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '1', '巨炮', '');
INSERT INTO `student` VALUES ('2', '1', '钢蛋', '');
INSERT INTO `student` VALUES ('3', '2', '七次郎', '');
INSERT INTO `student` VALUES ('4', '2', '米线', '');
INSERT INTO `student` VALUES ('5', '3', '辣条', '');

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '苍井空');
INSERT INTO `teacher` VALUES ('2', '麻生希');
INSERT INTO `teacher` VALUES ('3', '大桥未久');
INSERT INTO `teacher` VALUES ('4', '波多野结衣');
INSERT INTO `teacher` VALUES ('5', '天海翼');

 

posted @ 2017-09-25 00:34  za_szybko  阅读(497)  评论(0编辑  收藏  举报