SQL Server's add and revise operations

-------Adding constraints, increasing deletions

1  UseStudentDB22 Go3 --------Create a student table---------4 Create TableStudentinfo (5 --StudentID int PRIMARY key NOT NULL identity,---set as primary key and self-growing6StudentIDint  not NULL Identity(1,1),7Studentnumvarchar( -) not NULL,8Studentnamenvarchar( -) not NULL,9Studentmobileint NULL,TenClassnoint NULL One ) A --------Add Constraints---------- - --PRIMARY KEY constraint - Alter TableStudentinfo the     Add constraintPk_studentinfo_studentidPrimary Key(StudentID) - Go - --UNIQUE Constraint - Alter TableStudentinfo +     Add constraintUq_studentinfo_studentnumUnique(Studentnum) - Go + --Default Constraints A Alter TableStudentinfo at     Add constraintDf_studentinfo_studentmobiledefault 'number is unknown'  forStudentmobile - Go - --CHECK Constraints - Alter TableStudentinfo - --Check (len (studentmobile) =11): Checks if the phone number is 11 bits long - --expression in parentheses after check can be a compound logical expression consisting of multiple simple logical expressions with and or joins in     Add constraintCk_studentinfo_studentmobileCheck(Len(Studentmobile)= One) - Go to --====== to avoid the hassle of repeating writing, you can add constraints using the following methods + Alter TableStudentinfo - Add constraintDf_studentinfo_studentmobiledefault 'number is unknown'  forStudentmobile, the     constraintCk_studentinfo_studentmobileCheck(Len(Studentmobile)= One) * Go $ --Delete ConstraintPanax Notoginseng Alter TableStudentinfo -     Drop constraintDf_studentinfo_studentmobile--constraint keyword is optional, can write not write the Go + ------------Modify the data table----------- A --Adding Columns the Alter TableStudentinfo + AddRemark1varchar( -)NULL, -Remark2varchar( -)NULL $ Go $ --Delete Column - Alter TableStudentinfo - Drop columnRamark1 the Go - --------Modifying ColumnsWuyi Alter TableClassno the --data type, length, and nullability have been modified - Alter columnClassIdvarchar( -) not NULL Wu Go - --Modify column names About execsp_rename'Studentinfo.classno','Classnum' $ Go - --------------Delete a data table-------------------- - --before you delete it, you have to determine if the data table exists, or an error occurs. - --Judgment Method 1 A if exists(Select *  fromSys.sysobjectswhere [name]='Studentinfo') + Drop TableStudentinfo the Go - --Judgment Method 2 $ if object_id('Studentinfo') is  not NULL the Drop TableStudentinfo the Go

View Code

-------FOREIGN KEY constraints

1  UseStudentDB22 Go3 --Create a table4 Create TableScore (5StudentIDint  not NULL Identity(1,1),6Scoreint 7 )8 --Add a FOREIGN KEY constraint9 Alter TablescoreTen     Add constraintFk_score_studentinfo_stuidForeign Key(StudentID)Referencesstudentinfo (StudentID) One Go

--------Insert, UPDATE, delete

Use studentdb2go--all columns to insert data, provide data values for all columns, and list the values in the order of the columns in the table, so you do not have to specify the column name insert into studentinfo values (1, ' 000001 ', ' big strong ', 124565689,10086, ' n ', ' 001 '), go--provides all the columns in order, and gives all values accordingly (recommended notation) insert into studentinfo (Studentid,studentnum, STUDENTNAME,CLASSNO,REMARK1,REMARK2) VALUES (1, ' 000001 ', ' big strong ', 124565689,10086, ' n ', ' 001 '); go--You can also insert data without following the order in the table ( But all columns are provided) insert into Studentinfo (STUDENTID,CLASSNO,STUDENTNUM,REMARK1,STUDENTNAME,REMARK2) VALUES (1, 2, ' 000002 ', ' 02 ', ' Two dogs ', ' 003 '); go--insert partial column values (the number of inserted values is less than the number of columns), you must declare which column inserts into Studentinfo (Studentid,classno,studentnum) VALUES ( 3,03, ' 000003 '); Go---------------Update data----------------Simple UPDATE statement update studentinfo set remark1=000; go--UPDATE statement with where condition update studentinfo set studentnum=0101,studentname= ' King II leper ' where classno=222; Go-----------Delete the data---------------delete the entire table delete from studentinfo;go--delete a row delete from Studentinfo where studentid=001; Go

　　

SQL Server's add and revise operations