mysql操作 05 - SQL高级数据操作

大鹅鹅鹅
1,577 阅读8分钟

前言

本篇主要总结下mysql中的主键冲突、蠕虫复制、限制数量、查询条件判断、字段别名等使用方法。

mysql操作系列文章：

1. 新增数据

基本语法：

insert into 表名 [(字段列表)] values(值列表);

在数据插入的时候，假设主键对应的值已经存在：插入一定会失败。

主键冲突

当主键存在冲突的时候(Duplicate key)，可以选择性的进行处理，更新和替换。

  • 主键冲突：更新操作

    insert into 表名 [(字段列表:包含主键] values(值列表) on duplicate key update 字段 = 新值;

    例：

    -- 插入数据  第一个参数为主键
insert into my_calss values("php1","1");  -- 插入成功
insert into my_calss values("php1","2");  -- 插入失败 主键冲突

-- 主键冲突：更新
insert into my_class values("php1","2")
-- 冲突处理
on duplicate key update
-- 更新数据
room = '2';

  • 主键冲突：替换

    replace into 表名 [(字段列表：包含主键)] values(值列表);

    例：

    -- 主键冲突：替换
replace into my_calss values("php1","2");

    注意： 没有冲突则插入数据。有冲突替换。

蠕虫复制

蠕虫复制：从已有的数据中去获取数据，然后将数据又进行新增操作：数据成倍的增加。

表创建高级操作：从已有表创建新表（复制表结构）

create table 表名 like 数据库.表名;

-- 复制创建表: 表结构复制，数据不复制
create table my_copy like mg_gbk;

蠕虫复制：先查出数据，然后将查出的数据新增一遍

insert into 表名[(字段列表)] select 字段列表/* from 数据表名;

例：

-- 蠕虫复制
insert into my_copy select * from my_collate_bin;  -- 复制别的表
insert into my_copy select * from my_copy;         -- 复制自己的表

蠕虫复制的意义：

  1. 从已有表拷贝数据到新表中
  2. 可以迅速的让表中数据膨胀到一定的数量级，测试表的压力以及效率。

2. 更新数据(限制数量)

基本语法：

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

高级新增语法：

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

例：更新部分数据

--更新部分a变成c
update my_copy set name = 'c' where name = 'a' limit 3;

3. 删除数据

与更新类似，可以通过limit来限制数量。

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

例：

-- 删除数据：限制记录数为10
delete from my_copy where name = 'b' limit 10;

删除：如果表中存在主键自增长，那么当删除后，自增长不会还原。

思路：数据的删除是不会改变表结构，只能删除表后重建表。注意：表数据会被清空。只有表结构。

truncate 表名;  -- 先删除改变，后新增改变。

例：

-- 清空表：重置自增长
truncate my_student;

4. 查询数据

基本语法：

select 字段列表/* from 表名 [where 条件];

完整语法：

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

  1. select 选项 含义: select对查出来的结果的处理方式

    • all：默认的，保留所有的结果。
    • distinct：去重，查出来的结果，将重复的去除（所有字段都相同） 是对所有的字段进行去除
    -- select选项  以下这俩表达的意思一样
select * from my_copy;
select all * from my_copy;

    -- 去重
select distinct * from my_copy;

  2. 字段别名

    含义：当数据进行查询出来的时候，有时候名字并不一定就满足需求(多表查询的时候，会有同名字段) 需要对字段名进行重命名：别名。

    基本语法：

    字段名 [as] 别名;  -- 加as或者不加as都行。

    例：

    -- 字段别名
select
id,
number as 学号,
name as 姓名,
sex 性别 from my_student;

  3. 数据源

    含义：数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据类似二维表，最终都可以作为数据源。

    数据源分为多种：单表数据源，多表数据源，查询语句

    单表数据源：

    select * from 表名;

    多表数据源：

    select * from 表名1,表名2，,...;

    从一张表中取出一条记录，去另外一张表匹配所有记录，而且全部保留（记录数和字段数）将这种结果称为：笛卡尔积(交叉连接)。笛卡尔积没什么用，尽量避免使用。

    子查询：数据的来源是一条查询语句(查询语句的结果是二维表)

    select * from (select 语句) as 表名;

    例：

    -- 子查询
select * from (select * from my_student) as s;

  4. where子句

    where子句：用于判断数据，筛选数据。

    where子句返回的结果：0或者1, 0代表false, 1代表true;

    select * from my_student where 1; -- 表示所有条件都满足，这种表达是为了语句的完整性。

    判断条件：

    • 比较运算符：>,<,>=,<=,!=,<>,=,like,between,and,in/ont in
    • 逻辑运算符：&&(and), ||(or), !(not)

    where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件：从磁盘取出一条记录，开始进行where判断，判断的结果，如果成立就保存到内存，如果失败就直接放弃。

    条件查询1：

    例：找出学生id为1,3,5的数据

    -- 找出学生id为1,3,5的数据， 以下两种语句结果一样
select * from my_student where id = 1 || id = 3 || id = 5; -- 逻辑判断
select * from my_student where id in(1,3,5); -- 落在集合中

    条件查询2：

    例：找身高在180到190之间的学生

    -- 找身高在180到190之间的学生，以下两种语句结果一样
select * from my_student where height >= 180 and height <= 190;
select * from my_student where height between 180 and 190;

    注意： between本身是闭区间，between左边的值必须小于等于右边的值

5. group by子句

group by：分组的意思，根据某个字段进行分组（相同的放一组，不同的分到不同的组）。

~~~
-- 根据性别分组
select * from my_student group by sex;
~~~

分组的意思：是为了统计数据（按组统计：按分组字段进行数据统计）

SQL提供了一系列的统计函数
* count()：统计分组后的记录数：每一组有多少记录
* max()：统计每组中最大的值
* min()：统计最小值
* avg()：统计平均值
* sum()：统计和

例：
~~~
-- 分组统计：身高高矮，年龄平均和总年龄
select sex,count(*), max(height),min(height),avg(age),sum(age) from my_student group by sex;
~~~

count函数：里面可以使用两种参数：* 代表统计记录，字段名代表统计对应的字段(NULL不统计)

分组会自动排序：根据分组字段：默认升序
~~~
-- asc：升序， desc：降序
group by 字段 [asc|desc]; -- 对分组的结果合并之后的整个结果进行排序。

~~~

例：
~~~
select sex,count(*), max(height),min(height),avg(age),sum(age) from my_student group by sex desc;
~~~

多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组。

例：
~~~
-- 多字段分组: 先班级，后男女
select c_id,sex,count(*) from my_student group by c_id,sex; -- 多字段排序
~~~

有一个函数：可以对分组的结果中某个字段进行字符串连接(保留该组所有的某个字段)
~~~
group_concat(字段);
~~~

例：
~~~
-- 多字段分组: 先班级，后男女
select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex; -- 多字段排序
~~~

回溯统计：with rollup; 任何一个分组后都会有一个小组，最后都需要向上级分组进行汇报统计，根据当前分组的字段。这就是回溯统计。特点：回溯统计的时候会将分组字段置空。

~~~
-- 统计
select c_id,count(*) from my_student group by c_id;

-- 回溯统计
select c_id,count(*) from my_student group by c_id with rollop;

-- 多字段分组回溯统计
select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex; -- 多字段排序
select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex with rollop; -- 多字段分组回溯统计
~~~

多字段回溯：考虑第一层分组会有一次回溯，第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后再加上第一层回溯即可。

6. having子句

...

7. order by子句

...

8. limit子句

含义：limit子句是一种限制结果的语句：限制数量

limit有两种使用方式：

方案1：只用来限制长度(数据量)：limit数据量；

例：

--查询学生：前两个
select * from my_student limit 2;

方案2：限制起始位置，限制数量：limit 起始位置,限制数;

例：

-- 查询学生：前两个
select * from my_student limit 0,2; -- 记录数是从0开始编号

limit方案2：主要用来实现数据的分页：为用户节省时间，提高服务器的响应效率，减少资源的浪费。

对于用户来讲：可以点击的分页按钮1,2,3,4

对于服务器来讲，根据用户选择的页码来获取不同的数据：limit offset,length;

length:每页显示的数据量：基本不变。

offset: offset=(页码 - 1) * 每页显示量

分页查询语句及获取总记录数

select SQL_CALC_FOUND_ROWS  * from bloglist order by id limit 2,2;
SELECT FOUND_ROWS();
avatar
文章
阅读
粉丝
相关推荐
mybatis plus源码解析(一) ---基于springboot配置加载和SqlSessionFactory的构造
8.4k阅读
 · 
8点赞
Spring Boot干货系列：（一）优雅的入门篇 | 掘金技术征文
2.3k阅读
 · 
53点赞
Spring AOP:从实践到原理
919阅读
 · 
2点赞
MyBatis | 01 - MyBatis Hello World
449阅读
 · 
40点赞
MySQL 避「坑」指南 —— 你能设置出正确的主键吗？
20k阅读
 · 
27点赞
Web Socket 性能对比——Spring Boot vs Tomcat vs Netty
2.0k阅读
 · 
3点赞
解决Linux虚拟机中启动的MySQL数据库，在宿主机中无法连接的问题
2.2k阅读
 · 
2点赞
这一定是全网写JVM最好的文章之一 - JVM运行时数据区
2.5k阅读
 · 
43点赞
Spring MVC学习(8)—HandlerInterceptor处理器拦截器机制全解
2.4k阅读
 · 
6点赞