Tạo cơ sở dữ liệu – Thiết kế cơ sở dữ liệu
Đăng ngày 22-04-2020
Trong bài Thiết kế Cơ sở dữ liệu, chúng ta đã thiết kế được cấu trúc của Cơ sở dữ liệu. Trong bài này chúng ta sẽ bàn về việc chuyển đổi cấu trúc cơ sở dữ liệu đó thành cơ sở dữ liệu lưu trữ được trong hệ quản trị cơ sở dữ liệu.
Chúng ta sẽ sử dụng Cơ sở dữ liệu của hệ thống quản lý đơn hàng để làm ví dụ:
Sơ đồ thực thể quan hệ của Cơ sở dữ liệu cho hệ thống quản lý đơn hàng.
Kiểu dữ liệu
Việc đầu tiên để cài đặt cơ sở dữ liệu là cài đặt các kiểu dữ liệu cho các trường. Tùy theo hệ quản trị cơ sở dữ liệu mà có các kiểu dữ liệu khác nhau và tên gọi khác nhau. Tuy nhiên, về cơ bản nó có các kiểu dữ liệu chính như sau:
| Loại kiểu | Kiểu dữ liệu | Vùng giá trị | Mô tả | Ví dụ |
|---|---|---|---|---|
| Kiểu số | INT | -2147483648 đến 2147483647 | Số nguyên có dấu, kích thước 4 byte | 1,2,3,-5, -7.. |
| TINYINT | -128 tới 127 | Số nguyên có dấu cỡ nhỏ, kích thước 1 byte | 1,2,3,-5, -7.. | |
| SMALLINT | -32768 tới 32767 | Số nguyên có dấu, kích thước 2 byte | 1,2,3,-5, -7.. | |
| BIGINT | -9223372036854775808 tới 9223372036854775807 | Số nguyên có dấu cỡ lớn | 1,2,3,-5, -7. | |
| FLOAT(M,D) | M: số chữ số trước dấu”,” D: số chữ số sau dấu “,” | số thực dấu chấm động | 1.02, 11.503,.. | |
| DOUBLE(M,D) | M: số chữ số trước dấu”,” D: số chữ số sau dấu “,” | số thực dấu chấm động | 1.02, 11.503,.. | |
| Kiểu ngày | DATE | 1000-01-01 đến 9999-12-31/td> | Kiểu dữ liệu ngày tháng, định dạng YYYY-MM-DD | 2020-04-12 |
| DATETIME | 1000-01-01 00:00:00 đến 9999-12-31 23:59:59 | Tổ hợp Date và Time, định dạng YYYY-MM-DD HH:MM:SS | 2020-04-12: 10:40:50 | |
| Kiểu ký tự | CHAR(M) | 1 tới 255 ký tự | Chứa chữ cái và chữ số, một số chứa được unicode một số hệ quản trị có thêm kiểu NCHAR cho unicode | CHAR(5): “JOHN”, “SMITH”… |
| VARCHAR(M) | 1 tới 255 ký tự | Chứa ký tự, nhưng bộ nhớ cấp phát động, phù hợp với các trường có kích thước thay đổi nhiều. | VARCHAR(5): “JOHN”, “SMITH”.. | |
| TEXT/BLOG | Độ dài lớn đến 4000 ký tự | Dành để lưu các trường có dữ liệu ký tự lớn |
Bạn cần xác định dữ liệu dự kiến sẽ lưu trong các trường để chọn kiểu dữ liệu phù hợp cho các trường. Chọn kiểu quá nhỏ sẽ dẫn đến lỗi không lưu hết dữ liệu, chọn kiểu dữ liệu quá lớn sẽ làm lãng phí bộ nhớ và gây chậm ứng dụng. Do vậy, bạn cần cân nhắc khi chọn kiểu dữ liệu cho các trường, nhất là với các hệ thống có lượng dữ liệu lớn.
Index
Khi dữ liệu nhiều lên thì Index có vai trò rất quan trọng, ảnh hưởng trực tiếp đến tốc độ truy xuất của ứng dụng. Để dễ hiểu bạn hình dung thế này, chúng ta có một cuốn sách về Cơ sở dữ liệu có độ dạy 1000 trang, bạn cần tìm phần Thiết kế cơ sở dữ liệu, nếu bạn lật từng trang để tìm sẽ rất lâu mới tìm được, còn nếu bạn mở phụ lục rồi tìm số trang thì sẽ nhanh hơn rất nhiều. Index hoạt động như phụ lục của cuốn sách vậy.
Có 02 loại Index là Clustered Index (Index vật lý) và Non Clustered Index (Index logic).
Clustered Index
Clustered Index là cách sắp xếp các bảng ghi theo thứ tự của trường được index một cách vật lý trong cơ sở dữ liệu. Mỗi bảng chỉ có một Clustered Index. Hầu hết cơ sở dữ liệu chọn cách index này cho khóa chính của bảng.
Ví dụ: Bảng Customer bên dưới sử dụng trường CustomerNo làm clustered index
| CustomerNo | CustomerLastName | CustomerFirstName |
|---|---|---|
| 1 | Nguyễn Văn | A |
| 3 | Trần | Thanh |
| 6 | Nguyễn | Minh |
Nếu bạn thêm bảng ghi mới có CustomerNo = 4 thì dữ liệu sẽ được lưu như bên dưới:
| CustomerNo | CustomerLastName | CustomerFirstName |
|---|---|---|
| 1 | Nguyễn Văn | A |
| 3 | Trần | Thanh |
| 4 | Lê | Các |
| 6 | Nguyễn | Minh |
Dòng số 4 sẽ được chèn vào giữa, đúng với thứ tự tăng dần của trường CustomerNo.
Non Clustered Index
Đối với loại index này thì không ảnh hưởng đến việc lưu trữ vật lý mà nó sẽ tạo ra một bảng tạm để lưu trữ vị trí của bảng ghi đó so với khóa chính hoặc chỉ số dòng của bảng ghi đó.
Ví dụ: Bây giờ chúng ta sẽ tạo ra Non Clustered Index cho trường CustomerFirstName trong bảng Customer và khi chèn bảng ghi mới có CustomerNo = 4 vào.
| CustomerNo | CustomerLastName | CustomerFirstName |
|---|---|---|
| 1 | Nguyễn Văn | A |
| 3 | Trần | Thanh |
| 6 | Nguyễn | Minh |
| 4 | Lê | Các |
Dữ liệu vật lý trong bảng được ghi theo thứ tự thêm vào.
Hệ thống sẽ tạo ra một bảng phụ cho việc index trường CustomerFirstName như sau:
Bảng: CustomerFirstName_Index
| CustomerFirstName | LineNumber |
|---|---|
| A | 1 |
| Các | 4 |
| Minh | 3 |
| Thanh | 2 |
Trong bảng này sẽ sắp xếp dữ liệu cột được index và ghi lại dòng dữ liệu để khi truy xuất vật lý sẽ dùng như cách bạn tìm trang sách qua phụ lục.
Ưu điểm của Non Clustered Index:
- Tăng tốc độ truy xuất, nhất là tìm kiếm và sắp xếp.
Nhược điểm:
- Mỗi khi cập nhật, sửa, xóa dữ liệu liên quan đến trường này hệ quản trị cơ sở dữ liệu phải cập nhật thêm các bảng index.
Với ưu và nhược điểm như vậy, bạn cần cân nhắc khi sử dụng index sao cho hiệu quả.
Sử dụng câu lệnh SQL để tạo Cơ sở dữ liệu
SQL(Structured Query Language) là ngôn ngữ truy vấn dữ liệu, nó được chia ra làm nhiều bộ câu lệnh khác nhau như:
- DML (Data Manipulation Language): chứa các câu lệnh thao tác dữ liệu như SELECT, INSERT, UPDATE, DELETE
- DDL (Data Definition Language): chứa các câu lệnh tạo, xóa cơ sở dữ liệu và đối tượng trong cơ sở dữ liệu như CREATE, DROP, ALTER
- DCL(Data Control Language): chứa các câu lệnh phân quyền như GRANT, REVOKE…
Trong phần này chúng ta sẽ sử dụng các câu lệnh DDL để tạo ra Cơ sở dữ liệu.
Tạo cơ sở dữ liệu
Để tạo cơ sở dữ liệu chúng ta sử dụng câu lệnh CREATE DATABASE theo cú pháp:
CREATE DATABASE Tên_cơ_sở_dữ_liệu
Ví dụ:
CREATE DATABASE InvoiceDB
Cơ sở dữ liệu InvoiceDB sẽ được tạo.
Để tránh trường hợp bị lỗi do cơ sở dữ liệu InvoiceDB đã tồn tại bạn nên sử dụng kết hợp với câu lệnh IF EXISTS hoặc IF NOT EXISTS. Ví dụ:
CREATE DATABASE IF NOT EXISTS InvoiceDB
Lệnh này chỉ tạo cơ sở dữ liệu mới nếu như cơ sở dữ liệu đó chưa tồn tại.
Xóa cơ sở dữ liệu
Để xóa cơ sở dữ liệu bạn dùng câu lệnh DROP DATABASE như sau:
DROP DATABASE Tên_cơ_sở_dữ_liệu
Ví dụ:
DROP DATABASE InvoiceDB
Hoặc
DROP DATABASE IF EXISTS InvoiceDB
Sử dụng cơ sở dữ liệu
Để chỉ định cơ sở dữ liệu đang sử dụng bạn dùng lệnh USE:
USE Tên_cơ_sở_dữ_liệu
Ví dụ:
USE InvoiceDB
Tạo bảng dữ liệu
Để tạo bảng dữ liệu bạn dùng câu lệnh CREATE TABLE với cú pháp như sau:
CREATE TABLE Tên_bảng
(
Tên_cột_1 Kiểu dữ liệu [Thuộc tính của cột]
[, Tên_cột_2 Kiểu dữ liệu [Thuộc tính của cột]
[,Tên_cột_n Kiểu dữ liệu [Thuộc tính của cột]
[, các ràng buộc ở mức bảng]
)
Các thuộc tính phổ biến:
| Thuộc tính | Mô tả |
|---|---|
| NOT NULL | Chỉ định trường này không nhận giá trị null. |
| PRIMARY KEY | Chỉ định trường này làm khóa chính. Khi làm khóa chính nó không nhận giá trị trùng và rỗng. |
| UNIQUE | Chỉ định này không nhận giá trị trùng. |
| DEFAULT value | Chỉ định giá trị mặc định cho trường này, nếu giá trị này không được nhập nó sẽ nhận giá trị được chỉ định trong biến value. |
| AUTO_INCREMENT(n, m) | Chỉ định trường này sẽ nhận giá trị tự tăng, n là giá trị khởi đầu, m là giá trị tăng. Nếu không chỉ định thì n=1 và m=1. |
Các ràng buộc ở mức bảng:
| Thuộc tính | Mô tả |
|---|---|
| PRIMARY KEY | Chỉ định khóa chính cho bảng. |
| FOREIGN KEY | Chỉ định khóa ngoại cho bảng. |
| CHECK | Chỉ định các ràng buộc kiểm tra. |
Ví dụ: Tạo bảng Customer
CREATE TABLE Customer
(
CustomerNo INT PRIMARY KEY AUTO_INCREMENT,
CustomerFirstName VARCHAR(50) NOT NULL,
CustomerLastName VARCHAR(50) NOT NULL,
CustomerAddress VARCHAR(255)
)
Tạo bảng có khóa ngoại
CREATE TABLE Invoice
(
InvoiceNo INT PRIMARY KEY AUTO_INCREMENT,
InvoiceDate DATE NOT NULL,
PaymentMethod VARCHAR(50) NOT NULL,
VAT DOUBLE,
CustomerNo INT NOT NULL,
foreign key fk_invoice_cust (CustomerID) references Customer(CustomerID)
)
Sửa bảng dữ liệu
Một khi bảng đã tồn tại hoặc có dữ liệu mà bạn muốn sửa cấu trúc của bảng, bạn có thể dùng lệnh ALTER TABLE để sửa bảng dữ liệu. Cú pháp như sau:
ALTER TABLE Tên_bảng
{
ADD Tên_cột kiểu_dữ_liệu Thuộc_tính_cột |
DROP Tên_cột |
MODIFY Tên_cột kiểu_dữ_liệu Thuộc_tính_cột
}
Ví dụ: Thêm cột CustomerTaxNo vào bảng Customer
ALTER TABLE Customer
{
ADD CustomerTaxNum CHAR(11) NOT NULL
}
Ngoài ra, bạn cũng có thể dùng lệnh ALTER TABLE để thêm khóa chính, khóa ngoại hay các ràng buộc cho bảng.
Xóa bảng dữ liệu
Bạn sử dụng lệnh DROP TABLE để xóa bảng dữ liệu. Cú pháp:
DROP TABLE Tên_bảng
Ví dụ:
DROP TABLE Invoice
Để tránh xảy ra lỗi khi bạn xóa một bảng dữ liệu không tồn tại bạn có thể kết hợp với lệnh IF EXISTS như sau:
DROP TABLE IF EXIST Invoice
Chỉ xóa khi bảng đó tồn tại.
Tạo Non-Clustered-Index cho bảng
Sử lệnh CREATE INDEX để tạo index cho bảng. Cú pháp như sau:
CREATE INDEX Tên_index ON Tên_bảng
{
Tên_cột_1 [ASC|DESC],
Tên_cột_2 [ASC|DESC]
}
Ví dụ: Tạo index cho trường CustomerFirstName trên bảng Customer để tăng khả năng truy xuất.
CREATE INDEX CustomerFirstName_index ON Customer
{
CustomerFirstName ASC
}
Tạo ra non-clustered index cho trường CustomerFirstName trên bảng Customer và sắp xếp tăng dần.
Vẫn còn nhiều cách dùng các câu lệnh liên quan đến Tạo, Sửa, Xóa các bảng dữ liệu, Index… Các bạn có thể tham khảo thêm ở phần Tạo cơ sở dữ liệu ở trang w3cschool.com.
Ngoài ra, các bạn có thể download file chứa script tạo Cơ sở dữ liệu InvoiceDB ở đây.
Kết luận
Như vậy qua bài viết này chúng ta đã hiểu được kiểu dữ liệu, index và các câu lệnh được sử dụng để tạo ra một cơ sở dữ liệu hoàn chỉnh.
Để khai thác cơ sở dữ liệu hiệu quả bạn cần nắm rõ thêm phần DML nữa. Bạn có thể tham khảo ví dụ về phần này từ trang w3cschool.com hoặc tìm đọc sách về Cơ sở dữ liệu dành cho Lập trình viên của Murach.
Chúc các bạn thành công.
Bài trước: Vấn đề dư thừa dữ liệu trong thiết kế Cơ sở dữ liệu
