目录：

一、数据新增

1、批量插入

批量插入：是一种优化数据逐条插入的方式

MySQL8 以后默认有事务安全，即批量要么都成功要么都失败，不会出现部分问题

全字段批量插入

insert into 表名 values(值列表1),(值列表2),...,(值列表N);

部分字段批量插入（注意字段默认值）

insert into 表名 (字段列表) values(值列表1),(值列表2),...,(值列表N);

2、蠕虫复制

蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表）

蠕虫复制的目标是快速增加表中的数据

  • 实现表中数据复制（用于数据备份或者迁移）
  • 实现数据的指数级递增（多用于测试）
insert into 表名[(字段列表)] select 字段列表 from 表名;

注意事项

  • 字段列表必须对应上
  • 字段类型必须匹配上
  • 数据冲突需要事先考虑

【例】创建一张新表，将t_30表中的数据迁移到新表中

create table t_35(
    id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
)charset utf8;

insert into t_35 select * from t_30;
# 快速让t_35表中的数据达到超过100条（重复执行）
insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35;

3、主键冲突

主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在

主键冲突的业务通常是发生在业务主键上（业务主键本身有业务意义）

主键冲突的解决方案

  1. 忽略冲突：保留原始记录
insert ignore into 表名 [(字段列表)] values(值列表);
  1. 冲突更新：冲突后部分字段更新
insert into 表名 [(字段列表)] values(值列表) on duplicate key update [字段 = 新值...];
  1. 冲突替换：先删除原有记录，后新增记录

效率没有insert高：需要检查是否冲突

replace into 表名 [(字段列表)] values(值列表);

【例1】用户名作为主键的用户注册（冲突不能覆盖）

create table t_36(
    username varchar(50) primary key,
    password char(32) not null,
    regtimr int unsigned not null
)charset utf8mb4;

insert into t_36 values('username','password',12345678);
# 冲突忽略
insert ignore into t_36 values('username','12345678',12345678);

【例2】用户名作为主键的记录用户使用信息（不存在新增、存在则更新时间）

create table t_37(
    username varchar(50) primary key,
    logintime int unsigned
)charset utf8;

insert into t_37 values('username',12345678);
# 冲突更新（替换部分字段数据）
insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp();  # 当前时间戳

【例3】用户名作为主键的记录用户使用信息（不存在新增、存在则更新全部）

create table t_38(
    username varchar(50) primary key,
    logintime int unsigned,
    clientinfo varchar(255) not null
)charset utf8;

insert into t_38 values('username',unix_timestamp(),'{PC:chrome}');
# 替换插入
replace into t_38 values('username',unix_timestamp(),'{phone:uc}');

二、数据查询

1、查询选项

查询选项：用于对查询结果进行简单数据筛选

查询选项是在select关键字之后，有两个互斥值

  • all：默认，表示保留所有记录
  • distinct：去重，重复的记录（所有字段都重复）

【例】查看商品表中所有品类的商品信息：重复的商品只保留一次（名字、价格、属性都一致）

create table t_39(
    id int primary key auto_increment,
    goods_name varchar(50) not null,
    goods_price decimal(10,2) default 99999999.00,
    goods_color varchar(20),
    goods_weight int unsigned
)charset utf8mb4;

insert into t_39 values(null,'mate10',5499.00,'blue',320),
(null,'mate10',5499.00,'gray',320),
(null,'nokia3301',1299,'black',420);

# 考虑所有字段的去重（不含逻辑主键）
select distinct goods_name,goods_price,goods_color,goods_weight from t_39;
select goods_name,goods_price,goods_color,goods_weight from t_39;

# 不考虑颜色去重
select distinct goods_name,goods_price,goods_weight from t_39;
select all goods_name,goods_price,goods_weight from t_39;

image-20210226153035701.png

2、字段选择&别名

字段选择：根据实际需求选择的要获取数据的字段信息

根据实际需求，明确所需要的字段名字，使用英文逗号,分隔

获取所有字段，使用星号*通配所有字段

字段数据可以不一定是来自数据源（select只要有结果即可）

  • 数据常量：select 1
  • 函数或者变量：select unix_timestamp(),@@version （@@是系统变量的前缀，后面跟变量名）

字段的选择只要在保证数据需求能实现的情况下，尽可能少使用*代替（MySQL优化）

  • 减少服务器的数据读取压力
  • 减少网络传输压力
  • 让客户端能够精确解析数据（不用大海捞针）

字段别名：给字段取的临时名字

字段别名使用as语法实现

  • 字段名 as 别名
  • 字段名 别名

字段别名的目的通常为了保护数据

  • 字段冲突：多张表同时操作有同名字段（系统默认覆盖），想保留全部
  • 数据安全：对外提供数据不使用真实字段名字

【例】查询商品信息

# 全部查询
select * from t_39;
# 需求为商品名字和价格
select goods_name, goods_price from t_39;
# 别名使用
select goods_name as gn, goods_price as gp from t_39;
# 获取当前时间戳和版本号(不需要数据源)
select unix_timestamp() as now,@@version as version,@@version;

image-20210226153807999.png

3、数据源

数据源：from关键字之后，数据的来源。只要最终结果是一个二维表，都可以当做数据源

数据源是为查询、检索提供数据支持的，使用时需要明确指定

  • 单表数据源：数据源就是一张表
select * from t1;
  • 多表数据源：数据来源是多张表。利用一张表的一条数据匹配另外一张表的所有记录，记录结果为：记录数 = 表1记录数 * 表2记录数；字段数 = 表1字段数 + 表2字段数（笛卡尔积）
select * from t_27, t_30;

image-20210226154240024.png

  • 子查询数据源：数据来源是一个查询结果

    • 数据源要求必须是一个表
    • 如果是查询结果必须给起一个表别名
select * from (select * from t_27,t_30) t; # 数据有冲突查不出来
select * from (select * from t_27) as t;

image-20210226154436759.png

  • 数据表也可以指定别名

    • 表名 as 别名
    • 表名 别名

数据表的别名在负责SQL查询操作时非常有用，而且有些地方是必须使用（如子查询数据源）

select * from t_30 as t;
select t1.*, t2.stu_name from t_27 as t1, t_30 t2;

4、where 语句

where子句：跟在from数据源之后，对数据进行条件匹配

where是用来匹配条件筛选数据的

where是在磁盘读取后，进入内存之前进行筛选

  • 不符合条件的数据不会进入内存

where筛选的内容因为还没进入内存，所以数据是没有被加工过的

  • 字段别名不能在where中使用

【例1】查询t_35表中学生为lily的成绩信息

select * from t_35 where stu_name = 'Lily';

因为where是在磁盘取数据时进行条件筛选，此时数据没有进入内存，所以字段别名是无效的

select stu_name name, score from t_35 where name = 'Lily';

image-20210226154949836.png

5、运算符

运算符：用来进行字段数据运算，配合where进行条件筛选

  • 比较运算符

    • >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于）
    • between A and B：A和B之间（A小于B），包括A和B本身（数值比较）
    • in (数据1,数据2,...数据N)：在列举的数据之中
    • like 'pattern'：像上面样的，用于字符串比较
      • _：单下划线，匹配对应位置的一个任意字符（ab_：ab开头+一个字符，匹配abc，ab1，但不能匹配abcd）
      • %：匹配当前位置（往后）任意数量任意字符（ab%：ab开头+任意数量任意字符，匹配abc，ab1，abcd）

  • 逻辑运算符

    • and（逻辑与）、or（逻辑或）、not（逻辑非）

  • null运算符

    • is null（为空）、is not null（不为空）

【例】查询成绩不及格的所有学生信息

select * from t_35 where score < 60;

【例】查询成绩在60-90之间的学生信息

select * from t_35 where score between 60 and 90;
select * from t_35 where score >= 60 and score <= 90;

【例】查询还没有成绩的学生

select * from t_35 where score is null;

6、group by 语句

group by子句：分组统计，根据某个字段将所有的结果分类，并进行数据统计分析

分组统计可以进行统计细分：先分大组，然后大组分小组

分组统计需要使用统计函数

  • group_concat()：将组里的某个字段全部保留
  • any_value()：不属于分组字段的任意一个组里的值
  • count()：求对应分组的记录数量
    • count(字段名)：统计某个字段值的数量（NULL不统计）
    • count(*)：统计整个记录的数量（较多）
  • sum()：求对应分组中某个字段是和
  • max()/min()：求对应分组中某个字段的最大/最小值
  • avg()：求对应分组中某个字段的平均值

group by子句位置：在 where 之后（where可以没有）

【例】查询学生信息

# 创建一张表，存储学生信息
create table t_40(
    id int primary key auto_increment,
    name varchar(10) not null,
    gender enum('男','女','保密'),
    age tinyint unsigned not null,
    class_name varchar(10) not null comment '班级名称'
)charset utf8;

insert into t_40 values(null,'鸣人','男',18,'木叶1班'),
(null,'佐助','男',18,'木叶1班'),
(null,'佐井','男',19,'木叶2班'),
(null,'大蛇丸','男',28,'木叶0班'),
(null,'卡卡西','男',29,'木叶0班'),
(null,'小樱','女',18,'木叶1班'),
(null,'雏田','女',18,'木叶1班'),
(null,'我爱罗','男',19,'木叶1班'),
(null,'向日葵','女',6,'木叶10班'),
(null,'博人','男',8,'木叶10班'),
(null,'鼬','男',28,'木叶0班');

统计每个班的人数

select count(*), class_name from t_40 group by class_name;

统计每个班那女学生数量

select count(*), class_name, gender from t_40 group by class_name, gender;

统计每个班里的人数，并记录班级学生的名字

select count(*),group_concat(name),class_name from t_40 group by class_name;
select count(*),any_value(name),class_name from t_40 group by class_name;

image-20210226162126969.png

7、回溯统计

回溯统计：在进行分组时（通常是多分组），每一次结果的回溯都进行一次汇总统计

【例】统计每个班的男女同学数量，同时要知道班级人数总数

只统计每个班的男女同学数量，没有班级汇总

select count(*), class_name, gender, group_concat(name) from t_40 group by class_name, gender;

汇总统计：回溯

select count(*), class_name, gender, group_concat(name) from t_40 group by class_name, gender with rollup;

image-20210226163330253.png

回溯原理

graph TB A(统计开始)-->B[大分组:班级名字分组] B-->|木叶1班|C[木叶1班组<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗] B-->|木叶2班|D[木叶2班组<br>佐井] B-->|木叶0班|E[木叶0班组<br>大蛇丸<br>卡卡西<br>鼬] B-->|木叶10班|F[木叶10班组<br>博人<br>向日葵] C-->G[小分组:性别分组] G-->|男|H[木叶1班组:男<br>鸣人<br>佐助<br>我爱罗] G-->|女|I[木叶1班组:女<br>小樱<br>雏田] D-->J[小分组:性别分组] J-->|男|K[木叶2班组:男<br>佐井] E-->L[小分组:性别分组] L-->|男|M[木叶0班组:男<br>大蛇丸<br>卡卡西<br>鼬] F-->N[小分组:性别分组] N-->|男|O[木叶10班组:男<br>博人] N-->|女|P[木叶10班组:女<br>向日葵] H-->Q[性别回溯<br>木叶1班组:NULL<br>鸣人<br>佐助<br>小樱<br>雏田<br>我爱罗] I-->Q K-->R[性别回溯<br>木叶2班组:NULL<br>佐井] M-->S[性别回溯<br>木叶0班组:NULL<br>大蛇丸<br>卡卡西<br>鼬] O-->T[性别回溯<br>木叶10班组:NULL<br>博人<br>向日葵] P-->T Q-->U[班级名字回溯<br>NULL:NULL<br>全部人] R-->U S-->U T-->U U-->V((回溯统计结束))

8、分组排序

分组排序：在分组后统计结果时可以根据分组字段进行升序或者降序显示数据

默认的系统就会自动对分组结果根据分组字段进行升序排序

可以设定分组结果的排序方式

  • group by 字段名 [ASC]：升序排序（默认）
  • group by 字段名 DESC：降序排序

【例】对分组结果女性优先显示：gender为枚举，男值为1，女值为2

select count(*), class_name, gender, group_concat(name), any_value(name) from t_40 group by class_name, gender;

MySQL8.0开始，group by 默认是没有排序的

除了索引替换排序的优化思路之外的相关排序，我们都必须使用order by来进行

9、having 子句

having子句：类似于where子句，是用来进行条件筛选数据的

having子句本身是针对分组统计结果进行条件筛选的

having子句必须出现在group by子句之后（如果同时存在）

having针对的数据是在内存里已经加载的数据

having几乎能做where能做的所有事，但是where却不一定

  • 字段别名（where针对磁盘数据，那时还没有）
  • 统计结果（where在group by之前）
  • 分组统计函数（having通常是针对group by存在的）

能用where解决问题的地方绝不使用 having

  • where 针对磁盘读取数据，源头解决问题
  • where 能够限制无效数据进入内存，内存利用率较高，而 having 是针对内存数据筛选

【例】获取班级人数小于3的班级

select count(*) as `count`, class_name, group_concat(name) from t_40 group by class_name having `count` < 3;

select count(*) as `count`, class_name, group_concat(name) from t_40 group by class_name having count(*) < 3; # 多用了一次函数（效率降低）

select class_name, group_concat(name) from t_40 group by class_name having count(*) < 3;

image-20210226172016780.png

10、order by 子句

order by子句：排序，根据某个指定的字段进行升序或者降序排序

排序的参照物是校对集

order by子句在having子句字后（如果同时存在）

排序分为升序和降序：默认是升序

  • order by 字段 [ASC]：升序
  • order by 字段 DESC：降序

多字段排序：在根据某个字段排序好后，可以再细分排序

【例】单字段排序：给所有学生按照年纪大小升序排序

select * from t_40 order by age;
select * from t_40 order by age asc;

【例】多字段排序：先性别降序排序，然后按年龄升序排序

select * from t_40 order by gender desc, age;
select * from t_40 order by gender desc, age asc;

11、limit 子句

limit子句：限制数据的获取数量（记录数）

limit子句必须在order by子句之后（如果同时存在）

limit限制数量的方式有两种

  • limit 数量：限制获取的数量（不保证一定能获取到指定数量）
  • limit 起始位置, 数量：限制数据获取的位置以及数量（分页）

【例】获取前三条数据

select * from t_40 limit 3;

【例】获取t_40表中第3条以后的3条数据

select * from t_40 limit 3, 3;
select * from t_40 limit 6, 3;

12、总结

完整语法：

select select选项 字段列表[别名]|* from 数据源[别名] where子句 group by子句 having子句 order by子句 limit 子句;

三、数据更新

限制更新：即更新时对更新的记录数进行限制

限制更新通过limit来实现

限制更新其实是局部更新的一种手段，一般更多情况下是依据条件精确更新

实际开发当中，极少出现这类操作，一般都愿意精准操作（利用where条件明确更新条件）

【例】对会员选3个发送10元红包（添加到账户）

create table t_41(
    id int primary key auto_increment,
    username varchar(50) not null unique,
    password char(32) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_41 values(null,'username1','password',default),
(null,'username2','password',default),
(null,'username3','password',default),
(null,'username4','password',default),
(null,'username5','password',default);

update t_41 set account = account + 10 limit 3;

四、数据删除

1、限制删除

限制删除：限制要删除的记录数

使用limit限制删除数量

一般很少使用限制删除，通常是通过where条件精确删除

delete from t_41 where account = 0 limit 1;

2、清空数据

清空数据：将表中的所有数据清除，并且将表的所有状态回到原始状态

清空数据的本质是先删除表，后创建表

清空数据表是一种比delete更彻底的数据删除方式，所以使用之前必须要慎重

清空数据能够让表的一些变化状态回到原始状态

  • 自增长重新回到初始值

清空语法： truncate 表名

【例】

truncate t_41;