瀏覽標籤:

MySQL

[Linux] 新增虛擬記憶體(Swap)

今天把 GCP 的記憶體調整縮小,立馬發生 MySQL 崩潰的問題,大膽判斷此問題與記憶體有關,檢查了一下 LOG 果真如此

InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12
InnoDB: Completed initialization of buffer pool
InnoDB: Fatal error: cannot allocate memory for the buffer pool

檢查發現 GCP 建立的時候沒有幫我開啟 Swap,然後我又把規格調整到最低的 f1-micro 才引發此問題。

閱讀更多

       

[Linux][MySQL][phpMyAdmin] 預設語系修改為 utf8

因為Mysql是瑞典人開發的所以預設語系是Latin1,如果想把他修改為utf8的話可以參考以下設定

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[mysql]
default-character-set=utf8
init-connect='SET NAMES utf8'  
character-set-server = utf8 

[client] 
default-character-set=utf8

phpmyadmin可以在config.inc.php新增一行

/* 預設語系 */
$cfg['DefaultCharset'] = 'utf8';
       

[MySQL][LeetCode][Easy] 595. Big Countries

心得:

這題只要找出領土大於三百萬或是人口大於兩百五十萬的的資料即可

問題:

There is a table World

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

Write a SQL solution to output big countries’ name, population and area.

For example, according to the above table, we should output:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

答案:

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

 

       

[MySQL][LeetCode][Medium] 180. Consecutive Numbers

心得

這題要找出連續三次重複出現的數字,我原本一直在想如果中間有斷層(中間有資料被刪除)的話是否要先自己排序一次,結果看了一下Top Solutions才發現根本不用考慮這個問題,直接ID+1尋找下筆資料即可,既然這麼單純的話也沒什麼問題了。

問題

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

答案

  1. 方法對,但會TimeOut
    # Write your MySQL query statement below
    SELECT DISTINCT(a.Num) AS ConsecutiveNums
    FROM Logs AS a
    WHERE ( SELECT z.Num
            FROM Logs AS z
            WHERE z.Id = a.Id + 1) = a.Num
    AND (   SELECT z.Num
            FROM Logs AS z
            WHERE z.Id = a.Id + 2) = a.Num
  2. 通過
    # Write your MySQL query statement below
    SELECT DISTINCT(a.Num) AS ConsecutiveNums
    FROM Logs AS a
    JOIN Logs AS b
    ON a.Id = b.Id - 1
    JOIN Logs AS c
    ON b.Id = c.Id - 1
    WHERE a.Num = b.Num
    AND b.Num = c.Num

     

       

[MySQL][LeetCode][Hard] 185. Department Top Three Salaries

心得

這題是 184. Department Highest Salary 的衍生題,但如果直接套用其方法的話會TimeOut,不過還是記錄一下方法:

# Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     LIMIT 0, 1) AS TopSalary,
                    (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     AND y.Salary < TopSalary
                     LIMIT 0, 1) AS TwoSalary,
                    (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     AND y.Salary < TwoSalary
                     LIMIT 0, 1) AS ThreeSalary
        FROM Department AS z) AS b
ON a.DepartmentId = b.Id
WHERE ( a.Salary = b.TopSalary
OR      a.Salary = b.TwoSalary
OR      a.Salary = b.ThreeSalary)

問題

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

答案

  1. 這個方法是在Top Solutions看到的,在WHERE裡面統計有幾個其他同部門的人大於自己的薪水,把小於三個的都列出來,就是答案。
    # Write your MySQL query statement below
    SELECT  b.Name AS Department,
            a.Name AS Employee,
            a.Salary AS Salary
    FROM Employee AS a
    JOIN Department AS b
    ON a.DepartmentId = b.Id
    WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
            FROM Employee AS z
            WHERE a.DepartmentId = z.DepartmentId
            AND a.Salary < z.Salary) < 3
  2. 同上,只是改成WHERE IN效能較佳
    # Write your MySQL query statement below
    SELECT  b.Name AS Department,
            a.Name AS Employee,
            a.Salary AS Salary
    FROM Employee AS a
    JOIN Department AS b
    ON a.DepartmentId = b.Id
    WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
            FROM Employee AS z
            WHERE a.DepartmentId = z.DepartmentId
            AND a.Salary < z.Salary) IN (0, 1, 2)

     

       

[MySQL][LeetCode][Hard] 262. Trips and Users

心得

這題只是看起來比較多欄位其實沒有多難,題目要求找出除了已經被封鎖的客戶以外的取消率。

問題

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

答案

# Write your MySQL query statement below
SELECT  a.Request_at AS Day,
        ROUND(SUM(IF(a.Status = 'completed',0 ,1)) / COUNT(1), 2) AS 'Cancellation Rate'
FROM Trips AS a
JOIN Users AS b
ON a.Client_Id = b.Users_Id
WHERE b.Banned = 'No' 
AND b.Role = 'client'
AND (a.Request_at BETWEEN '2013-10-01' AND '2013-10-03')
GROUP BY a.Request_at

 

       

[MySQL][LeetCode][Medium] 177. Nth Highest Salary

心得

這題非常有趣,題目要求寫一預存程式輸入N並取出排名第N名的薪水為多少,這題與178. Rank Scores差不多,所以可以直接套用。

題目

Write a SQL query to get the nth highest salary from the Employee table.

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

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

答案

  1. 這題我是拿178. Rank Scores的解法直接套上去取N
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
          # Write your MySQL query statement below.
          SELECT a.Salary
          FROM (SELECT (@row_number:=@row_number + 1) AS Rank, z.Salary
                FROM (  SELECT x.* 
                        FROM Employee AS x 
                        GROUP BY x.Salary
                        ORDER BY x.Salary DESC) AS z
                JOIN (SELECT @row_number := 0) AS y) AS a
          WHERE a.Rank = N
      );
    END

     

  2. 這解法是由高至低排序了所有的薪水,再用LIMIT來獲取Nth(因為LIMIT是從0開始所以N必須先減一),非常易懂的解法。
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
        DECLARE M INT;
        SET M = N - 1;
        RETURN (
            # Write your MySQL query statement below.
            SELECT Salary
            FROM Employee
            GROUP BY Salary
            ORDER BY Salary DESC
            LIMIT M, 1
      );
    END

     

參考:

  1. [MySQL][LeetCode][Medium] 178. Rank Scores
       

[MySQL][LeetCode][Medium] 184. Department Highest Salary

心得

題目要求找出每個部門薪水最高的員工,若同部門最高薪水有複數則一起顯示,這題我在JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來,這樣在關聯兩張表的時候就可以直接搜尋員工薪水是否等於最高薪資。

問題

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

答案

  1. JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來
    # Write your MySQL query statement below
    SELECT  b.Name AS Department,
            a.Name AS Employee,
            a.Salary AS Salary
    FROM Employee AS a
    JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                         FROM Employee AS y
                         WHERE z.Id = y.DepartmentId
                         LIMIT 0, 1) AS MaxSalary
            FROM Department AS z) AS b
    ON a.DepartmentId = b.Id AND a.Salary = b.MaxSalary
  2. (2017/01/16) 寫 185. Department Top Three Salaries 時後發現的方法
    # Write your MySQL query statement below
    SELECT  b.Name AS Department,
            a.Name AS Employee,
            a.Salary AS Salary
    FROM Employee AS a
    JOIN Department AS b
    ON a.DepartmentId = b.Id
    WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
            FROM Employee AS z
            WHERE a.DepartmentId = z.DepartmentId
            AND a.Salary < z.Salary) = 0