Giáo trình Quản trị cơ sở dữ liệu SQL

select lower('Nha Trang'), upper('Nha Trang')
7.2.8 Hàm LTRIM/RTRIM
Hàm LTRIM cắt các khoảng trắng bên trái của chuỗi, hàm RTRIM cắt khoảng trắng bên
phải chuỗi.
Ví dụ:
declare @llen int
declare @rlen int
declare @len int
select @llen = len(ltrim(' Nha Trang')),
@rlen = len(rtrim('Nha Trang ')),
@len = len('Nha Trang')
select @llen, @rlen, @len
7.3 Các hàm làm việc với kiểu dữ liệu Ngày tháng/ Thời gian
7.3.1 Hàm GETDATE
Hàm GETDATE trả về ngày giờ lúc thực hiện câu truy vấn.
Ví dụ:
select getdate()
7.3.2 Hàm DAY/ MONTH/ YEAR
Hàm DAY trả về ngày của một một giá trị thuộc kiểu datetime.
Hàm MONTH trả về tháng của một giá trị thuộc kiểu datetime
98
Hàm YEAR trả về năm của một giá trị thuộc kiểu datetime.
Ví dụ:
select day(orderdate) as DAYOFORDER,
month(orderdate) as MONTHOFORDER,
year(orderdate) as YEAROFORDER
from orders o inner join customers c on c.customerid = o.customerid
where c.customerid = 3
7.3.3 Hàm DATEPART
Trong quá trình làm việc với các CSDL, đôi lúc ta muốn biết xem một ngày nào đó thuộc
quý mấy trong năm, hay thuộc tuần thứ mấy trong tháng. Hàm DATEPART giúp giải quyết các
yêu cầu trên một cách dễ dàng.
Cấu trúc hàm DATEPART như sau:
DATEPART (yêu_cầu_trích_xuất, giá_trị_trích_xuất)
giá_trị_trích_xuất là một giá trị thuộc kiểu datetime.
yêu_cầu_trích_xuất: ngày, tháng, năm, quý,.
Khi có một yêu cầu trích xuất nào đó, chúng ta sẽ có các chữ viết tắt tương ứng với các
yêu cầu đó. Bảng dưới đây mô tả các yêu chữ viết tắt và các yêu cầu trích xuất tương ứng.
Ý nghĩa Chữ viết tắt
Năm yy, yyyy
Quý qq,q
Tháng mm,m
Số ngày đã qua trong năm dy,y
Ngày dd,d
Tuần wk,ww
Số ngày đã qua trong tuần dw
Giờ hh
Phút mi,n
Giây ss,s
Ví dụ:
select datepart(yyyy, orderdate)as YEAROFORDERDATE,
datepart(qq, orderdate)as QUARTEROFORDERDATE,
99
datepart(m, orderdate) as MONTHOFORDERDATE,
datepart(wk, orderdate) as WEEKOFORDERDATE,
datepart(d, orderdate) as DATEOFORDERDATE,
datepart(dy, Orderdate), datepart(dw, orderdate)
from orders
7.3.4 Hàm DATENAME
Tương tự hàm DATEPART nhưng hàm DATENAME trả về một chuỗi ký tự
Ví dụ:
select datename(yyyy, orderdate)as YEAROFORDERDATE,
datename(qq, orderdate)as QUARTEROFORDERDATE,
datename(m, orderdate) as MONTHOFORDERDATE,
datename(wk, orderdate) as WEEKOFORDERDATE,
datename(d, orderdate) as DATEOFORDERDATE,
datename(dy, Orderdate), datename(dw, orderdate)
from orders
7.4 Hàm CAST và CONVERTER
Chuyển đổi một giá trị thuộc kiểu dữ liệu này sang một kiểu dữ liệu khác. Hàm CAST và
CONVERTER cung cấp cùng một chức năng. Một điểm thuận lợi khi dùng CONVERTER là
khi chuyển đổi, hàm này cũng cho phép người dùng sẽ định dạng lại giá tri kết quả theo ý
muốn.
Cấu trúc hàm CAST và CONVERTER như sau:
CAST (biểu_thức/giá_ trị AS kiểu_dữ liệu [độ_dài_kiểu_dữ_liệu ])
CONVERT ( kiểu_dữ liệu [độ_dài_kiểu_dữ_liệu ] , biểu_thức/giá_ trị [ ,kiểu_định_dạng] )
Năm 2 chữ số Năm 4 chữ số Output
0 hoặc 100 mon dd yyyy hh:mi AM (PM)
1 101 mm/dd/yy
2 102 yy.mm.dd
100
3 103 dd/mm/yy
4 104 dd.mm.yy
5 105 dd-mm-yy
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mm:ss
9 hoặc 109 mon dd yyyy hh:mi:ss:mmmAM (PM)
10 110 mm-dd-yy
11 111 yy/mm/dd
12 112 yymmdd
13 hoặc 113 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 hh:mi:ss:mmm(24h)
Ví1 dụ:
select CUSTOMERNAME,
convert (varchar, BIRTHDAY, 103) as BIRTHDAY, ADDRESS
from Customers
where Customername = 'Le Thi Hoa'
and year(getdate()) - year(BIRTHDAY) > 20
Hàm CONVERT và hàm CAST có thể sử dụng kết hợp với nhau để cho kết qua như
mong muốn.
Ví dụ:
select c.CUSTOMERID, c.CUSTOMERNAME,
convert(varchar(20),cast(SUM(i.UNITPRICE*od.QUANTITY) as money),1) as
SUMTOTAL
from customers c inner join orders o on o.customerid = c.customerid
inner join orderdetail od on o.orderid = od.orderid
inner join items i on i.itemid = od.itemid
group by c.customerid, c.customername
101
8 Kết nối vào SQL Server 2005 từ các ngôn ngữ lập trình để
xây dựng các ứng dụng liên quan đến CSDL
Mục tiêu cuối cùng của việc học hệ quản trị CSDL Microsoft SQL Server 2005 là người
học biết dùng hệ quản trị này trong việc xây dựng các CSDL cho các ứng dụng quản lý trong
thực tế. Đồng thời sử dụng các công cụ trong Microsoft SQL Server 2005 để quản trị CSDL
của mình.
8.1 Cấu hình Microsoft SQL Server 2005
Như đã nói trong chương 1, Microsoft SQL Server 2005 sử dụng hai kiểu định danh
người dùng: Windows Authentication và SQL Server Authentication.
Windows Authentication: thích hợp trong việc xây dựng các ứng dụng quản lý trên máy
đơn ( nghĩa là SQL Server và ứng dụng quản lý cùng trên một máy). Những ứng dụng này
thường có CSDL khá nhỏ và tốc độ tăng trưởng của CSDL không cao.
SQL Server Authentication: thích hợp trong việc xây dựng các ứng dụng quản lý có
CSDL lớn, nhiều người dùng cùng lúc. Trong các ứng dụng này, CSDL được đặt trên Database
Server, ứng dụng trên các máy trạm sẽ thực hiện các kết nối vào Database Server này và thực
hiện các thao tác trên CSDL. Các kết nối này được gọi là các kết nối từ xa (remote connection).
Tuy nhiên, khi kết nối một máy tính đến một thể hiện (instance) của Microsoft SQL
Server 2005 để tạo một kết nối từ xa, bạn có thể nhận một thông báo lỗi như sau:
Microsoft SQL Native Client: An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
Lỗi này xảy ra vì Microsoft SQL Server chưa được cấu hình để chấp nhận các kết nối từ
xa.
Mặc định, phiên bản SQL Server 2005 Express Edition và phiên bản SQL Server 2005
Developer Edition không cho phép các kết nối từ xa. Để cấu hình SQL Server 2005 chấp nhận
các kết nối từ xa cần thực hiện các bước sau:
Cho phép tiếp nhận các kết nối từ xa trên thể hiện của SQL Server mà các ứng dụng máy
trạm cần kết nối.
Kích hoạt dịch vụ SQL Server Browser
Cấu hình tường lửa cho phép các dữ liệu liên quan đến SQL Server và dịch vụ SQL
Server Browser được lưu thông trên mạng.
102
8.1.1 Cho phép tiếp nhận các kết nối từ xa trên thể hiện của SQL
Server
Click Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL
Server Surface Area Configuration.
Trong trang SQL Server 2005 Surface Area Configuration, click Surface Area
Configuration for Services and Connections.
Trong trang Surface Area Configuration for Services and Connections, mở nút Database
Engine, click Remote Connections, click Local and remote connections, click chọn giao thức
thích hợp (giao thức này được lựa chọn dưa trên giao thức thực tế được dùng trong môi trường
làm viêc), sau đó click Apply. Trong môi trường học tập, chúng ta thường dùng giao thức
TCP/IP.
Lưu ý: Click OK khi nhận được thông báo:
Changes to Connection Settings will not take effect until you restart the Database Engine
service.
Trong trang Surface Area Configuration for Services and Connections, mở nút Database
Engine, click Service, click Stop, đợi đến khi dịch vụ MSSQLSERVER dừng lại, sau đó click
Start để khởi động lại dịch vụ MSSQLSERVER
 .
Lưu ý: nếu chúng ta không cần sử dụng SQL Server 2005 thường xuyên, ta có thể để chế
độ khởi động của dịch vụ MSSQLSERVER là manual (nghĩa là khi nào cần dùng thì sẽ kích
hoạt) nhằm giảm bớt thời gian khởi động Windows và tiết kiệm tài nguyên hệ thống..
8.1.2 Kích hoạt dịch vụ SQL Server Browser
Nếu chúng ta thao tác trên SQL Server 2005 bằng việc sử dụng tên thể hiện (instance
name) và không chỉ định một port cụ thể trong chuỗi kết nối (chúng ta sẽ nói về chuỗi kết nối
kỹ hơn trong phần kết nối các ngôn ngữ lập trình với SQL Server 2005) thì chúng ta phải kích
hoạt dịch vụ SQL Server Browser để cho phép các kết nối từ xa. Ví dụ: SQL Server 2005
Express Edition được cài đặt với một thể hiện mặc định là tên_máy_tính\SQLEXPRESS.
Chúng ta chỉ cần kích hoạt dịch vụ SQL Server Browser một lần bất kể chúng ta có bao nhiêu
103
thể hiện của SQL Server 2005 đang được sử dụng (running). Để kích hoạt dịch vụ SQL Server
Browser, cần làm theo các bước sau:
Click Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools, click
SQL Server Surface Area Configuration.
Trong trang SQL Server 2005 Surface Area Configuration, click Surface Area
Configuration for Services and Connections.
Trong trang Surface Area Configuration for Services and Connections, click SQL Server
Browser, click Automatic for Startup type, click Apply.
Lưu ý: tương tự như dịch vụ MSSQLSERVER, nếu chọn startup type là Automatic thì
dịch vụ SQL Server Browser sẽ được khởi động khi Windows khởi động
Kiểm tra tình trạng dịch vụ trong Service status. Sau đó click Start nếu dịch vụ này đang
bị Stop.
Lưu ý: Thực hiện các bước trên làm tăng các nguy cơ cho hệ thống vì hệ thống sẽ hiện thị
thông tin của các thể hiện của SQL Server đạng chạy trên hệ thống. Các nguy cơ này có thể
được giảm thiểu bằng cách không kích hoạt dịch vụ SQL Server Browser và kết nối vào thể
hiện của SQL Server thông qua một port. Chi tiết có thể tham khảo Microsoft Book Online
theo các chủ đề sau:
SQL Server Browser Service
Connecting to the SQL Server Database Engine
Client Network Configuration
8.1.3 Tạo các ngoại lệ trên Windows Firewall
Các bước dưới đây áp dụng cho Windows Firewall trong Windows XP Service Pack 2
(SP2) và trong Windows Server 2003.
Nếu tường lửa được sử dụng trên máy tính cài đặt SQL Server 2005, các kết nối từ bên
ngoài sẽ bị chặn trừ khi SQL Server 2005 và SQL Serve Browser có thể liên lạc qua tường lửa.
Chúng ta phải tạo ra các ngoại lệ cho mỗi thể hiện của SQL Server 2005 (muốn thể hiện nào
tiếp nhận kết nối từ xa thì chúng ta phải tạo ngoại lệ cho thể hiện đó) và một ngoại lệ cho dịch
vụ SQL Server Browser.
Việc tạo ngoại lệ theo các bước sau:
Click Start -> Programs -> Microsoft SQL Server 2005 ->Configuration Tools -> SQL
Server Configuration Manager.
104
Trong trang SQL Server Configuration Manager, click SQL Server Services, right-click
tên thể hiện, và click Properties.
Trong trang SQL Server Browser Properties, click tab Advanced, xác định instanceID
trong danh sách thuộc tính, và click OK.
Lưu ý: Chúng ta có thể có nhiều thể hiện SQL Server, do đó chúng ta phải xác định đúng
instanceID nào chúng tao muốn tạo ngoại lệ.
Để mở Windows Firewall, click Start, click Run, đánh firewall.cpl, và click OK.
Tạo ngoại lệ cho SQL Server 2005 trong Windows Firewall
Trong Windows Firewall, click tab Exceptions, sau đó click Add Program.
Trong cửa sổ Add a Program window, click Browse.
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe, click Open,
và click OK.
Lưu ý: Đường dẫn có thể khác nhau tùy thuộc vào thư mục cài đặt của SQL Server 2005.
MSSQL.1 là nơi lưu trữ cho instanceID chúng ta thu được trong bước trên.
Lặp lại các bước trên cho mỗi thể hiện của SQL Server 2005 cần thiết lập ngoại lệ.
Tạo ngoại lệ cho dịch vụ SQL Server Browser trong Windows Firewall
Trong Windows Firewall, click tab Exceptions, và click Add Program.
Trong cửa sổ Add a Program, click Browse.
C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe, click Open, và click
OK.
Lưu ý: Đường dẫn có thể thay đổi tùy thuộc vào thư mục cài đặt của SQL Server 2005.
8.2 Kết nối vào SQL Server trong các ngôn ngữ lập trình
8.2.1 C# và VB.NET
Tùy thuộc vào việc sử dụng .NET Data Provider (tập các đối tượng phục vụ việc trao đổi
dữ liệu) nào trong .NET Framework và việc sử dụng hình thức Authentication nào để truy xuất
dữ liệu trong SQL Server 2005, chúng ta sẽ có các cách khác nhau để kết nối đến SQL Server
2005. Trong phần trình bày này sẽ sử dụng System.Data.SqlClient là một Data Provider phổ
biến để kết nối với SQL Server 2005.
Trong C#:
public void ConnectToSql ()
105
{
 System.Data.SqlClient.SqlConnection conn =
 new System.Data.SqlClient.SqlConnection ();
 // TODO Xây dựng chuỗi kết nối
 conn.ConnectionString =
 "integrated security=true;data source=tên_SQLSERVER;" +
 "persist security info=False;initial catalog=tên_CSDL";
 try
 {
 conn.Open();
 // Xây dựng code để tương tác với CSDL ở đây
 }
 catch (Exception ex)
 {
 MessageBox.Show("Failed to connect to data source");
 }
 finally
 {
 conn.Close();
 }
}
Trong VB.NET
Public Sub ConnectToSql()
 Dim conn As New SqlClient.SqlConnection
 ' TODO Xây dựng chuỗi kết nối
 conn.ConnectionString = & _
 "integrated security=true;data source=tên_SQL Server;" & _
 "persist security info=False;initial catalog=tên_CSDL"
 Try
 conn.Open()
 ' Xây dựng code để tương tác với CSDL ở đây
 Catch ex As Exception
106
 MessageBox.Show("Failed to connect to data source")
 Finally
 conn.Close()
 End Try
End Sub
Trong hai ví dụ trên chúng ta xây dựng hàm kết nối vào SQL Server 2005 mà thành phần
quan trọng nhất là chuỗi kết nối vào CSDL:
Trong C#:
"integrated security=true;data source=tên_SQLSERVER;" +
 "persist security info=False;initial catalog=tên_CSDL";
Trong VB.NET:
"integrated security=true;data source=tên_SQL Server;" & _
 "persist security info=False;initial catalog=tên_CSDL"
Trong đó:
Intergrated security = true: sử dụng Windows Authentication
data source: chỉ định tên thể hiện của SQL Server 2005 mà chúng ta muốn kết nối.
persist security info: Thiết lập mặc định cho từ khóa persist security info là false. Thiết
lập sang giá trị true sẽ cho phép các dữ liệu nhạy cảm bao gồm UserID và password có thể
được truy xuất khi kết nối được mở
initial catalog: Tên CSDL mà chúng ta muốn tương tác.
Ví dụ chuỗi kết nối sử dụng Windows Authentication:
"integrated security=true;data source=.\\SQLExpress" +
 "persist security info=False;initial catalog=myDB";
Ví dụ chuỗi kết nối sử dung SQL Server Authentication:
"persist security info=False;User ID = *****; password = ***** “ +
“initial catalog=myDB; data source=.\\SQLExpress ";
8.2.2 VB 6
Ví dụ dưới đây minh họa việc xây dựng ứng dụng CSDL bằng VB6. Giả sử chúng ta có
CSDL tên là Test, SQL Server là .\\SQLExpress, User là sa, Password là 1234.
Private Sub Command1_Click()
 Dim connectionString As String
 Dim commandString As String
 Dim sqlConnection As ADODB.Connection
107
 Dim rs As Recordset
connectionString="PROVIDER=SQLOLEDB; DATA SOURCE=.\SQLEXPRESS;”
connectionString = “UID=sa; PWD=1234;DATABASE=Test"
 commandString = "select count(*) as count from Users where UserName = '"
 commandString = commandString & Text1.Text & "' and Password = '"
 commandString = commandString & Text2.Text & "'"
 Set sqlConnection = New ADODB.Connection
 sqlConnection.Open (connectionString)
 Set rs = New Recordset
 rs.Open commandString, sqlConnection
 If (rs("count") = 1) Then
 MsgBox "Login successfully"
 End If
End Sub
108
Tài liệu tham khảo
1. Giáo trình hệ quản trị cơ sở dữ liệu SQL Server, Khoa CNTT, Đại học Huế.
2. SQL Server 2005, T-SQL Recipes: Problem, Solution, Approach – Appress Publisher.
3. Sams Teach yourself Microsoft SQL Server 2005 Express in 24 hours.