MySQL Data manipulation

Inserting data

Form 1:

Insert into table name (field name 1, field Name 2, ...). Values (value A1, value A2, ...), (Value B1, value B2, ...), ..... ；

Form 2:

Insert into Table name 1 (field name 1, field Name 2, ...). ) Select Field Name 1, field Name 2, .... from table name 2;

Form 3:

Insert into table name set field name 1= value 1, field name 2= value 2, ... ；

Loading external "form-neat" data:

Load data infile ' file full name (with path) ' into table table name;

Copy the structure and data of a table:

CREATE TABLE Table Name 1 SELECT * from table Name 2;

Delete data:

Delete from table name [WHERE condition] [order sort] [limit limit];

The table name of the truncate [table], which is used to directly delete the entire table (structure) and recreate the table.

To modify the data:

Update table name set field name 1= Value expression 1, field name 2= value Expression 2, .... [Where Condition] [Order Sort] [Limit limit] ；

Query data:

SELECT [All | distinct] field or expression list [FROM clause] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [limit clause];

All: Duplicate allowed (default)

Distinct: Repeat not allowed (deduplication)

From sentence: Specify the source of the data

Where sentence:

Arithmetic operators: +-*/%

Comparison operators: > >= < <= = (equals) <> (not equal)

= = (equals, MySQL extension),! = (not equal to, MySQL extension)

Logical operators: and (with) or (or) not (non)

Logical judgment

Judge True:xx is True

Judge Fale:xx is False

Determination of NULL values

Judge Null:xx is null

Judged to be non-null: XX is NOT null

Between syntax: The value of the field xx is between the value 1 and the value 2 (inclusive), equivalent to: xx >= value 1 and xx<= value 2;

XX between value 1 and value 2;

int syntax: equivalent to XX = value 1 or xx = value 2 or xx = value 2

XX in (value 1, value 2, ...) ；

Like syntax: Fuzzy Lookup

% It means: Any character that represents any number

_ The meaning is: any character representing 1 characters

Like '%ab\%cd% '//What you're looking for here: characters that contain AB%CD characters

Like ' \_ab% '//What you're looking for here: _ab the beginning of the character

Like '%ab\ ' cd% '///What you're looking for here: characters containing ab ' CD characters

Example: Like '% keyword% '

GROUP BY: The maximum, minimum, average, and sum values of certain fields within a set. other fields, which are usually not available .

Group By field 1 Sort Way 1, field 2 sort by 2, .....

COUNT (*): Counts the number in a group, usually with "*" Parameters

Max (field name): Gets the maximum value in the Group for this field.

Min (field name): Gets the minimum value in the Group for this field.

sum (field name): Gets the sum of the fields in the group.

AVG (field name): Gets the average of the field in the group.

Group_concat (field name): Show parts that are not showing

Having sentence: where is a "conditional judgment" on the value of a table's field

Used in the back of group BY, the function and where are similar

Sentence by order: sort

Positive order: ASC (default), can omit

Reverse: DESC

Limit sentence:

Limit [start line number start], number of rows to be fetched num

MySQL Data manipulation