SQL常用实际命令

最新推荐文章于 2024-07-09 08:00:00 发布

define_us

最新推荐文章于 2024-07-09 08:00:00 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/define_us/article/details/52237553

版权

数据库专栏收录该内容

38 篇文章 0 订阅

订阅专栏

假设我有一张航班表
我想看看我的表究竟有多长呢？
select count(*) from flights;

然后我想看看这些航班究竟属于那些航空公司。也就是提取出不重复的航公公司字段
select distinct FlightCompany from flights;
最后的结果只出现一列。显示的是表中出现的所有航空公司。每个航空公司只出现一次

然后我又想算一算一共有多少航空公司
select count(distinct FLIGHTCOMPANY )from flights

然后我有想让你告诉我这些公司分别每个出现了几次呢？
（下面的语句提取了前20名）
select flightcompany,count(flightcompany) countc from flights group by flightcompany order by countc desc limit 20;

如果只显示航空公司名称，而不显示具体数目
select distinct flightcompany from flights group by flightcompany order by count(flightcompany) desc limit 20;

如果我让你不是按照航空公司分，而是按照时间点么。也就是分别计算0-1点，1-2点，2-3点的航班数据呢？
select Hour(PTD) hourc, count(PTD) countc from flights group by Hour(PTD) order by hourc asc;

如果我在让你找上面这些公司的飞机的班次都是在那起飞的，找出前五个机场并显示数量呢？
那么我们就使用嵌套查询。正好用到上面的查询结果
这样可以列出查询的前10的航空公司的所有出发地和航班数
select distinct flightcompany ,ADEPT,count(ADEPT) countc from flights where flightcompany in
(select t.flightcompany from
(select distinct flightcompany from flights group by flightcompany order by count(flightcompany) desc limit 10) as t)
group by flightcompany,ADEPT;
或者整理的更有格式一点，体现嵌套表

select * from(
    select distinct flightcompany ,ADEPT,count(ADEPT) countc from flights where flightcompany in
        (select t.flightcompanyc from
            (select distinct flightcompany  from flights group by flightcompany order by count(flightcompany) desc limit 10)
        as t)
    group by flightcompany,ADEPT)
group by flightcompany order by countc ;

但是，我们每个航空公司只要前面三个机场啊，于是乎，换一种方法嵌套一层

SELECT * FROM (
        select distinct flightcompany ,ADEPT,count(ADEPT) countc from flights where flightcompany
        in(
            select t.flightcompany 
            from(
                select distinct flightcompany  from flights group by flightcompany order by count(flightcompany) desc limit 10
            ) as t
        )
        group by flightcompany,ADEPT    
) as main
WHERE(
    SELECT COUNT(1) 
    FROM(
        select distinct flightcompany ,ADEPT,count(ADEPT) countc from flights where flightcompany
        in(
            select t.flightcompany 
            from(
                select distinct flightcompany  from flights group by flightcompany order by count(flightcompany) desc limit 10
            ) as t
        )
        group by flightcompany,ADEPT    
    ) 
    as x  WHERE 
     main.FlightCompany = FlightCompany
     AND main.countc < countc
  ) < 3;

上面的嵌套语句又长又不好理解。好在mysql允许我们自定义函数和存储过程。所谓函数，和我们日常编程时用到的差不多，接受一堆参数返回一个。而存储过程就类似于我们上面的sql语句，是用来对表格进性操作的。
我们写一个上述语句的的存储过程版本

DROP PROCEDURE IF EXISTS Proc; 
DELIMITER //
CREATE PROCEDURE Proc() 
BEGIN
#新建一个表，统计前10的航空公司，列出每一个航空公司起飞的所有机场，并列出对应的航班数目
drop table if exists test;
CREATE  TABLE    test
(
    select distinct flightcompany ,ADEPT,count(ADEPT) countc from flights where flightcompany
    in(
        select t.flightcompany 
        from(
            select distinct flightcompany  from flights group by flightcompany order by count(flightcompany) desc limit 10
        ) as t
    )
    group by flightcompany,ADEPT
);


#对上面标进行查找，每个航空公司只取前三个机场
SELECT * FROM test main
WHERE(
    SELECT COUNT(1) FROM test sub WHERE 
     main.FlightCompany = sub.FlightCompany
     AND main.countc < sub.countc
  ) < 3;

END//
DELIMITER ;