sql语句语法

╰+哭是因爲堅強的太久メ 2022-12-29 09:16 199阅读 0赞

## 手写顺序 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70][]

##  执行顺序 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 2][]

## join语句 ##

*  内连接：A、B表中共有的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 3][]

*  左连接：A表中所有的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 4][]

*  右连接:B表中所有的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 5][]

*  左连接+条件：A中有B中没有的

![20201221155636618.png][]

*  右连接+条件 ：B中有A中没有的

![20201221155716367.png][]

*  全连接：A、B表中所有数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 6][]

*  全不连接：A、B表中各自独有的

![20201221155850816.png][]

## sql语句 ##

#试题一、找出平均成绩大于60的所有学生的学号（即student_id)、姓名和平均分数
         
                SELECT a.`name`,a.student_id,AVG(b.number)
                FROM tbl_student a,tbl_score b
                WHERE a.student_id=b.`student_id`
                GROUP BY a.student_id
                HAVING AVG(b.number)>60
    #试题二、查询所有学生的学号，姓名，选课数和总成绩；
                SELECT a.`name`,a.student_id,COUNT(c.course_id),SUM(b.`number`)
                FROM tbl_student a,tbl_score b,tbl_course c
                WHERE a.student_id=b.`student_id` AND b.`course_id`=c.`course_id`
                GROUP BY a.student_id
                
                select a.student_id,b.name,COUNT(a.course_id),SUM(a.number)
                from tbl_score a inner join
                tbl_student  b on a.student_id = b.student_id
                GROUP BY a.student_id;
          
    #试题三、查找名字中含“封”字的老师的总数
                SELECT COUNT(NAME)
                FROM tbl_teacher
                WHERE NAME LIKE '%封%'
    #试题四、查询没有学过小甄老师课的同学的学号、姓名
                SELECT a.student_id ,a.name
                from tbl_student a
                where a.student_id not in
                (select student_id from tbl_score where course_id=
                 (SELECT course_id from tbl_course where teacher_id=
                  (select teacher_id from tbl_teacher where name = '小甄')))
    #试题五、查询学过"4"且学过编号"5"课程的同学的学号
                SELECT  a.student_id
                from (select * from tbl_score where course_id = 4) a
                INNER JOIN
                      (SELECT * from tbl_score where course_id = 5) b
                on a.student_id = b.student_id
                        
    #试题六、查询课程编号"1"成绩比编号"2"成绩低的学生的学号
                    
            SELECT t1.student_id
            from  (select student_id,course_id,number from tbl_score where course_id =1) t1
            INNER JOIN
                  (select student_id ,course_id,number from tbl_score where course_id =2) t2
            on t1.student_id = t2.student_id
            where t1.number < t2.number
            order by t1.student_id
        
    #试题七、找出有一门课程低于60分的学生的学号和名字
                    select b.student_id,b.`name`
                    from tbl_score a,tbl_student b
                    where a.number<60 and a.student_id =b.student_id
                    GROUP BY b.student_id
                    select  t1.student_id ,t1.name
                    from tbl_student t1
                    INNER JOIN  
                    (select student_id,MIN(number) from tbl_score
                     GROUP BY student_id
                     HAVING MIN(number)<60
                     ) t2
                     on t1.student_id = t2.student_id
    #试题八、查询选完全部课程的学生的学号
                    select a.student_id
                    from tbl_score a,tbl_course b
                    where  a.course_id=b.course_id
                    GROUP BY a.student_id
                    HAVING COUNT(a.course_id)=(select COUNT(*) from tbl_course)
                    
                    SELECT t1.student_id , count(t2.course_id) as totalCourse
                    from tbl_score t1
                    INNER JOIN
                    tbl_course t2
                    on  t1.course_id = t2.course_id
                    GROUP BY t1.student_id
                    HAVING totalCourse = (select COUNT(course_id) from tbl_course)
    #试题九、按平均成绩从高到低，显示所有学生的各科课程成绩
            SELECT *
    from tbl_student a,tbl_score b
    where a.student_id =b.student_id
    and a.student_id in(
    SELECT a.student_id
                FROM tbl_student a,tbl_score b
                WHERE a.student_id=b.`student_id`
                GROUP BY a.student_id
                ORDER BY AVG(b.number) desc
    )            
    select t.student_id , t.course_id,t.number,b.avgScore
    from tbl_score t
    LEFT JOIN (select student_id,AVG(number) as avgScore from tbl_score GROUP BY student_id) b
    on t.student_id = b.student_id
    ORDER BY avgScore desc
    #试题十、查询各科成绩的最高分和最低分及对应的学生姓名学号。
                
                SELECT  c.name,c.student_id,a.number
                from tbl_score a,
                (
                (
                SELECT MAX(number) as num ,a.course_id as course_id
                from tbl_course a,tbl_score b
                where a.course_id=b.course_id
                GROUP BY a.course_id
                )
                UNION
                (
                SELECT MIN(number) as num ,a.course_id as course_id
                from tbl_course a,tbl_score b
                where a.course_id=b.course_id
                GROUP BY a.course_id
                )
                ) b,tbl_student c
                where a.course_id=b.course_id and a.number=b.num
                 and a.student_id=c.student_id
    select *
    from
    (select a.course_id , a.student_id,b.max_number
    from tbl_score a
    INNER JOIN(select course_id , MAX(number) max_number from tbl_score GROUP BY course_id ) b
    on a.course_id = b.course_id
    where a.number = b.max_number) x1
    INNER JOIN
    (select c.course_id,c.student_id,d.min_number
      from tbl_score c
      INNER JOIN (select course_id,MIN(number) min_number from tbl_score GROUP BY course_id) d
      on c.course_id =d.course_id
      where c.number = d.min_number) x2
    on x1.course_id = x2.course_id
    ORDER BY x1.course_id

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70]: /images/20221120/bda4ab235a3c409f9a969e6f27462a8e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 1]: /images/20221120/9e97402cb55243b9b510ac9a3d3176ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 2]: /images/20221120/effa7eaebbda407994e2b036f36c72e5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 3]: /images/20221120/0656802d9765455f936996730a1ebb53.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 4]: /images/20221120/2f4d2c0752354195835d7956051f66d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 5]: /images/20221120/2490777aa91d4d0e88e784f972918d6a.png
[20201221155636618.png]: /images/20221120/f9ecf2a10e2d426688460d27bc8e83fa.png
[20201221155716367.png]: /images/20221120/aa25d48d4a6c46129c0e0c34d77f4de9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 6]: /images/20221120/c825082e9e6a4415af2f9d1da8e26b4d.png
[20201221155850816.png]: /images/20221120/c7c50336061c4e72ae85db9863f23a97.png