一、数据库是啥

存数据的地方。

MySQL（增、删、改、查），除了查询动作需要重点掌握以外，其他操作了解即可。

对于测试工作而言，如果项目页面没有实现，我们想要校验数据，则可以直接通过查询数据库实现。

关系：具体存在的商品录入后——》产生对应的数据（存到数据库中）——》最后会被加载到项目页面中

二、数据库分类

关系型数据库（RDMS）

用表来存储

代表产品：

Oracle:在大型项目中使用，例如：银行、电信等项目
MySQL:Web项目中使用最广泛的关系型数据库
Microsoft SQL Server：在微软项目中使用
SQLite:轻量级数据库，主要应用在移动平台

核心元素：

数据行（一条记录）
数据列（字段）
数据表（数据行的集合）
数据库（数据表的集合，一个数据库中能够有n多个数据表）

非关系型数据库

不存在表的概念，将数据以key-value、文本、图片等形式存储的数据构成。

代表产品：

Redis
MongoDB

三、SQL介绍

定义：Structured Query Language（结构化查询语言），对关系型数据库进行操作，主流的关系型数据库都支持SQL 语言进行操作。

分类：

DQL:数据查询语言，对数据进行查询，例如：select
DML:数据操作语言，对数据进行增、删、修改，例如：insert、update、delete
DCL:数据控制语言，进行授权和权限回收，例如：grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，例如：create、drop

注意：编写SQL语句，不区分字母的大小写

四、MySQL介绍

特点：开源、社区版免费、支持多平台（mac、windows、linux）、多语言（python、Java等）、使用范围广泛，是学习数据库的首选。

数据库连接工具Navicat Premium（或者SQLyog工具，这是免费的）。由于数据库软件处于服务器中，想要操作数据库，就必须使用工具远程连接数据库后，进行操作。

连接数据库操作步骤（说明）：

① 数据库所在服务器的IP地址以及数据库的端口号

② 向相关人员获取数据库的账号和密码

③ 使用数据库连接工具，远程连接数据库即可

注意：远程连接需要注意网络连通性

要点左下角进行测试连接。连接数据——》操作数据库、表、数据等实现。

示例：

五、数据库基础

1、数据类型和约束

可进行的操作：

1、设置数据类型：

2、设置主键：

3、设置唯一：

先在字段中设置好car，再选中索引，点击字段，添加字段中的car，点击索引类型为UNIQUE

4、设置默认值：

2、通过SQL语句实现一系列操作

Ⅰ、对数据库的操作

1. 创建数据库相关操作：

创建数据库 :

create database 数据库名 [charset] [字符编码] [collate] [校验规则]；

当没有设置charset和collate时，默认是utf8和utf8_general_ci(不区分大小写，会影响操作语句的结果)。而设置collate为utf8_bin时会区分大小写，属于是精准匹配。

创建结果查看:

show create database 数据库名；

如果查询窗口有多条语句，需要先选中再执行:

示例:创建一个叫python的数据库：

create database python charset=utf8 collate=utf8_general_ci;

示例:查看创建结果：

show create database python；

2. 使用数据库相关操作：

使用（打开）数据库

use 数据库名;

查看当前使用的数据库

select database(); ——（）是SQL的内置函数，不能省略

示例：使用（打开）python数据库：use python；

3. 修改数据库相关操作：

修改数据库

alter database [数据库名] [default] character set <字符集名> [default] collate <校对规则名>;

删除数据库（慎用！！）

drop database 数据库名；

示例：创建testpython数据库，字符集为gb2312:

create database testpython charset=gb2312

修改testpython的指定字符集为utf8mb4,默认校对规则修改为utf8mb4_general_ci:

alter database testpython default charset=utf8mb4 default collate utf8mb4_general_ci;

示例：删除python数据库

drop database python;

4. 查看所有数据库

show databases;

5. 重点：数据库备份

在测试工作中，为了防止对数据库产生错误操作，或产生垃圾数据，都需要在操作前，适当对数据进行备份操作。

垃圾数据：例如在自动化测试中，对注册模块操作生成的所有数据，属于典型的垃圾数据，应该清理。

备份方法：

①、利用工具

通过备份文件还原：右键数据库，选择运行SQL文件

②、使用命令备份（命令是不需要连接到数据库以后执行的！（非mysql>模式））

在终端备份数据库：备份的文件，就是对应的sql语句。

mysqldump -u 用户名 -p -B 数据库名1 数据库名2 > d:\test.sql；（#再按提示输入mysql密码）

恢复数据库：

方法：source d:\test.sql。（注意：进入MySQL命令行再执行）

备份库的表：

mysqldump -u 用户名 -p密码数据库名表1 表n > d:\文件名.sql

恢复表：

方法：（先进入数据库：mysql -u root -p密码）use 数据库名；

再恢复：source d:\test.sql。（注意：进入MySQL命令行再执行）

Ⅱ、对数据表的操作

1. 创建表

create table 表名（字段名类型约束，字段名类型约束....）

示例：

扩展一：判断表是否存在，存在时先删除再创建

drop table if exists students;

扩展二：通过Navicat获取创表语句

2. 查看创建的表（结果跟创建表一样）

show create table 表名；

3. 修改表结构（字段）

desc 表名；

示例：

4. 删除表

drop table 表名；

示例：drop table students；

Ⅲ、对数据的操作（增、删、改、查）

1. 增加数据

添加一行数据：

格式一：所有字段设置值，值的顺序与表中字段的顺序对应。

insert into 表名 values（...）

说明：主键列是自动增长，插入时需要占位，常使用0或default或null。

示例：插入一个学生，设置所有字段的信息：

insert into students values(0,'雅舍'，22，177.56)

格式二：部分字段设置值，值的顺序与给出的字段顺序对应。

insert into 表名（字段1，....）values（值1，....）

示例：插入一个学生，设置name为张山：

insert into students（name）values（'张山'）

添加多行数据:

方式一：将单行插入语句，多句执行，每句分号隔开；

方式二：写一条insert语句，设置多条数据，数据之间用用英文逗号隔开

insert into 表名 values（...）,(...)...

示例：插入多个学生，设置所有字段信息：

insert into students values(0,'李四'，20，155.23)，（0，'王五',23,168.23）

insert into 表名（列1，...） values(值1，...),(值1，...)...

示例：插入多个学生，只设置姓名:

insert into students(name) values('老夫子')，（'孔子'）

2. 修改数据

update 表名 set 列1=值1，列2=值2... where 条件

示例：update students set age=20 where id=9

注意：where不能省略，否则会修改整列数据

3. 删除数据

delete from 表名 where 条件；

示例：delete from students where id=6；

扩展1：逻辑删除（假删除）：通过某一特定字段isdelete的特定值表示数据是删除（1）或未删除状态（0）。

①、修改要删除的数据的特定字段为删除状态:

update students set isdelete=1 where id=4；

②、查询所有isdelete字段为0的所有数据：

select * from students where isdelete=0；

扩展2：其他数据删除方法

方法1：truncate table 表名

（删除表的所有数据，保留表结构，重置主键字段的计数）

示例：删除学生表的所有数据：truncate table students。

方法2：drop table 表名

（删除表，所有数据和表结构都删除）

示例：drop table students；

4. 查询数据

一、基本查询（不带条件where）：对列

查询所有数据:

select * from 表名；

查询部分字段:

select n个字段名 from 表名；

给字段起别名:

select prince（字段名） as ‘价格’ from goods；（注意：as可以省略，‘’也可以省略）

别名作用：可以美化数据显示效果，还可以起到隐藏真正字段名的作用。

另外，除了可以给字段取别名，还可以给数据表取别名（连接查询时使用）

去重:

select distinct（字段1，...） from 表名；

示例：显示所有公司名称：

select distinct(company) from goods；

二、复杂查询

在基础查询基础上，根据需求描述关系进行查询，实际应用中，往往是多种复合查询的组合使用。

一、条件查询（where）：按照一定条件筛选需要的结果

1. 比较查询

=、>、>=、<、<=、！=、<>

示例一：查询价格等于30并且出自拼多多的所有商品信息（比较查询）：

select * from goods where price=30 and company="拼多多"；

2. 逻辑查询

and 、or、 not

示例二：查询价格等于30并且不出自拼多多的所有商品信息（逻辑查询）：

select * from goods where price=30 and not company='拼多多'；

select * from goods where not company='拼多多' and price=30；

注意：and 左右两边连接条件。 not 只对右边的连接条件

3. 模糊查询

like '内容'

内容：%表示任意多个任意字符、_表示一个任意字符

示例三：查询全部一次性口罩的商品信息：（模糊查询）

select * from goods where remark like '%一次性口罩%'；

注意：%关键词%：关键词在中间、关键词%：关键词在开头、%关键词：关键词在末尾。如果需要控制字符数量，需要使用_,有几个字符就使用几个_

4. 范围查询

1、连续的范围：between ... and ... ; 2、非连续的：in（...，...）

示例四：查询所有价格在30-1元0间的商品信息:

select * from goods where price between 30 and 100；

示例四：查询家在北京或四川的学生:

select * from students where homeTown in（'北京'，'四川'）

5. 空判断

null / not null

示例五：查询没有描述信息的商品信息（有描述信息的即取反 not null）

select * from goods where remark is null；

示例五：查询填写了身份证信息的学生

select * from students where identity is not null

二、排序：按照一定的排序规则筛选所需结果

select * from 表名 order by 列1 asc | desc，列2 asc| desc

示例：查询所有商品信息，按照价格从大到小排序，价格相同时，按照数量少到多排序

select * from goods order by price desc，count asc；

注意：asc：升序默认，可省略，desc：降序，排序过程中，支持连续设置多条排序规则，但离order by 关键字越近，排序数据的范围越大。

三、聚合函数：对一组数据进行计算得到一个结果的实现方法

系统提供的一些直接用来获取统计数据的函数。算完是个数值！！！

注意：！聚合函数不能在where子句中使用。

常用的聚合函数：

1. count():查询总记录数

查询商品信息总条数：count（字段）。注意：统计数据总条数，建议使用*,如果使用某一字段，可能造成数据总数错误

示例：select count(*) from goods;

2. max():查询最大值

查询最高商品价格：max(字段)：查询最大值

示例：select max(price) from goods;

3. min():查询最小值

查询最低商品价格：min(字段)：查询最小值

示例：select min(price) from goods;

4. avg():求平均值

查询商品平均价格：avg(字段)：查询平均价格

示例：select avg(price) from goods;

5. sum():求和

一次性口罩的总数量：sum(字段):求和

示例：select sum(count) from goods where remark like '%一次性%'；

扩展：在需求运行的情况下，可以一次性在一条SQL语句中，使用所有的聚合函数

select count(*) ,max(price),min(price),avg(price) from goods;

四、分组：在同一属性（字段）中，将值相同的放到一组的过程。

目的是对每一组的数据进行统计（使用聚合函数）

语法格式：

select 字段1，字段2，聚合... from 表名 group by 字段1，字段2

注意：

一般情况下，使用哪个字段进行分组，那么只有该字段可以在*的位置处使用。（其它字段没有实际意义，只要一组数据中的一条）
分组操作多和聚合函数配合使用

示例：查询每家公司的商品信息数量

select count(*) from goods group by company;

select company,count(*) from goods group by company;(对应注意1，其它字段没有实际意义，只要一组数据中的一条)

扩展：分组后条件过滤

语法格式：select 字段1，字段2，聚合... from 表名 group by 字段1，字段2... having 字段1...，聚合...

注意：where和having区别:

① where是对from后面指定的表进行数据筛选。

② having是对group by 的结果进行筛选！！！

③ having后面的条件中可以使用聚合函数，而where不可以

④ having关键字后面可以使用的内容和 where 完全一致（比较、逻辑、模糊查询、范围查询、判断空）

示例：查询男生总数

方法1：使用where，从整个表中进行数据筛选

select count(*) from students where sex ='男';

方法2：先使用分组group by 再使用having

select sex,count(*) from student group by sex having sex = '男'；

五、分页：对大批量数据进行设定数量展示的过程

当数据量过大时，在一页中查看数据十分麻烦。

语法：

slect * from 表名 limit start，count

说明：limit 分页；start：起始行号，count：数据行数

注意：计算机的计数从0开始，因此，start默认的第一条数据应该是0，后续数据一次减1

示例:

获取前5条数据：select * from goods limit 0,5;

注意：如果默认从第一行开始，则0可以省略。

select * from goods limit 5;

查询表中第5-10行的所有数据:

select * from goods limit 4,6;

扩展1：分页格式：limit典型的应用场景就是实现分页查询（根据公式计算显示某页的数据）

示例：每页显示m条数据，求：显示第n页的数据

select * from students limit (n-1) * m,m;

扩展2：分页的应用:要求查询商品价格最贵的数据信息（聚合函数出来的只是一个数，所以不能使用max）

select * from goods order by price desc limit 0，1；

实际项目中数据库的数据表形式说明:

六、连接查询：将不同的表通过特定关系连接的过程

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回。

1. 内连接

查询的结果为两个表匹配到的数据。(有对应关系的数据都会显示出来，没有对应关系的数据均不显示)

语法格式：select * from 表1 inner join 表2 on 表1.列 = 表2.列

示例：查询所有存在商品分类的商品信息。（现在只有两张表，两张表中都有商品分类typeId）

select * from goods inner join category on goods.typeId =category.typeId;

扩充：给表起别名（1、缩短表名利于编写 2、用别名给表创建副本）

select * from goods go inner join category ca on go.typeId =ca.typeId;

扩展：内连接的另一种写法（旧写法）

select * from goods,category where goods.typeId = category.typeId;

2. 左连接

查询的结果为两个表匹配到的数据加左表特有的数据，对右表中不存在的数据使用null

语法格式：select * from 表1 left join 表2 on 表1.列 = 表2.列

说明：如果要保证一张数据表的全部数据都存在，则一定不能选择内连接。以 left join 关键字为界，关键字左侧表为主表（都显示），而关键字右侧的表为从表（对应内容显示，不对应为null）

示例：查询所有商品信息，包含商品分类

select * from goods go left join category ca on go.typeId = ca.typeId;

扩展：以分类展示所有内容

select * from category ca left join goods go on go.typeId = ca.typeId;

3. 右连接

查询的结果为两个表匹配到的数据加右表特有的数据，对左表中不存在的数据使用null

语法格式：select * from 表1 right join 表2 on 表1.列 = 表2.列

说明：以 right join 关键字为界，关键字右侧表为主表（都显示），而关键字左侧的表为从表（对应内容显示，不对应为null）

示例：查询所有商品分类以及其对应的商品信息

select * from goods go right join category ca on go.typeId = ca.typeId;

扩充需求：查询所有商品信息及其对应的分类信息

select * from category ca right join goods go on go.typeId = ca.typeId;

补充：存在左右连接的必要性

说明：能够体现左右连接必要性的场景为：至少为三张表进行连接查询

注意：实际工作中，最多也就三张表连接查询

七、自关联：将同一表通过特定关系连接的过程

前提：

数据表只有一张。
数据表中至少有两个字段之间有某种联系。

方式：通过给表起别名的形式，将原本只有一张的数据表变为两张，然后通过对应字段实现连接查询。

示例一：查询河南省所有的市

使用左连接

select * from areas a1 left join areas a2 on a1.aid = a2.pid where a1.name='河南省';

使用内连接

select * from areas a1 inner join areas a2 on a1.aid = a2.pid where a1.name='河南省';

示例二：查询河南省所有的市和区

说明：想要实现三级行政单位显示，需要分别处理省和市，市和区（三表联查）

select * from areas a1 left join areas a2 on a1.aid = a2.pid left join areas a3 on a2.aid = a3.pid where a1.name='河南省'

八、子查询：在一个查询套入另一个查询的过程

在一个select语句中，嵌入另一个select语句，那么嵌入的select语句被称为子查询语句

作用：子查询是辅助主查询的，要么充当【条件】，要么充当【数据源】

示例一：查询价格高于平均价的商品信息

（子查询语句充当条件，需要用括号（运算优先级括号最高）——求取平均价，）

select * from goods where price > (select avg(price) from goods);

示例二：查询所有来自拼夕夕的商品信息，包含商品分类

（子查询语句充当数据源）

注意：如果连接查询的结果中，表和表间的字段名不能出现重复，否则无法直接使用。解决：将重复字段使用别名加以区分（表名.*：当前表的所有字段）

方法一：select * from goods go left join category ca on go.typeId = ca.typeId where go.company = '拼夕夕';

方法二：select * from (select go.*, ca.id cid,ca.typeId ctid,ca.cateName from goods go left join category ca on go.typeId = ca.typeId) newname where newname='拼夕夕';

五、数据库高级

1、数据库ER模型

基本元素：

实体（数据表）、
联系（1：1、1：n、n：n）
属性（表内字段）

2、主键和外键：

主键：primary key

	主键	外键
作用	用来保证数据完整性	用来和其它表建立联系
定义	唯一的标识一条记录，不能重复，不能为空	一表的属性是另一表的主键，可以重复，可以为空
个数	一个表主键只能有一个	一个表可以有多个外键

外键

语法格式：

constraint 外键名 foreign key(自己的字段) reference 主表（主表字段）

删除外键：

alter table 表名 drop foreign key 外键名；

说明：通过外部数据表的字段，来控制当前数据表的数据内容变更，以避免单方面移除数据，导致关联表数据产生垃圾数据的一种方法。

注意：如果大量增加外键设置，会严重影响数据查询操作以外的其它操作（增、删、改）的操作效率，因此在实际项目中很少会被采用，但是在面试中容易被问到。

3、索引

作用：提升查询查询语句的速度

定义：快速查找特定值的记录

个数：一个表主键只能有一个

语法格式：

create index 索引名称 on 表名（字段名称（长度））

删除索引:

drop index 索引名称 on 表名；

示例：验证索引效果实现步骤

注意：如果大量增加键索引设置，会严重影响数据查询操作以外的其它操作（增、删、改）的操作效率，不方便过多增加

数据库