有关于MySQL的面试题

前言：一个互联网产品的架构通常包含接入层、逻辑处理层以及存储层，其中存储层承载着数据落地和持久化的任务，同时给逻辑处理层提供数据查询功能支持。

那么提到存储层必然就要说到数据库，对数据库的掌握程度也是软件测试工程师面试考察的必要知识点之一。

给大家准备了一些关于MySQL数据库的经典面试题，可以多参考多学习。

目录

01、用一句话介绍什么是MySQL？

02、对MySQL数据库去重的关键字是什么？

​03、MySQL多表连接有哪些方式？怎么用的？这些连接都有什么区别？

​04、MySQL数据库和Redis的区别？

​05、说一下索引的优势和劣势？

​06、什么是死锁？怎么解决？

​07、什么是视图？为什么要使用视图？

08、视图有哪些特点？使用场景有哪些？

​09、讲一下视图的优缺点？

10、MySQL的约束有哪些？

11、UNION和UNION ALL的区别？

补充： 这是一个简单的表设计：（其次展示的是有关于Mysql的SQL语句面试题）

​#问题1：1月每笔消费均大于20元的用户的总消费金额#条件：1月+大于20 sum(order_amt

#问题2：1月只吃了麻辣烫和汉堡的人数#条件：1月+（'麻辣烫' and '汉堡'）

#问题3：计算每个人bd_name的BD对应门店的销售额

​总共有3个类似于第一个的Mysql的SQL语句面试题（这个是第二个表设计）

#1写出表Department增加一条记录 和 更新一条记录的 SQL语句#增加记录值 ('12', '研发部', '张三') ； #更新 dept_id='12'的记录 ('12', '研发部', '张三新') ;

#2需要给表Department增加一列字段notes，长度为10的字符串，默认值为‘0’ , 请写出相关SQL语句

​#3查找工资大于2000元的员工记录，并按员工号id升序排列

​#4查找工资大于2000元的员工所在部门、部门编号、部门经理、员工名称

#5查找张三和李四所在部门所有人员的姓名

#6查看每个部门的部门经理和部门人数，按部门人数排序？

#7删除表Department中的所有记录

​#8删除表Department

第三个表：

​#1显示每个部门的男生人数、女生人数和总人数

#2显示每个部门的男生人数、女生人数和总人数，且该部门的额女生人数>=1,且按部门标号降序排序

---

---

 首先是有关于文字性的MySQL面试题（概念）：

01、用一句话介绍什么是MySQL？

MySQL是一个开源的关系型数据管理系统，用于存取数据、查询、更新和管理数据。

02、对MySQL数据库去重的关键字是什么？

select distinct 字段名 from 表名数据库自带的distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重复记录的所有值。其原因是 distinct只能返回它的目标字段，而无法返回其它字段。

03、MySQL多表连接有哪些方式？怎么用的？这些连接都有什么区别？

连接方式：左连接、右连接、内连接

使用方法：

左连接：select * from A LEFT JOIN B on A.id=B.id;

右连接：select * from A RIGHT JOIN B on A.id=B.id;

内连接：select * from A inner join B on a.xx=b.xx;（其中inner可以省略）

区别：

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

04、MySQL数据库和Redis的区别？

MySQL和Redis都可以存放数据，但MySQL里的数据是永久的，而Redis里的数据是缓存并有缓存机制，新的数据过来，老的数据会根据缓存机制失效。但是从Redis中读取数据比较快方便，而MySQL里的逻辑复杂，数据量大，读取数据耗时长。

05、说一下索引的优势和劣势？

优势：唯一索引可以保证数据库表中每一行数据的唯一性索引可以加快数据查询速度，减少查询时间

劣势：创建索引和维护索引要耗费时间索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间给表中的数据进行增、删、改的时候，索引也要动态的维护。

06、什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

有四个必要条件：互斥条件，请求和保持条件，环路等待条件，不剥夺条件。

解决死锁思路，一般就是切断环路，尽量避免并发形成环路。如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；如果业务处理不好可以用分布式事务锁或者使用乐观锁；死锁与索引密不可分，解决索引问题，需要合理优化索引。

07、什么是视图？为什么要使用视图？

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。

08、视图有哪些特点？使用场景有哪些？

视图特点：

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。视图是由基本表(实表)产生的表(虚表)。视图的建立和删除不影响基本表。对视图内容的更新(添加，删除和修改)直接影响基本表。当视图来自多个基本表时，不允许添加和删除数据。

视图用途：简化sql查询，提高开发效率，兼容老的表结构。

视图的常见使用场景：

重用SQL语句；简化复杂的SQL操作。使用表的组成部分而不是整个表；保护数据更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

09、讲一下视图的优缺点？

查询简单化。视图能简化用户的操作数据安全性。视图使用者能以多种角度看待同一数据，能够对机密数据提供安全保护逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

10、MySQL的约束有哪些？

NOT NULL: 约束字段的内容一定不能为NULL。

UNIQUE: 约束字段唯一性，一个表允许有多个Unique约束。PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。

FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。

CHECK: 用于控制字段的值范围。

11、UNION和UNION ALL的区别？

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

UNION的效率高于UNION ALL

 这是一个简单的表设计：（其次展示的是有关于Mysql的SQL语句面试题）

create table t_user(
    uid varchar(10) not null comment '用户ID',
    order_time timestamp null comment '下单时间',
    order_category varchar(20) not null comment '类型',
    order_amt float not null default '0.00' comment '价格',
    shop_id varchar(10) not null comment '商铺ID'
)comment '用户信息表';
 
create table t_shop(
    shop_id varchar(10) not null comment '商铺ID',
    bd_name varchar(10) not null comment '销售经理',
    bd_team varchar(10) not null comment '销售团队',
    start_time varchar(10) not null comment '开始时间',
    end_time varchar(10) not null comment '结束时间'
)comment '商铺信息表';
 
insert into t_shop(shop_id,bd_name,bd_team,start_time,end_time) values
('ZL123','小明','销售A组','2018-01-01','2018-01-14'),
('ZL123','小张','销售B组','2018-01-15','2099-12-31'),
('SM456','小张','销售B组','2016-01-01','2019-01-14'),
('HBW123','小李','销售C组','2015-01-01','2020-12-31'),
('XM456','小李','销售C组','2015-01-01','2016-01-14');
 
insert into t_user(uid,order_time,order_category,order_amt,shop_id) values
('A123','2018-01-01 12:34:00','麻辣烫',25.30,'ZL123'),
('A123','2018-01-06 12:34:00','粥',34.20,'SM456'),
('B456','2018-01-15 12:34:00','麻辣烫',25.30,'ZL123'),
('B456','2018-01-25 12:34:00','汉堡',36.30,'HBW123'),
('C789','2018-02-01 12:34:00','小龙虾',19.80,'XM456');

#问题1：1月每笔消费均大于20元的用户的总消费金额
#条件：1月+大于20 sum(order_amt

select month(now()) from daul;
select from t_user where month 
select round(sum(order_amt),2) from t_user where month(order_time)=1
and order_amt>20
group by uid;

#问题2：1月只吃了麻辣烫和汉堡的人数
#条件：1月+（'麻辣烫' and '汉堡'）

select count(uid) from t_user where month(order_time)=1 and order_category in ('麻辣烫','汉堡');
select count(t1.uid) from
(select uid from t_user where order_category='麻辣烫' and month(order_time)=1) t1,
(select uid from t_user where order_category='汉堡' and month(order_time)=1)t2
where t1.uid=t2.uid;

#问题3：计算每个人bd_name的BD对应门店的销售额

select s.shop_id,round(sum(u.order_amt),2) from t_user u,t_shop s where
u.shop_id=s.shop_id GROUP BY s.shop_id;
select shop_id,round(sum(order_amt),2)from t_user GROUP BY shop_id;

总共有3个类似于第一个的Mysql的SQL语句面试题（这个是第二个表设计）

Create Table Department(
dept_id varchar(2) not null comment '部门编号',
dept_name varchar(20) not null comment '部门名称',
dept_leader varchar(10) comment '部门经理'  
)comment '部门表';
 
Create Table Personnel(
id varchar(4) not null comment '员工号',       
name varchar(10) not null comment '姓名',      
dept_id varchar(2) not null comment '部门编号',   
age integer comment '年龄',                   
gzsj date comment '参加工作时间',                     
technical_post varchar(10) comment '职称',   
salary integer comment '薪水'                
)comment '员工表';
 
select * from Department;
select * from Personnel;
 
insert into Department(dept_id,dept_name,dept_leader) values('12','研发部','张三');
insert into Department(dept_id,dept_name,dept_leader) values('13','研发部','李四');
insert into Department(dept_id,dept_name,dept_leader) values('14','研发部','王五');
insert into Department(dept_id,dept_name,dept_leader) values('15','研发部','赵六');

#1写出表Department增加一条记录 和 更新一条记录的 SQL语句
#增加记录值 ('12', '研发部', '张三') ； 
#更新 dept_id='12'的记录 ('12', '研发部', '张三新') ;

update Department set dept_leader='张三发' where dept_leader='张三';

#2需要给表Department增加一列字段notes，长度为10的字符串，默认值为‘0’ , 请写出相关SQL语句

alter table Department add notes varchar(10) default 0;

#3查找工资大于2000元的员工记录，并按员工号id升序排列

insert into Personnel(id,name,dept_id,age,technical_post,salary) values
('1','小罗','12',23,'技术总监',12000),
('2','小张','13',18,'项目经理',10500),
('3','小胡','14',20,'产品经理',20000),
('4','小李','15',21,'执行总裁',30000);
select * from Personnel where salary>2000 order by id;

#4查找工资大于2000元的员工所在部门、部门编号、部门经理、员工名称

select d.dept_name,d.dept_id, d.dept_leader,p.name from Personnel p inner join Department d on p.dept_id=d.dept_id where p.salary>2000;

#5查找张三和李四所在部门所有人员的姓名

select name from Personnel where name ='张三' and name='李四';
select name from Personnel where dept_id in(select dept_id from Personnel where name ='张三' and name='李四');
 

#6查看每个部门的部门经理和部门人数，按部门人数排序？

select d.dept_leader,count(p.id) from Department d left outer join Personnel p on p.dept_id=d.dept_id group by d.dept_leader order by count(p.id);
 

#7删除表Department中的所有记录

delete from Department;

#8删除表Department

drop table Department;

 第三个表：

create table kingstar(
    dept_no char(4),
    person_no int,
    sex char(1),
    salary decimal(19,4)
);
 
insert into kingstar(dept_no,person_no,sex,salary) values('H001',1210,'M',1234.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H001',1211,'f',900.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H002',1212,'f',3000.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H002',1213,'M',4500.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H003',1214,'M',6394.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H003',1215,'f',7900.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H004',1216,'M',2300.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H004',1217,'M',3400.00);
insert into kingstar(dept_no,person_no,sex,salary) values('H005',1218,'M',3200.00);

#1显示每个部门的男生人数、女生人数和总人数

select dept_no,
sum(case when sex = 'M' then 1 else 0 end)男生人数,
sum(case  when sex = 'f' then 1 else 0 end)女生人数,
count(*)总人数
from kingstar GROUP BY dept_no;

#2显示每个部门的男生人数、女生人数和总人数，且该部门的额女生人数>=1,且按部门标号降序排序

select dept_no,
sum(case when sex = 'M' then 1 else 0 end)男生人数,
sum(case when sex = 'f' then 1 else 0 end)女生人数,
count(*)总人数
from kingstar GROUP BY dept_no having sum(case when sex = 'f' then 1 else 0 end)>=1 ORDER BY dept_no desc;