瀏覽標籤：

MySQL

[MySQL] 資料表結構遷移的好幫手 pt-online-schema-change

作者lex.xu
/
7 月 16, 2020
/
心得筆記

這邊跟大家介紹一套做 DB migration 的好東西 pt-online-schema-change，這是可以讓 DBA 省時省心力的工具，他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime 的好物。

[Docker] 使用容器建立 MySQL 與 phpMyAdmin

作者lex.xu
/
4 月 21, 2019
/
心得筆記

今天在玩 Docker on Windows 的時候想架個資料庫，於是乎選擇了 MySQL 與 phpMyAdmin 的組合，這裡做個筆記供日後參考。閱讀更多

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

作者lex.xu
/
11 月 16, 2018
/
心得筆記

今天把 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

作者lex.xu
/
10 月 31, 2017
/
心得筆記

因為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

作者lex.xu
/
10 月 19, 2017
/
心得筆記

心得：

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

問題：

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

作者lex.xu
/
1 月 16, 2017
/
心得筆記

心得

這題要找出連續三次重複出現的數字，我原本一直在想如果中間有斷層(中間有資料被刪除)的話是否要先自己排序一次，結果看了一下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.

答案

方法對，但會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

通過

# 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

作者lex.xu
/
1 月 16, 2017
/
心得筆記

心得

這題是 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  |
+------------+----------+--------+

答案

這個方法是在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

同上，只是改成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

作者lex.xu
/
1 月 15, 2017
/
心得筆記

心得

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

問題

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

作者lex.xu
/
1 月 15, 2017
/
心得筆記

心得

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

題目

Write a SQL query to get the n^th highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
For example, given the above Employee table, the n^th highest salary where n = 2 is 200. If there is no n^th highest salary, then the query should return null.

答案

這題我是拿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

這解法是由高至低排序了所有的薪水，再用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

參考：

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

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

作者lex.xu
/
1 月 15, 2017
/
心得筆記

心得

題目要求找出每個部門薪水最高的員工，若同部門最高薪水有複數則一起顯示，這題我在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  |
+------------+----------+--------+

答案

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

(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