Giáo trình Hệ quản trị cơ sở dữ liệu MS SQL server 2 - Nghề: Lập trình máy tính - CĐ Kỹ Thuật Công Nghệ Bà Rịa-Vũng Tàu

Chia sẻ: Ochuong_999 Ochuong_999 | Ngày: | Loại File: DOCX | Số trang:67

Thêm vào BST

Báo xấu

54
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

(NB) Mục đích của giáo trình là trang bị cho học viên những kiến thức và kỹ năng về xây dựngcác ứng dụng chuyên nghiệp theo mô hình Client/Server trên hệ quản trị cơ sở dữ liệu MS SQL Server; quản trị được hệ quản trị cơ sở dữ liệu MS SQL Server từ xa.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình Hệ quản trị cơ sở dữ liệu MS SQL server 2 - Nghề: Lập trình máy tính - CĐ Kỹ Thuật Công Nghệ Bà Rịa-Vũng Tàu

ỦY BBN NHBN DBN TỈNH BR – VT TRƯỜNH BBB ĐẲNH NHHỀ GBBB TRBN G BÔ ĐUN GỆ QU ẢN TR Ị BB DL BB BQL BBRVBR B TRBNH ĐỘ BBB ĐẲNH NHHỀ Ban hành Bèm theo QuBết định sốB BQĐBBBĐN… ngàB……Btháng…Bnăm BBBBBBBBB …………BBBBBBBBBBB của Hiệu trưởng trường Bao đẳng nghề tỉnh BR B VT
Bà Rịa – Vũng TàuB năm BBBB TUYÊN BỐ BẢN QUYỀN Tài liệu nàB thuộc loại sách giáo trình nên các nguồn thông tin có thể được phép dùng nguBên bản hoặc trích dùng cho các mục đích về đào tạo và tham BhảoB Bọi mục đích Bhác mang tính lệch lạc hoặc sử dụng với mục đích Binh doanh thiếu lành mạnh sẽ bị nghiêm cấmB
LỜB HBHỚB TỆBB U Bông nghệ thông tin ngàB càng phát triển và thậm nhập vào nhiều lĩnh vực trong cuộc sốngB Bong song với sự phát triển đóB là nhu cầu quản lý dữ liệu ngàB càng chặt chẽB Bicrosoft® BQL Berver™ được ví như là trái tim của một nền tảng dữ liệu toàn diệnB cho phép bạn có thể truB cập và gia công dữ liệu cho doanh nghiệp từ các thiết bị Bhác nhauB các nền tảng và dịch vụ dữ liệu trong doanh nghiệpB Vì vậB tác giả đã biên soạn cuốn giáo trình Hệ quản trị BBDL BB BQL Berver B dùng cho học sinh cao đẳng nghềB ngoài ra nó còn là cuốn sách tham Bhảo của nhiều độc giả muốn tìm hiểu về BQL BerverB Bục đích của giáo trình là trang bị cho học viên những Biến thức và Bỹ năng về xâB dựngcác ứng dụng chuBên nghiệp theo mô hình BlientBBerver trên hệ quản trị cơ sở dữ liệu BB BQL Berver B quản trị được hệ quản trị cơ sở dữ liệu BB BQL Berver từ xaB Nội dung chính của giáo trình gồm các phần chínhB Thủ tục lưu trữ (Btore BroceduceBB TriggerB Hàm (FunctionBB Bảo mật trong BQL Berver và Bết nối ứng dụng với cơ sở dữ liệu Bặc dù bản thân đã tham Bhảo các tài liệu và ý Biến tham gia của các đồng nghiệpB song cuốn giáo trình vẫn Bhông tránh Bhỏi những thiếu sótB Bong các bạn đóng góp ý BiếnB Tôi xin cảm ơn các thầB cô Bhoa BNTT–Trường Bao đẳng nghề đã cho tôi các ý Biến đóng góp quý báu để tôi hoàn thiện giáo trình nàBB TBB HBBẢ
B ỤB L ỤB
B GƯƠN G TRBN G BÔ ĐUN GỆ QU ẢN TR Ị BB DL BB BQL BBRVBR B Bã số mô đunB BĐ BB Thời gian mô đunB BBgiờ (Lý thuBếtB BB giờB Thực hànhB BB giờB BB VỊ TRÍB TÍNH BHẤT BỦB BÔ ĐUN Vị trí B Bô đun Hệ quản trị cơ sở dữ liệu Bicrosoft BQL Berver B là mô đun nâng cao của mô đun Hệ quản trị cơ sở dữ liệu Bicrosoft BQL Berver B mà người học đã được học trong giai đoạn BB Tính chất B Để học tốt mô đun nàB bắt buộc người học cần học qua mô đun Hệ quản trị cơ sở dữ liệu Bicrosoft BQL Berver BB BBB BỤB TBÊU BÔ ĐUN XâB dựng được các ứng dụng chuBên nghiệp theo mô hình BlientBBerver trên hệ quản trị cơ sở dữ liệu BB BQL BerverB Quản trị được hệ quản trị cơ sở dữ liệu BB BQL Berver từ xaB Thực hiện đựợc các biện pháp an toàn cho máB tính và vệ sinh công nghiệp BBBB NỘB DUNH BÔ ĐUN BB Nội dung tổng quát và phân phối thời gianB Bố Thời gian Tên các bài Thự Lý T trong Tổng c Kiểm thuBế T mô đun số hàn tra* t h B Thủ tục lưu trữ BB B BB B Trigger BB B BB B Hàm BB B B Bảo mật trong BQL B BB B B Berver Kết nối ứng dụng với cơ B BB B B sở dữ liệu BộngB BB BB BB *Hhi chúB Thời gian Biểm tra được tích hợp giữa lý thuBết với thực hành được tính vào giờ thực hànhB
BB Nội dung chi tiết BBB BB
T GỦ T ỤB L ƯU TR Ữ (BTBRB BRBBB DUBBB Hiới thiệu Để thuận lợi hơn trong việc sử dụng các câu lệnh có cấu trúcB và đáp ứng nhu cầu Bhai thác dữ liệu của người dùngB BQL server cung cấp một số thủ tục lưu trữ và cho phép người dùng tự định nghĩa thêm các thủ tục lưu trữB Bục tiêu Định nghĩa được thủ tục lưu trữ với các Biểu tham sốB Bửa đổi được thủ tục đã định nghĩaB Xóa được các thủ tụcB Nội dung BB Địn h n ghĩa BBs là tập hợp của các câu lệnh TBBQL được biên dịch trước (pre_compiledBBBBs được đặt tên và được xử lý như một Bhối lệnh thống nhất (chứ Bhông phải thực hiện rời rạc các câu lệnhBB BQL Berver cung cấp một số các thủ tục được lưu trữ sẵn trong hệ thống giúp thực hiện một số công việc thường xuBênB Nó được gọi là thủ tục hệ thống –BBstem stored proceduresB Bòn những thủ tục do người sử dụng tự viết gọi là User stored proceduresB BBs trong BQL Berver cũng tương tự như Bhái niệm về thủ tục trong các ngôn ngữ lập trình BhácB bởi vìB Bhấp nhận biến đầu vào và trả lại Bết quả Bhi thực hiệnB Bhứa những câu lệnh dùng trong lập trình có thể thao tác với cơ sở dữ liệu và có thể gọi đến các thủ tục BhácB Trả lại giá trị trạng thái Bhi thủ tục được gọi để xác định việc thực hiện thủ tục thành công haB thất bạiB
Hình BBB Bác thành phần của BBs BB L ợi í ch B hi qu ản lý dữ l iệu bằn g BBs Tăng tốc độ thực hiệnB Bột trong những lợi ích lớn nhất Bhi sử dụng BBs là tốc độB BBs được tối ưu hoá trong ngaB ở lần biên dịch đầu tiênB điều nàB cho phép chúng có thể thực hiện nhanh hơn nhiều lần so với các câu lệnh TBBQL thông thườngB Tốc độ truB cập dữ liệu nhanh hơnB Khi thực thi một câu lệnh BQL thì BQL Berver phải Biểm tra permission xem user gửi câu lệnh đó có được phép thực hiện câu lệnh haB Bhông đồng thời Biểm tra cú pháp rồi mới tạo ra một execute plan và thực thiB Nếu có nhiều câu lệnh như vậB gửi qua networB có thể làm giảm đi tốc độ làm việc của serverB BQL Berver sẽ làm việc hiệu quả hơn nếu dùng stored procedure vì người gửi chỉ gửi một câu lệnh đơn và BQL Berver chỉ Biểm tra một lần sau đó tạo ra một execute plan và thực thiB Nếu stored procedure được gọi nhiều lần thì execute plan có thể được sử dụng lại nên sẽ làm việc nhanh hơnB Ngoài ra cú pháp của các câu lệnh BQL đã được BQL Bever Biểm tra trước Bhi lưu nên nó Bhông cần Biểm lại Bhi thực thiB Bhương trình được modul hoáB Bột Bhi stored procedure được tạo ra nó có thể được sử dụng lạiB Ðiều nàB sẽ làm cho việc bảo trì (maintainabilitBB dễ dàng hơn do việc tách rời giữa business rules (tức là những logic thể hiện bên trong stored procedureB và cơ sở dữ liệuB Ví dụ nếu có một sự thaB đổi nào đó về mặt logic thì ta chỉ việc thaB đổi code bên trong stored procedure mà thôiB Những ứng dụng dùng stored procedure nàB có thể sẽ Bhông cần phải thaB đổi mà vẫn tương thích với business rule mớiB Nhất quánB Lợi ích nữa của BBs là thiết đặt được ràng buộc dữ liệu để đảm bảo tính nhất quánB Người
sử dụng Bhông thể thực hiện tuỳ tiện dữ liệu để làm mất tính đúng đắn của dữ liệuB Nâng cao Bhả năng bảo mật dữ liệuB Hiả sử chúng ta muốn giới hạn việc truB xuất dữ liệu trực tiếp của một user nào đó vào một số bảngB ta có thể viết một stored procedure để truB xuất dữ liệu và chỉ cho phép user đó được sử dụng stored procedure đã viết sẵn mà thôi chứ Bhông thể thao tác trực tiếp trên các bảng đóB Ví dụB ta có thể tạo ra BBs để ta làm chủ và chỉ cung cấp quBền BXBUTB cho những BBs nàBB vì thế những người sử dụng Bhác Bhông được phép trực tiếp làm việc với dữ liệuB Ngoài ra stored procedure có thể được encrBpt (mã hóaB để tăng cường bảo mật B Bá c B iểu BBs BBs chia làm B loạiB BBstem stored proceduresB Thủ tục m những người sử dụng chỉ có quBền thực hiệnB Bhông được phép thaB đổiB User stored proceduresB Thủ tục do người sử dụng tạo và thực hiện BBBB BBst em stor ed pro cedur es Là những stored procedure chứa trong Baster Database và thường bắt đầu bằng tiếp đầu ngữ sp_ B Bác stored procedure nàB thuộc loại builtBin và chủ Bếu dùng trong việc quản lý cơ sở dữ liệu (administrationB và bảo mật (securitBBB Ví dụ bạn có thể Biểm tra tất cả các processes đang được sử dụng bởi user DomainNameBBd iminastrbtors ạ n có thể dùng B sp_who @loginame='DomainName\Administrators' Người ta có thể chia các BBstem stored procedures thành các loại sauB
Hình BB Bác loại của BBstem stored procedures Bó hàng trăm sBstem stored procedure trong BQL BerverB Bạn có thể xem chi tiết phân loại và nội dung của từng thủ tục trong BQL Berver BooBs BnlineB Bau đâB là một số thủ tục hệ thống thường sử dụngB BBstem stored procedure Bhức năng sp_Databases Danh sách những Database có thể (avaiableB trên Berver (Danh sách nàB sẽ là Bhác nhau tuỳ t uộc vào uBền của người sử dụngB sp_server_info Bhi tiết những thông tin về BerverB ví dụ như tập các đặc tínhB phiên bản… sp_stored_procedures Danh sách tất cả các thủ tục có thể trên môi trường hiện tại sp_tables Danh sách tất các bảng có thể trên môi trường iện tại sp_start_Bob Khởi động tất cả các automated tasB ngaB lập tức sp_stop Bob Ngừng lại tất cả các automated tasB đang chạB sp_password ThaB đổi password cho login account sp_configure ThaB đổi lựa ch n cấu hình chung của BQL BBRVBRB Khi người sử ụng Bhông lựa chọn thì hệ thống sẽ hiển th cấu hình mặc địnhB sp_help Hiển thị thông in về bất Bỳ đối tượng nào trong Database sp_helptext Hiển thị nội dung (textB của các đối tượng
BBBB Us erB defin ed Btor ed Bro cedur e aBBiBBBBaB Bú phápB Người sử dụng có thể sử dụng câu lệnh BRBBTB BRBBBDURB để tạo thủ tục trong BBDL hiện tạiB Database owner mặc định có quBền sử dụng câu lệnh BRBBTB BRBBBDURBB Bú phápB CREATE PROC[EDURE] procedure_name Ví dụB CREATE PROCEDURE London_Flights AS PRINT 'This code displays the details of flights to London' SELECT * FROM flight WHERE destination='Lon' Hhi chú Tên thủ tục phải tuân theo quB tắc đặt tên Tất cả các đối tượng của cơ sở dữ liệu có thể được tạo trong BBsB trừ những đối tượngB defaultsB rulesB triggersB proceduresB và viewsB Những đối tượng đã được tạo có thể được tham chiếu đến ngaB Bhi nó được tạoB Btored procedures có thể tham chiếu tới những bảng phụ (temporarB tablesBB Bó thể có BBBB biến trong stored procedureB Bhúng ta có thể tạo nhiều biến địa phương trong stored procedure nếu bộ nhớ cho phépB Kích thước tối đa cho stored procedure là BBB BBB bB Thực hiện UserBdefined Btored Brocedures Bú phápB EXEC[UTE] procedure_name Ví dụB Thực hiện UserBdefined Btored Brocedures có tên là London_Flights EXEC[UTE] London_Flights cB Bử dụng biến trong Btored Brocedures Biến có thể được sử dụng để nhập dữ liệu vào (BNBUTB hoặc xuất dữ liệu ra ngoài (BUTBUTB
Bú phápB CREATE PROCEDURE procedure_name @Parameter_name data_type AS Câu lệnh SQL Ví dụB CREATE PROCEDURE city_flights @v_city varchar(15) AS SELECT * FROM flight WHERE destination=@v_city Thực hiện thủ tục có biếnB Hình BB Thực hiện UserBdefined Btored Brocedures có biến Nếu có nhiều biến trong thủ tục thì Bhi thực hiện ta liệt Bê theo thứ tự các biến và phải cách nhau bằng dấu phẩBB dB Biên dịch lại B ReBc iompilgne Btord B rocedures Khi người sử dụng làm thaB đổi tới những index của bảngB Btored procedures phải được biên dịch lại (recompiledB để chấp nhận những thaB đổi đóB Bó B cách để biên dịch lại proceduresB Bách BB Bử dụng sp_recompile sBstem stored procedureB Bạn có thể sử dụng cách nàB để biên dịch lại thủ tục ở lần chạB Bế tiếp của nóB Bú phápB sp_recompile [@objectname=] ‘object’ Bách BB Bhỉ ra BBTH RBBBBBBLB trong câu lệnh BRBBTB BRBBBDURBB BQL Berver sẽ biên dịch lại thủ tục ở mỗi lần nó thực hiệnB Bú phápB
CREATE PROCEDURE procedure_name @Parameter_name data_type WITH RECOMPILE AS Bách BB Bhỉ ra BBTH RBBBBBBLB trong câu lệnh BXBBUTBB Biên dịch lại ngaB ở lần thực hiện nàBB Bú pháp: EXEC[UTE] procedure_name WITH RECOMPILE eB Bửa cấu trúc của Btored Brocedures Bâu lệnh ALTER PROCEDURE được sử dụng để sửa BBB Bú pháp tương tự BRBBTB BRBBBDURB chỉ thaB từ CREATE bằng ALTERB Việc sửa chữa vẫn lưu lại quBền của người sử dụng (user permissionsB fB Thông báo lỗi Trả về mã lỗi (BodeB hoặc câu lệnh RBBBBRRBR có thể được sử dụng để nhắc người sử dụng về lỗiB Bã lỗi trả về là số nguBênB Bâu lệnh RBBBBRRBR giải thích lỗi và chỉ ra mức độ lỗiB Return codes là số nguBênB giá trị mặc định là BB Hiá trị của Return codes phải được trả về vào một biến Vi dụB DECLARE @return_variable_name data_type EXECUTE @return_variable_name = procedure_name ALTER PROCEDURE Titles_Pub @v_pubid char(4) AS DECLARE @v_return int SELECT @v_return=COUNT(*) FROM titles WHERE pub_id = @v_pubid IF @v_return>0 SELECT * FROM titles WHERE pub_id = @v_pubid ELSE RETURN @v_return+1
Ngoài việc sử dụng câu lệnh RBBBBRRBRB ta còn có thể dùng BRBNT để hiển thị thông báo lỗi cho người sử dụngB TuB nhiênB những lời nhắc nàB chỉ là tạm thời và chỉ hiển thị cho người sử dụngB Bhúng ta cần sử dụng câu lệnh RBBBBRRBR để ghi lại những lỗi nàB và gán cho nó mức severitBB Bú phápB RAISERROR ({msg_id | msg_str}{,severity, state} [WITH option[...n]] Ví dụ WHILE @v_ctr > 0 BEGIN SELECT @v_ctr * @v_ctr SELECT @v_ctr = @v_ctr – 1 IF @v_ctr = 2 BEGIN RAISERROR('Counter has fallen below 3', 1, 2) BREAK END END Kết quảB 25 16 9 Nội dung thông báoB (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Msg 50000, Level 1, State 50000 Counter has fallen below 3 Bâu hỏiB bài tập BBKý hiệu nào theo sau đứng trước tên biến trong câu lệnh BXBBUTB? BB B BB B
BB ? DB@ BB Bâu lệnh nào sau đâB sẽ Bết thúc thực hiện thủ tục (Những câu lệnh đằng sau câu lệnh nàB sẽ Bhông được thực hiệnB? BB RBTURNB BB BXBTB BB HBLTB DB FBNBBHB BB Người dùng nào sau đâB có quBền mặc định để chạB Btored Brocedure? BB Data BwnerB BB Row BwnerB BB Table BwnerB DB Database BwnerB BB Hiệu quả của việc chỉ ra BBTH RBBBBBBLB trong Bhi định nghĩa Btored Brocedure (BBB là gì? BB BB được biên dịch lại ở ngaB lần tiếp theo nó thực hiện B BB BB được biên dịch lại ở tất cả các lần nó thực hiệnB BB BB được biên dịch lại Bhi BQL Berver Bhởi động BB BB được biên dịch lại Bhi có chỉ số được tạo trên các bảng mà nó tham chiếu tới BB Bhúng ta có thể sửa chữa được những thủ tục hệ thống (BBstem stored proceduresB BBĐúngB BBBaiB BB Thông báo được định nghĩa cùng RBBBBRRBR có thể chứa đựng tối đa bao nhiêu Bý tự? BB BB BB BBB BB BBB DB BBB Yêu cầu Biểm tra đánh giá Bết quả học tập Định nghĩa được thủ tục lưu trữ với các Biểu tham sốB
Bửa đổi được thủ tục đã định nghĩaB Xóa được các thủ tụcB
BBB B B TRB GGBR Hiới thiệu Để phát biểu đầB đủ các ràng buộc toàn vẹnB ngoài việc sử dụng BhecB với ràng buộc miền giá trịB liên thuộc tínhB BQL cung cấp một loại Biểm tra BhácB chặt chẽ hơn và đảm bảo phát biểu ràng buộc toàn vẹn với đầB đủ bảng tầm ảnh hưởng của nó B TriggerB Bục tiêu Định nghĩa được Trigger Bp dụng được mệnh đề BF UBDBTB trong Trigger để đảm bảo tính toàn vẹn dữ liệu cho các thao tác BNBBRTB UBDBTBB DBLBTB Thực hiện đựợc các biện pháp an toàn cho máB tính Nội dung B Hiới thiệu Trigger Trigger là một loại stored procedure đặc biệtB và nó sẽ được tự động thực thi Bhi có sự Biện tương ứng tác độngB Bó B loại triggersB DBL triggersB DDL triggersB và logon triggersB DDL triggers là các trigger sẽ được tự động thực thi Bhi xảB ra các sự Biện liên quan đến định nghĩa dữ liệu trên phạm vi server hoặc databaseB DDL là viết tắt của dat a defin it ion l an gu ageB Logon triggers là trigger được Bích hoạt tương ứng với sự Biện LBHBNB Bự Biện nàB được phát sinh Bhi user session được thiết lập đối với một instance của BQL BerverB DBL triggers là các triggers sẽ được tự động thực thi Bhi các có sự Biện thao tác dữ liệu xảB tương ứng liên quan thao tác dữ liệu trên table hoặc view mà nó gắn BếtB DBL là viết tắt của dat a m an ipul at ion l an gu ageB Bác sự Biện DBL bao gồm các lệnh BNBBRTB UBDBTBB hoặc DBLBTB thao tác trên các đối tượng table hoặc viewB DBL trigger giống như một stored procedureB nghĩa là trigger cũng sẽ chứa tập hợp các câu lệnh TBBQLB TuB nhiênB ta Bhông thể phát lời gọi trực tiếp đối với triggerB Bác câu lệnh bên trong trigger sẽ tự động thực thi Bhi có thao tác dữ liệu tương ứng xảB ra trên đối tượng Table mà nó
gắnB Bác thao tác dữ liệu bao gồm các lệnh BNBBRTB UBDBTBB hoặc DBLBTBB Bác lệnh BNBBRTB UBDBTBB và DBLBTB được hiểu như là các lệnh Bích hoạt trigger và nó cũng là lệnh Bhởi đầu cho một giao tác (transactionBB các lệnh trong trigger cũng là một phần của giao tác nàBB Bó hai loại DBL triggerB B FTBR tr iggers và BNBTBBD B F tr iggers B Tạo Bnsert trigger Bnsert trigger đảm bảo dữ liệu nhập vào bảng được đúng đắnB Xem xét ví dụB Tạo BNBBRT trigger để đảm bảo Bhôn g có vé nào được đặt vào một ngàB trong quá BhứB Bác bước thực hiệnB Bước BB Bở QBB chọn cơ sở dữ liệu FBB Bước BB Thực hiện đoạn lệnh sau trong QBB CREATE TRIGGER insert_trigg ON Reservation FOR INSERT AS IF((Select journey_date From Inserted) BB Tạo Delete Trigger DBLBTB trigger ngăn cản việc xoá đi những dữ liệu quan trọng trong bảngB Xem xét ví dụ sauB Tạo trigger để tránh xoá B bản ghi trong bảng Bassenger đồng thờiB Bác bước thực hiệnB Bước BB Trong QB thực hiện như sauB CREATE TRIGGER delete_trigg ON Passenger FOR Delete
AS IF((Select count(*) From deleted)>2) BEGIN PRINT 'Ban khong duoc xoa cung luc 2 ban ghi' ROLLBACK TRAN END Bước BB Thực hiện câu lệnh xoá nhiều hơn B bản ghi từ bảng BassengerB giả sử Kết quảB Hình BBBKết quả thực hiện Delete trigger B Tạo Update trigger BBBB T ạo T abl e L ev el Up dat e Tr igger Trigger UBDBTB được thực hiện bất cứ Bhi nào dữ liệu trong bảng được cập nhậtB Xem xét ví dụB Tạo UBDBTB trigger đảm bảo rằng cột No_of_seats trong bảng Reservation Bhông được cập nhật giá trị lớn hơn B và BourneBe _da t h Bgônh nỏ hơn ngàB hiện tạiB Bác bước tiến hành Bước BB Thực hiện như sau trong QBB CREATE TRIGGER CheckingUpdate ON Reservation FOR UPDATE AS IF((Select no_of_seats From inserted)>5) OR ((Select journey_date From Inserted)