MySQL_走位操作二

桃扇骨 2022-01-22 12:23 211阅读 0赞

#### and 与or 关键字，and的优先级高于or，记得用小括号包起来。 ####

![在这里插入图片描述][20190606110335755.png]

#### 数据类型互转 ####

用convert方法
    字段A  int
    字段B varchar(10)
    则 convert(varchar(10),字段A)将返回A的varchar类型
    同理 convert(int,字段B)将返回B的int型

#### 实现查询结果若为null 则自动转为 0 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70 1]

#### MySQL 的 case when 关键字 ####

统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
    select sc.cno,c.cname,
    sum(case  when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
    sum(case  when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
    sum(case  when score between 60 and 70 then 1 else 0 end) AS "[70-60]",
    sum(case  when score <60 then 1 else 0 end) AS "[<60]"
    from sc, course c
    where  sc.cno=c.cno
    group by sc.cno ,c.cname;
    
    注意用法的不同
    
    查询男生、女生人数
    select 
    sum(case  ssex when '男' then 1 else 0 end )as 男
    from student;

保留小数位  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70 2]

[20190606110335755.png]: /images/20220122/e22f5d4238964502bdd6b6f3f9c16aeb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70]: /images/20220122/2e324ed5fffd46cc947db4c150384700.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70 1]: /images/20220122/2c948b2628e44ba491e0f78dbd2293fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70 2]: /images/20220122/66b4275bf8b3443fb57b4881917bccd1.png