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