实习日记（4-14）SQL练习

最新推荐文章于 2023-02-10 18:38:27 发布

黑胡子大叔的小屋

最新推荐文章于 2023-02-10 18:38:27 发布

阅读量601

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/lzl980111/article/details/105515042

版权

SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

小记

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

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

在数据库的查询中，若查询语句中有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
评论
实习日记（4-14）SQL练习

小记匆匆忙忙，来济南这边参加工作将近一周了，昨天几位技术骨干带我们谈了一些公司涉及的技术点以及过来人的一些经验。年轻，脚踏实地，切记不要浮躁，踏踏实实做事一步一个脚印，做好笔记，以往的代码要整理好，后期方便浏览和使用今天做了一天的数据库的习题，削微整理整理在数据库的查询中，若查询语句中有group by，select后面跟的（查询的东西）要包括在group by内部多表查询其中一条思...
复制链接

扫一扫

专栏目录