MySQL Query (phần 3)

Tiếp nối 2 phần trước:

1. BETWEEN

Các câu lệnh BETWEEN được sử dụng để chọn các giá trị trong một hàng.

Cú pháp:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Ví dụ:

SELECT *
FROM NhanVien
WHERE MaNhanVien BETWEEN "NV0003" AND "NV0006";

Câu lênh trên sẽ lấy ra các bản ghi thuộc 1 khoảng có mã nhân viên nằm trong khoảng từ 0003 đến 0006

Kết quả truy vấn:

2. JOINS

Một mệnh đề SQL JOIN được sử dụng để kết hợp các hàng từ hai hoặc nhiều bảng, dựa trên một trường chung giữa chúng.

Cú pháp:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON table1.columnID1 = table2.columnID2;

Ví dụ:

SELECT NhaSanXuat.TenNSX, SanPham.MaNSX, SanPham.MaSP, SanPham.TenSP
FROM NhaSanXuat
JOIN SanPham
ON NhaSanXuat.MaNSX= SanPham.MaNSX
Where NhaSanXuat.MaNSX = 1;

Câu lệnh trên sẽ join 2 bảng nhà sản xuất và bảng sản phẩm với nhau bằng MaNSX (với MaNSX là khóa ngoại của bảng sán phẩm) để lấy ra các sản phẩm từ bảng sản phẩm và tên của nhà sản xuất từ bảng nhà sản xuất và có mã MaNSX là 0001. Kết quả truy vấn:

3. INNER JOIN

INNER JOIN từ khóa chọn tất cả các dòng từ hai bảng miễn là có 1 liên kết giữa các cột ở cả hai bảng.

Cú pháp:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

Ví dụ:

SELECT *
FROM NhaSanXuat
INNER JOIN SanPham
ON NhaSanXuat.MaNSX= SanPham.MaNSX
Where NhaSanXuat.MaNSX = 1;

Câu lệnh trên sẽ lấy ra tất cả các bản ghi mà có mã nhân viên là 0001. Câu lênh INNER JOIN = với câu lệnh JOIN ở trên.

4. LEFT JOIN

LEFT JOIN trả về từ khóa tất cả các hàng từ bảng bên trái (table1), với các hàng tương ứng trong bảng bên phải (table2). Kết quả là NULL ở phía bên phải khi mà nó không match giữa 2 bảng.

Cú pháp:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Ví dụ:

SELECT *
FROM NhaSanXuat
LEFT JOIN SanPham
ON NhaSanXuat.MaNSX= SanPham.MaNSX
Where NhaSanXuat.MaNSX = 2;

Câu lệnh trên sẽ lấy ra tất cả các bản ghi mà có mã nhân viên là 0002. Các từ khóa LEFT JOIN trả về tất cả các hàng từ bảng bên trái (Nha San Xuat), ngay cả khi không có các bản ghi ở bảng bên phải (Sản phẩm).

Lưu ý: Các từ khóa LEFT JOIN trả về tất cả các hàng từ bảng bên trái (table 1), ngay cả khi không có các bản ghi ở bảng bên phải (table 2).

5. RIGHT JOIN

RIGHT JOIN trả về từ khóa tất cả các hàng từ bảng bên phải (table1), với các hàng tương ứng trong bảng bên trái (table2). Kết quả là NULL ở phía bên trái khi mà nó không match giữa 2 bảng.

Cú pháp:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Ví dụ:

SELECT *
FROM NhaSanXuat
RIGHT JOIN SanPham
ON NhaSanXuat.MaNSX= SanPham.MaNSX;

Các từ khóa RIGHT JOIN trả về tất cả các hàng từ bảng bên phải (Nha San Xuat), ngay cả khi không có các bản ghi ở bảng bên trái (Sản phẩm).

Giả sử ở bảng Sản phẩm có 1 sản phẩm không có mã nhà sản xuất thì cau lênh trên vẫn lấy ra sản phẩm đó và cả 1 bản ghi rỗng của bảng nhà sản xuất.

Lưu ý: Các từ khóa RIGHT JOIN trả về tất cả các hàng từ bảng bên phải (table 1), ngay cả khi không có các bản ghi ở bảng bên trái (table 2).

6. FULL JOIN

Các câu lệnh FULL OUTER JOIN trả về từ khóa tất cả các hàng từ bảng bên trái (table1) và từ bảng bên phải (table2).

Các OUTER FULL JOIN từ khóa kết hợp các kết quả của cả hai bên trái và phải tham gia.

Cú pháp:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Ví dụ:

SELECT *
FROM  NhaSanXuat
FULL  JOIN SanPham
ON NhaSanXuat.MaNSX= SanPham.MaNSX;

Các từ khóa FULL JOIN chỉ trả về các bản ghi khi mà ở các bản ghi của cả 2 bảng đều có mã nhà sản xuất. Giả sử ở bảng Sản phẩm có 1 sản phẩm không có mã nhà sản xuất thì câu lênh trên không lấy ra sản phẩm đó và cả 1 bản ghi rỗng của bảng nhà sản xuất.

7. UNION

The UNION được sử dụng để kết hợp 2 hay nhiều lệnh SELECT.

Cú pháp:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Hoặc:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Ví dụ:

SELECT TenSP FROM SanPham
UNION ALL
SELECT TenNSX FROM NhaSanXuat;

Câu lệnh trên sẽ lấy ra toàn bộ tên sản phẩm có trong bảng sản phẩm và tên của nhà sản xuất có trong bảng nhà sản xuất

8. UNIQUE và PRIMARY KEY

Ràng buộc UNIQUE xác định duy nhất cho mỗi bản ghi trong một bảng cơ sở dữ liệu. Các UNIQUE và PRIMARY KEY cung cấp một đảm bảo cho tính duy nhất cho một cột hay tập hợp các cột.

Một ràng buộc PRIMARY KEY tự động có một ràng buộc UNIQUE xác định trên nó.

Lưu ý rằng bạn có thể có nhiều ràng buộc UNIQUE cho mỗi bảng, nhưng chỉ có một ràng buộc PRIMARY KEY cho mỗi bảng.

Ví dụ:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

Câu lệnh trên tạo ra 1 bảng có tên là Persons với P_Id là khóa chính và nó có tính duy nhất, nghĩa là: chỉ có duy nhất 1 P_Id để xác định cho người đó, các ID không được trùng nhau. Ví dụ: trong DB đã có 1 ID là 1234 thì ta sẽ không thể thêm vào bảng Person 1 bản ghi có ID là 1234 vì P_Id là duy nhất.

Một FOREIGN KEY trong một table chỉ một khóa chính của một bảng khác.

Cú pháp:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

Ví dụ:

CREATE TABLE SanPham
(
MaSP int NOT NULL,
MaNSX int NOT NULL,
TenSP varchar(50),
PRIMARY KEY (MaSP ),
FOREIGN KEY (MaNSX) REFERENCES NhaSanXuat(MaNSX)
)

Câu lệnh trên sẽ tạo ra 1 bảng là sản phẩm có khóa chính là MaSP và khóa ngoại là MaNSX, trong đó MaNSX sẽ liên kết với MaNSX có trong bảng nhà sản xuất

9. CHECK

Các ràng buộc CHECK được sử dụng để giới hạn phạm vi giá trị mà có thể được đặt trong một cột.

Nếu bạn định nghĩa một ràng buộc CHECK trên một cột duy nhất nó cho phép chỉ có giá trị nhất định cho cột này.

Nếu bạn định nghĩa một ràng buộc CHECK trên một bảng nó có thể giới hạn các giá trị trong cột nhất định dựa trên các giá trị trong các cột khác ở hàng.

Cú pháp:

CREATE TABLE table_name
(
table_Id int NOT NULL,
column 1 varchar(255) NOT NULL,
column 1 varchar(255),
....
CHECK (table_Id >0)
)

Ví dụ:

CREATE TABLE TinTuc
(
MaTin int NOT NULL,
ChuDe varchar(200) NOT NULL,
NoiDung  longtext,
NguoiDang varchar(50),
NgayDang date,
PRIMARY KEY (MaTin),
CHECK (MaTin >0 )
)

Câu lệnh trên sẽ tạo ra 1 bảng Tin tức có khóa chính là mã tin và có kiểu là kiểu integer, lúc này nếu chỉ có là kiểu integer thì ta có thể nhập mã tin với kiểu số âm, nhưng khi sử dụng thêm câu lệnh với ràng buộc CHECK thì ta không thể nhập mã tin với kiểu số âm được nữa, ở đây mã tin phải luôn dương (>0). Ràng buộc Check cho phép kiểm tra dữ liệu nhập phải đúng với điều kiện đã cho.

10. CREATE INDEX

Lệnh CREATE INDEX được sử dụng để tạo ra các chỉ số trong bảng.

Chỉ số cho phép các ứng dụng cơ sở dữ liệu để tìm dữ liệu nhanh chóng, mà không đọc toàn bộ bảng.

Một chỉ số có thể được tạo ra trong một bảng để tìm kiếm dữ liệu một cách nhanh chóng và hiệu quả hơn.

Người sử dụng không thể nhìn thấy các chỉ số, chúng chỉ được sử dụng để tăng tốc độ tìm kiếm / truy vấn.

Lưu ý :. Đang cập nhật một bảng với chỉ số mất nhiều thời gian hơn so với việc cập nhật một bảng mà không có (vì các chỉ số cũng cần một bản cập nhật) Vì vậy, bạn chỉ nên tạo chỉ mục trên các cột (và bảng) mà sẽ thường xuyên tìm kiếm chống lại.

Cú pháp:

CREATE INDEX index_name
ON table_name (column_name)

Ví dụ:

CREATE INDEX PIndex
ON NguoiDung(HoTen)

Câu lệnh trên sẽ tạo chỉ số cho cột Họ tên của bảng Người Dùng, lúc này tương ứng với mỗi 1 bản ghi của trường họ tên sẽ là 1 số tương ứng, khi cần truy vấn để lấy ra họ tên của người dùng thì nó sẽ truy vấn nhanh hơn.

Thông thường 1 câu truy vấn lấy ra bản ghi có đánh index sẽ nhanh hơn 1 câu truy vấn có index khoảng 20 lần.

11. AUTO INCREMENT

Auto increment có thường được sử dụng với primary key để tự động tạo ra trường được gán auto increment khi có thêm 1 bản ghi mới

Cú pháp:

CREATE TABLE table_name
(
ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ID)
)

Ví dụ:

CREATE TABLE NguoiDung
(
ID int NOT NULL AUTO_INCREMENT,
Hoten varchar(30),
Diachi varchar(45),
Email varchar(45),
SDT varchar(30),
PRIMARY KEY (ID)
)

Câu lệnh trên sẽ tạo ra 1 bảng người dùng với trường ID là tự động tăng, nghĩa là khi ta sử dụng câu lệnh insert để thêm dữ liệu cho bảng này thì ta không cần quan tâm tới trường ID này nữa, bởi vì khi ta thêm mới 1 bản ghi vào bảng người dùng trường ID sẽ được tự động đánh số.

Insert into nguoidung(hoten, diachi,emai, SDT) values(“nhung nguyễn”, “framgiaVN”, “nhung@mail.com”, “0989789485”)

Khi insert dữ liệu = câu lệnh trên sẽ tạo ra được 1 bnar ghi mới có ID được tự động đánh số là 1.

12. VIEW

Trong SQL, một View là một bảng ảo dựa trên kết quả tập hợp các câu lệnh SQL.

Một view có chứa các hàng và cột, giống như một bảng thực. Các trường trong view là các trường lấy từ một hoặc nhiều bảng thực sự trong cơ sở dữ liệu.

Bạn có thể thêm chức năng SQL, WHERE, và JOIN và trình bày các dữ liệu như dữ liệu được đưa tới từ một bảng duy nhất.

Cú pháp:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Ví dụ:

CREATE VIEW `soluongsanphamtrongkho` AS
SELECT TenNSX, TenSP
FROM nhasanxuat
JOIN sanpham
ON sanpham.MaNSX = nhasanxuat.MaNSX
WHERE Dongia> 120000;

Câu lệnh trên sẽ tạo ra 1 view số lượng sản phẩm trong kho cho phép ta xem dữ liệu của 2 trường là tên nhà sản xuất và tên sản phẩm với điều kiện là đơn giá của sản phẩm lơn hơn 120000;

14. Trigger

Trigger là một đối tượng của cơ sở dữ liệu bao hồm tập các lệnh SQL kết hợp với nhau, nhưng Trigger phải được gắn liền với một bảng (Table) nào đó và được tự động thực thi khi xảy ra một trong các thao tác (Insert, Update, Delete) làm thay đổi dữ liệu của bảng

Cú pháp:

CREATE TRIGGER Ten_Trigger
ON Ten_Bang
FOR {[INSERT] | [UPDATE] | [DELETE]}
AS
BEGIN
 Các câu lệnh của Trigger
END

Ví dụ:

CREATE TRIGGER trig_tinhtuoi
  before  UPDATE
   ON nhanvien
for each row
BEGIN
  UPDATE nhanvien
  SET NgaySinh = YEAR(NOW())-year(NgaySinh) WHERE 1=1;
END;

Câu lệnh trên sẽ tạo ra 1 trigger thực hiện nhiệm vụ update toàn bộ bảng nhanvien với trường ngày sinh sẽ được update thành tuổi cuả nhân viên đó à không phải ngày tháng năm sinh như lúc ban đầu nữa.

//end