Chuong 1- Tong Quan MS SQL Server

  • Published on
    10-Jul-2015

  • View
    292

  • Download
    0

Transcript

H QUN TR CSDL SQL SERVER Chng 1:

TNG QUAN V MICROSOFT SQL SERVER 2005Gio vin: T Thc Nhu Khoa CNTT trng H Lc Hng

I- Gii thiu MS SQL Server 20051. H qun tr CSDL quan h kiu Client/Server 2. Cc dch v trong SQL Server 2005 3. Ngn Ng Lp Trnh Transact-SQL(T-SQL): 4. Mt s n bn ca MS SQL Server 2005:

2

SQL Server

1- H qun tr CSDL quan h kiu Client/Server :Microsoft SQL Server l mt h qun tr CSDL quan h (Relation Database Management System - RDBMS) hot ng trn h thng mng theo m hnh Client/Server cho php nhiu ngi dng t cc my trm truy cp d liu lu trn my ch.

3

SQL Server

2- Cc dch v trong SQL Server 2005 Dch v tch hp - Integration Services (SSIS) : rt trch, bin i v hp nht d liu (consolidate data) t nhiu ngun d liu khc nhau (disparate sources). Cng ngh CSDL - Database Engine: lu tr, x l, v bo mt d liu. Dch v phn tch - Analysis Services: x l phn tch trc tuyn (online analytical processing - OLAP); khai m d liu (Data Mining) cho cc ng dng nh gi tnh hnh thng mi. Dch v bo co - Reporting Services: to v qun l bo co vi nhiu dng thc khc nhau: dng bng (tabular), ha (graphical) Cc bo co ny cng c th c xut trn Web.4 SQL Server

2- Cc dch v trong SQL Server 2005 (tt) Mi gii dch v - Service Broker: Cung cp nhiu c s h tng (infrastructure) cn thit xy dng mt ng dng CSDL, lm gim ng k thi gian pht trin ng dng. Ti to d liu Replication: sao chp, phn phi d liu ti cc CSDL v tr khc, t xa hay di ng thng qua h thng mng, sau ng b gia cc CSDL duy tr tnh ng nht d liu (consistency). Full-Text Search: Phc v cho vic tm kim t, nhm t trong cc d liu dng vn bn lu trn table. Dch v thng bo - Notification Services: Phc v cho vic pht trin v trin khai cc ng dng to v gi cc thng bo ti ngi dng trn nhiu loi thit b khc nhau.

5

SQL Server

3- Ngn Ng Lp Trnh Transact-SQL(T-SQL): T-SQL l ngn ng th tc th h th 3, Khng dng to ra cc chng trnh ng dng c lp. Cc pht biu, th tc v hm vit bng T-SQL ch c thc hin trong mi trng SQL Server vi mc ch truy vn v hiu chnh d liu trong CSDL quan h. Cho php gi thc hin t cc ngn ng lp trnh khc

6

SQL Server

T-SQL c cc pht biu c phn loi nh sau : Data Control Language (DCL): Cha cc lnh iu khin, phn quyn truy xut d liu. Data Definition Language (DDL): Dng to, sa xa cc i tng trong CSDL nh Database, Table, Procedure Data Manipulation Language (DML): Cha cc lnh thm, sa, xo d liu Data Query Language (DQL) : Ch cha 1 pht biu SELECT dng truy vn d liu Cc thnh phn khc ca ngn ng nh kiu d liu, bin, ton t, hm, cc cu trc iu khin v ch thch.

7

SQL Server

4- Cc n Bn Ca Microsoft SQL Server 2005: n bn dng trn mi trng mng Windows Server Standard Edition (32-bit v 64-bit): dng cho vcc t chc va v nh. Enterprise Edition (32-bit v 64-bit): dng cho cc t chc ln vi cc yu cu phc tp Workgroup Edition (ch 32-bit): l gii php qun l d liu cho cc t chc nh, d dng nng cp ln phin bn Standard v Enterprise Edition. Developer Edition (32-bit v 64-bit): N cha cc tnh nng ca SQL Server Enterprise, dng xy dng cc loi ng dng trn SQL Server, sau trin khai trn cc phin bn khc. Express Edition (ch 32-bit): c phn phi t do vi cc chc nng qun l CSDL n gin, phi hp vi Microsoft Visual Studio xy dng cc ng dng qun l d liu.8 SQL Server

n bn cho my n vi HH Windows

II- Ci t MS SQL Server 2005 Developer a a Mocrosoft SQL Server Developer vo a CD/DVD. Chy tp tin Splash.hta khi ng chng trnh ci t (nu khng Autorun).

9

SQL Server

HT 1: Chn phin bn 32-bit (x86) hay 64-bit (x64) ty theo h iu hnh ca my.

10

SQL Server

HT 2: Chn ci t Server components, tools, Books Online, and samples

11

SQL Server

HT 3: Chn I accept the licensing items and conditions, sau chn nt Next

12

SQL Server

HT 4: Chn Install ci t cc thnh phn c yu cu trn hp thoi

13

SQL Server

14

SQL Server

HT 5: Chn nt Next bt u ci t SQL Server 2005

15

SQL Server

HT 6: Chn nt Next sau khi kim tra cu hnh h thng

16

SQL Server

HT 7: Nhp tn c nhn, cng ty v kha ca sn phm.

17

SQL Server

HT 8: Chn cc thnh phn mun ci t. Chn Advanced ch nh cc thnh phn khc

18

SQL Server

HT 9: Chn hay b chn cc thnh phn ci t, chn nt Next

19

SQL Server

HT 10: Chn Default instance nu mun tn th hin ca SQL Server trng vi tn ca my tnh; chn Named instance ch nh tn khc.

20

SQL Server

HT 11: Chn ti khon ngi dng h thng cc b (Local System)

21

SQL Server

HT 12: Chn ch chng thc

22

SQL Server

HT 13: Chn cu hnh sp xp ca Microsoft SQL Server.

23

SQL Server

HT14: Mc nh ty chn ca Report Server

24

SQL Server

HT 15: Chn nt Install bt u sao chp tp tin t CDROM vo a cng ca my

25

SQL Server

HT 16: Sau khi ci t xong, chn nt Next.

26

SQL Server

HT 17: Chn nt Finish kt thc ci t

27

SQL Server

III-Cc Cng C Ca SQL Server 2005Mt s cng c chnh: 1. Books Online 2. SQL Server Configuration Manager 3. SQL Server Profiler 4. Import And Export Data 5. Microsoft SQL Server Management Studio

28

SQL Server

1- Books Online: Sch hng dn trc tuyn cc vn lin quan n Microsoft SQL Server

29

SQL Server

2- SQL Server Configuration Manager SQL Server 2005 Services: Khi ng (Start), tm dng (pause) v dng (stop) cc dch v ca Microsoft SQL Server. SQL Server 2005 Network Configuration: Qun l cc nghi thc kt ni mng (Network Protocol) ca my ch phc v cho vic trao i thng tin gia my ch v my trm. SQL Native Client Configuration: Qun l cc nghi thc kt ni mng mc nh ca my trm khi thc hin ni kt vo Microsoft SQL Server ti cc my ch

30

SQL Server

3- SQL Server Profiler : Qun l cc bin c xy ra khi thc hin cc x l trn my ch, gip cho vic phn tch tm li thc hin ca cc cu lnh truy vn

31

SQL Server

4- Import And Export Data: Dng nhp, xut d liu gia Microsoft SQL Server vi cc loi CSDL khc nh: Access, Foxpro, Excel, file vn bn ASCII

32

SQL Server

5- Microsoft SQL Server Management Studio Mt cng c ha dng cho nh qun tr v pht trin CSDL SQL Server thc hin cc dch v c trong SQL Server. Tch hp cc chc nng qun tr CSDL v trnh son tho truy vn (Query Editor) vi ngn ng lp trnh Transact-SQL.

33

SQL Server

a- M SQL Server Management Studio Server Type: Chn dch v Server Name: Chn my ch ci SQL Server hoc tn th hin SQL Server c dng \. Ch nh truy xut SQL Server bng ti khon no: Windows Authentication : s dng ti khon ng nhp Windows SQL Server Authentication: dng ti khon to trong SQL Server, nhp tn ng nhp v mt khu c ngi qun tr cung cp.34 SQL Server

b- Cc thnh phn trong ca s Management Studio:

35

SQL Server

Chc nng tng thnh phn

Registered Servers: Lit k cc th hin ca Microsoft SQL Server (gi tt l server) trn cc my ch ng k trong Management Studio. Ta c th thm v xa cc server trong danh sch. Nu khng xut hin server trong Registered Servers, right-click Database Engine (Microsoft SQL Servers), v chn mc Update Local Server Registration. Double-click vo tn server kt ni ti server .

Object Explorer: lit k tt c cc i tng trong Server kt ni bao gm cc CSDL (databases), Security, Server Objects, Replication, Management, . Ca s h s: cha cc trang hin th thng tin ca i tng c chn trong ca s Object Explorer, cc trang to v hiu chnh cc i tng trong CSDL; cc trang truy vn ca trnh son tho truy vn Query Editor ... Template Explorer: Cha cc on trnh mu ca T_SQL36 SQL Server

c- Trang truy vn ca Query Editor Thc hin mt hay nhiu pht biu truy vn trc tip trn cc CSDL v nhn kt qu ngay sau khi thc hin. Son tho cc th tc, hm v lu tr cc pht biu vo tp tin kch bn (Script file) dng vn bn ASCII.37 SQL Server

M trang Query M trang Query kt ni vi CSDL trn server hin hnh:B1: Bm chn tn CSDL cn truy vn mc nh trong khung Object Explorer B2: Chn menu FileNewQuery With Current Connection hoc bm chn mc New Query trn thanh Standard

M trang Query kt ni vi CSDL trn server khc:B1. Chn menu FileNewDatabase Engine Query hoc bm chn biu tng Database Engine Query trn thanh Standard. B2. Khi thc hin s xut hin hp thoi Connect to Database Engine, chn Server name, ch nh quyn ng nhp v click nt Connect.

38

SQL Server

Mt s thao tc c bn trn trang Query: Thay i CSDL truy cp mc nh: Chn tn CSDL trong combo Available Databases trn thanh cng c SQL Editor Hoc s dng lnh: Use .

HighLight t kha bng chut v n Shift-F1 m phn tr gip lin quan. Click nt Execute hoc n phm F5 thc hin cc lnh trong trang Query hoc ch thc hin cc lnh t chn.

39

SQL Server

IV- Khai bo kt ni Microsoft SQL Server t my trm Khi ng tin ch SQL Server Configuration Manager ti my trm M nhnh SQL Native Client Configuration, bm phi chut vo mc Aliases v chn New Alias

40

SQL Server

Trong hp thoi AliasNew, ln lt nhp: b danh my trm (Alias); Cng kt ni (Port No); Nghi thc kt ni mng (Protocol); a ch IP ca my ch Bm nt OK ng kt ni.

Sau khi kt ni, ta c th s dng cc cng c ca Microsoft SQL Server truy cp CSDL trn my ch.41 SQL Server

Yu cu:1. Ci t Microsoft SQL Server 2005 Developer trn my n 2. Ci t Microsoft SQL Server 2005 Enterprise trn mt h thng mng

42

SQL Server

Ti liu tham kho Book Online ca Microsoft SQL Server 2005 Gio trnh SQL Server 2005, Trn Xun Hi Nguyn Tin Dng, i hc Quc Gia TP. HCM SQL Server 2005 Lp trnh T-SQL, Phm Hu Khang on Thin Ngn, NXB Lao ng X Hi SQL Server 2005 Lp trnh Th tc v Hm, Phm Hu Khang on Thin Ngn, NXB Lao ng X Hi

43

SQL Server