Bài giảng Kỹ thuật sử dụng SQL Server chuẩn nhất

8/15/2017

Kỹ thuật sử dụng SQL server Bộ môn Tin học

Giới thiệu học phần

1. Tên học phần: Kỹ thuật sử dụng SQL

server (SQL in Use)

 2. Mã học phần: INFO2211 3. Số tín chỉ: 2 (20,10)

Tài liệu Tham khảo

 [1] Bộ môn Tin học, Tập bài giảng kỹ thuật sử dụng SQL server. Đại học Thương mại, 2016.

 [2] Hồ Thuần, Hồ Cẩm Hà, Các hệ cơ sở dữ liệu lý thuyết và thực hành, Nhà xuất bản giáo dục, 2009.

 [3]. Mike Chapple. MS SQL Server for Dummies. Wiley Publishing. 2008

1 8/15/2017

Tài liệu tham khảo khác

 [4] Elmasri, Navathe, Fundamentals of Database Systems, 6th

Edition, 2010

 [5] Brian Knightet al, Professional SQL Server 2008

Administration, Wrox Press, 2009.

 [6] Microsoft SQL Server 2008 Books Online,

http://www.microsoft.com/en-us/download/details.aspx?id=1054

Nội dung

 Chương 1: Tổng quan tổ chức và khai thác cơ sở dữ liệu

(CSDL) trong SQL server

 Chương 2: Ngôn ngữ SQL và phần mềm SQL server

Chương 3: Tổ chức, khai thác CSDL trong SQL server

 Chương 4: Ứng dụng MS SQL server khai thác một số

bài toán kinh doanh

Chương 1: Tổng quan tổ chức và khai thác cơ sở dữ liệu (CSDL) trong SQL server

 1.1. Tổ chức CSDL

 1.1.1. Vai trò của việc tổ chức CSDL

 1.1.2 Qui trình tổ chức CSDL trong SQL server

 1.2.Khai thác CSDL

 1.2.1. Tầm quan trọng của khai thác CSDL

 1.2.2. Qui trình khai thác CSDL trong SQL server

2 8/15/2017

1.1. Tổ chức CSDL

 1.1.1. Vai trò của việc tổ chức CSDL

 Lưu trữ có hệ thống

 Dễ dàng quản lý DL thông qua việc quản lý tập

trung

 Dễ dàng chia sẻ

 Bảo mật, và tránh sự dư thừa dữ liệu

Các KN cơ bản Dữ liệu (Data) và CSDL (Database)

 Dữ liệu (data) có nghĩa là thông tin và nó là thành phần quan trọng trong bất kỳ lĩnh vực, công

việc nào.

 Một CSDL (database) là một tập hợp các dữ liệu (data).

Lưu trữ Thông tin

Users

Database

Cho phép truy xuất Thông tin

 CSDL là một tập hợp dữ liệu được tổ chức sao cho nội dung của nó có thể dễ dàng truy cập,

quản lý và cập nhật.

Quản trị dữ liệu

trên các thông tin đó.

 Hệ thống quản lý dựa trên tập tin (File-based systems)

 Hệ thống CSDL (Database systems)

 Quản trị dữ liệu chỉ việc quản lý lượng lớn thông tin bao gồm cả việc lưu trữ thông tin và cơ chế thao tác  Hai phương pháp quản trị dữ liệu khác nhau:

3 8/15/2017

Hệ thống quản lý tập tin 1-3

 Lưu trữ dữ liệu trong các tập tin riêng biệt.  Một nhóm các tập tin được lưu trữ trên máy tính và được truy cập bằng các thao tác máy tính.

Hệ thống quản lý tập tin 2-3

 Ví dụ:

Sudent Database

Data entry and reports File handling routines

 CSDL ManagementStudent sẽ chứa những bảng sau:

Hệ thống quản lý tập tin 3-3

 Student (StudNo, StudName, Age, Address, …)  Class (ClassNo, ClassName, FacultName)  ….

 Dư thừa và không nhất quán dữ liệu

 Truy vấn đột xuất

 Cô lập dữ liệu

 Truy cập đồng thời bất thường

 Vấn đề bảo mật

 Vấn đề toàn vẹn

 Các nhược điểm của hệ thống:

4 8/15/2017

Hệ thống CSDL

 CSDL sử dụng để lưu trữ dữ liệu có hệ thống và có tổ chức. Giúp người dùng quản

lý dữ liệu nhanh chóng và dễ dàng

 Ưu điểm của hệ thống CSDL này là:

 Giảm dư thừa dữ liệu

 Tránh vấn đề không nhất quán dữ liệu

 Dữ liệu được lưu trữ có thể được chia sẻ

 Các chuẩn có thể được thiết lập và duy trì

 Tính toàn vẹn dữ liệu được duy trì

 Bảo mật dữ liệu có thể được thực thi

Hệ quản trị CSDL (Database Management System - DBMS) 2-2

Data entry and reports

DBMS

Database

Data entry and reports

Lợi ích của hệ quản trị CSDL

 Lưu trữ dữ liệu

 Định nghĩa dữ liệu

 Thao tác dữ liệu

 Bảo mật và toàn vẹn dữ liệu

 Truy cập đồng thời và phục hồi dữ liệu

 Hiệu năng cao

 Điều khiển đa truy cập

 Ngôn ngữ truy xuất CSDL và giao diện lập trình ứng dụng API – (Application

Programming Interfaces)

5 8/15/2017

1.1.2 Qui trình tổ chức CSDL trong SQL server

 B1: Thu thập dữ liệu (sơ cấp, thứ cấp)

 B2: Phân loại DL: DL cần lưu trữ, DL loại bỏ

 B3: Xác định cấu trúc của CSDL (các table; field;

record; và mối quan hệ giữa chúng)

 B4: Chuẩn hóa CSDL

 B5: Nhập Dữ liệu vào máy tính thông qua các phần

mềm tương ứng

1.2.Khai thác CSDL  KN: Là các hoạt động tác động trực tiếp vào CSDL như: Truy vấn, cập nhật, xóa, lưu,… phục vụ mục đích của người dùng

 1.2.1. Ý nghĩa của khai thác CSDL

 Giúp cho việc tìm kiếm dữ liệu nhanh chóng, chính xác và thuận tiện

hơn

 Thông tin được cập nhật nhanh chóng và bảo mật tối đa

 Cơ chế chia sẻ thông minh, và báo cáo thuận tiện giúp nâng cao hiệu quả

quản trị

1.2.2. Qui trình khai thác CSDL trong SQL server

 Chạy ứng dụng SQL Server

 Kết nối CSDL (nếu có) hoặc tạo mới

 Thực hiện các thao tác với Tables, Diagram (Tạo mối

liên kết),.. Nếu cần

 Sử dụng truy vấn thông qua New Query. Gõ lệnh T-SQL

và Excute

 Có thể sử dụng màn hình Query Design để kéo thả

6 8/15/2017

Chương 2: Ngôn ngữ SQL và phần mềm SQL server  2.1. Các khái niệm cơ bản  2.1.1. Khái niệm ngôn ngữ SQL  SQL – Structure Query Language, ngôn ngữ truy vấn mang

tính cấu trúc, là một loại ngôn ngữ máy tính phổ biến để tạo, sửa, và lấy dữ liệu từ một hệ quản trị cơ sở dữ liệu quan hệ.

 Lịch sử: năm 1970, một nhóm các nhà phát triển tại trung tâm nghiên cứu của IBM tại San Jose phát triển hệ thống csdl "Hệ thống R" dựa trên mô hình của Codd. Structured English Query Language, viết tắt là "SEQUEL để truy tìm DL trong R

 Năm 1978, có SQL thương mại, và Relational Software

(OracleV2).

Lịch sử

2.1.2. Một số phần mềm SQL

 DB2

 Microsoft Access

 Microsoft SQL Server

 Mimer SQL

 Oracle

 PM miễn phí:

 MySQL

 PostgreSQL

 SQLite

7 8/15/2017

2.2. Tổng quan về SQL server  2.2.1. Lịch sử phát triển

 SQL Server được phát triển từ ngôn ngữ SEQUEL2 bởi IBM vào những năm 70 cho hệ thống QTCSDL lớn. MicroSoft phát triển SQL server vào 1987.

 RTM: Released to Manufactoring;

 SP: service pack

 Số serial number

2.2.2. Các phiên bản của SQL server

 Enterprise

 Standard

 Personal

 Developer

 Express: free

 Trial: Có các tính năng của Enterprise Edition,

download free, nhưng giới hạn thời gian sử dụng

2.2.3. Cài đặt phần mềm SQL server

 Microsoft SQL Server 2005 Express Edition

(SQLEXPR.EXE) là phiên bản miễn phí, dễ sử dụng và “nhẹ” của Microsoft SQL Server 2005

 SQL Server Management Studio Express

(SQLServer2005_SSMSEE.msi)

8 8/15/2017

Cài đặt (chú ý)

 Để đăng nhập vào SQL Server, người dùng này phải có một bộ username và password do SQL Server quản lý.

 Kiểu kiểm tra người dùng này thường được sử dụng khi ứng dụng khai thác dữ liệu và SQL Server không được cài trên cùng một máy tính.

2.3. Các thành phần của SQL server

 2.2.1. Ngôn ngữ định nghĩa CSDL - Data Definition

Language – DDL

 Một sơ đồ CSDL đặc tả bởi một tập các định nghĩa được biểu diễn bởi một ngôn ngữ đặc biệt được gọi là ngôn ngữ định nghĩa dữ liệu.

 Cấu trúc và các phương pháp truy nhập được sử dụng bởi hệ CSDL được đặc tả bởi một tập các định nghĩa trong một kiểu đặc biệt của DDL là ngôn ngữ định nghĩa và lưu trữ dữ liệu.

9 8/15/2017

2.1.2. Ngôn ngữ khai thác CSDL- (Data Manipulation Language - DML

 Các yêu cầu về thao tác dữ liệu bao gồm:

 Tìm kiếm thông tin được lưu trữ trong CSDL. Thêm thông tin mới vào CSDL.  Xoá thông tin từ CSDL. Thay đổi thông tin được lưu trữ trong CSDL

 Một ngôn ngữ thao tác dữ liệu (DML) là một ngôn ngữ cho phép người sử dụng truy nhập hay thao tác dữ liệu được tổ chức bởi mô hình dữ liệu thích hợp

 Có hai kiểu ngôn ngữ thao tác dữ liệu cơ bản:

 Các DML thủ tục đòi hỏi người sử dụng phải đặc tả dữ liệu nào cần tìm kiếm và tìm kiếm những dữ liệu này như thế nào.

 Các DML phi thủ tục đòi hỏi người sử dụng đặc tả dữ liệu nào cần tìm kiếm mà không phải đặc tả tìm kiếm những dữ liệu này như thế nào.

Các thành phần khác

 SQL Server Database

 Analysis Services

 Reporting Services

 Notification Services.

 Integration Services.

10 8/15/2017

Sử dụng SQL Server Configuration Manager  Là công cụ để quản lý các dịch vụ kết hợp với SQL

Server, để cấu hình các giao thức mạng được sử dụng bởi SQL Server, và để quản lý cấu hình kết nối mạng từ các máy tính trạm SQL Server.

 Vào start/Programs/Microsoft SQL Server

2005/Configuration Tools/SQL Server Configuration Manager

 Chọn SQL Server 2005 Services, right click lên thể hiện

của SQL Server mà ta muốn khởi chạy hoặc dừng.

 + Start: Khởi chạy thể hiện của SQL Server

 + Stop: Dừng hoạt động của thể hiện SQL Server.

 + Pause: Tạm dừng hoạt động của thể hiện SQL Server

 + Restart: Khởi động lại thể hiện của SQL Server

Chương 3: Tổ chức, khai thác CSDL trong SQL server  3.1. Phân tích bài toán  3.2 Tạo lập CSDL  3.2.1 Cơ sở dữ liệu, Bảng và kiểu dữ liệu trong SQLServer 3.2.2 Sửa

đổi cấu trúc CSDL  3.2.3 Nhập dữ liệu  3.3. Khai thác CSDL  3.3.1 Cập nhật CSDL  3.3.2 Truy vấn CSDL và tối ưu hóa truy vấn  3.3.3. Sao lưu và phục hồi CSDL  3.4. Kỹ thuật Trigger và Store Procedure  3.4.1. Kỹ thuật trigger  3.4.2. Kỹ thuật Store Procedure

11 8/15/2017

Chương 3: Tổ chức, khai thác CSDL

 3.1 Đặt bài toán

 Viết mô tả bài toán cần giải quyết

 XĐ DL đầu vào; đầu ra

 3.2 Phân tích bài toán

 Phân tích dữ liệu đầu vào

 Tổ chức CSDL

 Xây dựng quy trình để khai thác CSDL

 Kết xuất thông tin kết quả thỏa mãn yêu cầu bài toán

(Reports; query,…)

3.3 Tạo lập CSDL

 Tạo một CSDL mới:

 Đặt tên Database trong Textbox Database Name, click

OK.

Tạo bảng mới

12 8/15/2017

 Khai báo tên cột, khai báo ít nhất 1 khóa chính.

 Nút Save

 Đặt tên cho TableOK

 Các kiểu dữ liệu trong SQL Server

Kiểu dữ liệu

Miền giá trị dữ liệu lưu trữ

Kích thước

> Các kiểu dữ liệu dạng số nguyên

Int

4 bytes

từ-2,147,483,648đến +2,147,483,647

SmallInt

2 bytes

từ -32768 đến +32767

TinyInt

1 byte

từ 0 đến 255

Bit

1 byte

0, 1 hoặc Null

> Các kiểu dữ liệu dạng số thập phân

Decimal, Numeric 17bytes

từ -10^38 đến +10^38

> Các kiểu dữ liệu dạng số thực

Float

8 bytes

từ -1.79E+308 đến +1.79E+308

Real

4 bytes

từ -3.40E+38 đến +3.40E+38

 Các kiểu dữ liệu trong SQL Server

> Các kiểu dữ liệu dạng chuỗi có độ dài cố định

Char

N bytes

từ 1 đến 8000 ký tự, mỗi ký tự là một byte

> Các kiểu dữ liệu dạng chuỗi có độ dài biến đổi

VarChar

N bytes

từ 1 đến 8000 ký tự, mỗi ký tự là 1 byte

Text

N bytes

từ 1 đến 2,147,483,647 ký tự, mỗi ký tự là 1 byte

> Các kiểu dữ liệu dạng chuỗi dùng font chữ Unicode

NChar

2*N bytes

từ 1 đến 4000 ký tự, mỗi ký tự là 2 bytes

NVarChar

2*N bytes

từ 1 đến 4000 ký tự, mỗi ký tự là 2 bytes

NText

2*N bytes

từ 1 đến 1,073,741,823 ký tự, mỗi ký tự là 2 bytes

13 8/15/2017

 Các kiểu dữ liệu trong SQL Server

> Các kiểu dữ liệu dạng tiền tệ

-922,337,203,685,477.5808

đến

Money

8 bytes

từ +922,337,203,685,477.5807

SmallMoney

4 bytes

từ -214,748.3648 đến + 214,748.3647

> Các kiểu dữ liệu dạng ngày và giờ

DateTime

8 bytes

từ01/01/1753đến31/12/9999

SmallDateTime

4 bytes

từ01/01/1900đến06/06/2079

> Các kiểu dữ liệu dạng chuỗi nhị phân (Binary String)

Binary

N bytes

từ 1 đến 8000 bytes

VarBinary

N bytes

từ 1 đến 8000 bytes

Image

N bytes

từ 1 đến 2,147,483,647 bytes

Ngôn ngữ định nghĩa dữ liệu ( Data Definition Language – DDL)

CREATE TABLE

 cú pháp

 CREATE TABLE tên_bảng

 (

 tên_cột thuộc_tính_cột các_ràng_buộc

 [,...

 ,tên_cột_n thuộc_tính_cột_n các_ràng_buộc_cột_n]

 [,các_ràng_buộc_trên_bảng]

 )

 Tên_bảng: tuân theo quy tắc định danh, không vượt quá 128 k. tự

 Tên_cột: các cột trong bảng, mỗi bảng có ít nhất một cột.

 Thuộc_tính_cột: bao gồm kiểu dữ liệu của cột, giá trị mặc định của cột, cột có được thiết lập thuộc tính

identity, cột có chấp nhận giá trị NULL hay không. Trong đó kiểu dữ liệu là thuộc tính bắt buộc.

 Các_ràng_buộc: gồm các ràng buộc về khuôn dạng dữ liệu ( ràng buộc CHECK) hay các ràng buộc về

bào toàn dữ liệu (PRIMARY KEY, FOREIGN KEY, UNIQUE)

 Create object  Alter object  Drop object  Trong đó object có thể là: table, view, storedprocedure, function, trigger…

14 8/15/2017

Ràng buộc CHECK

khi

dụng

Check constrain ở table

 Ràng buộc CHECK được sử dụng nhằm chỉ định điều kiện hợp lệ đối với dữ liệu. Mỗi  có sự thay đổi dữ liệu trên bảng (INSERT, UPDATE), những ràng buộc này sẽ được sử  nhằm kiểm tra xem dữ liệu mới có hợp lệ hay không.  Ràng buộc CHECK được khai báo theo cú pháp như sau:  [CONSTRAINT tên_ràng_buộc] CHECK (điều_kiện)

15 8/15/2017

Ràng buộc PRIMARY KEY

 Ràng buộc PRIMARY KEY được sử dụng để định nghĩa khoá chính của bảng. Khoá chính của một bảng là một hoặc một tập nhiều cột mà giá trị của chúng là duy nhất trong bảng. Không chấp nhận giá trị NULL.

 cú pháp:

 [CONSTRAINT tên_ràng_buộc] PRIMARY KEY

[(danh_sách_cột)]

 Nếu khoá chính của bảng chỉ bao gồm đúng một cột và ràng buộc PRIMARY KEY được chỉ định ở mức cột, không cần thiết phải chỉ định danh sách cột sau từ khoá PRIMARY KEY.

Ràng buộc FOREIGN KEY

 FOREIGN KEY là một cột hay một sự kết hợp của nhiều cột được sử dụng để áp đặt mối liên kết dữ liệu giữa hai table. FOREIGN KEY của một bảng sẽ giữ giá trị của PRIMARY KEY của một bảng khác và chúng ta có thể tạo ra nhiều FOREIGN KEY trong một table.

 FOREIGN KEY có thể tham chiếu vào PRIMARY KEY hay cột có ràng buộc

duy nhất.

 FOREIGN KEY có thể chứa giá trị NULL

 Ví dụ: nếu ta xóa dữ liệu trong bảng cha thì dữ liệu trong bảng con trở nên

(orphan) vì không thể tham chiếu ngược về bảng cha.

 Nếu muốn xóa dữ liệu trong bảng cha thì trước hết bạn phải xóa hay vô hiệu

hóa ràng buộc FOREIGN KEY trong bảng con trước.

 Ràng buộc FOREIGN KEY được định nghĩa theo cú pháp dưới đây:

 [CONSTRAINT tên_ràng_buộc] FOREIGN KEY [(danh_sách_cột)]

 REFERENCES tên_bảng_tham_chiếu(danh_sách_cột_tham_chiếu)

 [ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT]

 [ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT]

16 8/15/2017

Tạo mối quan hệ giữa các bảng

 Chọn database diagram\add new diagram

 Chọn các bảng đưa vào màn hình quan hệ

 Kéo và thả.

 Chú ý: để tạo mối quan hệ 1-1. tạo khóa chính

của cả 2 bảng; kéo thả và chọn foreign key là no; và replication là no

Sửa bảng (ALTER TABLE)

 ALTER TABLE ADD <Định nghĩa cột> | ALTER COLUMN [] | DROP COLUMN | ADD CONSTRAINT <Định nghĩa ràng buộc> | DROP CONSTRAINT

 Ví dụ: ALTER TABLE HangHoa ADD COLUMN SoLuong

INT;

 ALTER TABLE HangHoa DROP COLUMN SoLuong;

17 8/15/2017

Ngôn ngữ thao tác dữ liệu (Data manipulation language – DML)

 Đây là các lệnh phổ biến dùng để xử l. dữ liệu.

Bao gồm:

 Select

 Insert

 Update

 Delete

Câu lệnh SELECT- truy vấn

 SELECT [ALL | DISTINCT][TOP n] danh_sách_chọn

 [INTO tên_bảng_mới]

 FROM danh_sách_bảng/khung_nhìn  [WHERE điều_kiện]

 [GROUP BY danh_sách_cột]

 [HAVING điều_kiện]

 [ORDER BY cột_sắp_xếp]

 [COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột]]

 select * from monHoc

 select * from monHoc where maMH like 'C%'

 select * from monHoc where maMH like '%F%'

 select maSV, tenSV, convert(char(12),ngaysinh,103) as ngaysinh

from sinhVien where diachi in ('HP','HN')

 select maSV, tenSV,cast(ngaysinh as char(12)) from sinhVien

where ngaySinh between '1986-1-1' and '1986-12-31'

18 8/15/2017

Tính toán trên bảng

 VD: SUM, AVG, MAX, MIN, COUNT()

 select sum(luong) as [tong luong], avg(luong) as [tbinh

luong] from luong

VD có CSDL

Bài tập

Distinct)

Desc)

 Tạo các bảng nói trên và nhập DL cho các bảng  1. Hiển thị ds khách hàng mua hàng với SL >=50 và giá tiền <=100.000  2. Hiển thị danh sách khách hàng nữ (căn cứ vào gender)  3. Hiển thị ds khách hàng (chú ý nếu mua nhiều lần thì cũng chỉ hiên thị 1 lần –  4. Hiên thị ds mua hàng được sắp xếp tăng dần theo giá và số lượng (order by : ASC,

19 8/15/2017

Truy vấn dữ liệu với UNION

 Phép hợp được sử dụng trong trường hợp ta cần gộp kết quả của hai hay nhiều truy vấn thành một tập kết quả duy nhất. SQL cung cấp toán tử UNION để thực hiện phép hợp.

 UNION [ALL]

 [UNION [ALL]

 ] ... [UNION [ALL]

]

 [ORDER BY ]

[COMPUTE [BY ]]

 SELECT MaSanPham AS Ma, TenSanPham AS Ten

FROM SanPham UNION SELECT MaNhaCungCap, TenNhaCungCap FROM TenNhaCungCap UNION SELECT MaLoaiSanPham, TenLoaiSanPham FROM LoaiSanPham ORDER BY Ma COMPUTE Count(TenSanPham) BY Ma

20 8/15/2017

Using JOINS

 Thứ tự từ trái sang phải: Inner Join, Left Outer Join, Right Outer Join, Full Outer Join

Phép nối trong INNER JOIN

 Phép nối thực hiện nối nhiều bảng trong đó những dòng có giá trị trong các cột được chỉ định thoả mãn điều kiện kết nối mới được hiển thị trong kết quả truy vấn, phép nối này loại bỏ thông tin chứa trong những dòng không thoả mãn điều kiện nối.

 Cú pháp nối trong: INNER JOIN ON <Điều

kiện nối>

 Ví dụ: SELECT TenSanPham, TenNhaCungCap FROM SanPham

AS SP INNER JOIN NhaCungCap AS NCC ON NCC.MaNhaCungCap=SanPham.MaNhaCungCap

Phép nối ngoài trái LEFT OUTER JOIN

 Phép nối này thực hiện nối nhiều bảng trong đó chứa tất cả các dòng dữ liệu của bảng nằm bên trái trong phép nối cho dù những dòng này không thoả mãn điều kiện của phép nối.

 Cú pháp nối ngoài trái: LEFT OUTER

JOIN ON <Điều kiện nối>

21 8/15/2017

 SELECT TenSanPham, TenNhaCungCap FROM NhaCungCap AS NCC LEFT OUTER JOIN SanPham AS SP ON NCC.MaNhaCungCap=SanPham.MaNhaCungC ap

Phép nối ngoài phải RIGHT OUTER JOIN

 Phép nối này thực hiện nối nhiều bảng trong đó chứa tất cả các dòng dữ liệu của bảng nằm bên phải trong phép nối cho dù những dòng này không thoả mãn điều kiện của phép nối.  Cú pháp nối ngoài phải: RIGHT OUTER JOIN ON <Điều

kiện nối>

 SELECT TenSanPham, TenNhaCungCap FROM

SanPham AS SP RIGHT OUTER JOIN NhaCungCap AS NCC ON NCC.MaNhaCungCap=SanPham.MaNhaCungC ap

22 8/15/2017

Phép nối ngoài đầy đủ FULL OUTER JOIN

 Phép nối này thực hiện nối nhiều bảng trong đó chứa tất cả các dòng dữ liệu của bảng nằm trong phép nối cho dù những dòng này không thoả mãn điều kiện của phép nối.

 Cú pháp nối ngoài đầy đủ: FULL OUTER

JOIN ON <Điều kiện nối>

 SELECT TenSanPham, TenNhaCungCap FROM

SanPham AS SP FULL OUTER JOIN NhaCungCap AS NCC ON NCC.MaNhaCungCap=SanPham.MaNhaCungC ap

OUTER JOIN

SELECT * FROM nhanvien LEFT OUTER JOIN donvi ON nhanvien.madv=donvi.madv

SELECT * FROM nhanvien RIGHT OUTER JOIN donvi ON nhanvien.madv=donvi.madv

23 8/15/2017

OUTER JOIN

SELECT * FROM nhanvien FULL OUTER JOIN donvi ON nhanvien.madv=donvi.madv

Truy vấn con

Truy vấn con : môt câu SELECT năm trong một câu SELECT khác

Truy vấn cha

Select From Bảng

Toán tử

WHERE =

Select From WHERE =

Truy vấn con

Truy vấn con lồng nhau

 Truy vấn con lồng nhau là câu lênh truy vấn con nằm trong một cau lênh truy vân

khác Ví dụ

Lồng nhau Tới 2 tầng

24 8/15/2017

 Sử dụng ALL

 SELECT SP.TenSanPham, SP.TenNhaCungCap FROM SanPham AS SP WHERE SP.MaNhaCungCap>ALL (SELECT MaNhaCungCap FROM NhaCungCap WHERE Fax IS NULL)

 Sử dụng ANY

 SELECT SP.TenSanPham, SP.TenNhaCungCap FROM SanPham AS SP WHERE SP.MaNhaCungCap>ANY(SELECT MaNhaCungCap FROM NhaCungCap WHERE Fax IS NULL)

 Sử dụng IN  SELECT SP.TenSanPham, SP.TenNhaCungCap FROM SanPham AS SP WHERE

SP.MaNhaCungCap IN (SELECT MaNhaCungCap FROM NhaCungCap WHERE Fax IS NULL)

25 8/15/2017

 Sử dụng EXIST  SELECT TenNhaCungCap FROM NhaCungCap AS NCC WHERE EXIST

(SELECT MaSanPham FROM SanPham AS SP WHERE SP.MaNhaCungCap = NCC.MaNhaCungCap)

THIẾT KẾ TRUY VẤN VỚI DEGISN QUERY

26 8/15/2017

Tạo và quản lý khung nhìn

 Một khung nhìn (view) có thể được xem như là một bảng “ảo” trong cơ sở dữ liệu có nội dung được định nghĩa thông qua một truy vấn (câu lệnh SELECT).

 giống như một bảng với một tên khung nhìn và là một tập bao

gồm các dòng và các cột.

 Điểm khác biệt giữa khung nhìn và bảng là khung nhìn không được xem là một cấu trúc lưu trữ dữ liệu tồn tại trong cơ sở dữ liệu.

 Thực chất dữ liệu quan sát được trong khung nhìn được lấy từ các

bảng thông qua câu lệnh truy vấn dữ liệu.

Tạo View

 Cú pháp:  CREATE VIEW [()] AS

SELECT>

Ví dụ:

 CREATE VIEW ViewCungCapHangHoa AS SELECT MaHangHoa,TenHangHoa,TenNhaCungCap FROM HangHoa INNER JOIN NhaCungCap ON HangHoa.MaNhaCungCap=NhaCungCap.MaNhaCung Cap

27 8/15/2017

 Sửa đổi khung nhìn:  ALTER VIEW [()] AS  Bỏ khung nhìn:  DROP VIEW

28 8/15/2017

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

 Thủ tục lưu trữ (Stored procedure): Thủ tục lưu trữ là một đối tượng trong CSDL, bao gồm nhiều câu lệnh T-SQL được tập hợp lại với nhau thành một nhóm, và tất cả các lệnh này sẽ được thực thi khi thủ tục lưu trữ được thực thi.

 Các TP: Các cấu trúc điều khiển (IF, WHILE, FOR) có thể được sử dụng

trong thủ tục;

 Bên trong thủ tục lưu trữ có thể sử dụng các biến như trong ngôn ngữ lập trình

nhằm lưu giữ các giá trị tính toán được,

 Một thủ tục có thể nhận các tham số truyền vào cũng như có thể trả về các giá

trị thông qua các tham số (như trong các ngôn ngữ lập trình).

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

GọI thực hiện ttnt Exec Ten_Thu_Tuc [ @ten_Tham_so_vao= gia_tri[,..] @ten_Tham_so_ra=@ten_Bien output] LỆNH Return trong TTNT: Trong thủ tục nt ta có thể dùng lệnh Return để trả về kết quả hoặc để thoát khỏI thủ tục Cú pháp :

Return giá trị :thoát khỏI thủ tục và trả về kết quả Hoặc Return : Thoát khỏI thủ tục GọI thủ tục có giá trị trả về Exec @ten_bien=Ten_thu_Tuc [ @ten_Tham_so_vao= gia_tri[,..] @ten_Tham_so_ra=@ten_Bien output]

 CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_số)]  AS  SQL_Statements  Trong đó:  SQL_Statements. Các lệnh này có thể nằm trong cặp  BEGIN…END hoặc không.

29 8/15/2017

Gía trị trả về

 CREATE PROC sp_list @bten char(20)  AS  SELECT hoten, ngaysinh, diachi  FROM nhanvien  WHERE hoten= @bten  Chú ý: khi gọi truyền tham số cho thủ tục dưới dạng: @tham_số = giá trị

 CREATE PROC sp_exam @bten char(20)  AS  IF EXISTS (SELECT * FROM nhanvien WHERE hoten = @bten)  RETURN 1  ELSE  RETURN 2

 Khi thực hiện:  DECLARE @ketqua real  EXEC Chia 100, 2, @ketqua OUT  SELECT @ketqua  Sẽ cho kq:  ----------------------  50.0

30 8/15/2017

Tạo Hàm

 DECLARE @ketqua real  EXEC Chia 100, 2, @ketqua  SELECT @ketqua  Sẽ cho kq:  ----------------------  (null)

 create function f_thu(@ngay datetime)

 returns nvarchar(10)

 as

 begin

 declare @st nvarchar(10)

 select @st=case datepart(dw,@ngay)

 when 1 then N'chủ nhật'

 when 2 then N'thứ hai'

 when 3 then N'thứ ba'

 when 4 then N'thứ tư'

 when 5 then N'thứ năm'

 when 6 then N'thứ sáu'

 else N'thứ bảy'

 end

 return (@st) /* trị trả về của hàm */

 end

 Cú pháp:  CREATE FUNCTION tên_hàm  ([danh_sách_tham_số]) RETURNS (kiểu_trả_về_của_hàm)  AS BEGIN  các_câu_lệnh_của_hàm  END

31 8/15/2017

Thực hiện

Kết quả

Hàm do NSD định nghĩa

 Cú pháp:  select *, dbo.f_thu(ngaysinh)  from sinhVien

 CREATE FUNCTION tên_hàm ([danh_sách_tham_số])  RETURNS TABLE  AS  RETURN (câu_lệnh_select)

32 8/15/2017

 create function f_SelectCustomer

 (@customerid int)

 returns table

 as

 return (select * from customers

 where customerid > @customerid)

VD: chọn SV

 create function f_chonsv

 (@masv char(10))

 returns table

 as

 return (select * from sinhVien

 where masv=@masv)

Chạy Excute

33 8/15/2017

Nhấn F5 tại Function

Thực hiện hàm

 Mở Query mới:

 select * from f_chonsv('11')

Kết quả

34 8/15/2017

Tạo các Trigger thao tác dữ liệu (DML Triggers)

 KN: Một trigger là một đối tượng gắn liền với một đối tượng

CSDL và được tự động kích hoạt khi xảy ra những giao tác làm thay đổi đối tượng CSDL. Định nghĩa một trigger bao gồm các yếu tố sau:

 • Trigger sẽ được áp dụng đối với đối tượng CSDL nào?

 • Trigger được kích hoạt khi có sự kiện gì?

 • Trigger sẽ làm gì khi được kích hoạt?

Cú pháp

Sử dụng mệnh đề IF UPDATE

 CREATE TRIGGER trigger_name  ON table_name  FOR {INSERT, UPDATE, DELETE}  AS sql_statements

 CREATE TRIGGER trigger_name  ON table_name  FOR {INSERT, UPDATE}  AS  IF UPDATE (column_name)  [{AND OR}UPDATE (column_name)...] sql_statements

35 8/15/2017

 Sau khi cập nhật điểm ở bảng KQ, trigger sẽ tự động hiển thị kết quả của bảng kết quả

 Tạo Trigger:

 CREATE TRIGGER tg_suadiem

 ON ketqua

 AFTER UPDATE

 AS

 BEGIN

 select * from ketqua

 END

 GO

 VD: sau khi cập nhật cho 1 bảng NhanVien cần có lệnh hiển thị nội dung bảng:  CREATE TRIGGER tgr_check  ON nhanvien  FOR INSERT, UPDATE  AS  print '*** Ket qua sau khi cap nhat ***'  SELECT * FROM nhanvien

36 8/15/2017

Sau đó mở query

Bài tập

 Viết các lệnh T-SQL để tạo cơ sở dữ liệu QLYBAIXE gồm các

bảng dữ liệu sau đây: ChuXe(MaCX, TenCX, DiaChi) LoaiXe(MaLoai, TenLoai) Xe(SoXe, MaCX, MaLoai) NhatKyBai(SoXe, NgayVao, NgayRa)

 Trong đó bảng NhatKyBai lưu giữ thông tin thời gian lưu bãi của xe. Một xe có thể vào bãi nhiều lần. Xe nào chưa ra khỏi bãi thì có có trường NgayRa = NULL.

 Gõ: update ketqua set diem=diem +1 where masv=10

37 8/15/2017

bảng Xe trong cơ sở dữ liệu QLYBAIXE.

nào đó đã có trên bảng.

phải thuộc về một loại xe có MaLoai nằm trong bảng LoaiXe

 Viết một thủ tục thường trú có tên sp_ThemXe làm nhiệm vụ thêm một bản ghi mới vào  Biết xe mới thêm vào phải thỏa mãn các điều kiện: Không có SoXe trùng với một xe  Xe này phải thuộc sở hữu của một chủ xe có MaCX nằm trong bảng ChuXe Xe này