MySQL相关查询

排序&分组查询

1. 排序查询

* 语法：
    select    （3）
        要查询的东西
    from    （1）
        表
    where      （2）
        筛选条件
    order by 排序的字段|表达式|函数|别名 【asc|desc】（4）
​
​
*特点：
    * 排序方式：
        * ASC：升序，默认的
        * DESC：降序
    *排序分类：
        1.按单个字段进行排序
        2.按多个字段排序
        3.按表达式排序
        4.按别名排序
        5.按函数排序
    *order by子句一般放在查询语句的最后面,limit 子句除外
    *
* 注意：
    * 如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件

---

2. 常见函数

1、字符函数
    1.concat: 拼接
    2.substr: 截取子串    （索引次从一开始）
    3.upper: 转换成大写
    4.lower: 转换成小写
    5.trim: 去前后指定的空格和字符
     *#trim SELECT LENGTH (TRIM('    账户   ')) AS   out_put;
     #删除前后的a
     *SELECT TRIM('a'  FROM   'aaa账aaaa单aaaa') AS  out_put;
    6.ltrim: 去左边空格
    7.rtrim: 去右边空格
    8.replace: 替换
    9.lpad: 左填充（用指定的字符实现左填充指定长度）
    10.rpad: 右填充
    11.instr: 返回子串第一次出现的索引，如果找不到返回0 
    12.length: 获取字节个数   
------------------------------------------------------------------------- 
2、数学函数
    round: 四舍五入
    rand: 随机数
    floor: 向下取整；返回<=该参数的最大整数
    ceil: 向上取整，返回>=该参数的最小整数
    mod: 取余
    truncate: 截断
----------------------------  ---------------------------------------------
3、日期函数 
    now: 当前系统日期+时间 
    curdate: 当前系统日期
    curtime: 当前系统时间
    str_to_date: 将字符转换成日期(%Y-%c-%d)
    date_format: 将日期转换成字符
    year，month，day，hour，minute，second.....可以获取指定的部分，年。月。日。小时。分钟。秒
​
-------------------------------------------------------------------------
4、流程控制函数  
    if 处理双分支  
    case语句 处理多分支
        情况1：处理等值判断
        情况2：处理条件判断
        
    case 要判断的字段或表达式  （等值）
    when 常量（条件）1 then 要显示的值1或语句1；
    when 常量（条件）2 then 要显示的值2或语句2；
    ...
    else 要显示的值n或语句n；
    end
    
    case  （区间）
    when 条件1  then 要显示的值1或语句1
    when 条件2  then 要显示的值2或语句2
    ....
    else 要显示的值或语句n
    end
    
-------------------------------------------------------------------------
5、其他函数
    version: 版本
    database: 当前库
    user: 当前连接用户

---

3. 分组函数（聚合，统计，组函数）

* count：计算个数
    * 一般选择非空的列：主键
    * count(*)用作统计行数
* max：计算最大值     
* min：计算最小值
* sum：计算和
* avg：计算平均值
* 特点：
    1、以上五个分组函数都忽略null值，除了 count(*)
    2、sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
    4、count的参数可以支持：
        * 字段、*、常量值，一般放1
* 注意：聚合函数的计算，排除null值。
    解决方案：
        * 选择不包含非空的列进行计算
        * IFNULL函数
*和分组查询的字段要求是group by 后的字段
​

---

4.分组查询

* 语法：
    select 查询的字段，分组函数
    from 表
    【where 筛选条件】
    group by 分组的字段
    having 分组后的筛选
    【order by 子句】
   *注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段
* 特点：
     @分组查询中的筛选条件分为两类
                    数据源       位置                  关键字
       分组前筛选     原始表       group by子句的前面     where
       分组后筛选     分组后合集    group by子句的后面     having
       
       分组函数做条件肯定放在having子句中
    1、可以按单个字段分组
    2、和分组函数一同查询的字段最好是分组后的字段
    3、可以按多个字段分组，字段之间用逗号隔开，没有顺序要求
    4、可以支持排序（放在整个分组排序的最后）
    5、having后可以支持别名
    6、支持表达式或函数（用的较少）
* 面试相关
    * where 和 having 的区别？
        1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
        2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

多表连接查询

1 笛卡尔集&内连接

当查询中涉及到多个表的字段，需要用到多表连接

1.笛卡尔集

    * 产生条件：
        1. 省略连接条件
        2. 连接条件无效
        3. 所有表中所有行互相连接
    * 解决方案：
        添加有效筛选条件
-------------------------------------------------------------------------
​
3. 内连接（等值连接，非等值连接，自连接）
    * SQL92语法：
    1、等值连接
    ①多表等值连接的结果为多表的交集部分
    ②n表连接，至少需要n-1个连接条件
    ③多表的顺序没有要求
    ④一般需要为表起别名
    ⑤可以搭配前面介绍的子句使用，比如排序... 
        SELECT 查询列表
        FROM 表名1 别名1 ,表名2 别名2 
        WHERE 等值的连接条件               
        AND 筛选条件                
        GROUP BY 分组列表           
        HAVING 分组后筛选条件        
        ORDER BY 排序列表   
    2、非等值连接
    SELECT 查询列表
        FROM 表名1 别名1 ,表名2 别名2 
        WHERE 非等值的连接条件              
        AND 筛选条件                
        GROUP BY 分组列表           
        HAVING 分组后筛选条件        
        ORDER BY 排序列表
    3、自连接
        SELECT 查询列表
        FROM 表 别名1 ,表 别名2 
        WHERE 等值的连接条件               
        AND 筛选条件                
        GROUP BY 分组列表           
        HAVING 分组后筛选条件        
        ORDER BY 排序列表
        
        
    * SQL99语法：（等值连接，非等值连接，自连接）
    
         内连接：    
       select  查询列表 
        from 表名1  别名 【连接类型】
        [inner] join 表名2 别名
         on 连接条件
        【where 筛选条件】
        【group by 分组条件】
        【having 分组后的筛选条件】
        【order by 排序字段】     
        特点：
        1、inner可以省略
        2、筛选条件放在where后面。连接条件放在on后面，提高分离性，便于阅读
        3、inner join连接和sql92语法中的等值连接效果一样，都是查询多表的交集
        4、②n表连接，至少需要n-1个连接条件
        5、③多表的顺序没有要求
        6、内连接的结果等于多表的交集
    
    分类： 
    1、内连接（⭐）：inner
    2、外连接（⭐） 
        左外（⭐）：left 【outer】
        右外（⭐）：right 【outer】
        全外：full 【outer】  （MySQl不支持）
    3、交叉连接：cross join 
    select 查询列表
    from 表1 别名
    cross join表2 别名
    
    特点：类似于笛卡尔乘积
* 注意：
    1. 使用表名前缀在多个表中区分相同的列
    2. 在不同表中具有相同列名的列可以用表的别名加以区分
    3. 如果使用了表别名，则在select语句中需要使用表别名代替表名
    4. 表别名最多支持32个字符长度，但建议越少越好
    为表起别名
    优点：提高语句简洁度；区分多个重名字段
    注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

---

2 外连接

一般用于查询除了交集部分剩余的不匹配的行1

1. 左外连接
    * 语法：
        select 字段列表 
        from 表1 
        left [outer] join 表2 on 条件
        ...
    * 注意：
        左外连接查询的是左表所有数据以及其交集部分
-------------------------------------------------------------------------
5. 右外连接
    * 语法：
        select 字段列表 
        from 表1 
        right [outer] join 表2 on 条件
        ...
    * 注意：
        右外连接查询的是右表所有数据以及其交集部分       

3 .子查询

*含义：出现在>>>>>>>>
      外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
      外面如果为select语句，则称此语句为外查询或主查询
* 概念：查询中嵌套查询，称嵌套查询为子查询
*分类：
    按照子查询的出现的位置
    ①、子查询可以放在
      select后面：
        仅仅支持标量子查询
      from后面：（将子查询结果充当一张表，要求必须起别名）
        支持表子查询
      where后面、having后面（⭐）：
        标量子查询（单行）（√）
        列子查询  （多行）（√）
        行子查询
      exists后面（相关子查询）：
        表子查询
         语法：exist(完整的查询语句)
              结果：1后或0
   
    ②按结果集的行列数
     标量子查询（结果集只有一行一列）（⭐）
     列子查询（结果集只有一列多行）（⭐）
     行子查询（结果集只有多行多列）
     表子查询（结果集一般为多行多列）
    
    一》where 或having 后面
       ① 单行子查询（标量子查询）
            结果集只有一行一列
            一般搭配单行操作符使用：> < = <> >= <= 
            非法使用子查询的情况：
                a、子查询的结果为一组值
                b、子查询的结果为空
      特点：    
    1、子查询都放在小括号内 
    2、 子查询一般放在条件的右侧
    3、标量子查询， 一般搭配单行操作符使用：> < = <> >= <= 
    4、列子查询， 一般搭配多行操作符使用：any、all、in、not in
    5、子查询优先于主查询执行，主查询使用了子查询的执行结果
    #非法使用标量子查询
    ② 多行子查询（列子查询）  
            结果集有多行
            一般搭配多行操作符使用：any、all、in、not in
            in： 属于子查询结果中的任意一个就行
            any和all往往可以用其他查询代替      

---

4 分页查询

*应用场景：
        当要查询的条目数太多，一页显示不全
* 语法：
    select 字段|表达式,...
    from 表
    【join type】  join 表2
    on 连接条件
    where 筛选条件
    group by 分组字段
    having 分组后的筛选
    order by 排序的字段
    limit offset（起始的条目索引，从0开始），size（条目数）;
* 示例：每页显示3条记录
        * SELECT * FROM student LIMIT 0,3; -- 第1页
        
        * SELECT * FROM student LIMIT 3,3; -- 第2页
        
        * SELECT * FROM student LIMIT 6,3; -- 第3页
* 特点：
    1.起始条目索引从0开始
​
    2.limit子句放在查询语句的最后
​
    3.公式：假如要显示的页数为page，每一页的条目数为sizePerPage
    
    select * from  表 limit （page-1）*sizePerPage,sizePerPage
    假如:
        每页显示条目数sizePerPage
        要显示的页数 page

5 .联合查询（union ）

union 联合，合并：将多条查询语句的结果合并成一个结果

应用场景：

要查询的结果来自于多个表，且多个表没有 直接的连接关系，但查询的信息一致时

* 语法：
    select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
    select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
    select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
    .....
    select 字段|常量|表达式|函数 from 表 where 条件
* 特点：
    1、多条查询语句的查询的列数必须是一致的
    2、多条查询语句的查询的列的类型和顺序最好一致
    3、union代表去重，union all代表不去重