mysql 练习题（持续更新中）

傷城~ 2022-02-27 03:42 307阅读 0赞

### 表名与字段名：  ###

–1.学生表 
    Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 
    –2.课程表 
    Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
    –3.教师表 
    Teacher(t_id,t_name) –教师编号,教师姓名 
    –4.成绩表 
    Score(s_id,c_id,s_score) –学生编号,课程编号,分数

### sql脚本：  ###

/*
    Navicat MySQL Data Transfer
    
    Source Server         : 192.168.160.132
    Source Server Version : 50725
    Source Host           : 192.168.160.132:3306
    Source Database       : test
    
    Target Server Type    : MYSQL
    Target Server Version : 50725
    File Encoding         : 65001
    
    Date: 2019-03-26 17:28:51
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for Course
    -- ----------------------------
    DROP TABLE IF EXISTS `Course`;
    CREATE TABLE `Course` (
      `c_id` varchar(20) NOT NULL,
      `c_name` varchar(20) NOT NULL DEFAULT '',
      `t_id` varchar(20) NOT NULL,
      PRIMARY KEY (`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of Course
    -- ----------------------------
    INSERT INTO `Course` VALUES ('01', '语文', '02');
    INSERT INTO `Course` VALUES ('02', '数学', '01');
    INSERT INTO `Course` VALUES ('03', '英语', '03');
    
    -- ----------------------------
    -- Table structure for Score
    -- ----------------------------
    DROP TABLE IF EXISTS `Score`;
    CREATE TABLE `Score` (
      `s_id` varchar(20) NOT NULL,
      `c_id` varchar(20) NOT NULL,
      `s_score` int(3) DEFAULT NULL,
      PRIMARY KEY (`s_id`,`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of Score
    -- ----------------------------
    INSERT INTO `Score` VALUES ('01', '01', '80');
    INSERT INTO `Score` VALUES ('01', '02', '90');
    INSERT INTO `Score` VALUES ('01', '03', '99');
    INSERT INTO `Score` VALUES ('02', '01', '70');
    INSERT INTO `Score` VALUES ('02', '02', '60');
    INSERT INTO `Score` VALUES ('02', '03', '80');
    INSERT INTO `Score` VALUES ('03', '01', '80');
    INSERT INTO `Score` VALUES ('03', '02', '80');
    INSERT INTO `Score` VALUES ('03', '03', '80');
    INSERT INTO `Score` VALUES ('04', '01', '50');
    INSERT INTO `Score` VALUES ('04', '02', '30');
    INSERT INTO `Score` VALUES ('04', '03', '20');
    INSERT INTO `Score` VALUES ('05', '01', '76');
    INSERT INTO `Score` VALUES ('05', '02', '87');
    INSERT INTO `Score` VALUES ('06', '01', '31');
    INSERT INTO `Score` VALUES ('06', '03', '34');
    INSERT INTO `Score` VALUES ('07', '02', '89');
    INSERT INTO `Score` VALUES ('08', '03', '90');
    
    -- ----------------------------
    -- Table structure for Student
    -- ----------------------------
    DROP TABLE IF EXISTS `Student`;
    CREATE TABLE `Student` (
      `s_id` varchar(20) NOT NULL,
      `s_name` varchar(20) NOT NULL DEFAULT '',
      `s_birth` varchar(20) NOT NULL DEFAULT '',
      `s_sex` varchar(10) NOT NULL DEFAULT '',
      PRIMARY KEY (`s_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of Student
    -- ----------------------------
    INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01', '男');
    INSERT INTO `Student` VALUES ('02', '钱电', '1990-12-21', '男');
    INSERT INTO `Student` VALUES ('03', '孙风', '1990-05-20', '男');
    INSERT INTO `Student` VALUES ('04', '李云', '1990-08-06', '男');
    INSERT INTO `Student` VALUES ('05', '周梅', '1991-12-01', '女');
    INSERT INTO `Student` VALUES ('06', '吴兰', '1992-03-01', '女');
    INSERT INTO `Student` VALUES ('07', '郑竹', '1989-07-01', '女');
    INSERT INTO `Student` VALUES ('08', '王菊', '1990-01-20', '女');
    
    -- ----------------------------
    -- Table structure for Teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `Teacher`;
    CREATE TABLE `Teacher` (
      `t_id` varchar(20) NOT NULL,
      `t_name` varchar(20) NOT NULL DEFAULT '',
      PRIMARY KEY (`t_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of Teacher
    -- ----------------------------
    INSERT INTO `Teacher` VALUES ('01', '张三');
    INSERT INTO `Teacher` VALUES ('02', '李四');
    INSERT INTO `Teacher` VALUES ('03', '王五');

### 练习题及sql答案： （本人自己写的sql，仅供参考，也许你有更好的写法） ###

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数  
    SELECT s.*,sc.s_score,sc1.s_score from 
     Student s 
    	left join Score sc  on s.s_id=sc.s_id and sc.c_id='01' 
    	left join Score sc1 on s.s_id=sc1.s_id and sc1.c_id='02' 
    where sc.s_score>sc1.s_score
    
    -- 2、查询学生男性和女性分别多少人？（这个笔试题遇到过）
    写法一：
    SELECT s_sex,count(*) from Student GROUP BY s_sex
    
    写法二:
    select count(s1.s_id) as nan,count(s2.s_id) as nv
    from Student s
    	 left join Student s1 on s.s_id=s1.s_id and s1.s_sex='男'
    	 left join Student s2 on s.s_id=s2.s_id and s2.s_sex='女'
    
    -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（这个笔试题遇到过）
    select s.s_id,s.s_name,ROUND(avg(sc.s_score),1)
    from Student s left join Score sc
    on s.s_id=sc.s_id
    group by s.s_id,s.s_name
    having avg(sc.s_score)>60
    注：因为分数一般保留1位小数，所以这里用ROUND()函数将其平均成绩保留一位小数；
    
    -- 4、查询"李"姓老师的数量 （这个笔试题遇到过）
    select count(*)
    from Teacher t
    where t.t_name like '李%'
    
    -- 5、查询每门课程被选修的学生数 
    select c_id,count(s_id)
    from Score 
    group by c_id
    
    -- 6、查询选修了全部课程的学生信息 
    写法一：
    select * 
    from Student where s_id in (
    	select sc.s_id
    	from Score sc
    	group by sc.s_id
    	having count(*)=(select count(c_id) from Course))
    
    写法二：显示的内连接，一般称为内连接，有INNER JOIN
    SELECT * 
    from Student inner join (SELECT Score.s_id from  Score 
    group by Score.s_id HAVING COUNT(Score.c_id)=(SELECT COUNT(1) FROM Course)) t
    on  Student.s_id=t.s_id
    
    
    写法三：其实与写法二意思一样，只是隐式的内连接，不需要INNER JOIN
    SELECT * from Student,(SELECT Score.s_id from  Score group by Score.s_id HAVING COUNT(Score.c_id)=(SELECT COUNT(1) FROM Course)) t
    where  Student.s_id=t.s_id
    
    
    -- 7、查询老师"张三"教的学生信息
    select s.*
    from Student s left join Score sc
    on s.s_id=sc.s_id
    left join Course c
    on sc.c_id =c.c_id
    left join Teacher t
    on c.t_id=t.t_id
    where t.t_name='张三'
    
    -- 8、查询每门选修课最高的分数、平均分、最低分；
    写法一：
    SELECT c_id,max(s_score),avg(s_score),min(s_score)
    FROM Score
    group by c_id
    
    写法二：
    select c.c_name,max(s_score),AVG(s_score),MIN(s_score)
    from Score sc left join Course c
    on sc.c_id=c.c_id
    GROUP BY c.c_name
    
    -- 9、查询老师"张三"教的学生中该选修课成绩最高的分数和最低的分数
    写法一：
    select t.t_name,c.c_name,max(sc.s_score),min(sc.s_score)
    from Score sc left join Course c
    on sc.c_id=c.c_id
    left join Teacher t
    ON c.t_id=t.t_id
    group by t.t_name,c.c_name
    having t.t_name='张三'
    
    写法二：
    SELECT Course.c_name,Course.t_id,MAX(Score.s_score),MIN(Score.s_score)
    FROM Score,Course 
    where Score.c_id=Course.c_id
    GROUP BY Course.c_name,Course.t_id
    HAVING Course.t_id=(SELECT t_id FROM Teacher where t_name="张三")
    
    -- 10、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
    写法一：
    select *
    from Student where s_id in(
    	select sc.s_id
    	from Score sc
    	where sc.s_id !='01' and sc.c_id in (select c_id from Score where s_id='01')
    	group by sc.s_id
    	having count(c_id)=(select count(c_id) from Score where s_id='01')
    注：课程完全相同，那么必须最少满足两个条件：1、课程的数量相同 2、课程必须在01号同学学习的课程当中
    
    写法二：
    SELECT  Student.*
    FROM Student LEFT JOIN Score
    on Student.s_id=Score.s_id
    where Student.s_id !=01
    GROUP BY Student.s_id
    HAVING count(Score.c_id)= (SELECT count(c_id) FROM Score WHERE s_id=01)
    
    
    -- 11、查询没有学过'张三'老师任何一门课的学生
    SELECT * from Student where s_id not in(
      SELECT s.s_id
    	from Teacher t INNER JOIN Course c
    	on t.t_id=c.c_id
    	INNER JOIN Score s
    	on c.c_id=s.c_id
    	where t.t_name='张三'
    )
    思路：这个思路很简单，先用Teacher 、Course 、Score 三表进行内连接关联，条件查询出'张三'老师教过的学生，然后用子查询，查询学生表中id不在子查询查询结果集中的；
    
    
    
    -- 12、查询大于28岁的学生信息
    写法一：
    select s.*
    from Student s
    where DATE_FORMAT(NOW(),'%Y-%m-%d')-s.s_birth >28
    
    写法二：
    SELECT *,DATE_ADD(s_birth,INTERVAL 28 YEAR) as newDate
    FROM Student
    GROUP BY s_id
    HAVING newDate>NOW()
    
    -- 13、查询每门课程都在70分以上的学生信息
    SELECT Student.* FROM Student where s_id in (
    	SELECT s_id
    	FROM Score 
    	where s_score>70 
    	GROUP BY s_id
    	HAVING COUNT(s_score)=(SELECT count(*) FROM Course)
    )
    
    -- 14、查询既是'张三'又是'王五'的学生信息
    SELECT * from Student where s_id in(
    	SELECT s.s_id
    	from Teacher t INNER JOIN Course c
    	on t.t_id=c.c_id
    	INNER JOIN Score s
    	on c.c_id=s.c_id
    	where t.t_name in ('张三','王五')
    	GROUP BY s.s_id
    	HAVING count(c.c_id)=2
    )
    
    思路：先用Teacher 、Course 、Score 三表进行内连接关联，然后条件查询教师名称在('张三','王五')
    之中，因为既是'张三'又是'王五'的学生，那么根据学生进行分组，再对结果用HAVING 条件查询出课程编
    号有2个的学生id，那么这个学生肯定既是'张三'又是'王五'。