MySQL Learning Note 4

1. Natural connection

1. Using MySQL's own judgment to complete the connection process without specifying a connection condition, MySQL uses the same fields from multiple tables as the join condition.

   • Internal connection natural Join ? Inner join using
     
   • left OUTER join natural left join using
     
   • Right outer join natural ? Right join using
     Ps:left join and Right join can be converted to each other

3. Support multi-table connection query
   

5. Summary
   This picture, pass up is this appearance, also do not know how to rotate, if someone knows how to do, ask for advice.
   

2. Redirection

1. In addition to returning the output to the interface, MySQL can also write the output to a file.

selectintofrom ....

It is important to note that he does not allow overwriting files that already exist, but can create new files. The default is to use ' \ t ' to distinguish between fields, using ' \ n ' to distinguish records, can be modified.

3. Inserting data

1. Inserting data using INSERT INTO
   
2. When inserting data conflicts, you can use on duplicate key update to update the data
   
3. It is also important to note that we can use the result of the clause obtained by the SELECT query to insert
   
4. Default
   
5. Replace into for inserting data that can handle primary key or unique index conflict issues
6. The load data infile can be used to set the format of the data imported from the file.

4. Delete data

1. Delete from XXX limit n
   
2. Limit is used to restrict the number of records that can be used with order by
3. Support for connection removal, which can be used to simulate foreign key constraints

deletefromonetwousingonetwoon xxx where xxx

5. Clear the table

1. Truncate directly deletes the table and creates a new table, so his autogrow index is calculated starting at 0.
2. The delete from table data is deleted, but their autogrow index value does not change to 0.

6. Update the table

1. Replace/insert on duplicate key update
2. Conditional update, limit, order by & limit etc
3. Supports simultaneous updates of multiple tables

update [one join two on xxx] set xxx where xxx

MySQL Learning Note 4