54,(简管题,9.0 分
已知通信录数据库有以下3张表:
lxrenb(联系人表)其字段为(联系人ID、姓名、联系电话、家庭地址)
thjlb(通话记录表)其字段为(通话记录ID、联系人ID、通话开始时间、通知结束时间、通话类别、通话状态),
dxjlb(短信记录表)其字段为(短信记录ID、联系人ID、接发时间、短信内容、短信类别)。
对于通信录数据库,请写出正确的SQL语句:
1)在联系人表中依据姓名建立索引,索引名称为:index 姓名。
CREATE INDEX index_姓名 ON lxrenb (姓名);
2)依据联系人表和短信记录表建立视图:vlxren_dxjlb(联系人编号、姓名、短信内容、短信类别)。
CREATE VIEW vlxren_dxjlb AS
SELECT
lxrenb.联系人ID ,
lxrenb.姓名,
dxjlb.短信内容,
dxjlb.短信类别
FROM
lxrenb
JOIN
dxjlb ON lxrenb.联系人ID = dxjlb.联系人ID;
3)统计所有用户本月通话时间(分钟),输出用户姓名,通话时间。
SELECT
lxrenb.姓名,
COALESCE(SUM(TIME_TO_SEC(TIMEDIFF(thjlb.通知结束时间, thjlb.通话开始时间))) / 60, 0) AS 通话时间
FROM
lxrenb
LEFT JOIN
thjlb ON lxrenb.联系人ID = thjlb.联系人ID
WHERE
YEAR(thjlb.通话开始时间) = YEAR(CURDATE())
AND MONTH(thjlb.通话开始时间) = MONTH(CURDATE())
GROUP BY
lxrenb.联系人ID,lxrenb.姓名
ORDER BY
通话时间 DESC;