Mysql一些有用的查询

===mysql的一些查询笔记

SELECT *FROM zjw_course;

SELECT * FROM zjw_teacher;

SELECT * FROM zjw_score;

Q1 :查询 "01" 课程比 "02" 课程成绩高的学生的信息及课程分数 
SELECT 
  s.s_id,
  s.s_name,
  temp.ss1,
  temp.ss2 
FROM
  zjw_student AS s 
  INNER JOIN 
    (SELECT 
      sc1.s_id AS s_sid,
      sc1.s_score AS ss1,
      sc2.s_score AS ss2 
    FROM
      zjw_score sc1 
      INNER JOIN zjw_score sc2 
        ON sc1.c_id = '01' 
        AND sc2.c_id = '02' 
        AND sc1.s_id = sc2.s_id 
    WHERE sc1.s_score >sc2.s_score) temp 
    ON s.s_id = temp.s_sid 
    
    
    
    SELECT 
      s.s_id,
      s.s_name,zjw1.s_score AS '科目1分数',zjw2.s_score AS '科目2分数'
    FROM
      zjw_student s 
      LEFT JOIN  zjw_score zjw1
        ON zjw1.s_id =s.s_id 
        AND zjw1.c_id = '01' 
      LEFT JOIN zjw_score zjw2 
        ON zjw2.s_id =s.s_id 
        AND zjw2.c_id='02'
       WHERE zjw1.s_score>zjw2.s_score 
       
       
       
SELECT st.*,sc.s_score AS '语文' ,sc2.s_score '数学' 
FROM zjw_student st
LEFT JOIN zjw_score sc ON sc.s_id=st.s_id AND sc.c_id='01' 
LEFT JOIN zjw_score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'  
WHERE sc.s_score>sc2.s_score

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
       
 SELECT st.*,sc.s_score AS '语文' ,sc2.s_score '数学' 
FROM zjw_student st
LEFT JOIN zjw_score sc ON sc.s_id=st.s_id AND sc.c_id='01' 
LEFT JOIN zjw_score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'  
WHERE sc.s_score<sc2.s_score 

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩   


SELECT st.s_id, st.s_name, AVG(sc.s_score)AS '平均成绩' FROM zjw_student st LEFT JOIN zjw_score sc
ON sc.s_id=st.s_id GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;
       
 -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
 
 
 SELECT st.s_id,st.s_name, COUNT(sc.c_id) AS '选课总数' ,
 SUM(
  CASE
      WHEN sc.s_score IS NULL 
      THEN 0
      ELSE
      sc.s_score
      END 
 ) AS '总成绩'
 FROM zjw_student st LEFT JOIN zjw_score sc
 ON sc.`s_id`=st.`s_id`
 GROUP BY st.s_id;
       
 -- 7、查询学过"张三"老师授课的同学的信息 
 
 SELECT *FROM zjw_course;
 
 SELECT  *FROM zjw_student;
 
 SELECT * FROM zjw_teacher;
 
 SELECT st.s_id,st.s_name FROM zjw_student st 
 LEFT JOIN zjw_score sc
 ON sc.s_id=st.s_id
 LEFT JOIN
 zjw_course c
 ON c.`c_id`=sc.`c_id`
 LEFT JOIN zjw_teacher t
 ON c.`t_id`=t.`t_id`
 WHERE t.`t_name`='张三'
 
 -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
 
 SELECT st.s_id,st.`s_name` FROM zjw_student st INNER JOIN zjw_score sc1
 ON sc1.s_id=st.s_id AND sc1.c_id='01'
 INNER JOIN zjw_score sc2
 ON sc2.s_id=st.`s_id` AND sc2.c_id='02'
 
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
  SELECT DISTINCT st.s_id,st.`s_name` FROM zjw_student st INNER JOIN zjw_score sc1
 ON sc1.s_id=st.s_id AND sc1.c_id='01'
 INNER JOIN zjw_score sc2
 ON sc2.s_id=sc1.`s_id` AND sc2.c_id != '02' 
 
 
--  下面是非常重要的排名查询

-- 1.总成绩排名前三的学生

SELECT *FROM zjw_score;

SELECT s_id,SUM(s_score) AS count_score  FROM zjw_score
GROUP BY s_id
ORDER BY count_score DESC LIMIT 3;

-- set @curr_cnt :=0,@prev_cnt:=0,@rank :=0
-- select s_id,
   --  @curr_cnt := SUM(s_score) AS cnt
   -- @rank     := IF(@prev_cnt<>@curr_cnt,@rank+1,@rank)AS rank, 
   --  @prev_cnt := @curr_cnt AS dummy   
-- from zjw_score
-- GROUP BY s_id
-- ORDER BY cnt DESC;

-- 2.找出各科成绩最好的学生
    SELECT a.c_id,a.s_id,b.max_score FROM zjw_score a INNER JOIN
   (SELECT c_id,MAX(s_score)AS max_score FROM zjw_score  GROUP BY c_id)b
    ON a.`c_id`=b.c_id AND a.s_score=b.max_score;
    
-- 3.找出各科成绩前两名的学生

    -- 法一、使用自身左连接
         SELECT s_id,c_id,s_score FROM 
        (SELECT a.* FROM zjw_score a LEFT JOIN zjw_score b
        ON a.`c_id`=b.`c_id` WHERE a.`s_score`<=b.`s_score` 
        ORDER BY a.`s_score` DESC)temp
        GROUP BY s_id,c_id,s_score
        HAVING COUNT(c_id)<=2
        ORDER BY c_id
        
    -- 法二
    
        SELECT a.c_id,a.s_id,a.s_score FROM zjw_score a LEFT JOIN zjw_score  b
        ON a.`c_id`=b.`c_id` WHERE a.`s_score`<=b.`s_score`
        GROUP BY a.s_id,a.c_id,a.s_score
        HAVING COUNT(a.c_id)<=2  
        ORDER BY a.c_id```
======///下面是表结构和数据///===================

create table `zjw_course` (
	`c_id` varchar (60),
	`c_name` varchar (60),
	`t_id` varchar (60)
); 
insert into `zjw_course` (`c_id`, `c_name`, `t_id`) values('01','语文','02');
insert into `zjw_course` (`c_id`, `c_name`, `t_id`) values('02','数学','01');
insert into `zjw_course` (`c_id`, `c_name`, `t_id`) values('03','英语','03');


create table `zjw_score` (
	`s_id` varchar (60),
	`c_id` varchar (60),
	`s_score` int (3)
); 
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('01','01','80');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('01','02','65');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('01','03','99');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('02','01','70');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('02','02','60');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('02','03','80');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('03','01','80');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('03','02','80');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('03','03','80');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('04','01','50');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('04','02','30');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('04','03','20');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('05','01','76');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('05','02','87');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('06','01','31');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('06','03','34');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('07','02','89');
insert into `zjw_score` (`s_id`, `c_id`, `s_score`) values('07','03','98');

create table `zjw_student` (
	`s_id` varchar (60),
	`s_name` varchar (60),
	`s_birth` varchar (60),
	`s_sex` varchar (30)
); 
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('01','赵雷','1990-01-01','男');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('02','钱电','1990-12-21','男');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('03','孙风','1990-05-20','男');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('04','李云','1990-08-06','男');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('05','周梅','1991-12-01','女');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('06','吴兰','1992-03-01','女');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('07','郑竹','1989-07-01','女');
insert into `zjw_student` (`s_id`, `s_name`, `s_birth`, `s_sex`) values('08','王菊','1990-01-20','女');
create table `zjw_teacher` (
	`t_id` varchar (60),
	`t_name` varchar (60)
); 
insert into `zjw_teacher` (`t_id`, `t_name`) values('01','张三');
insert into `zjw_teacher` (`t_id`, `t_name`) values('02','李四');
insert into `zjw_teacher` (`t_id`, `t_name`) values('03','王五');






```cpp