MySQL truy vấn nâng cao - Cách viết biểu thức bảng trong MySQL

Cách viết biểu thức bảng chung trong MySQL

Biểu thức bảng chung (CTE) là một cách tuyệt vời để chia nhỏ các truy vấn phức tạp. MySQL bắt đầu hỗ trợ điều này trong phiên bản 8. Sau đây là một truy vấn đơn giản để minh họa cách viết CTE:

with beta_users as (
select *
from users
where beta is true
)
select events.*
from events
inner join beta_users on beta_users.id = events.user_id;

Bạn có thể tìm thấy những ví dụ phức tạp hơn về cách sử dụng CTE trong bài Cách tránh khoảng trống trong dữ liệu trong MySQL và trong bài Tính tổng tích lũy trong MySQL.

Cách nhập - Import file CSV vào MySQL

Nhập CSV vào MySQL yêu cầu bạn phải tạo bảng trước. Sao chép cấu trúc của bảng hiện có cũng có thể hữu ích ở đây.
Hướng dẫn này tập trung vào LOAD DATAlệnh có sẵn nếu bạn sử dụng máy khách dòng lệnh MySQL. Theo mặc định, nó giả định dữ liệu nguồn của bạn được phân định bằng tab (so với dấu phẩy), có chuỗi chứa dấu phân cách tab trong dấu ngoặc đơn và mỗi hàng dữ liệu kết thúc bằng \nký tự xuống dòng ().
Đầu tiên bạn cần kết nối với MySQL:

$ mysql -u username -p -h database.host.name database_name

Tùy chọn này -p sẽ khiến người dùng được nhắc nhập mật khẩu. -h database.host.name là tùy chọn nếu máy chủ MySQL nằm trên cùng một máy chủ. Cũng là tùy chọn database_name nhưng tốt hơn là chỉ định tên cơ sở dữ liệu/lược đồ ngay bây giờ thay vì sau này.

Sau đây là một số ví dụ sử dụng LOAD DATA lệnh này:

-- source file is located in the MySQL Server data directory, is tab delimited, and the columns exactly match the table
LOAD DATA INFILE 'user_data.tsv' INTO TABLE users;

-- source file is local, is comma delimited and contains only some columns. Database is remote
LOAD DATA LOCAL INFILE '/tmp/user_data.csv'
INTO TABLE users (first_name, last_name, email)
FIELDS TERMINATED BY ',';

-- source file is comma delimited, strings are enclosed by double quotes, lines are terminated by carriage return/newline pairs, has a single header row that has to be ignored
LOAD DATA INFILE 'data.txt' INTO TABLE my_table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

-- Ignore a column in the source file by assigning it to a user variable and not assigning the variable to a table column
LOAD DATA INFILE 'data.txt'
INTO TABLE my_table_name (column1, @dummy, column2, @dummy, column3);

-- Populate a column the source file does not have any data for using the SET clause
LOAD DATA INFILE 'file_no_timestamps.txt'
INTO TABLE my_table_with_timestamps
(column1, column2)
SET timestamp_column = CURRENT_TIMESTAMP;

Cách so sánh hai giá trị khi một giá trị là Null trong MySQL

Hãy tưởng tượng bạn đang so sánh hai cột MySQL và bạn muốn biết có bao nhiêu hàng khác nhau. Không vấn đề gì, bạn nghĩ:

SELECT count(1)
FROM items
WHERE width != height;

Không nhanh như vậy đâu. Nếu một số chiều rộng hoặc chiều cao là null, chúng sẽ không được tính! Chắc chắn đó không phải là ý định của bạn. Đó là lúc bạn cần một toán tử nhận biết null như <=>:

SELECT count(1)
FROM items
WHERE NOT (width <=> height);

Bởi vì <=> thực ra là phiên bản nhận biết null của toán tử bằng, chúng ta cần phủ định nó bằng NOT. Bây giờ, số đếm của bạn sẽ "nhận biết null" và bạn sẽ nhận được kết quả mong muốn.

Cách viết câu lệnh Case trong MySQL

Các câu lệnh case hữu ích khi bạn xử lý nhiều IF câu lệnh trong mệnh đề select của mình. Nó có hai dạng:

SELECT
CASE
WHEN score < 70 THEN 'failed'
WHEN score BETWEEN 70 AND 80 THEN 'passed'
WHEN score BETWEEN 81 AND 90 THEN 'very good'
ELSE 'outstanding'
END AS performance
FROM test_scores;

SELECT
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Good'
WHEN 'C' THEN 'Needs Improvement'
ELSE 'Failed'
END AS grade_interpretation
FROM grades;

Cách truy vấn một cột JSON trong MySQL

Bắt đầu từ phiên bản 5.7.8, MySQL hỗ trợ các cột JSON. Điều này mang lại lợi thế cho việc lưu trữ và truy vấn dữ liệu không có cấu trúc. Sau đây là cách bạn có thể truy vấn một cột JSON trong MySQL:

-- Getting the params.name string value from events table
SELECT params->>'$.name'
FROM events;

-- Getting rows where the browser.name is Chrome
-- This also shows the difference of using -> vs ->>
-- Using -> will cause strings to be enclosed in quotes
SELECT browser->>'$.name', browser->'$.name'
FROM events
WHERE browser->>'$.name' = 'Chrome';

-- Give me the first index of a JSON array
SELECT properties->>'$.my_array[0]'
FROM events;
-- Going deeper to get the X resolution only
SELECT properties->'$.resolution.x'
FROM events;

Làm thế nào để truy vân đếm nhiều số trong MySQL

Để thực hiện nhiều lần đếm trong một truy vấn trong MySQL, bạn có thể kết hợp COUNT() với IF():

SELECT
COUNT(1), -- Count all users
COUNT(IF(gender='male', 1, 0)), -- Count male users
COUNT(IF(beta=true, 1, 0)) -- Count beta users
COUNT(IF(active=true AND beta = false, 1, 0)) -- Count active non-beta users
FROM users;

Cách tính tổng tích lũy-tổng chạy trong MySQL

Giả sử đối với cơ sở dữ liệu cho thuê DVD MySQL của chúng ta, chúng ta muốn xem biểu đồ gậy khúc côn cầu về lượng cho thuê tích lũy theo ngày. Đầu tiên, chúng ta sẽ cần một bảng có cột ngày và cột số lượng:

SELECT
date(rental_date) as day,
count(rental_id) as rental_count
FROM rental
GROUP BY day;

day | rental_count
-----------+--------------
2005-05-24 | 8
2005-05-25 | 137
2005-05-26 | 174
2005-05-27 | 166
2005-05-28 | 196

Sau đó, chúng ta sử dụng điều này để thực hiện tổng tích lũy của chúng ta. Trước MySQL phiên bản 8, bạn có thể sử dụng các biến cho việc này:

SELECT t.day,
t.rental_count,
@running_total:=@running_total + t.rental_count AS cumulative_sum
FROM
( SELECT
date(rental_date) as day,
count(rental_id) as rental_count
FROM rental
GROUP BY day ) t
JOIN (SELECT @running_total:=0) r
ORDER BY t.day;

day | rental_count | cumulative_sum
-----------+--------------+----------------
2005-05-24 | 8 | 8
2005-05-25 | 137 | 145
2005-05-26 | 174 | 319
2005-05-27 | 166 | 485
2005-05-28 | 196 | 681

Đối với MySQL 8, bạn có thể sử dụng biểu thức bảng chung (CTE) của MySQL SUM() và có cửa sổ thay vì truy vấn phụ để dễ đọc hơn, kết quả vẫn như vậy:

with data as (
select
date(rental_date) as day,
count(rental_id) as rental_count
from rental
group by day
)

select
day,
rental_count,
sum(rental_count) over (order by day) as cumulative_sum
from data;