实习日记(4-14)SQL练习

小记

匆匆忙忙,来济南这边参加工作将近一周了,昨天几位技术骨干带我们谈了一些公司涉及的技术点以及过来人的一些经验。
年轻,脚踏实地,切记不要浮躁,踏踏实实做事
一步一个脚印,做好笔记,以往的代码要整理好,后期方便浏览和使用

今天做了一天的数据库的习题,削微整理整理

  • 在数据库的查询中,若查询语句中有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'
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值