MySQL：增删改查

1.1、基本的增删改查语句

插入数据

增删改查俗称为CRUD，这也是MySQL运行之后执行次数最多的一类SQL语句，同时也是每位开发者写的最多的SQL语句，接下来则说说这块的语句，首先登场的是咱们的几位老伙伴，即insert、delete、update、select...这类普通SQL语句。

• insert into 表名(字段名...) values(字段值...);：向指定的表中插入一条数据。

• insert into 表名(字段名...) values(字段值...),(...)...;：向表中插入多条数据。

• insert into 表名 set 字段名=字段值,...;：插入一条数据，但只插入某个字段的值。

如果要插入一条完整的数据，字段名可以用*代替所有字段，除开上述两种插入数据的基本方式外，还有几种批量插入的方式，如下：

-- 使用insert语句批量插入另一张表中查询的数据
insert into 表名(字段名...) select 字段名... from 表名...;

-- 使用replace语句来实现批量插入
replace into 表名(字段名1,字段名2...) values(字段值....),(字段值...),...;

上述批量插入数据的方式中，还可以通过replace关键字来实现插入，它与insert有啥区别呢？答案在于它可以实现批量更新，使用replace关键字来插入数据的表必须要有主键，MySQL会根据主键值来决定新增或修改数据，当批量插入的数据中，主键字段值在表中不存在时，则会向表中插入一条相应的数据，而当插入数据中的主键值存在时，则会使用新数据覆盖原有的老数据。

删除数据

• delete from 表名;：删除一张表的所有数据。

• delete from 表名 where 条件;：根据条件删除一条或多条数据。

• truncate table 表名：清空一张表的所有数据。

修改数据

• update 表名 set 字段名=字段值,...;：修改表中所有记录的数据。

• update 表名 set 字段名=字段值,... where 条件;：根据条件修改一条或多条记录的数据。

• replace 表名(字段名1,...) values(字段值...),...;：批量修改对应主键记录的数据。

查询数据

• select * from 表名;：查询一张表的所有数据。

• select * from 表名 where 条件;：根据条件查询表中相应的数据。

• select 字段1,字段2... from 表名 where 条件;：根据条件查询表中相应数据的指定字段。

• select 函数(字段) from 表名;：对查询后的结果集，进行某个函数的特殊处理。

上述三种是最基本的查询方式，接着来看一些高级查询语法，如下：

-- 为查询出来的字段取别名
select字段1as别名,...from表名where条件;
select字段1别名,...from表名;

-- 为查询出的表取别名
select*from表名as别名;

-- 以多条件查询数据
select*from表名where字段1=值1and字段2=值2and...;-- 所有条件都符合时才匹配
select*from表名where字段1=值1or字段2=值2or...;-- 符合任意条件的数据都会返回
-- =符号，可以根据情况换为>、<、>=、<=、!=、between and、is null、not is null这些

-- 对查询后的结果集使用函数处理
select函数(字段)from表名where条件;

-- 对查询条件使用函数处理
select*from表名where函数(条件);

-- 模糊查询
select*from表名where字段like"%字符";-- 查询字段值以指定字符结尾的所有记录
select*from表名where字段like"字符%";-- 查询字段值以指定字符开头的所有记录
select*from表名where字段like"%字符%";-- 查询字段值包含指定字符的所有记录

-- 按照多值查询对应行记录
select*from表名where字段in(值1，值2,...);
-- 按照多值查询相反的行记录
select*from表名where字段notin(值1，值2,...);
-- 基于多个字段做多值查询
select*from表名where(字段1,字段2...)in((值1，值2,...),(...),...);

-- 只需要查询结果中的前N条数据
select*from表名 limit N;
-- 返回查询结果中 N~M 区间的数据
select*from表名 limit N,M;

-- 联合多条SQL语句查询（union all表示不去重，union表示对查询结果去重）
select*from表名where条件
unionall
select*from表名where 条件;

当然，对于MySQL中支持的函数稍后再展开聊，下面再聊聊一些其他的高级查询语法，如分组、过滤、子查询、关联查询等。

分组过滤、数据排序

写SQL语句时，有些需求往往无法通过最基本的查询语句来实现，因此就需要用到一些高级的查询语法，例如分组、过滤、排序等操作，接着先聊聊这个，语法如下：

-- 基于一个字段进行排序查询
select*from表名orderby字段名asc;-- 按字段值正序返回结果集
select*from表名orderby字段名desc;-- 按字段值倒序返回结果集
select*from表名orderby字段1asc,字段2desc;-- 按照多字段进行排序查询

-- 基于字段进行分组
select*from表名groupby字段1,字段2....;

-- 基于分组查询后的结果做条件过滤
select*from表名groupby字段1having 条件;

实际上group by、having这些语句，更多的要配合一些聚合函数使用，如min()、max()、count()、sum()、avg()....，这样才能更符合业务需求，但对于聚合函数后面再介绍，先简单说说where、having的区别：

这两个关键字都是用来做条件过滤的，但where优先级会比group by高，因此当分组后需要再做条件过滤时，就无法使用where来做筛选，而having就是用来对分组后的结果做条件过滤的。查询语句中的各类关键字执行优先级为：from → where → select → group by → having → order by。

子查询

子查询也可以理解成是查询嵌套，是指一种由多条SQL语句组成的查询语句，语法如下：

-- 基于一条SQL语句的查询结果进一步做查询
select*from(select*from表名where条件)as别名where条件;

-- 将一条SQL语句的查询结果作为条件继续查询（只适用于子查询返回单值的情况）
select*from表名where字段名=(select字段名from表名where条件);

-- 将一条SQL语句的查询结果作为条件继续查询（适用于子查询返回多值的情况）
select*from表名where字段名exists(select字段名from表名where条件);
-- 上述的exists可以换为not exists，表示查询不包含相应条件的数据

-- 将一条SQL语句的多个查询结果，作为条件与多个字段进行范围查询
select*from表名where(字段1,字段2...)in(select字段1,字段2...from 表名);

在上述子查询语法中，exists的作用和in大致相同，只不过not in时会触发全表扫描，而not exists依旧可以走索引查询，因此通常情况下尽量使用not exists代替not in来查询数据。

关联查询

关联查询也被称之为连表查询，也就是指利用主外键连接多张表去查询数据，这几乎也是日常开发中写的最多的一类查询语句，MySQL中支持多种关联类型，如：

• 交叉连接

• 内连接

• 外连接：

      • 左外连接

      • 右外连接

      • 全外连接

语法如下：

-- 交叉连接：默认把前一张表的每一行数据与后一张表的所有数据做关联查询
select*from表1,表2...;-- 这种方式默认采用交叉连接的方式
select*from表1crossjoin表2;-- 显式声明采用交叉连接的方式

-- 内连接：只返回两张表条件都匹配的数据
-- 隐式的内连接写法
select*from表1,表2...where表1.字段=表2.字段...;
-- 等值内连接
select*from表1别名1innerjoin表2别名2on别名1.字段=别名2.字段;
-- 不等式内连接
select*from表1别名1innerjoin表2别名2on别名1.字段<别名2.字段;

-- 左外连接：左表为主，右表为次，无论左表在右表是否匹配，都返回左表数据，缺失的右表数据显示NULL
select*from表1leftjoin表2on表1.字段=表2.字段;

-- 右外连接：和左连接相反，右表为主，左表为次，永远返回右表的所有数据
select*from表1rightjoin表2on表1.字段=表2.字段;

-- 全外连接：两张表没有主次之分，每次查询都会返回两张表的所有数据，不匹配的显示NULL
-- MySQL中不支持全连接语法，只能通过union all语句，将左、右连接查询拼接起来实现
select*from表1leftjoin表2on表1.字段=表2.字段
unionall
select*from表1rightjoin表2on表1.字段=表2.字段;

-- 继续拼接查询两张以上的表
select*from表1leftjoin表2on表1.字段=表2.字段leftjoin表3on表2.字段=表3.字段;
-- 通过隐式连接的方式，查询两张以上的表
select*from表1,表2,表3...where表1.字段=表2.字段and表1.字段=表3.字段...;
-- 通过子查询的方式，查询两张以上的表
select*from
(表1as别名1leftjoin表2as别名2on别名1.字段=别名2.字段)
leftjoin
表3as别名3on别名1.字段=别名3.字段;

对于连表查询的语法相信大家都并不陌生，因此不做过多产生，重点讲一下多表联查时的笛卡尔积问题，所谓的笛卡尔积问题就是指两张表的所有数据都做关联查询，一般连表查询都需要指定连接的条件，但如果不指定时，MySQL默认会将左表每一条数据挨个和右表所有数据关联一次，然后查询一次数据。比如左表有3条数据，右表有4条数据，笛卡尔积情况出现时，一共就会查询出3 x 4 = 12条数据。

笛卡尔积现象出现时，会随着表数据增长越来越大，因此在连表查询时一定要消除笛卡尔积问题，咋消除呢？其实就是指定加上关联条件即可。