The primary key conflict

In case of primary key conflict, you can choose to update or replace

update

Primary key conflict, update operation,
Insert into table name [(list of fields: contains primary key)] values(list of values) on duplicate key update field = new value;

-- Primary key conflict: update
insert into my_class values('0810'.'B205')
-- Conflict management
on duplicate key update
-- Updating the classroom
room = 'B205';
Copy the code

replace

Primary key conflict: replace
Replace into table name [(list of fields: contains primary keys)] values(list of values);

-- Primary key conflict: replace
replace into my_class values('0710'.'A203');
replace into my_class values('0910'.'B207');
Copy the code

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.


Create table by copy
create table my_copy like my_gbk;

-- Worm replication
insert into my_copy select * from my_collate_bin;
insert into my_copy select * from my_copy;
Copy the code

The meaning of worm replication
1. Copy data from an existing table to a new table
2. You can quickly inflate the data in a table to an order of magnitude: test the stress and efficiency of the table

Update the data

The basic grammar
- Update 表名 set 表名 = value [where condition];
Advanced new syntax
- Update 表名 set 表名 = value [where condition][limit number of updates];

Change part A to C
update my_copy set name = 'c' where name = 'a' limit 3;
Copy the code

Delete the data

Similar to updates: You can limit the number with a LIMIT
Delete from table_name [where condition][limit number];
If the primary key self – increment exists in the table, self – increment does not restore R after deletion

-- Delete data: limit the number of records to 10
delete from my_copy where name = 'b' limit 10;
Copy the code

The query

The basic grammar
- Select * from (Select * from (Select * from));
Complete syntax
- Select [Select option] field list [field alias]/* from data source [WHERE conditional clause][group by clause][Having clause][Order by clause][LIMIT clause];

Select options

The select option is what SELECT does to the results it finds
- All: By default, all results are retained
- Distinct: The result is removed. Duplicate fields are removed only when all fields are the same

- select options
select * from my_copy;
select all * from my_copy;

- to heavy
select distinct * from my_copy;
Copy the code

Field alias

Field aliases: When the data is queried, sometimes the name is not necessarily sufficient (multi-table query, there will be fields with the same name). The field name needs to be renamed: alias
Syntax: field name [as] alias;

-- Field alias
select 
id,
number asStudent id, nameasName, sexfrom my_student;
Copy the code

The data source

Data source: The source of the data, the source of the relational database is the data table: essentially as long as the data looks like 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;

select * from my_copy;
Copy the code

Multitable data source

Select * from table1, table2… ;
(cross join): Cartesian product (cross join): Cartesian product is useless and should be avoided.

-- Multi-table data source
select * from my_student,my_class;
Copy the code

The subquery

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;

- the subquery
select * from (select * from my_student) as s;
Copy the code

The where clause

Where clause: Used to judge and filter data.
The Where clause returns the result: 0 or 1, Where 0 is false and 1 is true
Judgment conditions:
- Comparison operators: >, <, >=, <=,! = ,<>, =, like, between and, in/not in
- Logical operators: && (and), | | (or),! (not)
Principle of Where: Where is the only criterion to start judging when data is directly obtained from the disk. When a record is retrieved from the disk, judge Where: If the result of judgment is true, it is saved to memory. If you fail, just give up

Add the age and height fields
alter table my_student add age tinyint unsigned;
alter table my_student add height tinyint unsigned;

Rand takes a random number between 0 and 1, and floor is rounded down
update my_student set age=floor(rand() * 20 + 20),height = floor(rand()*20 + 170);

Select * from id 1,3,5
select * from my_student where id = 1 || id = 3 || id = 5; -- Logical judgment
select * from my_student where id in(1.3.5); -- Falls into the set

-- Look for someone between 180 and 190
select * from my_student where height > = 180 and height < = 190;
select * from my_student where height between 180 and 190;

--Between itself is a closed interval; The value on the left of between must be less than or equal to the value on the right, otherwise the search will fail
select * from my_student where height between 190 and 180;
Copy the code

The group by clause

Group by: Group by a certain field
- Basic syntax: group by field name;

Select * from student group by sex;Copy the code

Group meaning: for the purpose of statistics (group statistics: statistics of data by group fields)
- SQL provides a series of statistical functions
- Count(): Number of records in a group: the number of records in each group
- Max(): Collects the maximum value in each group
- Min(): indicates the minimum statistical value
- Avg(): indicates the statistical average
- And the Sum () : statistics

-- Group statistics: height, average age and total age
select sex,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;
Copy the code

Count function: * indicates the statistics record, and field name indicates the corresponding field (NULL does not Count).

select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by sex;
Copy the code

Groups are automatically sorted by group field: Default ascending (ASC)
Group by field [asc | desc]; Sort the result of grouping and then the whole result after merging

select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by sex desc;
Copy the code

Multi-field grouping: Group the results based on one field, and then group the results based on other fields

-- Multi-field grouping: class first, then boys and girlsselect c_id,sex,count(*),group_concat(name) from my_student group byc_id,sex; Sort by multiple fieldsCopy the code

Extension – Retrospective statistics

Having clause

Having clause: Same as where clause: Having a condition.
Where is to determine disk data: once in memory, the data will be grouped, and the result will need to be processed by having.
Having can do almost everything where can do, but where can’t do many of the things Having does.

The results of grouping statistics, or statistical functions, are available only to having.

Figure out the number of students in each class whose size is 2 or greater
select c_id,count(*) from my_student group by c_id having count(*) > = 2;
select c_id,count(*) from my_student where count(*) > = 2 group by c_id ; -- failed, where function count cannot be used
Copy the code

Having can use field aliases: WHERE cannot: Where is the data from disk, and the name can only be the field name: aliases are generated after the field goes into memory

select c_id,count(*) as total from my_student group by c_id having total > = 2;
select c_id,count(*) as total from my_student where total > = 2 group by c_id ; Where can't use alias
Copy the code

The order by clause

Order by: sort a field in ascending or descending Order, depending on the collation set.
Use basic syntax
- The Order by the field name [asc | desc]; Asc is in ascending order (default),desc is in descending order

-- group by sort
select * from my_student group by c_id;
-- Order by
select * from my_student order by c_id;
Copy the code

Sorting can be sorted by multiple fields: first sort according to a certain field, then sort the internal, and then sort again according to a certain data

-- Multi-field sorting: first by class, then by gender
select * from my_student order by c_id, sex desc;
Copy the code

Limit clause

The Limit clause is a statement that limits results: limits the number of results.

Limit can be used in two ways

Used only to limit the length: limit the amount of data

-- Query students: the first two
select * from my_student limit 2;
Copy the code

Limit start position, limit number: limit start position, length

-- Query students: the first two
select * from my_student limit 0.2; -- The number of records is numbered from 0
select * from my_student limit 2.2;
select * from my_student limit 4.2;
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Database study notes in August 5 – | advanced operation more challenges

The primary key conflict

update

replace

Worms copy

Update the data

Delete the data

The query

Select options

Field alias

The data source

Single-table data source

Multitable data source

The subquery

The where clause

The group by clause

Having clause

The order by clause

Limit clause

Database study notes in August 5 – | advanced operation more challenges

The primary key conflict

update

replace

Worms copy

Update the data

Delete the data

The query

Select options

Field alias

The data source

Single-table data source

Multitable data source

The subquery

The where clause

The group by clause

Having clause

The order by clause

Limit clause

Related Posts

Java operations Linux calls shell commands, shell scripts

This time, I don’t even need web.xml.

Jenkins: Getting started