mysql-单表操作

今天我们就来介绍一下对单表执行查询操作时,MySQL有哪些查询数据的方式。很详细，有兴趣的可以看下。

单表操作

• 复制表结构和数据
  • 复制表结构
    • create table 表名{like 旧表名}
    • 只复制相同 表结构
  • 复制表数据
    • insert into 数据表名1【（字段列表）】 select 【（字段列表）】 from 数据表2
    • 数据表名1 2通常使用的是通一个表
  • 临时表的使用
    • 仅在当前会话可见 并在当前对话关闭时自动关闭
    • 主要用于临时储存数据
    • 语法：只需在create与table关键中间添加temporary即可
    • 创建
      • create temporary table mydb.tmp_table1（id int）；
      • create temporary table mydb.tmp_table2 select id in name frorm shop.sh_goods
• 解决主键冲突
  • 主键冲突更新
    • INSERT [INTO] 数据表名 [(字段列表)] {VALUES | VALUE} (字段列表) ON DUPLICATE KEY UPDATE 字段名1 = 新值1[,字段名2 = 新值2] …;
  • 主键冲突替换：删除此条记录，并重新插入
    • REPLACE [INTO] 数据表名 [(字段列表)]
    • {VALUES | VALUE} (值列表) [, (值列表)] …;
  • 清空数据
    • TRUNCATE [TABLE] 表名
  • 去除重复记录
    • SELECT select选项 字段列表 FROM 数据表
    • select选项：默认值为All，表示保存所有查询到的记录
    • 当设置为DISTINCT时，表示去除重复记录，只保留一条
• 排序和限量
  • 排序
    • 对数据进行上升或下降的的排序
      • 单字段排序
        • SELECT *|{字段列表} FROM 数据表名 ORDER BY 字段名 [ASC | DESC];
        • ASC表示升序，DESC表示降序
        • ORDER BY默认值为ASC
      • 多字段排序
        • SELECT *|{字段列表} FROM 数据表名 ORDER BY 字段名1 [ASC | DESC] [, 字段名2 [ASC | DESC]]…;
        • 多字段排序首先按照字段名1进行排序，当字段1的值相同时，再按照字段2进行排序，依次类推
    • 数据表的字符集是utf8，当排序的字段为中文时，默认不会按照中文拼音的顺序排序
  • 限量
    • 一个关键字LIMIT，可以限定记录的数量，也可以指定查询从哪一条记录开始
      • SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] [ORDER BY 字段 ASC|DESC] LIMIT [OFFSET,] 记录数;
    • 记录数：表示限定获取的最大记录数量。仅含此参数时，表示从第1条记录开始获取。
    • OFFSET：表示偏移量，用于设置从哪条记录开始，默认第1条记录的偏移量值为0，第2条记录的偏移量值为1，依次类推
  • 数据更新之后的排序与限量
    • UPDATE 数据表名 SET 字段=新值, … [WHERE 条件表达式] ORDER BY 字段 ASC|DESC LIMIT 记录数
  • 数据删除之后的排序与限量
    • DELETE FROM 数据表名 [WHERE 条件表达式] ORDER BY 字段 ASC|DESC LIMIT 记录数
  • UPDATE和DELETE操作中添加ORDER BY表示根据指定的字段，按顺序更新或删除符合条件的记录。
  • 如果UPDATE和DELETE操作没有添加WHERE条件，则可以使用LIMIT来限制更新和删除的数量。
• 运算符
  • 算术运算符
    • 适用于数值类型的数据
    • 注意事项
      • DIV与MOD运算符
        • DIV
          • 运算符DIV与 / 都能实现除法运算，区别在于前者的除法运算结果会去掉小数部分，只返回整数部分
        • MOV
          • 运算符MOD与%功能相同，都用于取模运算
      • NULL参与算术运算
        • NULL是一个特殊的值，它参与的算术运算结果均为NULL
      • “/”运算
        • / 在MySQL中用于除法操作，且运算结果使用浮点数表示
        • 除法运算中除数如果为0，则系统显示的执行结果为NULL。
      • 含有精度的运算
        • 对浮点数进行乘法运算时，运算结果中的精度，以参与运算的操作数的精度和为准
        • 浮点数进行加减运算时，运算结果中的精度（小数点后的位数）等于参与运算的操作数的最大精度
        • 浮点数的精度等于被除数的精度加上系统变量div_precision_increment设置的除法精度增长值
      • 有符号的减法运算结果
      • 无符号的加减乘法运算
        • 运算符+、-和*的操作数都是无符号整型，则运算结果也是无符号整型
  • 比较运算符
    • 应用在条件表达式中对结果进行限定
    • MySQL中比较运算符的结果值
      • 1 (TRUE，表示为真)
      • 0 (FALSE，表示为假)
      • NULL
        • 比较运算符=、>、<、>=、<=、<>、!=在与NULL进行比较时，结果均为NULL
        • 比较运算符=、>、<、>=、<=、<>、!=在与NULL进行比较时，结果均为NULL
    • 参与比较的操作数的数据类型不同，则MySQL会自动将其转换为同类型的数据后再进行比较
    • 条件表达式中对指定区间的数据进行判断
      • BETWEEN 条件1 AND 条件2
      • 用于表示条件1到条件2之间的范围（包含条件1和条件2）
      • 件1和条件2在设置时，条件1必须小于等于条件2
      • NOT BETWEEN…AND…表示不在指定范围内，与BETWEEN…AND使用方式相同
    • IS NULL或IS NOT NULL
      • 用于在条件表达式中判断字段是否为NULL
    • LIKE与NOT LIKE
      • like
        • 模糊匹配
      • not like
        • 获取匹配不到的数据
      • 匹配模式符“%”可以匹配任意0到多个字符
    • 正则匹配查询
      • 符号“|”在正则中表示分隔符，用于分割多种条件，在匹配时只要指定字段满足分隔符左右两边条件中的一个，就表示匹配成功
  • 逻辑运算符
    • 应用在条件表达式中的逻辑判断
    • 参与逻辑运算的操作数以及逻辑判断的结果只有3种
      • 1 (TRUE，表示为真)
      • 0 (FALSE，表示为假)
      • NULL
    • 运算符
      • AND或 &&
        • 逻辑与，操作数全部为真，则结果为1，否则为0
      • OR或 ||
        • 逻辑或，操作数中只要有一个为真，则结果为1，否则为0
      • NOT或 !
        • 逻辑非，操作数为0，则结果为1，操作数为1，则结果为0
      • XOR
        • 逻辑异或，操作数一个为真，一个为假，则结果为1；若操作数全部为真或全部为假，则结果为0
      • 仅有逻辑非（NOT或!）是一元运算符，其余均为二元运算符。
      • NOT和“!” 虽然功能相同，但是在一个表达式中同时出现时，先运算“!”，再运算“NOT”
    • 注意事项
      • 逻辑与：只有操作数全部为真时，运算的结果才为真
        • 操作数中含有NULL，而另一个操作数若为1（真），则结果为NULL
        • 另一个操作数为0（假），则结果为0
      • 逻辑或：只要操作数有一个为真，则运算的结果就为真
        • 操作数中含有NULL，而另一个操作数若为1（真），则结果为1
        • 另一个操作数为0（假），则结果为NULL
      • 逻辑异或
        • 表示两个操作数同时都为1或0，则结果为0
        • 两个操作数一个为1，一个为0，则结果为1
        • 当操作数为NULL时，则结果为NULL
      • “=”是一个比较特殊的运算符，既可以用于比较数据是否相等，又可以表示赋值
      • “=”作为比较运算符使用，“:=”作为赋值运算符使用
• 聚合函数
  • 结合MySQL提供的聚合函数才能够统计出具有价值的数据
  • 参数前添加DISTINCT，表示对不重复的记录进行相关操作
    • sum（）
      • 返回参数字段之和
    • group_concat（）
      • 返回符合条件的参数字段值的连接字符串
    • min（）
      • 返回参数字段的最小值
    • max（）
      • 返回参数字段的最大值
    • count（）
      • 返回参数字段的数量 不统计null的记录
      • 参数设置为“*”时，表示统计符合条件的所有记录（包含NULL）
    • avg（）
      • 返回参数字段的平均值
  • json_objecctagg（）
    • 将符合统计的参数字段值作为单个json对象返回
  • json_arrayagg（）
    • 将符合条件的参数字段值做为单个json数组返回
• 分组
  • 为了统计
  • 分组的关键字：GROUP BY
  • 分组筛选：用HAVING进行条件筛选
    • 对分组后的内容进行过滤
  • 分组统计
    • 查询时在WHERE条件后添加GROUP BY即可进行分组统计
    • SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名;
    • 分组后，SELECT获取的字段列表只能是GROUP BY分组的字段，或使用了聚合函数的非分组字段
    • 若在获取非分组字段时没有使用聚合函数，MySQL会报错误提示
  • 多分组统计
    • 按某个字段进行分组后，对已经分组的数据进行再次分组的操作
    • SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式]
    • GROUP BY 字段名1 [ASC | DESC], [, 字段名2 [ASC | DESC]]…
  • 分组排序
    • 默认分组操作的字段提供按升序排序，因此在分组时可为指定的字段进行升序或降序排序
    • SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名 [ASC | DESC]
    • GROUP BY分组排序的实现不需要使用ORDER BY，直接在分组字段后添加ASC（升序，默认值可省略）或DESC（降序）即可
  • 回溯统计
    • 在根据指定字段分组后，系统又自动对分组的字段向上进行了一次新的统计并产生一个新的统计数据，且该数据对应的分组字段值为NULL
    • SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式]
    • GROUP BY 字段名1 [ASC | DESC], [, 字段名2 [ASC | DESC]]… WITH ROLLUP
  • 统计筛选
    • 对查询的数据分组时，可以利用HAVING根据条件进行数据筛选
    • SELECT [select 选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名 [ASC | DESC], …[WITH ROLLUP] HAVING 条件表达式
  • 在查询中使用别名
    • 设置别名
      • SELECT g.category_id cid, MAX(g.price) max_price FROM sh_goods g
      • GROUP BY cid HAVING cid = 3 OR cid = 6