Shulou(Shulou.com)05/31 Report--

This article introduces what MYSQL commonly used SQL sentences are, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

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 sql server

-create a device for backing up data

USE master

EXEC sp_addumpdevice 'disk',' testBack','c testBack', Mssql7backupMyNwindshield 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 (create a new table using the 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. 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: 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 is immutable and must be deleted and rebuilt if you want to change it.

9. Description:

Create a 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)

Deleting: delete from table1 where ran

Updating: update table1 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: select count * as totalcount from table1

Summation: select sum (field1) as sumvalue from table1

Average: select avg (field1) as avgvalue from table1

Maximum: select max (field1) as maxvalue from table1

Minimum: 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, 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 outer join:

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 outer join:

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 outer join:

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 top 0 * 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 adddate from table where table.title=a.title a.username from table a b.adddate, (select max (title) adddate from 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 where table1.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.c inner 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 where tb.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 from tableC)

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 by col1,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) FROM tablename 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. If the conditions are met, it will be executed by the THEN.

The piece of code at the beginning of the keyword. 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 the more stable enterprise solution, which is usually

There are 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 is a big deal.

The processing time is reduced.

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 (ID here 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 top 10 * From table name ORDER BY Rnd (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 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where...

Use SQL statements with. Instead of overly long string display

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) > 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. You can determine whether the transaction is successful by connecting the Error collection of objects. If the number of members of the Error set is not 0,

It means that an error occurred and the transaction failed. Each Error object in the Error collection represents an error message.

About what MYSQL commonly used SQL sentences are shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.