sql语句练习(1) 含问题,答案,数据库表,数据

小鱼儿 2023-10-03 13:58 27阅读 0赞

## 前言 ##

分享一些sql语句联系题,4个表的关联,sql语句答案不唯一,可以当成练习使用,聚合函数 in,between and,having,order by,group by,limit,case when关键字语法都有用到 还挺综合,我写的应该有错的 欢迎大家下方评论留言

## 以下为4个表的数据结构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70 3]

## 以下为创建表的语句,直接选择数据库执行即可,如果是dbeaver客户端,可能需要删除这句直接创建 DROP TABLE IF EXISTS `grade`; ##

/*
    Navicat MySQL Data Transfer
    
    Source Server         : 本地mysql
    Source Server Version : 50715
    Source Host           : localhost:3306
    Source Database       : geminno_day_07
    
    Target Server Type    : MYSQL
    Target Server Version : 50715
    File Encoding         : 65001
    
    Date: 2017-07-11 19:16:14
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for grade
    -- ----------------------------
    DROP TABLE IF EXISTS `grade`;
    CREATE TABLE `grade` (
      `GradeId` varchar(255) DEFAULT NULL,
      `GradeName` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of grade
    -- ----------------------------
    INSERT INTO `grade` VALUES ('1', 'S1');
    INSERT INTO `grade` VALUES ('2', 'S2');
    INSERT INTO `grade` VALUES ('3', 'S3');
    
    -- ----------------------------
    -- Table structure for result
    -- ----------------------------
    DROP TABLE IF EXISTS `result`;
    CREATE TABLE `result` (
      `Id` varchar(255) DEFAULT NULL,
      `StudentNo` varchar(255) DEFAULT NULL,
      `SubjectId` varchar(255) DEFAULT NULL,
      `StudentResult` varchar(255) DEFAULT NULL,
      `ExamDate` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of result
    -- ----------------------------
    INSERT INTO `result` VALUES ('1', 'S1201302001', '1', '80', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('2', 'S1201302002', '1', '45', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('3', 'S1201302001', '2', '90', '2013/10/18 星期五');
    INSERT INTO `result` VALUES ('5', 'S1201302002', '2', '60', '2013/10/18 星期五');
    INSERT INTO `result` VALUES ('12', 'S2102001', '3', '76', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('13', 'S2102002', '3', '90', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('14', 'S2102003', '3', '99', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('15', 'S2102004', '3', '79', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('16', 'S2102005', '3', '80', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('17', 'S2102006', '3', '61', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('18', 'S2102007', '3', '82', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('19', 'S2102008', '3', '35', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('20', 'S2102009', '3', '64', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('21', 'S2102010', '3', '65', '2013/9/13 星期五');
    INSERT INTO `result` VALUES ('22', 'S1101001', '1', '80', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('23', 'S1101002', '1', '61', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('24', 'S1101003', '1', '82', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('25', 'S1101004', '1', '55', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('26', 'S1101005', '1', '64', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('27', 'S1101006', '1', '65', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('28', 'S1101007', '1', '88', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('29', 'S1101008', '1', '67', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('30', 'S1101009', '1', '68', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('31', 'S1101010', '1', '44', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('32', 'S1101011', '1', '89', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('33', 'S1101012', '1', '71', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('34', 'S1101013', '1', '44', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('35', 'S1101014', '1', '77', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('36', 'S1101015', '1', '86', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('37', 'S1101016', '1', '75', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('38', 'S1101017', '1', '76', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('39', 'S1101018', '1', '90', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('40', 'S1101019', '1', '99', '2013/12/22 星期日');
    INSERT INTO `result` VALUES ('41', 'S1101020', '1', '79', '2013/12/23 星期一');
    INSERT INTO `result` VALUES ('42', 'S1101001', '2', '80', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('43', 'S1101002', '2', '61', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('44', 'S1101003', '2', '82', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('45', 'S1101004', '2', '35', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('46', 'S1101005', '2', '64', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('47', 'S1101006', '2', '65', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('48', 'S1101007', '2', '88', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('49', 'S1101008', '2', '67', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('50', 'S1101009', '2', '68', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('51', 'S1101010', '2', '44', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('52', 'S1101011', '2', '75', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('53', 'S1101012', '2', '71', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('54', 'S1101013', '2', '44', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('55', 'S1101014', '2', '88', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('56', 'S1101015', '2', '86', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('57', 'S1101016', '2', '75', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('58', 'S1101017', '2', '38', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('59', 'S1101018', '2', '90', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('60', 'S1101019', '2', '99', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('61', 'S1101020', '2', '79', '2013/11/15 星期五');
    INSERT INTO `result` VALUES ('62', 'S1101011', '1', '68', '2013/7/1 星期一');
    INSERT INTO `result` VALUES ('63', 'S1101014', '1', '75', '2013/7/1 星期一');
    INSERT INTO `result` VALUES ('64', 'S1101008', '2', '45', '2013/7/1 星期一');
    INSERT INTO `result` VALUES ('65', 'S1101014', '2', '60', '2013/7/1 星期一');
    INSERT INTO `result` VALUES ('66', 'S2102001', '8', '99', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('67', 'S2102002', '8', '55', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('68', 'S2102003', '8', '60', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('69', 'S2102004', '8', '70', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('70', 'S2102005', '8', '85', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('71', 'S2102006', '8', '88', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('72', 'S2102007', '8', '95', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('73', 'S2102008', '8', '44', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('74', 'S2102009', '8', '68', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('75', 'S2102010', '8', '88', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('76', 'S2102011', '8', '88', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('77', 'S1101001', '3', '88', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('78', 'S1101002', '3', '50', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('79', 'S1101003', '3', '44', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('80', 'S1101004', '3', '77', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('81', 'S1101005', '3', '75', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('82', 'S1101006', '3', '63', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('83', 'S1101007', '3', '15', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('84', 'S1101008', '3', '55', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('85', 'S1101009', '3', '69', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('86', 'S1101010', '3', '90', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('87', 'S1101011', '3', '95', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('88', 'S1101001', '4', '99', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('89', 'S1101002', '4', '60', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('90', 'S1101003', '4', '65', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('91', 'S1101004', '4', '77', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('92', 'S1101005', '4', '75', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('93', 'S1101006', '4', '63', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('94', 'S1101007', '4', '45', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('95', 'S1101008', '4', '55', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('96', 'S1101009', '4', '69', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('97', 'S1101010', '4', '90', '2013/3/22 星期五');
    INSERT INTO `result` VALUES ('98', 'S1101011', '4', '99', '2013/3/22 星期五');
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `StudentNo` varchar(255) DEFAULT NULL,
      `LoginPwd` varchar(255) DEFAULT NULL,
      `StudentName` varchar(255) DEFAULT NULL,
      `Sex` varchar(255) DEFAULT NULL,
      `GradeId` varchar(255) DEFAULT NULL,
      `Phone` varchar(255) DEFAULT NULL,
      `Address` varchar(255) DEFAULT NULL,
      `BornDate` varchar(255) DEFAULT NULL,
      `Email` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('S1101001', '228996245', '金蝶', '男', '1', '717215147', '天津市河西区', '1995/1/22 星期日', 'okiwcydml@ryhlo.com');
    INSERT INTO `student` VALUES ('S1101002', '228996246', '洛飞', '女', '1', '666762663', '天津市南开区', '1995/4/22 星期六', 'jnqlpkdwb@nsjpt.com');
    INSERT INTO `student` VALUES ('S1101003', '228996247', '凌辉', '女', '1', '353149818', '北京市海淀区成府路', '1998/6/17 星期三', 'eepispykh@oitbl.com');
    INSERT INTO `student` VALUES ('S1101004', '228996251', '白燕', '男', '1', '676151367', '学生宿舍', '1995/07/11星期二', 'cxmnnrhfn@sjsam.com');
    INSERT INTO `student` VALUES ('S1101005', '228996252', '夏一桐', '男', '1', '397739963', '北京市朝阳区大屯', '1994/11/25 星期五', 'npgiygxox@ootml.com');
    INSERT INTO `student` VALUES ('S1101006', '228996253', '欧阳燕飞', '男', '1', '13512345678', '河南省南阳市', '1992/8/31 星期一', 'dkwkiqbrj@yrkro.com');
    INSERT INTO `student` VALUES ('S1101007', '228996254', '孟祥亚', '男', '1', '13512345679', '河南省洛阳市涧西区', '1998/07/11 星期四', 'gekdinmky@ijisq.com');
    INSERT INTO `student` VALUES ('S1101008', '228996257', '凌洋', '女', '1', '15812345680', '湖南省长沙', '1995/2/12 星期日', '');
    INSERT INTO `student` VALUES ('S1101009', '228996259', '杨阳', '男', '1', '13512345681', '上海市长虹区', '1994/4/3 星期日', '');
    INSERT INTO `student` VALUES ('S1101010', '228996263', '方晴', '男', '1', '13412345682', '山东省聊城市', '1995/9/24 星期日', '');
    INSERT INTO `student` VALUES ('S1101011', '228996267', '圆荷', '女', '1', '13512344483', '河北省石家庄', '1994/5/29 星期日', 'idfwxlbjr@bkxko.com');
    INSERT INTO `student` VALUES ('S1101012', '228996270', '崔今生', '女', '1', '13512345684', '河北省邯郸市', '1995/3/20 星期一', 'qrakldetd@ogtso.com');
    INSERT INTO `student` VALUES ('S1101013', '228996271', '姜北', '男', '1', '578346237', '学生宿舍', '1995/4/9 星期日', 'soppebati@hwxpo.com');
    INSERT INTO `student` VALUES ('S1101014', '228996273', '姜丫丫', '男', '1', '926457035', '北京市朝阳区慧忠里', '1997/9/6 星期六', 'rfkhgcxhy@oomkw.com');
    INSERT INTO `student` VALUES ('S1101015', '228996274', '孙河', '男', '1', '895684949', '北京市通州区', '1995/9/7 星期四', 'kdaqojpjj@olmcy.com');
    INSERT INTO `student` VALUES ('S1101016', '228996275', '王可', '男', '1', '328884827', '北京市石景山', '1995/9/8 星期五', 'bojzfsixf@qjwgw.com');
    INSERT INTO `student` VALUES ('S1101017', '228996276', '赵七', '女', '1', '511686053', '北京市海淀区中关村', '1990/9/9 星期日', 'ltshcitdp@qdpeh.com');
    INSERT INTO `student` VALUES ('S1101018', '228996279', '买燕', '男', '1', '155426854', '河南省周口', '1995/9/10 星期日', 'rmpnhbiop@mwrom.com');
    INSERT INTO `student` VALUES ('S1101019', '228996281', '冯征', '男', '1', '436987577', '湖北省襄樊', '1992/9/10 星期四', 'mdokehdic@omcte.com');
    INSERT INTO `student` VALUES ('S1101020', '228996283', '冯中', '男', '1', '786340887', '天津市南开区', '1995/9/12 星期二', 'lwspyimci@kcplq.com');
    INSERT INTO `student` VALUES ('S1201302001', 'zhangsan', '张三', '男', '1', '01062768866', '解放路', '1994/1/1 星期六', 'zhangsan@126.com');
    INSERT INTO `student` VALUES ('S1201302002', 'lisi89', '李四', '男', '1', '13812345678', '长江路', '1992/2/1 星期六', '未知@');
    INSERT INTO `student` VALUES ('S1201302003', '123456', '王五', '男', '1', '13912345678', '学生宿舍', '1992/5/3 星期日', '未知@');
    INSERT INTO `student` VALUES ('S1201302004', 'wanglili', '王丽丽', '女', '1', '13112345678', '山东省济南市文化路1号院', '1991/6/2 星期日', 'wangll@sohu.com');
    INSERT INTO `student` VALUES ('S1201302005', '123456', '张峰', '男', '2', '13212345678', '学生宿舍', '1994/8/8 星期一', '未知@');
    INSERT INTO `student` VALUES ('S2102001', '228996284', '包卫丽', '女', '2', '13875940258', '湖州德清', '1992/9/13 星期三', 'sakuralove2829@yahoo.com');
    INSERT INTO `student` VALUES ('S2102002', '228996285', '吴可鹏', '男', '2', '13875940259', '学生宿舍', '1996/9/13 星期五', 'peggyonhkhk@yahoo.com');
    INSERT INTO `student` VALUES ('S2102003', '228996287', '於炉冰', '男', '2', '13875940260', '学生宿舍', '1995/9/15 星期五', 'yyyyyyyyyyu@yahoo.com');
    INSERT INTO `student` VALUES ('S2102004', '228996290', '朱月清', '女', '2', '13875940261', '衢州开化', '1995/9/16 星期六', 'yinyin01094@yahoo.com.hk');
    INSERT INTO `student` VALUES ('S2102005', '228996291', '赵献星', '男', '2', '13875940262', '杭州建德', '1995/2/12 星期日', 'ada19262002@hotmail.com');
    INSERT INTO `student` VALUES ('S2102006', '228996292', '吴宝玉', '女', '2', '13875940263', '湖州埭溪', '1992/2/13 星期四', '');
    INSERT INTO `student` VALUES ('S2102007', '228996293', '胡梦秋', '女', '2', '13875940264', '杭州临安', '1995/2/14 星期二', 'bobo_lok13@yahoo.com');
    INSERT INTO `student` VALUES ('S2102008', '228996302', '吴春红', '女', '2', '13875940265', '金华金东区', '1995/2/15 星期三', 'yintakli@yahoo.com.hk');
    INSERT INTO `student` VALUES ('S2102009', '228996304', '张文俊', '男', '2', '13974807750', '绍兴上虞', '1998/2/16 星期一', 'natalielam01098@yahoo.com');
    INSERT INTO `student` VALUES ('S2102010', '228996305', '乐晓燕', '男', '2', '13007426477', '台州三门', '1995/2/17 星期五', '');
    INSERT INTO `student` VALUES ('S2102011', '228996307', '裴珍', '男', '2', '13873140386', '北京大兴区', '1994/12/1 星期四', 'lingling52030996@hotmail.com');
    INSERT INTO `student` VALUES ('S2102012', '228996308', '周巧飞', '男', '2', '13107428484', '学生宿舍', '1994/12/2 星期五', 'yuenfunki@yahoo.com.hk');
    INSERT INTO `student` VALUES ('S2102013', '228996309', '王晓文', '男', '2', '13574220088', '杭州建德', '1997/12/3 星期三', 'kykwok9@hotmail.com');
    INSERT INTO `student` VALUES ('S2102014', '228996312', '卢凤华', '女', '2', '13574220089', '衢州开化', '1994/12/4 星期日', 'sulia123@yahoo.com');
    INSERT INTO `student` VALUES ('S2102015', '228996316', '胡灵', '女', '2', '13574220090', '台州天台', '1994/12/5 星期一', 'chankwaiching@yahoo.com');
    INSERT INTO `student` VALUES ('S2102016', '228996317', '盛立', '男', '2', '13574220091', '安徽阜阳', '1994/12/6 星期二', 'yankchan2004@yahoo.com');
    INSERT INTO `student` VALUES ('S2102017', '228996318', '吴孝红', '男', '2', '13678945617', '黑龙江哈尔滨', '1994/12/7 星期三', 'ayako04620@yahoo.com');
    INSERT INTO `student` VALUES ('S2102018', '228996323', '曾飞燕', '男', '2', '13678945618', '杭州临安', '1998/12/8 星期二', 'ayako04620@yahoo.com');
    INSERT INTO `student` VALUES ('S2102019', '228996324', '陈科', '男', '2', '13678945619', '新疆乌鲁木齐', '1994/8/25 星期四', '');
    INSERT INTO `student` VALUES ('S2102020', '228996325', '严丽丽', '男', '2', '13678945620', '新疆伊宁', '1995/7/17 星期一', 'phoebe_swy@yahoo.com');
    INSERT INTO `student` VALUES ('S31003001', '228996326', '孙瑞晨', '男', '3', '13574220096', '金华兰溪', '1994/7/17 星期日', 'faat11@yahoo.com');
    INSERT INTO `student` VALUES ('S31003002', '228996327', '黄金宵', '男', '3', '13574220097', '绍兴诸暨', '1994/4/8 星期五', '');
    INSERT INTO `student` VALUES ('S31003003', '228996329', '陈婷婷', '男', '3', '13574220098', '衢州常山', '1994/6/20 星期一', 'mandylau1986@yahoo.com');
    INSERT INTO `student` VALUES ('S31003004', '228996334', '章巧丹', '女', '3', '13174220099', '宁波宁海', '1994/8/22 星期一', '');
    INSERT INTO `student` VALUES ('S31003005', '228996340', '刘丽云 ', '女', '3', '13174220100', '学生宿舍', '1994/1/25 星期二', 'raymondchoi04@yahoo.com');
    INSERT INTO `student` VALUES ('S31003006', '228996341', '丁 亮', '男', '3', '13174220101', '嘉兴嘉善', '1994/3/28 星期一', 'tlh10202003@yahoo.com');
    INSERT INTO `student` VALUES ('S31003007', '228996342', '李明华', '女', '3', '13574220102', '金华兰溪', '1993/6/1 星期二', 'mimichoi276@yahoo.com');
    INSERT INTO `student` VALUES ('S31003008', '228996343', ' 金 晶', '男', '3', '13574220103', '学生宿舍', '1993/6/2 星期三', 'mapdhhljp@ncqfg.com');
    INSERT INTO `student` VALUES ('S31003009', '228996346', '张世庆', '男', '3', '13574220104', '湖州双林', '1995/6/3 星期六', 'godspnrel@eblox.com');
    INSERT INTO `student` VALUES ('S31003010', '228996348', '方汝滔', '男', '3', '13574220105', '衢州常山', '1993/6/4 星期五', 'wieiowzmq@jplop.com');
    INSERT INTO `student` VALUES ('S31003011', '228996349', '王 波', '男', '3', '13574220106', '北京朝阳区', '1993/6/5 星期六', 'wengsanc14l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003012', '228996351', '颜俊俊', '男', '3', '13374220107', '衢州龙游', '1993/6/6 星期日', 'wengsanc15l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003013', '228996352', '刘雪琼', '女', '3', '13374220108', '绍兴嵊州', '1994/9/8 星期四', 'wengsanc16l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003014', '228996353', '胡晓奕', '女', '3', '13374220109', '学生宿舍', '1994/12/6 星期二', 'wengsanc17l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003015', '228996357', '王莉莉', '女', '3', '13374220110', '湖州杨家埠', '1995/1/20 星期五', '');
    INSERT INTO `student` VALUES ('S31003016', '228996362', '金辉', '男', '3', '13574220111', '台州温岭', '1994/7/7 星期四', 'wengsanc19l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003017', '228996364', '郑峰', '男', '3', '13574220112', '北京海淀区', '1994/12/7 星期三', 'wengsanc20l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003018', '228996365', '何国英', '男', '3', '13574220113', '台州温岭', '1993/5/14 星期五', '');
    INSERT INTO `student` VALUES ('S31003019', '228996370', '方振', '男', '3', '13574220114', '台州三门', '1995/2/11 星期六', 'wengsanc22l@yahoo.com');
    INSERT INTO `student` VALUES ('S31003020', '228996371', '雷应飞', '男', '3', '13574220115', '湖州吴兴区', '1994/3/31 星期四', 'wengsanc23l@yahoo.com');
    
    -- ----------------------------
    -- Table structure for subject
    -- ----------------------------
    DROP TABLE IF EXISTS `subject`;
    CREATE TABLE `subject` (
      `SubjectId` varchar(255) DEFAULT NULL,
      `SubjectName` varchar(255) DEFAULT NULL,
      `ClassHour` varchar(255) DEFAULT NULL,
      `GradeId` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of subject
    -- ----------------------------
    INSERT INTO `subject` VALUES ('1', 'Java编程基础', '55', '1');
    INSERT INTO `subject` VALUES ('2', 'HTML和CSS网页技术', '60', '1');
    INSERT INTO `subject` VALUES ('3', 'C#语言开发基础', '70', '1');
    INSERT INTO `subject` VALUES ('4', '使用C#语言开发数据库应用系统', '36', '1');
    INSERT INTO `subject` VALUES ('5', '深入.NET平台和C#编程', '76', '2');
    INSERT INTO `subject` VALUES ('6', 'SqlServer数据库', '42', '2');
    INSERT INTO `subject` VALUES ('7', '基于.NET平台的软件系统分层开发', '44', '2');
    INSERT INTO `subject` VALUES ('8', '面向对象程序设计', '59', '2');
    INSERT INTO `subject` VALUES ('9', 'JavaScript案例开发', '40', '2');
    INSERT INTO `subject` VALUES ('10', '使用Java EE技术开发电子购物系统', '55', '2');
    INSERT INTO `subject` VALUES ('11', '使用ASP.NET技术开发电子购物系统', '86', '3');
    INSERT INTO `subject` VALUES ('12', '开发基于Ajax和控件技术的Web应用系', '60', '3');
    INSERT INTO `subject` VALUES ('13', '使用Java框架技术开发网上信息发布系统', '52', '3');
    INSERT INTO `subject` VALUES ('14', '使用Java企业级技术开发企业应用', '68', '3');

以下为题目(有的可能我写的有问题,有的我写了好几种语法)

-- 查询全部S1的学生信息
     select * from  student s  where s.GradeId=1
     select * from  student s2  where s2.GradeId in (select g.GradeId from grade g where g.GradeName ='s1')
    -- 查询全部S2的学生的姓名和电话
     SELECT StudentName,Phone FROM student WHERE GradeId=2;
     select s.StudentName ,s.Phone  from student s where  s.GradeId  in (select g.GradeId from grade g where g.GradeName='s2')
     -- 查询全部S1女同学的信息
     select * from student s where s.Sex = '女' and s.GradeId='1'
     select * from student s where  s.Sex = '女' and s.GradeId in (select  g.GradeId from grade g where g.GradeName="s1")
     -- 查询课时超过60的科目信息
     select * from subject sub where sub.ClassHour >60
     -- S2的科目名称。
     select sub.SubjectName from subject sub where sub.GradeId in (select g2.GradeId  from grade g2 where g2.GradeName="s2")
     select sub.SubjectName from subject sub left join grade g2 on sub.GradeId =g2.GradeId where g2.GradeName ='s2'
     select sub.SubjectName from subject sub where sub.GradeId =2
    -- S2男同学的姓名和住址 
     select s.StudentName ,s.Address from student s where s.GradeId =2 and s.Sex ='男'
     select s.StudentName ,s.Address from student s where s.Sex ='男'and s.GradeId in (select g.GradeId from grade g where g.GradeName='s2')
     select s.StudentName ,s.Address from student s left join grade g on s.GradeId =g.GradeId where g.GradeName ='s2' and s.sex='男'
    -- 无电子邮件的学生姓名和年级信息
     select s.StudentName,g.GradeName from student s left join grade g on g.GradeId =s.GradeId where s.Email is null or s.Email =''
     select s.StudentName,g.GradeName from student s,grade g where g.GradeId =s.GradeId and s.Email=""
     SELECT StudentName,GradeName FROM student,grade WHERE student.GradeId=grade.GradeId AND Email='';
     -- 出生日期在1993年之后的S2的学生姓名和年级信息
     select s.StudentName,g.GradeName from student s left join grade g  on s.GradeId =g.GradeId where s.BornDate>'1993' and s.GradeId ='2'
     select s.StudentName,g.GradeName from student s , grade g  where s.GradeId =g.GradeId and s.BornDate>'1993' and s.GradeId in (select GradeId  from grade g2 where g2.GradeName='s2')
     SELECT StudentName,GradeName FROM student,grade WHERE student.GradeId=grade.GradeId AND student.GradeId=2 AND SUBSTRING(BornDate FROM 1 FOR 4)>'1993'
     select SUBSTRING(BornDate FROM 1 FOR 4) from student s2 
     -- 参加了日期为2013年11月15日的“HTML和CSS网页技术”科目考试的成绩信息
     select * from `result` r where r.SubjectId in (select sub.SubjectId from subject sub where sub.SubjectName ='HTML和CSS网页技术') and substring(r.ExamDate from 1 for 10)='2013/11/15' 
     select * from `result` r where r.SubjectId =2 and substring(r.ExamDate from 1 for 10) ='2013/11/15'
     select * from `result` r where r.SubjectId =2 and substring(r.ExamDate from 1 for 10) ='2013/11/15'
     SELECT * FROM `result` WHERE SUBSTRING(ExamDate FROM 1 FOR 10)='2013/11/15'  AND SubjectId in(SELECT SubjectId FROM
    `subject` WHERE SubjectName LIKE 'HTML和CSS网页技术');
     -- 按照出生日期查询S1的学生信息
     select * from student s where s.GradeId  in (select  g.GradeId from grade g where g.GradeName='s2') order by s.BornDate  desc
     select * from student s where s.GradeId  =2 order by s.BornDate
     select s.* from student s,grade g where s.GradeId =g.GradeId and g.GradeName ='s2' order by s.BornDate
     select * from student s left join grade  g on g.GradeId =s.GradeId  where g.GradeName ='s2' order by s.BornDate
     -- 按日期由前到后、成绩由高到低的次序查询参加编号为1的科目考试信息
     select * from `result` r where r.SubjectId =1 order by r.ExamDate,r.StudentResult desc
     SELECT * FROM result WHERE SubjectId=1 ORDER BY StudentResult desc,ExamDate ;
     -- 查询2013年3月22日参加“面向对象程序设计”考试的前5名学员成绩信息
     select stu.studentName,r.StudentResult from `result` r,student stu
    where r.SubjectId =(select sub.subjectId from subject sub where subjectName='面向对象程序设计') 
     and substring(r.ExamDate from 1 for 10)='2013/3/22'
     and stu.StudentNo =r.StudentNo 
     order by r.StudentResult desc
     limit 0,5
     
    SELECT  r.StudentResult  FROM result r 
    WHERE SubjectId in(SELECT SubjectId FROM `subject` WHERE SubjectName='面向对象程序设计')
    AND SUBSTRING(ExamDate FROM 1 FOR 10)='2013/3/22' 
    order by r.StudentResult desc
    limit 0,5;
    
    -- 查询s2的课时最多的科目名称及课时
    select sub.SubjectName,max(sub.ClassHour) from subject sub
    where sub.GradeId ='2'
    select sub.SubjectName,max(sub.ClassHour) from subject sub
    where sub.GradeId in(select g.GradeId  from grade g where g.GradeName ='s2')
    SELECT SubjectName,MAX(ClassHour) FROM `subject` where GradeId=(SELECT GradeId FROM grade WHERE gradename='S2');
    -- 查询年龄最小的学生所在的年级及姓名
    SELECT GradeName,StudentName FROM grade,student order by BornDate desc LIMIT 1;
    -- 查询2013年3月22日参加考试的最低分出现在哪个科目
    select s.SubjectName ,r.StudentResult from  `result` r,subject s  where r.SubjectId =s.SubjectId and substring(r.ExamDate from 1 for 10)='2013/3/22' order by r.StudentResult limit 0,1;
    SELECT subjectname FROM `subject`  WHERE subjectid=(select subjectid from result ORDER BY studentresult limit 1);
    -- 查询学号为“S1101003”的学生参加过的所有考试信息，并按照时间前后次序显示
    select * from `result` r where r.StudentNo ='S1101003' order by r.ExamDate desc 
    select * from `result` r where r.StudentNo in(select StudentNo from student s where s.StudentNo='S1101003' )
    order by r.ExamDate desc 
    -- 查询学号为“S1101003”的学生参加过的所有考试中最高分及时间、科目
    select max(r.StudentResult),r.ExamDate,sub.SubjectName from `result` r,subject sub where r.SubjectId =sub.SubjectId  and r.StudentNo ='S1101003' 
    select r.StudentResult,r.ExamDate,sub.SubjectName from `result` r,subject sub where r.SubjectId =sub.SubjectId  and r.StudentNo ='S1101003' order by r.StudentResult  desc limit 1
    SELECT StudentResult,ExamDate,SubjectName FROM result r,`subject` sub where r.SubjectId =sub.SubjectId  and r.StudentNo ='S1101003' 
    -- 查询年龄超过20周岁的s3的学生信息
    SELECT * FROM student  WHERE (YEAR(CURDATE())-SUBSTRING(BornDate FROM 1 FOR 4))>20 AND gradeid in(SELECT gradeid FROM grade WHERE gradename='S3');
    -- 查询1月份过生日的学生信息
    select * from student s where substring(s.BornDate from 6 for 1)='1' 
    -- 查询今天过生日的学生姓名及所在年级
    SELECT StudentName,GradeName FROM student,grade  WHERE student.GradeId=grade.GradeId AND (SUBSTRING(BornDate FROM 6 FOR 7))=MONTH(CURTIME())
    AND (SUBSTRING(BornDate FROM 9 FOR 10))=DAY(CURTIME());
    -- 查询学号为“s31003007”的学生Email的域名
    select  SUBSTRING_INDEX(Email,'@', -1)  from student s where s.StudentNo ='s31003007'
    -- 查询住址为“山东”的学生姓名、电话、住址
    select s.StudentName ,s.Phone,s.Address  from student s where s.Address like '山东%'
    -- 查询名称中含有“数据库”字样科目名称、学时及所属年级，并按年级由低到高显示 
    select sub.SubjectName ,sub.ClassHour,g.GradeName from subject sub,grade g where sub.GradeId =g.GradeId 
    and sub.SubjectName  like '%数据库%' order by g.GradeId 
    select SubjectName classhour,gradeid from subject where subjectname like'%数据库%' order by gradeid
    -- 查询电话中含有以“1387”开头的学生姓名，住址和电话
    select s.StudentName ,s.Address ,s.Phone from student s where s.phone like '1387%'
    -- 查询姓名为“姜*”的学号、姓名和住址，其中*代表一个字  
    select s.StudentNo  ,s.StudentName,s.Address from student s where s.StudentName like '姜_'
    -- 查询学号为S1101004的学员的科目编号为1、2、3的考试成绩信息。
    select studentresult from result where studentno='s1101004'and subjectid =1 or subjectid =2 or subjectid =3
    -- 查询出生日期中1989/1/1到1995/12/31之间的学生信息
    select * from student s where s.BornDate  between '1989/1/1' and '1995/12/31' 
    -- 统计数据库中学生总人数
    select count(*)总人数 from student
    -- 查询s1年级的总学时 
    select sum(sub.ClassHour) from subject sub where sub.GradeId =1
    select sum(ClassHour) as S1年级的总学时 from subject WHERE GradeId =(SELECT GradeId FROM grade WHERE GradeName='S1');
    -- 学号为S1101004的学生第一学期内考试总成绩
    select sum(r.StudentResult)as 第一学期总成绩 from student s,grade g ,`result` r where s.StudentNo =r.StudentNo and g.GradeId =s.GradeId and s.StudentNo ='S1101004' and g.GradeId =1
    -- 学号为S1101004的学生第一学期所有考试的平均分
    select avg(r.StudentResult)as 第一学期总成绩 from student s,grade g ,`result` r where s.StudentNo =r.StudentNo and g.GradeId =s.GradeId and s.StudentNo ='S1101004' and g.GradeId =1
    -- 查询2013年3月22日的科目“C#语言开发基础”的最高分、最低分、平均分
    select max(r.StudentResult)最高分,min(r.StudentResult)最低分,avg(r.StudentResult)平均分,sum(r.StudentResult)总分,count(r.StudentResult)次数  from subject sub,`result` r
    where r.SubjectId =sub.SubjectId 
    and sub.SubjectName ='C#语言开发基础'
    and  substring(r.ExamDate from 1 for 10)='2013/3/22' 
    -- 查询2013年3月22日科目“C#语言开发基础”的及格学生的平均分
    select  avg(r.StudentResult) from subject sub,`result` r
    where r.SubjectId =sub.SubjectId 
    and  sub.SubjectName ='C#语言开发基础'
    and  substring(r.ExamDate from 1 for 10)='2013/3/22' 
    and  r.StudentResult>60
    -- 查询所有参加“Java编程基础”科目考试平均分
    select avg(r.StudentResult) from `result` r,subject sub
    where r.SubjectId =sub.SubjectId 
    and sub.SubjectName ='Java编程基础'
    -- 查询每个学期的总学时数，并按照升序排列。
    select  g.GradeName ,sum(sub.ClassHour)时间 from subject sub,grade g 
    where sub.GradeId =g.GradeId
    group by g.GradeId 
    order by 时间 desc
    -- 查询每个参加考试的学员的平均分
    select s.StudentName ,s.StudentNo ,avg(r.StudentResult) from `result` r,student s 
    where r.StudentNo=s.StudentNo group  by s.studentno 
    select studentno as 学号,avg(studentresult)as 平均分 from result group by studentno;
    --  查询每门课程的平均分，并按照降序排列
    select sub.SubjectName ,avg(r.StudentResult)平均分 from `result` r ,subject sub where r.SubjectId =sub.SubjectId  
    group by sub.SubjectId order by 平均分 desc
    select SubjectName as 课程名,avg(studentresult)as 平均分 from `subject`,result WHERE `subject`.SubjectId=result.SubjectId group by result.subjectid order by avg(studentresult) desc;
    --  查询每个学生参加的所有考试的总分，并按照降序排列
    select s.StudentNo ,s.StudentName ,sum(r.StudentResult)总分 from `result` r ,student s where r.StudentNo =s.StudentNo 
    group by s.StudentNo order by 总分 desc
    select studentno as 学号,sum(studentresult)as 总分 from result group by studentno order by 总分 desc
    -- 查询每学期学时数超过50的课程数。
    select g.GradeName ,count(*) from subject sub,grade g where sub.GradeId =g.GradeId and sub.ClassHour >50
    group by g.GradeId
    -- 查询每学期学生的平均年龄。
    select  g.GradeName ,avg((YEAR(CURDATE())-SUBSTRING(s.BornDate from 1 for 4))) from student s,grade g where s.GradeId =g.GradeId
    group by g.GradeId 
    
    select  GradeName as 年级,avg((YEAR(CURDATE())-SUBSTRING(BornDate FROM 1 FOR 4)))as 平均年龄 from student,grade
    WHERE student.gradeid=grade.GradeId
    group by student.gradeid;
    -- 查询北京地区的每学期学生人数
    select g.GradeName,count(*) from student s,grade g  where s.GradeId =g.GradeId 
    and s.Address like '%北京%'
    group by g.GradeId 
    
    select GradeName as 年级,count(*) as 北京地区总人数 from student,grade
    where address like '%北京%' AND student.gradeid=grade.GradeId
    group by student.gradeid;
    
    -- 查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列
    select * from student s,`result` r where s.StudentNo=r.StudentNo 
    having avg(r.StudentResult)
    -- 查询考试日期2013-3-22的课程的及格平均分
    select * from `result` r,student s
    where s.StudentNo =r.StudentNo 
    and substring(r.ExamDate from 1 for 10) ='2013/3/22'
    group by r.StudentNo 
    having  avg(r.StudentResult)>=60
    
    -- 查询考试日期2013-3-22的课程的及格平均分最高分的前三个
    select * from result,student
    WHERE student.studentno=result.StudentNo
    and substring(result.ExamDate from 1 for 10) ='2013/3/22'
    group by result.StudentNo
    having avg(studentresult)>=60
    order by avg(studentresult) desc
    limit 0,3
    -- 查询参加考试至少一次考试不及格的学生学号、不及格次数
    select s.StudentNo,count(*)不及格次数 from result r,student s
    WHERE s.studentno=r.StudentNo and r.StudentResult <60
    group by s.StudentNo 
    having 不及格次数>=1
    -- 查询学生姓名、所属年级名称及联系电话。
    select s.StudentName ,g.GradeName ,s.Address 
    from student s 
    left join grade g
    on s.GradeId =g.GradeId 
    -- 查询年级编号为1的科目名称、年级名称及学时。
    select sub.SubjectName ,g.GradeName ,sub.ClassHour 
    from grade g,subject sub 
    where g.GradeId =sub.GradeId and sub.GradeId =1
    
    -- 查询参加科目编号为1的考试的学生姓名、分数、考试日期
    select s.StudentName ,r.StudentResult ,r.ExamDate from student s ,subject sub,`result` r 
    where s.StudentNo =r.StudentNo  and r.SubjectId =sub.SubjectId 
    and sub.SubjectId =1
    -- 查询学号为S1101007的学生参加的考试科目名称、分数、考试日期
    select sub.SubjectName ,r.StudentResult ,r.ExamDate from student s,`result` r ,subject sub
    where s.StudentNo =r.StudentNo and sub.SubjectId =r.SubjectId and s.StudentNo ='S1101007'
    -- 查询参加考试的学生学号、所考科目名称、分数、考试日期.
    select s.StudentNo,sub.SubjectName ,r.StudentResult ,r.ExamDate from student s,`result` r ,subject sub
    where s.StudentNo =r.StudentNo and sub.SubjectId =r.SubjectId  
    -- 查询学生学号、姓名、考试科目名称及成绩。
    select s.StudentNo ,s.StudentName ,sub.SubjectName ,r.StudentResult from student s,subject sub,`result` r 
    where sub.SubjectId =r.SubjectId  and s.StudentNo =r.StudentNo 
    -- 查询参加“Java编程基础”考试的学生姓名、成绩、考试日期
    select s.StudentName  ,r.StudentResult,r.ExamDate from student s,subject sub,`result` r 
    where sub.SubjectId =r.SubjectId  and s.StudentNo =r.StudentNo and sub.SubjectName ="Java编程基础"
    -- 查询所有科目的参考信息（某些科目可能还没有被考试过）
    select j.subjectname as "科目名称", r.studentno as"学号", r.studentresult as "分数" 
    from subject as j left outer join result as r on j.subjectid=r.subjectid;
    -- 查询没有被考过的科目信息。
    select j.subjectname  "科目名称" from result  r 
    right  join subject  j on j.subjectid=r.subjectid 
    where r.studentresult is null and r.studentno is null;
    -- 查看所有学生的姓名班级,如果班级为1表示一年级,2表示二年级,3表示三年级
    
    select 
    s.StudentName, 
    CASE 
    WHEN s.GradeId = 1 THEN '一年级' 
    WHEN s.GradeId = 2 THEN '二年级' 
    WHEN s.GradeId = 3 THEN '三年级' 
    ELSE '其他' END 
    from student s
    
    -- 查看s1的所有女同学 
    select count(*) from student s where s.GradeId =(select g.GradeId from grade g where g.GradeName='s1')
    -- 查看不在地址不在北京的有多少人
    select count(*) from student s where s.Address  not like '%北京%'
    -- 查看s1,s2共多少人
    select count(*) from student s where s.GradeId in (select g.GradeId from grade g where g.GradeName='s1' or g.GradeName='s2'  )
    select count(*) from student s where s.GradeId in (1,2)
    select count(*) from student s where s.GradeId between 1 and 2 
    -- 名字里有几个有丽的同学 
    select count(*) from student s where s.StudentName   like '%丽%' 
    -- 名字里有丽的同学都是谁
    select s.StudentName  from student s where s.StudentName   like '%丽%'

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/66de3d7d088246faae8f6e92cd7bf5d8.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/4b27cc8fcc4244138d981851c4c4ebf1.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/9ed83cc0fb1446c0b3e760fea5f37de2.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/ce3822178f8040bfada4e3f12c925700.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjcxMzUwOA==,size_16,color_FFFFFF,t_70