mysql（4.有关sql查询练习题1）

创建几张表如下代码如下：

创建如下几张表

course表：

score表：

student表：

teacher

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(10) NOT NULL,
  `c_name` varchar(255) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '1');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sc_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(10) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `s_score` double DEFAULT NULL,
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '75');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张雪峰');
INSERT INTO `teacher` VALUES ('2', '老子');
INSERT INTO `teacher` VALUES ('3', '墨子');

-- 1、查询课程编号“001”比课程编号“002”  成绩高的所有学生的学号；

--  select a.s_id from  (select s_score ,s_id from score where c_id='1') as a,
   (select s_score,s_id from score where c_id='2')as b where a.s_id=b.s_id AND a.s_score>b.s_score


2、查询平均成绩大于60分的同学的学号和平均成绩;

   select s_id,AVG(s_score) from score GROUP BY s_id HAVING AVG(s_score)>66


 3、查询所有同学的学号、姓名、选课数、总成绩；
      #做这类题的时候先看一下题目要求牵扯到几张表，如果有几个字段都在一张表，那么
--     先在一张表中把要查的字段查出来，在连接另一张表
--     学号、选课数和总成绩在一张表中，然后再连接另一张表将所要求的字段全部查出来
--     SELECT DISTINCT s.s_name,sc.s_id,COUNT(1),SUM(s_score) from score as sc,student
        as s where sc.s_id=s.s_id GROUP BY s_id


--  4查询含有"子"的老师的个数；

--   SELECT COUNT(1) from teacher where t_name like '%子%'


5、查询没学过“老子”老师课的同学的学号、姓名；

  select DISTINCT s.s_id,s.s_name from student as s,score sc where sc.s_id not in (
     select s_id from score where c_id in
       (SELECT c.c_id from teacher t ,course c where t.t_id=c.t_id and t_name='老子')
        )