Môn học hệ quản trị cơ sở dữ liệu sql server ngành nghề thiết kế trang web trường CĐN Đà Lạt

LỜI GIỚI THIỆU

Vài nét về xuất xứ giáo trình:

Giáo trình này được viết theo căn cứ Thông tư số 03/2017/TT-BLĐTBXH ngày 01 tháng 03 năm 2017 của Bộ Lao động – Thương binh và Xã hội về việc Quy định về quy trình xây dựng, thẩm định và ban hành chương trình; tổ chức biên soạn, lựa chọn, thẩm định giáo trình đào tạo trình độ trung cấp, trình độ cao đẳng.

Quá trình biên soạn:

Giáo trình này được biên soạn có sự tham gia tích cực của các giáo viên có kinh nghiệm, cùng với những ý kiến đóng góp quý báu của các chuyên gia về lĩnh vực công nghệ thông tin.

Mối quan hệ của tài liệu với chương trình, mô đun/môn học:

Căn cứ vào chương trình đào tạo nghề Thiết kế trang web, giáo trình giúp cung cấp cho người học những kiến thức cơ bản về ngôn ngữ truy vấn SQL Server cũng như kỹ năng sử dụng phần mềm SQL Server để thiết kế cơ sở dữ liệu quan hệ.

Để học được mô đun này người học cần có kiến thức cơ bản về cơ sở dữ liệu.

Cấu trúc chung của giáo trình này bao gồm 6 bài:

Bài 1. Tổng quan về DBMS và SQL sever Bài 2. Ngôn ngữ định nghĩa dữ liệu

Có thể bạn quan tâm!

Xem toàn bộ 128 trang tài liệu này.

Bài 3. Ngôn ngữ thao tác dữ liệu

Bài 4. Làm việc với View ( khung nhìn ) Bài 5. Bảo mật trong SQL

Bài 6. Thủ tục lưu trữ , hàm và trigger

Lời cảm ơn

Giáo trình được biên soạn trên cơ sở các văn bản quy định của Nhà nước và tham khảo nhiều tài liệu liên quan có giá trị. Song chắc hẳn quá trình biên soạn không tránh khỏi những thiếu sót nhất định. Ban biên soạn mong muốn và thực sự cảm ơn những ý kiến nhận xét, đánh giá của các chuyên gia, các thầy cô đóng góp cho việc chỉnh sửa để giáo trình ngày một hoàn thiện hơn.

Lâm Đồng, ngày 10 tháng 12 năm 2019

Tham gia biên soạn

1. Phạm Đình Nam

2. Ngô Thiên Hoàng

3. Nguyễn Quỳnh Nguyên

4. Phan Ngọc Bảo

MỤC LỤC

BÀI 1. Tổng quan về DBMS và SQL sever 6

1. Tổng quan về DBMS và SQL Sever 6

1.1Tổng quan về DBMS 6

1.2 Tổng quan về cơ sở dữ liệu quan hệ 8

1.3 Giới Thiệu SQL Server 2000 10

1.4 Cài Ðặt SQL Server 2000 (Installation) 11

chọn các giá trị mặc định (default) 11

ra nên chọn các giá trị mặc định (default) 14

2. Sơ lược về SQL 15

2.1 Kiểu dữ liệu 18

2.2 Các tập tin vật lý lưu trữ cơ sở dữ liệu 20

Kết chương 21

Bài 2. Ngôn ngữ định nghĩa dữ liệu 22

1. Ngôn ngữ định nghĩa dữ liệu 22

1.1 Tạo CSDL 22

1.2 Tạo bảng dữ liệu 26

1.3 Tạo cấu trúc bảng bằng T-SQL 28

1.4 Ràng buộc CHECK 30

1.5 Ràng buộc PRIMARY KEY 31

1.6 Ràng buộc UNIQUE 32

1.7 Ràng buộc FOREIGN KEY 33

2. Sửa đổi định nghĩa bảng 35

3. Xoá bảng 36

Bài 3. Ngôn ngữ thao tác dữ liệu 38

1. Ngôn ngữ thao tác dữ liệu 38

1.1 Truy xuất dữ liệu với câu lệnh SELECT 38

1.1.1Mệnh đề FROM 39

1.1.2 Danh sách chọn trong câu lệnh SELECT 39

1.1.3 Tạo mới bảng dữ liệu từ kết quả của câu lệnh SELECT 47

1.1.4 Sắp xếp kết quả truy vấn 47

1.1.5 Phép hợp 48

1.1.6 Phép nối 50

1.2 Phép nối 53

1.2.1 Các loại phép nối 53

1.2.2 Sử dụng phép nối trong SQL2 57

1.2.3 Thống kê dữ liệu với GROUP BY 59

1.2.4 Thống kê dữ liệu với COMPUTE 62

1.2.5 Truy vấn con (Subquery) 65

2. Bổ sung, cập nhật và xoá dữ liệu 68

2.1 Bổ sung dữ liệu 68

2.2 Cập nhật dữ liệu 69

2.3 Xoá dữ liệu 71

Bài tập chương 3 72

Bài 4. Làm việc với View ( khung nhìn ) 76

1 Khái niệm view (Khung nhìn) 76

2 Tạo khung nhìn 77

3 Cập nhật, bổ sung và xoá dữ liệu thông qua khung nhìn 79

4 Sửa đổi khung nhìn 81

5 Xoá khung nhìn 81

Bài tập chương 3 82

Lời giải 83

Bài 5. Bảo mật trong SQL 86

1 Các khái niệm 86

2 Cấp phát quyền 86

3 Cấp phát quyền thực thi các câu lệnh 88

4 Thu hồi quyền 89

Bài 6. Thủ tục lưu trữ , hàm và trigger 92

1. Thủ tục lưu trữ (stored procedure) 92

1.1 Các khái niệm 92

1.2 Tạo thủ tục lưu trữ 92

1.3 Lời gọi thủ tục lưu trữ 94

1.4 Sử dụng biến trong thủ tục 94

1.5 Giá trị trả về của tham số trong thủ tục lưu trữ 95

1.6 Tham số với giá trị mặc định 95

1.7 Sửa đổi thủ tục 96

1.8 Xoá thủ tục 96

2. Hàm và trigger 97

2.1 Hàm do người dùng định nghĩa 97

2.2 Trigger 100

Bài tập chương 5 108

Bài 6. Giao dịch SQL 111

1 Giao dịch và các tính chất của giao dịch 111

2 Mô hình giao dịch trong SQL 111

3 Giao dịch lồng nhau 114

Phụ lục: Cơ sở dữ liệu mẫu 116

Bài 1. Tổng quan về DBMS và SQL sever

1. Tổng quan về DBMS và SQL Sever

Chương này trình bày một cách nhìn khái quát về cơ sở dữ liệu (CSDL/DB), về hệ quản trị cơ sở dữ liệu (HQTCSDL/DBMS) và về hệ cơ sở dữ liệu (HCSDL/DBS). Các đòi hỏi khi xây dựng một HQTCSDL đó cũng chính là những chức năng mà một HCSDL cần phải có.

Trong chương này chúng tôi cũng muốn giới thiệu tổng quan về ngôn ngữ hỏi có cấu trúc (SQL) và các hệ quản trị cơ sở dữ liệu quan hệ là một trong những nền tảng kỹ thuật quan trọng trong công nghiệp máy tính. Cho đến nay, có thể nói rằng SQL đã được xem là ngôn ngữ chuẩn trong cơ sở dữ liệu. Các hệ quản trị cơ sở dữ liệu quan hệ thương mại hiện có như Oracle, SQL Server, Informix, DB2,... đều chọn SQL làm ngôn ngữ cho sản phẩm của mình

Vậy thực sự SQL là gì? Tại sao nó lại quan trọng trong các hệ quản trị cơ sở dữ liệu? SQL có thể làm được những gì và như thế nào? Nó được sử dụng ra sao trong các hệ quản trị cơ sở dữ liệu quan hệ? Chương này sẽ cung cấp cho chúng ta cái nhìn tổng quan về SQL và một số vấn đề liên quan.

Ta tìm hiểu DBMS trên một HQCSDL cụ thể: SQL Server 2000. Do vậy chương này giới thiệu cài đặt SQL Server 2000 và các thành phần của nó, giúp chúng ta chủ động khai thác trong nắm bắt và tạo lập ứng dụng.

1.1Tổng quan về DBMS

MỘT SỐ KHÁI NIỆM

Một cơsở dữ liệu - CSDL(DataBase): Là một kho dữ liệu được tổ chức theo một nguyên tắc nào đó. Đó là một tập hợp các tập tin có liên quan với nhau, được thiết kế nhằm làm giảm thiểu sự dư thừa dữ liệu, đảm bảo tính tin cậy khi truy xuất dữ liệu. Các tập tin này chứa các thông tin biểu diễn các đối tượng trong một ứng dụng thế giới thực.

CSDL lưu giữ thông tin của một trường đại học như : khoa, giảng viên, sinh viên, khóa học,…

Thông thường, một cơ sở dữ liệu sẽ bao trùm tất cả các thông tin của một ứng dụng, không nên đặt hai cơ sở dữ liệu vào một ứng dụng.

Hệ quản trị cơ sở dữ liệu DBMS(DataBaseManagement System): là một hệ thống gồm một CSDL và các thao tác trên CSDL. Đó là hệ thống chương trình, công cụ cho phép quản lý và tương tác với CSDL. Trên đó người dùng có thể định nghĩa, thao tác, và xử lí dữ liệu trong một CSDL để xuất ra những thông tin có nghĩa.

Ví dụ 1-5 : một DBMS có thể quản trị cơ sở dữ liệu của một trường đại học cũng như những cơ sở dữ liệu có ý nghĩa khác như : cơ sở dữ liệu phục vụ tổng thu nhập quốc gia,

một cơ sở dữ liệu liên hợp quốc về dữ liệu địa lý thế giới,v..v…

- Một hệ cơ sở dữ liệu (HCSDL/ DBS: DataBase System) là một phần mềm cho phép xây dựng một HQTCSDL.

Các vấn đề cần xử lý của hệ cơ sở dữ liệu

Một số điểm bất lợi chính của việc lưu giữ thông tin có tổ chức trong hệ thống xử lý file thông thường mà hệ HCSDL cần lưu ý:

• Dư thừa dữ liệu và tính không nhất quán (Data redundancy and inconsistency) : Do các file và các trình ứng dụng được tạo ra bởi các người lập trình khác nhau, nên các file có định dạng khác nhau, các chương trình được viết trong các ngôn ngữ lập trình khác nhau, cùng một thông tin có thể được lưu giữ trong các file khác nhau. Tính không thống nhất và dư thừa này sẽ làm tăng chi phí truy xuất và lưu trữ, hơn nũa, nó sẽ dẫn đến tính không nhất quán của dữ liệu: các bản sao của cùng một dữ liệu có thể không nhất quán .

• Khó khăn trong việc truy xuất dữ liệu: Môi trường của hệ thống xử lý file thông thường không cung cấp các công cụ cho phép truy xuất thông tin một cách hiệu quả và thuận lợi.

• Sự cô lập dữ liệu(Data isolation) : Các giá trị dữ liệu được lưu trữ trong cơ sở dữ liệu phải thoả mãn một số các ràng buộc về tính nhất quán của dữ liệu ( ràng buộc nhất quán

/ consistency contraints ).

Trong hệ thống xử lý file thông thường, rất khó khăn trong việc thay đổi các chương trình để thoả mãn các yêu cầu thay đổi ràng buộc. Vấn đề trở nên khó khăn hơn khi các ràng buộc liên quan đến các hạng mục dữ liệu nằm trong các file khác nhau.

• Các vấn đề về tính nguyên tử (Atomicity problems):

Tính nguyên tử của một hoạt động (giao dịch) là: hoặc nó được hoàn tất trọn vẹn hoặc không có gì cả . Điều này có nghĩa là một hoạt động (giao dịch) chỉ làm thay đổi các dữ liệu bền vững khi nó đã hoàn tất (kết thúc thành công) nếu không, giao dịch không để lại một dấu vết nào trên CSDL. Trong hệ thống xử lý file thông thường khó đảm bảo được tính chất này.

• Tính bất thường trong truy xuất cạnh tranh : Một hệ thống cho phép nhiều người sử dụng cập nhật dữ liệu đồng thời, có thể dẫn đến kết quả là dữ liệu không nhất quán. Điều này đòi hỏi một sự giám sát. Hệ thống xử lý file thông thường không cung cấp chức năng này.

• Vấn đề an toàn (Security problems): một người sử dụng hệ cơ sở dữ liệu không cần thiết và cũng không có quyền truy xuất tất cả các dữ liệu. Vấn đề này đòi hỏi hệ thống phải đảm bảo được tính phân quyền, chống truy xuất trái phép ... Các bất lợi nêu trên đã gợi mở sự phát triển các DBMS. Phần sau của giáo trình sẽ đề cập đến các quan niệm và các thuật toán được sử dụng để phát triển một hệ cơ sở dữ liệu nhằm giải quyết các vấn đề nêu trên .

Hầu hết các hệ quản trị CSDL đều thực hiện các chức năng sau :

Lưu trữ dữ liệu

Tạo ra và duy trìCSDL

Cho phép nhiềungười dùngtruy xuất đồngthời Hỗ trợ tính bảo mật và riêng tư

Cho phép xem và xử lý dữ liệu lưu trữ

chọn

Cho phép cập nhật và lưu trữ dữ liệu sau khi cập nhật

Cung cấp một cơ chế chỉ mục (index) hiệu quả để truy cập nhanh các dữ liệu lựa

Cung cấp tính nhất quán giữa các bản ghi khác nhau

Bảo vệ dữ liệu khỏi mất mát bằng các quá trình sao lưu (backup) và phục hồi

(recovery).

1.2 Tổng quan về cơ sở dữ liệu quan hệ

Mô hình dữ liệu quan hệ

Mô hình dữ liệu quan hệ được Codd đề xuất năm 1970 và đến nay trở thành mô hình được sử dụng phổ biến trong các hệ quản trị cơ sở dữ liệu thương mại. Nói một cách đơn giản, một cơ sở dữ liệu quan hệ là một cơ sở dữ liệu trong đó tất cả dữ liệu được tổ chức trong các bảng có mối quan hệ với nhau. Mỗi một bảng bao gồm các dòng và các cột: mỗi một dòng được gọi là một bản ghi (bộ) và mỗi một cột là một trường (thuộc

tính). Hai hay nhiều bảng có thể có liên kết nếu chúng có một hay nhiều trường chung)

Hình 1.1 minh hoạ cho ta thấy được 3 bảng trong một cơ sở dữ liệu

Bảng (Table)

Như đã nói ở trên, trong cơ sở dữ liệu quan hệ, bảng là đối tượng được sử dụng để tổ chức và lưu trữ dữ liệu. Một cơ sở dữ liệu bao gồm nhiều bảng và mỗi bảng được xác định duy nhất bởi tên bảng. Một bảng bao gồm một tập các dòng và các cột: mỗi một dòng trong bảng biểu diễn cho một thực thể (trong hình 1.1, mỗi một dòng trong

bảng SINHVIEN tương ứng với một sinh viên), và mỗi một cột biểu diễn cho một tính chất của thực thể (chẳng hạn cột NGAYSINH trong bảng SINHVIEN biểu diễn cho ngày sinh của các sinh viên được lưu trữ trong bảng).

Như vậy, liên quan đến mỗi một bảng bao gồm các yếu tố sau:

• Tên của bảng: được sử dụng để xác định duy nhất mỗi bảngn trong cơ sở dữ

liệu.

• Cấu trúc của bảng: Tập các cột trong bảng. Mỗi mộtcột trong bảng được xác

định bởi một tên cột và phải có một kiểu dữ liệu nào đó (chẳng hạn cột NGAYSINH trong bảng SINHVIEN ở hình 1.1 có kiểu là DATETIME). Kiểu dữ liệu của mỗi cột qui định giá trị dữ liệu có thể được chấp nhận trên cột đó.

• Dữ liệu của bảng: Tập các dòng (bản ghi) hiện có trong bảng.

Khoá của bảng

Trong một cơ sởdữ liệu được thiết kế tốt, mỗi một bảng phải có một hoặc một tập các cột mà giá trị dữ liệu của nó xác định duy nhất một dòng trong một tập các dòng của bảng.

Tập một hoặc nhiều cột có tính chất này được gọi là khoá của bảng.

Việc chọn khoá của bảng có vai trò quan trọng trong việc thiết kế và cài đặt các cơ sở dữ liệu quan hệ. Các dòng dữ liệu trong một bảng phải có giá trị khác nhau trên khoá.

Bảng MONHOC trong hình dưới đây có khoá là cột MAMONHOC

Bảng MONHOC với khoá chính là MAMONHOC

Một bảng có thể có nhiều tập các cột khác nhau có tính chất của khoá (tức là giá trị của nó xác định duy nhất một dòng dữ liệu trong bảng). Trong trường hợp này, khoá được chọn cho bảng được gọi là khoá chính (primary key) và những khoá còn lại được gọi là khoá phụ hay là khoá dự tuyển (candidate key/unique key).

Mối quan hệ và khoá ngoài

Các bảng trong một cơ sở dữ liệu không tồn tại độc lập mà có mối quan hệ mật thiết với nhau về mặt dữ liệu. Mối quan hệ này được thể hiện thông qua ràng buộc giá trị dữ liệu xuất hiện ở bảng này phải có xuất hiện trước trong một bảng khác. Mối quan