Ngày đăng: 2024-07-22 12:57:18

Mục lục:

1. Cài đặt Mysql và thăm quan PHPMyAdmin 2. Mysql Table Types & Storage Engines 3. MySQL Data Types 4. Lệnh tạo Database (Create Database) 5. Lệnh tạo Bảng (Create Table) 6. Tạo khóa chính 7. Tạo Unique 8. AUTO_ INCREMENT 9. Khóa ngoại (Foreign Key) 10. Thay đổi cấu trúc bảng ( Alter Table) 11. Thêm dữ liệu vào bảng ( insert table) 12. Lệnh SELECT lấy dữ liệu trong MySQL 13. WHERE IN - WHERE LIKE trong MySQL 14. Sắp xếp kết quả với lệnh Order By 15. Giới hạn kết quả trả về với LIMIT 16. Phép tích đề cát 17. Sử dụng AS (Alias) 18. Gộp kết quả với toán tử UNION 19 . Select trong khoảng với BETWEEN 20. Hàm nối chuỗi Concat 21. INNER JOIN 22. Left Join và Right Join 23. Group By 24. Truy vấn con (Sub Query)

1. Cài đặt MySQL và tham quan PHPMyAdmin

A. Cài đặt

Để cài đặt MySQL rất đơn giản bạn chỉ cần download một Web Server ảo. Hiện nay có rất nhiều chương trình tạo máy chủ ảo như:

• Vertrigo
• XAMPP
• WAMPP
• ..

Với tôi thì tôi sẽ chọn Vertrigo và tôi cũng có một bài hướng dẫn rồi nên theo tôi bạn cũng nên cài đặt nó luôn cho tiện.

B.Thăm quan PHPMyAdmin

Sau khi cài đặt xong bạn vào trình duyệt gõ đường dẫn sau: http://localhost/phpmyadmin/ và sẽ xuất hiện một ô đăng nhập như sau: 

Tại đây nếu bạn cài Vertrigo thì bạn sẽ nhập username là root và password là vertrigo. Với các phần mềm khác thì tùy thuộc vào password mà bạn đã đặt, riêng đối với username vẫn là root.

Sau khi đăng nhập vào bạn sẽ thấy một giao diện tổng quát như sau:

Chúng ta quan tâm đến hai vị trí mà tôi đã bôi đỏ:

• Danh sách data: Là danh sách các database hiện có trong hệ thống
• Các tiện ích sử dụng: Là các chức năng tiện ích như SQL là mở trình editor, Exporter là import dữ liệu, Exporter là hơi xuất dữ liệu. Chức năng này tùy thuộc vào giao diện hiện tại mà hiển thị những tool khác nhau.

C. Viết câu truy vấn trong PHPMyAdmin

Để viết câu truy vấn trong PHPMyAdmin thì bạn click vào chức năng (tab) SQL và dán code hoặc nhập code  vào, sau đó click vào nút Execute để thực thi.

2. MySQL Table Types & Storage Engines

A. Các kiểu table trong MySQL.

MyISAM

MyISAM là phần mở rộng cho công cụ lưu trữ ISAM cũ, các bảng được lưu trữ dưới dạng MyISAM được tối ưu hóa cho tốc độ nén. Kích thước của MyISAM có thẻ lên tới 256TB, rất là lớn phải không các bạn. Ngoài ra bảng kiểu MyISAM có thể được nén và chỉ đọc để tăng tính truy vấn lấy dữ liệu. Lúc MYSQL khởi động sẽ kiểm tra các bảng có kiểu MyISAM nếu bị lỗi thì nó sẽ fix lôi luôn, nên nếu ban lưu trữ cách này sẽ không an toàn.

Thông thường các bảng như tin tức, chuyên mục tin tức, sản phẩm, ... thì chúng ta sử dụng kiểu MyISAM để tốc độ truy vấn nó nhanh hơn. Các phiên bản bé hơn 5.5 thì MyISAM là kiểu bảng mặc định khi bạn tạo mới một table, kể từ bản 5.5 trở đi thì mặc định nó sẽ lấy kiểu InnoDB.

InnoDB

Các table InnoDB có đầy đủ các tính năng ACID-compliant và transaction và nó cũng được tối ưu hóa cho tính perormance. Bảng InnorDB hỗ trợ khóa ngoại (Foreign Key), commit, rollback trong transaction. Kích thước của InnoDB có thể lên tới 64TB, con số này khá nhỏ so với bảng MyISAM phải không nào.

Cũng như MyISAM lúc MySQL khởi động nó cũng kiểm tra các table nếu có lỗi thì sẽ báo hoặc tự fix (nếu cần thiết).

MERGE

Bảng MERGE là bảng ảo, là sự kết hợp của nhiều bảng MyISAM có cấu trúc tương tự và đưa chúng vào một bảng duy nhất, chính vì vậy kiểu MERGE ta có thể hiểu nó có quy tắc giống như MyISAM . Các bảng MERGE không có chỉ số indexes riêng của mình mà nó sử dụng indexes của các bảng con.

Sử dụng bảng MERGE bạn có thể tăng tốc độ hiệu suất trong truy vấn tham gia nhiều bảng. MYSQL chỉ cho phép bạn thực hiện các thao tác SELECT, INSERT, UPDATE, DELETE trên bảng đó, nếu bạn DROP một table nằm trong danh sách table của bảng MERGE thì chỉ có các đặc điểm riêng của nó bị loại bỏ và các bảng bên dưới sẽ không bị ảnh hưởng.

Memory

Bảng memory được lưu trữ trong bộ nhớ và sử dụng chỉ số băm để có được tốc độ nhanh hơn các bảng MyISAM. Vòng đời sống của dữ liệu phụ thuộc vào thời gian sống của Database Server. Ta có thể hiểu store engine memory giống như bộ nhớ HEAP.

Archive

Archive là công cụ lưu trữ cho phép bạn lưu trữ số lượng lớn các bản ghi mà mục đích chỉ để lưu trữ và được chuyển sang định dạng nén để giảm bớt không gian lưu trữ. Nó sử dụng thư viện zlib để nén dữ liệu.

Các bảng lưu trữ chỉ cho phép thực hiện hai thao tác là INSERT và SELECT và nó không hỗ trợ chỉ mục indexes, chính vì vậy khi bạn thực hiện lệnh select nó sẽ đọc toàn bộ bảng nên tốc độ sẽ chậm.

CSV

CSV là phần mở rộng của Excel phải không nhỉ? Trong PHP thường ta sử dụng phần header có phần mở rộng là CSV để chuyển thành file Excel. Nhưng trong MYSQL thì với kiểu này nó sẽ lưu các fields dữ liệu sẽ được ngăn cách nhau bởi dấu phẩy, đây cũng là cách lưu trữ rất giống với NO-SQL

CSV table không hỗ trợ dữ liệu NULL và khi bạn truy vấn đọc (select) thì nó sẽ duyệt toàn bộ bảng giống như Archive.

FEDERATED

FEDERATED storage engine cho phép bạn quản lý dữ liệu máy chủ từ xa mà không cần sử dụng cluster. Các table Federated local sẽ không có dữ liệu mà khi bạn truy vấn dữ liệu thì dữ liệu sẽ được kéo về bảng này.

3. MySQL Data Types

A. Kiểu dữ liệu số (Numeric Data Types)

Trong MySQL có các kiểu dữ liệu dạng số như số nguyên (nguyên âm, nguyên dương). Ngoài ra MySQL còn hỗ trợ lưu trữ kiểu dữ liệu dạng BIT, và bạn không thể lưu trữ một chuỗi cho kiểu dữ liệu số. Sau đây là danh sách tất cả các kiểu dữ liệu dạng số trong MySQL. 

 

Kiểu dữ liệu	Mô tả
TINYINT	Kích thước rất nhỏ
SMALLINT	Kích thước nhỏ
MEDIUMINT	Kích thước vừa
INT	Kích thước chuẩn
BIGINT	Kích thước lớn
DECIMAL	Số cố định
FLOAT	Số có dấu chấm động
DOUBLE	Số có dấu chấm động lớn hơn
BIT	Kiểu BIT

B. Kiểu chuỗi (String)

Khi khai báo kiểu dữ liệu chuỗi thì đồng nghĩa với việc bạn có thể lưu trữ một chuỗi, một chuỗi ở đây chính là một dãy các kí tự các chữ cái hoặc các con số và các kí tự đặc biệt. Sau đây là một số kiểu dữ liệu chuỗi.

Kiểu dữ liệu	Mô tả
CHAR	Có chiều dài cố định và không ở dạng nhị phân
VARCHAR	Có chiều dài và ta phải xác định chiều dài cho nó, không ở dạng nhị phần
BINARY	Chuỗi nhị phân có độ dài cố định
VARBINARY	Chuỗi nhị phân có độ dài thay đổi
TINYBLOB	BLOB rất nhỏ
BLOB	BLOB nhỏ
MEDIUMBLOB	BLOB vừa
LONGBLOB	BLOB lớn
TINYTEXT	Chuỗi không nhị phân rất nhỏ
TEXT	Chuỗi không nhị phân nhỏ
MEDIUMTEXT	Chuỗi không nhị phân trung bình
LONGTEXT	Chuỗi không nhị phân lớn
ENUM	enumeration
SET	Bộ

C. Kiểu ngày tháng (Date and Date time)

MySQL cũng cung cấp các kiểu dữ liệu liên quan đến thời gian nhằm giúp ta lưu trữ ngày tháng và thời gian dễ dàng hơn. Khi lưu ở dạng này bạn có thể truy vấn dữ liệu theo từng phần riêng lẻ như theo ngày, theo tháng và theo năm, hay thậm chi là truy vấn kết hơp cả ngày tháng năm.

Nếu bạn muốn lưu trữ ngày, tháng và năm thì chọn kiểu Date, lưu trữ có thêm giờ phút giây thì Datetime và lưu trữ chỉ giờ phút giây thì chọn time. Ngoài ra có thêm kiểu timestamp dùng lưu trữ thời gian khi record được thêm vào. Sau đây là danh sách các kiểu dữ liệu trong MYSQL:

Kiểu dữ liệu	Mô tả
DATE	Kiểu ngày tháng, định dạng YYYY-MM-DD
TIME	Kiểu thời gian, định dạng HH:MM:SS
DATETIME	Kiểu ngày tháng có thời gian, định dạng YYYY-MM-DD HH:MM:SS
TIMESTAMP	Kiểu ngày tháng có thời gian, định dạng YYYY-MM-DD HH:MM:SS
YEAR	Kiểu năm, định dạng YYYY

D. Các loại dữ liệu không gian (Spatial)

Khi làm việc với các ứng dụng web thì các loại dữ liệu trên kia là quá đủ rồi, bạn không cần phải tìm hiểu các kiểu dữ liệu dưới đây làm gì. Tuy nhiên tôi vẫn liệt kê ra để các bạn tham khảo cho biết.

MySQL có hỗ trợ nhiều loại kiểu dữ liệu không gian liên quan đến đối tượng điểm, tọa độ và địa lý sau:

Kiểu dữ liệu	Mô tả
GEOMETRY	Giá trị không gian của bất kì loại nào
POINT	Một cặp điểm tọa độ X:Y
LINESTRING	Một đường cong
POLYGON	Một đa giác
GEOMETRYCOLLECTION	Một dãy các GEOMETRY
MULTILINESTRING	Một dãy các đường cong
MULTIPOINT	Một day các điểm
MULTIPOLYGON	Một dãy các đa giác

4. Lệnh tạo database (Create Database)

A. Lệnh tạo Database - Create Database

Để tạo mới một Database thì chúng ta sử dụng cú pháp như sau:

CREATE DATABASE [IF NOT EXISTS] database_name

Trong đó:

• CREATE DATABASE: là từ khóa tạo database
• [IF NOT EXISTS]: Nếu có dòng này thì khi chạy nếu bảng đã tồn tại thì nó không báo lỗi (bỏ cặp dấu ngoặc)

Ví dụ

CREATE DATABASE SINHVIEN; /*OR*/ CREATE DATABASE IF NOT EXISTS SINHVIEN;

B. Sử dụng CHARACTER SET và COLLATE

Để thiết lập Charset và Collate cho database thì chúng ta sử dụng cú pháp sau:

Cú pháp

CREATE DATABASE IF NOT EXISTS database_name CHARACTER SET 'charset_name' COLLATE 'collateName'

 

Ví dụ

CREATE DATABASE IF NOT EXISTS SINHVIEN CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'

Thông thường chúng ta sử dụng charset là UTF-8 và Collate là utf8_unicode_ci để khi nhập tiếng Việt không bị lỗi font.

C. Lệnh sử dụng database (use database)

Trong MySQL để chọn một database nào đó ta sẽ sử dụng cú pháp sau:

Cú pháp

USE database_name

Ví dụ

USE SINHVIEN

D. Xóa database (Drop Database)

Để xóa database ta sử dụng cú pháp: DROP DATABASE db_name.

Ví dụ

DROP DATABASE SINHVIEN

5. Lệnh tạo bảng (Create Table) 

A. Lệnh tạo 

Trước tiên chúng ta cần sử dụng lệnh tạo database để tạo mới một database, sau đó sử dụng lệnh USE để chọn database này.

CREATE DATABASE QLSV; USE QLSV;

Để tạo mới một bảng ta sử dụng cú pháp sau:

CREATE TABLE [IF NOT EXISTS] table_name(         /*column_list*/ ) ENGINE=table_type

Trong đó:

• CREATE TABLE: là từ khóa tạo bảng
• [IF NOT EXISTS]: dòng này có thể có hoặc không, ý nghĩa của nó là nếu table này chưa tồn tại thì thực hiện tạo table, còn nếu tồn tại rồi thì không tạo.
• column_list: là danh sách các fields, ví dụ như TenSV, MaSV, ... Mỗi field gồm hai thông số là tên field và kiểu dữ liệu cho nó, nếu có nhiều field thì sẽ dùng dấu phảy (,) để ngăn cách.
  Ví dụ: TenSV varchar(255)
• ENGINE=table_type: Là kiểu engine của bảng này.

Ví dụ: Cho bảng SINHVIEN gồm có các thông tin sau:

• TenSV: Tên sinh viên, kiểu varchar và chiều dài tối đa 255 ký tự
• MaSV: Mã sinh viên, kiểu INT và chiều dai là tối đa 11 số
• NamSinh: Năm sinh, kiểu INT và chiều dài tối đa là 4 số

Lệnh tạo bảng như sau:

USE QLSV;   CREATE TABLE SINHVIEN(     TenSV VARCHAR(255),     MaSV INT(11),     NamSinh INT(4) ) ENGINE = InnoDB

B. Gán giá trị mặc định cho table

Giá trị mặc định tức là khi bạn thêm một record mà bạn không nhập dữ liệu vào thì nó sẽ lấy giá trị mặc định làm giá trị. Để gán giá trị mặc định thì ta sẽ dùng từ khóa DEFAULT đằng sau mỗi field.

 

Ví dụ

USE QLSV;   CREATE TABLE SINHVIEN(     TenSV VARCHAR(255) DEFAULT 'noname',     MaSV INT(11),     NamSinh INT(4) ) ENGINE = InnoDBsinhvien

C. Thiết lập Null và Not Null cho Column

Nếu bạn muốn một column bắt buộc nhập dữ liệu khi thêm mới thì bạn sẽ sử dụng từ khóa NOT NULL. Ngược lại thì bạn sẽ dùng từ khóa NULL (mặc định là NULL).

Vi dụ

USE QLSV;   CREATE TABLE SINHVIEN(     TenSV VARCHAR(255) DEFAULT 'noname',     MaSV INT(11) NOT NULL,     NamSinh INT(4) ) ENGINE = InnoDBsinhvien<br><br><br><br>

D. Xóa bảng (DROP TABLE)

Để xóa bảng ta sử dụng cú pháp: DROP TABLE tb_name.

Ví dụ

DROP TABLE users

khi bạn chạy lệnh này thì bảng sẽ được xóa khỏi database, đương nhiên toàn bộ dữ liệu nằm trong bảng cũng bị xóa theo.

6. Tạo khóa chính 

A. Đặc điểm của khóa chính (Primary key)

Giả sử chúng ta có bảng SINHVIEN và mỗi sinh viên là một record, lúc này để nhận phân biệt giữa các sinh viên thì ta không thể dựa vào tên của sinh viên được vì tên sinh viên có thể trùng nhau. Chính vì vậy ta chọn giải pháp là ta thêm một field MaSV dùng để làm khóa chính và giá trị của nó là duy nhất nên có thể phân biệt giữa các sinh viên với nhau.

Sau đây là một số đặc điểm của khóa chính trong MySQL nói chung và trong các mô hình CSDL nói riêng:

• Giá trị của khóa chính là duy nhất cho mỗi record, nghĩa là không thể tồn tại hai record trùng khóa chính
• Khóa chính không thể cho phép NULL .
• Mỗi bảng chỉ tồn tại duy nhất một khóa chính, mỗi khóa chính có thể có nhiều column.

Bởi vì trong MySQL hoạt động với kiểu số sẽ nhanh hơn kiểu chuỗi nên ta sẽ chọn kiểu INT (INT, BIGINT, ..), sau khi chạy code tạo khóa chính thì nó sẽ tạo một chỉ mục có tên là PRIMARY và đây chính là chỉ mục khóa chính.

B. Tạo khóa chính (Primary key) trong MySQL

Thông thường chúng ta có hai cách tạo khóa chính đó là tạo luôn trong quá trình tạo bảng (create table) hoặc là tạo bảng trước rồi tạo khóa chính sau. Sau đây là các ví dụ về hai cách này.

Tạo trong lệnh tạo bảng Create Table

Để chọn field cho khóa chính ta sẽ đặt từ khóa primary key đằng sau column đó, ví dụ:

CREATE TABLE users (   id INT(10) UNSIGNED NOT NULL PRIMARY KEY,   email VARCHAR(70) NOT NULL ) ENGINE = INNODB;

Hoặc ta sẽ dùng lệnh PRIMARY KEY(field)  đặt ở cuối các fields trong lệnh tạo bảng, ví dụ:

CREATE TABLE users (   id INT(10) UNSIGNED NOT NULL,   email VARCHAR(70) NOT NULL,   PRIMARY KEY(id) ) ENGINE = INNODB;

Chọn nhiều fields làm khóa chính

Để chọn nhiều fields làm khóa chính thì ta sử dụng lệnh PRIMARY KEY(field1, field2, ..) và đặt nó ở cuối danh sách các field trong lệnh tạo bảng, ví dụ:

CREATE TABLE users (   id INT(10) UNSIGNED NOT NULL,   email VARCHAR(70) NOT NULL,   PRIMARY KEY(id, email) ) ENGINE = INNODB;

 

 Sử dụng lệnh alter để tạo khóa chính

Thông thường cách này sử dụng trong trường hợp ta tạo bảng trước rồi sau đó tạo khóa chính sau. Ta sẽ sử dụng cú pháp sau để tạo:

ALTER TABLE table_name ADD PRIMARY KEY(primary_key_column);

Ví dụ:

CREATE TABLE users (   id INT(10) UNSIGNED NOT NULL,   email VARCHAR(70) NOT NULL ) ENGINE = INNODB;   ALTER TABLE users ADD PRIMARY KEY(id)

 

Sử dụng CONSTRAINT đặt tên cho khóa chính

Cách này khuyến khích sử dụng khi sử dụng lệnh CONSTRAINT chúng ta có thể tạo tên cho khóa chính, nhờ đó sau này ta sẽ dễ quản lý hơn. Sau đây là một số ví dụ sử dụng CONSTRAINT để tạo khóa chính:

 

CREATE TABLE users (   id INT(10) UNSIGNED NOT NULL,   email VARCHAR(70) NOT NULL,   CONSTRAINT pk_user PRIMARY KEY(id) ) ENGINE = INNODB;

Hoặc:

CREATE TABLE users (   id INT(10) UNSIGNED NOT NULL,   email VARCHAR(70) NOT NULL ) ENGINE = INNODB;   ALTER TABLE users ADD CONSTRAINT pk_user PRIMARY KEY (id)

 

C. Xóa khóa chính (Drop Primary Key)

Để xóa khóa chính thì chúng ta sử dụng lệnh ALTER TABLE kết hợp với DROP, ví dụ:

ALTER TABLE users DROP PRIMARY KEY

7. Tạo Unique 

A. Unique trong MySQL

UNIQUE thực chất là một index KEY nên khi bạn thiết lập một field nào đó là UNIQUE thì việc truy vấn dữ liệu trên field đó sẽ nhanh hơn. Nó được dùng trong trường hợp bạn muốn tạo thêm một field có giá trị là duy nhất.

Ví dụ trong bảng Users gồm các fields như sau: Users(id, username, email) thì với id ta sẽ chọn làm khóa chính. Nhưng mà tôi muốn email và username của người dùng phải là duy nhất, nghĩa là người dùng không được sử dụng một email hoặc username để đăng ký nhiều tài khoản. Để giải quyết thì ta sẽ thiết lập cho hai fields này là UNIQUE.

Như vậy sự khác biệt giữa UNIQUE và Primary Key là:

•  Primary Key có cấp cao hơn UNIQUE, nó  thường dùng để phân biệt giữa các record.
• Trong mỗi bảng chỉ có có một Primary Key, còn UNIQUE thì có thể có nhiều

B. Tạo UNIQUE trong MySQL

Để tạo UNIQUE ta có hai cách, cách thứ nhất là tạo trực tiếp trong lệnh CREATE TABLE và cách thứ hai là sử dụng lệnh ALTER TABLE.

Tạo trong lệnh tạo bảng

Chúng ta sẽ thêm từ khóa UNIQUE vào field mà ban muốn thiết lập nó là UNIQUE.

 

CREATE TABLE Users(    id INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(50) NOT NULL UNIQUE,    email VARCHAR(50) NOT NULL UNIQUE );

 

Hoặc ta có thể thêm nó ở dưới danh sách các fields (giống Primary Key).

 

CREATE TABLE Users(    id INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(50) NOT NULL,    email VARCHAR(50) NOT NULL,    UNIQUE (username),    UNIQUE (email) );

Sử dụng Alter Table để tạo

Trong trường hợp này chúng ta sẽ tạo bảng trước, sau đó sử dụng lệnh Alter Table để thêm UNIQUE.

 

CREATE TABLE Users(    id INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(50) NOT NULL,    email VARCHAR(50) NOT NULL );   ALTER TABLE Users ADD UNIQUE(username); ALTER TABLE Users ADD UNIQUE(email);

 

Sử dụng CONSTRAINT để tạo tên cho UNIQUE

Cách này khuyến khích sử dụng vì mỗi index chúng ta nên đặt cho nó một cái tên để sau này muốn thực hiện thao tác delete unique thì sẽ dựa vào tên constraint đó.  Ví dụ:

 

CREATE TABLE Users(    id INT PRIMARY KEY AUTO_INCREMENT,    username VARCHAR(50) NOT NULL,    email VARCHAR(50) NOT NULL,    CONSTRAINT user_unique UNIQUE (username),    CONSTRAINT email_unique UNIQUE (email) );

Hoặc:

ALTER TABLE Users ADD CONSTRAINT user_unique UNIQUE (username)

C. Xóa (Drop) Unique trong MySQL

Sau khi tạo UNIQUE xong nếu bạn muốn bỏ nó đi thì sẽ sử dụng lệnh ALTER TABLE kết hợp với DROP. Tuy nhiên để xóa UNIQE nào thì chúng ta phải biết được tên của nó, vì vậy ta nên dùng CONSTRAINT để tạo unique nhé. Sau đây là ví dụ xóa UNIQUE.

 

ALTER TABLE Users DROP INDEX user_unique

Các bạn lưu ý chữ thường chính là các chỗ bạn sẽ đổi còn chữ hoa là các keyword nên không được đổi nhé.

8. AUTO_INCREMENT

A. AUTO_INCREMENT là gì?

AUTO_INCREMENT nói nôm na có nghĩa là tăng tự động, có nghĩa là nếu bạn thiết lập một field nào đó là tăng tự động thì khi bạn thêm record mới bạn không cần phải truyền data cho nó và nó sẽ tự lấy giá trị lớn nhất tăng lên 1. Tuy nhiên không phải lúc nào nó cũng lấy giá trị lớn nhất mà sẽ tuân theo những tính chất sau đây:

• AUTO_INCREMENT chỉ thiết lập được cho kiểu INT và mỗi bảng chỉ có một field duy nhất, nghĩa là nếu bạn thiết lập 2 fields là AUTO_INCREMENT thì sẽ bị lỗi ngay.
• Khi bạn thêm dữ liệu nếu bạn có truyền data thì nó sẽ lấy data đó thay vì tăng tự động, ngược lại nó sẽ lấy giá trị lớn nhất hiện tại và tăng lên 1(giá trị lớn nhất này lưu trong config của table chứ không phải là id lớn nhất trong các records).
• Khi bạn xóa một record thì sẽ bị khuyết mất một giá trị, lúc này nếu bạn thêm thì nó sẽ không lấp vào vị trí này mà nó tuân theo quy luật trên.
• Giả sử giá trị 120 là lớn nhất, bạn xóa đi 120 thì lúc này lớn nhất là 119. Lúc này nếu ban thêm mới thì nó sẽ lấy 121 chứ không phải là 120 vì giá trị lớn nhất nó lưu trong config của table.
• Thông thường ta sử dụng AUTO_INCREMENT cho Primary Key khi viết ứng dụng website
• Mặc định AUTO_INCREMENT sẽ có giá trị đầu tiên là 1

Đấy là những đặc điểm của AUTO_INCREMENT. Bây giờ ta sẽ tìm hiểu các thiết lập nó nhé.

B. Tạo AUTO_INCREMENT

Để tạo AUTO_INCREMENT thì ta thêm từ khóa AUTO_INCREMENT đằng sau field muốn tạo trong lệnh tạo bảng (Create Table). Thông thường chúng ta dùng cho khóa chính nên trong các ví dụ dưới đây tôi sử dụng cho field ID.

CREATE TABLE Users(     id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL UNIQUE,     email VARCHAR (50) NOT NULL UNIQUE )

 

C. Thay đổi giá trị AUTO_INCREMENT

Như bạn biết mặc định AUTO_INCREMENT sẽ có giá trị đầu tiên là 1. Nhưng đôi lúc bạn muốn giá trị đầu tiên của nó là một con số khác thì bạn sẽ sử dụng lênh ALTER TABLE để thay đổi.

ALTER TABLE Users AUTO_INCREMENT = 1000

 

Sau khi chạy lệnh này thì khi bạn thêm dữ liệu nó sẽ lấy 1000 thay vì 1 như cấu hình mặc định ban đầu.

Lưu ý: Nếu số bạn chọn nhỏ hơn số max của auto thì sẽ không có tác dụng. Ví dụ field ID hiện tại max là 1000 nhưng bạn thay đổi thông số auto 400 thì sẽ không có tác dụng.

9. Khóa ngoại (Foreign Key).

A. Khóa ngoại (Foreign Key) là gì?

Trong bài này tôi sẽ không trình bài khái niệm khóa ngoại một cách chi tiết nữa mà đi vào định nghĩa chính của nó. Foreign key là mối quan hệ giữa hai bảng và mối quan hệ này ta hay gọi là cha - con, nghĩa là nếu bảng A có một thuộc tính liên kết tới bảng B thì lúc này bảng B đóng vai trò là cha và bảng A đóng vai trò là con.

Khái niệm Foreign key là gì không chỉ có ở MySQL mà nó là một thành phần của tất cả các hệ quản trị CSDL như SQL Server, Oracle, Access, ... Nếu bạn đã từng học qua các mô hình CSLD thì không còn lạ gì khóa ngoại nữa.

Thông thường chúng ta có hai loại khóa ngoại đó là khóa ngoại giữa hai bảng và khóa ngoại trỏ đến chính nó (đệ quy).

Khóa ngoại giữa hai bảng

Xét sơ đồ CSDL sau đây:

Các bạn thấy trong bảng customers và orders có một mối quan hệ với tên gọi là "mỗi order là của một customer nào đó". đây ta gọi là mối quan hệ (1:n), có nghĩa là một customer có thê có nhiều orders và mỗi order chỉ thuộc về một customer duy nhất. Xem kỹ hơn ta thấy trong bảng orders có field customerNumber và nó sẽ trỏ đến khóa chính (Primary Key) của bảng customers.

Như vậy ta có kế luận như sau:

Khóa ngoại ở bảng orders sẽ tham chiếu đến khóa chính của bảng customers. Lúc này bảng customers gọi là bảng cha và bảng order gọi là bảng con. Đây chính là điều BẮT BUỘC của khóa ngoại.

Khóa ngoại trỏ đến chính bảng đó

Xét sơ đồ CSDL sau đây:

Trong sơ đồ này nó có một khóa ngoại là reportsTo và trỏ đến chính khóa chính của nó employeeNumber. Mối quan hệ này ta nói như sau "mỗi nhân viên có thể là một nhân viên bình thường hoặc  là người quản lý của một nhân viên khác. Hằng ngày các nhân bị quản lý khác sẽ báo cáo (reportsTo) tới nhân viên quản lý". Sơ đồ này ta gọi là đệ quy, nghĩa là khóa ngoại sẽ tham chiếu tới chính table nó luôn. Trong thực tế cũng hay gặp trường hợp này nên các bạn cần lưu ý nhé.

B. Tạo khóa ngoại (Foreign Key) trong MySQL

Ta sẽ sử dụng cú pháp T-SQL để tạo khóa ngoại, chúng ta có khá nhiều các tạo và tùy vào nhu cầu của ban mà sử dụng cho phù hợp. Tất cả các cách đều có chung một cấu trúc đó là khai báo field của bảng A và sẽ tham chiếu đến field nào của bảng B bởi từ khóa REFERENCES.

Tạo trong lệnh tạo bảng create table

Chúng ta sẽ tạo trực tiếp trong lệnh tạo bảng và cú pháp của nó cũng tương tự như lệnh tạo khóa chính, nghĩa là sẽ đặt ở cuối phần khai báo field. Nếu sử dụng cách này thì khóa ngoại sẽ không có tên.

Ví dụ

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL );   CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL ,     email VARCHAR (50) NOT NULL ,     groupid INT(11),     FOREIGN KEY (groupid) REFERENCES Groups(groupid) );

 

Các bạn thấy tôi đã sử dụng từ khóa FOREIGN KEY (groupid) REFERENCES Groups(groupid) để tạo khóa ngoại, trong đó:

• FOREIGN KEY (groupid): là field được chọn làm khóa ngoại ở bảng con, tức là bảng Users.
• REFERENCES Groups(groupid): là khóa chính của bảng cha, tức là bảng Groups.

Sau khi tạo xong bạn vào PHPMyAdmin và chọn mục database, chọn diagram ở thanh công cụ thì lúc này bạn sẽ thấy một sơ đồ như sau:

Như vậy là bạn đã tạo thành công rồi đấy.

Ví dụ có đặt tên:

Tương tự như các phần trước, để đặt tên cho khóa ngoại thì ta phải sử dụng từ khóa CONSTRAINT.

 

Ví dụ có đặt tên

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL );   CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL ,     email VARCHAR (50) NOT NULL ,     groupid INT(11),     CONSTRAINT fk_group FOREIGN KEY (groupid) REFERENCES Groups(groupid) );

 

Tạo bằng lệnh ALTER TABLE

Với cách này ta phải tạo hai bảng trước, sau đó sẽ dùng lệnh ALTER TABLE để thêm FOREIGN KEY.

 

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL );   CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL ,     email VARCHAR (50) NOT NULL ,     groupid INT(11) );   ALTER TABLE Users ADD FOREIGN KEY(groupid) REFERENCES Groups(groupid);

 

Hoặc:

 

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL );   CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL,     email VARCHAR (50) NOT NULL,     groupid INT(11) );   ALTER TABLE Users ADD CONSTRAINT fk_group FOREIGN KEY(groupid) REFERENCES Groups(groupid);

 

Tạo khóa ngoại trường hợp tham chiếu chính nó

Trường hợp này ta cũng sử dụng cú pháp tương tự, thay vì tham chiếu tới bảng nào đó thì sẽ tham chiếu đến chính nó.

 

Ví dụ

CREATE TABLE Employee(     id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     fullname VARCHAR(50) NOT NULL,     email VARCHAR (50) NOT NULL,     leader_id INT (11) NOT NULL,     CONSTRAINT pk_self FOREIGN KEY (leader_id) REFERENCES Employee(id) );

 

C. Xóa (Drop) Foreign Key

Để xóa được Foreign Key thì bạn phải biết tên của nó là gì, mà tên chỉ tồn tại trong trường hợp ta có sử dụng từ khóa CONSTRAINT lúc tạo khóa, vì vậy khuyến khích bạn sử dụng CONSTRAINT để tạo khóa ngoại.

Sau đây là cú pháp xóa Foreign Key:

 

ALTER TABLE Users DROP FOREIGN KEY fk_group;

 

Trong đó fk_group là tên của khóa ngoại.

Lưu ý quan trọng:

Bạn chỉ thực hiện được thao tác xóa khi không tồn tại một bảng con nào tham chiếu đến nó.

10. Thay đổi cấu trúc table (Alter Table)

A. Đặt vấn đề với Alter Table

Giả sử chúng ta có bảng tasks như sau:

Và tôi sử dụng lệnh CREATE TABLE để tạo bảng này như sau:

CREATE TABLE tasks(     task_id INT(11) NOT NULL AUTO_INCREMENT,     SUBJECT VARCHAR(45),     start_date DATE,     end_date DATE,     description VARCHAR(200),     CONSTRAINT pk_task PRIMARY KEY(task_id) );

Bây giờ ta sử dụng bảng này để học các lệnh về ALTER TABLE để đổi cấu trúc table nhé.

B. Môt số các dùng ALTER TABLE

Đổi cấu trúc column

Giả sử tôi cần đổi cấu trúc field description sang kiểu varchar(250) ký tự và NOT NULL:

ALTER TABLE tasks CHANGE COLUMN description description VARCHAR(250) NOT NULL;<br />

Lưu ý là trong này field description ta phải gõ 2 lần nhé.

Thêm column:

Giả sử tôi cần thêm một column tên là active kiểu TINYINT(1) và mặc định sẽ là 0.

ALTER TABLE tasks ADD COLUMN active TINYINT(1) DEFAULT 1

Đổi tên table (rename table)

Bây giờ ta đổi tên tasks thành nhiemvu:

ALTER TABLE tasks RENAME TO nhiem_vu;

11.Thêm dữ liệu vào bảng (INSERT TABLE).

A. Lệnh INSERT trong MySQL

Trước khi vào tìm hiểu lệnh INSERT thì ta cần tạo database, tạo table đã nhé. Giả sử tôi tạo database tên qlsv và table sinhvien như sau:

CREATE DATABASE IF NOT EXISTS qlsv; USE qlsv;   CREATE TABLE IF NOT EXISTS sinhvien(     sv_id INT(11) NOT NULL AUTO_INCREMENT,     sv_name VARCHAR(255) NOT NULL,     sv_description  VARCHAR(500),     CONSTRAINT pk_sinhvien PRIMARY KEY(sv_id) ) ENGINE = InnoDB

Hình minh họa:

Thực ra thì MySQL hay MSSQL đều có cú pháp giống nhau bởi chúng đều là ngôn ngữ truy vấn T-SQL. Sau đây là một số cú pháp mà ta có thẻ sử dụng để INSERT database.

Cú pháp 1: Khai báo rõ tên column

INSERT INTO table_name(field1, field2, field2, ..., fieldn) VALUES('field1', 'field2', 'field3', ...,'fieldn')

Khi sử dụng cú pháp này thì các field ở vị trí nào sẽ tương ứng với vị trí của nó ở VALUES, và ở phần khai báo có bao nhiêu column thì ở phần value sẽ có bấy nhiêu value tương ứng.

Ví dụ:

INSERT INTO sinhvien(sv_name, sv_description) VALUES ('Nguyen van cuong', 'Sinh vien dai hoc');<br />

Vì sv_id là PRIMARY KEY và tăng tự động (AUTO_INCREMENT) nên tôi không cần truyền dữ liệu cho nó.

Cú pháp 2: Không khai báo rõ tên column

INSERT INTO table_name VALUES ('field_1', 'field_2', ..., 'field_n');

Trong trường hợp này thì bạn bắt buộc phải truyền cho tất cả các column, ví dụ bạn có 10 column thì bạn phải truyền cho cả 20, nếu không sẽ báo lỗi ngay.

Ví dụ:

INSERT INTO sinhvien VALUES ('2', 'Nguyen van Kinh', 'Hoc sinh trung hoc');

Các bạn thấy tuy là khóa chính và tăng tự động nhưng vẫn phải truyền value cho nó, điều này khác hoàn toàn với cách trên.

B. Insert có khóa ngoại (Primary Key)

Bây giờ bạn xóa database mà ta demo ở trên để làm lại database khác. Giả sử ta có bảng SINHVIEN và bảng LOP, trong đó SINHVIEN sẽ là con của bảng LOP. Vây ta cần tạo hai bảng này và thêm khóa chính PRIMARY KEY như sau:

CREATE DATABASE IF NOT EXISTS qlsv; USE qlsv; CREATE TABLE IF NOT EXISTS lop(     lop_id INT(11) NOT NULL AUTO_INCREMENT,     lop_name VARCHAR(255) NOT NULL,     CONSTRAINT pk_lop PRIMARY KEY(lop_id) ) ENGINE = INNODB;   CREATE TABLE IF NOT EXISTS sinhvien(     sv_id INT(11) NOT NULL AUTO_INCREMENT,     sv_name VARCHAR(255) NOT NULL,     sv_description  VARCHAR(500),     lop_id INT(11) NOT NULL,     CONSTRAINT pk_sinhvien PRIMARY KEY(sv_id),     CONSTRAINT fk_sinhvien_lop FOREIGN KEY (lop_id) REFERENCES lop(lop_id) ) ENGINE = INNODB;

Hình minh họa:

Trong trường hợp INSERT vào bảng có khóa ngoại thì bạn cần phải lưu ý những đặc điểm sau (lấy sơ đồ trên làm ví dụ):

• Khi thêm vào bảng LOP thì thêm bình thường không vấn đề gì.
• Khi thêm vào bảng SINHVIEN thì tại vì nó có một khóa ngoại là lop_id có trỏ đến bảng LOP nên bắt buộc giá trị của lop_id phải tồn tại trong bảng LOP, nếu không sẽ bị báo lỗi ngay.

Áp dụng hai lưu ý trên thì vấn đề thêm dữ liệu quá đơn giản.

Ví dụ: Thêm dữ liệu bảng LOP sau đó thêm dữ liệu cho bảng SINHVIEN.

INSERT INTO lop(lop_name) VALUES('CNTT'); INSERT INTO lop(lop_name) VALUES('SPTIN')

Sau khi INSERT xong thì bảng LOP sẽ có hai record như sau:

 

Ok, lop_id đã được thiết lập tăng tự động. Bây giờ ta sẽ viết code thêm bảng SINHVIEN nhé:

INSERT INTO sinhvien(sv_name, sv_description, lop_id) VALUES ('Nguyen Van Cuong', 'Hoc sinh guong mau', 1)

Chạy câu truy vấn này thì thêm thành công. Nhưng giờ giả sử bạn đổi giá trị của lop_id sang số 4 thử xem? Chạy sẽ bị lỗi tại vì lop_id = 4 không tồn tại bên bảng LOP.

C. Một số vấn đề khác

Bây giờ ta tìm hiểu một số vấn đề khác hơi râu ria một chút như sau:

• Trường hợp bạn thêm cho field là số thì bạn không cần đặt trong cặp dấu nháy đơn, tuy nhiên nếu đặt vẫn được (khuyến khích đặt).
• Trường hợp bạn thêm là chuỗi thì nếu trong chuỗi có ký tự dấu nháy đơn, kép thì bạn nên thêm dấu / đằng trước nếu không sẽ bị lỗi ngay.

Ví dụ:

INSERT INTO sinhvien(sv_name, sv_description, lop_id) VALUES ('Nguyen Van Cuong', 'Hoc sinh guong mau 'nhat lop' ', 1)

12. Lệnh SELECT lấy dữ liệu trong MySQL

A. Lệnh SELECT trong MySQL

Lệnh SELECT có nhiệm vụ lấy danh sách dữ liệu của một hoặc nhiều bảng trong database, tuy nhiên phạm vi của bài này chúng ta chỉ đề cập đến SELECT một table, trong bài học về JOIN chúng ta sẽ tìm hiểu SELECT nhiều table.

Cú pháp của lệnh SELECT như sau:

 

SELECT field1, field2, field3, ... FROM table_name WHERE <dieu_kien_loc> ORDER BY field_name ASC|DESC LIMIT start, limit

Trong đó:

• SELECT field1, field2, field3, ... là danh sách các fields cần lấy
• FROM table_name tên table cần lấy
• WHERE <dieu_kien_loc> là các điều kiện để lấy các dòng dữ liệu
• ORDER BY field_name, ASC|DESC: là cách sắp xếp cho field_name theo kiểu ASC (tăng dần) hoặc DESC (giảm dần)
• LIMIT start, limit là lấy limit records kể từ record thứ start trong kết quả.

Ví dụ: Lấy danh sách sinh viên gồm các thông tin sv_id, sv_name, sv_description từ bảng SINHVIEN.

SELECT sv_id, sv_name, sv_description FROM SINHVIEN

Chạy lên kết quả gồm tất cả sinh viên (4 sv) trong bảng SINHVIEN.

Để lấy tất cả các fields thì ta sử dụng dấu sao (*).

Ví dụ:

SELECT *   FROM SINHVIEN

B. Lệnh SELECT có WHERE

Để lọc danh sách sinh viên theo một tiêu chí nào đó thì ta sử dụng thêm chức năng WHERE trong SELECT. Ví dụ tôi muốn lấy sinh viên có tên là 'Mr Kinh' thì tôi sẽ viết câu truy vấn như sau:

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name = 'Mr Kinh'

Chạy lên kết quả là một sinh viên:

Tới đây bạn sẽ có câu hỏi là "Có phải ở WHERE chỉ có sử dụng toán tử bằng để lọc?". Câu trả lời là không nhé, có rất nhiều toán tử dùng để lọc như một ngôn ngữ lập trình khác, tuy nhiên về cú pháp thì hơi khác chút xíu (xem các bảng dưới đây).

Bảng các toán tử quan hệ thông dụng

Các toán tử quan hệ chính là các toán tử thể hiện mối quan hệ giữa hai vế (vế phải và vế trái).

Ký hiệu	Diễn giải
=	So sánh bằng, ví dụ sv_name='nguyen van cuong'
>	Lớn hơn, ví dụ sv_id > 1
>=	Lớn hơn hoặc bằng, ví dụ sv_id >= 2
<	Bé hơn, ví dụ sv_id < 2
<=	Bé hơn hoặc bằng, ví dụ sv_id <= 3
<> hoặc !=	Khác, ví dụ sv_id <> 3
Like	So sánh theo biểu thức. Học sau
in	Trong một tập hợp nào đó. Học sau

Trong bảng này toán tử LIKE và IN sẽ tìm hiểu ở một bài khác. bây giờ ta sẽ thực hành vài ví dụ để các bạn làm quen nhé.

Ví dụ 1: Lấy danh sách sinh viên có sv_id lớn hơn 2.

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_id > 2<br><br>

 

Ví dụ 2: Lấy danh sách sinh viên có tên không phải là Mr Cuong

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name <> 'Mr Cuong'

 

Ví dụ 3: Lấy danh sách sinh viên có sv_id bé hơn 3

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name < 3

Bảng các toán tư luận lý thông dụng:

 

Toán tử luận lý dùng để thể hiện suy luận giữa hai mệnh đề (mệnh đề trái, mệnh đề phải) và từ đó trả về kết quả đúng hoặc sai.

Ký hiệu	Diễn giải
AND	nếu cả hai mệnh đề đều đúng thì kết quả là đúng
OR	Nếu một trong hai mệnh đề đúng thì sẽ trả kết quả đúng

Thông thường chúng ta sử dụng hay toán tử này là quá đủ rồi. Sau đây là một số ví dụ:

Ví dụ 1: Lấy danh sách sinh viên có sv_id > 1 và có tên là Mr Cuong

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_id > 1 AND sv_name = 'Mr Cuong'

 

Ví dụ 2: Lấy danh sách sinh viên có id = 2 hoặc có tên là Mr Kinh

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_id = 2 OR sv_name = 'Mr Kinh'

 

C. Lệnh SELECT có ORDER BY

ORDER dùng để sắp xếp kết quả trả về của câu truy vấn, nó có hai tham số là tên field và kiểu sắp xếp. Sau đây là cách dùng của cả hai trường hợp.

Sắp xếp tăng dần:

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN ORDER BY sv_id ASC

 

Kết quả:

Sắp xếp giảm dần:

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN ORDER BY sv_id DESC

 

Kết quả:

Các bạn thấy hai kết quả đều trả về 4 sinh viên nhưng thứ tự của nó lại khác nhau. Lý do là ta đã sắp xếp field sv_id tăng dần cho ví dụ 1 và giảm dần cho ví dụ 2.

D. Lênh SELECT có LIMIT

LIMIT dùng để giới hạn hết quả trả về và rất thường hay sử dụng kết hợp với thuật toán phân trang để tốc độ website chạy nhanh hơn. Nó có hai tham số đó là vị trí record cần lấy (start) và lấy bao nhiêu record (limit).

Ví dụ: Lấy một sinh viên đầu tiên trong kết quả

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN ORDER BY sv_id DESC LIMIT 0, 1

Ví dụ: Lấy hai sinh viên bắt đầu từ sinh viên thứ 2 trong kết quả

SELECT sv_id, sv_name, sv_description FROM SINHVIEN ORDER BY sv_id DESC LIMIT 2,2

Ở phần này bạn hay sai ở chỗ truyền tham số vào bị nhầm lẫn giữa start và limit.

E. Quy trình xử lý câu SELECT

Phần này có lẽ nhiều bạn chưa biết nên tôi đưa vào luôn cho bạn tham khảo. Nhìn vào cú pháp câu truy vấn bạn có thắc mắc về quy trình chạy câu truy vấn hay không? Nhiều bạn hiểu lầm là cứ chạy từ trên xuống là sai nhé. Quy trình chạy của nó như sau: FROM -> WHERE -> SELECT, nghĩa là nó sẽ chọn từ bảng trước, sau đó kiểm tra điều kiện WHERE rồi mới quyết định chọn SELECT.

13. WHERE IN - WHERE LIKE trong Mysql

A. WHERE IN trong MySQL

WHERE IN có tác dụng tương tự như hàm in_array() trong PHP vậy, nghĩa là sẽ kiểm tra giá trị của field đó có nằm trong một tập hợp nào đó hay không. Chẳng hạn bạn cần kiểm tra trong bảng sinh viên có sinh viên nào có tên là Mr Cường, Mr Kính hoặc Mr Chính hay không? thì ta sẽ tạo điều kiện IN như sau:

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name IN ('Mr Cuong', 'Mr Kinh', 'Mr Chinh')

Như vậy những record nào có tên là Mr Cuong hoặc Mr Kinh hoặc Mr Chinh thì đều được chọn.

Nếu không sử dụng IN thì ta sẽ dùng toán tử OR để tạo điều kiện. Như ví dụ trên tôi sẽ chuyển sang sử dụng OR như sau:

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE     sv_name = 'Mr Cuong' OR     sv_name = 'Mr Kinh' OR      sv_name = 'Mr Chinh'

 

B. WHERE LIKE trong MySQL

Like dịch trong tiếng anh có nghĩa là giống, trong ngôn ngữ T-SQL thì nó cũng có ý nghĩa tương tự đó là tìm những dòng nào mà có dữ liệu giống với cấu trúc lệnh LIKE truyền vào. Nó hoạt động theo nguyên tắc tương tự như Regular Expression vậy, nghĩa là sẽ so khớp với cấu trúc của chuỗi LIKE truyền vào. Sau đây là một số ví dụ liên quan tới lệnh LIKE trong MySQL.

LIKE với ký hiệu %

Ký hiệu % sẽ đại diện cho 0 hoặc nhiều ký tự.

Ví dụ: cần tìm những sinh viên nào có tên và trong tên chỉ cần có chữ Cuong là được chọn.

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name LIKE '%Cuong%'

Kết quả nó sẽ trả về một record duy nhất. Riêng với chuỗi %Cuong% thì tôi sẽ giải thích cho các trường hợp sau là đúng:

 

• Chuỗi 'some thing Cuong some thing' đúng
• Chuỗi 'some thing Cuong' đúng
• Chuỗi 'Cuong some thing' đúng

Ví dụ: Tìm những sinh viên nào có lastname là Kinh

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name LIKE '%Kinh'

 

Có lẽ bạn thắc mắc tại sao lại chỉ có mỗi dấu % ở đằng trước nhỉLý do đơn giản bởi vì lastname thì ở cuối của chuỗi rồi cho nên đằng sau lastname sẽ không có gì nữa, còn đằng trước thì là các chữ cái bất kì

Like với kí hiệu _

Ký hiệu _ đại diện cho một ký tự bất kì, nghĩa là khi bạn sử dụng ký hiệu này thì nó sẽ là một ký tự thay vì 0 hoặc nhiều ký tự như ký hiệu %.

Ví dụ: Tìm sinh viên có tên bắt đầu chữ M, ký tự thứ 2 bất kì và tiếp theo là khoảng trắng, tiếp theo nữa là các ký tự bất kì

 

SELECT sv_id, sv_name, sv_description FROM SINHVIEN WHERE sv_name LIKE 'M_ %'

 

Cú pháp 'M_ %' có ý nghĩa là bắt đầu chữ M, tiếp theo là dấu _ nên nó là ký tự bất kì, tiếp theo là khoảng trắng và tiếp nữa dấu % là 0 hoặc nhiều ký tự bất kì.

Còn khá nhiều nữa nhưng thông thường chúng ta sử dụng hai ký hiệu % và _ thôi nên mình sẽ không trình bày thêm để tránh độ phức tạp ở đây. Nếu bạn muốn tìm hiểu có thể vào link này để tham khảo.

14. Sắp xếp kết quả với lệnh Order By

A. Giới thiệu lệnh ORDER BY trong MySQL

Lệnh ORDER BY dùng chung với lệnh SELECT để sắp xếp kết quả trả về theo tiêu chí:

• Kết quả sẽ tăng hoặc giảm dần
• Có thể chọn một hoặc nhiều columns để sắp xếp

Sắp xếp theo 1 column

Cú pháp như sau:

 

SELECT field1, field2, ... FROM table_name WHERE ... ORDER BY column_name sort_type<br><br><br>

 

Trong đó:

• column_name là tên column cần sắp xếp
• sort_type là loại sắp xếp và có giá trị là:
  • ASC nếu tăng dần
  • DESC nếu giảm dần

Ví dụ: Lấy danh sách sinh viên trong bảng SINHVIEN và kết quả trả về sắp xếp theo ID sinh viên và tăng dần

 

SELECT MaSV, TenSV, NamSinh FROM SINHVIEN ORDER BY MaSV ASC

 

Sắp xếp theo nhiều column

Cú pháp như sau:

 

SELECT field1, field2, ... FROM table_name WHERE ... ORDER BY column_name1 sort_type1, column2 sort_type2, ...

Giải thích tương tự như trường hợp trên, nghĩa là column_name sẽ là tên column và sort_type sẽ là cách sort tăng (ASC) hoặc giảm (DESC).

Ví dụ: Lấy danh sách sinh viên trong bảng SINHVIEN và sắp xếp theo ID sinh viên là tăng dần và năm sinh giảm dần

 

SELECT MaSV, TenSV, NamSinh FROM SINHVIEN ORDER BY MaSV ASC, NamSinh DESC

 

B. Một số ví dụ khác về ORDER BY trong MySQL

Trước tiên bạn cần tạo database QLHS và tạo bảng SINHVIEN, sau đó thêm vào một số record như sau:

CREATE DATABASE QLHS;   USE QLHS;   CREATE TABLE SINHVIEN(     MaSV INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     TenSV VARCHAR (255),     NamSinh INT(4) ) ENGINE = INNODB;   INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van A', '1990'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van B', '1991'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van C', '1980'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van D', '1976'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van E', '1990'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van F', '1954'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van G', '1967'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van H', '1978'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van I', '1991'); INSERT INTO SINHVIEN (TenSV, NamSinh) VALUES ('Nguyen Van K', '2000');

 

Ok bây giờ ta làm các ví dụ nhé.

Ví dụ 1: Lấy danh sách sinh viên sắp xếp theo ID và tăng dần

 

SELECT * FROM SINHVIEN ORDER BY MaSV ASC

 

Kết quả:

Ví dụ 2: Lấy danh sách sinh viên có năm sinh bé hơn 1990 và sắp xếp theo Năm sinh tăng dần

 

SELECT * FROM SINHVIEN WHERE NamSinh < 1990 ORDER BY NamSinh ASC

 

Kết quả:

Ví dụ 3: Lấy danh sách sinh viên và sắp xếp tăng dần theo mã sinh viên và giảm dần theo năm sinh

 

SELECT * FROM SINHVIEN ORDER BY MaSV ASC, NamSinh DESC

 

Kết quả:

15. Giới hạn kết quả trả về với LIMIT

A. Giới thiệu lệnh LIMIT trong MySQL

Lệnh LIMIT đi kèm với lệnh SELECT và thông thường nó nằm ở cuối cùng. Trong thuật toán phân trang sẽ sử dụng lệnh LIMIT để xác định kết quả cho mỗi trang, điều này thật sự rất là hữu ích vì nó tăng tốc độ load trang hơn.

Cú pháp LIMIT như sau:

 

SELECT field1, field2 FROM table_name WHERE ... ORDER BY column_name, order_type LIMIT start, limit

 

Trong đó:

• ORDER BY là kiểu sắp xếp
• Ở LIMIT ta có:
  • start: lấy từ record thứ start
  • limit: bắt đầu từ start và lấy tiếp limit records.

Ví dụ: Lấy danh sách 20 sinh viên và bỏ đi 10 records đầu tiên trong kết quả trả về (bỏ đi 10 records tức là ta sẽ xác định start = 10)

 

SELECT * FROM SINHVIEN LIMIT 10, 20

 

B. Một số ví dụ khác với lệnh LIMIT 

Ví dụ 1: Lấy 20 sinh viên đầu tiên trong kết quả

 

SELECT * FROM SINHVIEN LIMIT 20

 

Trong trường hợp này các bạn thấy tôi chỉ truyền một tham số là LIMIT 20 thôi và kết quả nó sẽ trả về 20 rows đầu tiên.

Như vậy để lấy N rows đâu tiên thì ta chỉ cần truyền số đó là được.

Ví dụ 2: lấy ra 5 sinh viên có năm sinh lớn nhất

Trường hợp này ta phải kết hợp với lệnh ORDER BY để sắp xếp theo sinh viên, sau đó dùng lệnh LIMIT để lấy 5 sinh viên đầu tiên.

SELECT * FROM SINHVIEN ORDER BY NamSinh DESC LIMIT 5

 

Hoặc:

 

SELECT * FROM SINHVIEN ORDER BY NamSinh DESC LIMIT 0, 5

16. Phép tích đề cát

B. Tìm hiểu phép tích đề cát trong MySQL

Giả sử table A có 50 records và table B có 100 records thì kết quả trả về tối đa của phép tích này là 50 * 100 = 5000 records. Tại sao tôi lại nói là tối đa? Lý do là kết quả là bao nhiêu sẽ phụ thuộc vào mệnh đề WHERE nữa. Về tài nguyên cần để thực hiện cho phép tích khá là cao nên ta rất ít khi sử dụng nó, hầu như là không sử dụng luôn.

Để hiểu rõ hơn thì chúng ta làm một ví dụ nhé.

Cho sơ đồ CSDL như sau:

Trong sơ đồ này chúng ta có hai khóa chính và một khóa ngoại.

Dữ liệu bảng SINHVIEN:

Dữ liệu bảng LOP:

Bây giờ nếu chúng ta tích hai bảng này lại thì kết quả sẽ là 10 * 3 = 30 records. Nhưng nếu chúng ta truy vấn thêm điều kiện thì sẽ khác, ví dụ tích với điều kiện tên lớp là 'TOÁN' thì kết quả là 1 * 10 = 10 records như hình dưới đây.

Để ý kỹ hơn thì các bạn thấy khi chúng ta tích đề cát nếu SELECT * thì kết quả nó sẽ trả về tổng số các fields của hai bảng luôn. Ok bây giờ chúng ta tìm hiểu cú pháp và một vài ví dụ về câu SQL tích đề cát luôn nhé.

B. Cú pháp tích đề cát trong MySQL

Để tích hai bảng với nhau thì trong lệnh FROM chúng ta chỉ cần khai báo hai bảng cách nhau bởi dấu phẩy.

 

SELECT * FROM SINHVIEN, LOP

 

Như ở bài khóa ngoại tôi đã trình bày thì đối với sơ đồ trên bảng SINHVIEN gọi là bảng con và bảng LOP gọi là bảng cha. Mỗi SINHVIEN phải thuộc một LOP nào đó và mỗi LOP có thể có nhiều SINHVIEN.

Ví dụ 1: Lấy danh sách sinh viên thuộc lớp TOÁN thì ta phải làm thế nào?

Chúng ta sẽ sử dụng tích đề cát để nhân hai bảng lại với nhau, sau đó dựa vào kết quả của phép nhân này nó sẽ lọc theo điều kiện là TenLop = TOÁN. Trường hợp này chúng ta có

 

SELECT * FROM SINHVIEN, LOP WHERE TenLOP = 'TOAN'

 

Ví dụ 2: Lấy danh sách sinh viên và tên lớp mà sinh viên đó đang học?

Để giải bài này thì chúng ta dựa vào khóa ngoại, nghĩa là trong hai bảng SINHVIEN và LOP nếu LopID bên bảng SINHVIEN = LopID trong bảng LOP thì kết quả đúng, câu SQL như sau:

 

SELECT * FROM SINHVIEN, LOP WHERE SINHVIEN.LopID = LOP.LopID

 

Ví dụ 3: Lấy tên các sinh viên thuộc lớp CNTT

Bài này hơi khó vì chúng ta phải sử dụng hai điều kiện:

• Để xác định sinh viên học lớp nào thì chúng ta sử dụng SINHVIEN.LopID = LOP.LopID
• Nhưng để xác định tên lớp là CNTT nữa thì chúng ta phải thêm một điều kiện LOP.TenLop = 'CNTT'

 

SELECT SINHVIEN.TenSV FROM SINHVIEN, LOP WHERE SINHVIEN.LopID = LOP.LopID AND LOP.TenLop = 'CNTT'

 

Vậy ta có kết luận:

• Sử dụng dấu phẩy để tích các bảng với nhau trong lệnh SELECT
• Sử dụng lệnh WHERE để thiết lập điều kiện cho câu truy vấn trong phép tích đề cát.
• Trong trường hợp hai bảng có tên column trùng nhau thì ban phải sử dụng cú pháp table_name.column để tránh xung đột. Như ví dụ trên thì LopID đã bị trùng cho cả hai bảng nên ở WHERE tôi có khai báo là SINHVIEN.LopID = LOP.LopID

17. Sử dụng AS (Alias)

A. Sử dụng AS (Alias) trong MySQL

Câu trả lời cho câu hỏi trên là ta sẽ sử dụng từ khóa AS (Alias) để đặt lại cái định danh mới cho Table, Column.

Chúng ta có hai cách viết như sau:

• old_name AS new_name
• old_name new_name

Nghĩa là ta sử dụng từ khóa AS để đặt tên mới hoặc là bỏ luôn chữ AS.

Sau đây là một số ví dụ:

# Sử dụng AS (Alias) cho column

Ví dụ: Trường hợp này cả hai bảng trùng tên column title nên ta phải dùng AS để định cái tên mới cho cả hai.

 

SELECT     tb_titoe_product.cate_id AS cate_id,     tb_titoe_product.title AS product_title,     tb_titoe_product_cate.title AS cate_title FROM     tb_titoe_product, tb_titoe_product_cate WHERE     tb_titoe_product.cate_id = tb_titoe_product_cate.id

Hoặc:

 

SELECT     tb_titoe_product.cate_id cate_id,     tb_titoe_product.title product_title,     tb_titoe_product_cate.title cate_title FROM     tb_titoe_product, tb_titoe_product_cate WHERE     tb_titoe_product.cate_id = tb_titoe_product_cate.id

 

Kết quả trả về của ví dụ này gồm các column: cate_id | product_title | cate_title

# Sử dụng AS (Alias) cho table

Ví dụ: Trường hợp này tên dài quá nên sử dụng AS để viết ngắn gọn hơn.

 

SELECT     product.cate_id AS cate_id,     product.title AS product_title,     cate.title AS cate_title FROM     tb_titoe_product AS product, tb_titoe_product_cate AS cate WHERE     product.cate_id = product.id

 

Như vậy khi ta đặt alias cho tên table thì ở WHERE và SELECT sử dụng ngắn gọn hơn rất nhiều.

18. Gộp kết quả với toán tử UNION

A. Toán tử UNION trong MySQL

Toán tử UNION cho phép bạn nối kết quả của hai hoặc nhiều câu truy vấn lại với nhau để trở thành một danh sách kết quả duy nhất. Cú pháp của MySQL UNION như sau:

 

SELECT column1, column2 UNION [DISTINCT | ALL] SELECT column1, column2 UNION [DISTINCT | ALL] …

 

Tuy nhiên khi sử dụng UNION trong MySQL chúng ta cần phải tuân thủ những nguyên tắc sau đây:

• Số lượng colums trong tất cả các lệnh SELECT phải bằng nhau
• Mỗi column tương ứng vị trí phải có cùng kiểu dữ liệu và độ dài

Theo mặc định thì UNION sẽ loại bỏ các kết quả trùng lặp của các câu SELECT nên nó tạo cho chúng ta hai lựa chọn sau:

• Nếu chọn UNION DISTINCT thì nó sẽ loại bỏ kết quả trùng.
• Nếu chọn UNION ALL thì nó giữ lại kết quả trùng.
• Nếu bạn không chọn gì thì mặc định nó sẽ lấy UNION DISTINCT

B. Một số ví dụ UNION trong MySQL

Trước tiên bạn tạo database, hai tables và thêm một số dữ liệu bằng cách chạy lệnh SQL sau:

CREATE DATABASE uni_db;   USE uni_db;   CREATE TABLE news_1 (     id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,     title VARCHAR(255) ) ENGINE = INNODB;   CREATE TABLE news_2 (     id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,     title VARCHAR(255) ) ENGINE = INNODB;     INSERT INTO news_1(title) VALUES('Tin Thoi Su'); INSERT INTO news_1(title) VALUES('Tin The Thao'); INSERT INTO news_1(title) VALUES('Tin Quoc Te'); INSERT INTO news_1(title) VALUES('Tin Do day'); INSERT INTO news_1(title) VALUES('Tin Khung Bo');   INSERT INTO news_2(title) VALUES('Ngay Quoc Te'); INSERT INTO news_2(title) VALUES('Ngay Phu Nu'); INSERT INTO news_2(title) VALUES('The Gioi Do Day'); INSERT INTO news_2(title) VALUES('Chien Tranh The Gioi II'); INSERT INTO news_2(title) VALUES('Du Hoc Vien');

 

Dữ liệu Table news_1:

 

Dữ liệu trang news_2:

Ok, bây giờ ta làm một số ví dụ để các bạn hiểu rõ hợn lệnh UNION trong MySQL này.

Ví dụ 1: Lấy dữ liệu của hai bảng news_1 và news_2 và sau đó gom lại thành một kết quả

 

SELECT id, title FROM news_1 UNION SELECT id, title FROM news_2

 

Kết quả:

Ví dụ 2: Lấy ID của bảng news_1, news_2 và sau đó gom lại thành một kết quả

 

SELECT id FROM news_1 UNION SELECT id FROM news_2

 

Kết quả:

Rõ ràng UNION đã bị xóa đi kết quả trùng vì đáng lẽ ra nó phải hiển thị 10 kết quả nhưng ở đây nó hiển thị có 5 kết quả.

Bây giờ ta sử dụng lệnh ALL để xem thế nào.

 

SELECT id FROM news_1 UNION ALL SELECT id FROM news_2

 

Kết quả:

Với lệnh ALL thì dù bị trùng lặp nhưng nó vẫn trả về kết quả.

19. Select trong khoảng với BETWEEN

A. Select trong khoảng với BETWEEN trong MySQL

Giả sử ta có bảng dữ liệu như sau:

Bây giờ cần lấy những sinh viên có năm sinh trong khoảng từ 1970 đến 1990 thì ta sử lệnh SELECT và ở WHERE sử dụng toán tử AND:

 

SELECT * FROM SINHVIEN WHERE NamSinh >= 1970 AND NamSinh <= 1990

 

Thay vì sử dụng toán tử OR thì ta sử dụng toán tử BETWEEN với cú pháp:

 

field_name BETWEEN begin AND end

 

Trong đó:

• field_name: tên field cần kiểm tra
• begin: giá trị bắt đầu
• end: giá trị kết thúc

Quay lại ví dụ trên ta sẽ viết lại câu SQL như sau:

 

SELECT * FROM SINHVIEN WHERE NamSinh BETWEEN 1970 AND 1990

 

Cả hai cách đều có kết quả giống nhau như hình dưới đây:

B. Select ngoài khoảng với BETWEEN 

Ngoài cú pháp sử dụng select trong khoảng thì BETWEEN còn có một cách sử dụng là select ngoài khoảng.

Cú pháp như sau:

field_name NOT BETWEEN begin AND end

 

Sự khác nhau giữa trong khoang và ngoài khoảng là thêm chữ NOT đằng trước chữ BETWEEN.

Quay lại bài trên bây giờ cần lấy danh sách sinh viên có năm sinh không nằm trong khoảng 1970 và 1990 thì ta sẽ làm hai cách như sau:

Cách 1: Sử dụng toán tử OR

SELECT * FROM SINHVIEN WHERE NamSinh < 1970 OR NamSinh > 1990

 

Cách 2: Sử dụng NOT BETWEEN

 

SELECT * FROM SINHVIEN WHERE NamSinh NOT BETWEEN 1970 AND 1990

 

Cả hai ví dụ đều có kết quả như sau:

C. Sử dụng BETWEEN với các kiểu dữ liệu khác

BETWEEN thường sử dụng với dữ liệu kiểu INT, tuy nhiên bạn vẫn sử dụng được với các kiểu dữ liệu khác như:

• CHARACTER: Tuân theo thứ tự trong bảng mã ASCII
• DATE: Bạn nên sử dụng thêm hàm CASE chuyển dữ liệu sang dạng DATE để có kết quả chính xác nhất.

Ví dụ 1: Lấy danh sách sinh viên có tên gồm các ký tự trong khoảng 'A' đến 'B'

SELECT * FROM SINHVIEN WHERE TenSV BETWEEN 'A' AND 'B'<br>

Ví dụ 2: Giả sử bảng sinh viên thêm cột ngày nhập học. Bây giờ viết câu truy vấn lấy danh sách sinh viên nhập học từ ngày 01/01/2003 đến ngày 01/01/2014 thì ta làm như sau:

SELECT * FROM SINHVIEN WHERE NgayNhapHoc BETWEEN CAST('2003-01-01' AS DATE) AND CAST('2014-01-01' AS DATE)

20. Hàm nối chuỗi Concat

B. Hàm nối chuỗi Concat trong MYSQL

Hàm concat có tác dụng là nối các chuỗi lại với nhau với cú pháp như sau:

concat(str1, str2, str2, ... , strn)

Như vậy tham số truyền vào là không giới hạn, nghĩa là nếu bạn nối bao nhiêu thì sẽ truyền vào bấy nhiêu.

 

Ví dụ: Nối hai chuỗi titoe và .net lại với nhau

select concat('titoe','.net') as domain

Chạy lên kết quả sẽ là (domain => titoe.net).

B. Một số ví dụ hàm nối chuỗi concat trong MYSQL

Sử dụng Concat trong thao tác SELECT:

Thực hiện lấy tên các tour du lịch và nối thêm một dòng chữ "Địa điểm du lịch".

SELECT CONCAT('Ðịa điểm du lịch ', title) AS title FROM tour

Sử dụng Concat trong thao tác INSERT:

Trong thao tác thêm tour du lịch hãy nối thêm một dòng chữ "Chào mừng" vào tiêu đề.

 

INSERT INTO tour(title) VALUES(CONCAT('Chào mừng', 'Tiêu đề tour'))

 

Sử dụng Concat trong thao tác UPDATE:

Mỗi khi update hãy tự động nối chuỗi "Địa điểm du lịch" vào tiêu đề của tour.

 

UPDATE tour SET title = CONCAT('Địa điểm du lịch', 'Tieu de tour') WHERE id = 12

Sử dụng Concat trong thao tác DELETE:

 

Khi xóa bài viết theo tiêu đề hãy nối thêm dòng chữ "Địa điểm du lịch" ở trong điều kiện xóa.

 

DELETE FROM tour WHERE title = CONCAT('Địa điểm du lịch', 'tiêu đề tour')

21. INNER JOIN 

B. Tìm hiểu INEER JOIN trong MySQL

Giả sử chúng ta cần viết một câu truy vấn xem danh sách sinh viên và lớp mà sinh viên đó đang học thì chúng ta dựa vào khóa ngoại (foreign key) của bảng sinhvien và khóa chính của bảng lop để truy vấn. Chúng ta có hai cách giải.

Sử dụng phép tích đề cát

SELECT * FROM sinhvien, lop WHERE sinhvien.LopID = lop.LopID

Sử dụng INNER JOIN

SELECT * FROM sinhvien INNER JOIN lop ON sinhvien.LopID = lop.LopID

Cả hai câu truy vấn đề có kết quả giống nhau như hình dưới đây.

Vậy sự khác nhau giữa hai câu truy vấn trên là gì?

• Với phép tích thì sau khi tích hai bảng lại với nhau nó sẽ có tổng cộng là 10 x 3 = 30 records, sau đó ở mỗi record nó sẽ kiểm tra điều kiện nếu sinhvien.LopID = lop.LopID đúng thì record đó sẽ được chọn, ngược lại thì không được chọn.
• Với INNER JOIN thì khác một xíu, trong quá trình thực hiện tích hai bảng nó sẽ kiểm tra điều kiện ở ON (tức là sinhvien.LopID = lop.LopID), nếu đúng thì được chọn và sai thì bỏ qua

Như vậy xét về tốc độ truy vấn thì trường hợp sử dụng INNER JOIN sẽ nhanh hơn rất nhiều so với sử dụng phép tích.

Từ ví dụ trên ta rút ra được kết luận INNER JOIN sẽ được đặt ở FROM theo cú pháp sau:

SELECT column_list FROM t1 INNER JOIN t2 ON join_condition1 WHERE where_conditions;

Trong đó:

• t1, t2 là bảng cần JOIN
• join_condition1: Nếu TRUE thì record đó sẽ được chọn

Ta có thể ví phép JOIN giống như so sánh dữ liệu giữa hai bảng nếu có sự tương đồng thì được chọn và ngược lại thì không chọn như hình dưới đây.

Ảnh (Sưu tầm)

B. INNER JOIN nhiều table trong MySQL

Ở ví dụ phần 1 ta chỉ mới tìm hiểu cách JOIN hai bảng, tuy nhiên bạn có thể JOIN nhiều bảng lại với nhau và tuân theo quy luật chạy từ trái qua phải, nếu bảng nào khai báo trước thì chạy trước và ngược lại sẽ chạy sau. Nếu bạn sử dụng cặp dấu ngoặc thì sẽ thực hiện trong ngoặc trước.

Cú pháp:

SELECT column_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2

Làm vài ví dụ cho vui nhé.

Cho sơ đồ cơ sở dữ liệu sau:

Ví dụ 1: Hãy liệt kê danh sách sinh viên, thông tin lớp và khoa mà sinh viên đó đang học.

 

SELECT * FROM     sinhvien     INNER JOIN lop ON sinhvien.LopID = lop.LopID     INNER JOIN khoa ON sinhvien.KhoaID = khoa.KhoaId

 

Ví dụ 2: Liệt kê danh sách sinh viên học lớp TOAN gồm các thông tin (thông tin sinh viên + thông tin khoa mà sinh viên đang học)

 

SELECT * FROM     sinhvien     INNER JOIN lop ON sinhvien.LopID = lop.LopID     INNER JOIN khoa ON sinhvien.KhoaID = khoa.KhoaId WHERE lop.TenLop = 'TOAN'

 

Trong ví dụ này ta thêm điều kiện lop.TenLop = 'TOAN' ở WHERE.

Các ví dụ này nếu ta sử dụng truy vấn lồng thì sẽ nhanh hơn nữa, tuy nhiên vấn đề này tôi sẽ trình bày sau nhé.

C. Tránh lỗi ambiguous khi thực hiện INNER JOIN

Ở các ví dụ trên các bạn thấy tên column ở các bảng trùng khá nhiều như LopID trùng ở bảng sinhvien và lop, KhoaID trùng ở bảng sinhvien và khoa. Vậy làm sao để phân biệt giữa các field?

Ví dụ câu truy vấn này chạy sẽ bị lỗi vì lý do ở SELECT nó không hiểu LopID từ bảng nào.

 

SELECT TenSV, TenLop, LopID FROM sinhvien INNER JOIN lop ON sinhvien.LopID = lop.LopID

 

Nếu để ý kĩ thì bạn thấy ở ON đã sử dụng một cú pháp giúp phân biệt đó là tenbang.field. Sửa lại câu truy vấn như sau và mọi thứ OK.

 

SELECT TenSV, TenLop, lop.LopID FROM sinhvien INNER JOIN lop ON sinhvien.LopID = lop.LopID

 

Như vậy với cột nào bị trùng thì ta phải sử dụng cú pháp tenbang.tenfield.

D. INNER JOIN với ALIAS

Chúng ta đã được học cú pháp ALIAS và tác dụng của nó rồi nên bây giờ ta áp dụng vào lệnh JOIN luôn.

Các bạn xem câu truy vấn dưới đây:

SELECT TenSV, TenLop, l.LopID FROM sinhvien AS sv INNER JOIN lop AS l ON sv.LopID = l.LopID

 

Nhìn gọn hơn rất nhiều đúng không nào, cách này rất hữu ích khi tên bảng quá dài và join nhiều table.

22. Left Join và Right Join 

A. Dữ liệu thực hành Left Join MySQL

Cho bảng Students có dữ liệu như sau:

Bảng Class như sau:

Như bạn thấy, bảng students có một khóa ngoại class_id trỏ đến bảng class.

Có một sinh viên tên là Duong Van Hoi không thuộc một lớp nào cả (vì class_id = 0). Bây giờ ta sẽ thực hiện một vài phép LEFT JOIN cơ bản.

B. Cách sử dụng Join Left trong MySQL

Bài toán 1: Lấy tất cả thông tin sinh viên và lớp mà sinh viên đó đang học.

SELECT * FROM students     JOIN class ON students.class_id = class.class_id

Kết quả:

Trả về 2 kết quả là đúng, tai vì sinh viên thứ 2 có khóa ngoại class_id = 0 nên không thuộc một lớp nào cả.

Bài toán 2: Lấy tất cả thông tin sinh viên và lớp đang học, nếu không có học lớp nào cũng phải trả kết quả.

SELECT * FROM students     LEFT JOIN class ON students.class_id = class.class_id

Kết quả:

Sinh viên Dương Van Hoi mặc dù không thuộc lớp nào nhưng vẫn được trả về.

C. Cách sử dụng Right Join trong MySQL

Bảng Students:

Bảng Class:

Trong bảng class có ba lớp (CNTT, TOÁN, VAT LY). Trong bảng Student thì chỉ có 2 sinh viên có khóa ngoại trỏ đến lớp CNTT và TOÁN mà thôi.

Bây giờ ta hãy làm một vài ví dụ để hiểu rõ hơn về lệnh này.

Bài toán: Lấy thông tin lớp và sinh viên đang học lớp đó, lấy luôn cả những lớp không có sinh viên nào.

Ta chỉ cần sử dụng RIght Join là có thể giải quyết được bài toán này.

SELECT * FROM students     RIGHT JOIN class ON students.class_id = class.class_id

Kết quả:

Bạn hãy để ý dòng thứ 3 sẽ có một số field có giá trị null, đó chính là những lớp không có sinh viên nào.

Như vậy ta có thể tổng kết lai như sau:

• Inner Join: Dùng trong trường hợp bạn muốn JOIN 2 bảng lại và chỉ lấy thông tin thỏa ở lệnh ON.
• Left Join: Lấy kết quả như Inner Join, đồng thời lấy thêm dữ liệu ở bảng bên trái.
• Right Join: Lấy kết quả như Inner Join, đồng thời lấy thêm dữ liệu ở bảng bên phải.

 

23. Group By

A. Giới thiệu Group By trong MySQL

Mệnh đề GROUP BY sẽ gom nhóm dữ liệu và chỉ trả về một record cho một nhóm duy nhất, vì vậy nó giúp giảm bớt dữ liệu không cần thiết trong kết quả trả về.

Lệnh này thường kết hợp với các hàm như: SUM, AVG, MAX, MIN và COUNT để đếm hoặc lấy thông tin cần thiết trả về cho từng nhóm.

Cú pháp như sau:

SELECT     c1, c2,..., cn FROM     table WHERE     where_conditions GROUP BY c1 , c2,...,cn;

* Lưu ý:

• Lệnh Group By có khoảng trắng, và nó phải nằm sau lệnh WHERE.
• Những field có trong group by bắt buộc phải có trong Select.
• Thứ tự thực thi câu lệnh sẽ là: From -> Where -> Select -> Group By

B. Cách sư dụng Group By trong MySQL

Cho bảng Students có dữ liệu như sau:

Bài toán 1: Hãy lấy danh sách tuổi và số sinh viên có độ tuổi đó.

Với bài toán này ta phải hiển thị hai thông tin: Tuổi | Số sinh viên có độ tuổi này. Ví dụ tuổi 20 thì sẽ có 2 sinh viên, tuổi 21 có 2 sinh viên, tuổi 22 có một sinh viên.

Vì bài toán yêu cầu lấy danh sách theo tuổi nên ta sẽ gom nhóm theo tuổi. Hãy thử câu lệnh sau:

SELECT student_age FROM students     GROUP BY student_age

Kết quả:

Đây không phải là kết quả mong muốn, bởi chưa có thông tin số sinh viên thuộc độ tuổi đó.

Vì kết quả nó trả về 1 record, chính là đại diện cho nhóm nên ta có thể sử dụng hàm Count để đếm số record trong nhóm. Đây cũng chính là số sinh viên thuộc độ tuổi đó. Bạn có thể đếm một field bất kì nhé.

SELECT student_age, COUNT(student_name) AS total_students FROM students     GROUP BY student_age

Kết quả:

* Ghi nhớ: Các hàm Count, Max, Sum sẽ có tác dụng đến các records trong nhóm ở câu lệnh group by.

Bài toán 2: Đếm tổng số tuổi của tất cả sinh viên và hiển thị theo lớp (class_id).

Bài này ta sử dụng hàm SUM và gom nhóm theo class_id là được.

SELECT  class_id, SUM(student_age) AS total_age FROM students GROUP BY class_id

Kết quả:

C. Dùng Having để thêm điều kiện cho Group By

Bạn có thể thêm điều kiện lọc ở lệnh Group By bằng cách sử dụng mệnh đề Having. Lúc này thứ tự chạy sẽ như sau:

• From -> Where -> Select -> Group By -> Having -> Order By -> Limit

Quay lại dữ liệu ở trên mình sẽ ra một bài toán như sau: Đếm tổng số sinh viên theo từng lớp, và chỉ trả về lớp nào có từ 2 sinh viên trở đi.

Mình sẽ hiển thị lại bảng dữ liệu cho bạn dễ hình dung.

Bạn hãy nhìn thứ tự chạy ở trên để suy ngẫm nhé. Kết quả mong muốn phải được lấy từ lệnh Group By nên điều kiện này không thể đặt ở Where được (where chạy trước group by). Vì vậy ta phải đặt trong Having.

SELECT  class_id, COUNT(student_age) AS total_student FROM students GROUP BY class_id HAVING total_student >= 2

Kết quả:

Bạn hãy thử đặt điều kiện ở lệnh Where xem thế nào nhé.

SELECT  class_id, COUNT(student_age) AS total_student FROM students WHERE total_student >= 2 GROUP BY class_id

Chạy lên sẽ nhận được lỗi không tồn tại field total_student.

Error Code : 1054 Unknown column 'total_student' in 'where clause' Execution Time : 00:00:00:000 Transfer Time  : 00:00:00:000 Total Time     : 00:00:00:000

 

24. Truy vấn con (Sub Query)

A. Truy vấn con - sub query trong MySQL

Truy vấn con hay còn gọi là Sub Query, đây là một cách xử lý dữ liệu của ngôn ngữ T-SQL nói chung và MySQL nói riêng. Bản chất sub query trong MySQL là trả về một bảng ảo, sau đó sử dụng bảng ảo đó để thực hiện tiếp các câu lệnh khác.

Lênh sub query sẽ có tốc độ chậm hơn bình thường, bởi bản chất nó là tạo ra table ảo nên tốn nhiều tài nguyên lưu trữ hơn.

* Lưu ý: Một câu truy vấn sẽ được bắt đầu bằng lệnh Select, vì vậy ta sẽ dùng nó để nhận biết trong một câu lệnh SQL có bao nhiêu truy vấn con.

Ví dụ: Lấy thông tin nhân viên đang làm việc tại USA.

SELECT     lastName, firstName FROM     employees WHERE     officeCode IN (SELECT             officeCode         FROM             offices         WHERE             country = 'USA');

Xem hình để hiểu rõ hơn:

B. Thực hành Sub Query trong MySQL

Quay lại dữ liệu như bài trước như sau:

Bảng Students:

Bảng Class:

Bây giờ mình sẽ ra một đề bài: Lấy tất cả sinh viên thuộc lớp có là 1.

Thực ra bài này ta chỉ cần thêm lệnh where như sau là được:

SELECT * FROM students WHERE class_id = 1

Tuy nhiên mình sẽ giải thêm một cách khác là sử dụng truy vấn con. Mình sẽ tạo một sub query trả về một table ảo tên là (class_tmp) ,chứa id của lớp cần lấy, sau đó join với table Students là ra kết quả.

SELECT * FROM students     JOIN        (         SELECT * FROM class         WHERE class_id = 1     ) AS class_tmp ON class_tmp.class_id = students.class_id

Đương nhiên câu lệnh này sẽ chậm hơn cách thông thường, bởi chương trình sẽ phải thực hiện nhiều query rất phức tạp.

 

Bài tiếp theo...