Common SQL statements-create a table, add a primary key, and add a column common SQL statements-create a table, add a primary key, and add a column

Source: Internet
Author: User
Common SQL statements-create a table, add a primary key, and add a column

Create a table:
Create table [table name]
(
[Automatic number field] int IDENTITY (1, 1) PRIMARY KEY
,
[Field 1] nVarChar (50) default \ 'default value \ 'null,
[Field 2] ntext null,
[Field 3]
Datetime,
[Field 4] money null,
[Field 5] int default 0,
[Field 6] Decimal (12, 4)
Default 0,
[Field 7] image null,
)

Delete table:
Drop table [table name]

Insert data:
Insert into [Table name] (Field 1, Field 2) VALUES (100, \ '51windows. NET \')

Delete data:
Delete from [Table name] WHERE [field name]> 100

Update Data:
UPDATE [Table name] SET [Field 1] = 200, [Field 2] = \ '51windows. NET \ 'WHERE [Field 3] =
\ 'Haiwa \'

New field:
Alter table [TABLE name] ADD [field name] NVARCHAR (50) NULL

Delete field:
Alter table [TABLE name] drop column [field name]

Modify Field:
Alter table [TABLE name] alter column [field name] NVARCHAR (50) NULL

Rename a table: (Access to rename a table, refer to the article: rename a table in Access database)
Sp_rename \ 'table name \ ', \ 'new table name \',
\ 'Object \'

New constraint:
Alter table [TABLE name] add constraint name CHECK ([CONSTRAINT field] <=
\ '2014-1-1 \')

Delete constraints:
Alter table [TABLE name] drop constraint name

Create Default Value
Alter table [TABLE name] add constraint default name DEFAULT \ '51windows. NET \'
[Field name]

Delete default value
Alter table [TABLE name] drop constraint default name

Delete logs in SQL Server to reduce the size of database files
Dump transaction database name with no_log
Backup
Log Database Name with no_log
Dbcc shrinkdatabase (database name)
Exec sp_dboption \ 'database name \',
\ 'Autoshrink \ ', \ 'true \'

\\\' Add a common field function
Sub
AddColumn (TableName, ColumnName, ColumnType)
Conn. Execute (\ "Alter Table
\ "& TableName & \" Add \ "& ColumnName &\"
\ "& ColumnType &\"\")
End Sub

\\\ 'Change the common function of a field
Sub
ModColumn (TableName, ColumnName, ColumnType)
Conn. Execute (\ "Alter Table
\ "& TableName & \" Alter Column \ "& ColumnName &\"
\ "& ColumnType &\"\")
End Sub

\\\' Check whether the table exists

SQL = \ "select count (*) as dida from sysobjects where id =
Object_id (N \ '[owner]. [Table name] \') and OBJECTPROPERTY (id, N \ 'isusertable \ ') = 1 \"

Set rsw.conn.exe cute (SQL)

Response. write rs (\ "dida \") \ 'returns a value. 0 indicates none, and 1 indicates existence.

Determine the existence of a table:
Select * from sysobjects where id =
Object_id (n \ '[DBO]. [tablename] \') and objectproperty (ID, n \ 'isusertable \ ') =
1

Structure of a table
Select * From syscolumns where id = object_id (n \ '[DBO]. [your table name] \')
And objectproperty (ID, n \ 'isusertable \ ') = 1

Create a table:
Create Table [Table name]
(
[Automatic number field] int identity (1, 1) primary key
,
[Field 1] nvarchar (50) Default \ 'default value \ 'null,
[Field 2] ntext null,
[Field 3]
Datetime,
[Field 4] Money null,
[Field 5] int default 0,
[Field 6] decimal (12, 4)
Default 0,
[Field 7] image null,
)

Delete table:
Drop table [Table name]

Insert data:
Insert into [Table name] (Field 1, Field 2) values (100, \ '51windows. Net \')

Delete data:
Delete from [Table name] Where [field name]> 100

Update Data:
Update [Table name] Set [Field 1] = 200, [Field 2] = \ '51windows. Net \ 'Where [Field 3] =
\ 'Haiwa \'

New field:
Alter table [Table name] add [field name] nvarchar (50) null

Delete field:
Alter table [Table name] Drop column [field name]

Modify Field:
Alter table [Table name] alter column [field name] nvarchar (50) null

Rename a table: (access to rename a table, refer to the article: rename a table in Access database)
Sp_rename \ 'table name \ ', \ 'new table name \',
\ 'Object \'

New constraint:
Alter table [Table name] add constraint name check ([constraint field] <=
\ '2014-1-1 \')

Delete constraints:
Alter table [Table name] Drop constraint name

Create Default Value
Alter table [Table name] add constraint default name Default \ '51windows. Net \'
[Field name]

Delete default value
Alter table [Table name] Drop constraint default name

Delete logs in SQL Server to reduce the size of database files
Dump transaction database name with no_log
Backup
Log Database Name with no_log
Dbcc shrinkdatabase (database name)
Exec sp_dboption \ 'database name \',
\ 'Autoshrink \ ', \ 'true \'

\\\' Add a common field function
Sub
AddColumn (TableName, ColumnName, ColumnType)
Conn. Execute (\ "Alter Table
\ "& TableName & \" Add \ "& ColumnName &\"
\ "& ColumnType &\"\")
End Sub

\\\ 'Change the common function of a field
Sub
ModColumn (TableName, ColumnName, ColumnType)
Conn. Execute (\ "Alter Table
\ "& TableName & \" Alter Column \ "& ColumnName &\"
\ "& ColumnType &\"\")
End Sub

\\\' Check whether the table exists

SQL = \ "select count (*) as dida from sysobjects where id =
Object_id (N \ '[owner]. [Table name] \') and OBJECTPROPERTY (id, N \ 'isusertable \ ') = 1 \"

Set rsw.conn.exe cute (SQL)

Response. write rs (\ "dida \") \ 'returns a value. 0 indicates none, and 1 indicates existence.

Determine the existence of a table:
Select * from sysobjects where id =
Object_id (N \ '[dbo]. [tablename] \') and OBJECTPROPERTY (id, N \ 'isusertable \ ') =
1

Structure of a table
Select * from syscolumns where id = object_id (N \ '[dbo]. [your table name] \')
And OBJECTPROPERTY (id, N \ 'isusertable \ ') = 1

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.