SQL statements worth adding to favorites

I. Basic 1. Description: create database database-name2, Description: delete database drop DATABASE dbname3, Description: Backup SQL server --- CREATE deviceUSE masterEXEC sp_addumpdevice 'disk' backup data ', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat '--- start backup database pubs TO testBack4. 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_ne W as select col1, col2... From tab_old definition only5. Description: Delete the new table drop table tabname6. Description: add an Alter table tabname add column col type. Note: The Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type. 7. Description: add a primary key: Alter table tabname add primary key (col) Description: delete a 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 cannot be changed. To change the index, you must delete it and recreate it. 9. Description: create view viewname as select statement Delete view: drop view viewname10. Description: select a few simple basic SQL statements: select * from table1 where range insert: insert into table1 (field1, field2) values (value1, value2) delete: delete from table1 where range update table1 set field1 = value1 where range search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query 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 max: select max (field1) as maxvalue from table1 min: select min (field1) as minvalue from table111 Note: several advanced query computation terms: the UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or table2. B: The distinct t operator. The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated. C: INTERSECT operator the INTERSECT operator derives a result table by only including rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated. Note: The query results of several computation words must be consistent. 12. Note: Use outer join A and left (outer) join: left outer join (left join): the result set contains matching rows of the connected table, and all rows of the left join table. SQL: select. a,. b,. c, B. c, B. d, B. f from a left out join B ON. a = B. cB: right (outer) join: right outer join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table. C: full/cross (outer) join: full outer join: includes not only matching rows in the symbolic join table, but also all records in the two connection tables. 12. Group: Group by: A table. After grouping is completed, you can only obtain Group-related information. Group-related information: (Statistical Information) criteria for grouping count, sum, max, min, and avg) in SQLServer: text, ntext, fields of the image type can be grouped based on fields in the selecte statistics function. They cannot be put together with common fields. 13. perform operations on the database: detaching the database: sp_detach_db; and attach the database: sp_attach_db indicates that the complete path 14 is required for appending. how to modify the Database name: sp_renamedb 'old _ name', 'new _ name' 2. Upgrade 1. Description: copy the table (only copy the structure, source table name: a new table name: b) (Access available) Method 1: select * into B from a where 1 <> 1 (for SQlServer only) Method 2: select top 0 * into B from a2, note: copy table (copy data, source table name: a target table name: B) (Access available) inse Rt into B (a, B, c) select d, e, f from B; 3. Description: copies of tables across databases (absolute paths are used 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 .. 4. Description: subquery (table name 1: a table name 2: B) select a, B, c from a where a IN (select d from B) or: select, b, c from a where a IN (, 3) 5. Description: select. title,. username, B. adddate from table a, (select max (adddat E) adddate from table where table. title =. title) b6, Description: External join query (table name 1: a table name 2: B) select. a,. b,. c, B. c, B. d, B. f from a left out join B ON. a = B. c7, 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. When between restricts the Data Query range, it includes the boundary value. not between does not include select * from table1 where time between time1 and time2select, b, c, from table1 where a not between value 1 and value 29. Description: select * From table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6') 10. Description: two joined tables, 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: Table 4 Joint query problem: select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where ..... 12. Note: Five minutes ahead of schedule reminder SQL: select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 513. Description: using one SQL statement to Score Database points Page select top 10 B. * from (select top 20 primary key field, sorting field from table name order by sorting field desc) a, table name B where B. primary key field =. primary key field order by. specific implementation of sorting fields: Database paging: declare @ start int, @ end int @ SQL nvarchar (600) 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 Note: a variable cannot be directly followed after top, so this is the only special method in actual application. Rid is an identifier column. If there are specific fields after top, this is very beneficial. This avoids the inconsistency in the actual table after the query results of the top field if it is a logical index. (the data in the logical index may be inconsistent with that in the data table, if the query is indexed, the index is first queried.) 14. Note: select top 10 * form table1 where range of the first 10 records 15. Description: select all the information of the largest a record corresponding to the data with the same B value in each group (usage similar to this can be used for monthly ranking of the forum and Analysis of popular products each month, rank by subject score, etc .) select a, B, c from tablename ta where a = (select max (a) from tablename tb where tb. B = ta. b) 16. Description: includes all rows in TableA but not in TableB and TableC and removes all repeated rows to derive a result table (select a from tableA) except T (select a from tableB) Random T (select a from tableC) 17. Description: select top 10 * from tablename order by newid () 18. Description: select newid () 19. Note: delete record 1), delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...) 2), select distinct * into temp from tablename delete from tablename insert into tablename select * from temp comment: this operation involves moving a large amount of data, this method is not suitable for large capacity but Data Operations 3). For example, if you import data to an external table, for some reasons, only a part of the data is imported for the first time. But it is difficult to determine the specific location, so that only the next full import will generate a lot of repeated fields, how to delete duplicate fields alter table tablename -- add an auto-incrementing column add column_ B int identity () delete from tablename where column_ B not in (select max (column_ B) from tablename group by column1, column2 ,...) alter table tablename drop column column_b20: list all the table names in the database. select name from sysobjects where type = 'U' // U indicates user 21. Description: list all columns in the Table. select name from syscolumns where id = object_id ('tablename ') 22. Description: lists the fields of type, vender, and pcs, which are arranged by the type field. case can be easily selected, 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: type vender pcs computer A 1 computer A 1 Disc B 2 Disc A 2 mobile phone B 3 mobile phone C 323, Description: initialization TABLE table1TRUNCATE TABLE table124, description: select top 5 * from (select top 15 * from table order by id asc) records from 10 to 15) table _ alias order by id desc 3. Tips 1, 1 = 2, in the SQL statement When a combination is used, "where 1 = 1" indicates that all "where 1 = 2" is selected, not all, for example: if @ strWhere! = ''Ininset @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] where' + @ strWhereendelsebeginset @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] 'end we can directly write an error! Directory item not found. Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] where 1 = 1 stationary '+ @ strWhere 2. shrink the database -- re-create the index dbcc reindexdbcc indexdefrag -- contract data and log dbcc shrinkdbdbcc SHRINKFILE3 and compress database dbcc shrinkdatabase (dbname) 4. Transfer the database to a new user with the existing user permission exec sp_change_users_login 'Update _ one', 'newname', 'oldname' go5. Check the backup set restore verifyonly from disk = 'e: \ dvbbs. bak '6. Fix database alter database [dvbbs] SET SINGLE_USERGODBCC CHECKDB ('dvbbs ', r Epair_allow_data_loss) with tablockgoalter database [dvbbs] SET MULTI_USERGO7, log clearing set nocount ondeclare @ LogicalFileName sysname, @ MaxMinutes INT, @ NewSize INTUSE tablename -- Name of the database to be operated SELECT @ LogicalFileName = 'tablename _ log', -- log file name @ MaxMinutes = 10, -- Limit on time allowed to wrap log. @ NewSize = 1 -- the size of the log file you want to set (M) Setup/initializeDECLARE @ OriginalSize intSELECT @ OriginalSize = sizeFROM sysfil EsWHERE name = @ LogicalFileNameSELECT 'ininal Size of '+ db_name () + 'Log is' + CONVERT (VARCHAR (30), @ OriginalSize) + '8 K pages or '+ CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024) + 'mb' FROM sysfilesWHERE name = @ LogicalFileNameCREATE 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 expiredAND @ 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 -- updateINSERT DummyTrans VALUES ('fill log') DELETE DummyTransSELECT @ Counter = @ Counter + 1 ENDEXEC (@ TruncLog) ENDSELECT 'final Size of '+ db_name () + 'Log is' + CONVERT (VARCHAR (30), size) + '8 K pages or '+ CONVERT (VARCHAR (30 ), (size * 8/1024) + 'mb' FROM sysfilesWHERE name = @ logicalfilenameddrop TABLE DummyTransSET NOCOUNT OFF8. Description: change a TABLE exec sp_changeobjectown. Er 'tablename', 'dbo' 9. Store and change all tables create procedure dbo. reply @ OldOwner as NVARCHAR (128), @ NewOwner as NVARCHAR (128) ASDECLARE @ Name as NVARCHAR (128) DECLARE @ Owner as NVARCHAR (128) DECLARE @ OwnerName as NVARCHAR (128) DECLARE curObject CURSOR FORselect 'name' = Name, 'owner' = user_name (uid) from sysobjectswhere user_name (uid) = @ OldOwnerorder by nameOPEN curObjectFETCH next from curObject INTO @ Name, @ OwnerWHILE (@ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwnerbegin set @ OwnerName = @ OldOwner + '. '+ rtrim (@ Name) exec sp_changeobjectowner @ OwnerName, @ NewOwnerend -- select @ name, @ NewOwner, @ OldOwnerFETCH next from curObject INTO @ Name, @ OwnerENDclose curObjectdeallocate curObjectGO10. SQL server directly writes data cyclically. declare @ I intset @ I = 1 while @ I <30 begin insert into test (userid) values (@ I) set @ I = @ I + 1en D Case: as shown in the following table, all the failures in the system should be framed. In each growth of 0.1, they should pass well: name score Zhangshan 80 Lishi 59 Wangwu 50 Songquan 69 while (select min (score) from tb_table) <60) beginupdate tb_table set score = score * 1.01 where score <60if (select min (score) from tb_table)> 60 breakelse continueend data development-classic 1. sort By surname strokes: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more 2. database encryption: select encrypt ('original password') select p Wdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password ') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise not the same 3. retrieve the table fields: declare @ list varchar (1000), @ SQL nvarchar (1000) select @ list = @ list + ',' + B. name from sysobjects a, syscolumns B where. id = B. id and. name = 'table a' set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table a' exec (@ SQL) 4. view the hard disk partition: EXEC master .. xp_fixeddriv Es5. compare whether tables A and B are equal: if (select checksum_agg (binary_checksum (*) from A) = (select checksum_agg (binary_checksum (*) from B) print 'equal to 'elasticprint' not equal to '6. kill all event Inspector processes: DECLARE hcforeach cursor global for select 'Kill '+ RTRIM (spid) FROM master. dbo. sysprocessesWHERE program_name IN ('SQL filer', n' SQL event profiler') EXEC sp_msforeach_worker '? '7. record Search: Select Top N * From Table --------------------------------------- N to M records (primary index ID required) select Top M-N * From table Where ID in (Select Top m id From Table) Order by ID Desc ------------------------------------ N to the end record Select Top N * From Table Order by ID Desc Case Example 1: A table has more than 10 thousand records. The first RecID field of the table is an auto-increment field. Write an SQL statement to find the 31st to 40th records of the table. Select top 10 recid from A where recid not in (select top 30 recid from A) Analysis: if this is the case, some problems may occur if the recid has A logical index in the table. Select top 10 recid from A where ...... Select top 30 recid from A is searched from the index, while select top 30 recid from A is searched in the data table. As A result, the order in the index may be different from that in the data table, in this way, the queried data is not originally expected. Solution 1: Use order by select top 30 recid from A order by ricid. If this field is not auto-incrementing, problem 2 will occur. In that subquery, add the following conditions: select top 30 recid from A where recid>-1 Example 2: query the last record in the table, and do not know how much data the table has and the table structure. Set @ s = 'select top 1 * from T where pid not in (select top '+ str (@ count-1) + 'pid from T) 'print @ s exec sp_executesql @ s9: obtain all user tables in the current database select Name from sysobjects where xtype = 'U' and status> = 0