（十一）数据高级操作

绝地灬酷狼 2022-05-08 02:56 181阅读 0赞

### 文章目录 ###

*   主键冲突（Duplicate key）
 *  表（结构）复制
 *  蠕虫复制
 *  清空表（可重置自增长）
 *  限制记录 （limit）
 *  查询数据
 *  select 选项
 *  字段别名
 *  数据源
 *  where条件
 *  group by 子句
 *  having 子句
 *  order by 子句
 *  limit 子句

--------------------

#  主键冲突（Duplicate key） #

当插入数据的时候，假设主键已经存在，则一定会插入失败；

但是有时候，我们就是想直接替换掉原来的数据，但是我们又不想先删除，再赋值，就想一步到位 ；

**有两种办法：**

-- 选择更新部分字段 -- 语法 insert into 表名[字段列表] values(值列表) on duplicate key update 字段名 = 新值 ； -- 没有set -- 插入主键冲突数据 mysql> insert into student values(3,'haha','男') -- 处理冲突，如果没发生冲突，则直接插入上面的数据，不执行下面的更新 -> on duplicate key update -- 更新想要更新的字段的值，多个字段间用逗号间隔 -> sex = 'female',name = 'dd' ; -- 最终影响2行 Query OK, 2 rows affected -- 小结 -- 为什么插入一条记录是影响2行 -- 因为，先插入的时候，发现数据已经存在了，会导致插入失败，然后再进行更新操作 -- 也就是插入失败一次，更新一次，一共2次 ； -- 如果存在自增长，那么自增长在插入失败的时候，会被触发，因为不管成功与否，你是执行插入了 ； -- ----------------- -- 如果没有产生冲突，则直接插入成功，影响一行 ； -- -------------------------------------------- -- 直接替换掉原来的数据 replace into 表名[字段列表] values(值列表) ; mysql> replace into student values(3,'ww','female') ; Query OK, 2 rows affected -- 影响2行的原因，是因为，它是先删除，再插入，因此影响2行 ； -- 如果要替换的记录不存在，则直接进行插入，影响一行 ；

--------------------

# 表（结构）复制 #

有时候，我们想测试线上数据库，需要在本地搭建的数据库环境中，将线上的数据导进来，我们一般是不想去备份线上数据库，或者我们只需要测试部分表，备份整个数据库，显得意义不大 ；

这时候，我们可以使用表复制，将我们需要的表复制过来，仅仅是复制表结构，表中数据不会得到复制，然后使用 蠕虫复制，导进来数据 ；

-- 语法 create table 表名 like 数据库.表名; mysql> create table my_copy_student like student ; Query OK, 0 rows affected mysql> desc student ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | varchar(10) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set mysql> desc my_copy_student ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | varchar(10) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set

--------------------

# 蠕虫复制 #

从已有的数据中获取数据，然后将获取的数据新增到表中，使得数据成倍增加 ；

-- 语法 insert into 表名[(字段列表)] select 字段列表/* from 表名; --从自己本身复制 ，数据在成倍的增加 mysql> insert into student(name,sex) select name,sex from student ; Query OK, 5 rows affected Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into student(name,sex) select name,sex from student ; Query OK, 10 rows affected Records: 10 Duplicates: 0 Warnings: 0 mysql> insert into student(name,sex) select name,sex from student ; Query OK, 20 rows affected Records: 20 Duplicates: 0 Warnings: 0 mysql> insert into student(name,sex) select name,sex from student ; Query OK, 40 rows affected Records: 40 Duplicates: 0 Warnings: 0 -- 从其他表复制，表结构相同 mysql> insert into my_copy_student select * from student ; Query OK, 5 rows affected Records: 5 Duplicates: 0 Warnings: 0

不止可以从自己本身表复制，只要是表结构一样的表，都可以进行复制 ；

蠕虫复制的意义：

*  从已有的表复制数据到其他表 ；
 *  快速的膨胀数据，以达到压力测试数据的要求 ；

--------------------

# 清空表（可重置自增长） #

-- 当前自增长 mysql> select auto_increment from information_schema.tables where tables.table_schema = 'database' and tables.table_name = 'student' ; +----------------+ | auto_increment | +----------------+ | 109 | +----------------+ 1 row in set mysql> -- 摧毁表，然后重建表 -> truncate student ; Query OK, 80 rows affected -- 再次查询 自增长是 1 mysql> select auto_increment from information_schema.tables where tables.table_schema = 'database' and tables.table_name = 'student' ; +----------------+ | auto_increment | +----------------+ | 1 | +----------------+ 1 row in set

有时候，我们想清空某张表，可以使用 `truncate` 代替 `delete` ；

前者是直接摧毁表，而不是一条一条的删除，因此更快；

--------------------

# 限制记录 （limit） #

更新、删除 是可以按照要求更新或者删除一部分数据的 ；

mysql> select * from my_copy_student ; +----+------+--------+ | id | name | sex | +----+------+--------+ | 2 | dog | male | | 3 | ww | female | | 12 | 哈哈 | 男 | | 22 | haha | NULL | | 33 | haha | 男 | +----+------+--------+ 5 rows in set -- 只更新一条 limit 1 ； mysql> update my_copy_student set name = 'ww' where name = 'haha' limit 1 ; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from my_copy_student ; +----+------+--------+ | id | name | sex | +----+------+--------+ | 2 | dog | male | | 3 | ww | female | | 12 | 哈哈 | 男 | | 22 | ww | NULL | | 33 | haha | 男 | +----+------+--------+ 5 rows in set -- 只删除一条 mysql> delete from my_copy_student where name = 'ww' limit 1 ; Query OK, 1 row affected mysql> select * from my_copy_student ; +----+------+------+ | id | name | sex | +----+------+------+ | 2 | dog | male | | 12 | 哈哈 | 男 | | 22 | ww | NULL | | 33 | haha | 男 | +----+------+------+ 4 rows in set

--------------------

# 查询数据 #

基础语法：

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

完整语法：

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

**完整语法是真的长，其中五子句，如果使用，则必须按照其定义的顺序使用 ；**

--------------------

# select 选项 #

`select` 对查出来的结果进行处理 ；

`All` ：默认的，保留所有的结果 ；  
`Distinct`：去重，去掉查询出来的结果中重复的；（重复，所有字段都相同的记录）

select distinct * from student ;

--------------------

# 字段别名 #

当数据查询出来的时候，我们可以为字段起个别名，防止多表查询的时候出现字段名冲突 ；

语法：

字段名 [as] 别名 ；

--------------------

# 数据源 #

直译为，数据的来源，关系型数据库的数据来源都是数据表，其实本质上只要是二维表都可以作为数据源 ；

数据源分类：

*  单表数据源
    
        select * from student ;
 *  多表数据源
    
        -- 语法 select * from 表1,表2... select * from student,teacher ;
    
    这样查出来的结果是个 ***笛卡尔积*** ；
    
    **也就是 `表1` 的每一条记录都会去匹配 `表2` 的数据，反过来表2也是这样，每一条记录都匹配表1的记录 ；**
    
    最后得到的结果记录数就是，表1的记录数 x 表2的记录数 ；（多张表以此类推下去）
 *  查询语句
    
    也叫 子查询 ；数据的来源是一条查询语句，因为查询语句查询的结果，其实就是一个二维表，满足数据源是二维表的要求 ；
    
        select * from (select * from student) as 表名（别名） mysql> select * from (select * from my_copy_student ) haha ; +----+------+------+ | id | name | sex | +----+------+------+ | 2 | dog | male | | 12 | 哈哈 | 男 | | 22 | ww | NULL | | 33 | haha | 男 | +----+------+------+ 4 rows in set

--------------------

# where条件 #

where 子句：用来判断数据，筛选数据 ；  
where 子句返回结果：0或者1，0 代表 `false` ，1代表`true` ;

判断条件：

比较运算符：

>，<，<=，>=，!=/<>，like，between ... and ...，in/not in

其中 `between` 是个闭区间，`between` 左值必须小于等于右值 ；  
逻辑运算符：

&&(and)，||（or），!(not)

***`where原理`*** ：`where` 是唯一一个直接从磁盘获取数据的时候，就开始判断的条件，每次从磁盘中读取一条记录，就会进行`where条件`判断，判断为真，则存进内存，判断为假，则直接放弃，读取下一条记录 ；

--------------------

# group by 子句 #

分组，根据字段进行分组（相同的放一组，不同，分到不同的组）；

-- 语法 group by 字段名 ;

***分组的意思，是为了统计数据，按照分组字段进行统计 ；***

***`SQL`*** 提供了一系列的统计函数：

count() ; -- 统计分组后的记录数，每一组有多少记录 ； max() ; -- 统计每组中的最大值 ； min() ; -- 统计每组中的最小值 ； avg() ; -- 统计每组的平均值 ； sum() ; -- 统计每组的和 ；

其中，`count()` 函数，填入 `*` 的时候，是统计记录数，如果填入 `字段名` 则统计是字段的记录数，但是假如字段的值为 `null` ，则不算入统计 ；

分组会自动的排序，默认是 ***`升序排序`*** ；

group by 字段名 [asc | desc ] ； -- 对分组的结果进行排序

**多字段分组**（按照字段依次排序 ）

group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ； -- 依次分组，然后排序

可以进行多次分组，按照分组字段的顺序进行多次分组 ，分组内部再分组；

**group\_concat(字段)**

可以对分组结果的某个字段进行字符串的拼接；

**回溯统计**（按照字段依次排序 ）

group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... with rollup ； -- 依次分组，然后排序

每层分组都会向上一层回溯自己分组的总记录数 ；

--------------------

# having 子句 #

与 `where` 子句一样，进行条件判断 ；

`having` 能做 `where` 能做的许多事 ，但是 `where`却不能 做许多`having` 可以做到事；

*  `where` 不能对数据进行分组操作 ，或者说统计函数，只有`having` 可以用 ；
    
    原因 ：`where` 是针对磁盘数据的进行判断，数据进入内存以后，分组以后，`where` 就不能再次对数据进行操作了，只能使用 `having` 来操作 ；
 *  `having` 可以使用别名，但是 `where` 不可以使用别名；
    
    原因 ：`where` 是针对磁盘数据的进行判断，再读磁盘数据的时候，只有字段名可以读，当数据进入内存以后，才会有别名，而 `where` 不能操控 内存中的数据，因此不可以使用别名；只能使用 `having` 来操作 ；

--------------------

# order by 子句 #

根据某个字段进行升序或者降序排序，依赖校对集 ；

**多字段排序**（按照字段依次排序 ）

order by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ； -- 排序中再次排序

在根据前面的字段的排序情况下，再对其进行排序；

--------------------

# limit 子句 #

一种限制结果的语句：限制数量；

*  限制长度（数据量）`limit 数据量`；
    
    前面的限制记录那里，就是限制记录数；
 *  限制起始位置，限制数量 ，`limit 起始位置，数据长度`；
    
    起始位置从 `0` 开始算，第一条记录是 `0` ;
    
    主要用于分页；