PHP-数据高级操作

素颜马尾好姑娘i 2022-05-25 09:23 340阅读 0赞

数据的操作无外乎就是对数据的增删改查

## 增加数据 ##

基本方式：**insert into 表名 \[(字段列表)\] values (值列表)；**

主键冲突：**在插入数据的时候，主键值已经存在了**，但是要求是必须使用该主键字段  
![这里写图片描述][70]

实现目标：**如果该主键不存在那么就增加记录，如果存在，就修改部分字段的值**

1.**使用主键冲突方式**

语法：insert into 表名 values(值列表) on **duplicate** key update 字段 = 值  
![这里写图片描述][70 1]

2.使用替换插入  
**replace into 表名 (字段列表) values(值列表)**  
![这里写图片描述][70 2]

**replace其实先删除存在的行，再进行插入。**

注意：大部分情况下，因为是使用逻辑主键，一般会自增长，所以使用null即可（insert），但是如果是使用业务主键，那么很有可能会存在冲突（replace）。

## 蠕虫复制 ##

蠕虫能够一分为2,2分为4，在成倍增长  
语法：**insert into 表名 (字段列表) select 字段列表 from 表名;**  
![这里写图片描述][70 3]

**蠕虫复制能够快速使用表中的数据量增加，没有办法改变数据**

**蠕虫复制也可以从其他的表中去获取数据。**

## 更新数据 ##

基本语法：**update 表名 set 字段 = 值 \[where条件\]**

高级语法：**update 表名 set 字段 = 值 \[where条件\] \[limit\]**  
![这里写图片描述][70 4]

## 删除数据 ##

基本语法：**delete from 表名 \[where条件\]**

高级语法：**delete from 表名 \[where条件\] \[limit\]**  
![这里写图片描述][70 5]

删除数据并不能修改一张表的索引和自增长。**如果想删除表的自增长重新来过**

**先删除表（drop），再新建表（create）**

mysql提供了简单方式实现两个步骤：**truncate**  
![这里写图片描述][70 6]

## 查询数据 ##

基础语法：**select 字段列表 from 表名 \[where条件\]**

高级语法：**select \[select选项\] 表达式 \[from子句\] \[where子句\] \[group by 子句\] \[having 子句\] \[order by子句\] \[limit 子句\]**

select选项：**在查询得到数据是否需要进行合并，all表示获取所有数据（默认的），distinct将完全一致的记录进行合并**  
![这里写图片描述][70 7]

**去重**  
![这里写图片描述][70 8]

## from子句 ##

用来指定数据源  
普通：from 表名  
**高级：from 多表名；**  
![这里写图片描述][70 9]

原理：拿着第一张表的记录去分别匹配第二张表的所有记录，将每次匹配的结果保存。  
以上结果在数学上被称之为**笛卡尔积**。

**在实际使用当中，笛卡尔积没有任何意义，所以要尽量避免出现笛卡尔积。**

高级：**from 表（不是真实存在的表，而是查询得到的结果–子查询）**  
![这里写图片描述][70 10]

## 别名 ##

对某个数据源所起的简单的用来描述的名字  
语法：**数据源 \[as\] 别名**  
![这里写图片描述][70 11]

**from后面如果跟的不是真实的表，那么必须使用别名来对数据源进行表述**  
![这里写图片描述][70 12]

## where子句 ##

依据条件筛选数据  
where如何筛选时数据？**通过运算符来进行数据筛选**  
运算符：  
　 比较运算符：>，>=，<，<=，=（比较），!=，<>，in，between and，is \[not\]null  
　　　 **in**：表示某个字段的值，**在某个集合（包含多个数据）中**  
![这里写图片描述][70 13]

**between and**：语法是between A and B（A必须小于等于B），表示在某个区间内（闭区间）  
![这里写图片描述][70 14]

如果**字段的值为null**，那么该字段与任何值的**运算的结果都是null**  
![这里写图片描述][70 15]

**is null判断数据是否为null**  
![这里写图片描述][70 16]

逻辑运算符：&&and，||or，not

**where后面得到的结果会被系统怎么处理？**  
结果会被系统当**成布尔处理**

**where工作原理**  
![这里写图片描述][70 17]

where是针对**元数据进行操作**，**所有where之后的语句，都是针对数据结果进行操作，而不是针对数据表。**

## group by子句 ##

**分组统计**  
语法：**group by 字段**  
![这里写图片描述][70 18]

group by分组之后，**会将数据进行合并**，**从而只象征性的保留每组的第一条记录。**

通常group by\*\*不是为了得到分组后的每组的全部数据，而是用来统计每组的信息。\*\*

统计函数：都是数学统计  
　　 **count**：统计**所有的记录的个数**，也可以是**所有字段（不统计null）**  
![这里写图片描述][70 19]

max：统计分组后每组里面的最大值，通常是某个字段  
min：统计最小  
avg：统计平均数  
sum：求和  
![这里写图片描述][70 20]

**group by工作原理**  
![这里写图片描述][70 21]

**group by排序：能够直接排序**  
语法：**group by 字段 \[asc|desc\]**  
根据分组字段进行排序  
![这里写图片描述][70 22]

**多字段分组：按照某个字段进行分组之后，将分组得到的数据再按照某个条件进行分组。**  
![这里写图片描述][70 23]

回溯统计：**每一层分组之后，都会在最终的结果上，再进行一次额外的统计**  
语法：在分组之后增加 **with rollup**  
![这里写图片描述][70 24]

## having子句 ##

**having子句功能与where一样，用于做条件判断**  
where：**对数据源进行条件处理**  
having：**对where得到数据内存进行处理**

相同点  
![这里写图片描述][70 25]

**不同点：having可以使用字段别名，而where不能**  
![这里写图片描述][70 26]

**原理**  
![这里写图片描述][70 27]

不同点：**having可以使用统计函数**  
![这里写图片描述][70 28]

分组统计在数据操作的过程中经常使用，尤其是做一些报表开发。分组通常与分组函数一起使用。

## order by子句 ##

主要是对字段进行排序，有两种方式：**asc默认的，desc降序排序，比较的依据是校对集。**

## limit子句 ##

理解的是**限制查找过程中返回数据的条数**

实际：**限制数据查询的起始位置和返回数据的条数**

语法：**limit offset,number**  
　　 **offset**：**从查询得到结果中的第几条开始**，在数据库里，offset从0开始  
　　 **number**：**一共返回多少记录**（返回的记录数不一定完全等于number数量）

通常limit用于实现数据的分页。

需求：显示所有学生信息，每页只能显示20个数据，总共55个学生  
第一页：最前面的20个——》20  
第二页：21个到40—–》20  
第三页：41到55—–》15  
![这里写图片描述][70 29]

从from开始的6个子句，他们的顺序必须完全一致（如果子句都存在的情况下）

## 联合查询 ##

**将两个查询的结果，合并到一起，将记录数合并，而字段数不变**

联合查询必须满足的条件  
**1.两个查询得到的字段数必须一致**

语法：select语句 **union \[union选项\]** select语句

union选项：**all和distinct（默认的）**  
![这里写图片描述][70 30]

**union刚需**  
![这里写图片描述][70 31]

**union不管数据类型，只要字段数一致即可**  
![这里写图片描述][70 32]

**所有的数据存储在磁盘上的时候是有数据类型的，但是一旦数据读入到内存里面，就都没有类型而言，全部都是字符串。**

**需求**：查出2班的所有学生，按照年龄的升序排序；查出3班的所有学生，按照年龄降序排序

**在union中使用order by必须对select语句进行括号处理**  
![这里写图片描述][70 33]

**在union中使用order by必须配以limit才能生效**  
![这里写图片描述][70 34]

**正确使用order by**  
![这里写图片描述][70 35]

**为何要使用union？**  
大数据处理，往往一个网站系统当数据量很大的时候，会进行数据的分表操作，**将同样的数据存放在不同的表中，因为一张表的数据太大会影响数据查询的效率，增加维护的难度。**所以，一般这样的数据的都是分表存放，但是在某些操作的时候，需要将所有的数据放到一起，这时会就使用联合查询。

## 连接查询 ##

概念：将多个表中相关的数据记性字段上面的增加，从而使得最终的结果能够完全显示数据信息

连接查询根据查询结果以及条件的不一样可以分为几类：  
1.内连接  
2.外连接  
3.自然连接  
4.交叉连接

## 内连接 ##

如果一张表的记录（有个字段记录另外一张表的主键），能够在另外一张表中匹配上，那么就保存，否则就不保存。  
左表：join关键字左边的表称之为左表  
右表：join关键字右边的表称之为右表

语法：**左表 \[inner\] join 右表 on/where 连接条件**  
![这里写图片描述][70 36]

**内连接原理：**  
![这里写图片描述][70 37]

**内连接的on条件可以使用where来进行匹配**  
![这里写图片描述][70 38]

内连接还可以通过使用多表查询，但是使用条件匹配来实现  
![这里写图片描述][70 39]

## 外连接 ##

**与内连接相似，如果主表（左外连接左表为主表，右外连接右表为主表）的记录在副表中匹配不上，那么该记录依然保留，只是副表字段都置空**

语法：**左表 left/right \[outer\] join 右表 on 连接条件**

**左外连接**：以左表为主表，使用关键字left

**右外连接**：以右表为主表，使用关键字right  
![这里写图片描述][70 40]

**内连接与外连接的区别**  
1.内连接的条件可以使用where关键字，外连接不行  
2.内连接可以没有条件，而外连接必须指定条件  
3.内连接只保留匹配成功的记录，而外连接会保留主表的全部记录

**外连接左右可以互相转换**  
![这里写图片描述][70 41]

不管是内连接还是外连接，都可以进行**多表连接**（超过两张表）  
![这里写图片描述][70 42]

## 交叉连接 ##

笛卡尔积  
语法：**左表 cross join 右表**  
![这里写图片描述][70 43]

## 自然连接 ##

**两个表之间的连接不需要指定连接条件，系统自动找条件，并按照条件进行处理。**

自然连接可以包含内连接，外连接和交叉连接

语法：  
　　　**左表 natural \[left/right\] join 右表**  
　　　  
　　　**左表 natural join 右表：自然内连接**  
　　　  
　　　**左表 natural \[left/right\] join右表：自然外连接**

自然连接自动寻找连接条件：**会判断两张表中是否有同样的字段名称，如果有，则作为连接条件，否则就没有条件**

自然连接，会合并相同的字段  
![这里写图片描述][70 44]

自然连接：**会将主表放在左边显示，副表放在右边显示**  
![这里写图片描述][70 45]

连接条件，若两张表中的**做为连接条件的字段，字段名称相同，那么可以使用using关键字，来确定连接条件，也会合并连接条件**  
语法：**using(连接条件)**

**使用内连接模拟自然内连接**  
![这里写图片描述][70 46]

**使用内连接模拟自然外连接**  
![这里写图片描述][70 47]

自然连接：无论两张表中有多少同名字段，通通拿过来作为连接条件。

连接查询：用于多表共同显示数据。

[70]: /images/20220525/91a3c50117c34c7894a6ff2265ab0887.png
[70 1]: /images/20220525/9802aa8e4410481c961845433d0477cd.png
[70 2]: /images/20220525/46fdcd47f8814439a3de625b8d4eb2c6.png
[70 3]: /images/20220525/2c8acca2611547a6a6a9e84c90b1e07c.png
[70 4]: /images/20220525/dec1ee3684e04cffb72c747a1bf96136.png
[70 5]: /images/20220525/997f3155367949ad87ba14d9c81090e7.png
[70 6]: /images/20220525/fc5b64e76e4141a29d515194bde5a1f4.png
[70 7]: /images/20220525/3ea450fcde0e45bdbd9cda2f6345e48f.png
[70 8]: /images/20220525/6289754b785648d78fb1d81a47c3ec7f.png
[70 9]: /images/20220525/791633ac0c68412cba93e4bcb9bf04b0.png
[70 10]: /images/20220525/45bbd17730464b228ebb5a6712d9cb25.png
[70 11]: /images/20220525/3ecdb066cd9340f590de5191e2a634f6.png
[70 12]: /images/20220525/e75d60ffd9484f39baca1522236b560c.png
[70 13]: /images/20220525/ac63f1e6d5494053aa0727cc468bb691.png
[70 14]: /images/20220525/d3f5e83596154eb8aeaf5590de520d89.png
[70 15]: /images/20220525/57e941dcfa0c40b89dda2dca3a3f3463.png
[70 16]: /images/20220525/84fc4ec5418343a6940dfd7ef4acbc71.png
[70 17]: /images/20220525/9a97797c24e1431ba78747e7e3bb5473.png
[70 18]: /images/20220525/78dcfc1d2d88447682eccc1e6e039739.png
[70 19]: /images/20220525/59e5e0260b6e4ead95beb21ff2396d78.png
[70 20]: /images/20220525/3bd329914e014bf6a5272ef1aa29e1b4.png
[70 21]: /images/20220525/f976b84776fd493d854ffdc0a9a2d8bb.png
[70 22]: /images/20220525/ec299291fe2443c1836cc697640a3c96.png
[70 23]: /images/20220525/879c46a43a3447288883e364bcfb51bc.png
[70 24]: /images/20220525/e6c2635dea12429891c3e71f41e75e78.png
[70 25]: /images/20220525/9ad99b0fce1c48b7b5d03ad7df630cdd.png
[70 26]: /images/20220525/b18757f5c9f44e9db6bae9988c77f7fb.png
[70 27]: /images/20220525/acf605708a0941e0861f738326702524.png
[70 28]: /images/20220525/004762fea57a475e84c4f691e0c32853.png
[70 29]: /images/20220525/5cfc056e30a74a99802a8861caf4f3e3.png
[70 30]: /images/20220525/02c28ae4ce054f218916b4d88907f9cc.png
[70 31]: /images/20220525/0d323e93a41244838344ea4f2a14dafd.png
[70 32]: /images/20220525/c8f7257697de4d8f84484d96f97d4d27.png
[70 33]: /images/20220525/35bff3b0b1da4ed587e66bcbcf592254.png
[70 34]: /images/20220525/275743c2e35a4b3e81b0fdefff748681.png
[70 35]: /images/20220525/31fb4f74dc6d4a80b780e4af8a13fd84.png
[70 36]: /images/20220525/bc6a27cec11844ddad2551d0d9e28e98.png
[70 37]: /images/20220525/5b91ee4779a54663b30978ffdca8e0f6.png
[70 38]: /images/20220525/845a7c8cd9ac408b834f66f05f8aff58.png
[70 39]: /images/20220525/0d7627b4283c467f9a80dac35a0498b7.png
[70 40]: /images/20220525/686930710c9c4b38a2468b94f5dc15ea.png
[70 41]: /images/20220525/932a8ab22ee14a11a8a82eed189f6ae6.png
[70 42]: /images/20220525/3e6035d388254f3695abff6e5e507d5c.png
[70 43]: /images/20220525/42e7f9f9557a43fc996b7bec82266c21.png
[70 44]: /images/20220525/e0c9e2b357544c2ca38023016522ee1c.png
[70 45]: /images/20220525/3ffb248370a84030b6c780fa06f3e331.png
[70 46]: /images/20220525/20a3eab3a2f1432aacb7af82681b20e4.png
[70 47]: /images/20220525/e8f33f6bb97543efa11f8f75483c3655.png