日常学习 7.20 sqlite数据库的复杂查询

【数据库的复杂查询】
一、联表查询
#场景：查询学生数据，列出完整的数据（包含 班级名称）
#前提：两张表中必须有对应（关联）的字段          如：学生表  班级表  对应字段：班级id
#联表的方式
    1.普通联表（直接联表）
    select * from 表1,表2 where 表1.字段名=表2.字段名;
    select 表1.*，表2.字段名 where 表1.字段名=表2.字段名;    （展示表1的全部字段和表2的字段名字段）
    2.公式联表
        -内连接 inner join .............. on
        select * from 表1 inner join 表2 on 表1.字段名=表2.字段名;
        连接过程：取交集（笛卡尔积）
        
        -外连接（左外连接(绝大多数)、右外连接、全外连接） left join .......... on
        select * from 表1 left join 表2 on 表1.字段名=表2.字段名;
        连接过程：left on前为主表  后为从表
        1.取交集
        2.将主表中没有匹配上的也列出来        

        #新增两张表验证内外连接的区别
        -学科表：学科id  学科名称
        
        -成绩表：学生id  学科id  分数
            1001    1           90
            1001    2           80
            1002    1         90
        设置主键解决方案：单独设置一个字段作为主键   （sqlite中主键类型为integer时(int不行)可自增长,填null即可）
        支线：解决方案是联合主键
        
        

重命名
select * 表1.字段名,表2.字段名 from 表1 inner 表2 on 表1.字段名=表2.字段名;
重命名为：select * A.字段名,B.字段名 from 表1 A inner 表2 B on A.字段名=B.字段名;
二、子查询
#嵌套子查询
    #场景：查询价格和蒙多一样的英雄信息
    1.查询蒙多的价格
    select hero_price from z_hero where hero_name='蒙多';
    2.查询英雄时，把价格=上面查询的结果
    select * from z_hero where hero_price=(select hero_price from z_hero where hero_name='蒙多';);
#in子查询
    #场景：前端传递来“一堆数据”(数组)  17,18,19,20,21
    查询指定年龄的学生信息
    -方式1：范围 大于 小于(缺点：数据必须连贯 有规则)
    select * from z_student where stu_age >=17 and stu_age<=21;
    -方法2：between and(缺点：数据必须连贯 有规则)
    select * from z_student where stu_age between 17 and 21;
    -方式3：取反大法（not）(缺点：数据必须连贯 有规则)
    select * from z_student where not (stu_age <17 and stu_age>21);
    -方法4：穷举法(缺点：麻烦)
    select * from z_student where stu_age=17 or stu_age=18 or stu_age=19 or stu_age=20 or stu_age=21;
    -in子查询：
    select * from z_student where stu_age in (17,18,19,20,21);

三、聚合函数查询
    -求和函数
    sum(字段)  如：select sum(字段名) from 表名;
    -求平均数函数
    avg(字段)  如：select avg(字段名) from 表名;
    -统计个数函数
    count(*)  如：select count(*) from 表名;
    -最值
    max(字段)  min(字段)  如：select max(字段) from 表名;   select min(字段) from 表名;
    -分组聚合查询 group by 字段名
    需求：展示每个学生的总分  
    select sum(score) from t_student group by stu_id;
    展示每个学生的总分和学生id  总分降序
    select sum(score),stu_id from t_student group by stu_id order by sum(score) desc;
    如果在分组查询中有条件筛选且条件是聚合函数字段，需在group by 字段名后且加having
    例如：展示总分在240及以上的学生总分 降序
    select sum(score) from t_student group by stu_id having sum(score)>=240 order by sum(score) desc;

    select sum(字段名) from 表名 group 字段名;