2022软件测试技能 Mysql数据库必会知识点总结

系列文章目录

提示：阅读文章前，建议先完整看一遍目录

---

前言

对于想入行软件测试行业的小伙伴们，mysql是绕不过的一项技能，因为在软件测试中，你需要通过自己编写mysql执行语句，来达到测试数据的目的，因为，前端的一切UI操作，后端接口的调用，最终都是操作数据，因此，数据库，必须掌握。

一、基础命令行

在了解语法之前，我们应当先了解，可以通过命令行来操作数据库

1. 登录

命令
mysql -h 主机ip -u 用户名 -p 密码 -P 端口

如果是在本机链接mysql，就无需加-h参数，端口默认为3306
mysql -u root -p

[root@localhost ~]# mysql -u root -p
Enter password: 

2. 显示所有数据库

show databases;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaoshi0703         |
| mysql              |
| performance_schema |
| test               |
| zentao             |
+--------------------+
6 rows in set (0.01 sec)

3. 创建数据库

create database 数据库名称 charset utf8;

MariaDB [(none)]> create database test_db charset utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaoshi0703         |
| mysql              |
| performance_schema |
| test               |
| test_db            |
| zentao             |
+--------------------+
7 rows in set (0.00 sec)

4. 删除数据库

drop database 数据库名称;

MariaDB [(none)]> drop database test_db;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaoshi0703         |
| mysql              |
| performance_schema |
| test               |
| zentao             |
+--------------------+
6 rows in set (0.00 sec)

5. 切换数据库

use 数据库名称;

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> 

6. 显示当前数据库所有表

注意：要先切换数据库
show tables;

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
...
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

7. 查看表结构

desc 表;

MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
....
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

8. 查看创建库的语句

show create database 数据库名;

MariaDB [mysql]> show create database mysql;
+----------+----------------------------------------------------------------------------------------+
| Database | Create Database                                                                        |
+----------+----------------------------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

9. 设置用户可以远程访问

注意：本质上就是修改user表里面，用户的host，%表示可以任意主机登录，localhost则是本地登录

update user set host=’%’ where user=’test’;

MariaDB [mysql]> update user set host='%' where user='test';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

flush privileges (立即刷新生效，不需要重启mysql)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

10. 其他

其他很多的命令行，其实就是把sql语句放到命令行去执行
比如，查询语句 select * from 表名;

MariaDB [zentao]> select * from zt_group;
+----+--------------+-------+-----------------+-----+
| id | name         | role  | desc            | acl |
+----+--------------+-------+-----------------+-----+
|  1 | 管理员       | admin | 系统管理员      |     |
|  2 | 研发         | dev   | 研发人员        |     |
|  3 | 测试         | qa    | 测试人员        |     |
|  4 | 项目经理     | pm    | 项目经理        |     |
|  5 | 产品经理     | po    | 产品经理        |     |
|  6 | 研发主管     | td    | 研发主管        |     |
|  7 | 产品主管     | pd    | 产品主管        |     |
|  8 | 测试主管     | qd    | 测试主管        |     |
|  9 | 高层管理     | top   | 高层管理        |     |
| 10 | 其他         |       | 其他            |     |
| 11 | guest        | guest | For guest       |     |
+----+--------------+-------+-----------------+-----+
11 rows in set (0.01 sec)

所以，尽情发挥你的思维吧！

二、数据类型

1. 数值类型

类型	用途
int	用于整数的储存，有符号时范围(-2 147 483 648，2 147 483 647)，无符号时范围(-2 147 483 648，2 147 483 647)
tinyint	用于小整数的储存，有符号时范围 (-128，127)，无符号时范围 (0，255)
smallint	用于大整数的储存，有符号时范围 (-32 768，32 767)，无符号时范围 (0，65 535)
mediumint	用于大整数的储存，有符号时范围 (-8 388 608，8 388 607)，无符号时范围 (0，16 777 215)
bigint	用于极大整数的储存，有符号时范围 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807)，无符号时范围 (0，18 446 744 073 709 551 615)
float	用于单精度浮点数值的储存，有符号时范围 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)，无符号时范围 0，(1.175 494 351 E-38，3.402 823 466 E+38)
double	用于双精度浮点数值的储存，有符号时范围 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)，无符号时范围 0，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
decimal	用于小数值的储存，比如：金额，用法需要指定两个参数，decimal(小数点前的位数，小数点后的位数)，例如：decimal(10,2), 有符号时范围 （依据参数），无符号时范围（依据参数）

2. 日期类型

类型	范围	格式	用途
data	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
year	1901/2155	YYYY	年份值
datetime	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3. 字符串类型

类型	用途
char	定长字符串，例如：char(2)，每一行数据，都会创建一个占位2个字符的数据
varchar	变长字符串，例如：varchar(10)，例如：name varchar(20)，xiaoming，那么只会创建8个字符的占位，不会创建20个占位，不过，最高只能占位20个字符，对于不确定长度的字符串，可以使用
tinytext	短文本字符串，最多255个字符
text	长文本数据，0-65 535 bytes
longtext	极大文本数据，0-4 294 967 295 bytes

三、sql语句

1. create table 创建表

语法：create table 表名（列名 列的类型）charset=utf8;
例如：

create table student (
	// 学号，整数类型，主键，自增
	'sno' int primary key auto_increment,
	// 姓名，字符串类型，长度最大20，不为空，而且唯一
	'name' varchar(20) not null unique,
	// 出生日期，日期格式
	'birth' datetime,
	// 备注，文本数据
	'comment' text
) charset=utf8;

2. drop table 删除表

语法：drop table 表名;
例如：

drop table student;

3. alter 修改表结构

新增列
语法：alter table 表名 add 列名 列的类型;
例如：

alter table student add age int;

删除列
语法：alter table 表名 drop 列名;
例如：

alter table student drop age;

修改列类型
语法：alter table 表名 modify 列名 列的类型;
例如：

alter table student modify comment longtext;

修改列名
语法：alter table 表名 change 列名 新的列名 列的类型;
例如：

alter table student change comment cmt longtext;

修改表名称
语法：alter table 表名 rename 新的表名;
例如：

alter table student rename stu;

4. insert into 插入数据

语法：insert into 表名(列名，列名…) values (数值1，数值2…)
例如：

insert into student(name, age) values('xiaoming', 18)

不加列名，则表示，values会按照列名的顺序，传入全部列名的值

insert into student values('xiaoming', 18, '1998-06-21')

也可以一次，添加多条数据

insert into student(name, age) values('xm', 18),('xh', 20),('xf', 26)

5. update 更新数据

语法：update 表名 set 列名=数值 where 条件
例如：

update student set age=30 where name='xiaoming'

使用此语句，最好习惯加where之后，再执行，如果不加where条件，默认会更新某个表的全部数据，所以，要非常小心的使用此语句

update student set age=20

6. delete from 删除数据

语法：delete from 表名 where 条件
例如：

delete from student where name='xiaoming'

如果你想删除某个表的所有数据，可以不加任何条件，所以，使用此命令，需要非常小心，否则的话，可能会把所有删除全部删光！！！

delete from student

7. select 查询数据

注意：查询语句的功能十分强大且灵活，这里只展示基础的语法，其他语法，在下面会扩展，例如：having，order by，group up，join等

语法：select 列名1，列名2… from 表名 where 条件
例如：

select name, age from student where name = 'xiaoming'

如果要把所有列名查询出来，写 * 即可

select * from student where name = 'xiaoming'

如果不加条件，即会把所有数据读出来，但是要注意，这样也是比较危险的，试想一下，如果某个表的数据量，可能高达几十万条，上百万条，这样一条查询语句，把所有数据都拉出来，服务器性能可能会达到高峰，疯狂处理这个结果，如果服务器性能不佳，可能会直接导致崩溃，进而引发系统崩溃，造成经济损失，因此，这里还是习惯性建议加上where，除非确保数据量不大，学习阶段，就可以不加

select * from student

8. 高阶查询数据

在where 使用，and ，or，not，in，not in，any，all，is null， in not null，like，between，来完成更复杂的查询

and 表示，两边都成立
查询，姓名为 xiaoming，年龄为 18

select * from student where name='xiaoming' and age=18;

or 表示，一边成立即可
查询，姓名为 xiaoming 或者 姓名为 xiaohong

select * from student where name='xiaoming' or name='xiaohong';

not 结果取反
查询，姓名不为 xiaoming

select * from student where not name='xiaoming'

in 包含在某个结果集里面
查询，姓名为xiaoming，或者为 xiaohong

select * from student where name in ('xiaoming', 'xiaohong')

not in 不包含在某个结果集里面
查询，姓名不为xiaoming，也不为xiaohong

select * from student where name not in ('xiaoming', 'xiaohong')

any 任意一条包含在结果集里面
查询，年龄比xiaoming，xiaohong大的

select * from user
where
age > any(select age from user where name in ('xiaoming', 'xiaohong'))

all 全部，前面的数据必须符合所有结果
查询，年龄均大于，一年级的学生

select * from user
where
age > all(select age from user where class = '一年级')

is null 为空的数据
查询，没有绑定手机号的学生

select * from user where phone is null

is not null 不为空的数据
查询，绑定手机号的学生

select * from user where phone is not null

like 模糊查询

% 表示任意0个或多个字符
_ 表示任意单个字符
[] 表示括号内任意一个字符
[^]表示不在括号内单个字符

查询姓名为 林 开头的学生

select * from user
where name like '林%'

查询姓名 明 结尾的学生

select * from user
where name like '%明'

查询姓名为三个字的

select * from user
where name like '___'

查询姓名为四个名字，并且第三个字，可能为王，亮，舞的学生

select * from user
where name like '__[王亮舞]_'

查询姓名为四个名字，并且第二个字，不为王，亮，舞的学生

select * from user
where name like '_[^王亮舞]__'

查询姓名包含小的学生

select * from user
where name like '%小%'

between 包含，前面的数据，包含在什么之间
语法：between start and end
注意：查询结果，均包含start和end的范围，比如1 and 10，查询就包括1和10
例如：

查询，年龄在15到30之间的学生

select * from user
where
age between 15 and 30;

9. 聚合函数

sum 合计
min 最小
max 最大
avg 平均
count 统计
常用于 select 的列名中，也会用于having，但是用法几乎一致，且简单

sum 合计
查询，所有学生的年龄加起来的总和

select sum(age) from student

min 最小
查询，年龄最小的学生

select min(age) from student

max 最大
查询，年龄最大的学生

select max(age) from student

avg 平均
查询，所有学生平均年龄

select avg(age) from student

count 总数
查询，学生的人数

select count(*) from student

10. 别名

语法：表 as 别名，列名 as 列的别名 （也可以忽略as，用空格代替）
例如：

用导入表的方式，student表关联class表，分别取别名st，cs

select * from student as st, class as cs
where st.classID = cs.id

用连接表的方式，student关联class表，分别取别名st，cs

select * from student st
left join class cs on st.classID = cs.id

如果某个列名太长了，也可以使用别名

select welcomeToSchoolTitle title from code

在子查询的聚合函数也常常使用别名
查询每个班级的学生人数

select class.name,t.cout from class
join (select classID,count(*) cout from student group by classID) t on t.classID = class.id

11. group by分组

语法：select [列名1，列名2，聚合函数] from 表名 group by 列名1，列名2

查询出相同年龄的学生有多少人

select age,count(*) from student
group by age

12. having 筛选

语法：select * from 表名 having 条件

查询出不同年级的学生，平均年龄大于20的
例如：

select class,avg(age) from student
group by class
having avg(age) > 20

13. order by 排序

语法：select * from student order by 列名

查询以学生姓名倒序
例如：

select * from student order by name desc

查询以学生姓名倒序，年龄正序
例如：

select * from student order by name desc,age asc

14. limit 分页

语法：select * from 表名 limit 起点, 行数
例如：

查询学生表，前10条数据

select * from student limit 0,10

查询学生表，每页10条数据，查询第四页

select * from student limit 40, 10

15. exists 条件验证

将主查询的结果放到子查询进行验证，根据返回的结果，True 或 False，来觉得主查询的数据结果是否保留

语法：select * from 表名 where exists(子查询)
例如：

查询，班长的年龄比自己大的学生

select * from student as stu
where
exists(select * from student where stu.leader = student.id and student.age > stu.age )

当然，与此之外，还有 not exists 就是取反的意思

四、用户与权限

1. 创建用户

语法：create user ‘用户名’@‘主机’ identified by ‘密码’
例如：

create user 'xiaoming'@'%' indentified by '123456'

2. 修改用户密码

修改xiaoming的密码

update mysql.user set password = password('123456789')
where user = 'xiaoming' and host='%'

3. 删除用户

语法：drop user ‘用户名’@‘主机’;
例如：
删除xiaoming用户

drop user 'xiaoming'@'%';

4. 授权

语法：grant 权限 on 库.表 to ‘用户名’@‘主机’;
例如：

授权xiaoming，只能查询，修改，student表

grant select,update on school.student to 'xiaoming'@'%';

授权xiaohong，全部库，全部表，全部权限

grant all on *.* to 'xiaohong'@'%';

5. 撤销授权

语法：remove 权限 on 库.表 from ‘用户名’@‘主机’;
例如：

撤销xiaohong，class表的查询权限

remove select on school.class from 'xiaohong'@'%';

五、表连接

1. left join 左连接

table1和table2连接之后，只有table1有的数据，才会显示出来，不会显示table2全部数据到结构

连接示例1

select * from student
left join class on class.id = student.classID

2. right join 右连接

table1和table2连接之后，只有table2有的数据，才会显示出来，不会显示table1全部数据到结构

连接示例2

select * from student
right join class on class.id = student.classID

3. inner join 内连接/自连接

table1和table2连接之后，只有table1和table2相关联的数据，才会显示

连接示例3

select * from student
inner join student st on st.id = student.leader

六、约束

主键约束

create table student (
	id int primary key
) charset=utf8;

类型约束，char…int…等

create table student (
	id int
) charset=utf8;

复合主键

create table student (
	id int,
	name char(50),
	primary key(id,name)
) charset=utf8;

主键自增，必须是数值类型

create table student (
	id int primary key auto_increment
) charset=utf8;

唯一

create table student (
	id int unique
) charset=utf8;

空与非空

create table student (
	id int unique,
	name char(20) not null,
	age int null
) charset=utf8;

默认值

create table student (
	age int null default 20
) charset=utf8;

外键
语法：constraint 键名 foreign key(列名) references 表名(列名)

create table student (
	id int,
	name char(50),
	classID int,
	constraint key_classid foreign key(classID) references class(id)
) charset=utf8;

外键有几个点需要注意一下
这里student 为从表，class为主表
创建外键时，要先创建主表，再来从表这里与主表进行关联
创建外键之后，当主表的某行数据删除了，那么会提示，该数据关联了从表某些数据，无法删除
所以，下面就可以设置，当主表的数据被删除时，可以设置，从表关联的列名，是采取设置为null，还是跟着被删除，还是拒绝删除

[on delete {cascade 级联 | set null 设置为空| restrict 拒接}]
[on update {cascade 级联 | set null 设置为空| restrict 拒接}]

create table student (
	id int,
	name char(50),
	classID int,
	constraint key_classid
	foreign key(classID)
	references class(id)
	on delete set null // 主表数据删除时，从表外键会被替换成null
	on update cascade // 主表数据删除时，这条数据会跟着删除
) charset=utf8;

七、索引

对于需要经常查询的列，设置为索引，可以提高查询速度，但是会牺牲插入，更新，删除的执行效率

创建索引
语法：create index 索引名 on 表（列名）;
例如：

create index index_name on student(name);

删除索引
语法：drop index 索引名 on 表名
例如：

drop index index_name on student;

八、视图

类似与把查询语句，封装起来，要用的时候，直接调用视图名即可，不需要重新写查询语句，亦或者，当你不想把某个表全部列展示时，需要隐藏一些敏感，重要的列名，就可以使用视图

创建视图
语法：create view 视图名 as 查询语句
例如：

create view view_stu as select name from student;

删除视图
语法：drop view 视图名
例如：

drop view view_stu

对于数据量太大的表，可以采用视图的办法，分成若干个表，这样查询起来，效率更高

create view view_stu_01 as select * from student limit 0,10000;
create view view_stu_02 as select * from student limit 10001,10000;
create view view_stu_03 as select * from student limit 20001,10000;

---

更新日志

提示：记录每一次版本更新的内容，会持续更新：
20220629，exists 条件验证

20220628，插入数据，更新数据，删除数据，查询数据，高阶查询数据，聚合函数，别名，创建用户，修改用户密码，删除用户，授权，撤销授权，group by分组，having 筛选，order by排序，limit 分页，left join 左连接，right join 右连接，inner 内连接/自连接，约束，视图，索引

20220627，登录，显示所有数据库，创建数据库，删除数据库，切换数据库，显示当前数据库所有表，查看表接哦股，查看创建库的语句，设置用户可以远程访问，数值类型，日期类型，字符串类型，创建表sql，删除表sql，修改表结构sql