edisoncgh的MySQL练习记录

开一篇文来记录我直接从题目开始硬上MySQL的历程。

LC627：变更性别

这题学到的知识点有：

SQL三目运算: if(a==b,true,false)
SQL update语句，“UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;”

UPDATE salary SET sex = IF(sex='m', 'f', 'm');

LC176：第二高的薪水

传送门

这题学到的知识点有：

用as关键字为select到的值命名
用max()方法从某一列找出最大的那个数

# Write your MySQL query statement below
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)

LC177：第N高的薪水

传送门

这题学到的知识点有：

SQL中的函数命名格式
用set关键字给变量赋值
用distinct关键字在有多个符合条件的值得情况下获取单个值
用order by xxx desc关键字来做数据的升序排序
用limit x offset y来表示“自y之后取x个值出来”，此语句也等价于"limit y, x".

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

LC182：查找重复的电子邮箱

传送门

解法一

主要思路是检索email属性相等但id属性不相等的记录。

学到的知识点有：

利用join关键字来链接两个表，以获取a表中x字段在b表中对应的y字段值，在本题中的应用就是：获取表a中email在表b中对应相等且id不等的email字段值。

# Write your MySQL query statement below
SELECT DISTINCT (a.Email) FROM Person a
JOIN Person b WHERE a.Email = b.Email AND a.Id != b.Id

解法二

主要思路是直接找到那些email出现次数大于1的记录。

学到的知识点有：

用group by语句来根据一个或多个属性对结果集进行分组。
用having语句来筛选分组后的记录。它和where的区别是where语句会在聚合前先筛选记录，即where会先于group by和having生效；而having会在分组后进行筛选。假设某个筛选条件为SUM(x)>100000，where语句无法正常执行，因为本质上不存在单个的x满足>100000，但having可以做到。
用count()方法来给记录计数。

SELECT email 
FROM Person 
GROUP BY email HAVING COUNT(email) > 1

值得注意的是，在实际提交中，解法1远远优于解法2，这说明group by语句本身执行起来是相当浪费时间的，应该谨慎使用。

LC595：大的国家

传送门

直接根据题意筛。

学到的知识点有：

用``来转义SQL的保留关键字。

# Write your MySQL query statement below
SELECT `name`, population, `area`
FROM World
WHERE `area` > 3000000 OR population > 25000000

LC620：有趣的电影

传送门

学到的知识点有：

用mod()方法来进行取模计算。(SQL奇偶判断)

SELECT id, movie, description, rating
FROM cinema
WHERE description != 'boring' AND MOD(id, 2) = 1
ORDER BY rating DESC

LC181：超过经理收入的员工

传送门

没有学到新知识点，用join来根据managerID检索就行了。

# Write your MySQL query statement below
select a.`Name` as Employee
from Employee a
join Employee b where a.ManagerId = b.Id and b.Salary < a.Salary

LC183：从不订购的客户

传送门

学到的知识点：

用left join语句来以左表为基准在右表中匹配，匹配不到的属性用NULL占位。
on和where关键字的区分与使用，详见MYSQL on和where的区别。笼统地说，on是边操作边筛选，而where是操作完了再筛选。

# Write your MySQL query statement below
select a.`Name` as `Customers`
from Customers a
left join Orders b
on b.CustomerId = a.Id 
where b.CustomerId is null

LC196：删除重复的电子邮箱

传送门

学到的知识点：

delete语句作用的对象应该是若干条记录，而非单个属性。

# Write your MySQL query statement below
delete p1
from Person p1, Person p2
where p1.Email = p2.Email and p1.id > p2.id

LC1179：重新格式化部门表

传送门

主要思路是统计每个id下12个月份分别对应的revenue值之和，然后把它们按id进行分组。

学到的知识点：

用sum()方法来进行求和。
使用聚合函数(MAX /MIN / SUM /AVG / COUNT)结合group by语句进行更优的分组。

# Write your MySQL query statement below
select id,
    sum(if(`month` = 'Jan', revenue, null)) as Jan_Revenue,
    sum(if(`month` = 'Feb', revenue, null)) as Feb_Revenue,
    sum(if(`month` = 'Mar', revenue, null)) as Mar_Revenue,
    sum(if(`month` = 'Apr', revenue, null)) as Apr_Revenue,
    sum(if(`month` = 'May', revenue, null)) as May_Revenue,
    sum(if(`month` = 'Jun', revenue, null)) as Jun_Revenue,
    sum(if(`month` = 'Jul', revenue, null)) as Jul_Revenue,
    sum(if(`month` = 'Aug', revenue, null)) as Aug_Revenue,
    sum(if(`month` = 'Sep', revenue, null)) as Sep_Revenue,
    sum(if(`month` = 'Oct', revenue, null)) as Oct_Revenue,
    sum(if(`month` = 'Nov', revenue, null)) as Nov_Revenue,
    sum(if(`month` = 'Dec', revenue, null)) as Dec_Revenue
from Department
group by id

LC197：上升的温度

传送门

学到的知识点：

使用datediff()方法来计算两个日期之间相差的天数，详见SQL日期(Dates).

# Write your MySQL query statement below
select a.`id`
from Weather a
left join Weather b
on datediff(a.recordDate, b.recordDate) = 1 
where a.Temperature > b.Temperature

LC596：超过5名学生的课

传送门

学到的知识点：

在count()方法内嵌套distinct语句可以直接做到不重复计数

# Write your MySQL query statement below
select `class`
from courses
group by class
having count(distinct(student)) >= 5

LC180：连续出现的数字

传送门

# Write your MySQL query statement below
select distinct(l1.Num) as ConsecutiveNums
from `Logs` l1, `Logs` l2, `Logs` l3
where l1.Num = l2.Num and l2.Num = l3.Num and l2.id = l1.id - 1 and l2.id - 1 = l3.id

LC178：分数排名

传送门

# Write your MySQL query statement below
select s1.Score, count(distinct(s2.Score)) as `Rank`
from Scores s1, Scores s2
where s1.Score  <= s2.Score
group by s1.Id
order by `Rank`

LC184：部门工资最高的员工

传送门

用到的知识点：

in关键字前可以用多个元素进行匹配

# Write your MySQL query statement below
select 
    d.`Name` as Department, 
    e.`Name` as Employee,
    e.Salary
from 
    Employee e,
    Department d
where
    e.DepartmentId = d.Id and
    (e.Salary, e.DepartmentId) in (
        select max(Salary), DepartmentId
        from Employee group by DepartmentId
    )

LC1757：可回收且低脂的产品

传送门

关键词：多条件查询

# Write your MySQL query statement below
select product_id from Products
where low_fats = 'Y' and recyclable = 'Y'

LC1683：无效的推文

传送门

关键词：基于length()方法有关属性长度的查询

# Write your MySQL query statement below
select tweet_id from Tweets
where length(content) > 15

LC1068：产品销售分析I

传送门

关键词：内连接(inner join)的使用。

# Write your MySQL query statement below
select p.product_name, s.`year`, s.price
from Product p
join Sales s
on p.product_id = s.product_id

LC1378：使用唯一标识码替换员工ID

传送门

关键词：左外连接(left join)的使用

# Write your MySQL query statement below
select unique_id, `name`
from Employees em
left join EmployeeUNI eu
on em.id = eu.id

LC1350：院系无效的学生

传送门

学到的知识点：

使用not in语句来实现"不含/不存在xxx"类的查询
在in关键字后跟select来创建一个用于查询的临时集合

# Write your MySQL query statement below
select s.id, s.`name`
from Students s
where s.department_id not in (
    select id 
    from Departments
)

LC1587：银行账户概要II

传送门

关键词：聚合函数的使用。

# Write your MySQL query statement below
select u.`name`, sum(t.amount) as balance
from Users u
join Transactions t
on u.`account` = t.`account`
group by u.`account`
having sum(t.amount) > 10000

LC1741：每个员工的工作时间

传送门

学到的知识点：

group by x, y表示"将x与y属性相同的记录归在一个组里"

# Write your MySQL query statement below
select 
    event_day as `day`,
    emp_id,
    sum(out_time - in_time) as total_time
from Employees
group by `day`, emp_id

LC1777：每家商店的产品价格

传送门

挨个统计每一个store的总和。

select 
    product_id,
    sum(if(store='store1', price, null)) as store1,
    sum(if(store='store2', price, null)) as store2,
    sum(if(store='store3', price, null)) as store3
from Products
group by product_id;