SQL statements (common) Create, delete, and modify table Structures

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, referArticle: Rename the table in the ACCESS database)

Sp_rename 'table name', 'new table name', 'object'

New constraint:

Alter table [Table name] add constraint name check ([constraint field] <= '2017-1-1 ')

Delete constraints:

Alter table [Table name] Drop constraint name

Create Default Value

Alter table [Table name] add constraint default name: '51windows. net' for [field name]

Delete default value

Alter table [Table name] Drop constraint default name

In addition, most of the preceding SQL syntaxes are the same under access.

check the item and verify it by yourself. It is confirmed that the following method is suitable for adding an automatic number field to the Access Database: Create Table data table name (ID counter constraint primarykey primary key) note that the second primary contains spaces. In addition, the keywords are case insensitive. another method I recently discovered is: SQL = "CREATE TABLE mytb (ID autoincrement () primary key, age INT)" sql2 = "CREATE TABLE testtb (ID autoincrement, age int, email char, primary key (ID) "in access, autoincrement is an automatically numbered field, and () is the initial value and step value. If no value is specified, the default is 1, 1, primary key specifies the primary key, the above example, the two methods can be specified
this article from the new technology Sky Original link: http://www.ntsky.com/tech/database/base/2007-05-10/55d961032c34cd4d.html