Personal access Information-sql statement

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 Database

CREATE DATABASE Database-name

2. Description: Delete Database

Drop Database dbname

3. Description: Back up SQL Server

---to create a device that backs up 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],..)

To create a new table from an existing table:

A:create table tab_new like Tab_old (create new table with old table)

B:create table tab_new as Select Col1,col2 ... from tab_old definition only

5. Description: Delete 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. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type.

7. Description: Add primary key: Alter table TabName Add primary key (COL)

Description: Delete primary key: Alter table tabname drop primary key (COL)

8. Description: Create INDEX: [unique] index idxname on tabname (col ...)

Drop INDEX: Idxname

Note: The index is immutable and you must remove the rebuild if you want to change it.

9. Description: Creating view: Create VIEW viewname AS SELECT statement

Delete view: Drop View ViewName

10, Description: A few 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

Updated: 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

Maximum: Select Max (field1) as MaxValue from table1

Min: select min (field1) as MinValue from table1

11. Description: Several advanced query operation words

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.

B:except operator

The 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.

C:intersect operator

The 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.

12. Description: Use external connection

A, LEFT OUTER join:

Left OUTER join (left JOIN): The result set includes a matching row for 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 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.

Next, 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 * into 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 (use absolute path for specific data) (Access 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: 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)

SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between usage, between limits the query data range 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 is not between value 1 and value 2

9. Description: How to use

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 before the reminder

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 in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, 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: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate 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 duplicate 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

Select name from sysobjects where type= ' U '

21, Description: List of all the

Select name from syscolumns where TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, 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

Show Results:

Type Vender pcs

PC A 1

PC A 1

Disc B 2

Disc A 2

Mobile B 3

Mobile 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

method of randomly selecting database records (using the Randomize function, implemented by SQL statements)

The random number feature gives the above effect to the data stored in the database, but they can be too slow. You can't ask the ASP to "find a random number" and print it out. A common solution is to create a loop that looks like this:

Randomize

Rnumber = Int (rnd*499) +1

While not objrec.eof

If Objrec ("ID") = Rnumber Then

... Here is the Execute script ...

End If

Objrec.movenext

Wend

It's easy to understand. First, you take out a random number within the range of 1 to 500 (assuming that 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, execute the piece of code that starts with the then keyword. If your rnumber equals 495, it can take longer to cycle through the database. Although the 500 figure looks larger, it is a small database compared to a more robust enterprise solution, which typically contains thousands of records within a database. You're not going to die now?

With SQL, you can quickly find the exact record and open a recordset that contains only that record, as shown here:

Randomize

Rnumber = Int (rnd*499) + 1

SQL = "SELECT *" from Customers WHERE & Rnumber

Set Objrec = objConn.Execute (SQL)

Response.writernumber & "=" & Objrec ("ID") & "" & Objrec ("C_email")

You don't have to write Rnumber and ID, you just need to check the match. As long as you are satisfied with the work of the above code, you can record the "random" operation on demand. The recordset does not contain anything else, so you will soon be able to find the records you need so that the processing time is greatly reduced.

Talk about random numbers again

Now that you are determined to squeeze the last drop of the random function, you may be able to take multiple random records at one time or to use a certain random range of records. By extending the standard random example above, you can use SQL to deal with both of these situations.

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:

SQL = "SELECT * from Customers WHERE & Rnumber &" or & RNumber2 & "or & RNumber3

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 possible (the ID here is an automatically generated number):

SQL = "SELECT * from Customers WHERE ID between" & Rnumber & "and" & Rnumber & "+ 9"

Note: The above code is not intended to check for 9 concurrent records within the database.

Randomly read a number of records, tested

Access syntax: SELECT top * FROM table name ORDER by Rnd (ID)

SQL server:select Top n * FROM table name ORDER by NEWID ()

Mysql

Access left connection syntax (recently developed to use left connection, Access Help nothing, no SQL instructions for access online, only own test, now write it down for later check)

Grammar

Use SQL statements with ... Instead of a long string display

Grammar:

SQL database: Select Case when Len (field) >10 and 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 the SQL statement. Depending on whether the recordset is returned after the SQL statement executes, the format of the method is divided into the following two types:

1. When you execute a SQL query statement, the recordset that the query gets is returned. To use:

Set object Variable name = Connection object. Execute ("SQL Query Language")

After the Execute method call, the Recordset object is automatically created, 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 execute an operational language for SQL, there is no return for the recordset. The usage at this time is:

The Connection object. Execute "SQL Operational Statement" [, recordaffected][, Option]

· Recordaffected is optional, this out can place a variable, after the SQL statement executes, the number of records that are in effect is automatically saved to the variable. By accessing the variable, you 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. By specifying this parameter, you can make execution more efficient.

· BeginTrans, RollbackTrans, CommitTrans methods

These three methods are the methods provided by the connection object for transaction processing. BeginTrans 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.

A transaction can treat a set of operations as a whole, and the transaction is successful only after all statements have been executed successfully, 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 is successful, 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.

Personal access Information-sql statement