Shulou(Shulou.com)06/01 Report--

The sections listed 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, a brief introduction to the basic statement:

1. Description: create a database

CREATE DATABASE database-name

2. Description: delete the database

Drop database dbname

3. Description: backup sqlserver

-create a device for backing 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],..)

Create a new table based on an existing table:

A:create tabletab_new like tab_old (create a new table using the old table)

B:create tabletab_new as select col1,col2... From tab_old definition only

5. Description: delete the new table droptable tabname

6. Description: add a column

Alter table tabname add column col type

Note: columns cannot be deleted after they are added. The data type cannot be changed after the column is added in DB2, and the only thing that can 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: Altertable tabname drop primary key (col)

8. Description: index creation: create unique index idxname on tabname (col … (.)

Delete index: dropindex idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

9. Description: create view: createview viewname as select statement

Delete View: drop viewviewname

10. Description: a few simple basic sql statements

Select: select * from table1 where range

Insert: insert intotable1 (field1,field2) values (value1,value2)

Deleting: delete fromtable1 where ran

Updating: updatetable1 set field1=value1 where ran

Look up: select * from table1 where field1 like'% value1%'-like's syntax is very exquisite, look up the information!

Sort: select * from table1 order by field1,field2 [desc]

Total: selectcount (*) as totalcount from table1

Summation: selectsum (field1) as sumvalue from table1

Average: selectavg (field1) as avgvalue from table1

Maximum: selectmax (field1) as maxvalue from table1

Minimum: selectmin (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, such as TABLE1 and TABLE2, and eliminating any duplicate rows in the table. When ALL is used with UNION (that is, UNION ALL), duplicate lines 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 lines are not eliminated.

C: INTERSECT operator

The INTERSECT operator derives a result table by including only the rows in both TABLE1 and TABLE2 and eliminating all duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate lines are not eliminated.

Note: several query result rows that use operands must be consistent.

12. Description: use external connections

A 、 left outerjoin:

Left outer join (left join): the result set includes the matching rows of the join table, as well as all rows of the left join 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 outerjoin:

Right outer join (right join): the result set includes both the matching join rows of the join table and all rows of the right join table.

C:full outerjoin:

Full outer join: includes not only the matching rows of the symbolic join table, but also all records in the two join tables.

Secondly, let's take a look at some good sql statements.

1. Description: copy table (only copy structure, source table name: a new table name: B) (available for Access)

Law 1: select * into b from a where 11

Method 2: select top0 * into b from a

2. Description: copy table (copy data, source table name: a target table name: B) (available for Access)

Insert into b (a, b, c) select d from b

3. Description: copy of tables across databases (use absolute path for specific data) (Access is available)

Insert into b (a, b, c) select djiggy e from b in 'specific database' where condition

Example:.. from b in' "& Server.MapPath (". ") &"\ data.mdb "&" 'where.. "

4. Description: sub-query (table name 1VLA, table name 2RB)

Select a dagger bjorc from a where an IN (select d from b) or: select a recital b recital c from a where an IN (1m 2pm 3)

5. Description: displays the article, author and last reply time

Select a.title adddatefrom table where table.title=a.title a.username from table a b.adddate, (select max (title) adddatefrom table where table.title=a.title) b

6. Description: external join query (table name 1VR a, table name 2RB)

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 1pora)

Select * from (SELECT a FROM c FROM a) T where t.a > 1

8. Description: the use of between. Boundary values are included when between limits the scope of query data, while not between does not include

Select * from table1 where time between time1 and time2

Select a dint bpenc, from table1 where a not between numeric 1 and numeric 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 to delete information that has not been found in the secondary table in the primary table

Delete from table1 where not exists (select * from table2 wheretable1.field1=table2.field1)

11. Description: joint check of the four tables:

Select * from a left inner join b on a.a=b.b right inner join c on a.a=c.cinner join d on a.a=d.d where.

12. Note: five minutes in advance of the schedule

SQL: select * from scheduling where datediff ('minute',f start time, getdate ()) > 5

13. Description: a sql statement completes database paging

Select top 10 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 10 * form table1 where range

15. Description: select all the information corresponding to the largest record of an in each set of data with the same b value (similar usage can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.).

Select a dint bjorn c from tablename ta where a = (select max (a) from tablename tb wheretb.b=ta.b)

Description: derives a result table by including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows

(select a from tableA) except (select a from tableB) except (select a fromtableC)

17. Description: 10 pieces of data are randomly selected

Select top 10 * from tablename order by newid ()

18. Description: randomly select records

Select newid ()

19. Description: delete duplicate records

Delete from tablename where id not in (select max (id) from tablename group bycol1,col2,...)

Description: list all the table names in the database

Select name from sysobjects where type='U'

21. Description: list all the items in the table

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

22, description: type, vender, pcs fields are listed and arranged in type fields. Case can easily achieve multiple choices, 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) FROMtablename group by type

Display the results:

Type vender pcs

Computer A1

Computer A1

CD B 2

CD A 2

Mobile phone B3

Mobile phone C 3

23. Description: initialization table table1

TRUNCATE TABLE table1

24. Description: select records from 10 to 15

Select top 5 * from (select top 15 * from table order by id asc) table_ alias order by id desc

The method of randomly selecting database records (using Randomize function, realized by SQL statement)

Random digit performance gives the above effect for data stored in the database, but they may be too slow. You can't ask ASP to "find a random number" and print it out. In fact, a common solution is to establish a loop like this:

Randomize

RNumber = Int (Rnd*499) + 1

While Not objRec.EOF

If objRec ("ID") = RNumber THEN

... Here is the execution script.

End if

ObjRec.MoveNext

Wend

It's easy to understand. First, you take a random number in the range of 1 to 500 (assuming 500 is the total number of records in the database). Then you traverse each record to test the value of ID and check to see if it matches RNumber. Execute the block of code that starts with the THEN keyword if the condition is met. If your RNumber equals 495, it takes a long time to loop through the database. Although the number 500 may seem large, it is a small database compared to a more stable enterprise solution, which usually contains thousands of records in a single database. Wouldn't you be dead by then?

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

Randomize

RNumber = Int (Rnd*499) + 1

SQL = "SELECT * FROM Customers WHERE ID =" & 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 operate "random" records as needed. Recordset contains nothing else, so you can quickly find the records you need, which greatly reduces processing time.

Talking about Random number again

Now that you are determined to drain the last drop of the Random function, you may take out more than one random record at a time or want to use a random range of records. By extending the standard Random example above, you can use SQL to deal with the above two situations.

To take out several randomly selected records and store them in the same recordset, you can store three random numbers and query the database for records that match those numbers:

SQL = "SELECT * FROM Customers WHERE ID =" & RNumber & "OR ID =" & RNumber2 & "OR ID =" & RNumber3

If you want to select 10 records (perhaps a list of 10 links per page load), 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 shows only one possibility (here ID is an automatically generated number):

SQL = "SELECT * FROM Customers WHERE ID BETWEEN" & RNumber & "AND" & RNumber & "+ 9"

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

Randomly read several records and tested them

Access syntax: SELECT top10 * From table name ORDER BYRnd (id)

Sql server:select top n * from table name order by newid ()

Mysql:Select * From table name Order By rand () Limit n

Access left join syntax (recently developed to use left link, Access help nothing, there is no SQL description of Access on the Internet, just test it yourself, write it down now for later check)

Syntax: Selecttable1.fd1,table1,fd2,table2.fd2 From table1 left join table2 ontable1.fd1,table2.fd1 where...

Use SQL statements with. Instead of overly long string display

Syntax:

SQL database: selectcase when len (field) > 10 then left (field,10) + '...' Else field end asnews_name,news_id from tablename

Access database: SELECTiif (len (field) > 2Mae left (field,2) + '..., field) FROM tablename

Conn.Execute description

Execute method

This method is used to execute SQL statements. Depending on whether the recordset is returned after the SQL statement is executed, this method can be used in the following two formats:

1. When the SQL query statement is executed, the recordset obtained by the query is returned. The usage is:

Set object variable name = connection object .Execute (SQL query language)

After the Execute method is called, the recordset object is automatically created, and the query results are stored in the recordset object. Through the Set method, the recordset is assigned to the specified object for storage, and then the object variable represents the recordset object.

2. When executing the operational language of SQL, there is no return of the recordset. The usage at this time is:

Connection object .Execute SQL operational statement [, RecordAffected] [, Option]

RecordAffected is optional, this output can place a variable, after the execution of the SQL statement, the number of records in effect will be automatically saved to the variable. By accessing this variable, you can know how many records have been manipulated by the SQL statement team.

Option is optional, which usually takes a value of adCMDText, which is used to tell ADO that the first character after the Execute method should be interpreted as 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 is used to start a thing; RollbackTrans is used to roll back a transaction; and CommitTrans is used to commit all transaction results, that is, to confirm the transaction's processing.

A transaction can treat a set of operations as a whole, and the transaction is successful only if all statements are executed successfully; if one of the statements fails, the whole process fails and returns to the previous state everywhere.

BeginTrans and CommitTrans are used to mark the beginning and end of a transaction, and the statements between them are used as transaction statements. Whether the transaction is successful or not can be determined by connecting the Error collection of objects. If the number of members of the Error set is not 0, an error occurs and the transaction fails. Each Error object in the Error collection represents an error message.

/ / Primary key

Alter table tabelname add new_field_id int (5) unsigned default 0 not null auto_increment, add primary key (new_field_id)

/ / add a new column

Alter table T2 add d timestalter table infos add ex tinyint not null default'0'

/ / Delete columns

Alter table t2 drop column c

/ / rename the column

Alter table t1 change ab integer

/ / change the type of column

Alter table T1 change b b bigint not null;alter table infos change list list tinyint not null default'0'

/ / rename table

Alter table t1 rename t2

Indexing

Mysql > alter table tablename change depno depno int (5) not null;mysql > alter table tablename add index index name (field name 1 [, field name 2 …]) ; mysql > alter table tablename add index emp_name (name)

Add the index of the primary key

Mysql > alter table tablename add primary key (id)

Index with unique restrictions

Mysql > alter table tablename add unique emp_name2 (cardnumber)

Delete an index

Mysql > alter table tablename drop index emp_name

Modify the table:

Add fields:

Mysql > ALTER TABLE table_name ADD field_name field_type

Modify the original field name and type:

Mysql > ALTER TABLE table_name CHANGE old_field_name new_field_name field_type

Delete a field:

Mysql > ALTER TABLE table_name DROP field_name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.