瀏覽標籤:

MySQL

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

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

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

閱讀更多

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

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

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

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

心得:

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

問題:

There is a table World

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:

答案:

 

[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.

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

答案

  1. 方法對,但會TimeOut
  2. 通過

     

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

心得

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

問題

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

The Department table holds all departments of the company.

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.

答案

  1. 這個方法是在Top Solutions看到的,在WHERE裡面統計有幾個其他同部門的人大於自己的薪水,把小於三個的都列出來,就是答案。
  2. 同上,只是改成WHERE IN效能較佳

     

[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’).

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

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.

答案

 

[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.

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

     
  2. 這解法是由高至低排序了所有的薪水,再用LIMIT來獲取Nth(因為LIMIT是從0開始所以N必須先減一),非常易懂的解法。

     

參考:

  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.

The Department table holds all departments of the company.

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.

答案

  1. JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來
  2. (2017/01/16) 寫 185. Department Top Three Salaries 時後發現的方法

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

心得

題目要求找出成績排名,如果分數相同的話則相同名次,MySQL不像MSSQL有ROW_NUMBER()可以用,只好用個變數來存了。

問題

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

For example, given the above Scores table, your query should generate the following report (order by highest score):

答案

 

[MySQL][LeetCode][Easy] 196. Delete Duplicate Emails

心得

題目要求刪除同樣Email的資料,在DELETE的條件WHERE裡面不能包含異動的表(Person),所以這裡再包了一層SELECT來排除編譯錯誤的問題,而這層必須給予別名(強制)否則會編譯錯誤。

問題

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

For example, after running your query, the above Person table should have the following rows:

答案

 

  • 1
  • 2