SQL database SQL statements

Not written for a long timeArticleNow,

Today, I practiced some SQL statements to operate table indexes and columns to make a record.

The format for creating an index is different from that for creating a constraint. Do not confuse it.

 

Create a table

Create Table plane
(
Id int identity (1, 1) not null,
Planeid int not null,
Persion nvarchar (50) not null,
Addtime datetime default getdate () not null
Constraint pk_plane primary key clustered (id asc) -- ID is the primary key and is used to write clustered index SQL. Other databases have not tried
)

Create a non-clustered index nonclustered clustered index clustered

Create nonclustered index idx_plane on plane (planeid)

 

Delete Index

Drop index idx_plane on Plane

 

Add Column

Alter table plane add num int not null default 0

 

Modify the column type. If the column has constraints or indexes, delete the index or constraints first.

Alter table plane alter column num varchar (100)

 

Add default Constraints

Alter table plane add constraint df_planename default ('fd ') for planename

 

Delete Constraints

Alter table plane drop df_planename

 

When I get off work, I copy others' records to make a record.

-- Add description information for the table
Execute sp_addextendedproperty n 'Ms _ description', 'personnel info table ', N 'user', N 'dbo', N 'table', N 'table', null, null

-- Add description information for field A1
Execute sp_addextendedproperty n 'Ms _ description', 'name', N 'user', N 'dbo', N 'table', N 'table', N 'column ', n'field'

-- Add description information for field A2
Execute sp_addextendedproperty n 'Ms _ description', 'gender', N 'user', N 'dbo', N 'table', N 'table', N 'column ', n'field'

-- Update the description attribute of column A1 in the table:
Exec sp_updateextendedproperty 'Ms _ description', 'field 1', 'user', DBO, 'table', 'table', 'column', 'field'

-- Delete the description attribute of column A1 in the table:
Exec sp_dropextendedproperty 'Ms _ description', 'user', DBO, 'table', 'table', 'column ', 'field'