首页 > 代码库 > 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中的高级数据操作