MySQL additions and deletions (DML)

Two ways to insert records:

Insert Basic Syntax One

category	Detailed Solution
Basic syntax	Insert into table values (value 1, value 2, value n);
Example	INSERT into user values (2, ' php Chinese web ', ' male ')
Example description	Insert a value ID of 2 to the user table, name Li Wenkei, sex is male

Insert Basic Syntax Two

category	Detailed Solution
Basic syntax	Insert into table (field 1, Field 2, field N) VALUES (value 1, value 2, value n);
Example	INSERT into User (Id,username,sex) values (213, ' Little Shenyang ', 1);
Example description	Insert ID 213,username to user table for small Shenyang, sex is 1

Description

The difference between basic Syntax 1 and basic Syntax 2 is:

1. The INSERT statement for Basic Syntax 1, the number of fields in the table that must be inserted in the number of values. One cannot be more, nor one can be less. If there is a default value, do not want to pass, you can write null.
2. Basic Syntax 2, unless a required field must be written to a value. If you do not want to write with a default value, you can ignore the write. MySQL automatically complements the primary default value.
3. In basic Syntax 2, the order of the values in the user (Id,username,sex) field is the order.

Inquire

Base query: SELECT * from table name;

Specify field query: Select field name from table name;

Query single field does not duplicate record: SELECT distinct field name from table name;

Conditional query: Select field name from table name where condition;

Conditions after which the where can be connected

The comparison operator result set lists the records that match the criteria. In the example above, the field behind the where is the ' = ' of the fields.

In addition, you can also use the >, <, >=, <=,! = and other comparison operators;

symbols	Description
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
!=	Not equal to
=	Equals

logical operators

Multiple conditions can also use logical operators such as or, and, to make multi-conditional Union queries

symbols	Description
Or	Or
and	And

Let's look at an example of multiple conditions:

type	Detailed content
Example	SELECT * from Money where ID <10 and
Description	Query all fields require ID less than 10 and province= ' Hubei '

Mysql> SELECT * from Money where ID <10 and province= ' Hubei ';

Sorting result Sets

Select field name from table name order BY field name sort keywords;

Keywords to use for sorting:

Key Words	Description
Asc	In ascending order, from small to large (default)
Desc	Descending order, from big to small

Result set limits

For query or sorted result sets, use the Limit keyword to limit the number of result sets if you want to display only part of it instead of displaying all: The number of select field names from table name limit;

restricting and sorting result sets

Select field name from table name ORDER by sort keywords limit quantity;

Result set Interval selection

Let's say I've taken 3 records starting with the No. 0 article. And then I want to start with the 3rd article to take 3 records. What if I want to take 4 records from the 6th article?

Select field name from table name limit offset, quantity;

With the selection of the result set interval, we can complete the pagination.

Statistical class functions are used

Statistical class functions are the most commonly used we have four:

function	Description
Sum	Sum
Count	Total statistics
Max	Maximum Value
Min	Minimum value
Avg	Average

Note: Of course you know that other MySQL functions are also available. However, in the actual work, many large and medium-sized companies are rarely used, they have a dedicated counting server. Because MySQL is computationally large in itself, in order to reduce stress we usually give the actual computational task to a Business Server or other server to complete.

Group : SELECT * FROM table name Group By field name;

statistics on a group basis : SELECT * FROM table name Group By field name with rollup;

Results re-filter having

The HAVING clause is similar to where, but there are also differences, which are the statements that set the conditions.

Having is a filter group and where is a filtered record.

Multi-Table Union query

Many times in the actual business we do not just query a table, the nature of multiple table query is: Table connection.

Table Connection

When you need to query the fields in more than one table, you can use table joins to make the table connections into inner and outer joins.

Internal connection:

• Select table 1. field name [as Alias], table N. field name from table 1 [alias], table n where condition;
• Select table 1. field name [as Alias], table N. field name from table 1 INNER JOIN table n on condition;

External connection:

The outer join is divided into left and right connections, the left connection contains all the records in the left table and even the records in the right table that do not match it; the right connection contains all the records in the right-hand table, or even the one that does not match it on the right side.

Select table 1. field name [as Alias], table N. field name from Table 1 left JOIN table n on condition;

Select table 1. field name [as Alias], table N. field name from table 1 right JOIN table n on condition;

Sub-query

Sometimes, when we query, the condition that we need is the result of another SELECT statement, then we need to use a subquery. The keywords used for subqueries include in, not in, =,! =, exists, not exists, and so on.

category	Detailed Solution
Basic syntax	Select field from table where field in (conditional)
Example 1	SELECT * from the user where UID in (1,3,4);
Example 1 illustrates	Query the specified user by ID
Example 2	SELECT * from the user where UID in (select UID from order_goods);
Example 2 illustrates	Display the user information of the purchased item

Record Union

Using the Union and UNION ALL keywords, the results of the two tables are combined to display when they are queried by a certain query condition. The main difference between the two is that the results are merged directly together, and the Union is a distinct of the result after the union all, eliminating the result of duplicate records.

Third, update the record

Update table name set field 1= value 1, field 2= value 2, field n= value n where condition;

Update two tables simultaneously: Update table 1 table 2 set field 1= value 1, field 2= value 2, field n= value n where condition;

Iv. Deleting records (be sure to remember to back up when deleting data)

Delete from table name [where condition];

Clear table record: Delete and truncate are the same, but they are a little different, that is, the delete can return the number of deleted records, and TRUNCATE table returns 0. Syntax: TRUNCATE TABLE name;

MySQL additions and deletions (DML)