Summary of commonly used SQL statements in Mysql, and mysqlsql statements

Summary of commonly used SQL statements in Mysql, and mysqlsql statements

(1) Data Record Filtering:

SQL = "select * from data table where field name = Field Value order by field name [desc]" SQL = "select * from data table where field name like '% Field Value % 'order by field name [desc] "SQL =" select top10 * from data table where field name order by field name [desc] "SQL =" select * from data table where field name in ('value 1 ', 'value 2', 'value 3') "SQL =" select * from data table where field name between value 1 and value 2"

(2) update data records:

SQL = "update data table set field name = field value where condition expression" SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"

(3) Delete data records:

SQL = "delete from data table where condition expression" SQL = "delete from data table" (delete all records of data table)

(4) add data records:

<Span style = "font-size: 12px;"> <span style = "color: #000000;"> SQL = "insertinto data table (Field 1, Field 2, field 3 ...) Values (value 1, value 2, value 3 ...) "SQL =" insertinto target data table select * from source data table "(add records of source data table to target data table) </span>

(5) statistical functions of data records:

AVG (field name) obtains the average COUNT (* | field name) of a table column for statistics on the number of rows or the number of rows with values in a column for statistics MAX (field name) obtain the maximum value MIN (field name) in a table column to obtain the smallest value SUM (field name) in a table column to add the values in the data column and reference the above functions: SQL = "select sum (field name) as Alias from data table where condition expression" setrs = conn. excute (SQL) uses rs ("alias") to obtain the value of the system. Other functions are used in the same way.

(6) Create and delete data tables:

Create a data table: CREATETABLE data table name (Field 1 type 1 (length), Field 2 type 2 (length )......) Example: create table tab01 (namevarchar (50), datetimedefanow now () delete data TABLE: DROPTABLE data TABLE name (permanently delete a data TABLE)