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

I. Foundation

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 table using the old table)

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

5. Description: delete the 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. 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 (col)

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 ran

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 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 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.

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

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 breco c from a where an IN (1m 2pr 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 1pura

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 ('worth 1', 'worth 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 schedule 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

14. Description: the first 10 records

Select top 10 form table1 where range

15. Description: select all the information corresponding to the largest record of an in each set of data with the same b value (similar usage can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.).

Select a dint bjorn c from tablename ta where a = (select max (a) from tablename tb where tb.b=ta.b)

Description: derives a result table by including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows

(select a from tableA except (select a from tableB) except (select a from tableC))

17. Description: 10 pieces of data are randomly selected

Select top 10 * from tablename order by newid ()

18. Description: randomly select records

Select newid ()

19. Description: delete duplicate records

Delete from tablename where id not in (select max (id) from tablename group by col1,col2,...)

Description: list all the table names in the database

Select name from sysobjects where type='U'

21. Description: list all the items in the table

Select name from syscolumns where id=object_id ('TableName')

22, description: type, vender, pcs fields are listed and arranged in type fields. Case can easily achieve multiple choices, similar to case in select.

Select type,sum (case vender when 'A'then pcs else 0 end), sum (case vender when 'C'then pcs else 0 end), sum (case vender when 'B'then pcs else 0 end) FROM tablename group by type

Display the results:

Type vender pcs

Computer A1

Computer A1

CD B 2

CD A 2

Mobile phone B3

Mobile phone C 3

23. Description: initialization table table1

TRUNCATE TABLE table1

24. Description: select records from 10 to 15

Select top 5 from (select top 15 from table order by id asc) table_ alias order by id desc

3. Technique 1. 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1

"where 1" means to select all "where 1" and none of them.

Such as:

The use of if @ strWhere! = 'br/ > 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 2, 1, 1, 1, 1, 2, 1, 1, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1

"where 1" means to select all "where 1" and none of them.

Such as:

If @ strWhere! ='

Br/ > set @ strSQL = 'select count (*) as Total from [' + @ tblName +'] where'+ @ strWhere

Begin

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +'] 'br/ > else

Begin

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +']'

We can write it directly.

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +'] where 1 stable 1'+ @ strWhere

2. Shrink the database

-- rebuild the index

DBCC REINDEX

DBCC INDEXDEFRAG

-shrinking data and logs

DBCC SHRINKDB

DBCC SHRINKFILE

3. Compress the database

Dbcc shrinkdatabase (dbname)

4. Transfer the database to the new user with existing user rights

Exec sp_change_users_login 'update_one','newname','oldname'

Go

5. Check the backup set

RESTORE VERIFYONLY from disk='E:\ dvbbs.bak'

6. Repair the database

Alter DATABASE [dvbbs] SET SINGLE_USER

GO

DBCC CHECKDB ('dvbbs',repair_allow_data_loss) WITH TABLOCK

GO

Alter DATABASE [dvbbs] SET MULTI_USER

GO

7. Log clear SET NOCOUNT ON

DECLARE @ LogicalFileName sysname,br/ > SET NOCOUNT ON

DECLARE @ LogicalFileName sysname

@ NewSize INT

USE tablename-- the name of the database to be operated is Select @ LogicalFileName = 'tablename_log',-- log file name br/ > Select @ LogicalFileName =' tablename_log',-- log file name

@ NewSize = 1-the size of the log file you want to set (M)

-- Setup / initializeDECLARE @ OriginalSize intbr/ > DECLARE @ OriginalSize int

Where name = @ LogicalFileNamebr/ > FROM sysfiles

Where name = @ LogicalFileName

Br/ > CONVERT (VARCHAR (30), @ OriginalSize) +'8K pages or'+

Where name = @ LogicalFileNamebr/ > FROM sysfiles

Where name = @ LogicalFileName

(DummyColumn char (8000) not null)

DECLARE @ Counter INT, @ StartTime DATETIME,br/ > @ StartTime DATETIME

Br/ > Select @ StartTime = GETDATE ()

DBCC SHRINKFILE (@ LogicalFileName, @ NewSize) EXEC (@ TruncLog) br/ > EXEC (@ TruncLog)

Br/ > WHILE @ MaxMinutes > DATEDIFF (mi, @ StartTime, GETDATE ())-time has not expired

Br/ > AND (@ OriginalSize * 8 / 1024) > @ NewSize

Br/ > Select @ Counter = 0

Insert DummyTrans VALUES ('Fill Log')

Delete DummyTrans

Select @ Counter = @ Counter + 1br/ > BEGIN-- update

Insert DummyTrans VALUES ('Fill Log')

Delete DummyTrans

Select @ Counter = @ Counter + 1

Br/ > EXEC (@ TruncLog)

Select 'Final Size of' + db_name () + 'LOG is' +

CONVERT (VARCHAR (30), size) +'8K pages or'+ CONVERT (VARCHAR (30), (size*8/1024)) + 'MB'

FROM sysfiles

Where name = @ LogicalFileNamebr/ > CONVERT (VARCHAR (30), (size*8/1024)) + 'MB'

FROM sysfiles

Where name = @ LogicalFileName

SET NOCOUNT OFF

8. Description: change a table

Exec sp_changeobjectowner 'tablename','dbo'

9. Store all tables for changes

Create PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwner as NVARCHAR, br/ > @ OldOwner as NVARCHAR

AS

DECLARE @ Name as NVARCHAR DECLARE @ Owner as NVARCHAR br/ > DECLARE @ Owner as NVARCHAR

DECLARE curObject CURSOR FORselect 'Name' = name

'Owner' = user_name (uid)

From sysobjects

Where user_name (uid) = @ OldOwnerbr/ > select 'Name' = name

'Owner' = user_name (uid)

From sysobjects

Where user_name (uid) = @ OldOwner

OPEN curObjectFETCH NEXT FROM curObject INTO @ Name, @ Ownerbr/ > FETCH NEXT FROM curObject INTO @ Name, @ Owner

If @ Owner=@OldOwnerbr/ > BEGIN

If @ Owner=@OldOwner

Br/ > set @ OwnerName = @ OldOwner +'.'+ rtrim (@ Name)

End

-- select @ name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @ Name, @ Owner

END

Close curObject

Deallocate curObject

GO

10. Write data declare @ I intbr/ > declare @ I int directly in SQL SERVER

Br/ > while @ i insert into test (userid) values (@ I)

End

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.