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

In this issue, the editor will bring you what are the common SQL sentences. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

(1) update data records:

Sql= "update data Table set Field name = Field value where conditional expression"

Sql= "update data Table set Field 1 = value 1, Field 2 = value 2... Field n = value nwhere conditional expression"

(2) add data records:

Sql= "insertinto data Table (Field 1, Field 2, Field 3...) values (value 1, value 2, value 3...)"

Sql= "insertinto destination data Table select*from Source data Table" (add records from the source data table to the target data table)

(3) Delete data records:

Sql= "deletefrom data Table where conditional expression"

Sql= "deletefrom data Table" (delete all records of the data table)

(4) data record filtering:

Sql= "select*from data Table where Field name = Field value orderby Field name [desc]"

Sql= "select*from data Table where Field name like'% Field value% 'orderby Field name [desc]"

Sql= "selecttop10*from data Table where Field name orderby Field name [desc]"

Sql= "select*from data Table where field name in ('value 1') 'value 2'')"

Sql= "select*from data Table where Field name between value 1and value 2"

(v) Establishment and deletion of data tables:

CREATETABLE data Table name (field 1 type 1 (length), field 2 type 2 (length) …)

Example: CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ())

DROPTABLE Datasheet name (permanently delete a Datasheet)

(6) Statistical function of data record:

AVG (field name) to get a table column average

COUNT (* | Field name) Statistics on the number of rows of data or the number of rows of data with values in a column

MAX (field name) gets the maximum value of a table column

MIN (field name) gets the minimum value of a table column

SUM (field name) adds the values of the data column

The method of referencing the above function:

Sql= "selectsum (field name) as alias from datasheet where conditional expression"

Setrs=conn.excute (sql)

Use rs ("alias") to get the value of the system, and other functions are the same as above.

(6) copy the tables of the database

Select * into new table name from the table name of the table to be replicated where 1: 2

To make a complete copy, just go to where 1 and 2.

(7) Delete a column of a table

Alter table bankbill drop column zsl

Alter table xx alter/add/drop column xx

(8) Fuzzy query

Sql= "select top 10 * from data Table where Field name order by Field name [desc]"

Find the top 10 records in the database

Sql= "select top n * form data sheet order by newid ()"

The method of randomly fetching several records in the database

Top n is the number of records to be taken out.

Sql= "select * from data Table where field name in ('value 1') 'value 2')"

These are the common SQL sentences that Xiaobian shares with you. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.