SqlServer series notes-- creation and maintenance of tables
-- create tables
Create table Employees
(
EmployeeID Int primary key
Name VarChar (10) NOT NULL
Sex Char (2) default 'male'
Birthdate Datetime NULL
Address Varchar (50) NULL
Phone Char (13) check (phone like '000-[039]')
Remark text
)
Create table wage
(
EmployeeID Int foreign key references Employees (EmployeeID)
Name VarChar (10) NOT NULL
Wage money NOT NULL
Putdate Datetime NOT NULL
)
-- add primary key constraint
Alter table Employees
Add constraint Employees_PK primary key (EmployeeID)
-- add foreign key constraint
Alter table wage
Add constraint wage_FK foreign key (EmployeeID) references Employees (EmployeeID)
-Delete constraint
Alter table wage
Drop constraint wage_FK
-- add default constraint
Alter table Employees
Add constraint a default ('unknown') for name
Constraint b default ('male') for sex
Constraint phone_check check (phone like'(\ d {3})\ d {9}')
-- delete column
Alter table Employees
Drop column Remark
-- add column
Alter table Employees
Add Remark text
Phone varchar (10)
Delete all the data of the table, the table is still there
Delete from table_name
DELETE FROM Person WHERE age > 20
-- remove the data restore identity
Truncate table table_name
-- add Insert
You can give the field a default value. If the default value of the Guid type primary key is set to newid (), the primary key will be generated automatically:
Insert into Person3 (Name,Age) values ('lili',38)
Insert into Person (Id,Name,Age) values (newid (), 'tom',30)
-- Update Update
Update a column: UPDATE T_Person Set Age=30
Update multiple columns: UPDATE T_Person Set Age=30,Name='tom'
Update part of the data: UPDATE T_Person Set Age=30 where Name='tom'
-Note that SQL equals to judge with a single = instead of = =
-- complex logic can also be used in Where to determine UPDATE T_Person Set Age=30 where Name='tom' or Age20 and Age=,
