Cú pháp SELECT
SELECT [ALL | DISTINCT]
{ * | table.* | [ table.truong1 [ as ten_dai_dien1] [,table.truong2 [as ten_dai_dien2]][,…]] }
FROM ten_bang [ as ten_dai_dien_bang ]
[left|out|inner join ten_bang2] #Truy vấn kết hợp
[ WHERE … ] #Chỉ định điều kiện kết quả phải thỏa mãn
[ GROUP BY …] #Chỉ định kết quả được nhóm theo các trường
[ HAVING …] #Lọc các bản ghi nhóm phải thỏa mãn điều kiện phụ
[ ORDER BY… ] #Chỉ định sắp xếp bản ghi theo một hoặc nhiều điều kiện
[ LIMIT { [ offset,] so_ban_ghi | so_ban_ghi OFFSET offset }] ; #Chỉ định các bản ghi truy vấn từ đâu đến đâu
Sắp xếp với ORDER BY
Truy vấn sắp xếp với ORDER BY
Sắp xếp kết quả trả về từ câu lệnh SELECT theo một số trường nhất định
Có thể kết hợp với DESC (giảm dần) hoặc ASC (tăng dần), mặc định là ASC
Giới hạn kết quả với LIMIT trong MySQL
LIMIT [m,]n hoặc LIMIT n OFFSET m
Giới hạn số hàng kết quả trả về của câu lệnh SELECT
m chỉ định vị trí bắt đầu của hàng kết quả đầu tiên
n chỉ định số lượng hàng kết quả tối đa
Lưu ý: Nếu không chỉ định m, vị trí bắt đầu sẽ là 0, tức là trả về n hàng đầu tiên. LIMIT thường được sử dụng cho phân trang.
SELECT * FROM `ket_qua` LIMIT 5 #Trả về 5 hàng đầu tiên
SELECT * FROM `ket_qua` LIMIT 5,10 #Trả về các hàng từ 6 đến 15
Truy vấn con (Subquery)
Truy vấn lồng nhau trong mệnh đề WHERE
Một câu lệnh truy vấn được lồng trong mệnh đề WHERE của một câu truy vấn khác
Lưu ý: Truy vấn con có thể bao gồm nhiều truy vấn con, được giải quyết từ trong ra ngoài. Kết quả trả về của truy vấn con thường là một tập hợp, vì vậy nên sử dụng từ khóa IN.
Các hàm trong MySQL
Hàm toán học
1. ABS(x) - Trả về giá trị tuyệt đối của x
SELECT ABS(-1);
2. AVG(bieu_thuc) - Trả về giá trị trung bình của một biểu thức
SELECT AVG(diem) FROM diem;
3. CEIL(x)/CEILING(x) - Trả về số nguyên nhỏ nhất lớn hơn hoặc bằng x
SELECT CEIL(1.5);
SELECT CEILING(1.5);
4. FLOOR(x) - Trả về số nguyên lớn nhất nhỏ hơn hoặc bằng x
SELECT FLOOR(1.5);
5. EXP(x) - Trả về e mũ x
SELECT EXP(3);
6. GREATEST(expr1, expr2, expr3, …) - Trả về giá trị lớn nhất trong danh sách
SELECT GREATEST(3, 12, 34, 8, 25);
7. LEAST(expr1, expr2, expr3, …) - Trả về giá trị nhỏ nhất trong danh sách
SELECT LEAST(3, 12, 34, 8, 25);
8. LN - Trả về logarith tự nhiên của một số
SELECT LN(2);
9. LOG(x) - Trả về logarith tự nhiên (cơ số e)
SELECT LOG(20.085536923188);
10. MAX(bieu_thuc) - Trả về giá trị lớn nhất trong trường expression
SELECT MAX(diem) FROM diem;
11. MIN(bieu_thuc) - Trả về giá trị nhỏ nhất trong trường expression
SELECT MIN(diem) FROM diem;
12. POW(x,y)/POWER(x,y) - Trả về x mũ y
SELECT POW(2,3);
SELECT POWER(2,3);
13. RAND() - Trả về số ngẫu nhiên từ 0 đến 1
SELECT RAND();
14. ROUND(x) - Trả về số nguyên gần x nhất
SELECT ROUND(1.23456);
15. SIGN(x) - Trả về dấu của x (-1 cho số âm, 0 cho 0, 1 cho số dương)
SELECT SIGN(-10);
16. SQRT(x) - Trả về căn bậc hai của x
SELECT SQRT(25);
17. SUM(bieu_thuc) - Trả về tổng của trường được chỉ định
SELECT SUM(diem) FROM diem;
18. TRUNCATE(x,y) - Trả về giá trị x được làm tròn đến y chữ số thập phân (không làm tròn)
SELECT TRUNCATE(1.23456,3);
Hàm chuỗi
1. Trả về mã ASCII của ký tự đầu tiên trong chuỗi s
SELECT ASCII('AB');
2. LENGTH/CHAR_LENGTH(s)/CHARACTER_LENGTH(s) - Trả về độ dài của chuỗi s
SELECT LENGTH('1234');
3. CONCAT(s1,s2…sn) - Nối nhiều chuỗi thành một chuỗi
SELECT CONCAT('xin','chao');
4. FIND_IN_SET(s1,s2) - Trả về vị trí của chuỗi s1 trong chuỗi s2
SELECT FIND_IN_SET("c", "a,b,c,d,e");
5. FORMAT(x,n) - Định dạng số x thành "#,###.##", giữ lại n chữ số thập phân
SELECT FORMAT(250500.5634, 2);
6. INSERT(s1,x,len,s2) - Thay thế chuỗi con trong s1 bằng s2
SELECT INSERT("google.com", 1, 6, "example");
7. LOCATE(s1,s) - Tìm vị trí bắt đầu của s1 trong chuỗi s
SELECT LOCATE('st','myteststring');
8. LCASE(s)/LOWER(s) - Chuyển đổi chuỗi s thành chữ thường
SELECT LOWER('VIETNAM');
9. UCASE(s)/UPPER(s) - Chuyển đổi chuỗi s thành chữ hoa
SELECT UPPER('vietnam');
10. TRIM(s) - Loại bỏ khoảng trắng ở đầu và cuối chuỗi s
SELECT TRIM(' VIETNAM ');
11. LTRIM(s) - Loại bỏ khoảng trắng ở đầu chuỗi s
SELECT LTRIM(' VIETNAM ');
12. RTRIM(s) - Loại bỏ khoảng trắng ở cuối chuỗi s
SELECT RTRIM(' VIETNAM ');
13. SUBSTR/SUBSTRING(s, start, length) - Lấy chuỗi con từ vị trí start với độ dài length
SELECT SUBSTR("VIETNAM", 2, 3);
SELECT SUBSTRING("VIETNAM", 2, 3);
14. POSITION(s1 IN s) - Tìm vị trí của s1 trong chuỗi s
SELECT POSITION('b' in 'abc');
15. REPEAT(s,n) - Lặp lại chuỗi s n lần
SELECT REPEAT('vietnam',3);
16. REVERSE(s) - Đảo ngược chuỗi s
SELECT REVERSE('abc');
17. STRCMP(s1,s2) - So sánh hai chuỗi s1 và s2
SELECT STRCMP("vietnam", "vietnam");
Hàm ngày và giờ
1. CURDATE()/CURRENT_DATE() - Trả về ngày hiện tại
SELECT CURDATE();
SELECT CURRENT_DATE();
2. CURRENT_TIME()/CURTIME() - Trả về thời gian hiện tại
SELECT CURRENT_TIME();
3. CURRENT_TIMESTAMP() - Trả về ngày và giờ hiện tại
SELECT CURRENT_TIMESTAMP();
4. ADDDATE(d,n) - Tính ngày sau khi thêm n ngày vào ngày bắt đầu d
SELECT ADDDATE("2023-12-01", INTERVAL 10 DAY);
5. ADDTIME(t,n) - Thêm n giây vào thời gian t
SELECT ADDTIME('2023-12-01 10:10:10', 5);
6. DATE() - Trích xuất giá trị ngày từ biểu thức ngày hoặc ngày-giờ
SELECT DATE("2023-12-01 10:10:10");
7. DAY(d) - Trả về phần ngày của giá trị ngày d
SELECT DAY("2023-12-01");
8. DATEDIFF(d1,d2) - Tính số ngày giữa d1 và d2
SELECT DATEDIFF('2023-01-01','2023-02-02');
9. DATE_FORMAT - Định dạng ngày d theo biểu thức f
SELECT DATE_FORMAT('2023.12.01 10:10:10','%Y-%m-%d %r');
10. DAYNAME(d) - Trả về tên ngày trong tuần của d
SELECT DAYNAME('2023-12-01 10:10:10');
11. DAYOFMONTH(d) - Tính ngày trong tháng của d
SELECT DAYOFMONTH('2023-12-01 10:10:10');
12. DAYOFWEEK(d) - Tính ngày trong tuần của d (1 = Chủ nhật, 2 = Thứ hai, ...)
SELECT DAYOFWEEK('2023-12-01 10:10:10');
13. DAYOFYEAR(d) - Tính ngày trong năm của d
SELECT DAYOFYEAR('2023-12-01 10:10:10');
14. EXTRACT(type FROM d) - Lấy giá trị được chỉ định từ ngày d
SELECT EXTRACT(MINUTE FROM '2023-12-01 10:15:16');
15. UNIX_TIMESTAMP() - Lấy timestamp
SELECT UNIX_TIMESTAMP('2023-12-01');
Hàm thông tin hệ thống
① version() - Xem thông tin phiên bản MySQL
② connection_id() - Xem số lần kết nối của người dùng hiện tại
Gọi trực tiếp hàm CONNECTION_ID() (không cần tham số) để xem số lần kết nối hiện tại với máy chủ MySQL.
③ processlist - Xem thông tin kết nối của người dùng
Cột Id: Mã định danh người dùng MySQL, được hệ thống tự động phân配 Cột User: Hiển thị "tên người dùng" hiện tại Cột Host: Hiển thị IP thực thi câu lệnh, dùng để truy vết người dùng gặp vấn đề Cột db: Hiển thị cơ sở dữ liệu hiện tại được kết nối Cột Command: Hiển thị lệnh đang thực thi, thường là Sleep, Query, Connect Cột Time: Hiển thị thời gian trạng thái, đơn vị là giây Cột State: Hiển thị trạng thái của câu SQL, bao gồm Copying to tmptable, Sorting result, Sending data Cột Info: Hiển thị nội dung SQL hiện tại
④ database(),schema() - Xem cơ sở dữ liệu đang sử dụng
⑤ user(),current_user(),system_user() - Lấy người dùng hiện tại
⑥ charset() - Sử dụng hàm CHARSET() để trả về bộ ký tự của chuỗi
⑦ collation() - Sử dụng hàm COLLATION() để trả về cách sắp xếp chuỗi
Các hàm thống kê trong MySQL
| Tên hàm | Mô tả |
|---|---|
| COUNT( ) | Trả về tổng số bản ghi thỏa mãn điều kiện SELECT, ví dụ SELECT COUNT(*)… |
| SUM( ) | Trả về tổng của một cột số hoặc biểu thức |
| AVG( ) | Trả về giá trị trung bình của một cột số hoặc biểu thức |
| MAX( ) | Trả về giá trị lớn nhất của một cột số, chuỗi hoặc biểu thức |
| MIN( ) | Trả về giá trị nhỏ nhất của một cột số, chuỗi hoặc biểu thức |
Nhóm kết quả với GROUP BY
Sử dụng GROUP BY để nhóm kết quả truy vấn
Thực hiện thống kê trên tất cả dữ liệu
Có thể nhóm theo nhiều trường, và nhóm lồng nhau
Kết hợp với HAVING để lọc dữ liệu sau khi nhóm
Sử dụng CASE WHEN THEN ELSE END trong SQL
1. Cú pháp cơ bản của CASE WHEN THEN ELSE END
SELECT
CASE
WHEN dieu_kien1 THEN ket_qua1
WHEN dieu_kien2 THEN ket_qua2
...
ELSE ket_qua_mac_dinh
END
FROM bang;
-- Ví dụ
SELECT
ten_nhan_vien,
luong,
CASE
WHEN luong > 50000 THEN 'Luong cao'
WHEN luong > 30000 THEN 'Luong trung binh'
ELSE 'Luong thap'
END AS danh_gia_luong
FROM nhan_vien;
dieu_kien1, dieu_kien2, …: Biểu thức điều kiện, khi thỏa mãn điều kiện nào thì trả về kết_qua tương ứng.
ket_qua1, ket_qua2, …: Kết quả trả về khi thỏa mãn điều kiện.
ket_qua_mac_dinh: Tùy chọn, trả về kết quả mặc định khi không thỏa mãn điều kiện nào.
2. Đánh giá nhiều điều kiện
SELECT
ten_nhan_vien,
luong,
CASE
WHEN luong > 50000 AND vi_tri = 'Ha Noi' THEN 'Luong cao - Ha Noi'
WHEN luong > 50000 AND vi_tri = 'TPHCM' THEN 'Luong cao - TPHCM'
WHEN luong > 30000 THEN 'Luong trung binh'
ELSE 'Luong thap'
END AS danh_gia_luong_vi_tri
FROM nhan_vien;
Bằng cách thêm điều kiện AND vi_tri = 'Ha Noi' và AND vi_tri = 'TPHCM', chúng ta có thể đánh giá mức lương kết hợp với vị trí làm việc.
3. Sử dụng CASE để tính toán
SELECT
ten_nhan_vien,
luong,
CASE
WHEN hieu_suat = 'Xuat sac' THEN luong * 0.2
WHEN hieu_suat = 'Tot' THEN luong * 0.1
ELSE luong * 0.05
END AS thuong_hieu_suat
FROM nhan_vien;
4. Kết hợp CASE với hàm tổng hợp
SELECT
CASE
WHEN luong > 50000 THEN 'Luong cao'
WHEN luong > 30000 THEN 'Luong trung binh'
ELSE 'Luong thap'
END AS muc_luong,
COUNT(*) AS so_nhan_vien
FROM nhan_vien
GROUP BY muc_luong;
Sử dụng CASE WHEN THEN ELSE END để phân loại nhân viên theo mức lương và thống kê số lượng nhân viên trong mỗi nhóm.
5. Câu lệnh CASE lồng nhau
SELECT
ten_nhan_vien,
luong,
CASE
WHEN luong > 50000 THEN
CASE
WHEN hieu_suat = 'Xuat sac' THEN 'Luong cao - Xuat sac'
WHEN hieu_suat = 'Tot' THEN 'Luong cao - Tot'
ELSE 'Luong cao - Trung binh'
END
WHEN luong > 30000 THEN
CASE
WHEN hieu_suat = 'Xuat sac' THEN 'Luong trung binh - Xuat sac'
WHEN hieu_suat = 'Tot' THEN 'Luong trung binh - Tot'
ELSE 'Luong trung binh - Trung binh'
END
ELSE
CASE
WHEN hieu_suat = 'Xuat sac' THEN 'Luong thap - Xuat sac'
WHEN hieu_suat = 'Tot' THEN 'Luong thap - Tot'
ELSE 'Luong thap - Trung binh'
END
END AS danh_gia_tong_hop
FROM nhan_vien;
Chúng ta có thể đánh giá nhân viên dựa trên cả lương và hiệu suất.
6. Lưu ý khi sử dụng CASE WHEN
CASE WHEN THEN ELSE ENDcó thể được sử dụng trong bất kỳ câu lệnh SQL nào, bao gồmSELECT,WHERE,ORDER BY, v.v.- Khi điều kiện đơn giản, có thể sử dụng dạng rút gọn
CASE column_name WHEN value THEN result END.