一、数据库基础概念
1.1 数据库定义
数据库：存储数据的软件，长期存储在计算机内，有组织的数据集合；
表：数据库存储数据的基本单位，数据按照分类存储到不同的表中，能够高效的的查询其中数据；
对于测试工作，如果项目页面没有实现，需要校验数据，则可以通过查询数据库实现；关系：具体商品录入后->产生对应的数据（存到数据库中）->最后悔被加载到项目页面中
1.2数据库分类
1）关系型数据库系统RDMS:(Relational Database Mangagement System)
- 以数据表为核心，将数据的关系用数据库表的形式表达，并将数据存储在表格中，以便查询
- Oracle：大型项目中使用，例如银行、电信等项目
- MsSQL：web项目中使用最广泛的关系型数据库
- Microsoft SQL Server：微软项目中使用
- SQlite：轻量级数据库，主要应用于移动平台
- 关系型数据库核心元素
- 数据行（一条记录）
- 数据列（字段）
- 数据表（数据行的集合）
- 数据库（数据表的集合，一个数据库中能够有n多个数据表
2）非关系型数据库
- 不存在数据表的概念
- 将数据以Key，value、文本、图片等形式存储数据构成
1.3 SQL(结构化查询语言 )
通过SQL语言对数据库进行操作，SQL：Structured Query Lanaguage结构化查询语言；
SQL语言分类：
- DQL：数据查询语言，用于对数据查询，例如：select
- DML：数据操作语音，对数据进行增加、修改、删除、例如：insert、update、delete
- DCL：数据控制语音，进行授权与权限收回，例如，grant，revoke
- DDL：数据定义语言，进行数据库、表的管理等，例如：create、drop
注意：①对于测试工程师来说，重点是数据的查询，因此需要熟练编写DQL；②在MySQL中，默认对SQL语法不区分大小写；③SQL语言默认支持操作所有的关系型数据库；
二、MySQL
关系型数据库管理系统，社区版免费，商用版收费，支持多平台、多语言；
2.1 连接数据库
1）连接工具Navicat，由于数据库软件处于服务器中，想要操作数据库，就必须使用工具远程连接数据库，进行操作
2）需要具备条件
- 数据库所在服务器IP地址及数据库的端口号
- 使用数据库连接工具，远程连接数据库即可；
- 远程连接需要注意网络连通性
2.2 数据类型和约束
- 1）常用数据类型
- 整数，int，有符号范围（-21亿~21亿），无符号（unsigned）范围（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）：能够唯一标志表中每一条记录的属性组
- 非空（notnull）：此字段不允许填写空值
- 唯一（unique）：此字段不允许重复
- 默认值：如果不填写此值会使用默认值，如果填写则以填写为准
- 外键：一个表中的一个字段引用另一个表的主键
三、使用SQL语句实现数据库操作
3.1 数据库的操作
3.1.1 创建数据库
语法格式：
#创建数据库#
create database数据库名[charset][字符编码][collate][校验规则]；
#创建结果查看#
show create database数据库名；
#举例说明：
#创建一个叫python的数据库#
create database python charset=utf8 collate=utf8_general_ci;
#查看创建结果#
show create datdabase python；
3.1.2 使用数据库
语法格式：
#使用（打开）数据库，
use 数据库名；
#查看当前使用的数据库
select databas（）；括号是SQL的内置函数，括号不能省略；
举例说明
#使用（打开）python的数据库：
use python
3.1.3 修改数据库
语法格式：
alter database[数据库名][deflault]character set<字符集名>[default]collate<校对规则名>;
举例说明
#创建testpython数据库，字符集位gb2312#修改testpython的指定字符集改为utf8mb4，默认校对修改规则位utf8mb4_general_ci;
3.1.4 删除数据库
语法格式：
drop database数据库名；
举例说明
#删除python数据库
drop database python；
3.1.5数据库其他操作
查看所有数据库
show database
备份数据库：
说明：测试工作中，为了防止对数据库产生误操作，或产生垃圾数据，都需要在操作前，适当对数据库进行备份操作；
垃圾数据：例如，自动化测试中对注册模块生成的所有数据属于典型的垃圾数据
mysqldump-uroot-p数据库名>python.sql;#按提示输入mysql密码
备份方法：
- 利用工具：转储SQL数据结构文件备份；运用SQL文件进行恢复；
- 使用命令备份，命令不需要连接到数据库以后执行：mysqldump-uroot-p数据库名>python.sql
恢复数据库：mysql-uroot-p数据库名<python.sql#根据提示输入mysql密码
3.2 表的操作
3.2.1创建表
语法格式：
#创建表
create table 表名（字段名 类型 约束，字段名 类型 约束...)
#查看创建表
show create table表名；
举例说明：
#例创建学生表，字段要求如下：姓名（长度为10）
create table students（name varchar（10））
3.2.2查看表
show create table 表名；
或者通过Nativecat查看表结构：
desc 表名；
3.2.3 删除表
语法：
格式一：drop table
格式二：drop table if exists 表名
#例如：删除学生表格
drop table students；
drop table if exists students；
3.2.4 表内操作（增加、修改、删除数据）
增加数据
1.增加一行数据：
insert into 表名 values（...）;
注意：
1）数据值需要和表的字段一一对应（数据个数和数据类型）；
2）主键列是自动增长，插入时需要占位，通常用0或者default或null来占位，插入成功后以实际数据为准。
2.部分字段设置值，值的顺序与给出的字段顺序对应：
insert into表名（字段1...)values(值1...)
例：插入一个学生，只设置姓名
insert into students（name）values（'老夫子‘)
3.添加多行数据
方式一：写多条insert语句，语句之间用英文分号隔开；
方式二：写一条insert语句，设置多条数据，数据之间用英文逗号隔开；
insert into students values(0,'白起',36,1.78),(0,'橘右京',34,1.70)；
修改数据
语法格式：
update表名set列1=值1，列2=值2...where条件
举例说明：修改ID为5的学生数据，姓名改为狄仁杰，年龄改为20；
update students set name='狄仁杰'，age=20 where ID=5；
注意：where条件不能省略，否则会修改整列数据；
删除数据
1.格式一：delete from 表名 where 条件；
例：delete from students where ID=2；
2.逻辑删除
对于重要的数据不能轻易执行delete语句进行删除，一旦删除，数据无法恢复可以进行逻辑删除
逻辑删除就是通过某一特定字段的特定值表示数据是删除或未删除状态；
具体执行步骤：
1）给表添加字段，代表数据是否删除，一般起名isdelete，0代表未删除，1代表删除，默认值为0；
2）当要删除某条数据时，只需要设置这条数据的isdelete字段为1
3）以后在查询数据时，只查询出isdelete为0的数据；
例：
1）给学生添加字段（isdelete）,默认值为0，如果表中已有数据，需要把所有数据的isdelete字段更新为0 update student set isdelete=0
2）删除id为1的学生
update students set isdelete=1 where ID=1
3）查询未删除的数据
select*from students where isdelete=0
3.其他删除方法
truncate：truncate table表名（删除表的所有数据，保留表结构）
例：删除表的所有数据：
truncate table students，
删除后起截断作用，新增数据id会重新计数，即重置主键计数。
delete：delete from表名
删除所有数据，但是不重置主键字段的计数；
drop：drop table表名
直接删除表，不保留表格结构，字段和数据均不存在
四、数据库查询操作
4.1 基础查询--不带条件where
查询所有字段
语法：
select * from 表名
例：查询所有学生的所有字段
select * from students
查询部分字段
语法：
select 字段1,字段2,... from 表名
例：查询所有学生的姓名、性别、年龄
select name,sex,age from students
起别名：
语法：
1.给表起别名：
select 别名.字段1,别名.字段2,... from 表名 as 别名
例：给学生表起别名
select s.name,s.sex,s.age from students as s;
2.给字段起别名
select 字段1 as 别名1,字段2 as 别名2,... from 表名
例：查询所有学生的姓名、性别、年龄，结果中的字段名显示为中文
select name as 姓名,sex as 性别,age as 年龄 from students;
去重：
语法：
select distinct 字段1,... from 表名
例：查询所有学生的性别，不显示重复的数据
select distinct sex from students;
4.2 条件查询：
按照一定条件筛选需要查询的结果，使用where对表中的数据筛选，符合条件的数据会出现在结果中；
语法：select 字段1，字段2...from 表名 where 条件；
条件构成，where后面支持多种运算
比较运算：=，>,<,>=,<=,不等于可以写成！=或者<>
逻辑运算：
与：and，左右两边连接
或：or，左右两边连接
非：not，只对右边连接
例：select *from goods where price =30 and company='拼多多';
模糊查询：like、%表示任意多个任意字符、_表示一个任意字符
例1：查询姓孙的学生select*from students where name like'孙%'
例2：查询姓孙且名字是一个字的学生：select*from students where name like '孙_';
例3：查询姓名以乔结尾的学生：select *from students where name like '%乔'
例4：select*from students where name like'%白%'
范围查询：
1.in 表示在一个非连续的范围内，格式为in（...,...）
例：查询家乡是上海、北京或者广东的学生
select * from students where hometown in（'北京'，'上海'，'广东'）
2.between...and...表示在一个连续的范围内
例：查询年龄为18至20的学生select*from students where age between 18 and 20
空判断：
1.在MySQL中，只有null才为空，其余空白可能是制表符/空格/换行符等
例1：查询没有填写身份证的学生select *from students where card is null
例2:查询填写了身份证的学生select*from students where card is not null
4.3 复杂查询
4.3.1排序
排序：按照一定的规则排序筛选结果
语法：select*from表名order by 列1 asc/desc，列2asc/desc...
默认按照列值从小到大排列；
asc从小到大排列，即升序；默认排序为升序，asc可省略。
desc从大到小排序，即降序
排序过程中支持设置多条排序规则，但离order by关键字越近，排序数据范围越大
4.3.2 聚合函数
对于一组数据进行计算返回单个结果的实现过程
常用的聚合函数/统计函数/多行函数：
count（）：查询总记录数
max（）：查询最大值
min（）：查询最小值
sum（）：求和
avg（）：求平均数
查询总记录数：select count（*）from students
使用聚合函数方便进行数据统计，但是聚合函数不能在where子句中使用
注：有多行函数相对就有单行函数
单行函数：year（提取年份）、month（提取月份）、day（提取日）、replace（替换）、substr（字符串截取）、ifnull（控制转化）
4.3.3 分组
分组：group by，在同一属性（字段）中将值相同的放到同一组的过程；
按照字段分组，此字段中相同的数据会被放到一个组中，分组的目的是对每一组的数据进行统计（使用聚合函数）。
语法：select 字段1，字段2，聚合...from 表名group by字段1，字段2....
例1：select sex，count（*）from students group by sex
一般情况，使用哪个字段进行分组，就只有该字段可以在*的位置使用；
例2：select class，sex，count（*）from students group by class，sex
分组操作多和聚合函数一起使用
拓展：分组后数据筛选
select 字段1，字段2，聚合...from 表名
group by 字段1 ，字段2 ，字段3...
having 字段1，...聚合...;having后面的条件运算符与where的相同
例：查询男生总人数 方案一：select count（*）from students where sex='男'；
例：方案二：select sex，count（*） from students group by sex having sex='男'；
where与having
1.group by和having一般情况下配合使用
2.group by后面不推荐使用where进行条件过滤
3.having关键字后侧允许使用聚合函数
4.having关键字后侧可以使用的内容与where完全一致（比较运算符/逻辑运算符/模糊查询/判断空）
例：查询班级平均年龄大于22岁的班级有哪些select class from students group by class having avg(age)>22;
4.3.4 分页
对大批量数据进行设定数量展示的过程；
select * from表名 limit start，count
从start开始，获取count条数据，limit限制，限度，start起始行，count数据行数
计算机计数从0开始，因此start默认第一条数据为0，如果默认从第一条数据取，则0可以省略。select * from goods limit 5;或者不省略则为select * from goods limit0， 5;
例：查询前三行的学生信息select *from students limit 0,3
分页格式：
limit典型的应用就是实现分页查询
已知每页显示m条数据，求显示第n页的数据
select * from students limit（n-1）*m，m；
例：需求：每页显示7条数据
select * from goods limit 0,7;
select * from goods limit 7,7;
需求：要求查询商品价格最贵的数据信息
select * from goods order by price desc limit 1;
需求：要求查询商品价格最贵的三条数据信息
select * from goods order by price desc limit 3;
4.4 连接查询
将不同的表通过特定的关系连接的过程
4.4.1内连接
内连接语法：
select * from 表1
inner join 表2 on 表1.列=表2.列
select * from students stu inner join scores sc on stu.studentNo=sc.studentNo
显示效果：两张表中有对应关系的都会显示出来，没有对应关系的均不再显示；
扩充：给表起别名1.缩短表名利于编写，2.用别名给表创建附本
扩展：内连接另一种旧式写法select * from 表1，表2 where 表1.字段名=表2.字段名
4.4.2 左连接
查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null
语法：select * from 表1 left join 表2 on表1.列=表2.列
要保证一张表数据全在，不能选择内连接，只能选择左连接或者右连接；
以left join关键字为界，左侧表示为主表，右侧为从表（对应内容显示，不对应为null）
4.4.3 右连接
与左连接方向相反
select*from表1 right join 表2 on表1.列=表2.列
以right join关键字为界，关键字右侧为主表（都显示），关键字左侧为从表（对应内容显示，不对应不显示）
连接查询可以连接无数张表，常见会连接三张表，存在左右连接的必要性
4.4.4 自关联
前提：数据表只有一张，其中两个字段有关联
方式：通过给表起别名，将原本只有一张的数据表变为两张，然后通过对应字段连接查询
4.5 子查询
在一个查询嵌套另一个查询的过程
子查询：在一个select语句中，嵌入了另一个select语句，则嵌入select的语句称之为子查询语句
主查询：外层的select语句称之为主查询语
子查询与主查询的关系：
子查询是嵌入到主查询中的
子查询是辅助主查询的，要么充当条件，要么充当数据源
子查询是可以独立使用的语句，是一条完整的select语句
例：查询价格高于平均价格的商品信息：
①先求平均价select avg(price) from goods;
②作为条件带入：select * from goods where price>(select avg(price) from goods);（运算优先级括号最高）
五、数据库设计扩展
5.1 E-R模型
数据库能够存储现实世界中有意义的数据，通过E-R图更加有效的模拟现实世界；
E-R模型的基本元素是：实体、联系和属性
- E表示entry，实体：描述具有相同特征事务的抽象【数据表】
- 属性：每个实体具有的各种特征成为属性【表内字段】
- R表示relationship，联系：实体之间存在各种关系，包括一对一、一对多和多对多；
5.2 数据库约束
主键约束：表里的列数据唯一且不为空，一个表的主键只能有一个；
外键约束：一表的属性是另一表的主键，可以重复，可以为空；用来和其他表建立联系，一个表可以有多个外键。数据库两张表之间存在关联，数据库会自动维护两表之间的关系；
唯一约束：保证表里的列数据唯一且不为空
非空约束： 保证表里列数据不为空但可重复。
5.3 索引
索引：数据库中用于提高查询效率的一种数据结构，索引由数据库中的一列或者多列组成；
作用：改变底层数据结构，提高查询效率
创建索引语法：
create index index_列 on 表（列）
删除索引语法：
drop index index_列 on 表（列）
查看索引语法：
show index from 表
