Làm Quen với SQL Server

20/09/2020•9 min read

Giới thiệu SQL Server của Microsoft và hướng dẫn thao tác với dữ liệu trong SQL Server.

Giới thiệu

Database là nơi tạo, cập nhật và lưu trữ các thông tin của 1 hệ thống. Nếu không có database, sẽ không có thế giới thông tin, không có cộng đồng mạng hay bất cứ hoạt động giao dịch điện tử nào có thể tồn tại. Hiện nay có nhiều công cụ cho phép tiếp cận và thao tác với database như Microsoft Office Access, My SQL, Oracle, DB2, …

Bài viết giúp làm quen với công cụ SQL Server của Microsoft, phiên bản sử dụng trong bài viết là SQL Server 2014 Express.

SQL là gì

SQL là viết tắt của Structure Query Language – Ngôn ngữ truy vấn mang tính cấu trúc. Đây là 1 chuẩn ngôn ngữ của ANSI (American National Standard Institute – Viện Tiêu chuẩn Quốc gia Hoa Kỳ), là 1 loại ngôn ngữ máy tính để thao tác với dữ liệu từ 1 hệ quản trị cơ sở dữ liệu.

Các công cụ SQL Server hay My SQL, Oracle, DB2, ... đều hoạt động dựa trên nền tảng SQL.

Cách sử dụng SQL Server

Khởi động SQL Server, nhấn vào New Query trên thanh công cụ hoặc vào menu File → New → Query With Current Connection.

Hoặc sử dụng phím tắt Ctrl + N để tạo mới 1 file truy vấn.

Bắt đầu xây dựng 1 CSDL mới trong SQL Server.

Định nghĩa dữ liệu

CREATE

Từ khoá CREATE dùng để tạo ra 1 CSDL hay quan hệ mới chưa tồn tại.

Để tạo ra CSDL STDIO_AUTHORS_MANAGEMENT, thực hiện như sau:

CREATE DATABASE STDIO_AUTHORS_MANAGEMENT

Trong đó STDIO_AUTHORS_MANAGEMENT là tên CSDL sẽ được tạo ra. Cuối dòng code không cần thiết phải có dấu ;.

Để quản lý các tác giả (author) của STDIO, tạo và thiết lập 1 số quan hệ như sau:

CREATE TABLE AUTHOR
(
    AUTHORID            smallint,
    AUTHORNAME          char(10),
    AUTHORTYPE          char(4),
    FIELD               varchar(30),
    ARTICLE             smallint,

    CONSTRAINT PK_AUTHORID PRIMARY KEY (AUTHORID)
)

CREATE TABLE AUTHOR_TYPE
(
    AU_TYPEID           char(4),
    TYPENAME            char(10),

    CONSTRAINT PK_AUTYPEID PRIMARY KEY (AU_TYPEID)
)

CREATE TABLE ARTICLE
(
    ARTICLEID            smallint,
    ARTICLETITLE         char(10),
    ARTICLETYPE          char(4),
    AUTHORID             smallint,
    VIEWS_COUNT          smallint,
    LIKES_COUNT          smallint,

    CONSTRAINT PK_ARTICLEID PRIMARY KEY (ARTICLEID)
)

CREATE TABLE ARTICLE_TYPE
(
    AR_TYPEID           char(4),
    TYPENAME            char(10),

    CONSTRAINT PK_ARTYPEID PRIMARY KEY (AR_TYPEID)
)

Các thuộc tính của mỗi quan hệ có cấu trúc: <tên thuộc tính> <kiểu dữ liệu>. Các thuộc tính cách nhau bởi dấu , và được đặt trong cặp dấu () sau khi tạo 1 quan hệ mới.

ALTER

Từ khoá ALTER dùng để chỉnh sửa cấu trúc của các quan hệ đã được tạo ra trước đó. SQL Server cho phép thay đổi kiểu dữ liệu của các thuộc tính, thêm/xoá thuộc tính, thêm/sửa khoá chính, khoá ngoại,...

Ở ví dụ trên, char(10) sẽ không đủ để lưu trữ họ tên của các Author, Article, … Do đó, tiến hành sửa lại các thuộc tính này như sau:

ALTER TABLE AUTHOR ALTER COLUMN AUTHORNAME varchar(50)
ALTER TABLE AUTHOR_TYPE ALTER COLUMN TYPENAME varchar(50)
ALTER TABLE ARTICLE ALTER COLUMN ARTICLETITLE varchar(50)
ALTER TABLE ARTICLE_TYPE ALTER COLUMN TYPENAME varchar(50)

Trong quan hệ ARTICLE, muốn thêm 1 thuộc tính đánh dấu ngày hoàn thành bài viết:

ALTER TABLE ARTICLE ADD LASTMODIFIED smalldatetime

DROP

Từ khoá DROP dùng để xoá 1 thuộc tính, 1 quan hệ hay CSDL đang tồn tại. Bằng cách kết hợp với các từ khoá như COLUMN, TABLE, DATABASE, …, sẽ xoá được các đối tượng tương ứng cần thiết.

Để có thể xoá được CSDL hay các quan hệ, thuộc tính, chúng phải không còn bị ràng buộc bởi các đối tượng khác. Ràng buộc là những khoá ngoại (Foreign Key) liên kết đến 1 hoặc 1 vài field trong quan hệ. Do đó, cần xoá tuần tự các quan hệ không bị ràng buộc rồi sau cùng mới xoá CSDL.

PRIMARY KEY/FOREIGN KEY

Primary Key (khoá chính) là 1 hoặc 1 vài thuộc tính đặc trưng cho quan hệ. 1 Primary Key cần có đủ các yếu tố sau:

Có thể phân biệt được các bộ (tuple) trong cùng 1 quan hệ.
Có ít thuộc tính nhất có thể.

Nếu có nhiều hơn 1 khoá thoả cả 2 điều kiện trên thì có thể chọn bất kỳ khoá nào làm khoá chính. Các khoá còn lại không được chọn gọi là khoá tương đương.
Đối với CSDL STDIO_AUTHORS_MANAGEMENT, xác định Primary Key cho từng quan hệ. Ngoài ra có thể xác định Primary Key sau khi đã tạo quan hệ bằng từ khoá ALTER đề cập ở trên. Cú pháp như sau:

ALTER TABLE <tên quan hệ> [ADD CONSTRAINT <tên khoá chính>] PRIMARY KEY (<tên thuộc tính>)

Trong đó, phần ADD CONSTRAINT là không bắt buộc, vì hệ thống sẽ tự động phát sinh ngẫu nhiên tên cho Primary Key. Tuy nhiên, để dễ quản lý và thao tác nên tự đặt tên cho các khoá.

Foreign Key (khoá ngoại) là 1 hoặc 1 vài thuộc tính trong 1 quan hệ trỏ đến 1 khoá (thường là khoá chính). Kiểu tham chiếu này là cách liên kết các thông tin lại với nhau và là 1 phần quan trọng trong quá trình chuẩn hoá dữ liệu.

Khoá ngoại có thể trỏ đến khoá chính trong cùng 1 quan hệ. Những khoá ngoại này được gọi là khoá ngoại đệ quy.

Thiết lập các khoá ngoại cho CSDL ở trên như sau:

ALTER TABLE AUTHOR ADD CONSTRAINT FK_AUTHORTYPE FOREIGN KEY(AUTHORTYPE) REFERENCES AUTHOR_TYPE(TYPEID)
ALTER TABLE ARTICLE ADD CONSTRAINT FK_AUTHORID FOREIGN KEY(AUTHORID) REFERENCES AUTHOR(AUTHORID)
ALTER TABLE ARTICLE ADD CONSTRAINT FK_ARTICLETYPE FOREIGN KEY(ARTICLETYPE) REFERENCES ARTICLE_TYPE(TYPEID)

Từ khoá REFERENCES có chức năng liên kết thuộc tính với khoá trong quan hệ phía sau. Ở dòng 1, thuộc tính AUTHORTYPE trong quan hệ AUTHOR sẽ tham chiếu đến thuộc tính TYPEID trong quan hệ AUTHOR_TYPE.

Thao tác với dữ liệu

INSERT

Sau khi đã thiết lập xong các thuộc tính cho các quan hệ của CSDL, tiến hành thêm dữ liệu vào bằng từ khoá INSERT. Thao tác như sau:

--INPUT FOR TABLE AUTHOR
INSERT INTO AUTHOR VALUES(1, 'La Kien Vinh', 'AU01', NULL, NULL)
INSERT INTO AUTHOR(AUTHORNAME, AUTHORID, AUTHORTYPE) VALUES('Vu Quang Huy', 3, 'AU01')
INSERT INTO AUTHOR VALUES(70, 'Amy Le', 'AU01', 'C++', NULL)
INSERT INTO AUTHOR VALUES(6, 'Dat Hoang Tien', 'AU02', NULL, NULL)
INSERT INTO AUTHOR VALUES(9, 'Tran Danh', 'AU02', NULL, NULL)
INSERT INTO AUTHOR VALUES(71, 'Hieu Nguyen Minh', 'AU02', NULL, NULL)
INSERT INTO AUTHOR VALUES(108, 'Hoa Dinh', 'AU01', NULL, NULL)

--INPUT FOR TABLE AUTHOR_TYPE
INSERT INTO AUTHOR_TYPE VALUES ('AU01', 'Periodic Author')
INSERT INTO AUTHOR_TYPE VALUES ('AU02', 'Free Author')

--INPUT FOR TABLE ARTICLE
INSERT INTO ARTICLE VALUES (1, 'STDIO Coding Convention - Level 1', 'AR10', 1, NULL, NULL, 15/7/2014)
INSERT INTO ARTICLE VALUES (2, 'Quan Ly Bo Nho Trong Cocos2d-x', 'AR05', 2, NULL, NULL, 29/9/2014)
INSERT INTO ARTICLE VALUES (3, 'Phat Hien Memory Leak Voi VLD', 'AR01', 3, NULL, NULL, 13/10/2014)
INSERT INTO ARTICLE VALUES (4, 'Pointer Trong C++', 'AR01', 74, NULL, NULL, 10/8/2014)
INSERT INTO ARTICLE VALUES (5, 'Preprocessor Va #include Directive', 'AR01', 70, NULL, NULL, 28/7/2014)
INSERT INTO ARTICLE VALUES (6, 'C++11 - Smart Pointer - Quan Ly Tai Nguyen', 'AR01', 6, NULL, NULL, 6/10/2014)
INSERT INTO ARTICLE VALUES (7, 'Ban Chat Cua Bien Trong C/C++', 'AR01', 9, NULL, NULL, 27/5/2014)
INSERT INTO ARTICLE VALUES (8, 'Template Trong C++', 'AR01', 61, NULL, NULL, 3/6/2014)
INSERT INTO ARTICLE VALUES (9, 'Thao Tac Voi Constant Trong C++', 'AR01', 71, NULL, NULL, 17/9/2014)
INSERT INTO ARTICLE VALUES (10, 'Tim Hieu Va Tao Tai Lieu XML Dau Tien', 'AR04', 108, NULL, NULL, 16/9/2014)
INSERT INTO ARTICLE VALUES (11, 'Xu Ly Thoi Gian Trong PHP', 'AR03', 107, NULL, NULL, 27/9/2014)

--INPUT FOR TABLE ARTICLE_TYPE
INSERT INTO ARTICLE_TYPE VALUES ('AR01', 'C/C++')
INSERT INTO ARTICLE_TYPE VALUES ('AR02', 'C#')
INSERT INTO ARTICLE_TYPE VALUES ('AR03', 'PHP')
INSERT INTO ARTICLE_TYPE VALUES ('AR04', 'XML')
INSERT INTO ARTICLE_TYPE VALUES ('AR05', 'Cocos2D-x')
INSERT INTO ARTICLE_TYPE VALUES ('AR06', 'Visual Studio')
INSERT INTO ARTICLE_TYPE VALUES ('AR07', 'Math')
INSERT INTO ARTICLE_TYPE VALUES ('AR08', 'Data Structure')
INSERT INTO ARTICLE_TYPE VALUES ('AR09', 'Experience')
INSERT INTO ARTICLE_TYPE VALUES ('AR10', 'STDIO Experience')

Dòng 2 - 4 là các cách nhập dữ liệu cho các quan hệ. Xác định các thuộc tính sẽ được nhập dữ liệu, hoặc thay đổi thứ tự nhập liệu,... Ngoài ra có thể sử dụng NULL để nhập dữ liệu cho các thuộc tính chưa được xác định giá trị.

UPDATE

Từ khoá UPDATE được sử dụng trong trường hợp cần thay đổi giá trị trong 1 quan hệ.

Thông thường, cần thêm 1 vài điều kiện để update như mong muốn. Chương trình sẽ duyệt qua từng tuple, kết hợp với điều kiện và thay đổi giá trị tương ứng nếu điều kiện đúng.

UPDATE AUTHOR SET FIELD = 'XML' WHERE AUTHORID = 108
UPDATE AUTHOR SET FIELD = 'PHP' WHERE AUTHORNAME = 'Jason Bui'

Ngoài ra, cũng có thể thay đổi nhiều thuộc tính cùng 1 lúc. Các thuộc tính được cách nhau bởi dấu ,.

DELETE

Từ khoá DELETE được sử dụng để xoá 1 hoặc nhiều tuple trong 1 quan hệ. Các tuple thoả điều kiện sẽ được xoá khỏi quan hệ.

Cú pháp delete như sau:

DELETE FROM <tên quan hệ> WHERE <điều kiện>

Nếu không có điều kiện, toàn bộ các tuple sẽ được xoá khỏi quan hệ.

Truy vấn dữ liệu

Tùy vào yêu cầu đề bài mà sử dụng câu truy vấn cho phù hợp. 1 câu truy vấn đầy đủ có dạng như sau:

SELECT [DISTINCT] <danh sách thuộc tính/hàm>
FROM <tên các quan hệ>
[WHERE <điều kiện chọn>]
[GROUP BY] <thuộc tính>]
[HAVING <điều kiện>]
[ORDER BY <thuộc tính> ASC/DESC]

SELECT là phép chiếu lấy các thuộc tính, hàm cần thiết. Các thuộc tính cách nhau bởi dấu ,. Từ khoá DISTINCT (không bắt buộc) được sử dụng để loại bỏ những dòng trùng nhau.
FROM được sử dụng để chọn ra 1 hoặc 1 vài quan hệ cần thiết cho câu truy vấn.
WHERE: Điều kiện chọn, sử dụng các phép nối luận lý (AND, OR, NOT, …) và các phép so sánh.
WHERE, GROUP BY, HAVING, ORDER BY không bắt buộc có mặt trong câu truy vấn. Tuỳ vào yêu cầu thực tế mà sử dụng cho phù hợp.

1 vài câu truy vấn minh hoạ:

Xuất danh sách các Periodic Author của STDIO (ID, Tên)

SELECT AUTHORID, AUTHORNAME FROM AUTHOR
WHERE AUTHORTYPE = 'AU01'

Xuất danh sách các tác phẩm ở chủ đề C/C++ (ID, Tên tác phẩm, Tên tác giả)

SELECT AR.ARTICLEID, AR.ARTICLETITLE, AU.AUTHORNAME FROM ARTICLE AR, AUTHOR AU
WHERE AR.AUTHORID = AU.AUTHORID AND AR.ARTICLETYPE = 'AR01'