软件测试——数据库

藤藤_

已于 2023-01-29 11:49:24 修改

阅读量505

点赞数

分类专栏：测试文章标签：数据库 mysql sql

于 2023-01-09 22:25:55 首次发布

本文链接：https://blog.csdn.net/mercury__/article/details/128615399

版权

测试专栏收录该内容

7 篇文章 0 订阅

订阅专栏

【目录】

数据库常见分类
- 关系型数据库
- 非关系型数据库
数据库连接工具Navicat
数据类型与约束
- 数据类型
- 约束
SQL介绍
SQL语句
扩展——E-R模型
扩展——外键
扩展——索引
扩展——查看创表语句和表注释

数据库常见分类

关系型数据库

以数据表为核心存储数据

核心元素：

代表产品：
非关系型数据库

不存在数据表的概念，数据以键值对、文本、图片等形式存储

代表产品：

数据库连接工具Navicat

连接：
在这里插入图片描述
写命令：

写查询：

数据类型与约束

数据类型
约束

注：（在Navicat中的设置位置）
主键

非空

唯一

默认值

SQL介绍

SQL（结构化查询语言）：通过SQL语言可以对数据库进行操作

SQL语言的分类：

注！
1.SQL语言默认支持所有常见的关系型数据库
2.对测试而言，必须要掌握DQL
3.对MySQL而言，不区分字母大小写

SQL语句

数据库增删改查

-- 创建数据库
-- create database 数据库名 charset=utf8 collate=utf8_general_ci;
create database test charset=utf8 collate=utf8_general_ci;
 
 
-- 使用数据库（切换当前数据库）
-- use 数据库名;
use test;


-- 查看当前数据库
select database();


-- 修改数据库
-- alter database 数据库名
-- default character set 编码格式
-- default collate 排序规则;
alter database test
default character set utf8mb4
default collate utf8mb4_general_ci;


-- 删除数据库
-- drop database 数据库名;
drop database test;


-- 查看所有数据库
show databases;

扩展——Navicat操作数据库备份

在测试⼯作中, 为了防⽌对数据库产⽣错误操作, 或产⽣垃圾数据, 都需要在操作前, 适当对数据库进⾏备份操作

步骤（使用工具）：
1.选中数据库>>转储SQL文件>>结构+数据
2.选择合适路径存储SQL文件
3.选中数据库>>运行SQL文件
4.找到存储的SQL文件进行还原
步骤：（使用命令）

数据表增删改查

-- 创建表（先判断表是否存在，若存在则先删除再创建，只需在直接创建表前加一个判断删除的语句）
-- drop table if exists 表名
-- create table 表名(
-- 字段名 类型 约束（unsigned : ⽆符号；primary key : 主键；auto_increment : ⾃动增⻓）,
-- 字段名 类型 约束
-- ...
-- )
drop table if exists students;
create table students(
 id int unsigned primary key auto_increment,
 name varchar(20),
 age int unsigned,
 height decimal(5,2)
);


-- 查看表
-- show create table 表名;
show create table students;


-- 查看表结构
-- desc 表名;
desc students;


-- 删除表
-- drop table 表名;
drop table students;


-- 增加数据

-- 一.增加⼀⾏数据
-- 1.增加一行完整数据
-- insert into 表名 values(...)
-- 注意: 数据值需要和表的字段⼀⼀对应(数据个数及数据类型)；主键列是⾃动增⻓，插⼊时需要占位，通常使⽤ 0 或者 default 或者null 来占位，插⼊成功后以实际数据为准
insert into students values(0, '张三', 28, 1.78);
-- 2.增加一行部分数据
-- insert into 表名(字段1,...) values(值1,...)
insert into students(name, height) values('李四', 1.68);

-- 二.增加多行数据
-- ⽅式1: 将单⾏插⼊语句多句执⾏, 每句分号隔开
insert into students values(0, '王五', 28, 1.78);
insert into students(name, height) values('赵六', 1.68);
-- ⽅式2: 在插⼊单⾏数据的语法基础上, 将 value 后边的数据进⾏多组化处
理
-- insert into 表名 values(...),(...)...
-- insert into 表名(列1,...) values(值1,...),(值1,...)...
insert into students values(0, '王五1', 29, 1.78),(0, '王五2',
30, 1.78);
insert into students(name, height) values('赵六1', 1.78),('赵六
2', 1.88);


-- 修改数据
-- update 表名 set 列1=值1,列2=值2... where 条件
-- 注意: where 不能省略, 否则会修改全部数据
update students set age=48 where id=9;


-- 删除数据
-- delete from 表名 where 条件;
-- 注意: where 不能省略, 否则会删除全部数据
delete from students where id=6;

重中之重——单表查询

**基本查询**

-- 查询所有数据
-- select * from 表名;
select * from goods;

-- 查询部分字段
-- select 字段名1, 字段名2 from 表名;
select goodsName, price from goods;

-- 查询时起字段别名
-- select 字段名 as '别名' from 表名;
select goodsName as '商品名称', price as '价格' from goods;
-- 注意: 别名的引号可以省略
select goodsName as 商品名称, price as 价格 from goods;
-- 注意: as也可以省略（常用）
select goodsName 商品名称, price 价格 from goods;
-- 起别名的作⽤:
-- 1> 美化数据结果的显示效果 
-- 2> 可以起到隐藏真正字段名的作⽤
-- 另: 除了可以给字段起别名以外, 还可以给数据表起别名(连接查询时使⽤)

-- 去重
-- 目的: 将查询的⽬标字段内重复出现的数据只保留⼀份显示
-- select distinct(字段名) from goods;
select distinct(company) from goods;



**复杂查询——条件查询**

⽐较运算符：
等于：=
大于：>
大于等于：>=
小于：<
小于等于：<=
不等于：!= 或 <>

逻辑运算符：
与：and
或：or
非：not

-- 例：查询价格等于30的商品信息
select * from goods where price=30;

-- 例：查询价格等于30并且出⾃并夕夕的所有商品信息
-- 注意: 字符串作为查询条件使⽤必须带引号！
select * from goods where price=30 and company='并夕夕';

-- 例：查询价格等于30但不出⾃并夕夕的所有商品信息（以下两种顺序都行）
-- 注意: not（右边连接条件） 与 and 和 or (左右两边连接条件)不同之处在于, not 只对⾃⼰右侧的条件有作⽤
select * from goods where not company='并夕夕' and price=30;
select * from goods where price=30 and not company='并夕夕'



**复杂查询——模糊查询**

like、%(任意多个字符)、_(任意⼀个字符)

-- 例：关键词在中间
select * from goods where remark like '%⼀次性%';
-- 例：关键词在末尾
select * from goods where remark like '%⼀次性';
-- 例：关键词在开头
select * from goods where remark like '⼀次性%';
-- 例：关键词在开头且字符长度为5的
select * from goods where remark like '⼀次性__';



**复杂查询——范围查询**

⾮连续范围: in(...,...)
连续范围: between ... and ...

-- 例：查询家乡是北京/上海/广东的学生
select * from students where hometown in('北京','上海','广东');

-- 例: 查询所有价格在30-100的商品信息
-- 注意: between ... and ... 的范围必须是从⼩到⼤
select * from goods where price between 30 and 100;



**复杂查询——查询空**

注意: 在MySQL中, 只有显示为 NULL 的才为空。其余空⽩可能是空格/制表符(tab)/换⾏符(回⻋键)等空⽩符号
为空: is null
不为空: is not null

-- 例: 查询没有描述信息的商品信息、
select * from goods where remark is null;

-- 例: 查询有描述信息的所有商品
select * from goods where remark is not null;



**复杂查询——排序**

-- select * from 表名 order by 列1 asc|desc,列2 asc|desc,...
-- 说明: asc是升序（默认）, desc是降序
select * from goods order by price desc;

-- 注意: 排序过程中, ⽀持连续设置多条排序规则（往后的排序规则建立在之前的排序基础之上）
-- 例: 查询所有商品信息, 按照价格从⼤到⼩排序, 价格相同时, 按照数量少到多排序
select * from goods order by price desc, count asc;
-- 注意: 默认排序为升序, asc 可以省略
select * from goods order by price desc, count;



**复杂查询——聚合函数**

聚合函数: 系统提供的⼀些可以直接⽤来获取统计数据的函数
常用聚合函数：
count()：计数
max()：最大值
min()：最小值
sum()：求和
avg()：平均值

-- 商品信息总条数: count(字段): 查询总记录数
-- 注意: 统计数据总数, 建议使⽤*, 如果使⽤某⼀特定字段, 可能会造成数据总数错误（比如要是某条数据该字段刚好为空呢）
select count(*) from goods;

-- 最⾼商品价格: max(字段): 查询最⼤值
select max(price) from goods;

-- 最低商品价格: min(字段): 查询最⼩值
select min(price) from goods;

-- 商品平均价格: avg(字段): 求平均值
select avg(price) from goods;

-- ⼀次性⼝罩的总数量: sum(): 求和
select sum(count) from goods where remark like '%⼀次性%';

-- 扩展: 在需求允许的情况下, 可以⼀次性在⼀条 SQL语句中, 使⽤所有的聚合函数
select count(*), max(price), min(price), avg(price) from goods;



**复杂查询——分组**

说明：按照字段分组的话，该字段相同的数据会被放到一个组中
注意:
1> ⼀般情况, 使⽤哪个字段进⾏分组, 那么只有该字段可以在 * 的位置处使⽤,并不是说其他字段放那里会报错，只是没有实际意义
2> 分组操作多和聚合函数配合使⽤

-- select 字段1,字段2,聚合函数... from 表名 group by 字段1,字段2...
-- 例: 查询每家公司的商品信息数量
select company, count(*) from goods group by company;

扩充: 分组后条件过滤，使⽤ having 关键字
where 和 having 的异同:
（同）having 关键字后侧可以使⽤的内容与 where 完全⼀致(⽐较运算符/辑运算符/模糊查询/判断空)
（异）having 后⾯的条件中可以⽤聚合函数，where 后⾯不可以
（异）where 是对 from 后⾯指定的表进⾏数据筛选，属于对原始数据的筛选；having 是对 group by 的结果进⾏筛选，属于对筛选数据的再次筛选

-- 例：查询男生总人数
select sex,count(*) from students group by sex having sex='男';
-- 例：列出班级平均年龄大于22岁的班级
select class from students group by class having avg(age)>22;



**复杂查询——分页**

数据量过大时，在一页中查看所查询到的数据会比较麻烦，所以需要分页展示

-- 分⻚查询: select * from 表名 limit start,count
-- 说明: limit 分⻚; start : 起始⾏号; count : 数据⾏数
-- 注意: 计算机的计数从 0 开始, 因此 start 默认的第⼀条数据应该为 0

-- 例: 获取前 5 条数据
select * from goods limit 0, 5;
-- 注意: 如果默认从第⼀条数据开始获取, 则 0 可以省略!
select * from goods limit 5;
-- 例：获取从第五行开始的6条数据:
select * from goods limit 4, 6;

-- 扩展 1: （典型应用场景）每⻚显示m条数据，求显示第n⻚的数据
-- select * from 表名 limit (n-1)*m, m
-- 例: 共12条数据，每⻚显示 4 条数据, 求展示前四页分别的数据内容
select * from goods limit 0, 4; -- 第1⻚(有数据)
select * from goods limit 4, 4; -- 第2⻚(有数据)
select * from goods limit 8, 4; -- 第3⻚(有数据)
select * from goods limit 12, 4; -- 第4⻚(无数据)

-- 扩展 2: 其他应⽤场景
-- 例: 要求查询商品价格最贵的数据信息
select * from goods order by price desc limit 1;
-- 例: 要求查询商品价格最贵的前三条数据信息
select * from goods order by price desc limit 3;

重中之重——多表查询

实际工作中一般最多三张表，从第三张表开始，若想与前两张表已经连接完的结果进行连接，那么只能左连接

**内连接**

-- select * from 表1 inner join 表2 on 表1.列=表2.列
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 表1, 表2 where 表1.字段名=表2.字段名;
select * from goods, category where goods.typeId=category.typeId;



**左连接**

-- 注意: 如果要保证⼀张数据表的全部数据都存在, 则⼀定不能选择内连接,可以选择左连接或右连接

-- select * from 表1 left join 表2 on 表1.列=表2.列
select * from goods go
left join category ca on go.typeId=ca.typeId;



**右连接**

-- select * from 表1 right join 表2 on 表1.列=表2.列
select * from goods go
right join category ca on go.typeId=ca.typeId;

**自关联**

前提: 1> 数据表只有⼀张 2> 数据表中⾄少有两个字段之间有某种联系
⽅式: 通过给表起别名的形式, 将原本只有⼀张的数据表变为两张, 然后通过对
应字段实现连接查询

-- 说明: ⽆论是使⽤内连接还是左连接, 都只影响中间数据表的内容多少, 由于最终的过滤条件相同, 因此查询结果⼀致

-- 例: 查询河南省所有的市（这张表title字段包含全部的省市区）
-- 使⽤内连接
select * from areas a1
inner join areas a2 on a1.aid=a2.pid
where a1.atitle='河南省';
-- 使⽤左连接
select * from areas a1
left join areas a2 on a1.aid=a2.pid
where a1.atitle='河南省';

-- 例: 查询河南省的所有的市和区
-- 说明: 想要实现三级⾏政单位显示, 需要分别处理省和市及市和区(三表连
查)
select * from areas a1
left join areas a2 on a1.aid=a2.pid
left join areas a3 on a2.aid=a3.pid
where a1.atitle='河南省';



**子查询**

在⼀个 select 语句中，嵌⼊了另外⼀个 select 语句，那么嵌⼊的select 语句称之为⼦查询语句，子查询语句需要用括号括起来。
⼦查询是辅助主查询的，要么充当[条件]，要么充当[数据源]

-- ⼦查询语句充当条件:
-- 例：查询价格高于平均价的商品
select * from goods where price > (select avg(price) from
goods);

-- ⼦查询语句充当数据源:
-- 注意：需给新形成的数据源表起个别名
-- 例: 查询所有来⾃并夕夕的商品信息, 包含商品分类
select * from (select * from goods go left join category ca on go.typeId=ca.typeId) new
where new.company='并夕夕';

扩展——对分组查询的理解

例：
初始数据：

按公司分组后查询全部（此时查询出的数据想象垂直屏幕来看，看作有三组队伍【因为一共三个公司】，展示出来的分别是站在队伍最前方的，实际屏幕后面还有数据）：
扩展——逻辑删除

对于重要的数据，不能轻易执⾏ delete 语句进⾏删除。因为⼀旦删除，数据⽆法恢复，这时可以进⾏逻辑删除。

1、给表添加字段，代表数据是否删除，⼀般起名 isdelete（或者delflag），0代表未删除，1代表删除，默认值为0
2、当要删除某条数据时，只需要设置这条数据的 isdelete 字段为1
3、以后在查询数据时，只查询出 isdelete 为0的数据
扩展——三种表删除的区别

– delete from 表名 : 删除表数据，保留表结构（自增字段不会从1开始）
– truncate table 表名 : 删除表数据，保留表结构（自增字段会从1开始）
– drop table 表名 : 删除表数据及结构