SQL Basic Operations

First, the basic

1 , Description: Create a database CREATE DATABASE database-name

2 , Description: Delete database drop dbname

3. Description: Backup SQL Server---Create backup data for device use master EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat ' ---Start backing up backup DATABASE pubs to Testback

4. Description: Creating 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 new table with old table)

B : Create TABLE tab_new as Select Col1,col2 ... from tab_old definition only

5. Description: Delete 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. DB2 the column plus the data type can not 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)

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

8. Description: Create INDEX :[unique] index idxname on tabname (col ...)

Delete indexes:DROP INDEX Idxname Note: The index is immutable and you need to remove the rebuild if you want to change it.

9 , Description: Creating a View: Create View viewname AS SELECT statement

Delete View : Drop View viewname

Ten , Description: A few simple basic SQL statements

Select:select * FROM table1 where range

insert: INSERT INTOtable1 (field1,field2) VALUES (value1,value2)

Delete:delete from table1 where range

updated : Update table1 set field1=value1 where range

Find : SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, check the information!

sort : SELECT * FROM table1 ORDER by FIELD1,FIELD2 [DESC]

Total : Select count as TotalCount from table1

sum : Select SUM (field1) as Sumvalue from table1

average : Select AVG (field1) as Avgvalue from table1

Maximum : select Max (field1) as MaxValue from table1

min : select min (field1) as MinValue from table1

11. Description: Several advanced query operation words

a:union operator The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

The b:except operator EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.

The INTERSECT operator of the c:intersect operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated. Note: Several query result rows that use an operation word must be consistent.

A , Description: Using outer joins

Aouter JOIN: Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table. Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

b:right (outer) Join: right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table. C:full/cross (outer) Join: Full outer join: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

A , grouping: GROUP by: A table, once the group is complete, the query can only get information about the group.

Group-related information:(statistical information) Count,sum,max,min,avg grouping criteria)

When grouping in SQL Server: You cannot use fields of type Text,ntext,image as a field in the selecte statistic function, and cannot be combined with normal fields;

13, the database operation: separate database : sp_detach_db; additional databases :sp_attach_db followed by indicates that the append requires a full pathname

How to modify the name of the database: sp_renamedb ' old_name ', ' new_name '

Second, promote

1. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available) method one:select * into B from a where 1<>1 (SQL Server only) Law II: Select top 0 * into B from a 2, Description: 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;

3. Description: Copy of table across databases (exact data using absolute path) (Access 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."

4, Description: Sub-query (table name 1:a table name 2:b) Select A,b,c from a where a in (select D from B) or: Select A,b,c from a where a )

5, Description: Display the article, the author and the last reply time select a.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

6, Description: External connection query (table name 1:a table 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:a) SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between usage, between limit the query data range includes the boundary value, not between does not include select * FROM table1 where time between time1 and Time2 sel ECT A,b,c, from table1 where a is not between value 1 and value 2

9, Description: In the method of use select * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have the information delete from table1 where is NOT EXISTS (SELECT * from table2 where table1.field1=table 2.FIELD1)

11, Description: Four table joint search problem: select * from a left INNER join B on a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D Where .....

12, Description: Schedule five minutes before the reminder Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

- , Description: a SQL statements take care of database paging select top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field Order by a. Sort field specific implementation: about database paging:

declare @start int, @end int

@sql nvarchar (600)

Set @sql = ' Select Top ' +str (@[email protected]+1) + ' +from t where rid not in (select Top ' +str (@str-1) + ' RIDs from T where Rid> ;-1) '

EXEC sp_executesql @sql

Note: in Top can not be directly followed by a variable, so in practical applications only such a special treatment. the Rid is an identity column, which is beneficial if there are specific fields after top. Because this avoids the top field if it is a logical index, the result of the query is inconsistent in the actual table ( the data in the logical index is likely to be inconsistent with the data table, and the index is queried first if it is in the index) )

14. Description: Top 10 records select top * form table1 where range

15, Description: Select in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, and so on.) Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table (select a from TableA) except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data select Top * FROM tablename ORDER by newid ()

18, Description: Random selection record select NEWID ()

19. Description: Delete duplicate record 1),Delete from tablename where ID not in (SELECT Max (ID) from tablename GROUP by Col1,col2,...) 2), select DISTINCT * to temp from tablename Delete from tablename insert to tablename SELECT * FROM temp Evaluation: This operation involves a large amount of data movement, this practice is not suitable for large capacity but data operation 3), such as: import data in an external table, for some reason only imported part of the first time, but it is difficult to determine the exact location, so that only the next time all imports, so that will produce a lot of duplicate fields, How to delete a repeating field

ALTER TABLE TableName-add a self-increment column add Column_b int identity (max) Delete from TableName where Column_b not in (select Max (c Olumn_b) from TableName GROUP by Column1,column2,...) ALTER TABLE tablename DROP column Column_b

20. Description: List all table names in the database select name from sysobjects where type= ' u '//u represents user

21. Description: List all column names in the table select name from syscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to case in select. Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then the PCs else 0 end), sum (case vend Er when ' B ' and PCs else 0 end) from tablename Group By type display result: type vender pcs pc a 1 pc a 1 disc B 2 disc a 2 lot Machine B 3 Mobile phone C 3

23. Description: Initialize table table1

TRUNCATE TABLE table1

24. Description: Select records from 10 to 15 select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

SQL Basic Operations