MySQL additions and deletions, check statements

Increase:

All field names are specified in the INSERT statement

Syntax:INSERT into table name (field name 1, field Name 2, ...) Values (value 1, value 2, ...) ）；

1.1.2 Field name is not specified in INSERT statement

If you do not specify a field name, the order of the added values is exactly the same as the order of the fields in the table.

INSERT  into VALUES (Value 11, value 2, ...) ）；

Add data for the specified field of the table :

Adds data to a specified field, which adds a value only to a partial field, while the value of the other field is the default value when the table is defined. As you can see from the results, the grade field value of the new record is NULLbecause the default value is added automaticallywhen you add a value that indicates grade .

INSERT  into table name (Field 1, Field 2, ...) Values(value 1, value 2, ...) ）

Additional notation for INSERT statements

INSERT  into  SET field name 1= value 1[, field name 2= value 2, ... ]

Add more than one data at a time

INSERT  into table name [(Field name 1, field Name 2, ...)   values (value 1, value 2, ...) ), (value 1, value 2, ...) ），... (value 1, value 2, ...) )

"Deleting"--deleting data (for example)

DELETE  from [WHERE condition Expression

Delete part of data

that is to delete the specified part of the data, you need to use The WHERE clause specifies the criteria for deleting records.

DELETE  from class WHERE id=3;

Delete all data

if the WHERE statement is not used in the DELETE statement , all records in the table are deleted.

DELETE  from table name         But the table is still in

"Change"--Update the data

Updating data refers to modifying existing data in a table. (for example)

Update updates some data

updates the specified record in the specified table, using the The WHERE clause to specify.

Update Set sno=1where=1;       (the bottom 3 is because I made all of the following changes first)

Update all data

If the where clause is not used in the update statement , the specified fields of all records in the table are updated.

UPDATE = 3;

Check: (for example)

4.1.1 Querying all fields

Change the order of field types :

4.1.2 Use (' * ') wildcard characters in SELECT statements instead of all fields

4.1.3 querying specified fields

Change the order of fields:

4.2.1 Query with relational operators

The Where is followed by a number:

1.

=

Equals

<>	Not equal to

!=	Not equal to the same effect as above

<

Less than

<=	Less than or equal

>	Greater than

>=	Greater than or equal

the Where is followed by a character:

4.2.2 with in keyword query

Second Type * can be changed to field name

4.2.3 query with between and keywords

(including both sides)

When the order is reversed not

4.2.4 Null value query

When you change to is isn't

4.2.5 with DISTINCT keyword query ( Obvious, clear)DISTINCT

4.2.6 queries with the LIKE keyword

(1) percent sign (%) wildcard character

For example: The string "k%" matches a string of any length starting with the character K :

For example: the string "K%7" denotes a string beginning with the character K, ending with 7 ;

For example: The string "%2%" represents a string containing the character " 2" , regardless of where the string " 2" is located.

(2) The underscore (_) wildcard character

For example, the string "k00_" matches characters starting with the string " k00" with a length of 4

When changing the length:

For example, the string "K__1" matches a string containing two characters between " k" and " 1",

When changing the length:

For example: there can be no spaces between consecutive "_",

4.2.7 Multi-criteria queries with and keywords

4.2.8 Multi-criteria Query with OR keyword

4.2.9 The use of or with and

when used with or and and , the and has precedence over OR, so when they are used together, they are first calculated expressions on and on both sides, and then on OR Both sides of the expression.

For example, query The price is 2.40 and the quantity is a total of $ or the price for 1.40 fruit Name:

4.3 Advanced query

4.3.1 aggregation function

(1) count () function: Count the number of bars counted (total) to see the number of rows.

(2)sum () function: Find out the sum of all the values in a field in a table

(3)AVG () function: Find the average of all values in a field in a table average

(4)max () function: Find the maximum value of all values in a field in a table

(5)min () function: Find the minimum value of all values in a field in a table

4.3.2 to sort the results of a query

ASC indicates ascending order,

Desc indicates descending order

4.3.3 Group Query

(1) grouping by using GROUP by alone

(2) GROUP by and aggregate functions are used together

For example, the fruit table follows Stack Group queries how many records each group has:

(3) GROUP by and having keywords used together

For example: will be Fruit The table is grouped by the Stack field, querying the sum of the number field values less than

4.3.4 to limit the number of query results by using limit

The LIMIT can be followed by two parameters, the first parameter "offset" represents the offset, if the offset is 0, from the first record of the query results, the offset is 1 starts with the second record in the query result, and so on. OFFSET is an optional value, the default value is 0, and the second parameter , " number of records " Represents the number of bars that specify the return query record.

For example: Query the first three records in the table:

For example, the third to fourth record in the query table:

4.4 Aliases for tables and fields

4.4.1 Aliases for tables

to be Fruit The table is an alias F andqueries the value of the Price field in the F table for 2.40 Records ;

4.4.1 to alias a field

For example I will IDs takes aliases as IDs

MySQL additions and deletions, check statements