Advanced Data operations in SQL

First, new data

1. Primary KEY violation: update and replace

①insert into table name [(field list)] Values (value list) on duplicate key Update field = new value, field = value, ...; --Update

②replace into table name [(field list)] VALUES (list of values); --Replacement

2. Worm Replication

Create TABLE table name a like database. Table name B; --Copy table structure from existing tables (do not copy data), such as Table B and table A in the same database, the dynamic database name;

Insert into table name a [Field list] SELECT */Field list from table name B; --Worm Replication

Meaning of the worm replication:

① copy data from existing tables into a new table;

② can quickly inflate the data in a table to a certain number of levels to test the pressure and efficiency of the database tables.

Second, update the data: Limit the number of updates

Update table name Set field = value [Where condition] [limit quantity];

Third, delete data: Limit the number of deletes

Delete from table name [where condition] [number of limit];

If the primary key in the table is self-growing, the self-growth is not reset after the data is deleted, because self-growth is part of the table structure, and to reset self-growth, the table can be emptied in the following way:

TRUNCATE TABLE name; --Delete the table first, and then add the table.

Iv. Querying data

1. Query statement complete syntax:

Select [Select Options] */Field list [Field alias] from data source [where conditional clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [limit clause];

2. Select option

All: By default, all results are preserved.

Distinct: Remove the duplicate results.

3. Field aliases

field name [as] Alias

4. Data sources

As long as the data is essentially similar to a two-dimensional table, it can be used as a data source: a single-table data source, a multi-table data source, and a subquery.

Single-table Data source: SELECT * from table name;

Multi-table Data source: SELECT * from table name 1, table name 2,...; -the result is a Cartesian product, which is a cross connection that is useless and avoids any use.

Sub-query: SELECT * FROM (SELECT * from table name) as alias;

5. WHERE clause

comparison operator: <, >=, <=,! =, =, like, in, not in, and, between;

Logical operator:&& (and), | | (OR),! (not);

Where is the decision to read data from disk, eligible to be saved to memory, can save memory space.

Example one: Find records with ID 1, 3, 5

SELECT * from mytab where id = 1 | | id = 3 | | id = 5;

SELECT * from Mytab where ID in (1, 3, 5);

Example two: Finding records with IDs between 5 and 10

SELECT * from Mytab where ID >= 5 && ID <= 10;

SELECT * from Mytab where ID between 5 and 10; --Between is a closed interval, and the left value must be small and equal to the right value

6. GROUP BY clause

① syntax: Group BY field name [Ase/desc]; --Grouping by field name, ASC ascending sort (default), desc descending sort

The purpose of ② grouping is to statistic data. SQL provides a number of statistical functions:

COUNT (Field/*); --When the parameter is a field, the number of records in that field in each group is counted, but NULL is not counted; when the parameter is *, counts all records in each group.

Max (field); --Statistics The maximum value of the field in each group

Min (field); --Statistics The minimum value of the field in each group

AVG (field); --Statistics The average of the field in each group

sum (field); --Statistics of the fields in each group and

③ Other functions:

Group_concat (field); --string connection of data for a field in a grouped result

④ single Field grouping example usage:

--To group sex, count the number of records per group, the maximum age in each group, concatenate the data in each group into a string, and sort in descending order.

Select Sex, COUNT (*), Max (age), Group_concat (name) from Mytab GROUP by sex desc;

⑤ Multi-field grouping

Select Sex, Class, COUNT (*) from Mytab GROUP by sex, class; --Group BY sex first, then group by class

⑥ Retrospective statistics: with rollup

Select Sex, Class, COUNT (*) from Mytab GROUP by sex[, class] with rollup;

Multi-field grouping backtracking count:

In the case of two-field grouping, if the X group is divided according to the first field, there will be a retrospective after the first grouping, and then the X-sub-group according to the second field, there will be X backtracking,

So the number of backtracking = X + 1. That is, several times the grouping behavior, there are several backtracking.

7. HAVING clause

Used to judge the data in memory. You can use field aliases

Select Class, COUNT (*) from Mytab Group by class have count (*) > 10; --Find out the number of students in all classes greater than 10

Select Class, COUNT (*) as total from Mytab Group by class have total > 10; --Using aliases

8. ORDER BY clause

Sort by a segment in ascending or descending order, depending on the proofing set.

Syntax: SELECT * FROM table name order By field name 1[, field name 2] [ASC/DESC]; --Default ascending (ASC)

9. Limit clause

Number of ①limit; --Limit Quantity

②limit starting position, quantity; --Limit the starting position (starting from 0), and then limit the number to be used for paging

Starting position algorithm when paging: (page-1) * Number of displays per page

Advanced Data operations in SQL