首页 > 代码库 > SQL中的高级数据操作

SQL中的高级数据操作

一、新增数据

  1. 主键冲突:更新和替换

   ① insert into 表名 [(字段列表)] values (值列表) on duplicate key update 字段 = 新值, 字段 = 值, ...;  -- 更新

   ② replace into 表名 [(字段列表)] values (值列表);  -- 替换

  2. 蠕虫复制

   create table 表名A like 数据库.表名B;  -- 从已有表中复制表结构(不复制数据), 如表B与表A在同一数据库,可活力数据库名;

   insert into 表名A [字段列表] select */字段列表 from 表名B;  -- 蠕虫复制

   蠕虫复制的意义:

   ① 从已有表拷贝数据到新表中;

   ② 可以使表中的数据迅速膨胀到一定数量级,用来测试数据库表的压力和效率。

 

二、更新数据:限制更新数量

  update 表名 set 字段 = 值 [where 条件] [limit 数量];

 

三、删除数据:限制删除数量

  delete from 表名 [where 条件] [limit 数量];

  如果表中的主键会自增长,那么删除数据后,自增长不会重置,因为自增长是表结构的一部分, 若要重置自增长,可用下面的方式清空表:

  truncate 表名;  -- 先删除该表,  再新增该表。

 

四、查询数据

  1. 查询语句完整语法:

   select [select选项] */字段列表 [字段别名] from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit子句];

  2. select选项

   all: 默认, 保留所有结果。

   distinct: 去掉重复的结果。

  3. 字段别名

    字段名 [as] 别名

  4. 数据源

   只要数据本质上类似二维表,都可以做为数据源:单表数据源、多表数据源、子查询。

   单表数据源:select * from 表名;

   多表数据源:select * from 表名1, 表名2,...;  -- 结果是笛卡尔积, 是一种交叉连接, 没什么用, 尽量避免使用.

   子查询:select * from (select * from 表名) as 别名;

  5. where子句

   比较运算符:>, <, >=, <=, !=, =, like, in, not in, and, between;

   逻辑运算符:&&(and), ||(or), !(not);

   where是从磁盘读取数据时进行判断, 符合条件的才会保存到内存, 可以节省内存空间。

   示例一:查找id为1, 3, 5的记录

     select * from mytab where id = 1 || id = 3 || id = 5;

     select * from mytab where id in (1, 3, 5);

   示例二:查找id在 5 ~ 10 之间的记录

     select * from mytab where id >= 5 && id <= 10;

     select * from mytab where id between 5 and 10;  -- between是闭区间, 且左值必须小与等于右值

  6. group by子句

   ① 语法:group by 字段名 [ase/desc];  -- 根据字段名进行分组, asc升序排序(默认值), desc降序排序

   ② 分组的目的是为了统计数据。SQL提供了一些统计函数:

     count(字段/*);   -- 参数为字段时,统计每组中该字段的记录数,但为NULL时不统计;参数为*时,统计每组中所有记录数。

       max(字段);      -- 统计每组中该字段的最大值

     min(字段);     -- 统计每组中该字段的最小值

     avg(字段);       -- 统计每组中该字段的平均值    

     sum(字段);      -- 统计每组中该字段的和 

   ③ 其他函数:

     group_concat(字段);  -- 对分组结果中的一个字段的数据进行字符串连接

   ④ 单字段分组 示例用法:

     -- 以sex进行分组, 统计每组的记录数、每组中年龄的最大值,把每组中的数据连接成一个字符串, 并以降序排序。

     select sex, count(*), max(age), group_concat(name) from mytab group by sex desc;

     ⑤ 多字段分组

     select sex, class, count(*) from mytab group by sex, class;  -- 先以sex进行分组,然后对每组以class再进行分组

   ⑥ 回溯统计:with rollup

     select sex, class, count(*) from mytab group by sex[, class] with rollup;

     多字段分组回溯次数计算:

      以双字段分组为例,若根据第一个字段分了x组,  第一次分组后会有一次回溯, 然后根据第二个字段,又会进行X次分组,这就会有X个回溯,

    所以回溯次数 = X + 1. 即进行了几次分组行为,就有几次回溯。 

  7. having 子句

   用来对内存中的数据进行判断。可以使用字段别名

   select class, count(*) from mytab group by class having count(*) > 10;  -- 查找所有班级中人数大于10的学生数量

   select class, count(*) as total from mytab group by class having total > 10;  -- 使用别名

  8. order by 子句

   根据某个段进行升序或降序排序, 依赖校对集。

   语法:select * from 表名 order by 字段名1[, 字段名2] [asc/desc];  -- 默认升序(asc)

  9. limit 子句

   ① limit 数量;  -- 限制数量

   ② limit 起始位置, 数量;  -- 限制起始位置(从0开始),再限制数量,可用于分页

    分页时起始位置算法:(页码 - 1) * 每页显示数量

SQL中的高级数据操作