软件测试mysql常用知识点

最新推荐文章于 2022-08-08 14:00:50 发布

是美好啊

最新推荐文章于 2022-08-08 14:00:50 发布

阅读量1.1k

点赞数 1

分类专栏：常用知识点文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_41596734/article/details/108919222

版权

常用知识点专栏收录该内容

5 篇文章 1 订阅

订阅专栏

软

一 MySQL数据库（关系型数据库，保存数据库的仓库）

1 数据库作用

数据库可以永久保存。
使用sql语句，查询方便效率高。
方便数据管理

2 数据库练习

学习来源：https://www.bilibili.com/video/BV1Us411n7XL?p=2

where：结果集进行筛选。

having：对分组的结果进行筛选。

not in（数据集），数据集必须时一个字段。

not exist（语句）：任何语句，不在乎返回的结果集。

count：计数。

left join on：左连接.

子查询：非相关子查询，相关子查询。

查询指定字段比直接查询*速度更快
if（condition,r1,r2）：满足condition，结果就是r1,否则为r2

max：最大值处理

sum：求和处理

case 条件值 when 值1 Then 输出结果1 when 值2 Then 输出结果2 else 输出结果 End：case 函数，条件值符合when后面的值，会输出对应的输出结果，End代表结束

union：并集,union all会获取所有记录；union会忽略重复的数据。使用注意select查询结果集字段相同

常量：可以获取数据表常量作为结果集列字段

多个表union并集后对结果集进行排序：进行查询虚表需要别名，然后进行排序order by desc，asc等排序数值类型

测试工程师：

数据验证，验证数据的一致性、完整性、正确性
制造数据，方便测试
数据测试，功能测试、性能测试
接口测试、自动化测试

测试需求：
需求不明确，明确数据库表结构、表与表的关系，数据库字典文档

3 Mysql数据库知识点

3.1 数据类型

3.1.1 数值型

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
在这里插入图片描述

3.1.2 日期类型

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。
在这里插入图片描述

3.1.3 字符类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

在这里插入图片描述

3.2 面试题目

SQL是一种结构化查询语句，用于访问和操作数据库的标准计算机语言。通常用途为操作数据库对象（表、存储过程、函数、索引），表记录的增删查改。SQL是一门若语言不分大小写。通常SQL分为一下五大类。
DDL：数据定义语言，包含create、alter、drop、truncate
DML：数据操作语言，包含insert、delete、update、select
DQL：数据查询语言，仅包含select，数据库中的重点
TCL：事务控制语言，包括begin、savepoint xxx、commit、rollback to xxx、commit
DCL：数据控制语言，包括分配数据库用户权限相关SQL语句

常用SQL语句

3.2.1 表里有ID自增主键，当insert8条数据，删除第7,8条数护具，再把mysql冲爱情，再insert一条记录，这条记录的ID是多少？

考试知识点：
Innodb：索引是一种排好序用户快速查找的数据结构。

1）如果表的类型是MyISAM，那么是9。
　　因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MysQL自增主键的最大ID也不会丢失。
　　
2）如果表的类型是InnoDB，那么是7。
　　InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

3.2.2 Mysql的技术特点是什么？
Mysql数据库软件时一个客户端或服务系统，其中包含：支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理工具。

3.2.3 数据库相关SQL

1.查询所有数据库
show database;

2.创建数据库
create database 数据库名称

3.删除数据库
drop database 数据库名称

4.查询数据库详情、查看数据库的字符集
show create database 数据名称

5.创建指定字符集数据库
create database 数据库名称 character set gbk/utf

6.选中数据库
use 数据库名称

3.2.4 表相关SQL

不使用工具的情况，在命令中输入下面相关的命令，需要先使用use 数据库名选择要操作表所属的数据库。

# 创建数据库表语句如下：创建引擎
create table if not exists 表名(
字段名1 字段类型,
字段名2 字段类型
)engine=InnoDB default charset=utf9;

#查询所有表
show tables;

#查询表详情
show create table 表名;

#查看表结构
desc 表名

#删除表
drop table 表名

3.3.5 修改表相关SQL

#修改表名
rename table 原表名 to 新表名；

#修改表引擎和字符集
alter table 表名 engine=innodb/myisam charset=gbk;

#添加表字段在最后
alter table 表名 add 字段名 字段类型

#添加表字段最前面
alter table 表名 add 字段名 字段类型 first;

#添加字段（指定字段后面）
alter table 表名 add 字段名A 字段A类型 after 字段B

#删除表字段
alter table 表名 drop 字段名

#修改表字段名称和类型
alter table 表名 change 字段名 原字段名 新字段名 新类型;

3.3.6 操作表记录相关SQL

SQL语言中最常用DML，就是增删查改

# 插入数据，全表插入
insert into 表名 values (字段1值,字段2值);
#插入数据，指定字段插入
insert into 表名(字段1,字段2) values (字段1值,字段2值);

#删除数据
delete from 表名 where 字段名=值;
#删除表中全部数据
delete from 表名

#修改指定值
update 表名 set 字段名1=值1 where 字段名1=值2;
#修改全部值
update 表名 set 字段名=值1;

#查询所有
select * from 表名

DQL（查询）详解

3.2.1 条件查询

#判断是否为空,例如水果价格表中的价格是否为空(is null,is not null)
select * from fruits where price is null;

#比较运行符（>、<、>=、<=、！=、<>）
#例如，查询水果价格大于20的水果信息
select * from fruits where price>20;

#and 和 or
#例如：查询水果为苹果，单价为5元的水果信息
select * from fruits where type='apple' and price='5'
#例如：查询水果类型为苹果或单价为5元的水果信息
select * from fuirts where type='apple' or price='5'

#in 在某些可选值范围内
#例如：查询单价为（5,6，7）的水果信息
select * from fruits where price in(5,6,7);

#between和 not between
#例如：查询水果单价在5-10范围内，包含5,10
select * from fruits where price between 5 and 10

#模糊查询 like_ 代表单个字符，% 代表0个或多个
#例如：查询水果名称为2个字符，且水果名第一个为苹的水果信息
select * from fruits where name='苹_'
#例如：查询水果名称已香开头的水果信息
select * from fruits where name='香%';

3.2.2 别名、去重
别名：使用as关键字或空格

#案例 查询单价为10元的水果名称
select f.name as 名称 from fruits f where f.price=10

去重：使用distinct对查询出来的进行去重

#例如查询的水果名称有重复
select distinct name from fruits

3.3.3 排序、分页查询
排序：order by （ASC 升序默认，DESC 降序）

分页查询：limit 跳过条数A 每页数量B

limit分页公式：curPage是当前第几页；pageSize是一页多少条记录
limit (curPage-1)*pageSize,pageSize

#例如：查询学生表数据id降序、年龄升序排序
select * from stuinfo order by id desc age;

#分页查询—limit 跳过条数A 每页数量B
#例如：查询学生表中年龄第三大的学生信息
select * from stuinfo order by age desc limit 2,1;

#分页查询sql的格式为curPage为页码 pageSize是每页显示的条数
select * from table limit (curPage-1)*pageSize,pageSize;

3.3.4 聚合函数、时间相关函数

#聚合函数：sum（字段名）求和
#分数求和,
select sum(score) from t_score

#聚合函数：avg(字段名) 平均值
#分数平均值
select avg(score) from t_score

#聚合函数：max(字段名) 最大值
select max(score) from t_score

#聚合函数：min(字段名) 最小值
select min(字段名) from t_score

#聚合函数：count(字段名) 统计数量
select count(*) from t_score

#时间函数：now() 当前年月日时分秒
select now()
#输出当前时间：2020-10-17 16:12:!2

#时间函数：current_date() 当前时间年月日
select current_date();
#输出当前时间年月日 2020-10-17

3.3.5 分组与having

#分组：group by
#计算每个学生的总成绩并排名（显示字段：姓名，总成绩）
select name,sum(score) as allscore from s_score group by sname order by allscore;

#having：解决聚合函数过滤问题，一般配合group by一起使用
#计算每个学生的总成绩，并查询总分大于600分的学生
select name,sum(score)as allscore from s_score group by sname having allscore>600

3.3.6 子查询
子查询就是写在where/having后作为查询条件的值。

临时表，用在from后面当做一个新表，新表需要有名称。

将一个查询结果作为一个新表，然后从新表查询数据。

#查询员工工资最低的员工信息，由于可能最低有多个员工，所以需要先查询出最低工资
select * from employees where salary=(select min(salary) from employees);

3.3.7 关联查询

INNER JOIN：如果表中有至少一个匹配，则返回行。

inner join与join相同

select col_names 
from table1
inner join table2
on table1.col_name=table2.col_name;

或
select col_names 
from table1
join table2
on table1.col_name=table2.col_name;

LEFT JOIN：关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

select col_name(s)
from table1
left join table2
on table1.col_name=table2.col_name

RIGHT JOIN：关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为
NULL。

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

select col_name(s)
from table1
right join table2
on table1.col_name=table2.col_name

FULL JOIN：关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

select col_name(s)
from table1
full outer join table2
on table1.col_num=table2.col_num

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

select col_name(s) from table1
union
select col_name(s) from table2;

约束

mysql中约束是对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束包含：非空约束（not null）、唯一约束（unique）、主键约束（primary key）、默认约束（default）、外键约束（foreign key）、指定约束（check）

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE
的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

索引

索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

普通索引：

#创建索引,
#如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
create index indexName on table_name(col_name)

#修改表结构（添加索引）
alter table tableName add index indexname(col_name)

#创建表的时候直接指定
create table mytable(
id int null,
username varchar(10) not null,
index [indexName] (username(length))
)

#删除索引
drop index [indexName] on mytable;

唯一索引： 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

#创建索引
create unique index indexName on mytable(username(length))

#修改表结构
alter table mytable add unique [indexName] (username(length))

#创建表的时候指定
create table mytable(
id int not null,
username varchar(10) not null,
unique [index] (username(length))
)

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

# 创建数据表
CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  

#查询数据表
select * from runoob_transaction_test;

#开始事务
begin
insert into runoob_transaction_test value(5);

# 回滚
rollback;   

# 因为回滚所以数据没有插入，查询数据没有变
 select * from runoob_transaction_test;

存储过程

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点：
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

缺点：
存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校与撰写，受限于各种数据库系统。

#创建数据库
create database db1;
use db1;
#创建存储过程
#将语句的结束符号从分号，临时改为两个$$可以是自定义
delimiter $$ 

create procedure delete_matches(in p_playerno integer)
begin
	delete from matches where playerno=p_playerno;
end$$

#将语句的结束符号恢复为分号
delimiter;  

#调用存储函数
call delete_matches(传参)