小记
匆匆忙忙,来济南这边参加工作将近一周了,昨天几位技术骨干带我们谈了一些公司涉及的技术点以及过来人的一些经验。
年轻,脚踏实地,切记不要浮躁,踏踏实实做事
一步一个脚印,做好笔记,以往的代码要整理好,后期方便浏览和使用
今天做了一天的数据库的习题,削微整理整理
- 在数据库的查询中,若查询语句中有group by,select后面跟的(查询的东西)要包括在group by内部
比如:
// group by包含了names 和status, select中直接查找的就只能有name和status
SELECT
a.NAMES AS "主机名称",
C.STATUS AS "报警状态" ,
sum(C.NUM) AS sumnum
FROM
MONITORINSTANCE1 a
INNER JOIN MONITORITEM1 b
ON a.ID=b.INSTANCEID
INNER JOIN LOGSTATUS_DAY1 C
ON b.id=C.MONITORITEMID
WHERE
C.DATES='2019-07-02'
GROUP BY
a.NAMES,
c.STATUS
ORDER BY
C.STATUS ASC,sumnum DESC
- 多表查询其中一条思路:内连接套内连接
题目一
/* 建表语句 */
CREATE TABLE LZL (
ID VARCHAR(30) NOT NULL PRIMARY KEY,
MONITORITEMID VARCHAR(30) ,
STATUS VARCHAR(1) ,
DATES VARCHAR(10) ,
TIMES VARCHAR(5)
)
/* 字段修改语句 */
ALTER TABLE ZRZ ALTER TIMES SET DATA TYPE VARCHAR(8)
/* 插入语句 */
INSERT INTO LZL(ID,MONITORITEMID,STATUS,DATES,TIMES)
SELECT ID,MONITORITEMID,STATUS,DATES,TIMES
FROM
LOGSTATUSALARMHISTORY
WHERE DATES BETWEEN DATE '2020-04-01' AND DATE '2020-04-30'
题目二
查询主机IP为“192.168.32.25”,日期为2019-07-01,明细报警信息,按照日期时间倒序排列,写出查询语句即可
/* 查询主机IP为“192.168.32.25”,日期为2019-07-01,明细报警信息,按照日期时间倒序排列,写出查询语句即可 */
select *
from
(LOGSTATUSALARMHISTORY a inner join MONITORITEM1 b on a.MONITORITEMID=b.id)
inner join
MONITORINSTANCE1 c
on b.INSTANCEID=c.id
where a.DATES='2020-04-01' and c.IP='192.168.32.25'
order by a.TIMES
//修订
SELECT
*
FROM
LOGSTATUSALARMHISTORY a
LEFT JOIN MONITORITEM1 b
ON (a.MONITORITEMID=b.id)
LEFT JOIN MONITORINSTANCE1 C
ON (b.INSTANCEID=C.id)
WHERE
a.DATES='2020-04-01' AND
C.IP='192.168.32.25'
ORDER BY
a.TIMES
//答案
SELECT A.ID,A.MONITORITEMID,A.STATUS,A.LASTSTATUS,A.DATES,A.TIMES,A.ALARMCONTENT
FROM
LOGSTATUSALARMHISTORY A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
WHERE
A.DATES='2019-07-01' AND C.IP='192.168.32.25'
ORDER BY
A.TIMES DESC
题目三
查询属于业务系统“网银web2”,日期为2019-07-01,报警状态为3的明细信息,写出查询语句即可
/* 查询属于业务系统“网银web2”,日期为2019-07-01,报警状态为3的明细信息,写出查询语句即可 */
select *
from
(MONITORBUSSINESSSYSTEM1 a inner join MONITORINSTANCE1 b on a.id=b.BUSSINESSSYSTEMID)
inner join
(MONITORITEM1 c inner join LOGSTATUS_DAY1 d on c.id=d.MONITORITEMID)
on b.id=c.INSTANCEID
where a.NAMES='业务系统2' and d.DATES='2019-01-03'
//修订
SELECT
*
FROM
MONITORBUSSINESSSYSTEM1 a
LEFT JOIN MONITORINSTANCE1 b
ON (a.id=b.BUSSINESSSYSTEMID)
LEFT JOIN MONITORITEM1 C
ON (b.ID=C.INSTANCEID)
LEFT JOIN LOGSTATUS_DAY1 d
ON (C.id=d.MONITORITEMID)
WHERE
a.NAMES='网银web2' AND
d.DATES='2019-01-03'
//答案
SELECT
A.ID,
A.MONITORITEMID,
A.STATUS,
A.LASTSTATUS,
A.DATES,
A.TIMES,
A.ALARMCONTENT
FROM
LOGSTATUSALARMHISTORY A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
JOIN MONITORBUSSINESSSYSTEM1 D
ON C.BUSSINESSSYSTEMID=D.ID
WHERE
A.DATES='2019-07-01'
AND A.STATUS='3'
AND D.NAMES='网银web2'
题目四
查询所有主机的2019-07-02的报警统计信息,按报警状态(升序),报警数量(降序)排列,显示字段:主机名称,报警状态,报警数量写出查询语句,截图查询结果
/* 4.查询所有主机的2019-07-02的报警统计信息,按报警状态(升序),报警数量(降序)排列,显示字段:主机名称,报警状态,报警数量写出查询语句,截图查询结果 */
select a.NAMES,c.STATUS ,c.NUM
from
MONITORINSTANCE1 a
inner join
(MONITORITEM1 b inner join LOGSTATUS_DAY1 c on b.id=c.MONITORITEMID )
on a.ID=b.INSTANCEID
where c.DATES='2019-07-02'
order by c.STATUS ASC,c.NUM DESC
//修订
SELECT
a.NAMES AS "主机名称",
C.STATUS AS "报警状态" ,
C.NUM AS "报警数量"
FROM
MONITORINSTANCE1 a
INNER JOIN MONITORITEM1 b
ON a.ID=b.INSTANCEID
INNER JOIN LOGSTATUS_DAY1 C
ON b.id=C.MONITORITEMID
WHERE
C.DATES='2019-07-02'
ORDER BY
C.STATUS ASC,
C.NUM DESC
//答案
SELECT
C.NAMES,
A.STATUS,
SUM(A.NUM) AS SUMNUM
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID = C.ID
WHERE
A.DATES='2019-07-02'
GROUP BY
C.NAMES,
A.STATUS
ORDER BY
A.STATUS,
SUMNUM DESC
分析:按照名称和状态将内联表分组,获得分组后的num的和是用sum()
题目五
查询各业务系统2018年每月所有报警数量排名(月份升序、报警数量倒序)
显示字段:业务系统名称、月份、报警数量
写出查询语句,截图查询结果
/* 5.查询各业务系统2018年每月所有报警数量排名(月份升序、报警数量倒序)
显示字段:业务系统名称、月份、报警数量
写出查询语句,截图查询结果。 */
select a.NAMES,month(d.DATES),count(a.names)
from
(MONITORBUSSINESSSYSTEM1 a inner join MONITORINSTANCE1 b on a.ID=b.BUSSINESSSYSTEMID)
inner join
(MONITORITEM1 c inner join LOGSTATUS_DAY1 d on c.ID=d.MONITORITEMID)
on b.ID=c.INSTANCEID
group by a.names,d.dates
//修订
SELECT
a.NAMES as "业务系统名称",
MONTH(d.DATES) as "月份",
COUNT(a.NAMES) as "报警数量"
FROM
MONITORBUSSINESSSYSTEM1 a
LEFT JOIN MONITORINSTANCE1 b
ON a.ID=b.BUSSINESSSYSTEMID
LEFT JOIN MONITORITEM1 C
LEFT JOIN LOGSTATUS_DAY1 d
ON C.ID=d.MONITORITEMID
ON b.ID=C.INSTANCEID
GROUP BY
a.NAMES,
d.dates
//答案
SELECT
D.NAMES,
MONTH(A.DATES) AS MONTHS,
SUM(A.NUM) AS SUMNUM
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
JOIN MONITORBUSSINESSSYSTEM1 D
ON C.BUSSINESSSYSTEMID=D.ID
WHERE
YEAR(A.DATES)=2018
GROUP BY
D.NAMES,
MONTH(A.DATES)
ORDER BY MONTHS ASC, SUMNUM DESC
题目六
查询主机类型为WIN的报警数量统计,显示字段:
主机名称、预警数量、报警数量、数据获取异常数量、网络中断数量
写出查询语句,截图查询结果
/* 6.查询主机类型为WIN的报警数量统计,显示字段:
主机名称、预警数量、报警数量、数据获取异常数量、网络中断数量
写出查询语句,截图查询结果。*/
select status,count(status),b.NAMES
from
(INSTANCETYPE a inner join MONITORINSTANCE1 b on a.ID=b.INSTANCETYPEID)
inner join
(LOGSTATUSALARMHISTORY c inner join MONITORITEM1 d on c.MONITORITEMID=d.id)
on d.INSTANCEID=b.id
where a.id='WIN' group by STATUS,b.NAMES
//修订
SELECT
C.NAMES,
SUM(
CASE A.STATUS
WHEN '2'
THEN 1
ELSE 0
END) AS "预警",
SUM(
CASE A.STATUS
WHEN '3'
THEN 1
ELSE 0
END) AS "报警",
SUM(
CASE A.STATUS
WHEN '4'
THEN 1
ELSE 0
END) AS "数据获取异常",
SUM(
CASE A.STATUS
WHEN '5'
THEN 1
ELSE 0
END) AS "网络中断"
FROM
LOGSTATUSALARMHISTORY A
LEFT JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
LEFT JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
WHERE
C.INSTANCETYPEID='WIN'
GROUP BY
C.NAMES
//答案
SELECT
C.NAMES,
SUM(CASE A.STATUS WHEN '2' THEN A.NUM ELSE 0 END) AS SUMNUM2,
SUM(CASE A.STATUS WHEN '3' THEN A.NUM ELSE 0 END) AS SUMNUM3,
SUM(CASE A.STATUS WHEN '4' THEN A.NUM ELSE 0 END) AS SUMNUM4,
SUM(CASE A.STATUS WHEN '5' THEN A.NUM ELSE 0 END) AS SUMNUM5
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
WHERE
C.INSTANCETYPEID='WIN'
GROUP BY
C.NAMES
题目七
统计2019年7月,各个主机类型的所有报警数量,如果没有则显示数量为0,写出查询语句,截图查询结果
/* 统计2019年7月,各个主机类型的所有报警数量,如果没有则显示数量为0,写出查询语句,截图查询结果 */
select count(a.id) ,a.id
from
(INSTANCETYPE a inner join MONITORINSTANCE1 b on a.ID=b.INSTANCETYPEID)
inner join
(LOGSTATUSALARMHISTORY c inner join MONITORITEM1 d on c.MONITORITEMID=d.id)
on d.INSTANCEID=b.id
where c.DATES BETWEEN DATE '2020-04-01' AND DATE '2020-04-30'
group by a.ID
//另一个同事的版本
SELECT D.NAMES, COALESCE(COUNT(A.STATUS),0)
FROM
LOGSTATUS_DAY1 A JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID
JOIN INSTANCETYPE D ON C.INSTANCETYPEID=D.ID
WHERE A.DATES BETWEEN DATE '2019-07-01' AND DATE '2019-07-31'
GROUP BY D.NAMES
//修订
select nvl(count(a.id),0) as "报警数量" ,a.names as "主机类型"
from
INSTANCETYPE a inner join MONITORINSTANCE1 b on a.ID=b.INSTANCETYPEID
left join
MONITORITEM1 d
on d.INSTANCEID=b.id
left join
LOGSTATUS_DAY1 c
on c.MONITORITEMID=d.id
where c.DATES BETWEEN DATE '2019-07-01' AND DATE '2019-07-31'
group by a.names
//答案
SELECT
D.NAMES,
COALESCE(SUNNUM,0) AS SUMNUM
FROM
INSTANCETYPE D
LEFT JOIN (
SELECT
C.INSTANCETYPEID,
SUM(A.NUM) AS SUNNUM
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
WHERE
SUBSTR(A.DATES,1,7)='2018-07'
GROUP BY
C.INSTANCETYPEID
)
E
ON D.ID=E.INSTANCETYPEID
题目八
统计所有报警数量最多的主机前10名,写出查询语句,截图查询结果
/* 8.统计所有报警数量最多的主机前10名,写出查询语句,截图查询结果。 */
select count(a.id) ,a.id
from
(MONITORINSTANCE1 a inner join MONITORITEM1 b on a.ID=b.INSTANCEID)
inner join LOGSTATUSALARMHISTORY c on b.ID=c.MONITORITEMID
group by a.id
order by count(a.id) Desc
fetch first 10 rows only
//版本二
SELECT * FROM(
SELECT C.NAMES, COUNT(A.STATUS) AS SUM
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C ON B.INSTANCEID=C.ID
GROUP BY C.NAMES
)
TOP ORDER BY SUM DESC
FETCH FIRST 10 ROWS ONLY
//修订
SELECT
sum(c.NUM) AS "总数量" ,
a.id AS "ID"
FROM
MONITORINSTANCE1 a
INNER JOIN MONITORITEM1 b
ON a.ID=b.INSTANCEID
INNER JOIN LOGSTATUS_DAY1 C
ON b.ID=C.MONITORITEMID
GROUP BY
a.id
ORDER BY
sum(c.NUM)
DESC FETCH FIRST 10 ROWS ONLY
//答案
SELECT
C.NAMES,
SUM(A.NUM) AS SUMNUM
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
GROUP BY
C.NAMES
ORDER BY SUMNUM DESC
FETCH FIRST 10 ROWS ONLY
题目九
按日期统计所有主机类型为LINUX的报警数量,按照报警数量倒序排名,显示字段:日期,数量,写出查询语句,截图查询结果
/* 9.按日期统计所有主机类型为LINUX的报警数量,按照报警数量倒序排名,显示字段:日期,数量,写出查询语句,截图查询结果。 */
select dates,count(dates)
from
(INSTANCETYPE a inner join MONITORINSTANCE1 b on a.ID=b.INSTANCETYPEID)
inner join
(MONITORITEM1 c inner join LOGSTATUS_DAY1 d on c.id=d.MONITORITEMID)
on b.id=c.INSTANCEID
where a.id='LINUX'
group by dates
order by count(dates) desc
//修订
SELECT
dates as "ÈÕÆÚ",sum(d.NUM) as "±¨¾¯×ÜÊý"
FROM
INSTANCETYPE a INNER JOIN MONITORINSTANCE1 b
ON a.ID=b.INSTANCETYPEID INNER JOIN MONITORITEM1 C
ON b.id=C.INSTANCEID INNER JOIN LOGSTATUS_DAY1 d
ON C.id=d.MONITORITEMID
WHERE
a.id='LINUX'
GROUP BY
dates
ORDER BY
sum(d.NUM) DESC
//答案
SELECT
A.DATES,
SUM(A.NUM) AS SUMNUM
FROM
LOGSTATUS_DAY1 A
JOIN MONITORITEM1 B
ON A.MONITORITEMID=B.ID
JOIN MONITORINSTANCE1 C
ON B.INSTANCEID=C.ID
WHERE C.INSTANCETYPEID='LINUX'
GROUP BY
A.DATES
ORDER BY SUMNUM DESC
题目十
导出报警明细表中2019年7月所有数据,明细数据不包含ID字段,写出导出语句即可
/* 10.导出报警明细表中2019年7月所有数据,明细数据不包含ID字段,写出导出语句即可。 */
select MONITORITEMID,STATUS,LASTSTATUS,DATES,TIMES,ALARMCONTENT
from LOGSTATUSALARMHISTORY where date(DATES)>'2020-04-01' and date(DATES)<'2020-05-01'