软件测试必备硬知识！mysql必知必会

末蓝、 2022-11-04 05:26 241阅读 0赞

## 检索数据 ##

**数据库操作**

显示已有数据库  
show 数据库；

选择数据库  
use 数据库；

显示创建特定数据库  
show create database;

**表操作**

显示可用的所有表  
show tables;

显示表的字段  
show columns from 表;  
mysql 5：describe 表；

显示创建特定表  
show create table;

显示指定字段  
select 字段 from 表；

完全限定的表名  
select 表.字段 from 数据库.表；

## 多表检索 ##

\#首先这些表之间要具有表关系  
\#一共有三种方法

子查询

select 语句嵌套  
\#eg.select (select column from table2) from table1；

组合查询

union 组合多个select语句，返回一个结果集  
\#eg. select column1 from table1 union select column2 from table 2；  
\#组合的每个查询必须包含相同的列  
\#union的每条select语句必须拥有相同数量的列，列的顺序也必须相同。  
\#union结果自动去除重复的行，如果要保留重复，可以使用union all

联结

自联结

1.不指定表关系  
select 字段1，字段2 from 表1，表2；  
\#如果两个表之间没有指定关系，返回结果为笛卡尔积

2.指定表关系  
select 字段1，字段2 from 表1，表2，表3  
where 表1.id = 表2.id and 表2.name = 表3.name；  
\#返回行数 = 表1行数 \* 表2行数  
\#关系表之间通过相同的字段一一对应

等值联结（内联结）

select 字段 from 表1 inner join 表2 on 等值条件 inner join 表3 on 等值条件；  
\#表名可用as来指定别名，缩短代码长度  
\#inner可省略

外联结  
\#outer可省略

左联结  
select 字段 from 表1 left outer join 表2 on 条件；  
\#左边表1的所有行，和表2满足条件的行联结

右联结  
select 字段 from 表1 right outer join 表2 on 条件；  
\#\#右边表2的所有行，和表1满足条件的行联结

**获取最后一条自增主键的值**

select last\_insert\_id()；

获取服务器信息

显示服务器状态  
show status;  
显示服务器错误  
show errors;

显示服务器警告  
show warnings;

获取用户信息

显示授予用户的安全权限  
show grants；

**全文本搜索**

创建全文索引  
fulltext(字段)

#比如创建表的时候，可以创建全文索引，并自动维护更新索引
       CREATE TABLE article ( 
                      id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
                      title VARCHAR(200), 
                      body TEXT, 
                      FULLTEXT(title, body) 
                  ) TYPE=MYISAM;

**执行全文本搜索**

\#全文本搜索不区分大小写，可以使用binary把字符串转换为二进制，来区分大小写。  
\#和like的区别：可以使用like模糊查询，但全文本搜索会优先返回匹配度高的文本，而like只是按照查找到的顺序无脑返回。  
\#如果表中数据小于3行，全文本搜索不返回结果（数据太少没有意义）  
\#忽略词中的单引号，比如don’t的索引为dont

索引字段作为条件执行

select 字段 from 表 where match(要索引的字段) against(‘索引表达式’)

比如返回包含“aaa”文本的id号
    select text_id from 表 where match(text) against('aaa');

**索引字段作为字段返回**

select text\_id,match(text) against(‘aaa’) as rank;  
\#作为字段的时候，返回的是计算出来的匹配等级值，不包含’aaa’的文本，匹配等级值是0。

**拓展查询**

select 字段 from 表 where match(要索引的字段) against(‘aaa’ with query expansion)  
\#第一遍索引出包含’aaa’的文本，第二遍索引出第一遍索引结果中包含的另外的词。

**布尔文本搜索**

select 字段 from 表 where match(要索引的字段) against(‘aaa -rope\*’ in boolean mode)  
\#匹配包含’aaa’的文本，但排除含有rope开头的词 的文本。

**文本搜索布尔操作符：**

• ‘+’ ：包含  
against(‘aaa rope\*’ in boolean mode) \#至少半酣一个aaa或一个rope  
against(’+aaa +rope\*’ in boolean mode) \#同时包含aaa和rope  
against(’“aaa rope”’ in boolean mode) \#搜索"aaa rope"这个短语  
• ‘-’：排除  
• ‘>’：包含，且增加等级值  
• ‘<’：包含，且减少等级值  
against(’+aaa +(<rope)\*’ in boolean mode)  
\#同时包含aaa和rope，并且降低rope的等级  
• ‘()’：把词组成表达式，整组被包含、排列、排除  
• ‘~’：取消一个值的排序值

## 处理数据 ##

**拼接字段**

select concat(字段1，’(’,字段2，’)’ ) from 表；  
\#sql语句使用+或者||拼接  
\#这句返回格式为 “字段1(字段2)”，可以自己灵活改格式

**使用别名**

select concat(字段1，’(’,字段2，’)’ ) as 别名 from 表;  
\#把"字段1(字段2)“格式的字段，重命名为"别名”  
\#当表名，列名或数据库名，不符合规定，或者容易误解时，可以使用别名  
\#数据库里的字段名并没有改变成别名  
\#as可以省略不写

**计算字段**

select （字段1+字段2）\*字段3 as 别名 from 表；

**去重**

select distinct 字段 from 表；  
\#如果有null值，也会保留一个null  
\#distinct还可以和聚合函数结合使用：avg(distinct price)  
\#distinct只能用于count()，不能用于count(\*)  
\#distinct后面只能是字段名，不能是计算或表达式

**限定个数**

select 字段 from 表 limit 个数；

**限定范围和个数**

select 字段 from 表 5,9;  
mysql 5: limit 9 offset 5;  
\#从第6行开始的9条数据  
\#第一行从0开始  
\#行数不够时，有多少行就返回多少行

**排序**

按多个列排序

select 字段1，字段2，字段3 from 表 order by 字段2，字段1；  
\#先按字段2排序，相同再按字段1排序  
\#默认的是升序asc

降序排列

order by 字段1，字段2 desc；  
\#只对字段2降序排列，字段1还是升序排列  
\#desc只作用到它前面的字段  
\#如果多个列降序，需对每个列都指定desc

## 聚合函数 ##

**求平均值**

avg(字段)  
\#忽略null值的行

**计数**  
count(\*)  
\#计算一共有多少行，null值的行也算  
count(字段)  
\#对特定的列进行计数，忽略null值

**求最大/最小值**  
max(字段)/min(字段)  
\#如果是按照某一列排序，max()还可以返回最后一行，min()返回第一行

**求和**  
sum(字段)

**数据分组**

group by 字段；  
\#列名为null的自动分为一组

**过滤分组**

having 条件;  
\#用法同where，只是where对行过滤，having对分组过滤  
\#where先过滤行，再分组；having先分组，再过滤

**对每一列进行汇总**

group by 字段 with rollup;

## 文本处理函数 ##

**删除空格**

去除两边的空格：trim(字段)  
去除左边的空格：rtrim(字段)  
去除左边的空格：ltrim(字段)

**将文本转换为大/小写**

upper(字段)/low(字段)

**返回串的长度**

length(字段)

**找出串的一个字串**

locate(字串，串，n)

\#返回从n位置开始，字串在串中出现的位置，不写即为1  
\#如果找不到子串，则返回串。如果子串或串时null，则返回null。

**返回子串的字符**

substring(串，m，n)

\#m代表从哪一位开始截取，从1开始，可以是负数  
\#n代表截取多少位，不写即不限位数  
\#select substring(“jason”,-3,2); \#结果为“so”

**返回串的soundex值**

soundex()  
\#where soundex('字段) = soundex(‘lee’) \#找出发音像lee的名字，比如lie

**截位函数**

返回串右边开始的n个字符：right(字段，n)  
返回串左边开始的n个字符：left(字段，n)

## 日期和时间处理函数 ##

\#首选日期格式：yyyy-mm-dd

**增加一个日期**

adddate(时间字段，interval n type)  
\#向时间字段里增加n type  
\#type可以是day，week，year，quarter等……参考附录1  
\#date\_add 用法同上

**增加一个时间**

addtime(时间字段，“时：分：秒”)  
\#例如addtime(‘08:11:23’,‘0:1:0’) \#加上1分钟

**返回当前日期和时间**

now()

**返回当前时间**  
curtime()  
**返回当前日期**  
curdate()

**返回日期时间的日期部分**  
date(日期时间字段)  
**返回日期时间的时间部分**  
time(日期时间字段)

**返回日期的年部分**  
year(日期时间字段)

**返回日期的月份部分**  
month(日期时间字段)  
**返回日期的天数部分**  
day(日期时间字段)  
**返回星期几**  
dayofweek(日期时间字段)  
**计算两个日期之差**  
datediff(日期1，日期2)  
\#select datediff(‘2008-12-29’,‘2008-12-30’) as datediff \#返回-1

**格式化日期或时间串**  
date\_format(时间，格式)  
\#date\_format(now(),’%m-%d-%Y’)  
\#具体格式可以看附录2“格式化时间”

**返回时间的小时部分**  
hour(时间字段)  
**返回时间的分钟部分**  
minute(时间字段)  
**返回时间的秒部分**  
second(时间字段)

## 数值处理函数 ##

**返回绝对值**  
abs(数值)  
**返回平方根**  
sqrt(数值)  
**返回指数值**  
exp(数值)

**返回除的余数**  
mod(被除数，除数)  
**返回圆周率**  
pi()  
返回\[0,1)之间的随机数  
rand()  
\#如果想返回100以内，100\*rand()就可以  
向小取整（去除小数）  
floor(数值)  
**返回正弦值**  
sin(弧度值)  
**返回正切值**  
tan(弧度值)  
**返回余弦值**  
cos(弧度值)

## 操作表 ##

**增**

**创建表**

create table 表 if not exist；  
\#当表不存在时创建表

create table 表(
    字段名 字段类型 not null auto_increment,
    字段名 字段类型 null,
    字段名 字段类型 null default 默认值,
    primary key (主键) )
    engine=innoDB;

\#引擎可省略  
\#不同于其他DBMS，mysql的默认值不能是函数，只能是常量。

**插入单行**

insert into 表 values(字段1的值，字段2的值)；

\#对于自增量id，字段值填入null做忽略处理。  
\#不安全，字段顺序变了值就不对了  
\#不方便，所有的值都必须填。

insert into 表(字段1，字段2) values（字段1的值，字段2的值）;

\#更安全，字段名和值明确对应  
\#只列出要填的字段就行,null和有默认值的字段可以选填  
提高整体性能：  
\#当插入语句不是最重要的时候，可以用low\_priority降低优先级  
\#insert low\_priority into……  
\#对update和delete也适用  
**插入多行**

insert into 表(字段1，字段2) values（字段1的值，字段2的值）,（字段1的值，字段2的值）;

**插入检索的数据**

insert into 表(字段1，字段2) select 字段3，字段4 from 表1；  
\#自动把字段3和字段4中的值依次赋给字段1和字段2，  
\#表1中有多少行，表中就插入多少条数据

**增加字段**

alter table 表 add 字段 字段类型 ……；  
\#和创建表时加的参数一样，但是字段名和字段类型必须要有；

**添加主键**  
alter table 表 add primary key(字段);  
**添加默认值**  
alter table 表 add default 默认值;  
**添加外键**  
alter table 表 add foreign key(作为外键的字段) references 表(对应字段)  
**指定新增字段的位置**  
alter table 表 add 字段 字段类型 first或（after 字段）；  
\#新增字段排在第一个或者某个字段之后

## 改 ##

**更新表名**

alter table 表 rename 新表名；

**改变记录的值**

update 表 set 字段=字段值，set 字段=字段值 where 过滤条件；

\#更改多行，出现一个错误，即全部不更新  
\#无视错误，继续更新，可使用 update ignore 表 set……  
\#删除某个字段值可以set 字段=null

**更改字段属性**

alter table 表 alter 字段 set 要更改的属性 更改后的值；  
alter table 表 modify 字段 int(5) not null;  
\#要更改的属性：比如default，primary key等  
\#更改后的值：比如char(10)，not null 之类的，直接写新值去覆盖原值。  
\#modify方法：直接把字段更改为int(5)的非空类型，不用指定属性。

## 删 ##

**删除所有记录**

delete from 表；  
\#删除表中的所有数据，这里不需要通配符\*就可以执行

**删除部分记录数据**

delete from 表 where 过滤条件；

**删除表，并创建同名空表**

truncate table 表；

\#要删除表中所有的记录时，用这个方法，不要使用delete from 表一条一条删除。

**删除表**

drop 表；

**删除字段**

alter table 表 drop column 字段；

**删除字段的默认值/主键/外键**

alter table 表1 alter 字段 drop default;

\#default还可以替换primary key，foreign key等删除主键或外键。

## 视图 ##

视图  
\#视图是一张不实际包含数据的虚拟表，包含的是一个sql查询。  
\#重用sql语句  
\#保护数据，授予用户视图的访问权限，而不是整张表。

**创建视图**

create view 视图 as sql查询语句代码块；  
\#视图用于检索，最好不要用于增删改

## 存储过程 ##

\#封装一系列sql步骤  
\#简化语句，减少对基础数据的访问  
\#查询速度更快

**创建存储过程**

create procedure 存储过程名(参数) begin sql查询语句代码块 end；

**执行存储过程**

call 存储过程名(@参数，@参数)；

**删除存储过程**

drop procedure 存储过程名 if exist；

**检查存储过程**

show create procedure 存储过程名；

**显示某个存储过程**

show procedure status；

**显示存储过程的详细信息**

show procedure status like ‘存储过程名’

## 游标 ##

**创建游标**

declare 游标名 cursor for 查询语句；

**打开游标**

open 游标名；

**关闭游标**

close 游标名；

## 触发器 ##

\#只有表支持触发器，视图和临时表不支持。  
\#触发器不能更新或覆盖，必须先删除再重新创建；

**创建触发器**

create trigger 触发器名 触发时间 监视时间 on 表名 for sql语句；  
查看已有触发器  
show triggers；

**删除触发器**

drop trigger 触发器名；

**触发器四要素**

监视地点（table）  
监视事件（insert/update/delete）  
触发时间（after/before）  
触发事件（insert/update/delete）

\#after操作，是在执行了监视动作后，才会执行触发事件  
\#before操作，是在执行了监视动作前，会执行触发事件

**行变量**

执行insert后会有一个新行，这个新行的变量用new表示  
执行delete后会有一个旧行，这个旧行的变量用old表示  
\#在触发目标上执行update操作后原纪录和新行，可以分别使用old和new

**行触发器**

for each row

\#每一行受影响，触发事件都执行  
\#for each row如果不写，无论update语句一次影响了多少行，都只执行一次触发事件。

## 引擎 ##

**InnoDB**  
事务处理引擎，不支持全文本搜索

**myISAM**  
性能高，支持全文本搜索，不支持事务处理

**memory**  
同MyISAM，但数据存储在内存，而不是磁盘，速度很快（特别适合临时表）  
\#不同的表可以用不同的引擎类型，但外键不能跨引擎引用。

## 附录1 date type ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70]

## 附录2 格式化时间 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70 2]  
如果对软件测试有兴趣，想了解更多的测试知识，解决测试问题,以及入门指导，帮你解决测试中遇到的困惑，我们这里有技术高手。如果你正在找工作或者刚刚学校出来，又或者已经工作但是经常觉得难点很多，觉得自己测试方面学的不够精想要继续学习的，想转行怕学不会的， 都可以加入我们  
![在这里插入图片描述][20210714132655347.png_pic_center]  
，群内可领取最新软件测试大厂面试资料和Python自动化、接口、框架搭建学习资料！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70]: /images/20221023/95cacc0e1d1340ed84fe761ebbc8ef72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70 1]: /images/20221023/6001363a209844e19855285d48649cba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70 2]: /images/20221023/717c240428df46daac71a96402bbe81a.png
[20210714132655347.png_pic_center]: /images/20221023/dd75e7ae9c6b41f695a41b7363a29ebc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDM0MzE4_size_16_color_FFFFFF_t_70_pic_center]: /images/20221023/cef6d96e407d4060ae902e512813719b.png