sql查询

阅读量：6029 次

发布时间：2019-06-20

本文共 5209 字，大约阅读时间需要 17 分钟。

------------------------------------表结构-------------------------------------

--学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）

--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）

--成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）

--教师表tblTeacher（教师编号TeaId、姓名TeaName）

高级查询

建库脚本

/* Navicat Premium Data Transfer Source Server         : local Source Server Type    : MySQL Source Server Version : 50716 Source Host           : localhost Source Database       : blancat Target Server Type    : MySQL Target Server Version : 50716 File Encoding         : utf-8 Date: 01/11/2018 11:35:30 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------  Table structure for `tblcourse`-- ----------------------------DROP TABLE IF EXISTS `tblcourse`;CREATE TABLE `tblcourse` (  `CourseId` varchar(3) NOT NULL COMMENT '课程编号',  `CourseName` varchar(20) NOT NULL COMMENT '课程名称',  `TeaId` varchar(3) DEFAULT NULL COMMENT '授课教师编号',  PRIMARY KEY (`CourseId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------  Records of `tblcourse`-- ----------------------------BEGIN;INSERT INTO `tblcourse` VALUES ('001', '企业管理', '002'), ('002', '马克思', '008'), ('003', 'UML', '006'), ('004', '数据库', '007'), ('005', '逻辑电路', '006'), ('006', '英语', '003'), ('007', '电子电路', '005'), ('008', '毛泽东思想概论', '004'), ('009', '西方哲学史', '012'), ('010', '线性代数', '017'), ('011', '计算机基础', '013'), ('012', 'AUTO CAD制图', '015'), ('013', '平面设计', '011'), ('014', 'Flash动漫', '001'), ('015', 'Java开发', '009'), ('016', 'C#基础', '002'), ('017', 'Oracl数据库原理', '010');COMMIT;-- ------------------------------  Table structure for `tblscore`-- ----------------------------DROP TABLE IF EXISTS `tblscore`;CREATE TABLE `tblscore` (  `StuId` varchar(5) DEFAULT NULL COMMENT '学号',  `CourseId` varchar(3) DEFAULT NULL COMMENT '课程编号',  `Score` float DEFAULT NULL COMMENT '成绩') ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------  Records of `tblscore`-- ----------------------------BEGIN;INSERT INTO `tblscore` VALUES ('1001', '003', '90'), ('1001', '002', '87'), ('1001', '001', '96'), ('1001', '010', '85'), ('1002', '003', '70'), ('1002', '002', '87'), ('1002', '001', '42'), ('1002', '010', '65'), ('1003', '006', '78'), ('1003', '003', '70'), ('1003', '005', '70'), ('1003', '001', '32'), ('1003', '010', '85'), ('1003', '011', '21'), ('1004', '007', '90'), ('1004', '002', '87'), ('1005', '001', '23'), ('1006', '015', '85'), ('1006', '006', '46'), ('1006', '003', '59'), ('1006', '004', '70'), ('1006', '001', '99'), ('1007', '011', '85'), ('1007', '006', '84'), ('1007', '003', '72'), ('1007', '002', '87'), ('1008', '001', '94'), ('1008', '012', '85'), ('1008', '006', '32'), ('1009', '003', '90'), ('1009', '002', '82'), ('1009', '001', '96'), ('1009', '010', '82'), ('1009', '008', '92'), ('1010', '003', '90'), ('1010', '002', '87'), ('1010', '001', '96'), ('1011', '009', '24'), ('1011', '009', '25'), ('1012', '003', '30'), ('1013', '002', '37'), ('1013', '001', '16'), ('1013', '007', '55'), ('1013', '006', '42'), ('1013', '012', '34'), ('1000', '004', '16'), ('1002', '004', '55'), ('1004', '004', '42'), ('1008', '004', '34'), ('1013', '016', '86'), ('1013', '016', '44'), ('1000', '014', '75'), ('1002', '016', '100'), ('1004', '001', '83'), ('1008', '013', '97');COMMIT;-- ------------------------------  Table structure for `tblstudent`-- ----------------------------DROP TABLE IF EXISTS `tblstudent`;CREATE TABLE `tblstudent` (  `StuId` varchar(5) NOT NULL COMMENT '学号',  `StuName` varchar(10) NOT NULL COMMENT '学生姓名',  `StuAge` int(11) DEFAULT NULL COMMENT '学生年龄',  `StuSex` char(1) NOT NULL COMMENT '学生性别',  PRIMARY KEY (`StuId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------  Records of `tblstudent`-- ----------------------------BEGIN;INSERT INTO `tblstudent` VALUES ('1000', '张无忌', '18', '男'), ('1001', '周芷若', '19', '女'), ('1002', '杨过', '19', '男'), ('1003', '赵敏', '18', '女'), ('1004', '小龙女', '17', '女'), ('1005', '张三丰', '18', '男'), ('1006', '令狐冲', '19', '男'), ('1007', '任盈盈', '20', '女'), ('1008', '岳灵珊', '19', '女'), ('1009', '韦小宝', '18', '男'), ('1010', '康敏', '17', '女'), ('1011', '萧峰', '19', '男'), ('1012', '黄蓉', '18', '女'), ('1013', '郭靖', '19', '男'), ('1014', '周伯通', '19', '男'), ('1015', '瑛姑', '20', '女'), ('1016', '李秋水', '21', '女'), ('1017', '黄药师', '18', '男'), ('1018', '李莫愁', '18', '女'), ('1019', '冯默风', '17', '男'), ('1020', '王重阳', '17', '男'), ('1021', '郭襄', '18', '女');COMMIT;-- ------------------------------  Table structure for `tblteacher`-- ----------------------------DROP TABLE IF EXISTS `tblteacher`;CREATE TABLE `tblteacher` (  `TeaId` varchar(3) NOT NULL COMMENT '教师编号',  `TeaName` varchar(10) NOT NULL COMMENT '教师名称',  PRIMARY KEY (`TeaId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------  Records of `tblteacher`-- ----------------------------BEGIN;INSERT INTO `tblteacher` VALUES ('001', '姚明'), ('002', '叶平'), ('003', '叶开'), ('004', '孟星魂'), ('005', '独孤求败'), ('006', '裘千仞'), ('007', '裘千尺'), ('008', '赵志敬'), ('009', '阿紫'), ('010', '郭芙蓉'), ('011', '佟湘玉'), ('012', '白展堂'), ('013', '吕轻侯'), ('014', '李大嘴'), ('015', '花无缺'), ('016', '金不换'), ('017', '乔丹');COMMIT;SET FOREIGN_KEY_CHECKS = 1;