Worthy collection of Microsoft SQL Server SQL statement

Worthy collection of Microsoft SQL Server SQL statement 2007-11-07 16:04:00 Tags: the SQL basic skills operating statement [pushed to the technology circle] SQL statement previously written, it is easy to forget some special usage, I hereby order the operation of the SQL statement. First, the basic instructions: Create a database CREATE DATABASE database-name Description: delete the database drop database dbname Description: backup-SQL Server — create backup data device use master the EXEC sp_addumpdevice ‘disk’, ‘testBack’ ‘c :/ mssql7backup/MyNwind_1.dat’ — start the backup BACKUP DATABASE pubs TO testBack Description: Create a new table create table tabname ([col1 type1 Not null] [PRIMARY KEY], col2 type2 [NOT NULL]. ) the basis of the table to create a new table: A: create table tab_new like tab_old (using the old table to create a new table): the create table tab_new as select col1, col2 … from tab_old Definition only 5 Description: delete the new table drop table tabname Description: add a column Alter table tabname add column col type Note: The column will be increased can not be deleted. List with DB2 data types can not be changed, the only change is to increase the length of varchar type. Description: Add the primary key: Alter table tabname add primary key (col) Description: delete the primary key: Alter table tabname drop primary key (col) 8, Description: create an index: the create [UNIQUE] index idxname on tabname (col ….) delete the index: the drop the index idxname Note: The index can not be changed, the changes must be deleted to re-build. Description: create a view: create view viewname as select statement to delete the view: the DROP VIEW viewname 10, Description: a few simple basic sql statement select: select * from table1 where range insert: insert into table1 (field1, field2) values (value1, value2) Delete: delete from table1 where range update: update table1 set field1 = value1 where range search: select * from table1 where field1 like ‘% value1%’ — like the syntax is very subtle, information search! Sort by: 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 a from table1 Max: SELECT max (field1) as maxvalue from table1 Minimum: select min (field1) as minvalue from table1 11, Description: several advanced query operators word A: UNION operator UNION operator through a combination of the other two tables (for example, TABLE1 and table2) and elimination table any duplicate lines and derive a result table. When used with UNION ALL (UNION ALL), duplicates are not eliminated. Both cases, the derived table for each row is not from TABLE1 from TABLE2. B: EXCEPT operator EXCEPT operator by including all rows in TABLE1 but not in table2 and remove all duplicate lines and derive a result table. When used in conjunction with the EXCEPT ALL (EXCEPT ALL), duplicates are not eliminated. C: INTERSECT operator INTERSECT operator by including only TABLE1 and TABLE2 has rows and remove all duplicate lines and derive a result table. ALL with INTERSECT (INTERSECT ALL), duplicates are not eliminated. Note: The use of the word of the operation of several rows of query results must be consistent. 12 Description: outer join A left outer join: left outer join (left): The result set including a matching line of the connection table, including the table all the rows in the left join. SQL: select aa, ab, ac, bc, bd, bf from a LEFT OUT JOIN b ON aa = bc B: right outer join: right outer join (right connection): The result set includes both the connection table matches the link line, including the right to connect all the lines. C: full outer join: full outer join: includes not only the symbolic link matching rows of the table, all records in the table also includes two connections. To enhance 1: Copy table (just copy the structure of the source table name: a new table: b) (Access available) method: select * into b from a where 1 <> 1 Act 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; Description: cross-database copy of the table (specific data using an absolute path) (Access available) insert into b (a, b, c) select d, e, f from b in ‘database’ where conditions for example: .. from b in ‘”& Server.MapPath (“. “) &” / data.mdb “&”‘ where .. 4, Description: subquery (table name: a table name 2: b) select a, b, c from a where a IN (select d from b) or: SELECT a, b, c the from a WHERE a IN (1,2,3), Description: Display articles, the author and the Last Post Time select a.title, a. username, b.adddate from table A, (select max (AddDate) AddDate the from table where table.title = a.title) b 6: Outer join query (table name: A table name 2: b) select aa ab, ac, bc, bd, bf from a LEFT OUT JOIN b ON aa = bc 7, Description: online view of the inquiry (the name of the table 1: a) select * from (SELECT a, b, c FROM a) T where TA> 1; Description: between usage between restrict the query range of data including the boundary values, not between not include select * from table1 where time between time1 and time2 select a, b, c, from table1 where a not between a value of 1 and value 29 Description: 10 Description: two related tables in use select * from table1 the where a [not] in (‘value’, ‘value’, ‘value’, ‘value’) delete the main table in the sub-table information delete from table1 the where not exists (select * from table2 where table1.column1 = table2.field1) 11 Description: the joint investigation of the four tables: select * from a left inner join b on aa = bb right inner join c on aa = cc inner join d on aa = dd where ….. 12 Description: Schedule at least five minutes to remind SQL: select * from schedule arrangements where datediff (‘minute’ f the beginning of time, getdate ())> 5 13 Description: a sql statement to get the database paging select top 10 b. * the FROM (select top 20 the primary key fields, sort fields from the table name sort order by 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 the range of 15, Description: select corresponding to the same data as the b value of each group in a the largest record all the information (similar to such usage can be used for the monthly chart of the Forum, selling monthly product analysis, ranked by subjects at Grade, and so on) select a, b, c from tablename the ta where a = (select max (a) from tablename tb where tb.b = ta.b) 16, Description: All in TableA but not TableB and TableC, line and remove all duplicate lines derives 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 10 * from tablename order by newid () 18 Description: picked randomly select newid () 19 Description: Delete duplicate records Delete from tablename where id not in (select max (id) from tablename group by col1, col2, …) 20, Description: List all the table names in the database select name from sysobjects where type = ‘U’ 21 Description: listed in table select name from syscolumns where id = object_id (‘TableName’) 22, Description: presented TYPE vender, PCS field, type field arrangement, the case can easily achieve multiple choice, similar to select the case. 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 results: type vender pcs computer A computer A CD-ROM B 2 disc B 3, A 2 Mobile phone C 3 23 Description: initialization table table1 TRUNCATE TABLE table1 24, Description: Choose from a record of 10-15 select top 5 * from (select top 15 * from table order by id asc) table_ alias order by id desc three techniques 1,1 = 1,1 = 2 used in the SQL statement when combined with more WHERE 1 = 1 “Select All” where 1 = 2 “All do not choose, such as: if _AT_ strWhere! =” begin set @ strSQL = ‘select count (*) as Total from [‘ + _AT_ tblName + ‘where’ + @ The strWhere end the the else begin set @ strSQL = ‘select count (*) AS Total from [‘ + _AT_ tblName + ‘]’ end can be written directly set @ strSQL = ‘select count (*) as Total from [‘ + _AT_ tblName + ‘] where 1 = 1 stability’ + _AT_ strWhere shrink database – rebuild the index DBCC REINDEX DBCC INDEXDEFRAG the – shrinkage Data and log DBCC SHRINKDB is DBCC SHRINKFILE 3, compressed database DBCC SHRINKDATABASE (dbname), transfer the database to the new user existing user permissions exec the sp_change_users_login ‘update_one’, ‘newname’, ‘oldname’ go 5, check the backup set RESTORE VERIFYONLY from disk = ‘E :/ dvbbs.bak’ 6 the repair 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, @ MaxMinutes INT, @ NewSize INT USE tablename – the database SELECT _AT_ want to operate LogicalFileName = the ‘tablename_log’ – the log filename _AT_ MaxMinutes = 10, – Limit on time allowed to the wrap log _AT_ NewSize = 1 – Do you want to set the log file size (M) – Setup / initialize DECLARE _AT_ OriginalSize int SELECT @ OriginalSize = size FROM sysfiles WHERE name = @ LogicalFileName SELECT ‘Original Size of’ + db_name () + ‘LOG is’ + CONVERT (VARCHAR (30), @ OriginalSize) + ‘8K pages or’ + CONVERT (VARCHAR ( 30), (@ OriginalSize * 8/1024)) + ‘MB’ FROM sysfiles WHERE name = @ LogicalFileName CREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @ Counter INT, @ StartTime DATETIME, @ TruncLog VARCHAR (255) SELECT @ StartTime = GETDATE (), @ TruncLog = ‘BACKUP LOG’ + db_name () + ‘WITH TRUNCATE_ONLY’ DBCC SHRINKFILE (@ LogicalFileName, @ NewSize) EXEC (@ TruncLog) – Wrap the log if necessary. WHILE @ MaxMinutes> DATEDIFF (mi, @ StartTime, GETDATE ()) – time has not expired AND @ OriginalSize = (SELECT size FROM sysfiles WHERE name = @ LogicalFileName) AND (@ OriginalSize * 8/1024)> @ NewSize BEGIN – Outer loop. SELECT @ Counter = 0 WHILE ((@ Counter <@ OriginalSize / 16) AND (@ Counter <50000)) BEGIN – update INSERT DummyTrans VALUES (‘Fill Log’) DELETE DummyTrans SELECT @ Counter = @ Counter + 1 END EXEC ( @ TruncLog) END 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 = @ LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF 8, Description: change a table the exec sp_changeobjectowner to ‘tablename’, ‘dbo’ storage change all table CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @ OldOwner as NVARCHAR (128), @ NewOwner as NVARCHAR (128) AS DECLARE @ Name as NVARCHAR (128) DECLARE @ Owner as NVARCHAR (128) DECLARE @ OwnerName as NVARCHAR (128) DECLARE curObject CURSOR FOR select ‘Name’ = name, ‘Owner’ = user_name (uid) from sysobjects where user_name (uid) = @ OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO @ Name, @ Owner WHILE (@ @ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwner begin set @ OwnerName = @ OldOwner + ‘.’ + rtrim (@ Name) exec sp_changeobjectowner @ OwnerName, @ NewOwner end – select @ name, @ NewOwner, @ OldOwner FETCH NEXT FROM curObject INTO @ Name, @ Owner END close curObject deallocate curObject GO 10, SQL SERVER cycle write directly the data declare _AT_ i int set @ i = 1 while @ i <30 begin insert into, test (userid) values ??(_AT_ i) set _AT_ i = @ i +1 end small note stored procedures frequently used this week, this month, the function DATEADD this year (WK, datediff (wk, 0, getdate ()), -1) DateAdd (wk, datediff (wk, 0, getdate ()), 6) DATEADD (mm, datediff (mm, 0, getdate ()), 0) Dateadd (ms, -3, dateadd (mm, datediff (m, 0, getdate ()) +1,0)) Dateadd (yy, datediff (yy, 0, getdate ()), 0) DATEADD (ms, -3, DATEADD (yy, DATEDIFF (yy, 0, getdate ()) +1, 0)) at the SQL code just one time segment DATEADD (wk, datediff (wk, 0 getdate ()) -1) DATEADD (WK, DateDiff (WK, 0, getdate ()), 6) is said this week time period. following SQL condition part, that is, the query time period in the range of this week : Where Time BETWEEN Dateadd (wk, datediff (wk, 0, getdate ()), -1) AND Dateadd (wk, datediff (wk, 0, getdate ()), 6) in a stored procedure select @ begintime = Dateadd (wk, datediff (wk, 0, getdate ()), -1) select @ endtime = Dateadd (wk, datediff (wk, 0, getdate ()), 6)