Connection (JOIN). • More efficient, because
MySQL does not need to create a temporary table in memory to complete this logic's query work that requires two steps.
3. Use the Union to replace the temporary table created manually
MySQL starts to support UNION queries from the version 4.0 version, which can be in a query that you need to use two or more SELECT queries that you need to use a temporary table. At the end of the client's query session, the temporary table will be automatically deleted to ensure
The database is neat and efficient. When using Union to create a query, we only need to connect multiple SELECT statements with Union as a keyword, pay attention to the number of fields in all SELECT statements, you want to think. The following example demonstrates a query using Union.
Select Name, Phone from CliantunionSelect Name, Birthdate from AuthorunionSelect Name, Supplier from Product
4, transaction
Although we can use sub-queries, connect (JOIN) and Union and Union, we can create a variety of queries, but not all database operations can only be done with one or a few SQL statements. of. More often you need to use a series of statements to do some work. However, in this case, when a statement in this statement is run, the operation of the entire statement block will become uncertain. Imagine, to insert a data simultaneously into two associated tables, this may happen: After the first table is successfully updated, the database suddenly appears unexpected conditions, causing the operation in the second table without completion. In this way, it will cause incomplete data, and even data in the database. To avoid this, you should use your transaction, its role is: Either statement is successful in the statement, or you have failed. In other words, it is to maintain consistency and integrity of data in the database. Things start with Begin keywords, and the commit key is ended. A SQL operation between this failed, then the ROLLBACK command can restore the database to the state before BeGin.
INSERT INTO SALESINFO SET CUSTOMERID = 14; Update Inventory Set Quantity = 11where Item = 'BOOK'; COMMIT;
Another important role of the transaction is when multiple users use the same data source simultaneously, it can utilize the method of locking the database to provide a safe access method for the user, which ensures that the user's operation is not interfered by other users. .
