Êý¾Ý¿âʵÑé--±íºÍ±íÊý¾ÝµÄ²Ù×÷µÈ ÏÂÔØ±¾ÎÄ

½ðÁê¿Æ¼¼Ñ§ÔºÊµÑ鱨¸æ

¶þ¡¢ÁÐ 1¡¢ÁÐÊý¾ÝÀàÐÍ

ÔÚÉè¼Æ±íµÄʱºò£¬ÐèÒªÖªµÀÿÁÐ×ֶεÄÊý¾ÝÀàÐÍ¡£SQL Server2005ϵͳÌṩÁË28ÖÖÊý¾ÝÀàÐÍ£¬Ö÷Òª·ÖΪÆß´óÀà¡£

£¨1£©¾«È·Êý×ÖÀàÐÍ£ºÕâЩÊý¾ÝÀàÐ͵ÄÊý¾Ý¿ÉÒԲμӸ÷ÖÖÊýѧÔËË㣬ËùÓеÄÊýÖµ¶¼ÊÇÓо«¶ÈµÄ£¬¾«¶ÈÊÇÖ¸ÓÐЧÊý×ÖλÊý¡£ÈçÕûÊýÊý¾ÝÀàÐÍ£ºbinint¡¢int¡¢smalling¡¢tinyint£»Î»Êý¾ÝÀàÐÍbit;»õ±ÒÊý¾ÝÀàÐÍ£ºMoney¡¢SmallmoneyµÈ¡£

£¨2£©½üËÆÊý×ÖÀàÐÍ£ºÔÚÏÖʵ¼ÆËãÖУ¬»¹ÓÐһЩÊý¾ÝûÓо«È·ÊýÖµ£¬Èç1/3£¬ËüÎÞ·¨Óþ«È·Êý×ÖÀàÐͱíʾ£¬ÐèÒªÓÉϵͳÀ´¶¨Ò徫ȷµ½ÄÄһ룬ÕâÖÖÀàÐ;ÍÊǽüËÆÊý¾ÝÀàÐÍ¡£È磺DecimalºÍNumeric£»FloatºÍRealµÈ¡£

£¨3£©×Ö·ûÊý¾ÝÀàÐÍ£ºËüÊÇ´æ´¢¸÷ÖÖ×ÖĸÊý×Ö·ûºÅºÍÌØÊâ·ûºÅ¡£ÔÚʹÓøÃÊý¾ÝÀàÐÍʱ£¬ÐèÒªÓÃÓõ¥ÒýºÅ»òË«ÒýºÅÀ¨ÆðÀ´¡£SQL Server2005Ö÷ÒªÌṩÁËChar¡¢Varchar¡¢Text3ÖÐÊý¾ÝÀàÐÍ¡£ £¨4£©Unicode×Ö·ûÊý¾ÝÀàÐÍ£ºUnicodeÊÇÒ»ÖÖÔÚ¼ÆËã»úÉÏʹÓõÄ×Ö·û±àÂë¡£ËüΪÿһÖÖÓïÑÔÖеÄÿ¸ö×Ö·ûÉ趨ÁËͳһ²¢ÇÒΨһµÄ¶þ½øÖƱàÂ룬ÒÔÂú×ã¿çÓïÑÔ¡¢¿çƽ̨½øÐÐÎı¾×ª»»¡¢´¦ÀíµÄÒªÇó¡£SQL Server2005Ö÷ÒªÓУºNchar¡¢Nvarchar¡¢NtextÈýÖÖ¡£

£¨5£©¶þ½øÖÆÊý¾ÝÀàÐÍ£º¶þ½øÖÆÊý¾ÝÀàÐÍ¿ÉÒÔÓÃÀ´´æ´¢¶þ½øÖƵÄÊý¾Ý¡£Ö÷ÒªÓУºBinary¡¢Varbinary¡¢ImageÈýÖÖÀàÐÍ¡£

£¨6£©ÈÕÆðºÍʱ¼äÊý¾ÝÀàÐÍ£ºÔÚSQL Server2005ÖУ¬ÈÕÆðºÍʱ¼äÀàÐÍÖ÷Òª°üÀ¨£ºdatetime ºÍsmalldatetimeÁ½ÖÖ¡£Á½ÕßÇø±ðÔÚÓÚÆä±íʾµÄÈÕÆÚºÍʱ¼äµÄ·¶Î§²»Í¬£¬Ê±¼äµÄ¾«È·¶ÈÒ²²»Í¬¡£ £¨7£©ÆäËûÊý¾ÝÀàÐÍ£ºSQL Server2005ϵͳ»¹ÌṩÁËCursor¡¢Sql_varant¡¢Table¡¢Timestamp¡¢Uniqueidentifie¡¢XML6ÖÖÌØÊâÓÃ;µÄÊý¾ÝÀ´ÐÍ¡£ 2¡¢ÁÐµÄÆäËûÊôÐÔ

£¨1£©NULLºÍNOT NULL£º¶¨ÒåÊôÐÔÁÐʱ¿ÉÒԹ涨¸ÃÁÐÊÇ·ñ¿ÉÒÔΪ¿Õ¡£

£¨2£©Ä¬ÈÏÖµ£ºµ±Ä³¸öÊôÐÔµÄÖµ´ó²¿·ÖµÄȡֵ¶¼ÊÇÏàͬʱ£¬¿ÉÒÔʹÓÃĬÈÏÖµÀ´¼õÉÙ²åÈëÊý¾ÝʱµÄʱ¼ä¡£

£¨3£©IDENTITY£ºÊ¹ÓÃIDENTITY¹Ø¼ü×Ö¶¨ÒåµÄ×Ö¶ÎΪ±êʶ×ֶΣ¬±êʶ×Ö¶ÎÊÇΨһ±êʶÿÌõ¼Ç¼µÄÌØÊâ×ֶΣ¬µ±Ò»ÌõмǼÌí¼Óʱ£¬Õâ¸ö×ֶξͱ»×Ô¶¯¸³ÓèÒ»¸öÐÂÖµ¡£Ä¬ÈÏÇé¿öÏÂÊÇ+1µÝÔö¡£ Èý¡¢´´½¨±í

1¡¢Ê¹ÓÃSQL Server Management Studio¹¤¾ßÔÚMyDBÊý¾Ý¿âÖд´½¨StudentºÍClass±í¡£ Student±íµÄ½á¹¹Îª£º

×Ö¶ÎÃû SNO SName Sex ÀàÐÍ Char(8) Varchar(10) Char(2) ÖÐÎÄÃû ѧºÅ ÐÕÃû ÐԱ𠱸ע Ö÷¼ü ÄС¢Å® 8

½ðÁê¿Æ¼¼Ñ§ÔºÊµÑ鱨¸æ

ClsNO StuAddr Birthday Height Char(6) Varchar(20) SmallDate Numeric(4,2) °à¼¶ סַ ³öÉúÄêÔ Éí¸ß ×Üѧ·Ö ÖÐÎÄÃû °àºÅ °àÃû ¸¨µ¼Ô± רҵ °à¼¶µÄ±àºÅ£¬²ÎÕÕ±íClass ÃèÊöÐÔ˵Ã÷ ÒÔÃ×Ϊµ¥Î»±íʾѧÉúµÄÉí¸ß¡£ ±¸×¢ Ö÷¼ü ¶Ô°à¼¶µÄÃèÊöÐÅÏ¢ °à¼¶µÄ¸¨µ¼Ô± TotalCredit Tinyint Class±íµÄ½á¹¹Îª£º

×Ö¶ÎÃû ClsNO ClsName Director Specialty ÀàÐÍ Char(6) Varchar(16) Varchar(10) Varchar(30) 2¡¢Ê¹ÓÃT-SQLÓïÑÔÔÚMyDBÊý¾Ý¿âÖд´½¨CourseºÍGrade±í Course±íµÄ½á¹¹Îª£º

×Ö¶ÎÃû CNO CName PCNO Credit

Grade±íµÄ½á¹¹Îª£º

×Ö¶ÎÃû SNO CNO Scorce Credit ÀàÐÍ Char(8) Char(4) ÖÐÎÄÃû ѧºÅ ¿Î³ÌºÅ ±¸×¢ ѧºÅ£¬²ÎÕÕStudent£¬ÓëCNO×é³ÉÖ÷¼ü ¿Î³ÌºÅ£¬²ÎÕÕCourse ÀàÐÍ Char(4) Varchar(16) char(4) Tinyint ÖÐÎÄÃû ¿Î³ÌºÅ ¿Î³ÌÃû ÏÈÐÞ¿Î³Ì Ñ§·Ö ±¸×¢ Ö÷¼ü ¿Î³ÌµÄÃû³Æ ÏÈÐ޿γ̵Ŀγ̺ŠNumeric (4,1) ³É¼¨ Tinyint ѧ·Ö ËÄ¡¢×Ô¶¨ÒåÊý¾ÝÀàÐÍ

£¨1£©Ê¹ÓÃSQL Server Management Studio¹¤¾ß´´½¨Óû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ£ºstudentNo£¬char(8) £¨2£©ÏµÍ³´æ´¢¹ý³Ìsp_addtype ´´½¨Óû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ£ºcourseNo£¬char(4) £¨3£©ÐÞ¸ÄMyDBÊý¾Ý¿âÖеĸ÷±í£¬Ñ§ºÅºÍ¿Î³ÌºÅÓ¦ÓÃ×Ô¶¨ÒåµÄÊý¾ÝÀàÐÍ¡£

Îå¡¢Ð޸ıí½á¹¹

£¨1£©Ê¹ÓÃAlterÐÞ¸ÄStudent±í½á¹¹£¬Ê¹ÆäÔö¼ÓÒ»ÁÐDepartment£¨Ïµ±ð£©Char(12)¡£ £¨2£©Ê¹ÓÃSQL Server Management Studio¹¤¾ßɾ³ý¸Õ¸ÕÔö¼ÓµÄDepartment¡£ Áù¡¢²åÈëÊý¾Ý

ʹÓÃSQL Server Management Studio¹¤¾ß»òinsert intoÓï¾ä·Ö±ðÏòÏÂÁбí¸ñ²åÈëÊý¾Ý¡£ £¨1£©Ïò±í£¨Student£©ÖвåÈëÊý¾Ý SNO 19920101 19920102 19920306

SName Sex Íõ¾ü Àî½Ü ÍõÍ® ÄÐ ÄÐ Å® ClsNO StuAddr CS01 CS01 MT04 ϹØ40# ½­±ß·96# ÖÐÑë·94# 9

Birthday 1976.12.21 1974.5.24 1977.3.18 Height 1.76 1.72 1.65 TotalCredit 10 9 9 ½ðÁê¿Æ¼¼Ñ§ÔºÊµÑ鱨¸æ

19940106 Îâè Ů PH08 Á«»¯Ð¡Çø74# 1979.4.8 1.60 4 ²åÈëÊý¾ÝÖ®ºóʹÓÃÃüÁSelect * from Student; ¼ì²é²åÈëÊý¾ÝµÄÕýÈ·ÐÔ £¨2£©Ïò±í£¨Class£©ÖвåÈëÊý¾Ý ClsNO CS01 MT04 PH08 ClsName ¼ÆËã»úÒ»°à ÊýѧËİà ÎïÀí°Ë°à Director ÍõÄþ ³Â³¿ ¸ð¸ñ Specialty ¼ÆËã»úÓ¦Óà Êýѧ ÎïÀí ²åÈëÊý¾ÝÖ®ºóʹÓÃÃüÁSelect * from Class; ¼ì²é²åÈëÊý¾ÝµÄÕýÈ·ÐÔ £¨3£©Ïò±í£¨Course £©ÖвåÈëÊý¾Ý CNO 0001 0003 0007 CName Êýѧ ¼ÆËã»ú»ù´¡ ÎïÀí PCNO Null 0001 0001 Credit 6 3 4 ²åÈëÊý¾ÝÖ®ºóʹÓÃÃüÁSelect * from Course; ¼ì²é²åÈëÊý¾ÝµÄÕýÈ·ÐÔ £¨4£©Ïò±í£¨Grade £©ÖвåÈëÊý¾Ý SNO 19920101 19920101 19920102 19920102 19920306 19920306 19940106 CNO 0001 0007 0001 0003 0001 0003 0007 Score 90 86 87 76 87 93 85 Credit 6 4 6 3 6 3 4 ²åÈëÊý¾ÝÖ®ºóʹÓÃÃüÁSelect * from Grade; ¼ì²é²åÈëÊý¾ÝµÄÕýÈ·ÐÔ Æß¡¢É¾³ý±íÖеÄÊý¾Ý

£¨1£©Ê¹ÓÃSQL Server Management Studio¹¤¾ßɾ³ý¿Î³ÌºÅΪ0001µÄÑ¡¿Î¼Ç¼¡£ £¨2£©Ê¹ÓÃT-SQLÓïÑÔɾ³ýGrade±íÖÐѧºÅΪ19920101µÄÊý¾Ý °Ë¡¢É¾³ý±í¸ñ

£¨1£©Ê¹ÓÃSQLSQL Server Management Studio¹¤¾ßɾ³ýClass±í¡£ £¨2£©Ê¹ÓÃDrop TableÃüÁîɾ³ýGrade±í¡£

*×¢ÒâÔÚɾ³ý֮ǰ£¬Ç뽫Êý¾ÝÎļþ¿½±´Ò»¸ö¸±±¾£¬Òѱ¸ÒÔºóʹÓá£

Îå¡¢ÎÊÌâ½â´ð¼°ÊµÑé½á¹û

Èý¡¢´´½¨±í

£¨1£©´´½¨Student±í

CREATE TABLE [dbo].[Student](

[SNO] [char](8) COLLATE Chinese_PRC_CI_AS NOT NULL, [SName] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [Sex] [char](2) COLLATE Chinese_PRC_CI_AS NOT NULL,

10

½ðÁê¿Æ¼¼Ñ§ÔºÊµÑ鱨¸æ

[ClsNO] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [StuAddr] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, [Birthday] [smalldatetime] NOT NULL, [Height] [numeric](4, 2) NOT NULL, [TotalCredit] [tinyint] NOT NULL,

CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [SNO] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] £¨2£©´´½¨class±í

CREATE TABLE [dbo].[Class](

[ClsNO] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [ClsName] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL, [Director] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [Specialty] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,

CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED ( [ClsNO] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] £¨3£©´´½¨Course±í

CREATE TABLE [dbo].[Course](

[CNO] [char](4) COLLATE Chinese_PRC_CI_AS NOT NULL, [CName] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL, [PCNO] [char](4) COLLATE Chinese_PRC_CI_AS NULL, [Credit] [tinyint] NULL,

CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ( [CNO] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] £¨4£©´´½¨grade±í

CREATE TABLE [dbo].[Grade](

[SNO] [char](8) COLLATE Chinese_PRC_CI_AS NOT NULL, [CNO] [char](4) COLLATE Chinese_PRC_CI_AS NOT NULL, [Scorce] [numeric](4, 1) NULL, [Credit] [tinyint] NULL,

CONSTRAINT [PK_Grade] PRIMARY KEY CLUSTERED ( [SNO] ASC,

[CNO] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

ËÄ¡¢×Ô¶¨ÒåÊý¾ÝÀàÐÍ

use MyDB

create table Grade ( SNO char(8) not null,

11