Database most commonly used statements

Database most commonly used statements

1, copy the table (copy structure only, source table name: A new table name: b) Law one: SELECT * into B from a where 1<>1 Law II: SELECT top 0 * into B from a 2. Copy table (copy data, source table name: A target table name: b) Insert into B (A, B, c) select d,e,f from B; 3. Copy of tables across databases (use absolute path for specific data) 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, sub-query (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 (all-in-a- 5. Display the article, the author and the last reply time Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b 6, Outer 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 off JOIN b on a.a = B.C 7, Online view query (table name 1:a) SELECT * FROM (select A,b,c from a) T where t.a > 1; 8, between usage, between limit the query data range including the boundary value, not between does not include SELECT * FROM table1 where time between time1 and time2 Select A,b,c, from table1 where a is not between value 1 and value 2 9, in the use of the method SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ') 10. Two related tables, delete the information in the main table that is not already in the secondary table Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1) 11. Four-table joint search: SELECT * 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 ... 12, schedule five minutes early reminder Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5 13, a SQL statement to take care of database paging Select 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 14, the first 10 records Select Top Ten * form table1 where range 15, select the same data in each group B value corresponding to the largest record of all the information (similar to the use of this can be used for the forum monthly leaderboard, monthly hot product analysis, ranked by the subjects, and so on.) Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b) 16, including all rows in TableA but not in TableB and TableC and eliminate all duplicate rows and derive a result table (select a from TableA) except (select a from TableB) except (select a from TableC) 17, randomly take out 10 data Select Top * FROM tablename ORDER by NEWID () 18. Random Selection of records Select NEWID () 19. Delete duplicate records Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...) 20. List all table names in the database Select name from sysobjects where type= ' U ' 21. List all of the tables Select name from syscolumns where id=object_id (' TableName ') + (1) data record filtering: sql= "select*from data table where field name = field value by field name [desc]" sql= "select*from data table where field name like '% field value% ' field name [ DESC] "sql=" select top10* from data table where field name order by field name [desc] "sql=" select*from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ') "sql=" Select*from data table where field name between value 1and value 2 "(2) Update data 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 conditional expression "sql=" delete   from data table (delete all records from datasheet) (4) Add data record: Sql= "insert    into 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 a record of the source data table to the Target data table) (5) Data record statistic function: AVG (field name) results in a table bar average count (*| field name) statistics on the number of rows of data or data rows with 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 column to the method that refers to the above function: sql= "select   SUM (field name)    as alias from data table    The Where conditional expression "setrs=conn.excute (SQL) obtains the system's value with RS (" Alias "), and the other functions use the same ibid. (5) Creation and deletion of data sheets: CReate   table Data table name (field 1 type 1 (length), Field 2 type 2 (length) ...) example:create   table   tab01 (name   varchar (4), datetime   Defaultnow ()) Droptable data table name (permanently delete a data table). Recordset object method: Rs.move Next moves the record pointer down one line from the current position Rs.move previous moves the record pointer up one line from the current position Rs.move first moves the record pointer to the top row of the data table Rs.move last moves the record pointer to the final row of the data table rs.absoluteposition= N Moves the record pointer to row n of the data table Rs.absolutepage=n moves the record pointer to the first row of page n Rs.pagesize=n sets the N records per page Rs.pagecount returns the total number of pages based on the settings of the PageSize Rs.recordcount returns the total number of records RS.BOF returns whether the record pointer is above the first end of the data table, True indicates Yes, FALSE is no rs.eof returns whether the record pointer is outside the end of the data table, True indicates Yes, FALSE is no Rs.delete deletes the current record, but the record pointer does not move down rs.add new add record to datasheet end rs.update Update datasheet record The addition, deletion, modification of SQL statements, although there are many ways, but in the use of the process is not enough, I do not know whether there are more flexible use of the method to contribute? Add, remove, and modify using DB. The Execute (SQL) command performs the action ╔----------------╗☆ data record filtering ☆╚----------------╝ Note: The use of single and double quotes may be incorrect (no test) SQL = "Select Distinct field name from data table" Distinct function to query for records that are not duplicated in the data inventory table SQL = "SELECT count (*) from data table where field name 1> #18:0:0# and field name 1< #19:00#" Count function, query number of records in the library table, "field name 1" refers to the same field example: Set rs= Conn.execute ("SELECT count (ID) as Idnum from News") Response.Write rs ("Idnum") Sql= "SELECT * from data table where field name between value 1 and value 2" sql= "SELECT * from data table where field name between #2003 -8-10# and #2003 -8-12#" Find all records from 2003-8-10 to 2003-8-12 in a field with a date class value of 2003-8-10 19:55:08, regardless of a few minutes. SELECT * from Tb_name where datetime between #2003 -8-10# and #2003 the data format in the -8-12# field is: 2003-8-10 19:55:08, via SQL isolated from 2003-8-10 to 2 003-8-12 all records, regardless of a few minutes. Sql= "SELECT * from data table where field name = field value order BY field name [desc]" Sql= "SELECT * from data table where field name like '% field value% ' Order by field name [desc]" Fuzzy query Sql= "SELECT top" from data table where field name order by field name [desc] "Find top 10 records in database Sql= "SELECT top N * form data table order by NEWID ()" a method of randomly fetching several records in a database top n,n is the number of records to be fetched Sql= "SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ')" ╔----------------╗☆ Add data record ☆╚----------------╝sql= insert INTO datasheet (field 1, Field 2, Field 3 ...) valuess (value 1, value 2, value 3 ...) Sql= "INSERT into data table valuess (value 1, value 2, value 3 ...)" does not specify a specific field name that will be added in the order of the fields in the data table, in turn Sql= "INSERT into Target datasheet SELECT * From source data table" adds a record of the source data table to the Target data table ╔----------------╗☆ Update data 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 " sql= "Update data Table set field 1= value 1, field 2= value 2 ... Field n= value n "No condition updates the specified field value in the entire data table ╔----------------╗☆ Delete data records ☆╚----------------╝sql= "Delete from data table where condition expression" Sql= "Delete from data table" No conditions will delete all records in the data table Methods that refer to the above functions: sql= "Select sum (field name) as Alias from data table where conditional expression" set Rs=conn.excute (SQL) obtains the system's value with RS ("Alias"), other functions are used as above. ╔----------------------╗☆ data table creation and deletion ☆╚----------------------╝create table data table name (field 1 type 1 (length), Field 2 type 2 (length) ...) Example: CREATE T ABLE TAB01 (name varchar (), datetime default Now ()) DROP table name (permanently delete a data table) ╔--------------------╗☆ Recordset object ☆╚--------------------╝rs.movenext moves the record pointer down one line from the current position Rs.moveprevious move the record pointer up one line from the current position rs.movefirst move the record pointer to the first row of the datasheet rs.movelast move the record pointer to the last row in the datasheet rs.absoluteposition=n Move the record pointer to row n of the data table rs.absolutepage=n move the record pointer to the first row of page n rs.pagesize=n set n records per page Rs.pagecount returns the total number of pages based on the pagesize settings rs.recordcount Returns the total number of records RS.BOF returns whether the record pointer is above the first end of the data table, True indicates Yes, FALSE is no rs.eof returns whether the record pointer is outside the end of the datasheet, True indicates yes, false to no rs.delete delete the current record, but the record pointer does not move down Rs.addnew add a record to the end of the datasheet rs.update update the data table record