一.数据库相关介绍

1.为什么学习数据库？

- 软件中产生的所有数据, 最终都要存储于数据库当中 
- 测试人员如果想要进行数据查询/数据校验, 就必须掌握对数据库的基本操作

2.数据库是什么？

- 定义: 用于存储数据的软件
- 特点: 表是数据存储的核心体现

3.数据库常见分类

- 关系型数据库(重点)
RDMS：(Relational Database Management System)关系型数据库系统
特征: 以数据表的形式存储数据, 便于数据查询
        - 常见的数据库代表
        Oracle：在大型项目中使用，例如：银行、电信等项目
        MySQL：Web 项目中使用最广泛的关系型数据库
        Microsoft SQL Server：在微软的项目中使用
        SQLite：轻量级数据库，主要应用在移动平台
        - 关系型数据库的核心要素
        数据行（一条记录）
        数据列（字段）
        数据表（数据行的集合）
        数据库（数据表的集合，一个数据库中能够有 n 多个数据表）
- 非关系型数据库
不以数据表的形式存储数据的数据库类型

4.数据库的远程连接(重点)

注意: 实际工作中, 数据库是安装在服务器当中的, 如果要直连数据库进行数据校验, 就必须远程连接
                            连接步骤
1. 获取两个信息: 服务器的 IP 地址; 数据库的账号和密码(找相关人
员询问)
2. 在自己电脑上使用数据库连接工具(例如: Navicat), 建立连接, 远
程连接数据库
3. 连接过程中需要确认自己电脑和服务器是否能够正常通信(ping命令)

二.SQL介绍

SQL：Structured Query Language（结构化查询语言），通过SQL语言可以对数据库进行操作
特点: 所有主流的关系型数据库, 都支持使用SQL语句进行数据查询!
注意: 虽然 SQL 语言分支很多, 但对于测试人员而言, 我们重点掌握查询操作即可
    DQL：数据查询语言，用于对数据进行查询，例如：select
扩展: 在 MySQL 中，默认对 SQL 语法不区分大小写

三.MySQL 介绍

1.来源和特点

来源: 目前属于 Oracle 旗下产品, 目前只有社区版免费
特点: 开源, 支持多平台(Linux/Windows/macOS), 支持多语言(Java/C/Python...)
注意: 熟悉 SQL 和熟悉 MySQL 不是一回事儿(熟悉 SQL : 熟悉 SQL语言; 熟悉 MySQL: 用过 MySQL 数据库)
结论 : 由于设置外键会极大降低对数据修改效率, 因此在实际工作中遇到使用外键的几率比较低

2.外键

如果一个实体的某个字段指向另一个实体的主键，就称为外键。
被指向的实体，称之为主实体（主表），也叫父实体（父表）。
负责指向的实体，称之为从实体（从表），也叫子实体（子表）
作用: 对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错

    -- foreign key(自己的字段名) references 目标表名(目标表的主键)
    foreign key(class_id) references class(id);
    -- 扩展1 : 对于已经存在的表添加外键
    -- alter table 从表名 add foreign key (从表字段) references 主表名(主表主键);
    alter table stu add foreign key (class_id) references class(id);
    -- 扩展2 : 查看外键和删除外键
    -- 查看外键
    -- show create table 表名
    show create table stu;
    -- CREATE TABLE `stu` (
    --   `name` varchar(10) DEFAULT NULL,
    --   `class_id` int(10) unsigned DEFAULT NULL,
    --   KEY `class_id` (`class_id`),
    --   CONSTRAINT `stu_ibfk_1` FOREIGN KEY
    (`class_id`) REFERENCES `class` (`id`)
    -- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -- 删除外键
    -- alter table stu drop foreign key 外键名称
    alter table stu drop foreign key stu_ibfk_1;

3.索引

定义: 类似于图书中的目录, 能够起到快速检索数据的作用
作用: 对于大量数据进行查询效率优化时, 可以采取添加索引的策略
结论 : 索引在使用的时候能提高查询效率, 但是在进行插入/更新/删除时, 索引会造成障碍, 因此需要在执行这些操作前, 先移除索引, 操作结束后, 重新添加索引即可    
```
-- 索引应用检验案例
-- 开启时间监测
set profiling=1;
-- 查询示例数据 num = 10000 的值
select * from test_index where num = 10000;
-- 查看运行时间
show profiles;
-- 添加索引
-- create index 索引名称 on 表名(目标字段)
create index num_index on test_index(num);
-- 再次执行查询数据操作
select * from test_index where num = 10000;
-- 再次查看运行时间
show profiles;
```

4.命令行中操作MYSQL

    登录数据库：mysql -u数据库用户名 -p数据库密码
    例如:mysql -uroot -p123456
    查看所有数据库 : show databases;
    使用数据库 : use 数据库名称;
    查看当前使用的数据库 : select database();
    创建数据库 : create database 数据库名称 charset=utf8;
    删除数据库 : drop database 数据库名称;

  数据表操作：
  查看数据库中的所有数据表 : show tables;
  查看表结构 : desc 表名;
  查看创表语句 : show create table 表名;
  注意 : 进入到数据库之后, 所有的 SQL 查询语句, 均可以正常使用!

修改 MySQL 数据库密码
                        - 查找配置文件并修改
    切换 root 用户
    su -
    定位配置文件位置
    locate my.cnf
    使用 vi 工具打开配置文件
    vi /etc/my.cnf
    在文件内容 [mysqld] 下方添加此内容, 保存后退出
    skip-grant-tables
                        - 重新启动 MySQL 服务
    systemctl restart mysqld
    重启完成可以通过查看状态命令进行验证
    systemctl status mysqld
                    - 登录数据库, 修改数据库账户密码
    注意 : 本步骤为 SQL 语句, 需要在 mysql > 状态下执行
    use mysql; -- 选择 mysql 数据库
    -- 更新密码
    -- 注意 : authentication_string 字段名需要根据 MySQL 版本就行对应修改
    -- update user set
    authentication_string=password('新密码') where user = '用户名';
    update user set
    authentication_string=password('123') where user = 'root';
    -- 刷新权限
    flush privileges;
                       - 还原配置文件设置, 使之登录需要密码
    还原配置文件设置与添加设置步骤基本相同
    切换 root 用户
    su -
    定位配置文件位置
    locate my.cnf
    使用 vi 工具打开配置文件
    vi /etc/my.cnf
    使用 # 注释 [mysqld] 下方内容, 保存后退出
    # skip-grant-tables

5.存储过程

定义 : 可以叫存储程序, 通过一定的代码逻辑, 将一句或多久SQL语句进行封装, 通过调用存储过程, 快速实现其内部封装SQL语句操作
用途 : 可以用来向数据库中, 快速插入大量测试数据时使用

```
基本语法格式：
delimiter // -- 取消默认结尾标识符 ; 的作用
create procedure 存储过程名(参数列表)
begin -- 代码逻辑的开始
SQL语句
end //
delimiter ; -- 还原默认结尾标识符 ; 的作用
```

6.事务

定义 : 所谓事务可以称之一个操作序列, 一系列操作要么都执行,要么就不执行. 对于数据库来讲, 对于数据的操作行为, 要么都实现, 要么都不实现, 最终需要确保写入到数据库的数据的一致性(原子性)!
事务实现案例前提: 数据表的数据引擎类型必须是 InnoDB (可以通过查看创表语句来确认) 
```
-- 注意 : 出发事务操作一般是由修改数据操作产生(插入数据insert/更新数据update/删除数据delete)
-- 开启事务
begin;
-- 提交事务
commit;
-- 回滚事务
rollback;
```

7.视图

场景 : 能够封装 SQL 语句, 以类似于表的形式存在
-- 创建视图语法
-- 注意: 视图命名一般以 v_视图名称 形式实现
-- create view 视图名称 as select 语句;

8.MySQL 日志的获取

注意 : MySQL 自带日志功能, 但是开启日志功能, 极其消耗数据库性能, 因此默认情况下是不开启的   
    ```
    -- 查看日志功能是否开启
    show variables like 'general%';
    -- 开启操作
    set global general_log = 1;
    -- 关闭操作
    set global general_log = 0;
    -- 注意: 日志功能使用完成, 需要记得马上关闭, 避免影响数据库性能!!!!!!
    ```

四.数据类型与约束(重点)

作用: 为了更加准确存储数据, 保证数据的有效性, 需要结合数据类型和约束来限制数据的存储

1.数据类型

整数：int，有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295）
小数：decimal，例如：decimal(5,2) 表示共存5位数，小数占2位，整数占3位
字符串：varchar，范围（0~65533），例如：varchar(3) 表示最多存3个字符，一个中文或一个字母都占一个字符
日期时间：datetime，范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），例如：'2020-01-01 12:29:59'

2.约束

主键（primary key）：物理上存储的顺序
非空（not null）：此字段不允许填写空值
惟一（unique）：此字段的值不允许重复
默认值（default）：当不填写此值时会使用默认值，如果填写时以填写为准
外键（foreign key）：维护两个表之间的关联关系(现阶段先不讲解, 后边再进行扩展)

五.数据表

1.创表语句

```
需求1: 创建商品表, 字段包括 id(主键), 商品名称, 价格, 数量, 公司,备注, 并添加商品
drop table if exists goods;
create table goods(
-- unsigned : 无符号
-- primary key : 主键(不为空)
-- auto_increment : 自动递增
-- 直接替换字段名使用皆可数据
id int unsigned primary key auto_increment,
goodsName varchar(20),
price decimal(6,2),
num int,
company varchar(20),
remark varchar(30)
);
```

2.增加数据(插入数据)

```
-- 需求1 添加商品
-- 查询一次(方便观察表中字段)
select * from goods;
-- 添加1条数据
-- 主键列是自动增长，插入时需要占位，通常使用0或者 default或者 null 来占位，插入成功后以实际数据为准
-- 语法 insert into 表名 values(...)
insert into goods values
(0, '战神笔记本', 6000.00, 100, '某东', '战神在手, 天下我有!');
-- 注意: 插入的数据个数与字段数必须匹配, 数据类型也需要对应
-- 插入多条数据
insert into goods values
(0, '小新笔记本', 5000.00, 100, '某东', '小新小新, 蜡笔小新!'),
(0, '外星人笔记本', 9999.00, 100, '某宝', '外星人上位, 战神渣渣!');
-- 注意: 插入多条数据, 每条数据使用逗点分隔即可
-- 扩展: 插入指定字段数据(了解)
-- insert into 表名(字段名) values(字段对应值)
insert into goods(goodsName) values('惠普游侠5');
```

3.修改数据

```
需求2: 修改商品数据1条
-- 需求2: 修改商品数据1条, 删除1条数据
-- 修改
-- 先查询所有数据, 便于观察数据内容
select * from goods;
-- 补充惠普电脑的数据
update goods set price=4500.00,num=50,company='并夕夕' where id=4;
-- 注意: 修改数据时, 为保证修改准确性, 务必要给出限定条件(where)
update goods set price=4500.00,num=50,company='并夕夕';
```

4.删除数据

```
-- 删除一条数据
-- 先查询所有数据
select * from goods;
-- 语法格式 : delete from 表名 where 条件
-- 注意: 如果要删除执行数据, 务必给出限定条件, 否则会删除所有数据!
delete from goods where id = 4;
        -- 扩展2: 3种删除数据方法(所有数据)
-- delete from 表名 : 清空表数据(只清空数据,保留结构), 但不会重置主键计数
-- truncate table 表名 : 清空表数据(只清空数据,保留结构),并会重置主键计数(截断表)
-- drop table 表名 : 删除表, 包括表结构和数据
delete from goods;
truncate table goods;
drop table goods;
-- 删除速度 : drop > truncate > delete
```

5.查询数据

I.基本查询

```
需求3 : 查询所有/查询指定字段/起别名/去重
-- 需求3: 准备商品数据, 查询所有数据, 查询部分字段, 起字段别名, 去重
-- 准备数据
-- 查询所有数据 : select * from 表名;
select * from goods;
-- 查询部分字段
-- 只需要在查询所有数据的基础上, 修改*的位置, 多个字段逗点隔开
select goodsName,price from goods;
      -- 起别名 : 使用 as 关键字, 后跟别名即可
select goodsName as '商品名称',price as '价格' from goods;
select goodsName '商品名称',price '价格' from goods; 
select goodsName 商品名称,price 价格 from goods; -- 中文别名引号可以省略
-- 去重 : 根据所给字段, 将字段中数据相同数据合并保留一个
-- 需求 : 当前表当中一共有几家公司
select distinct(company) from goods;
```

II.条件查询

```
需求4 : 比较运算符/逻辑运算符
-- 需求4: 查询价格等于30并且出自并夕夕的所有商品信息
-- 条件查询 : where 条件
select * from goods;
select * from goods where price = 30; -- 比较运算符
select * from goods where price = 30 and company ='并夕夕'; -- 逻辑运算符 and : 并列条件
-- 注意 : 在 where 关键后侧的字符串数据, 必须给引号, 否则会报错
-- select * from goods where price = 30 and company= 并夕夕;
```

III.模糊查询

```
 需求5 : 模糊查询
    -- 需求5: 查询全部一次性口罩的商品信息
-- 查询所有数据
select * from goods;
-- 模糊查询 : like (字符: % 匹配任意的多个字符)
-- 语句格式 : where 字段 like '%信息'
select * from goods where remark like '%一次性口罩';
-- 以一次性口罩结尾, 前面内容不限制
-- 由于目标信息可能出现在中间部分
-- 注意 : 模糊查询时要注意条件设定, 避免遗漏数据
select * from goods where remark like '%一次性口罩%';
-- 提取内容中包含目标信息的数据
-- 扩展: 查询公司来源是 x宝 的商品信息内容
-- like ( _ : 匹配任意单个字符)
select * from goods where company like '_宝';
```

VI.范围查询

```
-- 需求6: 查询所有价格在30-100的商品信息
-- 查询所有数据
select * from goods;
-- 条件查询-范围查询 : between 起始值 and 结束值 : 表示一个连续的范围
select * from goods where price between 30 and 100;
-- 注意: 范围应该从小到大
-- select * from goods where price between 100 and 30; -- 查询不出数据
-- 扩展: 以下数据出自于 某东 和 x宝 商品信息
-- 条件查询-范围查询 : in (条件1, 条件2, ...): 表示在某个不连续范围内
select * from goods where company in ('某东', 'x宝');
```

V.空判断

```
-- 需求7: 查询没有描述信息的商品信息
-- 查询所有数据
select * from goods;
-- 判断空 : is null
-- 注意 : null 与 '' 不是一码事; null : 空; '': 空字符
(例如:空格/制表符(tab))
select * from goods where remark is null;
-- 扩展 : 查询以下数据中描述信息不为空的所有商品数据
select * from goods where remark is not null;  --not : 表示对条件取反; not null : 双重否定表示肯定
```

VI.排序

```
-- 需求8: 查询所有商品信息, 按照价格从大到小排序, 价格相同时, 按照数量少到多排序
-- 查询所有数据
select * from goods;
-- 排序 : order by 字段名 asc(升序)/desc(降序)
select * from goods order by price desc;
-- 需求实现
select * from goods order by price desc, count asc;
-- 扩展:
select * from goods order by price; -- 默认情况下
order by 是以升序进行排列
select * from goods order by price desc, count; --因此 asc 可以省略
```

VII.聚合函数

```
-- 需求9: 查询以下信息: 商品信息总条数; 最高商品价格; 最低商品价格; 商品平均价格; 一次性口罩的总数量
-- 查询所有数据
select * from goods;
-- 聚合函数 : SQL 提前准备还一些方法, 具备一定的作用
-- 数据总数 : count()
select count(*) from goods;
select count(remark) from goods; -- 注意: 统计数据总数时, 推荐使用 * (按照数据最多的那一列统计总数)
-- 最大值 : max()
select max(price) from goods;
-- 最小值 : min()
select min(price) from goods;
-- 平均值 : avg()
select avg(price) from goods;
-- 求和 : sum()
select * from goods where remark like '%一次性口罩%';
-- 先获取所有一次口罩的数据
select sum(count) from goods where remark like '%一次性口罩%'; -- 需求实现
```

VIII.分组

```
-- 需求10: 查询每家公司的商品信息数量
-- 查询所有数据
select * from goods;
-- 分组 : group by 字段 : 可以根据给出的字段数据进行数据分组
-- 注意 : 一般情况下, 分组要配合聚合函数一起使用, 目的是对分组后的数据进行进一步统计
select company 公司名称,count(*) from goods group by company;
-- 分组后再次进行条件筛选
-- 扩展 : 查询某东和x宝最贵商品的价格
-- 先按照公司分组
select company 公司名称 from goods group by company;
-- 注意 : 如果想在分组后再次进行条件筛选, 可以使用 having关键字
-- 把并夕夕的数据排除掉
select company 公司名称 from goods group by company having company != '并夕夕';
-- 获取公司商品最贵价格
select company 公司名称, max(price) from goods group by company having company != '并夕夕';
```

IX.分页查询

```
-- 需求11: 查询当前表当中第5-10行的所有数据
-- 查询所有数据
select * from goods;
-- 分页查询 : limit 起始索引, 数据行数
-- 索引 : 在计算机当中, 不同于人类的自然计数从 1 开始, 计算机的计数起始值是 0 开始(索引)
-- 起始索引: 实际数据顺序 - 1 即为其对应的索引值
select * from goods limit 4, 6;
-- 扩展 : 如果获取是当前数据中的第1行数据时, 起始索引可以省略
-- 获取当前数据中的第1行数据
-- select * from goods limit 0, 1;
select * from goods limit 1;
-- 分页查询公式的使用
-- 扩展 : 分页查询公式的应用
-- 条件 : 每页显示 m 条数据，求：显示第 n 页的数据
-- 公式 : (当前页-1)*每页显示的条数, 每页显示的条数 : 查看指定页面数据
-- 假设 : 当前数据中, 每页显示 3 条数据, 查看第 2 页数据内容
select * from goods limit 3, 3;
```

6.连接查询

I.内连接

```
-- 需求1: 查询所有存在商品分类的商品信息
-- 查询商品数据
select * from goods;
select * from category;
-- 在查询中, 需要将两张表存在对应关系的数据全部显示出来时, 需要使用连接查询-内连接
-- 内连接 : 显示两张表中存在对应关系的数据, 无对应关系的数据不显示
-- 语法 : 表1 inner join 表2 on 表1.字段 = 表2.字段
select * from goods inner join category on goods.typeId = category.typeId;
```

II.左连接

```
-- 需求2: 查询所有商品信息,包含商品分类
-- 查询所有商品
select * from goods;
select * from category;
-- 左连接 : 以 left join 关键为界, 关键字左侧的表的信息要全部显示出来, 而关键字右侧表的信息, 有对应的部分显示, 无对应的部分以 null 占位填充即可
-- 语法 : 表1 left join 表2 on 表1.字段 = 表2.字段
select * from goods go left join category ca on
go.typeId = ca.typeId;
-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 左连接
select * from category ca left join goods go on
ca.typeId = go.typeId;
```

III.右连接

```
-- 需求2: 查询所有商品信息,包含商品分类
-- 查询所有商品
select * from goods;
select * from category;
-- 注意 : 右连接主要解决 3 张及以上表进行连接查询时常见
-- 右连接 : 以 right join 关键为界, 关键字右侧的表的信息要全部显示出来, 而关键字左侧表的信息, 有对应的部分显示, 无对应的部分以 null 占位填充即可
-- 语法 : 表1 right join 表2 on 表1.字段 = 表2.字段
select * from category ca right join goods go on
ca.typeId = go.typeId;
-- 需求3: 查询所有商品分类及其对应的商品的信
-- 右连接
select * from goods go right join category ca on
go.typeId = ca.typeId;
```

VI.自关联

```
特征 : 只有 1 张表, 表中最少存在两列字段具备对应关系;
原理 : 通过起别名的方式, 将 1 张表变为 2 张表, 通过对应字段的对应关系, 进行条件比对, 实现连接查询
        -- 需求4: 查询河南省所有的市
        -- 查询所有数据
        select * from areas;
        -- 自关联 :
        -- 1) 通过起别名的方式, 将一表变俩表
        -- 2) 通过 表1.字段 = 表2.字段 实现表间关联
        select * from areas a1
        inner join areas a2 on a1.aid = a2.pid
        where a1.atitle = '河南省';
        -- 需求5: 查询河南省的所有的市和区
        -- 自关联
        select * from areas;
        --
        select * from areas a1
        inner join areas a2 on a1.aid = a2.pid
        left join areas a3 on a2.aid = a3.pid
        -- inner join areas a3 on a2.aid = a3.pid
        where a1.atitle = '河南省';
```

V.子查询

定义 : 在一条查询语句当中, 利用另一条语句作为条件或数据源, 充当条件或数据源的语句称之为子查询语句. 注意 : 子查询语句在使用时, 务必使用括号括起来

i. 需求6 : 子查询-充当条件

```
-- 需求6: 查询价格高于平均价的商品信息
-- 查询所有数据
select * from goods;
-- 查询平均价格
select avg(price) from goods; -- 68.33
-- 需求实现
select * from goods where price > 68.33;
-- 优化实现 - 子查询 : 在 where 关键字后边的子查询语句是充当条件的
select * from goods where price > (select avg(price)
from goods);
```

ii.需求7 : 子查询-充当数据源

```
-- 需求7: 查询所有来自并夕夕的商品信息, 包含商品分类
-- 查询所有数据
select * from goods;
select * from category;
-- 并夕夕商品信息
select * from goods where company = '并夕夕';
-- 需求实现 - 子查询 : 在 from 和连接查询关键字后边的子查询语句时充当数据源的
select * from category ca
inner join (select * from goods where company = '并夕夕') a on ca.typeId = a.typeId;
```

iii.子查询的分类

根据子查询语句返回的结果形式体现, 子查询共分为 4 类:
子查询返回的结果是一个值(一行一列), 称之为: 标量子查询
子查询返回的结果是一列数据(一列多行), 称之为: 列子查询
子查询返回的结果是一行数据(一行多列), 称之为: 行子查询
子查询返回的结果是多行多列(相当于一张表), 称之为: 表级子查询

vi.子查询关键字

```
-- 需求 : 查询在25-100之间的商品的价格
select price from goods where price between 25 and 100;
-- in : 范围
-- select * from goods where price in (25, 30, 77, 30, 72,25);
select * from goods where price in (select price
from goods where price between 25 and 100);
-- some/any : 任意一个
select * from goods where price = some(select price
from goods where price between 25 and 100);
select * from goods where price = any(select price
from goods where price between 25 and 100);
-- all : 全部(= all : 等于所有; != 或 <> all : 不等于所有)
select * from goods where price = all(select price
from goods where price between 25 and 100);
select * from goods where price != all(select price
from goods where price between 25 and 100);
select * from goods where price <> all(select price
from goods where price between 25 and 100);
```