Mysql练习3

---

title: Mysql练习3
time: 2019年8月17日16:11:30
tags: Mysql

---

1. 查询男生、女生人数(不重点)

   SELECT
   	sum( CASE WHEN s_sex = '男' THEN 1 ELSE 0 END )男生,
   	sum( CASE WHEN s_sex = '女' THEN 1 ELSE 0 END ) 女生 
   FROM
   	student
   

2. 查询名字中含有"风"字的学生信息（不重点）

   SELECT
   	* 
   FROM
   	student 
   WHERE
   	s_name LIKE '%风%'
   

3. 查询1990年出生的学生名单（重点year）

   -- 第一种方法
       SELECT
           * 
       FROM
           student 
       WHERE
           s_birth LIKE '1990%'
   -- 第二种方法
   	SELECT
           * 
       FROM
           student 
       WHERE
           YEAR ( s_birth ) = '1990'
   

4. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

   SELECT
   	s.s_id 学号,
   	st.s_name 姓名,
   	avg(s.s_score ) 平均成绩 
   FROM
   	score AS s
   	INNER JOIN student AS st ON s.s_id = st.s_id 
   GROUP BY
   	s.s_id 
   HAVING
   	平均成绩 >= 85
   

5. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

   SELECT
   	c_id,
   	avg( s_score ) 
   FROM
   	score 
   GROUP BY
   	c_id 
   ORDER BY
   	avg( s_score ) ASC,
   	c_id DESC
   

6. 查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）

   SELECT
   	s.s_id,
   	st.s_name,
   	s.s_score 
   FROM
   	score AS s
   	INNER JOIN student AS st ON s.s_id = st.s_id 
   WHERE
   	c_id = ( SELECT c_id FROM course WHERE c_name = '数学' ) 
   	AND s_score < 60
   

7. 查询所有学生的课程及分数情况（重点）

   SELECT
   	s.s_id 学号,
   	st.s_name 姓名,
   	max( CASE WHEN c.c_name = '数学' THEN s.s_score ELSE NULL END ) 数学,
   	max( CASE WHEN c.c_name = '语文' THEN s.s_score ELSE NULL END ) 语文,
   	max( CASE WHEN c.c_name = '英语' THEN s.s_score ELSE NULL END ) 英语 
   FROM
   	score AS s
   	INNER JOIN student AS st ON st.s_id = s.s_id
   	INNER JOIN course AS c ON s.c_id = c.c_id 
   GROUP BY
   	s.s_id
   

8. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

   SELECT
   	s.s_id,
   	st.s_name,
   	c.c_name,
   	s.s_score 
   FROM
   	score AS s
   	INNER JOIN student AS st ON st.s_id = s.s_id
   	INNER JOIN course AS c ON c.c_id = s.c_id 
   WHERE
   	s_score > 70 
   ORDER BY
   	s_name
   

9. 查询不及格的课程并按课程号从大到小排列(不重点)

   SELECT
   	* 
   FROM
   	score 
   WHERE
   	s_score < 60 
   ORDER BY
   	c_id DESC
   

10. 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

    SELECT
    s.s_id 学号,
    st.s_name 姓名
    FROM score as s 
    inner JOIN student as st
    on s.s_id = st.s_id
    WHERE c_id ='03' and s_score>= 80
    

11. 求每门课程的学生人数（不重要）

    SELECT
    	c.c_name,
    	count( s.s_id ) 
    FROM
    	score AS s
    	INNER JOIN course AS c ON s.c_id = c.c_id 
    GROUP BY
    	s.c_id
    

12. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

    -- 第一种方法
        SELECT
            s.s_id,
            st.s_name,
            MAX( s.s_score ) 
        FROM
            score AS s
            INNER JOIN student AS st ON s.s_id = st.s_id
            INNER JOIN course AS c ON s.c_id = c.c_id
            INNER JOIN teacher AS t ON c.t_id = t.t_id 
        WHERE
            t.t_name = '张三'
    
    	-- 第二种方法
    -- SQL server 用 top1
    -- MySQL 用 limit
        SELECT
            -- TOP 1
            s.s_id,
            st.s_name,
            s.s_score 
        FROM
            score AS s
            INNER JOIN student AS st ON s.s_id = st.s_id
            INNER JOIN course AS c ON s.c_id = c.c_id
            INNER JOIN teacher AS t ON c.t_id = t.t_id 
        WHERE
            t.t_name = '张三' 
        ORDER BY
            s.s_score DESC 
            LIMIT 0,
            1 -- 从下标为0 的取 一个