I. Giới thiệu SQL & Cơ sở dữ liệu quan hệ

Cơ sở dữ liệu quan hệ là cách tổ chức dữ liệu dưới dạng các bảng (dòng x cột) kèm theo mối quan hệ liên kết giữa các cột của các bảng với nhau. Mỗi dòng trong một bảng tương ứng với một bản ghi, mỗi cột trong một bảng tương ứng với một trường dữ liệu, ngoài ra có các cột trung gian (liên kết) để thể hiện mối liên hệ giữa các bảng.

SQL là ngôn ngữ được sử dụng cho các truy vấn đến cơ sở dữ liệu quan hệ

Có nhiều engine Dữ liệu quan hệ khác nhau (MySQL/MariaDB, Oracle, MSSQL, PostgreSQL, SQLite, ...), mỗi engine sử dụng một "biến thể" (dialect) của SQL để thực hiện các truy vẫn, tuy nhiên các cú pháp truy vấn cơ bản (thêm, sửa, cập nhật, xoá, tìm kiếm) của các biến thể SQL đều khá giống nhau.

Các truy vấn trong các phần dưới đây được dùng cho MySQL/Maria DB nhưng cũng có thể dùng cho các CSDL khác mà không cần/cần rất ít sửa đổi

II. Các truy vấn dữ liệu trên một bảng

1. Tạo bảng

Cú pháp chung:


      CREATE TABLE table_name(
         field_1 type_1,
         field_2 type_2,...
         field_n type_n
         PRIMARY KEY(field_1)
      )

Giải thích:

table_name: tên bảng cần tạo
field_i: tên các trường (cột) dữ liệu của bảng

type_i: kiểu dữ liệu của các trường. Các kiểu dữ liệu trong SQL:

Kiểu dữ liệu	Ý nghĩa
TINYINT	Số nguyên từ 0-255
BOOL/BOOLEAN	0/1
SMALLINT	Số nguyên từ 0-65535
INT	Số nguyên từ 0-2³²
BIGINT	Số nguyên từ 0-2⁶⁴
FLOAT	Số thập phân
DOUBLE	Số thập phân độ chính xác kép
DECIMAL	Số thập phân dấu phảy tĩnh
CHAR(size)	Xâu có độ dài cố định bằng size
BINARY(SIZE)	Chuỗi byte có độ dài cố định bằng size
VARCHAR(size)	Xâu có độ dài thay đổi, tối đa bằng size
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT	Đoạn text với độ dài tối đa 2⁸/2¹⁶/2²⁴/2³² byte
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB	Chuỗi byte với độ dài tối đa 2⁸/2¹⁶/2²⁴/2³² byte
DATE	Ngày/tháng/năm
DATETIME	Ngày/tháng/năm giờ-phút-giây
TIME	Giờ-phút-giây
TIMESTAMP	Số giây kể từ 1/1/1970 UTC

PRIMARY KEY: trường đóng vai trò là khoá chính trong bảng

Ví dụ:


      CREATE TABLE student(
         id BIGINT NOT NULL AUTO_INCREMENT,
         student_number VARCHAR(20),
         student_name VARCHAR(50),
         phone VARCHAR(20),
         gpa FLOAT,
         PRIMARY KEY(id)
      )

Bảng dữ liệu được tạo ra có cấu trúc như sau:

2. Thêm mới bản ghi

Cú pháp chung:


      INSERT INTO table_name(field_1,field_2,..., field_n)
      VALUES(value_1,value_2,..., value_n)

Giải thích:

table_name: tên bảng cần tạo
field_i: các trường (cột) dữ liệu của bảng
value_i: giá trị của từng trường dữ liệu của bản ghi sẽ thêm vào.

Ví dụ:


      INSERT INTO student(student_number, student_name, phone, gpa)
      VALUES('1001', 'Nguyễn Văn A', '012323214', 7.5);

Bản ghi mới được thêm vào bảng:

2. Tìm kiếm bản ghi

Cú pháp chung:


      SELECT field_1, field_2, ..., field_n
      FROM table_name
      WHERE field_1=value_1 AND field_2=value_2 AND ...
      LIMIT offset,count
      ORDER BY field_i ASC/DESC

Giải thích:

field_1, field_2, ..., field_n: danh sách các cột muốn lấy về từ bảng dữ liệu. Trường hợp muốn lấy về toàn bộ các cột của bảng thì sử dụng:
SELECT * FROM ...
table_name: tên bảng muốn thực hiện truy vấn
WHERE dieu_kien: điều kiện tìm kiếm, tổ hợp của các điều kiện logic (AND, OR, NOT, >, <, >=, <=, =, <>) trên các cột. Ngoài ra trong SQL có điều kiện LIKE để tìm một trường chứa một cụm từ cho trước (xem ví dụ).
LIMIT offset, count : giới hạn số bản ghi lấy về (tối đa count bản ghi), bỏ qua offset bản ghi đầu. Tính năng này chủ yếu phục vụ cho việc phân trang dữ liệu tìm kiếm.
ORDER BY field_i ASC/DESC : sắp xếp các bản ghi theo chiều tăng (ASC) / giảm (DESC) của trường field_i

Ví dụ:

Lấy về 10 học sinh đầu tiên, sắp xếp theo tên (thứ tự tăng dần của bảng chữ cái):


      SELECT * FROM student ORDER BY student_name LIMIT 10

Tìm học sinh theo mã:


      SELECT * FROM student WHERE student_number = '1001'

Tìm học sinh theo số điện thoại:


      SELECT * FROM student WHERE phone LIKE '%01234%'

Liệt kê tên/điểm trung bình của các học sinh theo thứ tự điểm giảm dần:


      SELECT student_name, gpa FROM student ORDER BY gpa DESC

3. Cập nhật bản ghi

Cú pháp chung:


      UPDATE table_name SET field_1=value_1,field_2=value_2,...
      WHERE dieu_kien

Giải thích:

table_name: tên bảng cần tạo
field_i: các trường (cột) dữ liệu của bảng
value_i: giá trị của từng trường dữ liệu của bản ghi sẽ cập nhật.
dieu_kien: điều kiện tìm kiếm, tương tự như trong lệnh select

Ví dụ:


      UPDATE student SET student_name='Nguyen Van An', gpa=8.0 WHERE student_number='1001'

4. Xoá bản ghi

Cú pháp chung:


      DELETE FROM table_name
      WHERE dieu_kien

Giải thích:

table_name: tên bảng cần tạo
dieu_kien: điều kiện tìm kiếm, tương tự như trong lệnh select/update

Ví dụ:


      DELETE FROM student WHERE CHAR_LENGTH(phone) > 11

III. Các truy vấn dữ liệu trên nhiều bảng (JOIN)

1. Các loại liên kết giữa các bảng

Liên kết một-một

Mỗi bản ghi của bảng A chỉ liên kết với 1 bản ghi của bảng B và ngược lại.

Ví dụ:

Mỗi sinh viên (Student) có 1 tài khoản (User), mỗi tài khoản chỉ có tối đa một sinh viên tương ứng.

Liên kết một-nhiều

Mỗi bản ghi của bảng A có thể liên kết với nhiều bản ghi của bảng B. Mỗi bản ghi của bảng B chỉ liên kết với 1 bản ghi của bảng A.

Ví dụ:

Một tác giả (Author) có thể có nhiều sách (Book). Mỗi quyển sách chỉ có 1 tác giả.

Liên kết nhiều-nhiều

Một bản ghi của bảng A có thể liên kết với nhiều bản ghi của bảng B, một bản ghi của bảng B có thể liên kết với nhiều bản ghi của bảng A.

Ví dụ:

Một sinh viên có thể tham gia nhiều lớp học, mỗi lớp học có nhiều sinh viên.

2. Join giữa các bảng

Lệnh Join được dùng để mở rộng một bảng trong CSDL với 1 bảng liên kết để lấy thêm các trường thông tin từ bảng liên kết.

Ví dụ:

Trong bảng Book không chứa thông tin tên tác giả mà chỉ chứa id của tác giả (author_id).

Để lấy về thông tin tên tác giả cho mỗi cuốn sách, cần Join bảng Book với bảng Author qua điều kiện liên kết book.author_id=author.id


      SELECT book.*, author.name as author_name
      FROM book JOIN author ON book.author_id=author.id

Sau khi thực hiện join với bảng Author, thông tin tên tác giả sẽ được nối vào bên phải bảng Book

Left Join

Với lệnh Join ở trên (hay còn gọi là Inner Join), nếu bản ghi trong bảng liên kết không tồn tại, bản ghi thuộc bảng gốc sẽ không được trả về trong danh sách kết quả. Với lệnh Left Join, nếu bản ghi trong bảng liên kết không tồn tại, bản ghi của bảng gốc vẫn được trả về trong danh sách kết quả, nhưng phần mở rộng (thuộc bảng liên kết) sẽ nhận giá trị NULL.


      SELECT book.*, author.name as author_name
      FROM book LEFT JOIN author ON book.author_id=author.id

IV. Các lệnh Aggregation và Group

Các lệnh Aggregation được dùng để biến một tập bản ghi thành một bản ghi duy nhất thông qua một hàm kết hợp (aggregate/reduce function)

Hàm Aggregate	Tác dụng	Ví dụ	Giải thích
SUM	Tính tổng của một trường từ danh sách bản ghi	`SELECT SUM(qty) FROM order_detail WHERE product_id=1`	Tổng số lượng sản phẩm có ID bằng 1 từ các đơn hàng
MIN	Tính giá trị nhỏ nhất của một trường từ danh sách bản ghi	`SELECT MIN(price) FROM product WHERE category_id=1`	Giá thấp nhất của sản phẩm trong nhóm 1.
MAX	Tính giá trị lớn nhất của một trường từ danh sách bản ghi	`SELECT MAX(qty*price_unit) FROM order_detail`	Đơn hàng có số tiền lớn nhất.
GROUP_CONCAT	Gộp giá trị một trường của các bản ghi thành một	`SELECT GROUP_CONCAT( student_name, ', ' ) FROM student WHERE gpa>=8.0`	Danh sách sinh viên có điểm trung bình từ 8.0 trở lên.

GROUP BY

Các lệnh Aggregation thường đi kèm với lệnh group by: lệnh group by được dùng để chia tập bản ghi thành các tập bản ghi nhỏ theo giá trị của một trường, sau đó lệnh Aggregation được dùng để tính các giá trị đặc trưng cho các tập bản ghi con

Ví dụ:


      SELECT product_id, SUM(qty)
      FROM order_detail
      GROUP BY product_id

Lệnh trên chia danh sách các đơn hàng thành các nhóm theo từng sản phẩm, sau đó tính tổng số lượng sản phẩm của mỗi nhóm.

Lưu ý: các trường đơn (không Aggregate) trong mệnh đề WHERE phải nằm trong danh sách các trường group by

HAVING

Mệnh đề Having được dùng trong lệnh group by, khi điều kiện tìm kiếm là một biểu thức Aggregation

Ví dụ:


      SELECT product_id, SUM(qty)
      FROM order_detail
      GROUP BY product_id
      HAVING COUNT(customer_id) > 1

Lệnh trên lấy danh sách sản phẩm kèm số lượng từ các đơn hàng với điều kiện sản phẩm được mua bởi nhiều hơn 1 khách hàng.

V. Kết nối database server bằng Python

Để kết nối với database server, cần cài đặt thư viện client tương ứng. Ví dụ, để kết nối MySQL từ Python, cần cài đặt thư viện mysqlclient


      pip install mysqlclient

Kết nối với MySQL server từ Python


      import MySQLdb
      db_server = "localhost"
      db_username = "admin"
      db_password = "abc@123"
      db_name = "testdb"
      db = MySQLdb.connect(db_server, db_username, db_password, db_name, charset='utf8')

Để kết nối tới MySQL server, cần truyền vào các tham số sau cho hàm MySQLdb.connect:

db_server: Địa chỉ IP của MySQL Server
db_username: Username của tài khoản truy nhập MySQL Server
db_password: Password của tài khoản truy nhập MySQL Server
db_name: Tên của database muốn truy nhập

Thao tác thêm dữ liệu


      import MySQLdb
      db_server = "localhost"
      db_username = "admin"
      db_password = "abc@123"
      db_name = "testdb"
      db = MySQLdb.connect(db_server, db_username, db_password, db_name, charset='utf8')
      cursor = db.cursor()

      student_list = [
        {'student_number': '1001', 'student_name': 'Nguyen Van A', 'phone': '012345678', 'gpa': 7.5},
        {'student_number': '1002', 'student_name': 'Nguyen Van B', 'phone': '012345679', 'gpa': 8.0},
      ]

      try:
          for student in student_list:
              cursor.execute(
                'INSERT INTO student(student_number, student_name, phone, gpa) VALUE(%s,%s,%s,%s)',
                  (
                    student['student_number'], 
                    student['student_name'], 
                    student['phone'], 
                    student['gpa']
                  )
                )
          db.commit()

      except Exception as e:
          print(e)
          db.rollback()

      finally:
          db.close()

Thao tác đọc dữ liệu


      import MySQLdb
      db_server = "localhost"
      db_username = "admin"
      db_password = "abc@123"
      db_name = "testdb"
      db = MySQLdb.connect(db_server, db_username, db_password, db_name, charset='utf8')
      cursor = db.cursor()

      cursor.execute("SELECT student_number, student_name, gpa FROM student WHERE gpa >= %s", [7.0])
                                      
      results = cursor.fetchall()

      for record in results:
          student_number, student_name, gpa = record
          print(student_number, student_name, gpa)
          
      db.close()