Zz exquisite SQL

The following statements are Mssql statements and cannot be used in access.

SQL classification:
DDL-Data Definition Language (Create, Alter, Drop, DECLARE)
DML-data manipulation language (Select, Delete, Update, Insert)
DCL-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)

First, we will briefly introduce the basic statements:
1. Description: Create a database
Create DATABASE database-name
2. Description: delete a database.
Drop database dbname
3. Description: Back up SQL server
--- Create a device for the backup data
USE master
EXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat'
--- Start backup
Backup database pubs TO testBack
4. 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 (use the old table to create a new table)
B: Create Table tab_new as select col1, col2... From tab_old definition only
5. Description: Delete the new table drop table tabname
6. Description: Add a column.
Alter table tabname add column Col type
Note: 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)
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 viewname
10. Description: several simple basic SQL statements
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 %' --- 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 Table1
11. Description: several advanced query Operators
A: Union operator
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 Table1 or table2.
B: Random 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 the 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 external connections
A. left outer join:
Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected 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. c
B: 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 outer join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

Next, let's look at some good SQL statements.
1. Description: copy a table (only copy structure, source table name: a new table name: B) (Access available)
Method 1: select * into B from a where 1 <> 1
Method 2: select top 0 * into B from

2. Description: copy a 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;

3. Description: Table Copying across databases (absolute path 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: Table a 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 (1, 2, 3)

5. Description: displays the article, Submitter, and 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. Description: External join query (table name 1: Table a 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

7. Description: Online View query (table name 1:)
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 time2
Select a, B, c, from table1 where a not between value 1 and value 2

9. Description: How to Use in
Select * from table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6 ')

10. Description: two associated tables are used to delete information that is not in the secondary table.
Delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1)

11. Notes: four table join query problems:
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 ()> 5

13. Note: One SQL statement is used to handle database paging.
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. sorting Field

14. Note: The first 10 records
Select top 10 * Form Table1 where range

15. Note: select all the information of the largest record of a corresponding to the data with the same B value in each group (similar usage can be used for the 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) Before t (select a from tableb) Before t (select a from tablec)

17. Description: 10 data records are randomly taken out.
Select top 10 * from tablename order by newid ()

18. Description: randomly selected records
Select newid ()

19. Note: delete duplicate records
Delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...)

20. Description: Lists All table names in the database.
Select name from sysobjects where type = 'U'

21. Note: List all
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
Display result:
Type vender PCs
Computer A 1
Computer A 1
Cd B 2
Cd a 2
Mobile phone B 3
Mobile phone C 3

23. Description: Initialize table 1.
Truncate table Table1

24. Description: select a record from 10 to 15.
Select top 5 * from (select top 15 * from Table order by id asc) Table _ alias order by ID DESC
　　
Method for randomly selecting database records (using the Randomize function and using SQL statements)
For the data stored in the database, random numbers provide the above results, but they may be too slow. You cannot ask ASP to "find a random number" and print it out. In fact, a common solution is to create a loop as follows:
Randomize
RNumber = Int (rndx 499) + 1
　
While Not objRec. EOF
If objRec ("ID") = RNumber THEN
... The execution script...
End if
ObjRec. MoveNext
Wend
　
This is easy to understand. First, you get a random number ranging from 1 to 500 (assuming 500 is the total number of records in the database ). Then, you traverse each record to test the ID value and check whether it matches the RNumber. If conditions are met, the code starting with the THEN keyword is executed. If your RNumber is equal to 495, it takes a long time to repeat the database. Although the number 500 looks a little larger, it is still a small database than a more robust enterprise solution, which usually contains thousands of records in a database. At this time, it will not die?
With SQL, you can quickly find an accurate record and open a recordset that only contains the record, as shown below:
Randomize
RNumber = Int (rndx 499) + 1
　
SQL = "select * from MERs where id =" & rnumber
　
Set objrec = objconn. Execute (SQL)
Response. writernumber & "=" & objrec ("ID") & "& objrec (" c_email ")
　
You do not need to write rnumber and ID. You only need to check the matching conditions. As long as you are satisfied with the work of the above code, you can operate "random" records on demand. Recordset does not contain any other content, so you can quickly find the record you need, which greatly reduces the processing time.
Random Number again
Now you are determined to squeeze the last drop of the random function, you may retrieve multiple random records at a time or want to use records within a certain random range. By extending the standard random example above, you can use SQL to cope with the above two situations.
To retrieve several randomly selected records and place them in the same recordset, you can store three random numbers and query the database to obtain records matching these numbers:
SQL = "Select * FROM MERs Where ID =" & RNumber & "or ID =" & RNumber2 & "or ID =" & RNumber3
　
If you want to select 10 records (maybe the list of 10 links at each page loading), you can use BETWEEN or mathematical equations to select the first record and the appropriate number of incremental records. This operation can be completed in several ways, but the Select statement only shows one possibility (the ID here is the automatically generated number ):
SQL = "Select * FROM MERs Where ID BETWEEN" & RNumber & "AND" & RNumber & "+ 9"

Note: The above code is not executed to check whether there are 9 concurrent records in the database.

　
Read several records randomly, tested
Access Syntax: Select top 10 * From table name orDER BY Rnd (id)
SQL server: select top n * from table name order by newid ()
Mysqlelect * From table name order By rand () Limit n
Access left connection syntax (the left connection is used in recent development, Access does not help anything, there is no Access SQL instructions on the internet, only test by yourself, now write down for future check)
Syntax elect table1.fd1, table1, fd2, table2.fd2 From table1 left join table2 on table1.fd1, table2.fd1 where...
Use SQL statements to display long strings...
Syntax:
SQL database: select case when len (field)> 10 then left (field, 10) + '...' else field end as news_name, news_id from tablename
Access Database: Select iif (len (field)> 2, left (field, 2) + '...', field) FROM tablename;
　
Conn. Execute description
Execute Method
This method is used to execute SQL statements. This method is used in the following two formats based on whether the record set is returned after an SQL statement is executed:
1. When an SQL query statement is executed, the query records are returned. Usage:
Set object variable name = connection object. Execute ("SQL query language ")
After the Execute method is called, the record Set object is automatically created and the query results are stored in the record object. The Set method is used to assign the record Set to the specified object for storage, later, the object variable represents the record set object.

2. When the SQL operator language is executed, no record set is returned. The usage is as follows:
Connection object. Execute "SQL operational statement" [, RecordAffected] [, Option]
· RecordAffected is optional. A variable can be placed here. After the SQL statement is executed, the number of valid records is automatically saved to the variable. By accessing this variable, you can know how many records the SQL statement team has performed.
· Option. The value of this parameter is generally adshorttext, which is used to tell ADO that the first character after the Execute method should be interpreted as the command text. You can specify this parameter to make execution more efficient.

· BeginTrans, RollbackTrans, and CommitTrans Methods
These three methods are the methods provided by the connection object for transaction processing. BeginTrans is used to start a transaction; RollbackTrans is used to roll back the transaction; CommitTrans is used to submit all the transaction processing results, that is, to confirm the processing of the transaction.
Transaction processing can regard a group of operations as a whole. Transaction processing is successful only after all statements are successfully executed. If one of the statements fails to be executed, the entire process will fail, and restore the status before everywhere.
BeginTrans and CommitTrans are used to mark the start and end of a transaction. The statements between them are used as the statement for transaction processing. To determine whether the transaction is successfully processed, it can be achieved through the Error set of the connected object. If the number of members in the Error set is not 0, an Error occurs and the transaction fails to be processed. Each Error object in the Error set represents an Error message.