MySQL表的增删查改

目录

一 插入

1 基本语法

①全列插入

②指定列插入

③多条记录插入

④冲突更新

二 查询

 查询全部数据

 指定列查询

显示

拼接 

取别名

去重查找

where

逻辑运算符和比较运算符

结果排序

Limit

group by 分组

聚合函数

  对于count

  对于sum

对于group by

  相关的语法细节：

    ①如果有多个分组条件？

    ②关于显示

    ③关于分组后筛选

关于执行顺序

三 UPDATE 更新

四 DELETE/TRUNCATE 删除

DELETE

语法

注意：

TRUNCATE

---

 

一 插入

1 基本语法

先创建一个表之后插入

有几种写法，可以基本分类成是否带上列名，是否是多条记录插入，是否有冲突更新

①全列插入

不带列名，默认是一一对应的全列插入

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

示例

 全插入的话自增的值也是要指定的

②指定列插入

带上列名，默认插入的值是和自己指定的列名的顺序一一对应

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

 可以省略自增的值

注意：

此时如果默认省略了列名，进行全列插入的话，不能够省略自增字段的值，不会自动更新

此时如果指定了列名进行插入的话，可以省略自增长的值，会自动补充

③多条记录插入

 在上述两种插入的基础上增加新的记录

INSERT INTO table_name
VALUES (value1, value2, value3, ...)，(value1, value2, value3, ...)，(value1, value2, value3, ...);

或者

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)，(value1, value2, value3, ...)，(value1, value2, value3, ...)；​

④冲突更新

冲突理解

如果表中已经存在的主键或者唯一键和我要插入的值是一样的，此时就发生了冲突，如果发生冲突的话，mysql就会直接进行拦截，不允许该信息的插入。

本质：插入冲突是因为我们之前存在相同的值，如果不想让操作失败的话，可以先进行更新再插入。

其实就是一种替换，可以选择删除之前的数据再插入新的值。但是主键和唯一键不被修改，其他的被修改

语法

INSERT INTO …… 
ON DUPLICATE KEY UPDATE column = values [,column = value]…… 

其实replace可以替换掉上述的语句，两者的意思是一样的

示例

有冲突，并且冲突值不一样

有冲突但是冲突值和之前是一样的

没有冲突直接插入

 和replace是一样的

影响

此时分为几种情况：

①插入的时候无冲突，直接插入，一行受影响-----》只有自己插入的这一行受影响

②插入的时候发生键值冲突了，但是冲突的值和之前的是一样的，此时0行受影响

③插入的时候发生键值冲突了，并且冲突的值不一致，这时候就会删除之前的，然后插入新的，（假设之前只有1行记录冲突），那么就是两行受影响

二 查询

在一个数据库内，做一个表的组合查询

语法

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

先来简单介绍一下这些意思，

from就是要指明在哪些表中进行查找

where的话设定对应的查询条件，通过逻辑运算符或者算数运算符进行设置

order by的话是排序的意思，有升序和降序，默认是升序

limit的话，是筛选出指定行。我们可以和order by搭配使用，这样的话，就可以选出符合条件的前几条或者最后几条数据

演示一下对应的操作，先创建一个表并且插入数据做准备

 

 1 查询全部数据

后面不带任何的筛选条件就是查询所有的数据。但是这样的话，如果对于数据量非常大的表来说的话，数据量非常大，而且筛选不出符合自己条件的。 

 指定列查询

可以只查询自己关心的列。但是也是所有数据的。

显示

我们在查询列的时候，显示结果是跟着自己的指定列的，而非创建表的时候的顺序 

 

拼接 

我们查找的时候还可以拼接常数或者计算式或者字符串

取别名

as 取别名 （取别名的时候也可以不加as）

去重查找

规则：要根据对应查找的内容整体来判断的

比如这里根据名字和数学成绩一起去重，虽然数学成绩有重复的，但是名字是没有重复的，也没有数据被筛选掉，都被看成不重复的

 

 这样才会去重

where

主要使用两大类运算符来进行筛选：

逻辑运算符和比较运算符

以下是比较运算符的种类

运算符	说明
> >= < <=	大于 大于等于 小于 小于等于
=	等于 不能对NULL运算
<=>	等于 可以用来比较NULL
!= , <>	不等于
BETWEEN AND	闭区间内的一个值
IN（option1，option2，……）	如果是option中的任意一个，就可以返回TRUE
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配 %匹配多个 _匹配一个

>,>=,<,<=一般来说是可以来比较整数或者浮点数的大小的

比如这个语句就筛选出了薪水大于1000的 

=除了比较数字之外，还可以用来比较字符串，但是不能对NULL进行比较

注意：mysql中的等于是=，而非语言中的==

对于NULL值的运算可以使用<=> 或者 IS （NOT）NUL

注意null值和空串的区分

对于空串是可以使用 = ' '进行筛选出来的，但是null的话只能使用<=> 或者IS（NOT）NULL运算

‘ ’表示的是空串，他是个字符串类型，只不过内容是空的

NULL一般不参与计算，=这个运算符只能比较存在的数据，因此对于这个不存在的NULL不能进行比较  

查找一个区间内的数字 

一般整数和浮点数才能进行查找

比如筛选出工资在1000到2000的

 这个其实和这个查找语句是等同的

查找在一个集合内的

成功找到就返回1，找不到就返回-1

 也可以查看某一条记录的某个数据是否在集合范围内

模糊匹配

%多个匹配

-一个匹配

查找以A开头的姓名的人的所有信息

 查找姓名中有A的

逻辑运算符

这个是用来连接多个条件的，有时候查询条件可能不止符合一个

运算符	注释
AND	多个条件都必须为真才为真
OR	任意一个条件为真就可以为真
NOT	如果为真，结果就是假

其他的使用也是类似的

需要注意的是，最终结果的显示和查找的条件中的列数可以不相等。比如想查找符合某条件的，但是最终显示的时候不显示对应列也是可以的

比如这个查找的对应工资要大于1000的，但是最后只显示工资大于1000的人的对应的姓名

结果排序

我们可以对查找的结果按照某些顺序进行排序，比如可以按照薪资的多少进行排序，默认是升序，如果我们想按照降序排列带上des

 排序不是筛选条件是显示条件

 这里一些sal相同的也有顺序，其实是因为这个排序是以排序的关键字为key值进行排序，把整条记录都进行排序了。

对于NULL的话，默认是最小的，如果是升序排序就出现在最上面，降序排序就出现在最下面

Limit

如何查找符合某些条件的前几名

这样就选出了工资最少的前三名 

 

也可以指定一个区间，起始下标从0开始

 

 利用这个条件筛选可以实现分批取出数据的目的，一页limit多少数据，这一页放不下就放到下一页

group by 分组

在讲这个的时候，引入聚合函数的概念。因为聚合函数一般都是和group by搭配使用的

聚合函数

mysql内部本身就挺了一些数据库的函数以供我们使用。

对于聚合函数的理解

聚合函数是直接或者简介对列的数据进行统计。由于是列的数据，那么就具有相同的属性。换句话说，相同属性的就具有了聚合函数统计的前提

并且由于是列统计，那么对于一个表的数据进行统计一定是一行或者多行的（一批数据）

总结：对列，具有相同属性 对于一批数据

函数	注释
COUNT	返回查询到的数据的数量
SUM	返回查询到的数据的总和，必须是数字
AVG	返回查询到的数据的平均值，必须是数字
MAX	返回查询到的数据的最大值，必须是数字
MIN	返回查询到的数据的最小值，必须是数字

对于count

想查询有多少条记录

也可以进行重命名

其他的写法

所有的记录*

Select 1的时候其实已经把所有的数据都筛选出来了，只不过显示的时候只显示1

这样我们有多少条记录，就有多少个1。

 

 注意直接筛选的时候，null是不参与运算的，也要注意‘ ’为了避免空串的统计可以在条件中设置

 去重

如果不想统计的数据中有重复的，我们就可以使用这个进行去重

 

 执行的时候先去选出符合条件的不重复的数据，之后再进行统计是比较好的；如果先统计再计算的话，不符合要求，去重之后还需要再次统计，没意义

如果是distinct count（math）的话，是先去统计math的，已经统计了重复的。之后选出数据之后再去重，只不过此时不显示，这样的话我们看到的是7（统计没去重的）

如果是count（distinct math）的话， 是要先对count内部的math做去重之后再进行统计

总结：count的话，是先执行count内的

count都是先有最后的结果数据再统计的

对于sum

也是差不多的，可以计算总和，总和计算出来之后可以计算平均值 

 

这个相当于

同理，其他的聚合函数也是这么使用的

都是遍历列之后聚合的

对于group by

对指定列分组查询。

之间我们select查询的时候，都是将数据作为一个整体来查询的。其实，mysql也可以支持按照指定的列对数据进行分组，在分组的数据中操作。

比如，可以查找某个部门的最高，最低工资，平均工资

group by和聚合函数搭配使用，执行的顺序是先去执行group by对结果进行分组，之后再去每一组里面进行聚合。如果先执行聚合的话，是对所有数据的，而非组的。

比如上面的，就是先按照部门把人分出来，之后在每一个部门里面分别查找最高最低和平均的工资

相关的语法细节：

①如果有多个分组条件？

比如这个按照部门和工作进行分组，就是先对部门进行分组，再在部门内部根据工作进行分组 

其实相当于一棵多叉树的结构，先按照一个分组的依据，分成一个一个的小表，之后再在小表的内部继续分。

②关于显示

只有group by作为分组条件的列之后在显示的时候才能够显示出来。比如我这里显示想显示部门号和工作，但是我在输入分组条件的时候没有把工作作为分组的依据，因此就不能

 ③关于分组后筛选

由于where筛选的时候是先有对应的条件才会去对数据进行筛选；但是聚合函数是先有了对应的数据才会去聚合，分组。因此where后面是不能够带上group by的

如果想对group by的结果进行筛选的话，应该需要使用having语句

这里是对group by分组出来的数据，再进行having条件的筛选

 总结

group by的话一定会和聚合函数搭配使用统计

如果需要对分组后的条件再进行筛选的话，需要用having语句进行筛选。只有group by才使用having

having与where的区别

where对数据进行初筛；having 对where筛选和group by分组之后的数据再次进行筛选

where的话，执行语句是比较靠前的，是在分组之前先按照where的条件进行筛选，符合条件才会进入group by 的having

having之前通常有聚合函数统计和group by分组

举例子：比如我需要筛选出工资>1000的人，在这条件之下按照部门分组，平均工资<2000的人的相关信息

①先去from emp找到对应的表的数据

②where 对表的数据进行筛选

③group by分组

④avg聚合

⑤对这样分组和聚合的数据进行 having条件筛选

关于执行顺序

where条件设置的时候吗，是按照这样的一个逻辑来查找数据的：

①我们先要去找到对应的表，因此是from先找到表

②之后利用where进行数据的筛选

③group by分组，以便执行聚合函数和having

④执行聚合函数，如果之后有having筛选条件在having最后显示的时候筛选

⑤这样我们就完成了对应的条件的筛选，再去执行select找出符合的对应的列

⑥有了这样的数据才会知道有没有重复的，再去考虑distinct去重

⑦最后order by排序，因为还有一个limit是要在所有的数据都执行的差不多了的情况下才会去找出对应的几条数据

from -》where-》group by -》聚合函数-》having -》 select -》 distinct-》as-》order by -》limit

我们可以这样分类了之后去理解，有两类查询：where和having。与where相关的先用红色标出，橙色的也属于红色，只不过select后面可以重命名和去重比较特殊单独再拎出来

我们执行的时候先去执行where的执行条件，再去看having的

要执行where必须有数据，数据从表来，因此最开始from

from找得出数据后就可以where先进行一轮筛选，筛选的数据如果还需要分组等的筛选，就去执行蓝色的逻辑

这样就相当于完成了两轮的筛选，然后我们再根据一些显示的要求进行设置

order by在select之后是因为必须有select选出的数据再去排序才有意义，防止了一些重复的工作

最后limit一定是最后的，完成了所有工作之后才能去选出符合条件的

三 UPDATE 更新

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

更新一般是搭配查询来使用的，把符合条件的数据进行更新。因为只有有了对应的数据，才能对符合条件的数据进行更新。因为如果不配合对应的WHERE语句的话，就把所有的数据都更新了

 这样就把对应的名字为KING的员工的薪水修改成了99999；

相应的，数据一旦被修改，那么按照之前的条件进行筛选的话，很有可能会产生差异

四 DELETE/TRUNCATE 删除

删除这个操作，一般会带来比较严重的后果，所以不能够轻易地进行删除，只有特定权限的才能删除。

与UPDATE同理，删除的时候一定要注意筛选条件，否则很有可能就把所有的数据都删除了

DELETE

语法

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

注意：

删除数据仅仅是把对应的记录删除了，但是对于auto_increment并不改变，下一次插入的数据也是接着上一个auto_increment的值继续往后插入的

比如我这边有5个数据，我现在删除了1个数据，auto_increment的值并不会变成5，也就是说下一次插入的时候，会被自动分配6

TRUNCATE

TRUNCATE TABLE name

这个操作的特点是不能像DELETE一样，设置删除的条件，只能整体删除

这一个操作导致的后果就是表被清空的同时，auto_increment的值也会被修改，从初始值开始。

因为TRUNCATE在删除的时候，比DELETE更快，因为他不经过真正的事务，所以无法进行回滚。它不是对数据进行操作，也不会更新日志，直接把表清空，比delete要快是因为她的工作量更少。他直接干掉表数据，对应的索引结构，日志信息。没法回滚了。