Tổng quan về SQL server

  • Published on
    02-Aug-2015

  • View
    434

  • Download
    18

Embed Size (px)

Transcript

Tng quan v SQL serverMicrosoft SQL server l mt h qun tr c s d liu quan h (relational database management system-RDBMS) do Microsoft pht trin. SQL Server l mt h qun tr c s d liu quan h mng my tnh hot ng theo m hnh khch ch cho php ng thi cng mt lc c nhiu ngi dng truy xut d liu, qun l vic truy nhp hp l v cc quyn hn ca tng ngi dng trn mng. Ngn ng truy vn quan trng ca Microsoft SQL server l TransactSQL.Transact-SQL l ngn ng SQL m rng da trn SQL chun ca ISO (International Organization for Standardization ) v ANSI (American National Standars Institute ) c s dng trong SQL Server . Mt s cc phin bn ni bt ca Microsoft SQL server: Microsoft SQL server 2000: Lm ni bt dng m lnh mi, b sung cc tnh nng mi c ngha. Tng cng kh nng nh gi, tin cy v gi tr ca sn phm. Microsoft SQL server 2005: SQL server 2005 c pht hnh thng 11/2005, l phin bn tip theo ca SQL Server 2000.C nhng c im ni bc sau: Nng cao bo mt. M rng T-SQL. Tng cng h tr ngi pht trin.

Nguyn S Thng

M SV: 10T1021201

Tng cng kh nng qun l. Nng cao sn sng ca CSDL. Nng cao kh nng m rng ca CSDL. Tng cng kh nng khai thc thng tin. N tp trung vo kh nng x l giao dch trc tuyn trn di ng OLTP( large-scale online transactional processing), ng dng thng mi in t (e-commerce applications) v kho d liu (data warehousing). Ngoi ra,c nhng ci tin quan trng nh: Dch v bo co Reporting Services, Server Broker v s thay i ng k trong Database Engine. Microsoft SQL Servern 2008 : Ngy 27/02/2008, Microsoft t chc s kin c tn l Heroes Happen Here nhm gii thiu sn phm mi SQL Server 2008. Microsoft SQL server 2008 c mt s ci tin, ni bt l tnh nng phn bit loi bit ng mi v cc li ch vo trong nhm hoc cc vng chnh.C bn vng chnh l Enterprise Data Platform, Dynamic Development, Beyond Relational Database, v Pervasive . Cc c im ni bt: Nn tng cho cc nhim v then cht. S pht trin ng. D liu quan h m rng. Thng tin trong ton b doanh nghip. Microsoft SQL Server 2012: Trong thng 3/2012, tp on Microsoft tuyn b phin bn mi nht Microsoft SQL Server 2012, nn tng d liu sn sng trn th trng. Microsoft SQL Server 2012 m bo cho cc tc v quan trng lun c bo mt, thi gian h thng hot ng nhiu hn, cc tnh2

Nguyn S Thng

M SV: 10T1021201

nng bo mt c tng cng km kh nng t ph trong cung cp thng tin chuyn su. Nn tng d liu Microsoft SQL Server 2012 c ghi nhn l em n hiu nng hot ng nhanh ng ngc nhin. SQL Server 2012 gip gii quyt cc thch thc lin quan n s gia tng lng d liu bng cch chuyn cc d liu ny thnh cc tm nhn mang tnh quyt nh nhanh chng. Cc n bn ca SQL Server (Editions): Enterpise Manager: L n bn y ca SQL Server c th chy trn 32CPU v 64GB RAM. C cc dch v phn tch d liu Analysis Service. Standard: Ging nh Enterprise nhng b hn ch mt s tnh nng cao cp, c th chy trn 2CPU, 4GB RAM. Personal: Phin bn ny ch yu chy trn PC, nn c th chy trn cc h iu hnh Windows 9x, Windows XP, Windows 2000, Windows 2003 Developer: L phin bn tng t nh Enterprise nhng b gii hn bi s user kt ni n. Desktop Engine: L phin bn mt engine ch chy trn desktop v khng c giao din ngi dng (GUI), kch thc CSDL gii hn bi 2GB. Win CE: S dng cho cc ng dng chy trn Windows CE. Trial: Phin bn dng th, b gii hn bi thi gian. SQL Client: L phin bn dnh cho my khch, khi thc hin khai thc s thc hin kt ni n phin bn SQL Server, phin bn ny cung cp giao din GUI khai thc cho ngi s dng.

3

Nguyn S Thng

M SV: 10T1021201

SQL Connectivity only: L phin bn s dng ch cho cc ng dng kt ni n SQL Server, phin bn ny khng cung cp cng c GUI cho ngi dng khai thc SQL Server. Cc phin bn ny c ci t ph thc vo b ci t m bn chn hoc la chn khai ci t (v d phin bn Enterprise, Standard, Personal, bn phi chn theo b ci t, phin bn SQL Client, Connectivity, do bn chn trong cc hp thoi trong qu trnh ci t).

SQL Server c mt s c tnh sau: Cho php qun tr mt h CSDL ln (ln n vi tega byte), c tc x l d liu nhanh p ng yu cu v thi gian. Cho php nhiu ngi cng khai thc trong mt thi im i vi mt CSDL v ton b qun tr CSDL (ln n vi chc ngn user). C h thng phn quyn bo mt tng thch vi h thng bo mt ca cng ngh NT (Network Technology), tch hp vi h thng bo mt ca Windows NT hoc s dng h thng bo v c lp ca SQL Server. H tr trong vic trin khai CSDL phn tn v pht trin ng dng trn Internet Cho php lp trnh kt ni vi nhiu ngn ng lp trnh khc dng xy dng cc ng dng c th (Visual Basic, C, C++, ASP, ASP.NET, XML,...). S dng cu lnh truy vn d liu Transaction-SQL (Access l SQL, Oracle l PL/SQL).

Mt s khi nim ca SQL server:4

Nguyn S Thng

M SV: 10T1021201

Data Type (Kiu d liu ):l cc kiu lu tr c bn v d liu.C th chn cc kiu d liu khc nhau, chng hn nh k t, s hoc ngy. Stored procedure (th tc thng tr-th tc lu tr): m lnh Transact-SQL c th c vit v lu tr mang mt tn. User defined functions (Cc hm do ngi dng nh ngha ): L cc m lnh Transact-SQL rt ging vi cc m lnh ca th tc lu tr.C th c gi trong cc truy vn CSDL hoc chnh sa mt c s d liu hoc ng vai tr nh cc bng. V d: C th vit cc hm Date cho ring mnh chnh sa cc ct d liu c kiu datetime. Constraints (Cc rng buc): L cc c ch c h thng ci t da trn my ch nhm bo v tnh ton vn ca d liu. Triggers (cc by li): L cc th tc lu tr kch hot trc hoc sau khi b sung ,sa cha hoc xa d liu ra khi CSDL.m bo tun theo cc quy tc kinh doanh hoc quy tc ton vn d liu khc trong CSDL. Views (Cc khung nhn-hay bng o): L cc truy vn lu tr trong CSDL c th tham chiu ti mt hoc nhiu bng.Thng th hin hoc khng th hin cc ct no trong bng hoc l lin kt hai hoc nhiu bng vi nhau. Indexes (Cc ch mc ): Gip t chc li d liu, nn cc truy vn chy nhnh hn.

5

Nguyn S Thng

M SV: 10T1021201

Cc m hnh truy xut d liu : Kin trc Client/ Server (Client/ Server architecture): Pha my ch thng c ci t cc h c s d liu bo gm cc bng biu, cc th tc lu tr . . . v iu khin cc tin trnh sau: Qun l d liu, bo mt d liu, thc hin truy vn, rng buc v cc th tc lu tr, iu khin li.Pha my khch: To giao din ngi s dng (User interface) Kin trc nhiu tng (N-tier Architecture ): Qu trnh x l c phn tn trn cc lp khc nhau vi cc chc nng ring bit, ph hp cho vic t chc h thng thng tin trn mng Internet hay trong cc mng cc b.Cc tng (lp ): Ngun d liu, truy cp d liu, lp giao dch, lp giao din.

Transact SQL (T-SQL) : L ngn ng SQL m rng da trn chun ANSI. L ngn ng giao tip gia ng dng v SQL server T-SQL bao gm cc nhm : o Data Definition Language (DDL): ngn ng nh ngha d liu.CREATE, ALTER, DROP. o Data Manipulation Language (DML): Ngn ng x l d liu.SELECT, INSERT, UPDATE. o Data Control Language Statements (DCL): ngn ng iu khin d liu.DENY, GRANT, REVOKE

6