PHP Learning Day11---database (3) Data sheet additions and deletions

Today we learned about the operation of the data table, and here are the notes.

First, the data operation language1. Add Data

There are several syntactic forms for adding data to a data table

　　　　　　Form 1:insert [INTO] table_name [(Field 1, Field 2, Field 3 ...)] VALUES (value 1, value 2, value 3 ...), (...), (. ...), (.) ......;

Form 2:replace [INTO] table_name [(Field 1, Field 2, Field 3 ...)] VALUES (value 1, value 2, value 3 ...), (...), (. ...), (.) ......;

Form 3:insert [INTO] table_name [(Field 1, Field 2, Field 3 ...)] Select field 1, Field 2, field 3......from other_table_name;

Form 4:insert [into] table_name SET field 1 = value, field 2= value, ...

Description

In the four methods, Form 1 is the most commonly used method, where its value can be a fixed value or the result of a function call, or a variable, if the corresponding field is a character or time type, then use single quotation marks directly. The use of Form 1 and form 2 is generally consistent, the only difference is that when the primary key or unique key of the inserted data is duplicated, Form 1 will error, form 2 will change the data. Form 3 is a simple way to add data from other tables to the current table, but you must be aware that the field list should correspond. The comparison of Form 4 is not commonly used.

　　　　　　

　　　　Mode 2:

　　　　　　

　　　　Load data (load)

Grammatical form:

　　　　　　　　　Load data infile "full data file path" into table table_name;

　　　　　　

2. Delete Data

Syntax form for deleting data:

　　　　　　　　Delete from table_name [where] [order by] [limit];

Description

Delete data is in the unit of behavior;

Take a Where condition, otherwise all data will be deleted;

Order by sort settings, which are usually used in conjunction with limit;

Limit to limit the number of rows of data to be deleted;

》》

3. Modify the Data

Syntax for modifying data:

　　　　　　　　Update table_name SET field 1= value, field 2= value ... [WHERE] [ORDER BY] [Limit];

Description

Modify the same with where at the same time, otherwise modify all the data;

If the value is a string or a time type, add single quotation marks;

　　　　　　

4. Querying Data

Because the content of the query is more, so the relevant knowledge of the query we separate as a chapter to record. (A large chapter below)

Second, data table query detailed1. Basic grammatical Form

The basic syntax form of a query statement:

　　　　　　　　　　Select [All or distinct] field or expression list [FROM clause] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [LIMIT clause]

Let us explain in detail the meaning and usage of each clause;

2.all and Distinct

　　　　Used to set the data for select, whether to eliminate duplicate rows, can not write, default to all

　　　　　　　All: means no elimination, that is, all are displayed, is the default value;

　　　　　　　　Distinct: elimination;

　　　　　　

3. Fields and Expressions list

field, is freely derived from the table, when the table was just set up;

The expression is similar to the content such as: 1,1+2+3, or Now () and other intrinsic functions;

Of course, we can use the AS alias setting for each result we insert.

　　　　　　

4.from clause

The FROM clause represents the data source that gets the data in the Select section---is actually the table in which to take the data, followed by the table is usually the name, or some other data source (junction table), where the from before the field must be the source table has a field name;

5.where clause

Description

The WHERE clause is a conditional filter on the data from the data source in the FROM clause, and the filtering mechanism is judged on a line-by-row basis, which is essentially the same as an if statement. Where is dependent on the FROM statement.

Various operators are commonly used in where:

　　　　　　　　　Arithmetic operator: +-*/%;

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

　　　　　　　　　　Logical operators: And OR not

Special operators: is between in like

　　　　　　Is operator:

There are four kinds of use cases:

　　　　　　　　　XX is null: determines that a field is "null"

XX is not null: A field is judged not to be empty;

XX is true: Determines whether a field is true;

XX is false: Determine the value of a field no false: 0,0.0, ', null

　　　　　　

　　　　　Between operator:

Used to determine whether a field's data value is in a given range, and for a numeric type.

Usage: XX between value 1 and value 2;

Its usage is similar to xx>= value 1 and xx<= value 2;

　　　　　　

　　　　　　In operator:

The value of the field represents one of the listed values, which is essentially irregular even if the condition is met.

Syntax:XX in (value 1, value 2, value 3 ...) )

　　　　　　

　　　　　Like operator: Fuzzy query on a string

The function is to implement a fuzzy search for some characteristic information of a string, depending on the following two special symbols:

　　　　　　　%: Any character that represents any number;

　　　　　　　_: (lower bar), representing any character;

Its use: XX like "What to look for"

　　　　　　

　　　　　　

　　　　　　

6.group BY clause

Form:

　　　　　　Group BY field 1 [Desc|asc], field 2[DESC|ASC] ...

Description

Grouping is a standard grouping of data that has already been found and data that has been filtered by where, and the grouping result can also be specified as "Sort by", usually grouped by only one field, and less than 2.

So what is a grouping? is to classify multiple rows of data in a certain standard;

　　　　It is particularly important to note that the result of grouping is only one group, and the result is that the part that is output in the SELECT statement should only have the group's information:

Select group Information 1, group information 2,...from Data source group by field;

In an app, after grouping, there are usually the following types of group information that can be used (that is, it can appear in select):

　　　　　　　1. The group basis of the information itself, in fact, is the field name of the group;

2. The quantity information for each group is obtained by: count (*);

3. Max: Max (field name) min: min (field name) Average: AVG (field name) Sum: sum (field name)

　　　　　　

　　　　　　

7.having clause

The role of having has the same effect as where, but only the filtering of the grouped data.

　　　　　　

　　　　　　

8.order BY clause

It is used to sort the previously obtained data in a set standard to output the result.

Form:

　　　　　　Order BY field 1 [Asc|desc], field 2[asc|desc] ...

Description

Specifies one or more field orders for the preceding result data;

Ordering can be defined as a positive or a flashback;

Multiple field sorts are based on the previous field.

　　　　　　

　　　　　　

9.limit clause

Meaning:

It is used to take the previously fetched data out of the specified line: How many rows are fetched from the first line;

Form:

　　　　　　Limit start line number, number of rows;

Description

The start line number is zero-based;

The starting line number is not related to any one of the fields in the data;

The line number to be removed is also a number, which should be greater than 0;

Can have a brief: Limit line number, the expression starts from line 0th;

　　　　　　

　　　　　　

 Iii. Summary of the entire SELECT statement　1. Although in the form, many sentences of select can be omitted, but the order in which they appear cannot be disturbed, must be written in sequence; 2.where depends on the FROM clause; 3.having depends on the GROUP BY clause; The field is also dependent on the FROM clause; 5. The internal execution process of the above clauses is also dependent on the FROM clause;

PHP Learning Day11---database (3) Data sheet additions and deletions