Chuong 1- Tong Quan MS SQL Server

  • Published on
    10-Jul-2015

  • View
    292

  • Download
    0

Transcript

<p>H QUN TR CSDL SQL SERVER Chng 1:</p> <p>TNG QUAN V MICROSOFT SQL SERVER 2005Gio vin: T Thc Nhu Khoa CNTT trng H Lc Hng</p> <p>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:</p> <p>2</p> <p>SQL Server</p> <p>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.</p> <p>3</p> <p>SQL Server</p> <p>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</p> <p>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.</p> <p>5</p> <p>SQL Server</p> <p>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</p> <p>6</p> <p>SQL Server</p> <p>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.</p> <p>7</p> <p>SQL Server</p> <p>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</p> <p>n bn cho my n vi HH Windows </p> <p>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).</p> <p>9</p> <p>SQL Server</p> <p>HT 1: Chn phin bn 32-bit (x86) hay 64-bit (x64) ty theo h iu hnh ca my.</p> <p>10</p> <p>SQL Server</p> <p>HT 2: Chn ci t Server components, tools, Books Online, and samples</p> <p>11</p> <p>SQL Server</p> <p>HT 3: Chn I accept the licensing items and conditions, sau chn nt Next</p> <p>12</p> <p>SQL Server</p> <p>HT 4: Chn Install ci t cc thnh phn c yu cu trn hp thoi</p> <p>13</p> <p>SQL Server</p> <p>14</p> <p>SQL Server</p> <p>HT 5: Chn nt Next bt u ci t SQL Server 2005</p> <p>15</p> <p>SQL Server</p> <p>HT 6: Chn nt Next sau khi kim tra cu hnh h thng</p> <p>16</p> <p>SQL Server</p> <p>HT 7: Nhp tn c nhn, cng ty v kha ca sn phm.</p> <p>17</p> <p>SQL Server</p> <p>HT 8: Chn cc thnh phn mun ci t. Chn Advanced ch nh cc thnh phn khc</p> <p>18</p> <p>SQL Server</p> <p>HT 9: Chn hay b chn cc thnh phn ci t, chn nt Next</p> <p>19</p> <p>SQL Server</p> <p>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.</p> <p>20</p> <p>SQL Server</p> <p>HT 11: Chn ti khon ngi dng h thng cc b (Local System)</p> <p>21</p> <p>SQL Server</p> <p>HT 12: Chn ch chng thc</p> <p>22</p> <p>SQL Server</p> <p>HT 13: Chn cu hnh sp xp ca Microsoft SQL Server.</p> <p>23</p> <p>SQL Server</p> <p>HT14: Mc nh ty chn ca Report Server</p> <p>24</p> <p>SQL Server</p> <p>HT 15: Chn nt Install bt u sao chp tp tin t CDROM vo a cng ca my</p> <p>25</p> <p>SQL Server</p> <p>HT 16: Sau khi ci t xong, chn nt Next.</p> <p>26</p> <p>SQL Server</p> <p>HT 17: Chn nt Finish kt thc ci t</p> <p>27</p> <p>SQL Server</p> <p>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</p> <p>28</p> <p>SQL Server</p> <p>1- Books Online: Sch hng dn trc tuyn cc vn lin quan n Microsoft SQL Server</p> <p>29</p> <p>SQL Server</p> <p>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</p> <p>30</p> <p>SQL Server</p> <p>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</p> <p>31</p> <p>SQL Server</p> <p>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</p> <p>32</p> <p>SQL Server</p> <p>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.</p> <p>33</p> <p>SQL Server</p> <p>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</p> <p>b- Cc thnh phn trong ca s Management Studio:</p> <p>35</p> <p>SQL Server</p> <p>Chc nng tng thnh phn</p> <p>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 .</p> <p>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</p> <p>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</p> <p>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</p> <p> 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.</p> <p>38</p> <p>SQL Server</p> <p>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 .</p> <p> 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.</p> <p>39</p> <p>SQL Server</p> <p>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</p> <p>40</p> <p>SQL Server</p> <p>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.</p> <p>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</p> <p>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</p> <p>42</p> <p>SQL Server</p> <p>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</p> <p>43</p> <p>SQL Server</p>