Truy vấn nâng cao MySQL

Cách sử dụng Coalesce IFNULL trong MySQL

Giả sử bạn đang xem một cột số nguyên MySQL trong đó một số hàng là null:

select
day,
tickets
from stats;

day | tickets
------------+-------
2018-01-01 | 1
2018-01-02 | null
2018-01-03 | 3

Thay vì có giá trị null đó, bạn có thể muốn hàng đó là 0. Để làm như vậy, hãy sử dụng ifnullhàm trả về đối số không null đầu tiên được truyền vào:

select
day,
ifnull(tickets, 0)
from stats;

day | tickets
------------+-------
2018-01-01 | 1
2018-01-02 | 0
2018-01-03 | 3

Cách tính phần trăm trong MySQL

MySQL vẫn còn chậm hơn các cơ sở dữ liệu khác khi nói đến các hàm phân tích/cửa sổ. Nhưng có nhiều cách để thực hiện được điều này. Ví dụ, để có được thứ hạng phần trăm từ trên xuống của độ dài phim từ Cơ sở dữ liệu mẫu Sakila :

SELECT
f.title,
ROUND(100.0 * (SELECT COUNT(*) FROM film AS f2 WHERE f2.length <= f.length) / totals.film_count, 1) AS percentile
FROM film f
CROSS JOIN (
SELECT COUNT(*) AS film_count
FROM film
) AS totals
ORDER BY percentile DESC;

Cách lấy hàng đầu tiên cho mỗi nhóm trong MySQL

Bắt đầu từ phiên bản 8.0.2, MySQL hiện cung cấp một cách để dễ dàng đánh số hàng. Ví dụ, nếu chúng ta muốn liệt kê số lượng phim trong Sakila Sample Database , được nhóm theo xếp hạng và sắp xếp theo năm phát hành:

SELECT *,
row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
FROM film;

Bây giờ nếu bạn chỉ muốn lấy hàng đầu tiên cho mỗi xếp hạng, bạn có thể sử dụng biểu thức bảng phổ biến :

WITH _films AS (
SELECT *,
row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
FROM film;
)
SELECT *
FROM _films
WHERE row_num = 1;

Làm thế nào để tránh khoảng trống trong dữ liệu trong MySQL

Nếu bạn đang nhóm theo thời gian và không muốn có khoảng trống trong dữ liệu báo cáo, bạn cần tạo một chuỗi giá trị thời gian và sử dụng nó để thực hiện liên kết ngoài với dữ liệu của mình. Trước MySQL 8, bạn có thể thực hiện việc này bằng cách sử dụng các biến. Trong ví dụ sau, chúng tôi đã trích xuất số lượng cho thuê mỗi giờ từ Cơ sở dữ liệu mẫu Sakila :

-- The first line is to make the first value of statement below 0:00:00, not 1:00:00
-- LIMIT 720 gives 30 days worth of hourly values
SET @n:=('2005-05-25' - INTERVAL 1 HOUR);
SELECT
hours.this_hour,
count(rental.rental_id)
FROM
(SELECT (SELECT @n:= @n + INTERVAL 1 HOUR) this_hour
FROM inventory LIMIT 720) hours
LEFT JOIN rental ON (hours.this_hour=date_format(rental.rental_date,'%Y-%m-%d %H:00:00'))
GROUP BY hours.this_hour;

Có những hạn chế nghiêm trọng với phương pháp trên. Trước hết, bạn có thể hỏi inventorybảng có liên quan gì đến việc cho thuê? Nó không liên quan trực tiếp đến việc cho thuê. inventoryBảng được chọn vì nó có vài nghìn hàng và chúng tôi cần một bảng có ít nhất 720 hàng để tạo chuỗi giá trị thời gian của chúng tôi. Trên thực tế, bất kỳ bảng nào có nhiều hàng đều có thể được sử dụng cho mục đích này. Nhưng nếu không có bảng nào khả dụng thì sao?
May mắn thay, bắt đầu từ MySQL 8, bạn có thể sử dụng biểu thức bảng phổ biến :

WITH RECURSIVE my_hours AS
(
SELECT 0 as inc
UNION ALL
SELECT 1+inc
FROM my_hours WHERE inc<=720
)
SELECT
hours.this_hour,
count(rental.rental_id)
FROM
(SELECT '2005-05-25' + interval inc hour as this_hour
FROM my_hours) as hours
LEFT JOIN rental ON (hours.this_hour=date_format(rental.rental_date,'%Y-%m-%d %H:00:00'))
GROUP BY hours.this_hour;

Làm thế nào để thực hiện Type Casting trong MySQL

Theo mặc định, MySQL không nghiêm ngặt với việc ép kiểu. Ví dụ, thêm một giá trị số trong dấu ngoặc kép vào một giá trị số khác mà không gây ra lỗi thông thường mà các cơ sở dữ liệu và ngôn ngữ lập trình khác sẽ gây ra:

mysql> select 1 + '1'; +---------+ | 1 + '1' | +---------+ | 2 | +---------+

Tuy nhiên, nếu cần, bạn có thể sử dụng CAST() hàm để ép kiểu giá trị. Bạn có thể ép kiểu thành các kiểu sau: BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED.

-- cast float to unsigned integer
SELECT CAST(1.0 AS UNSIGNED);

-- cast string to date
SELECT CAST('2018-12-12' AS DATE);

-- cast string to decimal
SELECT CAST('12.345' AS DECIMAL(5,3));

-- cast string to time:
SELECT CAST('12:45' AS TIME);