Mysql Operations 05-SQL Advanced data operations

preface

This article mainly summarizes the primary key conflict in mysql, worm replication, limit the number, query condition judgment, field alias and other use methods.

Mysql > Mysql > Mysql

Mysql Operations 01 – Basic commands, SQL library operations
Mysql > select * from SQL where SQL > select * from SQL where SQL > SQL
Mysql Operations 03 – SQL data operations
Mysql operation 04 – SQL Chinese data problem
Mysql Operations 05-SQL Advanced data operations

1. Add data

Basic grammar:

Insert into table name [(list of fields)] values(list of values);Copy the code

When data is inserted, assume that the value corresponding to the primary key already exists: the insert will fail.

The primary key conflict

When Duplicate keys exist, you can selectively process, update and replace them.

Primary key conflict: update operation

Insert into table name [(field list: contains primary key) VALUES (value list) on duplicate key update field = new value;Copy the code

Ex. :

Insert into my_calss values("php1","1"); insert into my_calss values("php1","1"); Insert into my_calss values("php1","2"); Insert into my_class VALUES ("php1","2") insert into my_class values("php1","2") on duplicate key updateCopy the code

Primary key conflict: replace

Replace into table name [(list of fields: contains primary keys)] values(list of values);Copy the code

Ex. :

Replace into my_calss values("php1","2"); replace into my_calss values("php1","2");Copy the code

Note: Data is inserted if there is no conflict. There are conflicting substitutions.

Worms copy

Worm replication: from the existing data to obtain data, and then the data and new operations: data multiplied.

Table creation Advanced operations: Create a new table from an existing table (replicate table structure)

Create table Table name like database. The name of the table.Copy the code

Create table my_copy like MG_GBK; create table my_copy like MG_GBK;Copy the code

Worm copy: first find data, and then will find the data added again

Insert into table_name [(table_name)] select table_name from table_name;Copy the code

Ex. :

Insert into my_copy select * from my_collate_bin; Insert into my_copy select * from my_copy; -- Copy your own tableCopy the code

The meaning of worm replication:

Copy data from an existing table to a new table
You can quickly inflate the table to a certain order of magnitude, testing the table’s stress and efficiency.

2. Update data (limit quantity)

Basic grammar:

Update 表名 set 表名 = value [where condition];Copy the code

Advanced new syntax:

Update 表名 set 表名 = value [where condition][limit number of updates];Copy the code

Example: Update some data

Update my_copy set name = 'c' where name = 'a' LIMIT 3;Copy the code

3. Delete data

Similar to updates, you can limit the number with a LIMIT.

Delete from table_name [where condition][limit number];Copy the code

Ex. :

Delete from my_copy where name = 'b' limit 10; delete from my_copy where name = 'b' limit 10;Copy the code

Delete: If the primary key self – increment exists in the table, self – increment will not be restored after deletion.

Data deletion does not change the structure of a table. Note: Table data will be cleared. Only table structure.

Truncate table name. Delete changes first, then add changes later.Copy the code

Ex. :

-- Clear table: reset since growth truncate my_student;Copy the code

4. Query data

Basic grammar:

Select * from (select * from (select * from (select * from));Copy the code

Complete grammar:

Select [select option] field list [field alias]/* from data source [WHERE conditional clause][group by clause][Having clause][Order by clause][LIMIT clause;Copy the code

The meaning of the select option is as follows: select Indicates the processing mode of the obtained results
- All: By default, all results are retained.
- Distinct: The result is removed repeatedly (all fields are the same). All fields are removed
```
Select * from my_copy; select * from my_copy; select all * from my_copy;Copy the code
```
```
Select distinct * from my_copy;Copy the code
```
Field alias

Meaning: When the data is queried, sometimes the name does not necessarily meet the requirements (multi-table query, there will be a field with the same name) need to rename the field name: alias.

Basic grammar:
```
Field name [as] Alias; -- With or without as.Copy the code
```
Ex. :
```
Select id, number as student, name as student from my_student;Copy the code
```
The data source

Meaning: The source of the data, the source of the relational database is the data table, in essence, as long as the data is similar to a two-dimensional table, eventually can be used as a data source.

There are many kinds of data sources: single-table data sources, multi-table data sources, and query statements

Single-table data source:
```
Select * from table_name;Copy the code
```
Multi-table data source:
```
Select * from table1, table2,... ;Copy the code
```
The result of fetching a record from one table, matching all records from another table, and retaining all (the number of records and the number of fields) is called the Cartesian product (cross join). The Cartesian product is useless, so try to avoid it.

Subquery: The source of the data is a query statement (the result of the query statement is a two-dimensional table)
```
Select * from (select statement) as table name;Copy the code
```
Ex. :
```
Select * from (select * from student) as s;Copy the code
```
The where clause

Where clause: Used to judge and filter data.

The where clause returns 0 or 1, where 0 is false and 1 is true;
```
select * from my_student where 1; -- indicates that all conditions are met. This expression is used for statement integrity.Copy the code
```
Judgment conditions:
- Comparison operators: >,<,>=,<=,! =,<>,=,like,between,and,in/ont in
- Logical operators: && (and), | | (or),! (not)
Principle of WHERE: Where is the only condition to start judging when data is obtained directly from the disk: a record is retrieved from the disk, and the judgment is started. If the result is true, it is saved to memory, and if the failure, it is directly abandoned.

Query condition 1:

Example: find the data for student id 1,3,5
```
- find out the student id is 1,3,5 data, the results of the following two statements as select * from my_student where id = 1 | | id = 3 | | id = 5; Select * from student where id in(1,3,5); -- Falls into the setCopy the code
```
Query condition 2:

Example: Look for students between 180 and 190
```
Select * from my_student where height >= 180 and height <= 190; select * from my_student where height >= 180 and height <= 190; select * from my_student where height between 180 and 190;Copy the code
```
Note: Between itself is a closed interval, and the value on the left of between must be less than or equal to the value on the right

5. Group by clause

Group by: Group by a field (the same in a group, the different in a group). Select * from student group by sex; * count() : counts the number of records in a group: how many records are in a group * Max () : counts the maximum value in a group * min() : counts the minimum value * avg() : counts the number of records in a group * avg() : counts the number of records in a group * avg() : counts the number of records in a group * avg() : counts the number of records in a group * sum() : indicates the sum of statistics. Example: ~~~ -- Indicates the group statistics: Select count(*), Max (height),min(height),avg(age),sum(age) from student group by sex; * indicates the statistics record, and the field name indicates the corresponding field of the statistics (NULL does not count the statistics). The group will be automatically sorted by the fields: default ascending order ~~~ -- ASc: ascending order, desc: Descending group by field [asc | desc]; Sort the whole result after combining the grouped results. Select count(*), Max (height),min(height),avg(age),sum(age) from my_student group by sex desc; ~~~ Multi-field group: Group the results based on one field and then group the results based on other fields. Select c_id,sex,count(*) from student group by c_id,sex; -- Multi-field sorting ~~~ There is a function that concatenates a field in the result of grouping (reserving all the fields in the group) ~~~ group_concat(field); Select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex; -- Sorting multiple fields ~~~ Tracing statistics: with rollup; After each group, there will be a group. Finally, the group needs to report statistics to the superior group according to the fields of the current group. So that's retrospective statistics. Feature: The group field is left blank when tracing statistics. Select c_id,count(*) from my_student group by c_id; Select c_id,count(*) from my_student group by c_id with rollop; Select c_id,sex,count(*),group_concat(name) from student group by c_id,sex; Select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex with rollop; -- Multi-field group traceback statistics ~~~ Multi-field traceback: Consider that the first level of the group will have a traceback, the second group depends on the number of groups in the first group, the number of groups, the amount of traceback, and then add the first level of traceback.Copy the code

6. Having clause

7. Order by clause

8. Limit clause

Meaning: The limit clause is a statement that restricts results: limits the number of results

Limit can be used in two ways:

Solution 1: Use only to limit the length (data amount) : limit the amount of data;

Ex. :

Select * from my_student limit 2;Copy the code

Option 2: limit the starting position, limit the number: limit the starting position, limit the number;

Ex. :

Select * from my_student limit 0,2; -- The number of records is numbered from 0Copy the code

Limit scheme 2: Mainly used to achieve data paging: for users to save time, improve the response efficiency of the server, reduce the waste of resources.

For the user: clickable pagination buttons 1,2,3,4

For the server, according to the user selected page number to obtain different data: limit offset,length;

Length: The amount of data displayed on each page: basically unchanged.

Offset: offset=(page – 1) * Number of pages to display

Paging query statements and get total number of records

Select SQL_CALC_FOUND_ROWS * from bloglist order by id limit 2,2; SELECT FOUND_ROWS();Copy the code

Mysql Operations 05-SQL Advanced data operations

preface

1. Add data

2. Update data (limit quantity)

3. Delete data

4. Query data

5. Group by clause

6. Having clause

7. Order by clause

8. Limit clause

Related Posts

How to optimize MCU code through map file

Nginx configures HTTPS service

SpringBoot2 integrates MinIO middleware for easy file management