Mssql entry statement

The following statements are Mssql statements and cannot be used in access.

SQL classification:

DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE)
DML-data manipulation language (SELECT, DELETE, UPDATE, INSERT)
DCL-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)

First, we will briefly introduce the basic statements:

1. Description: Create a database

Create database database-name

2. Description: delete a database.

Drop database dbname

3. Description: Back up SQL server

--- Create a device for the backup data

USE master
EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat'

--- Start backup

Backup database pubs TO testBack

4. Description: Create a new table.

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)

Create a new table based on an existing table:

A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only

5. Description:

Delete a new table: drop table tabname

6. Description:

Add a column: Alter table tabname add column col type

Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.

7. Description:

Add primary key: Alter table tabname add primary key (col)

Note:

Delete primary key: Alter table tabname drop primary key (col)

8. Description:

Create an index: create [unique] index idxname on tabname (col ....)

Delete index: drop index idxname

Note: The index cannot be changed. To change the index, you must delete it and recreate it.

9. Description:

Create view: create view viewname as select statement

Delete view: drop view viewname

The following statements are Mssql statements and cannot be used in access.

SQL classification:

DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE)
DML-data manipulation language (SELECT, DELETE, UPDATE, INSERT)
DCL-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)

First, we will briefly introduce the basic statements:

1. Description: Create a database

Create database database-name

2. Description: delete a database.

Drop database dbname

3. Description: Back up SQL server

--- Create a device for the backup data

USE master
EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat'

--- Start backup

Backup database pubs TO testBack

4. Description: Create a new table.

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)

Create a new table based on an existing table:

A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only

5. Description:

Delete a new table: drop table tabname

6. Description:

Add a column: Alter table tabname add column col type

Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.

7. Description:

Add primary key: Alter table tabname add primary key (col)

Note:

Delete primary key: Alter table tabname drop primary key (col)

8. Description:

Create an index: create [unique] index idxname on tabname (col ....)

Delete index: drop index idxname

Note: The index cannot be changed. To change the index, you must delete it and recreate it.

9. Description:

Create view: create view viewname as select statement

Delete view: drop view viewname

6. Description: External join query (table name 1: Table a name 2: B)

Select a. a, a. B, a. c, B. c, B. d, B. f from a left out join B ON a. a = B. c

7. Description: Online View query (table name 1:)

Select * from (SELECT a, B, c FROM a) T where t. a> 1;

8. Description: between usage. When between restricts the Data Query range, it includes the boundary value. not between does not include

Select * from table1 where time between time1 and time2

Select a, B, c, from table1 where a not between value 1 and value 2

1 2 3 Next Page

[Content navigation]
Page 1st: Mssql entry statement	Page 2nd: Mssql entry statement
Page 3rd: Mssql entry statement