面试秘籍 ｜ 软件测试必备的mysql数据库技术

温馨提醒：本文共10000+字，适用于mysql数据库初学者及提升者，建议收藏慢慢看~~

MySQL 是一款最常见的关系型数据库管理系统（Relational Database Management System），是一款安全、跨平台、高效并与 多种主流编程语言紧密结合的数据库系统。
该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。
MySQL 的象征符号是一只名为 Sakila 的海豚，代表着 MySQL 数据库的速度、能力、精确和优秀本质。
无论是运维、开发、测试，还是架构师，数据库技术是一个必备加薪神器，那么，一直说学习数据库、学MySQL，到底是要学习它的哪些东西呢？

下面allen老师就帮大家梳理下作为软件测试工程师，应该掌握的数据库知识。

由于篇幅过大，现整理了目录，便于大家学习使用：

一、数据库常见概念
二、数据库操作
三、数据库表操作
四、数据库表数据操作
五、子表查询
六、多表查询
七、视图
八、索引
九、高级函数使用

一、数据库常见概念

1、数据库的特点

Mysql是开源的，所以你不需要支付额外的费用
Mysql支持大型的数据库，可以处理拥有上千万条记录的大型数据库
MySQL使用标准的SQL数据语言形式
Mysql可以允许于多个系统上，并且支持多种语言（C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等）
Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB
Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
2、常见的概念

数据库学习过程中，总会被DB、DBMS、DBS、DBA这些名称搞的晕头转向，下面就详细分析这几个常见的概念。
1）数据

对客观事物的符号表示,如数字,字母等
是数据库中存储的基本对象
每一行数据叫一条记录
每一列叫一个字段
2）DB

DB，DataBase的缩写，也叫数据库
DB用于长期存储、组织、共享数据集合
DB使用数据模型(表)来组织、描述、存储我们的数据
具备较小的冗余度（重复性）、数据独立性、易扩展性
3）DBMS

DBMS也叫数据库管理系统，mysql就是一款DBMS
用于创建数据库
用于操作数据库
用于数据库的保护
用于数据库的维护
4）DBA

数据库管理员（DBA）
有重新组织数据的能力
能改变数据的存储结构或数据存储位置
5）DBS

DBS也叫数据库系统
数据库系统=数据+数据库+数据库管理系统+用户+数据库管理员
6）DDL

Data Definition Languages（数据库定义语言），数据库操作数据库、表、列等（这些对象进行操作）

使用的关键字：CREATE、 ALTER、 DROP。

7）DML

Data Manipulation Language（数据操作语言），是对表中的数据进行增、删、改的操作。

使用的关键字：INSERT 、UPDATE、 DELETE。

3、数据库三范式

数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。

1）第一范式（1NF）: 列不可再分

每一列属性都是不可再分的属性值，确保每一列的原子性
两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据
2）第二范式（2NF）：属性完全依赖于主键

第二范式是建立在第一范式的基础上的
要求数据库表中的每个实例或行必须可以被惟一地区分
3）第三范式（3NF）：属性不依赖于其它非主属性

即每个属性都跟主键有直接关系而不是间接关系

二、数据库操作

1、数据库安装及启动

数据库安装可以选择客户端单独安装，也可以选择集成开发环境安装（XAMPP）。

win10系统下完成安装之后，需要将mysql的安装bin目录配置到环境变量。

可以通过navicate工具访问数据库，需要设置数据库的地址、端口、用户名和密码。

也可以在cmd窗口，通过命令行启动数据库。
2、数据库基本操作

主要是进行数据库的增删改查相关的操作，

1）增加数据库

create database 数据库名;

//三种创建方式
create database test03;
create database IF NOT EXISTS test03;
create database if not exists test03 default character set utf8 default collate utf8_general_ci;
1
2
3
4
5
6

default character set utf8：数据库字符集，设置数据库的默认编码为utf8，utf8中间不要"-"
default collate utf8_general_ci:数据库校对规则，ci是case insensitive的缩写，意思是大小写不敏感；相对的是cs，即case sensitive，大小写敏感；
2）删除数据库

drop database 数据库名;
1

3）查看数据库

查找该数据库管理系统下所有的数据库。

show databases;
1

4）选择数据库

选择某个数据库后，就可以对选择的数据库进行后续的操作了（表的创建和数据的增删改查）。

user 数据库名;

三、数据库表的操作

1、创建表

Create table 表名(
属性名1 数据类型 [约束条件],
属性名2 数据类型 [约束条件],
属性名3 数据类型 [约束条件]
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如:CREATE table t1(id int,name VARCHAR(10));
1
2
3
4
5
6
7

2、查看表

show tables;
1

3、删除表

drop table 表名;--删除一张表
drop table 表名1,表名2,...; --删除多张表
1
2

4、修改表

1）现有表添加字段

alter table 表名 add 列名 数据类型;
1

2）删除现有表字段

alter table 表名 drop 列名;
1

3）修改现有表字段名

alter table 表名 change 旧列名 新列名 数据类型;
1

4）修改现有表字段属性

alter table 表名 modify 列名 数据类型;
1

5）显示指定表结构

desc 表名;
1

5、主键约束操作

1）添加主键约束的方式

a、建立表的时候直接添加主键约束
格式一:
	create table 表名(
	列名1 数据类型 primary key,
	列名2 数据类型,
	...
	);

 格式二:
	create table 表名(
	列名1 数据类型,
	列名2 数据类型,
	constraint 主键约束的名字 primary key(列名1)
	);

	说明:主键约束的名字一般取为PK_列名
格式三:
	create table 表名(
	列名1 数据类型,
	列名2 数据类型,
	primary key(列名1)

b、通过alter语句(针对已经存在的表来添加主键约束)
	格式一:alter table 表名 add constraint 主键约束的名字 primary key(列名1)
	格式二:alter table 表名 add primary key(列名1)
	格式三:alter table 表名 modify 列名 数据类型 primary key
	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

2）删除主键约束

alter table 表名 drop primary key;
1

3）联合主键操作

把表的两个列看成一个整体，再给这个整体来进行主键约束的方式。

要求两个列都相同才算重复，才会受到主键的约束。

a、创建表时,创建联合主键
格式一:
	create table 表名(
	列名1 数据类型,
	列名2 数据类型,
	constraint 主键约束的名字 primary key(列名1,列名2)
	);
	说明:主键约束的名字一般取为PK_列名
格式二:
	create table 表名(
	列名1 数据类型,
	列名2 数据类型,
	primary key(列名1,列名2)
	);

b、针对已经有的表添加联合主键
格式一:alter table 表名 add constraint 主键约束的名字 primary key(列名1,列名2)
格式二:alter table 表名 add primary key(列名1,列名2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

6、唯一约束

1）创建唯一约束

a、创建表时,添加唯一束
    方式一：
	create table 表名(
	列名1 数据类据类型 ,
	列名3 数据类型,
	constraint 唯一约束的名字1 unique(列名1),
	constraint 唯一约束的名字2 unique(列名2)
	);
	
方式二：
	create table 表名(
	列名1 数据类据类型 unique,
	列名3 数据类型,

	);



b、给已有的表添加唯一约束
	alter table 表名 add unique(列名);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2）删除唯一约束

alter table 表名 drop index 唯一约束的名字
1

7、默认值约束

表中数据在未进行写入数据之前，指定相应的默认值的用法。

关键字:default
默认值约束可以有多个
1）创建默认值约束

create table 表名(
	列名1 数据类型 default '值',
	列名2 数据',
);
1
2
3
4

2）删除默认值约束

alter table 表名 modify 列名1 数据类型;
1

8、非空约束

要求有非空约束的列中的值不能为空，在插入数据时候该字段必须指定值。

关键字:not null
非空约束可以有多个
1）创建非空约束

a、创建表时,添加非空约束
		create table 表名(
		列名1 数据类型 not null,
		列名2 数据类型,
		列名3 数据类型 not null,
		)

b、针对已有表来添加非空约束
alter table 表名 modify 列名1 数据类型 not null;
1
2
3
4
5
6
7
8
9

2）删除非空约束

alter table 表名 modify 列名1 数据类型;
1

9、外键约束

外键约束实现了表与表之间的关联关系。

关键字：foreign key … references
外键约束是针对两个表来说的
当主表和从表建立起外键约束时，一定是从表中的某个列参照引用主表中的某个列，相当于把一个列当作是连接两个表之间的桥梁
当主表被从表引用时，主表删除记录时，要看从表是否有引用
当向从表中做操作（插入数据），先询问主表的意见
一个表中可以有多个外键约束
当从表中的某个列参照主表的某个列时，要求主表的列必须是主键约束或者唯一约束
同一个数据库中不允许多个重名的外键约束名称

1）创建外键约束a、创建表的同时创建外键约束
	主表:
	create table 表名1(
	列名1 数据类型 primary key,
	列名2 数据类型,unique
	);
	
	从表:在从表上创建外键约束
	create table 表名2(
	列名1 数据类型,
	列名2 数据类型,
	constraint 外键约束的名字 foreign key(从表的列名1) references 主表的名字(主表的列名1),
	constraint 外键约束的名字 foreign key(从表的列名2) references 主表的名字(主表的列名2)
	);
	一般取外键约束的名字用FK_列名

b、针对已经有的表添加外键约束
	alter table 从表名 add constraint 外键约束的名字 foreign key(从表的列名1) references 主表的名字(主表的列名1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2）删除外键约束

alter table 表名 drop foreign key 外键约束的名字;
1

四、表数据的操作

1、插入数据-insert into

格式一:
insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3,...) 
格式二:
insert into 表名 values(值1,值2,值3,...) 
格式三:
insert into 表名 values(值1,值2,值3),(值4,值5,值6),...


1
2
3
4
5
6
7
8

2、删除数据-delete

1）删除标中所有记录

delete from 表名;
1

2）有条件删除表记录

delete from 表名 where 条件;
delete from 表名 where 条件1 and 条件2 and 条件3; --多个条件同时满足的数据就删除
delete from 表名 where 条件1 or 条件2 or 条件3;--只要满足其中一个条件的数据就删除
1
2
3

3、更新数据-update

针对整列的数据进行修改:
	update 表名 set 列名=值;
针对多个列进行数据修改:
	update 表名 set 列名1=值1,列名2=值2,...;
有条件的修改:
	update 表名 set 列名1=值1,列名2=值2 where 条件;
修改过程进行计算:
	update 表名 set 列名=列名+10 where 条件;
1
2
3
4
5
6
7
8

4、查询数据

所谓查询，就是对已经存在于数据库中的数据按特定的组合、条件或次序进行检索。

1）查询语句的基本格式

1、查询的关键字是：select
2、格式
方式一：查询表中的所有字段,*是一个通配符，表示所有字段的所有数据记录
    select * from 表名称;
方式二：可以显示所有字段，但不要所有数据记录（加条件限制）
    条件：字段名=字段值
    select * from 表名称 where 条件
方式三：查询某个（多个）字段的数据 ，也可以加上条件
    select 字段1，字段2 from 表名称 条件 
1
2
3
4
5
6
7
8
9

2）去重复查询-distinct

实现对查询后结果的去重复过滤，一般是按照某个字段去重复。
关键是：distinct
语法：
方式一：
select distinct 字段名 from 表名 条件；
方式二：是把字段1和字段2看作一个整体去重复
select distinct 字段名1,字段名2 from 表名 条件
1
2
3
4
5
6
7

3）别名查询-as

给查询后的字段重新命名显示的过程。
关键字：as
方式一：
select 字段1 别名1,字段2 别名2.. from 表名 where 条件
方式二：用到关键字as
select 字段1 as 别名1,字段2 as 别名2 from 表名 where 条件
1
2
3
4
5
6

4）选择查询

比较：= > < >= <= != <>
逻辑：and、or
范围：between…and、not between…and
条件：in、not in、like

select *from 表名 where 条件;
select * from 表名 where 列名 between 最小值 and 最大值;
select * from 表名 where 列名 not between 最小值 and 最大值;		
select * from 表名 where 列 in(值1,值2,值3);
select * from 表名 where 列 not in(值1,值2,值3);
select *from 表名 where 列名 like '指定模式';
    %代表0个或多个字符
    代表1个字符,有多少个_代表多少个字符
select *from 表名 where 列名 not like '指定模式'
1
2
3
4
5
6
7
8
9

5）限制查询-limit

行数限定是限制查询出现的行数，对查询的结果进行过滤。

select *from 表名 limit 2;---显示2行
select *from 表名 limit 2,3;--从第2行开始显示,一共显示3行
说明:
行数是从0开始数的
[从第几行开始显示] 可写可不写
1
2
3
4
5

6）分组查询-group by
分组一般会和聚合函数结合使用
对一个或多个列的结果集进行分组
having通常和group by 的子句一起使用，having的子句可以包含聚合函数，where不行

1、分组介绍
MySQL中GROUP BY语句用于对某个或某些字段查询分组，并返回这个字段重复记录的第一条，
也就是每个小组（无排序）里面的第一条。
2、分组语法
方式一、按照一个字段分组：
select * from 表名 group by  字段

方式二、按照两个字段分组：
group by后跟多个字段，则按照多个字段分组，只有两个字段都相同才会被分到一个组中。
select * from 表名 group by  字段1，字段2

方式三、group by都会与聚合函数一起使用，以达到复杂的数据查询要求。
select *,count(*) from 表名 group by 字段名

方式四、group by结合着having条件语句进行，不能是where语句
    having后可以跟聚合函数
    where后不能跟聚合函数
    功能上是相似的，都是作为条件语句存在
3、案例
select *,count(*) from 表名 group by 字段名 having 聚合方法的条件
select 班级,sum(语文+英语+数学) as '总成绩' from student group by 班级 having sum(语文+数学)>170;
select *,count(*) as "分组数" from student group by 班级 having 分组数 > 3
select *,count(*) as "分组数" from student group by 班级 having  id>2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

7）排序查询-order by

在MySql中，使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
尤其非常特别重要：默认按升序（ASC）排列。
order by 后可加2个（或多个）字段，字段之间用英文逗号隔开。
若A用升序，B用降序：order by A ASC, B DESC;
若A用升序，B用降序：order by A, B DESC;
若A、B都用降序，必须用两个DESC，order by A DESC, B DESC;
多个字段时，优先级按先后顺序而定。

select 列名 from 表名 order by 列名;
select 列名 from 表名 order by 列名 asc;
select 列名 from 表名 order by 列名 desc;
select 列名 from 表名 order by 列名1 ,列明2;
select 列名 from 表名 order by 列名1 desc ,列名2;
select 列名 from 表名 order by 列名1 desc ,列名2 desc;
1
2
3
4
5
6

五、子查询

子查询是以其他查询的结果为查询条件的一种查询方式。

子查询可以嵌套在增删改查后面
子查询语句是select时，总是用小括号包含起来
子查询分为嵌套子查询和相关子查询
1、嵌套子查询–in

子查询的结果可以有多个。

select 列名 from 表名 where 列名 in (select ....);
例如：查询id>5的所有班级信息
select 班级,name from student where 班级 in (select 班级 from student where id>5 )
1
2
3

2、相关子查询

子查询查出的结果只有一个。

select 列名 from 表名 where 列名=(子查询);

例如：和黄真在同一个班级的人
select 班级,name from student where 班级 = (select 班级 from student where name="黄真" )
1
2
3
4

六、多表查询
如果要查询多个表，那么我们查询时就要把多个表进行连接，对连接之后的表进行查询。

1、内连接–inner join

只有两个表中都匹配到的数据才会有查询结果，可以使用where和join on关键字实现，也可以使用比较运算符（包括=、>、<、<>、>=、<=、!> 和!<）进行表间的比较操作，查询与连接条件相匹配的数据。
外连接分为左外连接和右外连接，至少返回一个表的所有行
先写的是左表，后写的是右表
左外连接：对左表不加以限制，返回的是左表的所有行
右外连接：对右表不加以限制，返回的是右表的所有行

select 表名.列,...from 表名1 left [outer] join 表名2 on 表名1.列=表名2.列;
select 表名.列,...from 表名1 right [outer] join 表名2	on 表名1.列=表名2.列;
1
2

七、视图
基于查询结果而生成的一张虚表，是根据需要从已有表中查询出的数据组成的一张虚拟表。

可以对表和表中的数据进行保护
对于重复而复杂的查询，可以把查询的结果集做成视图，便于下次查
对视图也可以像对表一样进行增删改查
1、创建视图

基础语法结构：create view 视图名 as select查询语句;

1、封装实现查询常量语句的视图，即所谓的常量视图

CREATE VIEW view_test1 AS SELECT 3.1415926;

2、封装使用聚合函数(SUN,MIN,MAX,COUNT等)查询语句的视图

CREATE VIEW  view_test2 AS SELECT COUNT(name) FROM student;

3、封装了实现排序功能(ORDER BY)查询语句的视图

CREATE VIEW view_test3 AS SELECT name from t_student ORDER BY id DESC;

4、封装了实现表内连接查询语句的视图

CREATE VIEW view_test4 AS SELECT s.name FROM t_student AS s,t_group AS g

WHERE s.group_id=g.id AND g.id=2;

5、封装了实现表外连接(LEFT JOIN,RIGHT JOIN)查询语句的视图

CREATE VIEW view_test5 AS SELECT s.name 

FROM t_student AS s LEFT JOIN t_group AS g ON s.group_id=g.id

WHERE g.id=2;

6、封装了实现子查询相关查询语句的视图

CREATE VIEW view_test6 
AS SELECT s.name
FROM t_student AS s
WHERE s.gruop_id IN(SELECT id FROM t_group);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

2、查询视图数据

1、查看视图的数据

select *from 视图名;
1

2、查看库中的视图

show tables
1

3、查看视图的结构

des 视图名
1

3、修改视图

1.修改视图(结构): alter view 视图名 as 结果集;
2.添加数据到视图: insert into 视图名 values(值1,值2,...);
3.修改视图中的数据: update 视图名 set 列名=值 where 条件;
1
2
3

4、删除视图

1.delete from 视图名 where 条件; --删除视图中的数据
2.drop view 视图名; ---会删除整个视图,再查询会报错
3.drop view 视图名1,视图名2,...;--删除多个视图
1
2
3

八、索引
索引是对数据库表中一个或多个列的值进行排序的结构，针对查询来说，索引可以加快查询速度。

1、索引的分类

普通索引
唯一性索引
全文索引
单列索引
多列索引
空间索引
2、索引的优点

加快检索速度
保证数据的唯一性
实现表与表之间的参照完整性
在使用group by和order by语句时，会减少分组和排序的时间
3、索引的缺点

创建索引会消耗数据空间和花费一定的时间
查询速度时快了，但是影响了增删改的操作速度
避免对经常更新的表建立过多的索引，保持索引较窄
对小表建立索引可能不会产生优化的效果
建立多个索引可能降低 更新多，数据量大的查询的性能
4、创建索引

创建普通索引:	create index 索引名 on 表名(列名);
创建唯一索引:	create unique index 索引名 on 表名(列名);
1
2

5、删除索引

drop index 索引名 on 表名;
1

九、函数的用法
mysql函数的介绍:有四种函数,分别是字符串函数、数学函数、日期时间函数、控制流函数。

1、字符串处理函数

length：返回字符串的字节长度，注意一个中文占用三个字节
select 列名,length(列名) from student;
char_length：返回字符串的字符长度
select 列名,char_length(列名) from student;
MID：从某个位置获取某个长度的字符(不是字节)
select 列名,MID(列名,2,1) from student;
2、数字函数

round：四舍五入
select round(23.4567,2)–保留小数点后2位,如果不写2,表示不保留小数点
least：获取最小数
select least(1,2,3,4,5,6);
greatest：获取最大数
select greatest(2,8,9,3,4);
3、日期时间函数

获取数据库服务器的当前日期和时间：now()
select now();
获取数据库服务器的当前日期：current_date()
select current_date();
获取数据库服务器的当前时间：current_time()
select current_time();
将日期转换为总天数(从1970年1月1日到当前日期过去了多少天)：to_days()
select to_days(‘xxxx-xx-xx’);
求该年过去了多少天：dayofyear()
select dayofyear(‘xxxx-xx-xx’);
返回当前时日是第几周：week()
select week(‘xxxx-xx-xx’)

4、控制流函数

if语句：有三个参数，第一个参数为空或false或0，就返回第三个参数，否则返回第二个参数。
select if(null,‘数据1’,‘数据2’);
ifnull：有二个参数,第一个参数为空，就返回第二个参数，否则返回第一个参数
select ifnull(null,‘数据1’);–返回数据1
布尔表达式
布尔是一种数据类型,只有2个值,true和false,true表示真,false表示假
布尔表达式中,0表示假,其他表示真,true转为数字就是1,false转为数字就是0

最后感谢每一个认真阅读我文章的人，下面这个网盘链接也是我费了几天时间整理的非常全面的，希望也能帮助到有需要的你！

这些资料，对于想转行做【软件测试】的朋友来说应该是最全面最完整的备战仓库，这个仓库也陪伴我走过了最艰难的路程，希望也能帮助到你！凡事要趁早，特别是技术行业，一定要提升技术功底。希望对大家有所帮助……

如果你不想一个人野蛮生长，找不到系统的资料，问题得不到帮助，坚持几天便放弃的感受的话，可以点击下方小卡片加入我们群，大家可以一起讨论交流，里面会有各种软件测试资料和技术交流。

敲字不易，如果此文章对你有帮助的话，点个赞收个藏来个关注，给作者一个鼓励。也方便你下次能够快速查找。

自学推荐B站视频：

零基础转行软件测试：38天自学完软件测试，拿到了字节的测试岗offer，堪称B站最好的视频！

自动化测试进阶：已上岸华为，涨薪20K，2022最适合自学的python自动化测试教程，自己花16800买的,无偿分享