Follow Fang!: MySQL

顯示具有 MySQL 標籤的文章。顯示所有文章

2023/10/05

使用MySQL 產生某個區間的時間

WITH RECURSIVE Date_Ranges AS (
    SELECT '2023-06-01' AS Date
   UNION ALL
   SELECT Date + INTERVAL 1 DAY
   FROM Date_Ranges
   WHERE Date < '2023-10-05')
SELECT * FROM Date_Ranges;

2023/05/15

macOS /bin/sh mariadb_config command not found

echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc
echo 'export LDFLAGS="$LDFLAGS:-L/opt/homebrew/opt/mysql-client/lib"' >> ~/.zshrc
echo 'export CPPFLAGS="$CPPFLAGS:-I/opt/homebrew/opt/mysql-client/include"'  >> ~/.zshrc

2022/12/24

LeetCode 176. Second Highest Salary

問題：輸出第二高的薪水解法：透過LIMIT和OFFSET搭配


SELECT (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary;

2022/12/20

LeetCode 1795. Rearrange Products Table

問題：輸出不同商店的價格

解法：UNION將SELECT語句合併即可

SELECT product_id, "store1" AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL
UNION
SELECT product_id, "store2", store2
FROM Products
WHERE store2 IS NOT NULL
UNION
SELECT product_id, "store3", store3
FROM Products
WHERE store3 IS NOT NULL;

LeetCode 1757. Recyclable and Low Fat Products

問題：找出低脂和可回收產品

解法：WHERE AND

SELECT product_id
FROM Products
WHERE low_fats = 'Y'
  AND recyclable = 'Y';

LeetCode 1741. Find Total Time Spent by Each Employee

問題：計算每一天不同員工進出時間

解法：透過SUM計算進出時間，Group BY將emp_id和event_day分組

SELECT event_day as day, emp_id, SUM(out_time - in_time) as total_time
FROM Employees
GROUP BY emp_id, event_day
ORDER BY event_day;

LeetCode 1693. Daily Leads and Partners

問題：取得每一天的Leads和Partners總數

解法：透過COUNT以及DISTINCT過濾重複的欄位值並計算總數，GROUP BY根據date_id和make_name欄位分組

SELECT date_id, make_name, COUNT(DISTINCT lead_id) AS unique_leads, COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales
GROUP BY date_id, make_name
ORDER BY date_id;

LeetCode 1587. Bank Account Summary II

問題：輸出銀行存款大於10000的金額和使用者名稱

解法：透過LEFT JOIN和GROUP BY將相同使用者Record整合成一筆，最後用HAVING判斷存款是否大於10000

SELECT name, SUM(T.amount) AS balance
FROM Transactions AS T
         LEFT JOIN Users AS U ON T.account = U.account
GROUP BY T.account
HAVING SUM(T.amount) > 10000;

LeetCode 1393. Capital Gain/Loss

問題：計算買賣損失

解法：透過SUM搭配CASE WHEN即可

SELECT stock_name,
       SUM(
               CASE
                   WHEN operation = 'Buy' THEN -1 * price
                   WHEN operation = 'sell' THEN price
                   END
           ) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;

LeetCode 627. Swap Salary

問題：將性別對調

解法：透過UPDATE和CASE WHEN即可

UPDATE Salary
SET sex=CASE sex
            WHEN 'm' THEN 'f'
            WHEN 'f' THEN 'm'
    END;

LeetCode 183. Customers Who Never Order

問題：哪個使用者沒有訂單

解法：兩者共同之處在於Customers的id等於Order的customerId，Orders裡面的customerId會告訴你誰沒有訂單，那將有訂單之外的使用者排除掉即可

SELECT name AS Customers
FROM Customers
WHERE Customers.id NOT IN
      (SELECT customerId FROM Orders);

LeetCode 175. Combine Two Tables

問題：如何讓兩個Tables能輸出成他所想要的

解法：關鍵在兩個Table都擁有personId欄位，將兩個Table欄位的personId JOIN就可以了，再輸出如需求的欄位即可

SELECT p.firstName, p.lastName, a.city, a.state
FROM Person AS p
         LEFT JOIN Address AS a ON p.personId = a.personId;

2020/01/07

DataGrip JDBC連接MySQL錯誤

今天再用DataGrip連接MySQL資料庫時發生了錯誤

The specified database user/password combination is rejected: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'unknown' is unrecognized or represents more than one time zone. 
You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

原本配置如下

該錯誤指向Time Zone問題

CentOS 7.0 Install MySQL Server

rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server

參考資料：
http://www.phpini.com/mysql/rhel-centos-yum-install-mysql

2015/02/03

C#.Net 連結 MySQL

Code：

            string connStr = @"server={0};database={1};uid={2};pwd={3};charset=utf8;Allow Zero Datetime=true;";
            using (MySqlConnection _conn = new MySqlConnection(connStr))
            {
                _conn.Open();
            }

2015/01/28

C#.Net MySQL Date to C#.Net DateTime format

兩年前也有遇到這問題，那時候應該用比較差的方法解決
只是一直忘記寫筆記，而且程式碼也不知道放到哪去了
剛好最近又遇到所以還是寫篇文章來紀錄

程式碼如下，看起來單純在撈資料，並將資料給予DataGridView作顯示而已

Code：

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Windows.Forms;

namespace Sample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            using (MySqlConnection conn = new MySqlConnection("server={0};database={1};uid={2};pwd={3};charset=utf8;Allow Zero Datetime = true;"))
            {
                conn.Open();
                using (MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT date FROM test;", conn))
                {
                    DataSet set = new DataSet();
                    adapter.Fill(set);
                    dataGridView1.DataSource = set.Tables[0];
                }
            } 
        }

    }

}

C#.Net DataGridView透過MySqlCommandBuilder更新資料

MySQLAdapter類別內有個方法是Update，必須搭配MySqlCommandBuilder
從MySqlCommandBuilder這個類別內的方法看來，除了新增修改也可以做到刪除的行為，開發者不需要自己去輸入MySQL語法
MySQLAdapter應該是透過BindingSource去取得更新或修改過後的DataSet

執行修改前資料如下：

執行修改後資料如下：

執行新增後資料如下：

Python 2.7.6 連接MySQL

Python要與MySQL連接是沒有問題的，網路上有人寫出『MySQL-Python』套件
想研究原始碼可以參考他的部落格或是原始碼

不過要注意Python 3將會在下一個版本支援
僅支援Python 2.4 or Python 2.7

Ubuntu 12.04 LTS 安裝Apache、MySQL以及PHP5

先安裝Apache

sudo apt-get install -y apache2

在安裝PHP

sudo apt-get install -y php5 php5-mysql libapache2-mod-php5

Fedora 17 安裝 MySQL PHP Apache

輸下以下指令安裝MySQL PHP Apache

yum install -y httpd mysql mysql-server php

現在以及開機時啟動Apache服務，輸入：

systemctl enable httpd.service
systemctl start httpd.service

訂閱：文章 (Atom)

Follow Fang!