SQL statement full

Before creating a database, determine if the database exists if exists (SELECT * from sysdatabases where name= ' databaseName ') drop DB databasenamegocreate DATABASE Databasenameon primary--belongs to the primary filegroup by default, which can be omitted (the specific description of the/*--data file--*/name= ' Databasename_data ',-- The logical name of the main data file filename= "stored in: \databasename_data.mdf ',--the physical name of the main data file size= MB,--The initial size of the main data file maxsize= the value MB,-- Main data file growth maximum value filegrowth= value%--main data file growth rate) Log on (/*--log file for specific description, the meaning of each parameter is--*/name= ' Databasename_log ',-- The logical name of the log file filename= ' stored directory: \databasename_log.ldf ',--the physical name of the log file size= the value MB, the initial size of the log file filegrowth= Value mb--log file growth) Delete database drop databases DatabaseName Backup---Create backup data deviceuse masterexec sp_addumpdevice ' disk ', ' testback ', ' C : \mssql7backup\mynwind_1.dat '---Start back up backup DATABASE pubs to testback create a new table TabName (col1 type1 [NOT NULL] [prim ary Key] Identity (start value, increment amount), col2 type2 [NOT NULL],..) --primary key identity indicates that the increment number creates a new table from an existing table: A:gouse the original database name Goselect * into destination database name. dbo. destination table name from the original table name (Create a new table using the old table) b:create table Tab_new as Select Col1,col2 ... from tab_old definition only creates sequence create sequenceSimon_sequenceminvalue 1--min maxvalue 999999999999999999999999999--Maximum start with 1--Start value increment by 1--Add a few cache 2 each time 0; drop table tabname--This is to delete the table along with the information in the table, but there will be a record deletion in the log file delete from table_name-This is to delete the information in the table but will keep this table add ALTER TABLE Table_ Name Add column_name column_type [default defaults]--add a column to the table, [] the contents of [] are optional DELETE columns ALTER TABLE table_name DROP column Column_ name--remove a column from a table add primary key Alter table TabName add primary key (COL) Description: Delete primary key: Alter table tabname drop primary KEY (COL) create index creation [Uni Que] index idxname on tabname (col .... Drop INDEX idxname on tabname Note: The index is immutable and you have to remove the rebuild if you want to change it. Creating views Create VIEW ViewName As SELECT statement Delete view: Drop view viewname Basic statement (1) Data record filter: sql= "SELECT * from data table where field name = field value Orde R by field name [desc] "(in descending order of a field value.) Default ascending ASC) sql= "SELECT * from data table where field name like '% field value% ' Order by field name [desc]" sql= "SELECT Top ten * from datasheet where field name = field Value Order b y field name [desc] "sql=" SELECT top * from data table order by field name [desc] "sql=" SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ') "Sql=" sel ECT * from data table where field name between value 1 and value 2 "(2) UpdateData record: sql= "Update data table set field name = field value where Condition expression" sql= "Update data Table set field 1= value 1, field 2= value 2 ... field n= value n Where Condition expression "(3) Delete data record: sql=" delete from data table where condition expression "sql=" Delete from data table "(delete all records of data table) (4) Add data record: sql=" insert in To Data table (field 1, Field 2, Field 3 ...) VALUES (value 1, value 2, value 3 ...) Sql= "INSERT INTO Target data table select * From source data table" (Add record of source data table to target data table) (5) Data record statistic function: AVG (field name) to derive a table bar average count (*; field name) Statistics on the number of rows of data or the number of data rows that have values for a column max (field name) gets the maximum value of a table column min (field name) gets the minimum value of a table column sum (field name) adds the value of the data bar to the method that references the above function: sql= "Select sum (field name) as Alias The From data table where condition expression "set Rs=conn.excute (SQL) uses RS (" Alias ") to obtain the value of the statistic, and the other functions use the same as above. Query removal duplicate values: SELECT DISTINCT * FROM table1 (6) data table creation and deletion: CREATE table data table name (field 1 type 1 (length), Field 2 type 2 (length) ...) (7) Single-column summation: SELECT sum (field name) from data table latest statement edit A table in the query database that contains the same field: select name from sysobjects where xtype = ' u ' and ID in (select ID from syscolumns WHERE name = ' S3 ') based on date of birth Can calculate Age: Select DateDiff (YEAR,SCRQ, ') as age from Page_shsjgrgl automatically calculates age based on current year select DATEDIFF (Year,csny,cast ( GETDATE ()) as Char)) annual Select year (DJSJ) from Page_shsjgrgl months Select month (DJSJ) from Page_shsjgrgl daily Select Day (DJSJ) from Pag E_shsjgrgl duplicate table structure in the same database: SELECT * into a from B where 1<>1 when IDENTITY_INSERT is set to OFF, an explicit value cannot be inserted for the Identity column in table ' AA '. SET IDENTITY_INSERT AA on----set Open, BULK INSERT: INSERT INTO AA (customer_id, Id_type, id_number) Select customer_id, Id_type, Id_nu Mber from Tcustomer;set identity_insert AA off---shutting down replication between different databases: Copy structure: SELECT * into TEST.DBO.B from GCRT.dbo.page_shsjgrgl W Here 1<>1 copy content: INSERT INTO TEST.DBO.B (XM,SSDQ) Select XM,SSDQ from GCRT.dbo.page_shsjgrgl View all data table table names in the database: Select Name from SysObjects where type= ' u ' view all tables in the database that contain the same field table: select name from SysObjects where xtype = ' u ' and ID in (SELECT ID fro M syscolumns WHERE name = ' same field ') View all fields in the datasheet: select nAme from syscolumns where id=object_id (' table name ') query database First 10 records: SELECT Top * from Td_areacode order by newid () modify field type: Alter TAB LE Table name ALTER COLUMN field name varchar (+) not nulluse zhjiangjgyldeclare @temp nvarchar () Set @temp = ' ZWI4 ' select Hllx from Pag E_yljg_zyry where Hllx not in (Selectcase @temp when "then" Else B1 endfrom (SELECT * from Td_code where page_en= ' Page_yl Jg_zyry ' and b2= ' Zwi ') s where s.b1!=case @temp when "then" else @temp end) Change the database table field type: ALTER TABLE Page_shsjgrgl ALTER column S1 int advanced query 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. Outer join a, left OUTER join: OUTER join (left JOIN): The result set includes both the matching row of 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 OUT Join B on a.a = B.cb:right outer join: Right outer join (right connection): The result set includes both the matching join row for the join table and all rows of the right join table. C:full outer join: Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables. Judging objects edit Determine if the database exists if exists (select*fromsysdatabaseswherename= ' database name ') dropdatabase[database name] Determine if the table exists if not EXISTS (SELECT * FROM sysobjects where [name] = ' table name ' and xtype= ' U ') begin--here to create the table end to determine if the stored procedure exists if exists (Select*fromsysobjectswhereid = object_ ID (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, n ' isprocedure ') = 1) dropprocedure[stored procedure name] determine if the temporary table exists if object_id (' tempdb. #临时表名 ') isnot nulldroptable# temporary table name to determine if the view exists--sql Server 2000IF EXISTS (select*fromsysviewswhereobject_id = ' [dbo].[ View name] '--sql Server 2005IF EXISTS (select*fromsys.viewswhereobject_id = ' [dbo].[ View name] ' determines if the function exists if exists (Select*fromdbo.sysobjectswhereid = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF ')) dropfunction[dbo]. [function name] Gets the type of the representation parameter that creates the information select[name],[id],crdatefromsysobjectswherextype= ' U '/*xtype, usually including the following C =check constraints D = default value or defaults constraint F =foreignkey Constraint L = log fn = scalar function if = inline table function P = stored procedure PK =primarykey constraint (type is k) RF = copy Filter stored procedure S = system table TF = table function TR = trigger u = User Table UQ =un Ique constraint (type is k) V = view X = Extended Stored procedure */Determine if the column exists if exists (select*fromsyscolumnswhereid=object_id (' tableName ') andname= ' column name ') altertable table name dropcolumn column name to determine if the column is self-increment if columnproperty (object_id (' table '), ' col ', ' isidentity ') =1print ' Self-increment ' elseprint ' is not a self-increment column ' select*fromsys.columnswhereobject_id=object_id (' table name ') and Is_identity=1 determine if there is an index in the table if exists ( select*fromsysindexeswhereid=object_id (' table name ') andname= ' index name ') print ' exists ' elseprint ' does not exist view objects in database select* Fromsysobjectswherename= ' Object name ' SELECT * FROM table (table name to query) where COLOUM (condition) promotion edit 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 Method II: Select top 0 * into B from a copy table (copy data, source table name: a mesh Label table name: B) (access available) insert into B (x, Y, z) select D,e,f from A; Copy of table across databases (exact data using absolute path) (Access available) insert into B (x, Y, z) Select D,e,f from a in ' specific database ' Where condition example: from B in ' "&server.mappath (". ") & "\data.mdb" & "' Where." Subquery (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 in (three-in-one) show article last time select a . title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where table.title=a.title) B 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 online view query (table name 1:aselect * FROM (select A,b,c from A) T where t.a > 1;between usage between to query the specified range of a field, limit the query data range to include boundary values, not between excluding boundary values select * FROM table1 where time betwee n time1 and Time2select a,b,c, from table1 where a not between value 1 and value 2in use SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ') Delete Main Table no information two association tables delete FROM table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1 four table linked to 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 ..... Schedule five minutes in advance Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5 A SQL database page select top b.* from (select t OP 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 first 10 records select Top * FROM table1 W Here range selection ranking selects all information corresponding to a largest record in each set of B values in the same data (such usage can be used for forum monthly leaderboard, monthly hot product analysis, ranking by subject score, etc.). ) Select A,b,c from TableName ta where a= (select Max (a) from TableName TB where tb.b=ta.b) derived results table includes all in TableA but not in tableb and table C rows and eliminates all duplicate rows to derive a result table (select a From TableA except (select a from TableB) except (select a from TableC) randomly extracts 10 data select top TableName ORDER by NEWID () Random selection record select NEWID () Delete duplicate record delete from TableName where ID not in (SELECT Max (ID) from TableName GROUP BY Col1,col2,...) List the table names in the database select name from sysobjects where type= ' U ' lists all the select name from sys in the tableColumns where id=object_id (' TableName ') lists the type, Vender, and PCs fields, arranged in the Type field, case can easily implement multiple selections, similar to case in select. Select Type,sum (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' B ' then 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 mobile B 3 Mobile phone C 3 initialization table Table1trunca TE TABLE table1 Selects records from 10 to 15 select Top 5 * FROM (select Top10 * from TABLE ORDER by ID ASC) Table_ alias O Rder by id DESC) table_2 the ORDER by ID data type conversion declare @numid intdeclare @id varchar (set @numid =2005set @id =convert (varchar, @numid) The data type int is converted to varchar by the above statement, other conversions are similar, see convert function Tips edit The use of 1=1,1=2 in the combination of SQL statements with more "where 1=1" is the choice of all "where 1=2" is not selected, such as: if @strWhere! = ' Beginset @strSQL = ' SELECT count (*) as Total F Rom [' + @tblName + '] where ' + @strWhereendelsebeginset @strSQL = ' SELECT count (*) as total from [' + @tblName + '] ' end I They can be written directly as set @strSQL = ' SELECT count (*) as total from tablename where + ' if (@strWhere! = ') {set @[email protected]+ ' and ' +strwhere} Shrink Database--rebuild index DBCC REINDEXDBCC indexdefrag--shrink data and log DBCC SHRINKDBDBCC Shrinkfile compress database DBCC SHRINKDATABASE ( dbname) Transfer database to new user with existing user rights exec sp_change_users_login ' Update_One ', ' newname ', ' oldname ' Go check backup set restore VERIFYONLY from disk= ' E:\dvbbs.bak ' repair database alter databases [DVBBS] SET single_usergodbcc CHECKDB (' Dvbbs ', Repair_allow_data_loss) with Tablockgoalter DATABASE [Dvbbs] set Multi_usergo log purge set NOCOUNT ondeclare @LogicalFileName sysname, @MaxMinutes int,@ NewSize intuse TableName--database name to manipulate select @LogicalFileName = ' Tablename_log ',--log file name @maxminutes = ten,--Limit on time a llowed to wrap log. @NewSize = 1-the size of the log file you want to set (M)--Setup /Initializedeclare @OriginalSize intSELECT @OriginalSize = sizefrom sysfileswhere name = @LogicalFileNameSelect ' Origina L Size of ' + db_name () + ' LOG is ' +convert (varchar (), @OriginalSize) + ' 8K pages or ' +convert (varchar), (@Original size*8/1024) + ' MB ' from sysfileswhere name = @LogicalFileNameCreate TABLE Dummytrans (dummycolumn char (8000) NOT NULL) DEC Lare @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR (255) Select @StartTime = GETDATE (), @TruncLog = ' BACKUP LOG ' + db_n Ame () + ' with Truncate_only ' DBCC shrinkfile (@LogicalFileName, @NewSize) EXEC (@TruncLog)-Wrap the log if necessary. While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time had not expiredand @OriginalSize = (Select size from S Ysfiles Where name = @LogicalFileName) and (@OriginalSize * 8/1024) > @NewSizeBEGIN--Outer loop. Select @Counter = 0WHILE ((@Counter < @OriginalSize/16) and (@Counter < 50000)) BEGIN--Updateinsert Dummytrans VA Lues (' Fill Log ') Delete dummytransselect @Counter = @Counter + 1ENDEXEC (@TruncLog) endselect ' Final Size of ' + db_name () + ' LOG is ' +convert (VARCHAR (+), Size) + ' 8K PA Ges or ' +convert (VARCHAR (+), (size*8/1024)) + ' MB ' from sysfileswhere name = @LogicalFileNameDrop TABLE dummytransset NOCO UNT off change a table exec sp_changeobjectowner ' tablename ', ' dbo ' store to change all tables create PROCEDURE dbo. User_changeobjectownerbatch@oldowner as NVARCHAR, @NewOwner as NVARCHAR (+) Asdeclare @Name as NVARCHAR (128) DECLARE @Owner as NVARCHAR (+) DECLARE @OwnerName as NVARCHAR (+) DECLARE curobject CURSOR forselect ' name ' = name, ' Owner ' = USER_NAME (UID) from Sysobjectswhere user_name (UID) [Email protected]order by Nameopen Curobjectfetch NEXT from Curobject into @Name, @OwnerWHILE (@ @FETCH_STATUS =0) beginif @[email protected]beginset @OwnerName = @OldOwner + '. ' + RTrim (@Name) exec sp_changeobjectowner @OwnerName, @NewOwnerend--select @name, @NewOwner, @OldOwnerFETCH NEXT from Curobject into @Name, @OwnerENDclose curobjectdeallocate Curobjectgo Loop Write data declare @i inTset @i=1while @i<30begininsert into Test (userid) VALUES (@i) Set @[email protected]+1end

SQL statement full