Bài 03 : Hệ quản trị cơ sở dữ liệu

  

I/Truy vấn đơn giản SELECT … FROM

❖ Cú pháp: 

SELECT tên danh sách các cột 

FROM tên bảng 

[WHERE điều kiện chọn] 

[GROUP BY nhóm] 

[HAVING điều kiện chọn nhóm] 

[ORDER BY Tên cột sắp xếp 

[DESC, ASC]] 

[LIMIT giới hạn số lượng mẩu tin];

❖ Từ khóa DISTINCT sẽ loại bỏ dữ liệu trùng lặp từ câu lệnh SELECT 

❖ Cú pháp: 

SELECT DISTINCT tên danh sách các cột 

FROM tên bảng

❖ Thuộc tính suy diễn (Derived Attribute) 

-    SQL cung cấp khả năng tạo các thuộc tính suy diễn trong bảng kết quả trả về sử dụng các toán tử và hàm dựa trên các thuộc tính có sẵn. 

-    Tên cột của thuộc tính suy diễn phụ thuộc vào hệ thống, tuy nhiên có thể gán bí danh làm tên cột.

1/ Mệnh đề WHERE

❖ Mệnh đề WHERE của câu lệnh SELECT cho phép tìm kiếm theo điều kiện hoặc tiêu chí tìm kiếm là một điều kiện nhất định 
❖ Cú pháp: 
SELECT tên danh sách các cột 
FROM tên bảng 
[WHERE điều kiện chọn]

❖Các phép toán thường gặp trong điều kiện chọn 
-    So sánh: >, >=, <, <=, =, !=, <> 
-    Logic: and, or, not, in, not in, between, like, not like 

❖ Cú pháp: 

SELECT tên danh sách các cột 

FROM tên bảng 

[WHERE điều kiện chọn 1 AND/OR điều kiện chọn 2 …]

❖ Cú pháp: 

SELECT danh sách các cột 

FROM tên bảng 

WHERE cột IN ("giá trị 1","giá trị 2"…)

❖ Cú pháp: 

SELECT danh sách các cột 

FROM tên bảng 

WHERE cột NOT IN ("giá trị 1","giá trị 2"…)

❖ LIKE cho phép thực hiện việc tìm kiếm thông tin dựa trên sự so sánh ký tự (‘giống như’) 

❖ MySQL cung cấp cho hai ký tự đại diện sử dụng với LIKE, đó là % và _ 

-     Ký tự đại diện tỷ lệ phần trăm (%) đại diện cho bất kỳ chuỗi có thể không có hoặc có nhiều ký tự 

-     Gạch dưới (_) chỉ đại diện cho một ký tự duy nhất.

❖ Dùng NOT kèm với LIKE để hàm chứa ý nghĩa phủ định 

❖ Chú ý: Trường hợp chuỗi tìm kiếm của lại bắt đầu bởi một ký tự đại diện, Mysql cung cấp cho ký tự ‘\’ để chỉ ra rằng các ký tự đại diện đi sau đó được sử dụng theo đúng nghĩa đen chứ không còn là ý nghĩa là ký tự đại diện nữa.

❖ Phép toán IS NULL 

❖ Cú pháp: 

SELECT tên danh sách các cột 

FROM tên bảng 

[WHERE điều kiện chọn IS NULL]: tìm các giá trị không xác định

❖ Có thể sử dụng mệnh đề WHERE để liên kết dữ liệu của nhiều bảng trong truy vấn 

❖ Cú pháp: 

SELECT tên danh sách các cột 

FROM Tên_bảng_1, Tên_bảng_2, ... 

WHERE Tên_bảng_1.tên_cột = Tên_bảng_2.tên_cột 

[ORDER BY Tên_cột_sắp_xếp [DESC, …]] 

2/Truy vấn có sắp xếp dữ liệu

❖ Giúp lấy dữ liệu của các cột bên trong bảng đồng thời sắp xếp lại dữ liệu theo thứ tự tăng dần hoặc giảm dần 
❖ Cú pháp: 
SELECT tên danh sách các cột
 FROM tên bảng 
[WHERE điều kiện chọn] 
[ORDER BY Tên cột sắp xếp [DESC, ASC]]

❖ LIMIT cho phép hạn chế các bản ghi trả lại với câu lệnh SELECT 

❖ Cú pháp: 

SELECT * 

FROM table_name 

LIMIT S, N //lấy ra một số lượng N bản ghi nhất định tính từ một vị trí S nào đó 

3/ Sử dụng mệnh đề UNION trong truy vấn

❖ UNION cho phép kết nối dữ liệu của các câu lệnh truy vấn lại với nhau. 
❖ Cú pháp: 
SELECT statement 
UNION [DISTINCT | ALL] 
SELECT statement UNION 
[DISTINCT | ALL] … 

❖ Nguyên tắc: 
-    Số lượng các cột trong mỗi câu lệnh SELECT phải giống nhau. 
-    Các kiểu dữ liệu của cột trong danh sách cột của câu lệnh SELECT phải giống nhau hoặc ít nhất là có thể chuyển đổi sang cho nhau

❖ Ghi chú: 
-    Mặc định UNION loại bỏ tất cả các hàng trùng lặp từ kết quả ngay cả khi không sử dụng từ khoá DISTINCT 
-     Muốn các hàng trùng lặp vẫn còn trong tập hợp kết quả thì sử dụng từ khóa UNION ALL. 
-     Khi sử dụng ORDER BY để sắp xếp kết quả với UNION, phải đặt nó ở vị trí cuối cùng trong mệnh đề SELECT

-    MySQL cũng cung cấp một lựa chọn khác để sắp xếp các kết quả thiết lập dựa trên vị trí cột trong mệnh đề ORDER BY.

-     Nếu tên cột không giống nhau trong hai mệnh đề SELECT của phép UNION, MySQL sẽ sử dụng các tên cột của câu lệnh SELECT đầu tiên làm tên cột đầu ra.

4/ Các hàm cấu trúc điều khiển

❖ Hàm IF:là một hàm điều khiển, trả về kết quả là một chuỗi hoặc số dựa trên một điều kiện cho trước. ❖ Cú pháp: IF (biểu_thức_so_sánh, biểu_thức_1, biểu_thức_2) 
-     Tham số đầu tiên là biểu thức so sánh sẽ được kiểm tra là đúng hay sai. Biểu thức so sánh thì không bằng 0 và không bằng NULL. 
-   Khi biểu thức so sánh đúng thì kết quả trả về là biểu thức 1, ngược lại thì kết quả trả về là biểu thức 2

❖ Hàm IFNULL: kiểm tra giá trị NULL 
❖ Cú pháp: IFNULL(biểu thức 1, biểu thức 2) 
-    Nếu biểu thức 1 khác NULL thì hàm IFNULL có kết quả trả về là biểu thức 1, ngược lại thì kết quả trả về là biểu thức 2 
-    Hàm IFNULL trả về giá trị số hoặc chuỗi tùy thuộc vào nội dung trong các biểu thức. 

❖ Hàm NULLIF: So sánh sự khác biệt 
❖ Cú pháp: NULLIF(biểu thức 1, biểu thức 2) 
-    Nếu biểu thức 1 bằng biểu thức 2 thì hàm NULLIF có kết quả trả về là NULL, ngược lại thì kết quả trả về là biểu thức 1. 

❖ Hàm CASE: thực hiện việc so sánh một giá trị hay một biểu thức với hàng loạt các giá trị khác để đưa về một kết quả thích hợp với giá trị hay biểu thức đã đem so sánh. 

❖ CASE dạng đơn giản: 
-    Cú pháp: 
CASE biểu_thức_giá_trị 
WHEN giá_trị_so_sánh 
THEN kết_quả 
[WHEN giá_trị_so_sánh THEN kết_quả ...] 
[ELSE kết_quả] END 

❖ CASE dạng có điều kiện: 
-    Cú pháp: 
CASE WHEN điều_kiện_1 
THEN kết_quả_1 WHEN điều_kiện_2 
THEN kết_quả_2 ... [WHEN điều_kiện_n-1 
THEN kết_quả_n-1] [ELSE kết_quả_n] 
END

5/Các hàm chuyển đổi kiểu dữ liệu

❖ Hàm CAST 
-    Dùng để chuyển đổi một giá trị hoặc biểu thức sang một kiểu dữ liệu khác. 
-     Kết quả trả về là giá trị hoặc biểu thức với kiểu dữ liệu mới 
❖ Cú pháp: 
CAST(biểu_thức AS kiểu_dữ_liệu) Kiểu dữ liệu có thể là một trong các kiểu sau: BINARY[(N)], CHAR[(N)], DATE, DATETIME, DECIMAL, SIGNED [INTEGER], TIME, UNSIGNED [INTEGER]

❖ Hàm CONVERT 
-    Dạng 1: dùng để chuyển đổi một giá trị hoặc biểu thức sang một kiểu dữ liệu khác. 
-     Kết quả trả về là giá trị hoặc biểu thức với kiểu dữ liệu mới 
-    Cú pháp: CONVERT(biểu_thức, kiểu_dữ_liệu)

❖ Hàm CONVERT 

-    Dạng 2: dùng để chuyển một giá trị hoặc một biểu thức sang một kiểu hiển thị khác. 

-     Kết quả trả về là giá trị hoặc biểu thức dưới dạng hiển thị mới 

-    Kiểu hiển thị có thể là một trong các kiểu sau: utf8, latin1..,latin7, ascii, binary...

-    Cú pháp: CONVERT(biểu_thức USING kiểu_hiển_thị)

6/Các hàm xử lý chuỗi

❖ Hàm CHAR_LENGHT(), CHARACTER_LENGTH() và LENGTH() 
-    Kết quả trả về là chiều dài của chuỗi (str) nhưng theo hai dạng là chiều dài tính theo ký tự (char_length(), character_length()) và chiều dài tính theo byte (length) 
-     Cú pháp: 
CHAR_LENGTH(str) 
CHARACTER_LENGTH(str) 
-    Kết quả trả về số ký tự có trong chuỗi bao gồm cả khoảng trắng

❖ Hàm CHAR_LENGHT(), CHARACTER_LENGTH() và LENGTH() 
-     Cú pháp: LENGTH(str) 
-    Kết quả trả về chiều dài của chuỗi được tính bằng byte

❖ Hàm CONCAT() 
-    Nối hai hoặc nhiều chuỗi thành một chuỗi mới. 
-    Nếu các đối số là số, chúng sẽ được chuyển đổi thành chuỗi trước khi nối. 
-    Nếu bất kỳ đối số trong danh sách đối số là NULL, hàm CONCAT sẽ trả về NULL 
-    Cú pháp: CONCAT(str1,str2,...)

❖ Hàm CONCAT_WS ( ) 
-    Nối hai hay nhiều hơn hai chuỗi với một dấu phân cách được xác định trước. 
-     Cú pháp: CONCAT_WS(chỉ định cách,str1,str2,...)

❖ Hàm LOWER() 
-     Kết quả trả về là một chuỗi sau khi đã chuyển các ký tự trong chuỗi thành chữ thường 
-    Cú pháp: LOWER(str) 

❖ Hàm UPPER() 

-    Kết quả trả về là một chuỗi sau khi đã chuyển các ký tự trong chuỗi thành chữ hoa

-    Cú pháp: UPPER(str)

❖ Hàm LEFT() và RIGHT() 
-     Kết quả trả về là một chuỗi con được trích ra từ chuỗi gốc. Trong đó chuỗi con được trích ra có thể bắt đầu từ bên trái (LEFT()) hay bên phải (RIGHT()) của chuỗi. 
-    Cú pháp :
LEFT(str, số_byte) 
RIGHT(str, số_byte)

❖ Hàm MID() và SUBSTRING() 
-    Hàm Substring cho phép trích xuất một chuỗi con từ một chuỗi khác, bắt đầu tại vị trí cụ thể và với một độ dài nhất định 

-    Cú pháp 1: 
MID(str,pos); 
SUBSTRING(str,pos); 
SUBSTRING(str FROM pos); 

-    Cú pháp 2: 
MID(str,pos,len); 
SUBSTRING(str,pos,len); 
SUBSTRING(str FROM pos FOR len);

❖ Hàm MID() và SUBSTRING() 
-    Chú ý: Nếu sử dụng giá trị âm cho tham số pos, sự bắt đầu của chuỗi con được tính từ cuối của chuỗi.

❖ Hàm REPEAT() 

-    Được dùng để lặp lại nhiều lần một chuỗi. 

-     Kết quả trả về là một chuỗi mới được tạo ra từ chuỗi được lặp lại 

-    Cú pháp: REPEAT(str, số_lần_lặp) 

❖ Hàm REVERSE() 
-    Kết quả trả về là một chuỗi đảo ngược. 
-    Cú pháp: REVERSE(str) 

❖ Hàm REPLACE() 
-    Kết quả trả về là một chuỗi mới sau khi tìm và thay thế một chuỗi con trong chuỗi nguồn bằng một chuỗi khác 
-    Cú pháp: 
UPDATE SET tên cột = REPLACE(tên cột,chuỗi cần tìm, chuỗi thay thế) 
WHERE  <các điều kiện>

❖ Hàm ENCODE(): Dùng để mã hóa một chuỗi
-     Cú pháp: ENCODE(str, khóa) 
    ▪ str: là chuỗi sẽ được mã hóa dưới dạng chuỗi nhị phân. 
    ▪ khóa: là password do chúng ta đặt ra để không cho phép người khác giải mã

❖ Hàm DECODE() dùng để giải mã thông tin đã bị mã hóa. 
❖ Cú pháp: DECODE(str, khóa) 
    ▪ str: là chuỗi đã bị mã hóa. 
    ▪ khóa: là mật khẩu được đặt ra khi tiến hành mã hóa. Không có mật khẩu này thì không thể giải mã

❖ Hàm SPACE() 
-    Kết quả trả về là một chuỗi có N khoảng trắng 
-    Cú pháp: SPACE(N)

❖ Hàm STRCMP() 
-    Dùng để so sánh chính xác hai chuỗi 
-    Kết quả trả về bằng 0 nếu hai chuỗi giống nhau, trả về -1 nếu chuỗi 1 nhỏ hơn chuỗi 2, trả về 1 nếu chuỗi 1 lớn hơn chuỗi 2 
-    Cú pháp: STRCMP(str1, str2)

❖ Hàm CEILING() / CEIL(): dùng để làm tròn số theo cận trên 
-     Kết quả trả về là số nguyên nhỏ nhất có giá trị không nhỏ hơn X 
-    Cú pháp: CEILING(X) CEIL(X) 

❖ Hàm FLOOR() dùng để làm tròn số theo cận dưới 
-     Kết quả trả về là số nguyên lớn nhất có giá trị không lớn hơn X 
-    Cú pháp: FLOOR(X)

❖ Hàm SIGN(): xét dấu của số hay biểu thức’ 
-     Kết quả trả về là 1 nếu số hay biểu thức là số dương, -1 nếu số âm, 0 nếu số bằng 0 
-    Cú pháp: SIGN(số)

7/Các hàm xử lý thời gian

❖ Hàm ADDDATE() và DATE_ADD(): có cùng kết quả trả về là một ngày mới sau khi đã cộng thêm một đơn vị thời gian 
-    Cú pháp 1: 
ADDDATE(ngày, số_ngày) 
DATE_ADD(ngày, số_ngày) 
-    Cú pháp 2: 
ADDDATE(ngày, INTERVAL giá_trị kiểu) 
DATE_ADD(ngày, INTERVAL giá_trị kiểu)

❖ Hàm SUBDATE() và DATE_SUB(): có cùng kết quả trả về là một ngày mới sau khi đã trừ đi một đơn vị thời gian 
-    Cách sử dụng và cú pháp của hai hàm này tương tự như hai hàm ADDDATE() và DATE_ADD()

❖ Hàm CURDATE(), CURRENT_DATE() có kết quả trả về là ngày hiện hành của hệ thống
 Cú pháp: CURDATE() CURRENT_DATE() 
❖ Hàm CURTIME(), CURRENT_TIME() có kết quả trả về là giờ hiện hành của hệ thống. 
Cú pháp: CURTIME() CURRENT_TIME()

❖ Hàm NOW(): có kết quả trả về là ngày giờ hiện hành của hệ thống 
-    Cú pháp: NOW() 
❖ Hàm DATE() có kết quả trả về là ngày-tháng-năm của một biểu thức thời gian bất kỳ 
-     Cú pháp: DATE(biểu thức thời gian)

❖ Hàm MONTH() có kết quả trả về là tháng của một biểu thức thời gian bất kỳ
-     Cú pháp: MONTH(biểu thức thời gian)

❖ Hàm MONTHNAME() có kết quả trả về là tên của tháng (tiếng Anh) của của một biểu thức thời gian bất kỳ 
-     Cú pháp: MONTHNAME(biểu thức thời gian)

❖ Hàm YEAR() có kết quả trả về là năm của một biểu thức thời gian bất kỳ 
-     Cú pháp YEAR(biểu thức thời gian)

❖ Hàm DAY() và DAYOFMONTH() có kết quả trả về là giá trị ngày của một biểu thức thời gian có kiểu ngày/ngày giờ bất kỳ 
-    Cú pháp: 
DAY(biểu thức thời gian) 
DAYOFMONTH(biểu thức thời gian)

❖ Hàm DAYNAME() có kết quả trả về là tên của ngày trong tuần của một biểu thức thời gian có kiểu ngày/ngày giờ bất kỳ 
-    Cú pháp: DAYNAME(biểu thức thời gian)

❖ Hàm DAYOFWEEK(): kết quả trả về là giá trị số tương ứng với ngày trong tuần
-     Kết quả trả về từ 1->7, trong đó 1 tương ứng với ‘Sunday’, 2 tương ứng với ‘Monday’, … 
-    Cú pháp: DAYOFWEEK(biểu thức thời gian)

❖ Hàm DAYOFYEAR() có kết quả trả về là ngày trong năm của một biểu thức thời gian có kiểu ngày/ngày giờ bất kỳ 
-    Cú pháp: DAYOFYEAR(biểu thức thời gian)

❖ Các hàm SECOND(), MINUTE(), HOUR(), TIME() có kết quả trả về là một số nguyên chỉ định giây, phút, giờ và thời gian của một biểu thức thời gian có kiểu giờ:phút:giây hoặc kiểu ngàythángnăm giờ:phút:giây bất kỳ 
-     Cú pháp: 
SECOND(biểu thức thời gian) 
MINUTE(biểu thức thời gian) 
HOUR(biểu thức thời gian) 
TIME(biểu thức thời gian)

❖ Hàm DATEDIFF: có kết quả trả về là khoảng cách đại số giữa hai ngày bất kỳ 
-     Cú pháp: DATEDIFF(expr1,expr2) 
-    Chú ý: Nếu expr1 nhỏ hơn expr2 thì kết quả sẽ là số nguyên âm, ngược lại thì kết quả sẽ là số nguyên dương 

❖ Hàm TIMEDIFF() có kết quả trả về là khoảng cách đại số của hai biểu thức thời gian bất kỳ 
-    Cú pháp: TIMEDIFF(expr1, expr2) 
SELECT TIMEDIFF('23:59:59', '10:44:45')

❖ Hàm ADDDATE: trả về một giá trị thời gian là kết quả của thao tác trên một giá trị thời gian khác.
Ví dụ 1: đưa ra ngày tháng sau ngày giờ hiện tại 30 ngày: 
SELECT ADDDATE(NOW(), INTERVAL 30 DAY);

❖ Hàm EXTRACT: tách ra các giá trị như ngày, tháng, năm từ một giá trị có kiểu thời gian 
Ví dụ: đưa ra tháng của một giá trị thời gian: 
SELECT EXTRACT(MONTH FROM '2004-12-31 23:59:59’);

❖ Hàm LAST_INSERT_ID trả về ID của bản ghi cuối cùng được chèn vào bảng, với điều kiện đó là ID của cột có thuộc tính AUTO_INCREMENT 
SELECT LAST_INSERT_ID();
❖ MySQL LAST_INSERT_ID hoạt động dựa trên nguyên tắc độc lập với client 119 SELECT LAST_INSERT_ID();

8/Truy vấn nhóm

❖ Hàm SUM :Hàm trả về tổng các giá trị theo nhóm
SELECT sum(quantityInStock) FROM products

❖ Hàm AVG :Tính giá trị trung bình của một biểu thức, Nó không chấp nhận giá trị NULL. 
SELECT AVG(buyPrice) average_buy_price FROM Products

❖ Hàm COUNT  Hàm trả về số lượng mẩu tin theo nhóm.
SELECT COUNT(*) AS Total FROM products

❖ Hàm MAX và MIN  Hàm MAX trả về giá trị lớn nhất và hàm MIN trả về giá trị nhỏ nhất của một tập các giá trị.
SELECT MAX(buyPrice) highest_price, MIN(buyPrice) lowest_price FROM Products

9/Mệnh đề nhóm GROUP BY

❖ Nhóm các bản ghi có cùng giá trị tại một hay nhiều cột, thành một tập hợp. 

❖ GROUP BY nếu có thì nó phải đứng sau mệnh đề WHERE hoặc FROM. 

❖ Theo sau từ khoá GROUP BY là một danh sách các biểu thức, phân cách nhau bởi dấu phẩy. 

❖ Cú pháp: 

SELECT Danh sách các cột, các hàm thống kê(biểu thức) 

FROM tên bảng 

WHERE điều kiện lọc 

GROUP BY Danh sách các cột nhóm dữ liệu

[ORDER BY Tên cột sắp xếp [DESC, …]]

10/Mệnh đề điều kiện HAVING

❖ Một điều kiện lọc trên dữ liệu là một nhóm các bản ghi hoặc là kết quả của việc thực hiện hàm nhóm. 
❖ HAVING thường được sử dụng cùng với GROUP BY, điều kiện lọc chỉ được áp dụng trên các cột xuất hiện trong mệnh đề GROUP BY. 
❖ HAVING không đi kèm với GROUP BY có ý nghĩa là WHERE 
❖ Cú pháp: 
SELECT Danh sách các cột, Hàm thống kê [as tên] 
FROM Tên bảng 
[WHERE Điều kiện lọc] 
GROUP BY Danh sách các cột nhóm dữ liệu 
HAVING Điều kiện lọc sau khi nhóm [ORDER BY Tên cột sắp xếp [DESC, …]

11/Truy xuất dữ liệu từ nhiều bảng

a/INNER JOIN

❖ Chỉ định việc so sánh giá trị trong các cột của các bảng là tương đương – dữ liệu đều có ở cả hai bảng 
❖ Kết quả sau khi thực hiện câu lệnh truy vấn kết hợp INNER JOIN là các mẩu tin thỏa điều kiện quan hệ ở cả hai bảng 
❖ Cú pháp: 
SELECT Danh sách các cột 
FROM Bảng 1 INNER JOIN Bảng 2 ON điều kiện nối 1 
    INNER JOIN Bảng 3 ON điều kiện nối 2 ... 
[WHERE Điều kiện lọc] [ORDER BY Danh sách các cột sắp xếp [DESC]];

b/LEFT JOIN

❖ Một tùy chọn của câu lệnh SELECT cho phép lấy thêm dữ liệu từ các bảng khác. 
❖ Chọn tất cả các hàng từ bảng bên trái ngay cả khi không có bản ghi phù hợp với nó trong bảng bên phải 
❖ Cú pháp: 
SELECT Danh sách các cột 
FROM Bảng 1 LEFT JOIN Bảng 2 ON điều kiện nối 1 
    LEFT JOIN Bảng 3 ON điều kiện nối 2 ... 
[WHERE Điều kiện lọc] [ORDER BY Danh sách các cột sắp xếp [DESC]];

c/RIGHT JOIN

❖ Chỉ định việc so sánh giá trị trong các cột của các bảng được ưu tiên cho mối quan hệ bên nhánh phải 
❖ Cú pháp: 
SELECT Danh sách các cột 
FROM Bảng 1 RIGHT JOIN Bảng 2 ON điều kiện nối 1 
    RIGHT JOIN Bảng 3 ON điều kiện nối 2 ... 
[WHERE Điều kiện lọc] [ORDER BY Danh sách các cột sắp xếp [DESC]];

d/SELF JOIN

❖ Một bảng được nối với chính nó, cụ thể khi một bảng có một khóa ngoài tham chiếu tới khóa chính của nó