Shulou(Shulou.com)06/01 Report--

1. Description: create a database

CREATE DATABASE database-name

2. Description: delete the database

Drop database dbname

3. Description: backup sql server

-create a device for backing up 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 (create a new form A using the old form B)

Note: when copying table B to A, the complete field structure and index of table B will be copied to table A.

B:create table tab_new as select col1,col2... From tab_old definition only

Note: this method will only copy the field structure of Table B to Table A, but will not copy the indexes in Table B to Table A. This approach is flexible to specify which fields to copy while copying the original table structure, and the self-replicated table can also increase the field structure as needed. Conclusion:

Create table as select makes a complete copy of the data in the original table, but the index in the table structure is lost.

Create table like will only completely copy the table creation statements of the original table, but will not copy the data.

Neither method replicates the permission settings for the table when copying the table. For example, table B was originally set for permissions, but after replication, table A does not have permissions similar to table B.

5. Description: delete the new table

Drop table tabname

6. Description: add a column

Alter table tabname add column column_name type

Note: columns cannot be deleted after they are added. The data type cannot be changed after the column is added in DB2, and the only thing that can 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

Note: a data table can have only one primary key, so there is no primary key to delete a column.

8. Description: create an index: create [unique] index idxname on tabname (col … (.)

Delete index: drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

9. Description: create view: create view viewname as select statement

Delete View: drop view viewname

10. Description: a few simple basic sql statements

Select: select * from table1 where range

Insert: insert into table1 (field1,field2) values (value1,value2)

Deleting: delete from table1 where ran

Updating: update table1 set field1=value1 where ran

Look up: select * from table1 where field1 like'% value1%'-like's syntax is very exquisite, look up the information!

Sort: select * from table1 order by field1,field2 [desc]

Total: select count as totalcount from table1

Summation: select sum (field1) as sumvalue from table1

Average: select avg (field1) as avgvalue from table1

Maximum: select max (field1) as maxvalue from table1

Minimum: 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, such as TABLE1 and TABLE2, and eliminating any duplicate rows in the table. When ALL is used with UNION (that is, UNION ALL), duplicate lines are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

B: EXCEPT operator

The 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 lines are not eliminated.

C: INTERSECT operator

The INTERSECT operator derives a result table by including only the rows in both TABLE1 and TABLE2 and eliminating all duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate lines are not eliminated.

Note: several query result rows that use operands must be consistent.

12. Description: use external connections

A, left (outer) join:

Left outer join (left join): the result set includes the matching rows of the join table, as well as all rows of the left join table.

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B:right (outer) join:

Right outer join (right join): the result set includes both the matching join rows of 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 records in the two join tables.

12. Group: Group by:

A table, once the grouping is completed, only the group-related information can be obtained after the query.

Group related information: (statistics) criteria for count,sum,max,min,avg grouping)

When grouping in SQLServer: fields of type text,ntext,image cannot be used as grouping basis

Fields in selecte statistics functions cannot be put together with ordinary fields

13. Operate the database:

Detach database: sp_detach_db; attach database: sp_attach_db followed by indicates that attaching requires a full pathname

14. How to modify the name of a database:

Sp_renamedb 'old_name',' new_name'

II. Promotion

1. Description: copy table (only copy structure, source table name: a new table name: B) (available for Access)

Law 1: select * into b from a where 11 (for SQlServer only)

Method 2: select top 0 * into b from a

2. Description: copy table (copy data, source table name: a target table name: B) (available for Access)

Insert into b (a, b, c) select d from b

3. Description: copy of tables across databases (use absolute path for specific data) (Access is available)

Insert into b (a, b, c) select djiggy e from b in 'specific database' where condition

Example:.. from b in'"& Server.MapPath (". ") &"\ data.mdb "&" 'where.. "

4. Description: sub-query (table name 1VLA, table name 2RB)

Select a dagger bjorc from a where an IN (select d from b) or: select a recital b recital c from a where an IN (1m 2pm 3)

5. Description: displays the article, author and last reply time

Select a.title adddate from table where table.title=a.title a.username from table a b.adddate, (select max (title) adddate from table where table.title=a.title) b

6. Description: external join query (table name 1VR a, table name 2RB)

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 1pora)

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

8. Description: the use of between. Boundary values are included when between limits the scope of query data, while not between does not include

Select * from table1 where time between time1 and time2

Select a dint bpenc, from table1 where a not between numeric 1 and numeric 2

9. Description: how to use in

Select * from table1 where a [not] in ('value 1', value 2', 'value 4', 'value 6')

10. Description: two associated tables to delete information that has not been found in the secondary table in the primary table

Delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)

11. Description: joint check of the four tables:

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. Note: five minutes in advance of the schedule

SQL: select * from scheduling where datediff ('minute',f start time, getdate ()) > 5

13. Description: a sql statement completes database paging

Select top 10 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

Set @ sql='select top'+str (@ end-@start+1) +'+ from T where rid not in (select top'+str (@ str-1) + 'Rid from T where Rid >-1)'

Exec sp_executesql @ sql

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.