第一部分 MySQL基础
1.
创建数据源
create table if not exists stu (学号 int not null,
姓名 varchar(5),
课程名称 varchar(5),
分数 int);
truncate table stu;
insert into stu values (1,'joy','历史',69),
(2,'可乐','数学',89),
(3,'joy','生物',80);
update stu set 分数=分数+3 where 课程名称='历史' and姓名='joy';
select * from stu;
2.
选D。
desc 表名。
3.
unique唯一索引的作用是保证各行在该索引上的值都不得重复。
添加唯一性索引的数据列可以为空，但是只要存在数据值，就必须是唯一的。
4.
创建数据源
create table if not exists team(name varchar(5));
truncate table team;
insert into team values ('a'),('b'),('c'),('d');
select * from team as a
inner join team as b
on a.name<b.name
第二部分 MySQL进阶面试题
题目1
现有表1-房源表house_info和表2-shop_info,字段如下：
表1-房源表：house_info
房源id house_id
维护人id hold_ucid
委托类型 del_type
维护门店编码 hold_shop_code
表2-门店信息表:shop_info
门店编码 shop_code
区域 holdarea
求出每个区域中有人维护切委托类型为二手和新房的房源量，输出格式为：
创建数据源
create table if not exists house_info(house_id int,
hold_ucid int,
del_type varchar(10),
hold_shop_code int);
truncate table house_info;
insert into house_info values (1,1,'新房',100),(2,1,'二手房',200),(3,2,'新房',300),(4,3,'二手房',400),(5,4,'二手房',100),(6,8,'新房',200),(7,4,'二手房',200),(8,2,'二手房',300);
create table if not exists shop_info(shop_code int,
holdarea varchar(10));
truncate table shop_info;
insert into shop_info values (100,'南山大部'),
(200,'福田大部'),
(300,'宝安大部'),
(400,'福田大部');
代码：
select
b.holdarea as '区域',
sum(case when a.del_type='二手房' then 1 else 0 end) as '二手房房源量',
sum(case when a.del_type='新房' then 1 else 0 end) as '新房房源量'
from house_info as a
inner join shop_info as b
on a.hold_shop_code=b.shop_code
group by b.holdarea
题目2
现有表1-房源表house_info
表1-房源表：house_info
房源id house_id
维护人id hold_ucid
委托类型 del_type
维护门店编码 hold_shop_code
表3-电话信息表telephone_info
房源id house_id
电话拨打日期 create_date
拨通状态 call_status
求出近30天内未成功接通(接通状态为'接通失败')客户电话的所有房源（<font color='red'> 提示：最近一次接通成功记录在30天以外，或者从未有过接通成功记录的房源</font>），及该房源最新一次成功接通电话的日期，输出格式：
创建数据源
create table if not exists house_info(house_id int,
hold_ucid int,
del_type varchar(10),
hold_shop_code int);
truncate table house_info;
insert into house_info values (1,1,'新房',100),(2,1,'二手房',200),(3,2,'新房',300),(4,3,'二手房',400);
create table if not exists telephone_info(house_id int,
create_date varchar(20),
call_status int);
truncate table telephone_info;
insert into telephone_info values (1,'2020-01-01',0),
(1,'2020-01-05',0),
(1,'2020-05-05',1),
(1,'2020-06-10',0),
(1,'2020-06-15',1),
(2,'2020-01-01',0),
(2,'2020-01-05',0),
(2,'2020-05-05',0),
(2,'2020-06-10',0),
(2,'2020-06-15',0),
(3,'2020-01-01',0),
(3,'2020-01-05',0),
(3,'2020-05-05',1),
(3,'2020-06-10',0),
(3,'2020-06-15',0),
(4,'2020-01-01',0),
(4,'2020-01-05',0),
(4,'2020-05-05',1),
(4,'2020-06-10',0),
(4,'2020-06-15',0);
代码
select a.house_id as '房源id',max(b.create_date) as '电话拨打日期' from house_info as a
inner join telephone_info as b
on a.house_id=b.house_id
where b.call_status=1
group by a.house_id
having datediff(curdate(),max(b.create_date))>30
union
select c.house_id as '房源id',null as '电话拨打日期' from house_info as c
left join telephone_info as d
on c.house_id=d.house_id
group by c.house_id
having sum(d.call_status)=0 or sum(d.call_status) is null
