SQL Statement Encyclopedia

SQL, a Structured Query Language (structured Query Language), is a special purpose programming language, a database query and programming language for accessing data and querying, updating, and managing relational database systems, as well as the extension of database script files. SQL statements regardless of the type or quantity are numerous, many statements are often used, SQL query statement is a typical example, whether it is advanced query or low-level query, SQL query statement requirements are the most frequent.

Basis

Create a database

Before creation, 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= MB,--The initial size of the log file filegrowth= the value%--the growth of the log file)SQL statement Daquan Delete databaseDrop database DatabaseNameSQL statement Daquan backup---Create backup data deviceuse masterexec sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '---start backing up backup DATABASE pubs to TestbackSQL statement Encyclopedia creating a new tableCREATE TABLE TabName (col1 type1 [NOT NULL] [primary key] identity (start value, increment), 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 onlySQL statement Daquan create sequenceCreate sequence Simon_sequenceminvalue 1--min maxvalue 999999999999999999999999999--Maximum start with 1--Start value increment by 1--Add a few cache 20 at a time;SQL statement Daquan Delete tableDrop table tabname--This is to delete the table along with the information in the table, but there will be records in the log fileSQL statement Daquan Delete informationDelete from table_name-This is the deletion of the information in the table but the table is preservedSQL statement Encyclopedia add columnAlter Table table_name Add column_name column_type [default defaults]--add a column to the table, [] contents are optionalSQL statement daquan Delete columnAlter table table_name Drop column column_name--remove a row from the tableSQL statement Daquan add primary keyALTER TABLE TabName add primary key (COL) Description: Delete primary key: Alter table tabname drop primary key (COL)SQL statement Daquan CREATE INDEXCreate [unique] 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.SQL statement Daquan CREATE VIEWCREATE VIEW viewname As SELECT statement Delete view: Drop View ViewNameSQL statement Daquan Basic statement(1) Data record filter: sql= "SELECT * from data table where field name = field value order 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) Update data record: sql=" Update data table set field name = field value where Condition expression "sql=" Update datasheet 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 query   Querying the database for tables that contain the same field: select name from sysobjects where xtype = ' u ' and ID in (select ID from syscolumns WHERE name = ' S3 ') Age can be calculated according to date of birth: Select DateDiff (YEAR,SCRQ, ') as age from Page_shsjgrgl automatically calculates age based on the current year select DateDiff (Year,csny,cast ( Year (GETDATE ()) as Char)) annual Select (DJSJ) from Page_shsjgrgl months Select month (DJSJ) from Page_shsjgrgl daily Select Day (DJSJ) From Page_shsjgrgl duplicate table structure in the same database: SELECT * into a from B where 1<>1 cannot insert an explicit value for an identity column in table ' AA ' when IDENTITY_INSERT is set to OFF. 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: ALT ER Table table name ALTER COLUMN field name varchar (+) not nulluse zhjiangjgyldeclare @temp nvarchar () Set @temp = ' ZWI4 ' Select Hllx fr Om Page_yljg_zyry where Hllx not in (Selectcase @temp when "then" Else B1 endfrom (SELECT * from Td_code where page_en= ' p Age_yljg_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 INTSQL Statement Daquan advanced QueryThe a:union operator 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.SQL Statement Encyclopedia outer joinsA, LEFT OUTER join: Open 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 off JOIN b on a.a = B.cb:right outer JOIN: Right outer join (right connection): The result set includes both a matching join row for the join table, and All rows that include 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 determine if the database existsif exists ( Select* fromsysdatabases where name= ' database name ') Drop Database[Database name] determine if a table existsIf not EXISTS (SELECT * from sysobjects where [name] = ' table name ' and xtype= ' U ') begin--CREATE table end here determine if a stored procedure existsif exists ( Select* fromsysobjects whereid = object_id (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, n ' isprocedure ') = 1) Drop procedure[Stored procedure name] determine if a temporary table existsIf object_id (' tempdb.. #临时表名 ') isNOT NULL Drop Table#临时表名 To determine whether a view exists--sql Server 2000IF EXISTS ( SELECT* fromSysviews WHEREobject_id = ' [dbo]. [View name] '--sql Server 2005IF EXISTS ( SELECT* fromSys.views WHEREobject_id = ' [dbo]. [View name] ' determine if a function existsif exists ( Select* fromDbo.sysobjects whereid = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF ')) Drop function[dbo]. [function name] Get creation Information SELECT[ name],[id],crdate fromsysobjects whereThe xtype= ' U '/*xtype represents the parameter type, usually including the following C = CHECKConstrain D = default value or DEFAULTConstraint F = FOREIGN KEYConstraint L = log fn = scalar function if = inline table function P = Stored procedure pk = PRIMARY KEYConstraint (type is k) RF = copy Filter stored procedure S = system table TF = table function TR = trigger u = User table UQ = UNIQUEConstraint (type is k) V = view X = Extended stored procedure */ determine if a column existsif exists ( Select* fromsyscolumns whereid=object_id (' table name ') and name= ' column name ') Alter TableTable name Drop columnColumn Name To determine whether a column is self-incrementIf ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1print ' self-increment ' Elseprint ' is not self-adding column ' SELECT* fromSys.columns WHEREobject_id=object_id (' table name ') and Is_identity=1 determine if an index exists in the tableif exists ( Select* fromsysindexes whereid=object_id (' table name ') and name= ' index name ') print ' exists ' Elseprint ' does not exist viewing objects in a database SELECT* fromsysobjects WHERE name= ' Object name ' SELECT * FROM table (table name to query) where Coloum (condition) Ascension Copying Tables(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 target table name: b) (Access available) insert into B (x, Y, z) select D,e,f from A; copies of tables across databases(Use absolute path for specific data) (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." Sub-query(Table name 1:a table name 2:b) Select A,b,c from a where A to (select D from B or: Select A,b,c from a where a in (three-to-one) Show article last timeSelect A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b outer JOIN 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 off 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; the use of betweenBetween includes boundary values for querying the specified range of a field, limiting the query data range, not between excluding boundary values select * FROM table1 where time between time1 and Time2select A,b,c, F ROM table1 where a not between value 1 and value 2 in how to useSELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ') Delete information that is not in the main tableTwo association tables delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1 Four table-linked problemsSELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ... five minutes before scheduleSql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5 a SQL Database pageSelect Top 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 Top 10 RecordsSelect Top Ten * from table1 where range Select RankSelect all information for a maximum record in the same data for each set of B values (similar 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 TableIncludes all rows in TableA but not in TableB and TableC and eliminates all duplicate rows to derive a result table (select a From TableA except (select a from TableB) except (Select a fro M TableC) randomly remove 10 dataSelect Top * FROM tablename ORDER by NEWID () random selection of recordsSelect NEWID () Delete duplicate recordsDelete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...) list the table names in the databaseSelect name from sysobjects where type= ' U ' list all of the tablesSelect name from syscolumns where id=object_id (' TableName ') List ArrangementThe type, vender, and PCs fields are listed in the Type field, which makes it easy to 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 Initialize table table1TRUNCATE TABLE table1 Select a record from 10 to 15Select Top 5 * FROM (select Top10 * FROM (select top, from table, ORDER by ID ASC) Table_ alias ORDER by id desc) table_2 o Rder by ID Data type ConversionsDECLARE @numid intdeclare @id varchar (set @numid =2005set @id =convert (varchar, @numid) through the above statement to complete the data type int converted to varchar, Other conversions are similar, see the CONVERT function Tips Use of 1=1,1=2More "where 1=1" is used when combining SQL statements to select All "where 1=2", such as: if @strWhere! = ' Beginset @strSQL = ' SELECT count (*) as total from [' + @ Tblname + '] where ' + @strWhereendelsebeginset @strSQL = ' SELECT count (*) as total from [' + @tblName + '] ' end we can write directly to set @strSQL = ' SELECT count (*) as total from tablename where + ' if (@strWhere! = ') {set @[email protected]+ ' and ' +strwhere} Shrinking a database--Rebuild Index DBCC REINDEXDBCC indexdefrag--shrink data and log DBCC SHRINKDBDBCC Shrinkfile Compress DatabaseDBCC SHRINKDATABASE (dbname) Transfer database to new user with existing user rights exec sp_change_users_login ' Update_One ', ' newname ', ' oldname ' Go Check the backup setRESTORE verifyonly from disk= ' E:\dvbbs.bak ' Repairing the databaseAlter database [Dvbbs] SET single_usergodbcc CHECKDB (' Dvbbs ', repair_allow_data_loss) with Tablockgoalter DATABASE [ Dvbbs] SET Multi_usergo Log CleanupSET 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 allowed to wrap log. @NewSize = 1-The log file you want to set Size (M)--Setup/initializedeclare @OriginalSize intselect @OriginalSize = sizefrom sysfileswhere name = @LogicalFileNameS Elect ' Original Size of ' + db_name () + ' LOG is ' +convert (varchar (), @OriginalSize) + ' 8K pages or ' +convert (varchar ( (@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 LO G 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 pages or ' +convert (varchar ($), (size*8/1024)) + ' MB ' from Sysfileswhere Nam E = @LogicalFileNameDrop TABLE dummytransset NOCOUNT OFF Change a tableEXEC sp_changeobjectowner ' tablename ', ' dbo ' Store changes All tablesCreate 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) [e-mail Protected]order by Nameopen Curobjectfetch NEXT from Curobject Into @Name, @OwnerWHILE (@ @FETCH_STATUS =0) beginif @[email protected]beginset @OwnerName = @OldOwner + '. ' + RTrim (@Name) ex EC sp_changeobjectowner @OwnerName, @NewOwnerend-select @name, @NewOwner, @OldOwnerFETCH NEXT from Curobject into @Name, @OwnerENDclose curobjectdeallocate Curobjectgo Loop Write DataDECLARE @i intset @i=1while @i<30begininsert into Test (userid) VALUES (@i) set @[email protected]+1end

SQL Statement Encyclopedia