瀏覽標籤:

MySQL

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

答案

 

       

[MySQL][LeetCode][Easy] 197. Rising Temperature

心得

題目要求找出溫度比前一天高的資料,這裡用了DATE_SUBTO_DAYS兩種方式來解答。

題目

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

For example, return the following Ids for the above Weather table:

答案

  1. Sub Query
  2. Join
  3. TO_DAYS

     

參考:

  1. DATE_ADD() 與 DATE_SUB() 日期的加法與減法
  2. Mysql日期和時間函數不求人
       

[MySQL][LeetCode][Easy] 183. Customers Who Never Order

心得

這題要show出沒有訂過東西的客戶,所以可以使用LEFT JOIN來關聯兩張表。

問題

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

Table: Orders.

Using the above tables as example, return the following:

答案

  1. LEFT JOIN
  2. Sub Query
       

[MySQL][LeetCode][Easy] 176. Second Highest Salary

心得:

找出該資料表內第二高薪水的筆數,若無則回傳null。

問題:

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

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

答案:

  1. Sub Query
  2. DISTINCT > 不重複
  3. GROUP BY > 不重複

     
       

[MySQL][LeetCode][Easy] 181. Employees Earning More Than Their Managers

心得:

題目要求找出有哪些人的薪水比主管高,找出名字並修改欄位名稱為Employee

題目:

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

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

答案:

  1. Sub Select
  2. Join
  3. From

     
       

[MySQL][LeetCode][Easy] 175. Combine Two Tables

心得:

題目要求找出Person表內所有人員的地址,就算是空的也無所謂,所以不用理會Address表內是否有Person表的資料,這題必須使用LEFT JOIN來關聯。

問題:

Table: Person

Table: Address

Write a SQL query for a report that provides the following information for
each person in the Person table, regardless if there is an address for each
of those people:

答案:

參考:

  1. [SQL] 一張圖片解釋 JOIN
       

[MySQL][LeetCode][Easy] 182. Duplicate Emails

心得:

題目要求找出Email重複的資料並Show出來,這裡要注意的是沒辦法使用WHERE而必須使用HAVING,因為WHEREGROUP BY前面,HAVINGGROUP BY後面。

問題:

Write a SQL query to find all duplicate emails in a table named Person.

答案:

參考:

  1. SQL語法中WHERE與HAVING有何差異?
       

[MySQL] INSERT資料前檢查資料是否重複

有時候需要檢查資料是否重複再填入,這裡簡單做個筆記。

程式碼

 

SELECT後面是準備填入的參數

WHERE這邊驗證資料是否重複

 

來源:MySQL INSERT 與 UPDATE 的一些特殊情況用法

       

[C#][ASP.NET MVC5] MySQL Entity Framework 學習筆記(一) – 環境設定

剛開始在碰ASP.NET MVC的時候,查詢該如何連結資料庫時常常會聽到Entity Framework這個方法,但由於那時沒有太多時間,沒去查太多資料都用SQL自幹,現在閒下來了,就來玩玩看這個東西,首先是環境設定:

  1. NuGet安裝MySQL使用Entity Framework時所需要的套件
    01
     
  2. 總共有三個東西要安裝,分別是
    • MySql.Data
    • MySql.Data.Entity
    • MySql.Web

    01

  3. 安裝完畢後就要開始設定連結字串,打開Web.config新增連結字串

    02

  4. 建立MySqlContext.cs並繼承DbContext供後續使用

 

結論:

這樣環境就建置完成囉,後續就可以開始使用Entity Framework了!

 

參考:

  1. ASP.NET MVC, ENTITY FRAMEWORK CODE FIRST與MYSQL筆記
  2. Entity Framework 快速上手與學習資源整理