SQL Server Tutorial: Classic SQL Statement Collection

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, a brief introduction to the underlying statement: 1, Description: Create a database

2. Description: Delete Database

Drop Database dbname

3. Description: Backup SQL Server---the device that created the backup data

Use masterexec sp_addumpdevice ' disk ', ' testback ', ' C:/mssql7backup/mynwind_1.dat '

---start Backup

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 a new table with the old table) B:

CREATE TABLE tab_new as Select Col1,col2 ... from tab_old definition only

5. Description: Delete new table

6. Description: Add a column

Alter table tabname Add column col type

Note: Columns cannot be deleted after they are added. The DB2 column plus the data type cannot be changed, and the only thing that can change is the length of the add varchar type. 7. Description: Join the PRIMARY key:

Description: Delete primary key:

8. Description: Create INDEX:

To delete an index:

Drop INDEX Idxname

Note: The index is immutable and you want to change it to be deleted and built again. 9. Description: Create a view:

To delete a view:

Drop View ViewName

10, Description: A few simple main SQL statement selection:

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

Find:

SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, check the 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

Biggest:

Select Max (field1) as MaxValue from table1

Minimum:

Select min (field1) as MinValue from table1

11. Description: Several advanced query operators the UNION operator a:union a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any repeated rows in the table. When all is used with the Union (that is, union All), repeated rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2. The EXCEPT operator of the b:except operator derives a result table by containing all rows in TABLE1 but not in TABLE2 and eliminating all repeated rows. When all is used with EXCEPT (EXCEPT all), repeated lines are not eliminated. The c:intersect operator INTERSECT operator derives a result table by simply including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When all is used with INTERSECT (INTERSECT all), repeated lines are not eliminated. Note: Several query result rows that use an operation word must be consistent. 12. Description: Use outer connection A, left OUTER join: Outer join (left connection): The result set contains the matching rows 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.C

B:right outer join: Right outer join (right Join): The result set contains both the matching join row for the join table and all rows of the right join table. C:full outer join: Full OUTER join: Contains not only the matching rows of the symbolic join table, but also all the records in the two join tables. Second, let's look at some nice SQL statements. 1, Description: Copy table (copy structure only, source table name: A new table name: B) (Access available) Law one:

SELECT * to B from a where 1<>1

Law II:

Select top 0 * into B from a

2. Description: Copy 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: Copy of table across databases (detailed data using absolute path) (Access available)

Insert into B (A, B, c) Select d,e,f from B in ' verbose database ' where condition

Examples:.. From B in ' "&server.mappath (". ") & "/data.mdb" & "' Where." 4, Description: 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, Description: 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, Description: External 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, Description: Online view query (table name 1:a) 8, Description: Between use method, between limit the query data range contains the boundary value, not between does not contain

SELECT * FROM table1 where time between time1 and Time2select A,b,c, from table1 where a is not between value 1 and value 2

9. Description: In usage

SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have information

Delete from table1 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

11, Description: Four table linked to check the problem:

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, Description: Schedule five minutes in advance to remind SQL:

SELECT * FROM Schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

13, Description: 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, Description: The first 10 records

Select Top Ten * form table1 where range

15, Description: Select the same data in each set of B value corresponding to the largest record of the full information (similar to the use of this method can be used for the monthly forum rankings, monthly hot product analysis, ranked by the scores of 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, Description: Contains all the rows in the TableA but not in the TableB and TableC and eliminates all repeated rows to derive a result table

(select a from TableA) except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data

Select Top * FROM tablename ORDER by NEWID ()

18, Description: Random selection of records

Select NEWID ()

19, Description: Delete repeated records

Delete from TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

20, Description: List all the table names in the database

21, Description: List of all the

Select name from syscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, in the Type field, case can easily implement multiple choices, similar to case in select.

Select Type,sum (case vender if ' A ' then PCs else 0 end),

SUM (case vender if ' C ' then PCs else 0 end),

From tablename GROUP By type

Display results: Type vender pcs pc a 1 pc a 1 Disc B 2 disc a 2 mobile phone B 3 Mobile phone C 3 23, Description: Initialize table table1

TRUNCATE TABLE table1

24. Description: Select records from 10 to 15

Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

Random selection of database records (using the Randomize function, implemented by SQL statements) for data stored in the database, the random number characteristics can give the above effect, but they may be too slow. You can't ask the ASP to "find a random number" and print it out. The most common solution is to create loops such as the ones seen below:

This is very easy to understand. First, you remove a random number within the range of 1 to 500 (if 500 is the total number of records in the database). Then, you traverse each record to test the value of the ID and check if it matches the rnumber. If the condition is met, run the piece of code that was started by then keyword. If your rnumber equals 495, it can take longer to cycle through the database. Although the 500 figure looks bigger, it's a small database compared to a more solid corporate solution, which typically includes thousands of records in a single database. 　　You're not going to die now? With SQL, you'll be able to find accurate records very quickly and open a recordset that includes only that record, as seen in the following:

You don't have to write Rnumber and ID, you just need to check the match. Just because you're comfortable with the code above, you can "randomly" record your own on-demand operations. The recordset does not include anything else, so you can find the records you need very quickly so that the processing time is greatly reduced. Talking about random numbers. Now you're determined to squeeze the last drop of the random function, you may take multiple random records at one time or you want to use a record in a certain random range. 　　The above standard random demo sample extension can be used to deal with the above two cases of SQL. To take a few randomly selected records and store them in the same recordset, you can store three random numbers and then query the database for records that match those numbers:

If you want to select 10 records (perhaps a list of 10 links each time the page is loaded), you can use between or mathematical equations to select the first record and the appropriate number of incremental records. This can be done in several ways, but the SELECT statement only shows one possibility (the ID here is the number that was actively generated by itself):

　　Note: The above code is not intended to check for 9 concurrent records within the database. Randomly read several records, tested access syntax: SELECT top * FROM table name ORDER by RND (ID) SQL Server syntax: SELECT TOP n * FROM table name ORDER by NEWID () MySql Syntax: Select * FROM table name Order by rand () Limit n Access left connection syntax (recent development to use left connection, Access Help nothing, online without access SQL instructions, just have their own test, now write down for later check) syntax: Se Lect table1.fd1,table1,fd2,table2.fd2 from table1 left joins table2 on TABLE1.FD1,TABLE2.FD1 where ... Use SQL statements with ... Replace too long string display syntax: SQL database: Select Case time 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 describes the Execute method used by this method to run SQL statements. Whether the recordset is returned after the SQL statement is run, the format for using the method is divided into the following two types: 1. When you run the SQL query statement, the recordset that the query gets is returned. Use the method: Set object variable name = Connection object. 　　　　After execute (SQL query Language) Execute method invocation, the Recordset object is created on its own initiative, the query results are stored in the Record object, the recordset is assigned to the specified object by the set method, and later the object variable represents the Recordset object. 2. When you run SQL's operational language, there is no return for the recordset. Use this method as: Connection object. Execute "SQL Operational Statement" [, recordaffected][, option] Recordaffected is optional, this out can place a variable, after the SQL statement runs, the number of records that are in effect is actively saved to the variable. Through the interviewThis variable, you can know how many records of the SQL statement team have been manipulated. option, the value of which is typically adCmdText, which tells ADO that the first character after the Execute method should be interpreted as the command text. You can make the operation more efficient by specifying the number of parameters. The three methods of the BeginTrans, RollbackTrans, and CommitTrans methods are the methods provided by the connection object for transaction processing.　　BeginTrans is used to start a thing; RollbackTrans is used to rollback a transaction; CommitTrans is used to commit all transaction results, that is, the processing of the transaction is confirmed.　　The transaction is able to treat a set of operations as a whole, and the transaction is successful only after all statements have been successfully run, and if one of the statements fails, the entire process fails, and the status is restored to the previous state. BeginTrans and CommitTrans are used to mark the start and end of a transaction, in which statements between these two are statements that are transacted. If the transaction succeeds, it can be implemented by the error collection of the connection object, if the number of members of the error collection is not 0, then an error occurs and the transaction fails. Each Error object in the Error collection, which represents a fault message.