MySQL Learning 2

1. Create a foreign key

 　　 Create Table <Table name>(<Field>Field type not NULL,<Field>Field type not NULL,<Field>Field type not NULL,Foreign Key(<Field>)References <Primary table name>(<Field>));

2.Mysql connection (left join, right join, INNER join, full join)
1) INNER JOIN (internal link to get two tables with the same field data)
　　

  Select*from< 1 names >innerjoin< table 2 >on< table 1 names >. < field name >=< table 2 names >. < field name >;

is to show only the intersection of 2 tables

2) LEFT join (differential set on the right side of the connection)

　　　　Select *  from < table 1 >leftjoin< table 2 >on< Table 1 >. < field name >=< table 2 names >. < field name >;

Returns records that include all records in the left table and the equivalent of the junction fields in the right table

3) Right Join (the difference is set in the left-hand connection)

Select *  from < table 1 >rightjoin< table 2 >on< table 1 >. < field name >=< table 2 names >. < field name >;

Returns records that include all records in the right table and the equivalent of the junction fields in the left table

4) Full join (Seek and set)

Select *  from <Table 1 Names>  Left Join <Table 2 Names>  on <Table 1 Names>.<Field name> = <Table 2 Names>.<Field name> Union Select *  from <Table 1 Names>  Right Join <Table 2 Names>  on <Table 1 Names>.<Field name> = <Table 2 Names>.<Field name>;

3.mysql Things
MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

Transactions are supported in MySQL only for databases or tables that use the InnoDB database engine
Transactions can be used to maintain the integrity of the database, to ensure that a batch of SQL statements are either all executed or not executed
Transactions are used to manage insert,update,delete statements
In general, transactions are required to meet 4 conditions (ACID): atomicity (atomicity), consistency (stability), isolation (isolation), durability (reliability)

1), the atomicity of the transaction: a set of transactions, either success or withdrawal.
2), Stability: There is illegal data (foreign key constraints and the like), transaction recall.
3), Isolation: Transactions run independently. The result of one transaction affects other transactions, and then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed.
4), reliability: After the hardware and software crashes, the INNODB data table driver uses the log file to reconstruct the changes. Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.

　　　

　　　　begin　　　　;                       # Start a transaction  insertintovalues(555);  rollback;                    Rollback so that the data is not written

If the data is OK, enter commit, and submit the order.

4.mysql Index
MySQL indexing is important for the efficient operation of MySQL, and indexing can greatly improve the retrieval speed of MySQL.

1) Create an index

Create Index < index name >on< table name >< field name >(word length));

2) directly specify when creating a table

       Create Table < table name >intchar(notnull  ,  primarykey(ID),  index< indexed name >< /c18> (username (+)));            

3) Delete Index

　　　　Drop Index < index name >on< table name >

4) Unique Index
Create an index

　　　　　　Create Unique Index < index name >on< table name >< field name > (word length))

Specify directly when creating a table

　　　　　　Create Table < table name >intnotnullchar(  not NULL , Unique < index name > (username (+)));

5.ORM

ORM English Full Name Object relational mapping, that is, objects mapping relation program, simply say we like python this object-oriented program is all objects, but we use the database is relational, in order to ensure consistent use of the habit, By ORM, the object model of the programming language and the relational model of the database are mapped, so that we can use the programming language to manipulate the database directly using the programming language object model, instead of using the SQL language directly

MySQL Learning 2