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
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.
A long integer in a number. Equivalent to long in java.
Single precision floating point data
Double precision floating point data
Short date type syntax representation:% Y-%m-%d
Long date type syntax representation:% Y -% m -% d% H:% I:% s
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
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!!!