【MySQL】分组统计Aggregate

学之初学之时 2021-04-30

492

课程来源：

Udemy - The Ultimate MySQL Bootcamp:

Go from SQL Beginner to Expert

分享的大部分数据来源于课程，总结为本人原创

包含视频/约50分钟

撰文/Iris帆

排版/Iris帆

全文/850字（包括代码）

序言

到目前为止，我们学习了用关键词CREATE和INSERT INTO的配合来创建简单的表格，也学习了结合其它关键词WHERE、AS、DISTINCT、ORDER BY、LIMIT和LIKE来SELECT我们想要读取的内容。不仅如此，我们还学习了用一些常用字符串函数，比如CONCAT()、SUBSTRING()、REPLACE()、REVERSE()和UPPER()/LOWER()，以及关键词UPDATE...SET和DELETE来对表格内容进行删改。

今天，我将要分享的知识点是——SQL统计。

本期分享重点

5大常用数值函数：COUNT()、MIN()、MAX()、SUM()、AVG(）
5大常用数值函数和关键词GROUP BY结合运用
运用今天的知识解决上期牛客网实战题

下面为视频中使用到的代码：

-- -- 数值函数COUNT() -- -- 数数


SELECT COUNT(*) FROM books;
 
SELECT COUNT(author_fname) FROM books;
 
SELECT COUNT(DISTINCT author_fname) FROM books;
 
SELECT COUNT(DISTINCT author_lname, author_fname) FROM books;
 
SELECT title FROM books WHERE title LIKE '%the%';
 
SELECT COUNT(*) FROM books WHERE title LIKE '%the%';


-- -- 关键词 GROUP BY -- 分组


SELECT title, author_lname FROM books;
 
SELECT title, author_lname FROM books
GROUP BY author_lname;
 
SELECT author_lname, COUNT(*) 
FROM books GROUP BY author_lname;
 
SELECT title, author_fname, author_lname FROM books;
 
SELECT title, author_fname, author_lname FROM books GROUP BY author_lname;
 
SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname;
 
SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname, author_fname;
 
SELECT released_year FROM books;
 
SELECT released_year, COUNT(*) FROM books GROUP BY released_year;
 
SELECT CONCAT(
    'In ', 
    released_year, 
    ' ', 
    COUNT(*), 
    ' book(s) released') AS year 
    FROM books 
    GROUP BY released_year;


-- -- 数值函数MIN()和MAX()


SELECT MIN(released_year) FROM books;
 
SELECT MIN(pages) FROM books;
 
SELECT MAX(pages) FROM books;
 
SELECT MAX(released_year) FROM books;


SELECT * FROM books 
WHERE pages = (SELECT Min(pages) 
                FROM books); 
 
SELECT title, pages FROM books 
WHERE pages = (SELECT Max(pages) 
                FROM books); 
 
SELECT title, pages FROM books 
WHERE pages = (SELECT Min(pages) 
                FROM books); 


SELECT author_fname, 
       author_lname, 
       Min(released_year) 
FROM   books 
GROUP  BY author_lname, 
          author_fname;
-- 每个作家最早发行的书籍
 
SELECT
  author_fname,
  author_lname,
  Max(pages)
FROM books
GROUP BY author_lname,
         author_fname;
-- 每个作家拥有页数最多/最长的书籍


SELECT
  CONCAT(author_fname, ' ', author_lname) AS author,
  MAX(pages) AS 'longest book'
FROM books
GROUP BY author_lname,
         author_fname;


-- -- 数值函数SUM()和AVG()


SELECT SUM(pages)
FROM books;
 
SELECT author_fname,
       author_lname,
       Sum(pages)
FROM books
GROUP BY
    author_lname,
    author_fname;
 
SELECT AVG(pages) 
FROM books;
 
SELECT AVG(stock_quantity) 
FROM books 
GROUP BY released_year;
 
SELECT author_fname, author_lname, AVG(pages) FROM books
GROUP BY author_lname, author_fname;

扫码关注我吧

学之初学之时

我努力前行，想在停下来的时候，把人生分享给您

mysql

文章转载自学之初学之时，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【MySQL】分组统计Aggregate

评论