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