Shulou(Shulou.com)06/01 Report--

-- 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=,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.