木偶笔记
Pupper
2022-03-29
目录

MySQL 增删改查

# 一、 基础查询语句

# select 语法格式：

select 字段名 from 表名 where 表达式
1

# 实例 1：查询表的所有字段

select * from yytest;
1

img

# 知识点

  • * 通配符，代表查询所有字段

  • 使用 * 时，只能按照数据表中字段的顺序进行排列，不能自定义字段排序

  • 建议：不知道所需查询的列名称时，才用 * ，否则获取不需要的列数据会降低查询和所使用应用程序的效率

# 实例 2：查询表的指定字段

# 查询表指定的字段（全部）
select id,username,sex,birth,department,address,poloyy6 from yyTest;

# 查询指定字段
select id,username from yyTest;
1
2
3
4
5

img

# 知识点

  • 可以指定表的所有字段，然后更改字段顺序， 这种查询所有字段的写法比较灵活
  • 也可以只指定某几个字段，多个字段用 , 隔开

# 常见关键字的执行顺序：

from on join --- 表连接 where --- 查询条件 group by --- 分组查询 having --- 过滤分组结果 select distinct --- 数据去重 order by --- 对查询结果进行排序 limit --- 显示查询结果的条数

# 1. distinct 去重

当使用distinct的时候，只会返回指定的字段，其他字段都不会返回，所以查询语句就变成去重查询语句

# 语法：

select distinct 字段名，字段名 from 表名;
1

# 知识点

  • distinct只能在select语句中使用

  • distinct必须在所有字段前面

  • 如果有多个字段需要去重，则会对多个字段进行组合去重，即所有字段的数据重复才会被去重

# 实例：

img

# 实例 1：对单个字段去重

select distinct age from yyTest;
1

img

# 实例 2：对多个字段去重

select distinct sex,age from yyTest;
1

img

# 实例 3：查看去重字段有多少种值

select count(distinct age) from yyTest;
1

img

# 错误实例：

select username,distinct age from yyTest;  
select distinct age，distinct username from yyTest;
1
2

# 2. limit 限制数据返回量

# 1. 指定初始位置

语法：

limit 初始位置，记录数
1

知识点：

  • 初始位置从 0 开始
  • 初始位置 和 记录数都必须是正整数

实例：

img

select * from yyTest limit 2,2;
1

img

# 2. 不指定 初始位置

语法：

limit 记录数
1

知识点：

  • 记录数 > 表中总记录数时，返回所有记录数
  • 默认起始位置是第一条记录

实例：

select * from yyTest limit 5;
1

img

# 3. order by 对查询结果进行排序

语法：

order by 字段名 asc|desc
1
  • asc ： 升序，默认值
  • DESC ： 降序

实例：

img

实例 1： 根据 id 倒序

select * from yyTest order by id desc;
1

img

实例 2： 先根据 sex 倒序，再根据 height 升序

select * from yyTest order by sex desc, height asc;
1

img

# 4. like 模糊查询

语法：

like "字符串"
not like "字符串"
1
2
  • NOT：取反，不满足指定字符串时匹配
  • 字符串：可以是精确的字符串，也可以是包含通配符的字符串
  • LIKE支持 % 和 _ 两个通配符

% : 表示任意长度的 字符串

_ : 表示单个字符

# 使用通配符的注意点

  • 注意大小写：不加 binary 关键字的话，大小写是不敏感的
  • 注意头部、尾部 多余的空格 ： " test% " 是不会匹配到“test1”的
  • **注意NULL：**通配符是不能匹配到字段为NULL的记录的
  • 不要过度使用通配符：因为Mysql对通配符的处理速度会比其他操作花费更长的时间
  • **在确定使用通配符后：除非绝对有必要，否则不要把它们用在字符串的开始处，**把通配符置于搜索模式的开始处，搜索起来是最慢的。

# 实例：

img

实例 1: 查询username字段开头不为test且department字段等于seewo的记录

select * from yyTest where username not like "test%" and department = "seewo";
1

img

实例 2：查询username字段test开头且后面只跟一个字符结尾的记录

select * from yyTest where username like "test_";
1

img

# like 区分大小写

  • 默认 like 匹配的字符串是不区分大小写的；
  • 如果需要区分大小写，需要加入 binary 关键字
select * from yyTest where username like binary "TEST_";
1

# 使用转义字符

  • 如果查询的字符串包含%，可以使用 \ 转义符
  • **实际场景：**搜索功能，搜索框只输入%看是否返回所有记录，如果是的话证明没有做转义可以提个优化项哦！
select * from yyTest where username like "%\%"
1

# 5. between and 查询范围

# 语法：

between 值1 and 值2

not between 值1 and 值2
1
2
3
  • 取值1：范围的起始值
  • 取指2：范围的终止值
  • NOT：取反，不在取值范围内的值将被返回

# 实例：

img

实例 1： 查询年龄在19-21之间的记录

select * from yyTest where age between 19 and 21;
1

img

# 6. is null 空值查询

is null
is not null
1
2
  • is null 是一个整体，不能用 = null 替代
  • is not null 同理，不能用 != null 或 <>替代

实例：

# 查询 sex 字段为空的记录
select * from yyTest where sex is null;

# 查询 sex 字段不为空的记录
select * from yyTest where sex is not null;
1
2
3
4
5

# 7. group by 分组查询

语法：

group by 字段名
1
  • group by 一般都会结合Mysql聚合函数来使用
  • 如果需要指定条件来过滤分组后的结果集，需要结合 having 关键字；
    • **原因：**where不能与聚合函数联合使用 并且 where 是在 group by 之前执行的
  • 分组之后，只会返回组内第一条数据；

# 实例：

img

实例 1： 对 sex 单个字段进行分组查询

分组之后，只会返回组内第一条数据

select * from yyTest group by sex;
1

img

实例 2： 先按照age进行分组，然后再在每个组内按department分组

  • 多个字段分组查询时，先按照第一个字段分组，如果第一个字段有相同值，则把分组结果再按第二个字段进行分组，以此类推
  • 如果第一个字段每个值都是唯一的，则不会按照第二个字段再进行分组了
select * from yyTest group by age,department;
1

img

# group by + 聚合函数

  • count()：统计记录的条数
  • sum()：字段值的总和
  • max()：字段值的最大值
  • min()：字段值的最小值
  • avg()：字段值的平均值
# count统计条数
select count(*) from yyTest group by department;

# sum总和
select sum(age) from yyTest group by department;

# max最大值
select max(age) from yyTest group by department;

# min最小值
select min(age) from yyTest group by department;

# avg平均值
select avg(age) from yyTest group by department;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# group by + with rollup

with rollup用来在所有记录的最后加上一条记录，显示上面所有记录每个字段的总和

select sum(age) from yyTest group by department with rollup;
1

img

# 8. having 过滤分组结果集

where 和 having 的一些差异性

where having
不可以使用聚合函数 可以使用聚合函数
数据 group by 前过滤 数据 group by 后过滤
查询条件中不可以使用字段别名 查询条件中可以使用字段别名
用于过滤数据行 用于过滤分组后的结果集
根据数据表的字段直接过滤 根据已查询出的字段进行过滤

语法：

having 查询条件
1

# 实例：

img

group_concat()： 可以将分组后每个组内的值都显示出来

实例 1： 单独使用 having

select *,GROUP_CONCAT(username) from yyTest group by age having department = "seewo";
1

img

实例 2： having + where

select *,GROUP_CONCAT(username) from yyTest where sex = "1" group by department having department = "seewo"
1

img

实例 3： having + where + 聚合函数

select *,GROUP_CONCAT(date) from yyTest where sex = "1" group by department having max(date) > "2020-05-08";
1

img

# 二、 多表联查

# 1. inner join 内连接

只有两张表相互匹配到的数据才会返回**（满足查询条件的数据）**，简单理解就是：取交集

语法：

select 字段名 from 表1 inner join 表2 no子句
1
  • inner join 可以连接 ≥ 两个的表
  • inner join 也可以使用 where 来指定连接条件，但是 inner join ... on 是官方标准写法，而且 where 可能会影响查询性能
  • inner join 也可以只写 join 不加 inner

# 实例：

image-20220323161827022

实例 1： 查询每个员工的部门详细信息

select * from emp as a inner join dept as b on a.dept_id = b.id;
select * from emp as a join dept as b on a.dept_id = b.id;
1
2

img

# 自连接： 同一张表相连

实例 2 ： 查询有leader的员工以及leader信息

select * from emp as a inner join emp as b on a.leader = b.id;
1

img

# **不等值连接：**查询条件的逻辑运算符是大于或小于

实例 3：

select * from emp as a inner join dept as b on a.dept_id > b.id;
1

img

# 2. left / right join 外连接

  • left join的主表是左表，从表是右表
  • right join的主表是右表，从表是左表
  • 外连接会返回主表的所有数据，无论在从表是否有与之匹配的数据，若从表没有匹配的数据则默认为空值（NULL）
  • 外连接只返回从表匹配上的数据
  • **重点：**在使用外连接时，要分清查询的结果，是需要显示左表的全部记录，还是右表的全部记录

语法：

select <字段名> from <表1> left join <表2> <ON子句>
select <字段名> from <表1> right join <表2> <ON子句>
1
2

# 实例：

image-20220323161827022

实例 1：left join

select * from emp as a left join dept as b on a.dept_id = b.id;
1

img

实例 2： left join + where

select * from emp as a left join dept as b on a.dept_id = b.id where b.id is null;
1
  • **on：**筛选两张表可以进行连接数据
  • **join：**将筛选后的数据连接起来
  • **where：**将连接后的数据结果集再次条件筛选

img

实例 3： right join

select * from emp as a right join dept as b on a.dept_id = b.id;
1

img

# 3. union 全连接

# 语法：

[sql1]
UNION [ALL | DISTINCT]
[sql2]
UNION [ALL | DISTINCT]
[sql3]
....
1
2
3
4
5
6
  • ALL：可选参数，返回所有结果集，包含重复数据
  • distinct：可选参数，删除结果集中重复的数据（默认只写 union 也会删除重复数据，所以不加也没事）

# union all

select * from emp as a left join dept as b on a.dept_id = b.id
union all
select * from emp as a right join dept as b on a.dept_id = b.id;
1
2
3

img

蓝色圈子：第一条 sql 的查询结果

红色圈子：第二条 sql 的查询结果

# union

select * from emp as a left join dept as b on a.dept_id = b.id
union
select * from emp as a right join dept as b on a.dept_id = b.id;
1
2
3

img

  • 使用 union 连接的多条sql，每个 sql 查询出来的结果集的字段名称要一致
  • 最终 union 连接查询的结果集的字段顺序会以第一个 sql 查出来结果集的字段顺序为基准

# 4. in / exists 子查询

in exists
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE； 用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；
适合外表大而内表小的情况 适合内表大而外表小的情况

# 比较运算符：

实例 1： 查询部门是销售部的员工信息

select * from emp where dept_id = (select id from dept where name = "销售部")
1

img

实例 2： 查询部门不是销售部的员工信息

select * from emp where dept_id <> (select id from dept where name = "销售部")
1

img

# in

select * from emp where dept_id in (select id from dept where name = "财务部" or name ="销售部")
1

img

# not in 的栗子

select * from emp where dept_id not in (select id from dept where name = "财务部" or name ="销售部")
1

img

# exists + 其他查询条件

select * from emp where exists (select * from dept where id = 1) and dept_id = 2
1

img

# 5. regexp 正则表达式查询

推荐用 like 模糊匹配中文字符

语法：

字段名 regexp 表达式;
1
选项 说明 例子 匹配值示例
^ 匹配文本的开始字符 '^b' 匹配以字母 b 开头的字符串 book、big、banana、bike
$ 匹配文本的结束字符 'st$' 匹配以 st 结尾的字符串 test、resist、persist
. 匹配任何单个字符 'b.t' 匹配任何 b 和 t 之间有一个字符 bit、bat、but、bite
* 匹配零个或多个在它前面的字符 'f*n' 匹配字符 n 前面有任意个字符 f fn、fan、faan、abcn
+ 匹配前面的字符 1 次或多次 'ba+' 匹配以 b 开头，后面至少紧跟一个 a ba、bay、bare、battle
<字符串> 匹配包含指定字符的文本 'fa' 匹配包含‘fa’的文本 fan、afa、faad
[字符集合] 匹配字符集合中的任何一个字符 '[xz]' 匹配 x 或者 z dizzy、zebra、x-ray、extra
[^] 匹配不在括号中的任何字符 '[^abc]' 匹配任何不包含 a、b 或 c 的字符串 desk、fox、f8ke
字符串{n,} 匹配前面的字符串至少 n 次 'b{2}' 匹配 2 个或更多的 b bbb、bbbb、bbbbbbb
字符串 {n,m} 匹配前面的字符串至少 n 次， 至多 m 次 'b{2,4}' 匹配最少 2 个，最多 4 个 b bbb、bbbb

实例：

# 以 2018 开头
select * from product where product_name regexp '^2018';

# 以 潮 结尾
select * from product where product_name regexp '潮$';

# name 中包含 p 或 s
select * from emp where name regexp '[p,s]';
select * from emp where name regexp '[ps]';
1
2
3
4
5
6
7
8
9
10
11

实例 2：

name 字段包含字母 p ，且 p 后面出现字母 o 的记录，而 * 可以表示0个字符，代表不出现

select * from emp where name regexp 'po*';
1
  • *可以表示 0 个字符

img

查询 name 字段包含字母 p ，且 p 后面出现字母 o 的记录，但 + 表示至少出现1个字符

select * from emp where name regexp 'po+';
1

img

实例 3：

这里的^是取反，不是开头的意思哦！不要混淆

查询 id >=10 且 开头非字母 p 的记录

select * from emp where id >=10 and  name regexp '^[^p]';
1

img

# 三、插入数据

# 1. 语法：

# insert 。。。 values 语法：

insert into 表名(字段名，字段名) values (值，值)
1
  • 字段名：可以不指定，默认指定表的所有列名
  • values：字段有多少个，值就要有多少个，且顺序要对应，否则会报错

# insert ... set 语法：

insert into 表名 set 字段名=值，字段名=值
1

  • insert .. values 可以插入任意行数据

  • insert ... set 每次只能插入一行数据

# 案例：

emp 表

img

案例 1： 不指定字段添加

insert into emp values ("20", "员工1", 3, 1, 1 );
1

案例 2： 指定所有字段添加

insert into emp (id, NAME, dept_id, leader, is_enable)values("20", "员工1", 3, 1, 1);
1

# 四、 修改数据

# 1. 语法

update 表名 set 字段=值，字段=值 where子句 limit子句
1
  • 多指定多个字段，需要用 , 隔开
  • 如果修改的字段有默认值，可以用 default 来设置字段的值，
    • 如： name = default ，这样就会把字段的值修改成默认值
  • where 就不用多说了，一般 update 数据都会指定条件
  • 添加 limit 是为了限制被修改的行数，加不加都行

# 案例

案例 1： 修改单个字段的栗子

update emp set is_enable = 0 where id = 1
1

案例 2： 修改多个字段的栗子

update emp set is_enable = 0,NAME = "修改的名字",dept_id = 2 where id = 1
1

# 五、 删除数据

# 1. 语法

delete from 表名 where 子句 limit 子句
1

案例 1： 删除表中的全部数据

delete from emp;
1

案例 2： 根据条件删除表中的数据

delete from emp where is_enable = 0 or is_enable is null;
1
编辑 (opens new window)
上次更新: 2022/03/29, 10:41:55
MySQL 数据库与表操作
mongoDB基础概述

← mongoDB基础概述→

最近更新
01
MySQL 数据库与表操作
03-29
02
loguru-日志技术
03-29
03
TS 基础
03-29
更多文章>
Theme by Vdoing | Copyright © 2021-2022 Pupper | MIT License
×