MySQL 排序、分页查询、聚合查询

SELECT id, name, gender, score FROM students ORDER BY score;

ORDER BY <列名> DESC  # 降序排列

SELECT id, name, gender, score FROM students ORDER BY score DESC;

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
# 先根据分数降序，然后根据性别

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; # 每页3条记录，从第0条开始

SELECT COUNT(*) FROM students; # 返回一个二维表 ，一行一列

SELECT COUNT(*) num FROM students;

SELECT AVG(score) average FROM students WHERE gender = 'M';

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

SELECT class_id, gender, COUNT(*) num FROM students 
GROUP BY class_id, gender;

每个班级的平均分
SELECT class_id, AVG(score) avgsc FROM students
GROUP BY class_id ORDER BY avgsc DESC;

每个班级男生和女生的平均分
SELECT class_id, gender, AVG(score) avgsc FROM students
GROUP BY class_id, gender ORDER BY avgsc DESC;

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

# Write your MySQL query statement below
SELECT 
(
    SELECT DISTINCT Salary
    FROM Employee ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
)
SecondHighestSalary

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set n = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary
      FROM Employee ORDER BY Salary DESC
      LIMIT 1 OFFSET n
  );
END

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values ('1', 'a@b.com')
insert into Person (Id, Email) values ('2', 'c@d.com')
insert into Person (Id, Email) values ('3', 'a@b.com')

示例：
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
说明：所有电子邮箱都是小写字母。

# Write your MySQL query statement below
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)>1;

例如，下表 cinema:
+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

对于上面的例子，则正确的输出是为：
+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

# Write your MySQL query statement below
SELECT * FROM cinema 
WHERE description != 'boring' 
        AND id%2 = 1 
ORDER BY rating DESC

Customers 表：
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

# Write your MySQL query statement below
SELECT C.Name Customers
FROM Customers C
WHERE C.Id NOT IN 
(
    SELECT CustomerId FROM Orders
)

# Write your MySQL query statement below
SELECT C.Name Customers
FROM Customers C
LEFT OUTER JOIN Orders O
ON C.Id = O.CustomerId
WHERE O.CustomerId is null

例如,表:
+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+
应该输出:
+---------+
| class   |
+---------+
| Math    |
+---------+
Note:
学生在每个课中不应被重复计算。（有课程中，重复出现2次A，只算一次）

# Write your MySQL query statement below
SELECT class FROM courses GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;