v1.0 ¿É±à¼¿ÉÐÞ¸Ä ¿Î³ÌÃû³Æ ʵÑéÃû³Æ ѧºÅ ʵÑéÄ¿µÄ£º Êý¾Ý¿âÔÀí ½»»¥Ê½SQLÓïÑÔ ÐÕÃû °à¼¶ ʵÑé ³É¼¨ ÈÕÆÚ 1. ÊìϤÊý¾Ý¿âµÄ½»»¥Ê½SQL¹¤¾ß£» 2. ÊìϤͨ¹ýSQLÓïÑÔ¶ÔÊý¾Ý¿â½øÐвÙ×÷£» ʵÑéÆ½Ì¨£º ÀûÓÃRDBMS£¨SQL Server 2005£©¼°Æä½»»¥²éѯ¹¤¾ßÀ´²Ù×÷SQLÓïÑÔ£» ʵÑéÄÚÈÝÓë²½Ö裺 Ò»¡¢ Êý¾Ý¶¨Òå £¨Ò»£©»ù±¾±í²Ù×÷ 1. ½¨Á¢»ù±¾±í ÔÚÊý¾Ý¿âTESTÖн¨Á¢3ÕÅ»ù±¾±í£ºStudent¡¢CourseºÍSC¡£ £¨1£© ´´½¨Ñ§Éú±íStudent£¬ÓÉÒÔÏÂÊôÐÔ×é³É£ºÑ§ºÅSNO(charÐÍ£¬³¤¶ÈΪ4£¬ Ö÷Âë)£¬ÐÕÃûSNAME(charÐÍ£¬³¤¶ÈΪ8£¬ÆäֵΨһ)£¬ÐÔ±ðSEX£¨charÐÍ£¬³¤¶ÈΪ2£©£¬ËùÔÚϵDEPTNO(charÐÍ£¬³¤¶ÈΪ2)¡£ --CREATE TABLE Student --( -- SNO CHAR(4)PRIMARY KEY, -- SNAME CHAR(8)UNIQUE, -- SEX CHAR(2), -- DEPTNO CHAR(2), --) £¨2£©´´½¨¿Î³Ì±íCourse,ÓÉÒÔÏÂÊôÐÔ×é³É£º¿Î³ÌºÅCNO(charÐÍ,³¤¶ÈΪ2£¬Ö÷Âë)£¬¿Î³ÌÃû³ÆCNAME(charÐÍ£¬³¤¶ÈΪ20£¬·Ç¿Õ)£¬ÊڿνÌʦ±àºÅTNO£¨charÐÍ£¬³¤¶ÈΪ3£¬Ö÷Â룩£¬Ñ§·ÖCREDIT(intÐÍ)¡£ --CREATE TABLE Course --( -- CNO CHAR(2), 1 v1.0 ¿É±à¼¿ÉÐÞ¸Ä -- CNAME CHAR(20), -- TNO CHAR(3)PRIMARY KEY, -- CREAIT INT, --); £¨3£©´´½¨Ñ§ÉúÑ¡¿Î±íSC,ÓÉÒÔÏÂÊôÐÔ×é³É£ºÑ§ºÅSNO( charÐÍ£¬³¤¶ÈΪ4£¬Ö÷Âë),¿Î³ÌºÅCNO( charÐÍ,³¤¶ÈΪ2£¬Ö÷Âë),³É¼¨GRADE(intÐÍ)¡£ -- CREATE TABLE SC -- ( -- SNO CHAR(4), -- CNO CHAR(2), -- GRADE INT, -- PRIMARY KEY (SNO,CNO), -- ); £¨4£©´´½¨½Ìʦ±íTeacher,ÓÉÒÔÏÂÊôÐÔ×é³É£º½Ìʦ±àºÅTNO(charÐÍ£¬³¤¶ÈΪ3£¬Ö÷Âë),½ÌʦÐÕÃûTNAME(charÐÍ£¬³¤¶ÈΪ8,·Ç¿Õ)£¬ËùÔÚϵDEPTNO£¨charÐÍ£¬³¤¶ÈΪ2£© -- CREATE TABLE Teacher -- ( -- TNO CHAR(3)PRIMARY KEY, -- TNAME CHAR(8)NOT NULL, -- DEPTNO CHAR(2) -- ) £¨5£©´´½¨Ïµ±íDept£¬ÓÉÒÔÏÂÊôÐÔ×é³É£ºÏµºÅDEPTNO(charÐÍ£¬³¤¶ÈΪ2£¬Ö÷Âë)£¬ÏµÃûDNAME(charÐÍ£¬³¤¶ÈΪ20,·Ç¿Õ)¡£ CREATE TABLE Dept ( DEPTNO CHAR(2)PRIMARY KEY, DNAME CHAR(20) NOT NULL, 2
v1.0 ¿É±à¼¿ÉÐÞ¸Ä ) 2. Ð޸Ļù±¾±í ÔÚstudent±íÖмÓÈëÊôÐÔAGE£¨intÐÍ£©£» ALTER TABLE Student ADD AGE INT; 3. ɾ³ý»ù±¾±í ºó±ßËùÓвÙ×÷½áÊøºó£¬É¾³ýËù´´½¨µÄ»ù±¾±í£» DROP TABLE Student; DROP TABLE Course; DROP TABLE SC; DROP TABLE DEPT; DROP TABLE TEACHER; 3
v1.0 ¿É±à¼¿ÉÐÞ¸Ä £¨¶þ£©Ë÷Òý²Ù×÷ 1. ½¨Á¢Ë÷Òý £¨1£© ÔÚStudent±íÉϽ¨Á¢¹ØÓÚÊôÐÔSNOµÄΨһË÷Òý£» CREATE UNIQUE INDEX Stusno ON Student(Sno); £¨2£© ÔÚCourse±íÉϽ¨Á¢¹ØÓÚCNOµÄΨһË÷Òý£» create unique index Coucno ON Course(Cno); 2. ɾ³ýË÷Òý ɾ³ýÉÏÃæËù½¨Á¢µÄÁ½¸öË÷Òý£» -drop index --drop index ; £¨Èý£©ÊÓͼ²Ù×÷ 1. ½¨Á¢ÊÓͼ ÔÚ²åÈëÊý¾ÝµÄStudent»ù±¾±íÉÏΪ¼ÆËã»úϵѧÉúµÄ¼Ç¼½¨Á¢Ò»¸öÊÓͼCS_STUDENT¡£ CREATE VIEW CS_STUDENT AS SELECT SNO,SNAME,SEX,DEPTNO,AGE FROM Student 4