A Select collection of action tables and table structures in SQL Server

Source: Internet
Author: User
Tags include min range create database backup

1. Add Field


CHAR (200)

2. Delete fields



3. Modify the field type


column_name New_data_type

4.sp_rename renamed

EXEC sp_rename ' [dbo]. [Table_1]. [FiledName1] ', ' filedName2 ', ' COLUMN '

Changes the name of a user-created object (such as a table, column, or user-defined data type) in the current database.


sp_rename [@objname =] ' object_name ', [@newname =] ' new_name ', classified information; [, [@objtype =] ' object_type ']

such as: EXEC sp_rename ' newname ', ' Partstock '

5.sp_help shows some basic information about the table

Sp_help ' object_name '

such as: EXEC sp_help ' Partstock '

6. Determine whether a field partvelocity exists in a table Partstock


Exists (SELECT * from syscolumns where

id=object_id (' Partstock ') and Name= ' partvelocity ')


' Partvelocity exists '

else print ' partvelocity not


Another law:

To determine the existence of a table:

Select COUNT (*) from sysobjects where type= ' U '

and name= ' Your table name '

To determine the existence of a field:

Select COUNT (*) from syscolumns


ID = (SELECT id from sysobjects where type= ' u ' and name= ' your table name ')

and name = ' field name you want to judge '

A small example

--Suppose the table name you want to work with is:


--Determine if there is a primary key in the table to add the column

if exists (select 1 from sysobjects where

parent_obj=object_id (' TB ') and xtype= ' PK ')



' The table already has a primary key and the column can only be added as a normal column '

--Add a column of type int with a default value of 0

ALTER TABLE TB add column name int default





No primary key in print ' table, add primary key column '

--Add a column of type int with a default value of 0


Table TB Add column name int primary key default 0


7. Random reading of several records

Access syntax: SELECT top * FROM table name ORDER BY

Rnd (ID)

SQL server:select Top n * FROM table name ORDER by NEWID ()

MySQL select * FROM table name ORDER by rand () Limit n

8. Note: Schedule five minutes advance reminder


SELECT * FROM Schedule where DATEDIFF (minute,f start time, GETDATE ()) >5

9. First 10 records

Select Top * form. Table1 where scope

10. To include all rows in TableA but not in TableB and TableC and to eliminate all duplicate rows and derive a result table

(Select a

From TableA) except (select A to TableB) except (select a from TableC)

11. Note: Randomly remove 10 data

Select Top * FROM tablename ORDER by NEWID ()

12. List all the table names in the database

Select name from sysobjects where type=u

13. List all the field names in the table

Select name from syscolumns where

ID=OBJECT_ID (tablename)

14. Description: Listing type, Vender, PCs fields, arranged in type fields, case can easily implement multiple selections, similar to select

In the case.

Select Type,sum (Case vender when A then PCs else 0

End), sum (case vender when C then PCs else 0 "), sum (case vender when B then PCs

else 0 end) from tablename GROUP By type

15. Note: Initialize table table1


TABLE table1

16. Note: Several advanced query operators

A:union operator

The UNION operator combines the other two result tables, such as

TABLE1 and TABLE2) and derive a result table by eliminating any duplicate rows in the table. When all is used with union (that is, union

All), do not eliminate duplicate rows. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

B:except operator

The EXCEPT operator is passed including all in TABLE1 but not in TABLE2

And all duplicate rows are eliminated and a result table is derived. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.

C:intersect operator

The INTERSECT operator only includes TABLE1 and TABLE2

To derive a result table by eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT

All), do not eliminate duplicate rows.

Note: Several query result rows that use an operator must be consistent.

17. Note: Online view query (table name 1:a)

SELECT * FROM (select A,b,c from a) T where

T.A > 1;

18. Note: The use of between, between limit the range of query data include the boundary value, not between does not include


Table1 where time between time1 and time2

Select A,b,c, from table1

Where a not between value 1 and value 2

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

19. Note: How to use in

SELECT * FROM table1 where a [does] in

(' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

20. Description: Two related tables, delete the information in the primary table that has not been in the secondary table

Delete from table1 where NOT exists (

SELECT * FROM Table2 where table1.field1=table2.field1


21. Note: Copy table (copy only structure, source table name: A new table name: B) (Access available)

Law I: SELECT * into B from a

where 1<>1

Method Two: Select top 0 * into B from a

22. Note: Copy table (copy data, source table name: A target table name: B) (Access available)

Insert into B (A, B, c)

Select d,e,f from B;

23. Note: Copies of tables across databases (use absolute paths for specific data) (Access is available)

Insert into B (A, B, c)

Select d,e,f from B in ' specific database ' where condition

Example:.. From B in

"&server.mappath (". ") & "\data.mdb" & "where."

24. Create a Database


25. Note: Delete database

Drop Database dbname

26. Note: Back up SQL Server


Device of Backup data

Use master

EXEC sp_addumpdevice disk, Testback,


---start Backup

BACKUP DATABASE pubs to Testback

27. Note: Create a new table

CREATE TABLE TabName (col1 type1 [NOT NULL] [primary

Key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:

A:create Table Tab_new

Like Tab_old (Create a new table with the old table)

B:create table tab_new as Select Col1,col2 ...

From Tab_old definition only

28. Description:

Delete new table: Drop table TabName

29. Description:

Add a column: Alter table tabname Add column col


Note: The column will not be deleted after it has increased. The data type can not be changed when the column in the DB2 is added, and the only change is the length of the varchar type.

30. Description:

Add primary key: Alter table TabName Add primary key (COL)


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

31. Description:

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

Deleting indexes: Drop INDEX Idxname

Note: The index is not to be changed and you want to change the rebuild must be deleted.

32. Description:

Creating a View: Create VIEW viewname AS SELECT statement

Delete view: Drop view


33. Description: A few simple basic SQL statements

Selection: SELECT * FROM table1 where

Inserting: INSERT INTO

Table1 (field1,field2) VALUES (value1,value2)

Delete: Delete from table1 where


Update: UPDATE table1 set field1=value1 where scope

Find: SELECT * FROM table1

where field1 like '%value1% '---the syntax of like is very exquisite, check information!

Sort: SELECT * FROM table1


Total: SELECT Count * as TotalCount from


Sum: Select SUM (field1) as Sumvalue from table1

Average: Select

AVG (field1) as Avgvalue from table1

Max: Select Max (field1) as MaxValue from


Min: select min (field1) as MinValue from table1

Related Article

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.