SQL frequently used to create TABLE constraints main foreign key Delete and change the statement

Create a database

1  UseMaster;2 GO
3 --Journaling Database
4 Create DatabaseDiarybase5  on
6 (
7Name=Diarybase_dat,--Logical Name
8FILENAME='C:\DiaryBase.log',
9Size=10MB,TenMaxSize=50MB, OneFileGrowth=5MB A )
 - Log  on
 - (
 theName=Diarybase_log, -FILENAME='C:\DiaryBase.mdf',
 -Size=10MB, -MaxSize=50MB, +FileGrowth=5MB - )
 + Go

View Code

CREATE TABLE (with constraints)

1 --drop table Users
2 Create TableUsers3 (
4Idint Identity(1,1)Primary Key,
5Usernamenvarchar( -)Unique  not NULL,--UNIQUE Constraint
6UserPasswordvarchar( -) not NULL,
7GenderChar Check(Gender='male' orGender='female'),--Check (here is a bool value)
8Ageint ,
9       Check(age>0  andAge< -)--CHECK Constraints
Ten )
 One Go
 A --drop table Diaryinfo
 - Create TableDiaryinfo - (
 theDidint Identity(1,1)Primary Key,
 -Useridint Foreign Key ReferencesUsers (ID),--FOREIGN Key
 -DInfonvarchar( -) not NULL,
 -Dcreatetimedatetime default(getdate()),--Default Constraints
 +Dtitlenvarchar( -) 
 - )
 + Go

View Code

Modify Table

1 --Adding and Removing columns
2 Alter TableUsersAddUseraddrnvarchar( -) 
3 
4 Alter TableUsersDrop columnuseraddr5 
6 Alter TableUsersAddUserphonenumbernvarchar( +) 
7         --Add DF_USERS_USERPHONEDF constraint
8  default('000000')
9 
Ten 
 One --to modify a constraint on a table
 A --Delete and add default constraints
 -  Alter TableUsersDrop constraintdf__users__userphone__20c1e124 - Alter TableUsersAdd constraintDf__users__userphonenumber the          Default('5555') forUserphonenumber - --Add delete Unique constraint (in MSSM in index Group)
 - Alter TableUsersDrop constraintuq__users__f3dbc572276edeb3 - Alter TableUsersAdd constraintUq_users_usernameUnique(UserName) + 
 - --adding and removing primary key constraints
 + Alter TableUsersDrop constraintpk__users__3213e83f24927208 A Alter TableUsersAdd constraintpk_users_idPrimary Key(ID) at 
 - --Adding and removing foreign key constraints
 - Alter TableDiaryinfoDrop constraintfk__diaryinfo__useri__30f848ed - Alter TableDiaryinfoAdd constraintFk_diaryinfo_userid -             Foreign Key(UserId)ReferencesUsers (ID) -             
 in --Adding and removing check constraints
 - Alter TableUsersDrop constraintck__users__age__2a4b4b5e to Alter TableUsersAdd constraintCk_users_ageCheck(age< -  andAge>0)

View Code

Add multiple constraints at the same time

Alter TableEmployeesAdd
constraintFk_employees_departmentForeign Key(Empdepid)ReferencesDepartment (depid),constraintCk_employees_empageCheck(Empage>=0  andEmpage<= -),
constraintCk_employees_empgenderCheck(Empgender='male' orEmpgender='female')

View Code

Inquire

Select
Student number =tsid,
Tsname as student name,
Tsgender Sex,
Marriage no = ' no '--the direct assignment of ' no ' when querying
From Tblstudent
--where sex = ' male ' Here sex is invalid He's just used to show it.

--distinct keyword, for results that have been queried and then remove duplicates
SELECT DISTINCT * from Tblstudent

--If the top is not a number, but an expression be sure to use () to enclose the expression.
Select Top (2*2) * from Tblscore ORDER BY Tmath Desc

Select top percent * from Tblscore ORDER BY Tmath Desc

--1. Aggregate functions do not count null values

--for in or or queries, if the criteria in the query are consecutive numbers, it is best to use >= <= or Between...and do not use or or in. Increase efficiency
SELECT * from tblstudent where tsname like ' Zhang [a-z0-9] sister '

--The wildcard is not considered a wildcard if it is escaped in the []. "" is the system default transfer character
SELECT * from tblstudent where tsname like '%[%]% '

Find

When you want to search for names with [or] characters

SELECT * from tblstudent where tsname like '%[]]% '

We're not going to find this, we need to use the custom transfer character ESCAPE

SELECT * from tblstudent where tsname like '%/[% ' ESCAPE '/'

SQL frequently used to create TABLE constraints main foreign key Delete and change the statement