1, Connection query

Classification according to the way of table connection:
Internal connection: equivalent connection, non equivalent connection and self connection
External connection: left external connection (left connection), right external connection (right connection)

Syntax: join ... on ... 
select ... from Table name 1 join Table name 2 on condition where ... 

Self connection: the two tables are the same
 Equivalence and non equivalence refer to on Later conditions

External connection: refers to join Added before left and right Left and right concatenation caused by keyword
select ... from Table name 1 left join Table name 2 on condition where ... 
The above statement indicates that table name 1 is the main table
select ... from Table name 1 right join Table name 2 on condition where ... 
The above statement indicates that table name 2 is the main table

Note that there is no primary or secondary connection between the two tables left and right It means internal connection

2, Subquery

In mysql, sub queries are divided into sub queries after where and from

stay from Subquery after
 Syntax: select * from (select * from Table name);
Will put from The following sub query is treated as a temporary table

stay where Subquery after
 Syntax: select * from Table name where sal > (select max(sal) from emp);
Indicates that the subquery is found sal Compare the maximum value

3, union merge query result set

What is involved here is only the efficiency of query

Syntax: select field... from Table name union select field... from Table name;
The premise is that the number of columns in two query result sets must be the same before merging

4, Use of limit (Master)

Full usage: limit, StartIndex, length
startIndex is the starting subscript, length is the length, and the starting subscript starts from 0.

limit Use of: take out 3-5 Employees
select staff from Table name order by field asc limit 2,3;

limit Use of paging
pageNo: Which page
pageSize: It refers to the number of data on the page
 Formula: limit (pageNo-1)*pageSize , pageSize

Application:
Display per page pageSize Records
 The first pageNo Page: limit (pageNo - 1) * pageSize  , pageSize

Note: in mysql, limit is executed after order by

5, A summary of DQL statements

select .. from .. where .. group by .. having .. order by .. limit ..
Execution sequence:
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit

6, Creating and deleting tables

1. Syntax format of table creation: (table creation belongs to DDL statement, and DDL includes: create drop alter)

create table Table name(
    Field 1 data type,
    Field 2 data type,
    Field 3 data type
)

Table name: it is suggested to t_ perhaps tbl_At first, it is readable. See the name and know the meaning.
Field name: see the meaning of the name.
Both table and field names are identifiers.

About data types in mysql?

For many data types, we only need to master some common data types.

Varchar (max 255)
Variable length string
It is intelligent and saves space.
Space will be dynamically allocated according to the actual data length.

Advantages: space saving
Disadvantages: it needs to allocate space dynamically and the speed is slow.

Char (maximum 255)
Fixed length string
No matter what the actual data length is.
Allocate a fixed length of space to store data.
Improper use may lead to a waste of space.

Advantages: there is no need to dynamically allocate space and the speed is fast.
Disadvantages: improper use may lead to a waste of space.

How should we choose varchar and char?
What do you choose for the gender field? Because gender is a fixed length string, char is selected.
What do you choose for the name field? Everyone's name is different in length, so choose varchar.

Int (max. 11)
An integer in a number. Equivalent to java int.

        bigint
A long integer in a number. Equivalent to long in java.

        float    
Single precision floating point data

        double
Double precision floating point data

        date
Short date type syntax representation:% Y-%m-%d

        datetime
Long date type syntax representation:% Y -% m -% d% H:% I:% s

        clob
Character large object
Up to 4G strings can be stored.
For example: store an article and a description.
CLOB character large objects shall be used to store more than 255 characters.
            Character Large OBject:CLOB

        blob
Binary large object
            Binary Large OBject
It is specially used to store streaming media data such as pictures, sounds and videos.
When inserting data into a BLOB type field, such as inserting a picture, video, etc,
You need to use IO stream.

2. Table deletion

Syntax format: drop table Table name;
If the table does not exist, an error will be reported!
The following syntax is recommended
drop table if exists Table name; 
No error will be reported

7, Insertion, deletion and modification of table data

1. Table data insertion

Syntax: insert into Table name(Field 1, field 2) values(Field 1 value, Field 2 value);
Fields and values must correspond one to one
 If you do not write fields, all fields are selected
insert into Table name values(Values of all fields);

You can also insert more than one at a time
 Syntax: insert into Table name() values(),(),(); 
Indicates that multiple items are inserted at one time

2. Modification of table data

Syntax: update Table name set Field 1 = ..., Field 2 = ..., Field 3 = ... where ...
Conditions must be added, otherwise all data will be modified

3. Deletion of table data

Syntax: delete from Table name where ...
Conditions must be added, otherwise all data will be deleted
delete Just deleted the data, the space is still there, and it can be recovered

start transaction; Open transaction

Execute here delete After that, the data can be recovered

rollback; RollBACK 
After the rollback execution, the data will be recovered

truncate Physical deletion does not support rollback recovery
 Syntax: truncate table Table name;
All data will be cleared directly without adding conditions

Note: truncate is important and must be mastered!!!
The table is truncated once, which belongs to physical DDL
The efficiency of using truncate is much higher than that of using delete, but it must be improved before using
Very rigorous, otherwise the data will be gone!!!