软件测试之MySQL数据库必知必会，面试必备！
一、前言1.1 数据库概念及分类1.2 SQL语句概念及分类1.3 MySQL数据类型
二、常用SQL语句2.1 数据库相关SQL2.2 表相关SQL2.3 修改表相关SQL2.4 操作表记录相关SQL
三、DQL（查询）详解3.1 条件查询3.2 别名、去重3.3 排序、分页查询3.4 聚合函数、时间相关函数3.5 分组与having3.6 子查询3.7 关联查询
四、约束、索引、存储过程、事务4.1 约束4.2 索引4.3 存储过程4.4 事务
五、相关面试题分组(group by) + 聚合函数筛选(having) + join on(关联查询)——第12题自连接——第19题使用同一个表两次、解题思路——第24题
一、前言
1.1 数据库概念及分类
首先，我们经常说的MySQL是一个数据库管理系统，而非数据库。数据库是组织、存储和管理数据的仓库，存储数据的容器。而数据库管理系统是操纵和管理数据库的大型软件，建立、使用和维护数据库。数据表是真正的数据存储单元，其他对象的基础。三者之间的关系为：一个数据库管理系统维护了多个数据库，一个数据库包含若干数据表。
关于数据库的分类，可能有很多种分类。一般来说，我们用到最多的就是关系型数据库和NoSQL数据库。而其中关系型数据库又是应用最为广泛的。
1.2 SQL语句概念及分类
SQL：一种结构化查询语句，用于访问和操作数据库的标准计算机语言。通常用途为操作数据库对象（表、存储过程、函数、索引），表记录的增删改查。SQL是一门弱语言，不区分大小写。通常，将SQL语句分为下面五大类。
1.3 MySQL数据类型
MySQL支持多种数据类型，大致可以分为四大类，如下图所示。关于MySQL数据类型的详细内容，请参考链接 MySQL常见的数据类型
二、常用SQL语句
2.1 数据库相关SQL
查询所有数据库show databases
;
创建数据库create database 数据库名称
;
删除数据库drop database 数据库名称
;
查询数据库详情、查看数据库的字符集show create database 数据库名称
;
创建指定字符集数据库
create database 数据库名称 character
set gbk/utf8
;
选中数据库use 数据库名称
;
2.2 表相关SQL
如果不借助工具的情况下，在命令中输入下面相关的命令，需要先使用use 数据库名选择要操作表所属的数据库。
创建表
CREATE TABLE IF NOT EXISTS 表名
(
字段名1 字段类型,
字段名2 字段类型
)ENGINE
=InnoDB DEFAULT CHARSET
=utf8
;
CREATE TABLE IF NOT EXISTS NUMBER
(
ID INT NOT NULL AUTO_INCREMENT,
USERNAME VARCHAR
(40
),
PRIMARY KEY
(ID
)
)ENGINE
=INNODB DEFAULT CHARSET
=UTF8
;
查询所有表show tables
;
查询表详情show create table 表名
;
查看表结构desc 表名
;
删除表drop table 表名
;
2.3 修改表相关SQL
关于修改表相关的SQL，除了修改表前没加alter table 表名，其他都是在该基础添加的语句。这部分的SQL一般来说在实际工作中用的不多，因为相关操作都可以借助Navicat实现。
修改表名
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字段名 原字段名 新字段名 新类型
;
2.4 操作表记录相关SQL
对于测试工程师而言，SQL语言中最常用的就是DML——数据操作语言，即为增删改查。而其中用到最多的就是DQL——数据查询语句。
插入数据（全表插入）insert into 表名 values
(字段1值,字段2值
);
insert into 表名 values
(字段1值,字段2值
),
(字段1值,字段2值
);
插入数据（指定字段）insert into 表名
(字段1,字段2
) values
(值1,值2
);
insert into 表名
(字段1,字段2
) values
(值1,值2
),
(值1,值2
);
删除数据
delete from 表名 where 字段名
=值
;
delete from 表名
;
修改数据
update 表名
set 字段名
=值1 where 字段名
=值2
;
update 表名
set 字段名
=值1
;
查询数据
select * from 表名
三、DQL（查询）详解
DQL即为数据查询语句，也是使用最多的一种SQL语句。将该部分的内容分为以下几部分：其中最为常用的是关联查询、分组、分页、排序、条件查询这几种。对于其中常用的where 、group by、having、order by、limit，其顺序为：·select * from 表A join 表B on 条件 where 条件 group by 分组字段 having 聚合函数过滤 order by 排序字段 limit ...
3.1 条件查询
判断是否为空（is null 、is not null）
SELECT * FROM book_borrow WHERE return_time is NULL
;
比较运行符（>、<、>=、<=、!=、<>）
SELECT * FROM book WHERE book_id
>2
and 和 or
SELECT * FROM book_borrow WHERE user_id
=1 AND book_id
=1
;
SELECT * FROM book_borrow WHERE user_id
=1 OR book_id
=1
;
in 在某些可选值范围内
SELECT * FROM book_borrow WHERE user_id
in (1,2,3
);
between 和 not between
SELECT * FROM book_borrow WHERE user_id BETWEEN 1 AND 2
;
模糊查询 like _代表单个字符，%代表0个或多个字符
SELECT * FROM book WHERE book_name LIKE
'三_';
SELECT * FROM book WHERE book_name LIKE
'天才%';
3.2 别名、去重
别名——使用as关键字或空格 给表名或字段别名
SELECT b.author as 作者 FROM book b WHERE b.book_name
='天才在左 疯子在右';
去重——使用distinct对查询出来的去重
select distinct employeeName from employee
;
3.3 排序、分页查询
排序——order by （ASC 升序 默认、DESC 降序 ）
select * from student order by
id desc,age
;
分页查询——limit 跳过条数A 每页数量B
select * from student order by age desc limit 2,1
;
select * from table limit
(start-1
)*pageSize,pageSize
;
3.4 聚合函数、时间相关函数
聚合函数——sum(字段名) 求和
SELECT SUM
(a.score
) FROM table_socre as a
;
聚合函数——avg(字段名) 平均值
SELECT AVG
(a.score
) FROM table_socre as a
;
聚合函数——max(字段名) 最大值
SELECT MAX
(a.score
) FROM table_socre as a
;
聚合函数——min(字段名) 最小值
SELECT MIN
(a.score
) FROM table_socre as a
;
聚合函数——count(字段名) 统计数量
SELECT COUNT
(*
) as 图书总数 FROM book
注意点,count
()函数的扩号中也可以填写字段，如果
字段的值为0，则不参与合计
时间函数——now() 当前年月日时分秒SELECT NOW
();
时间函数——current_date() 当前时间年月日SELECT CURRENT_DATE
();
3.5 分组与having
分组——group by group by的常规用法是配合聚合函数，利用分组信息进行统计。
SELECT t.t.title,MAX
(score
) FROM table_socre t GROUP BY t.title
;
having——解决聚合函数过滤问题，一般配合group by一起使用。
SELECT t.t.title,MAX
(score
) FROM table_socre t GROUP BY t.title having MAX
(score
)>20
;
3.6 子查询
写在where/having后作为查询条件的值
SELECT * FROM employees
WHERE salary
=(
SELECT MIN
(salary
)
FROM employees
);
临时表——用在from后面，当做一个新表，新表必须有名称
select cou,name from
(select count
(*
) AS cou,enabled AS name from
`user` group by enabled
) as a where cou
>0
3.7 关联查询
参考链接：Mysql中的关联查询(内连接，外连接，自连接)
内连接—— 表A inner join 表B on 条件，特点：只查询连接的表中能够有对应的记录的数据
SELECT
e.empName,
d.deptName
FROM
t_employee e
INNER JOIN t_dept d ON e.dept
= d.id
;
左外连接——表A left join 表B on 条件，特点：以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null
SELECT
e.empName,
d.deptName
FROM
t_employee e
LEFT JOIN t_dept d ON d.id
= e.dept
;
右外连接——表A right join 表B on 条件，特点：与坐外连接类似，只是基准表变了，用右表去匹配左表。所以左外连接能做到的事情，右外连接也能做到。
SELECT
e.empName,
d.deptName
FROM
t_employee e
RIGHT JOIN t_dept d ON d.id
= e.dept
;
自连接——当前表与自身连接查询
SELECT
e.empName,
b.empName
FROM
t_employee e
LEFT JOIN t_employee b ON e.bossId
= b.id
;
四、约束、索引、存储过程、事务
4.1 约束
MySQL中约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
4.2 索引
MySQL中的索引是一种高效获取数据的存储结构——B+Tree；MySQL中索引的好处在于加快查询速度，坏处就是降低了增删改的速度，增大了表的文件大小（索引文件可能比数据文件还要大）。MySQL索引实现原理可以参考：mysql索引实现原理
create index 索引名 on 表名
(字段
);
show index from 表名
;
drop index 索引名 on 表名
;
4.3 存储过程
MySQL中存储过程类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。
show procedure status
;
show create PROCEDURE 存储过程名
;
CREATE PROCEDURE 名称
()
BEGIN
.........
END
drop procedure
if exists proc_addNum
;
create procedure proc_addNum
(in x int,in y int,out
sum int
)
BEGIN
SET sum
= x + y
;
end
call proc_addNum
(2,3,@sum
);
select @sum
;
4.4 事务
参考链接：MySQL数据库事务的四大特性以及事务的隔离级别
事务：是数据库中执行SQL语句的工作单元，可以保证事务内的SQL语句要么全部成功，要么全部失败。
五、相关面试题
对于软件测试而言，在MySQL数据库相关的面试题中，除了上面MySQL数据库相关的概念，最重要的就是DQL语句的编写。关于DQL相关语句的考题，可以直接参考牛客网中SQL实战编程：牛客网SQL实战。
分组(group by) + 聚合函数筛选(having) + join on(关联查询)——第12题
SELECT
B.dept_no,
B.emp_no,
A.salary AS salary
FROM
salaries AS A
JOIN dept_emp AS B ON A.emp_no
= B.emp_no
WHERE
A.to_date
= '9999-01-01'
AND B.to_date
= '9999-01-01'
GROUP BY
B.dept_no
HAVING
A.salary
= max
( A.salary
);
自连接——第19题
SELECT
e.emp_no,
s.salary,
e.last_name,
e.first_name
FROM
employees e
JOIN salaries s ON e.emp_no
= s.emp_no
AND s.to_date
= '9999-01-01'
AND s.salary
= (
SELECT
max
( salary
)
FROM
salaries
WHERE
salary
< ( SELECT max
( salary
) FROM salaries WHERE to_date
= '9999-01-01' )
AND to_date
= '9999-01-01'
)
SELECT
e.emp_no,
s.salary,
e.last_name,
e.first_name
FROM
employees e
JOIN salaries s ON e.emp_no
= s.emp_no
AND s.to_date
= '9999-01-01'
AND s.salary
= (
SELECT
s1.salary
FROM
salaries s1
JOIN salaries s2 ON s1.salary
<= s2.salary
AND s1.to_date
= '9999-01-01'
AND s2.to_date
= '9999-01-01'
GROUP BY
s1.salary
HAVING
count
( DISTINCT s2.salary
) = 2
)
使用同一个表两次、解题思路——第24题
SELECT
de.emp_no,
sa.salary
FROM
dept_emp de,
salaries sa
WHERE
de.emp_no
= sa.emp_no
AND de.to_date
= '9999-01-01'
AND sa.to_date
= '9999-01-01'
SELECT
dm.emp_no manager_no,
sal.salary
FROM
dept_manager dm,
salaries sal
WHERE
dm.emp_no
= sal.emp_no
AND dm.to_date
= '9999-01-01'
AND sal.to_date
= '9999-01-01'
SELECT
de.emp_no,
dm.emp_no manager_no,
sa.salary emp_salary,
sal.salary manager_salary
FROM
dept_emp de,
salaries sa,
dept_manager dm,
salaries sal
WHERE
de.emp_no
= sa.emp_no
AND dm.emp_no
= sal.emp_no
AND de.dept_no
= dm.dept_no
AND de.to_date
= '9999-01-01'
AND sa.to_date
= '9999-01-01'
AND dm.to_date
= '9999-01-01'
AND sal.to_date
= '9999-01-01'
AND sa.salary
> sal.salary
对于测试工程师而言，在面试中，应该不会遇到特别难的SQL题目。且工作中，对于SQL要求不会特别高，重点在于根据需求梳理好思路，然后编写相关的SQL，对于其SQL的效率不回特别追求。
