Summary of commonly used sql statements in Mysql

(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('value1','value2','value3')"
sql = "select * from data table where field name between value1 and value2"
(2)UpdateData record:
sql="update data tableset 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 conditional expression"
(3)Deletedata record:
sql ="delete from data table where conditional expression"
sql="delete from data table" (delete all records in the data table)
(4) Add data records:
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" (put the source data The records of the table are added to the target data table)
(5) Data record statisticsFunction:
AVG(field name) gets a tablecolumn average
COUNT(*|field name) counts the number of data rows or counts the number of data rows with values ​​in a certain column
MAX(field name) obtains 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 columns
Reference Method of the above function:
sql="select sum( Field name) as alias from data table where conditional expression "
setrs=conn.excute(sql)
Use rs("alias") to obtain the statistical value, Other functions are used as above .
(5) Creation and deletion of data table:
CREATETABLE data table name (field 1 type 1 (length), field 2 type 2 (length)...)
Example: CREATE TABLE tab01 (namevarchar ( 50), datetimedefaultnow ())
DROPTABLE data table name (permanently delete a data table)

The above is the detailed content of Summary of commonly used sql statements in Mysql. For more information, please follow other related articles on the PHP Chinese website!