MySQL Multi-table query
1. Merging result sets
Effect: Merging result sets is the result of connecting two SELECT statement queries together!
/* Create tables T1*/create table T1 (a INT PRIMARY KEY, b VARCHAR (Ten)) insert into T1 values (1, ' a '); INSERT into T1 values (2, ' B '); SERT into T1 VALUES (3, ' C ');
/* Create t2*/create TABLE T2 (c INT PRIMARY KEY, D VARCHAR (Ten)) insert into T2 values (4, ' d '); INSERT into T2 values (5, ' e '); INS ERT into T2 VALUES (6, ' F ');
There are two ways of merging result sets:
Union: Removes duplicate records.
/* Connect with Union two SELECT statements */select * FROM T1 Union select * FROM T2;
The query results are:
In order to reflect the idea of re-thinking: we add a piece of data to T1
INSERT into T1 values (4, ' d ');
To re-query it:
SELECT * FROM t1 Union select * FROM T2;
Proving that the same data from the T1 and T2 tables will be removed
UNION ALL: Do not go out and repeat records.
SELECT * FROM t1 UNION ALL select * from T2;
4d appears twice in the output result
Summary: Union: Connect two query statements to remove identical data
UNION ALL: Connect two query statements without removing identical records
Requirement: Two results to be merged: Number of columns, column type must be the same.
2. Connection Query
Connection query is to find the product of multiple tables EG:T1 connection t2, then the result of the query is T1*T2
SELECT * from T1,t2;
A connection query produces a Cartesian product.
assume the collection A={A,B}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases.
To get rid of the extra data that the Cartesian product brings us, we use a join query to give a constraint (FOREIGN KEY constraint)
In order to better experience the constraints of the connection query we re-built two tables
CREATE TABLE classes ( CID int primary key auto_increment, cname varchar (), cnum int); INSERT INTO classes VA Lues (NULL, ' Class 01 ', +); Insert into classes values (NULL, ' 02 classes ', "); Insert into classes values (NULL, ' Class 03 ', +); Insert into classes values (NULL, ' 04 classes ', 41);
CREATE TABLE Student (
Sid Int Primary Kay Auto_increament,
CNAME varchar (20),
Sex varchar (1),
Brithday date,
CNO int,
Constraint fk_st_c1 foreign KEY (CNO) references classes (CID)
);
/* Add data to the student table */
INSERT into student VALUES (NULL, ' Zhang San ', ' Male ', ' 1990-09-01 ', 1);
INSERT into student VALUES (NULL, ' John Doe ', ' female ', ' 1991-02-13 ', 1);
INSERT into student VALUES (NULL, ' Harry ', ' Male ', ' 1990-03-12 ', 1);
INSERT into student VALUES (NULL, ' Zhao Liu ', ' Male ', ' 1992-02-12 ', 2);
INSERT into student VALUES (NULL, ' Tianqi ', ' Male ', ' 1994-05-21 ', 2);
INSERT into student VALUES (NULL, ' Zhang Five ', ' female ', ' 1990-05-17 ', 2);
INSERT into student VALUES (NULL, ' Zhang Lao Seven ', ' female ', ' 1990-06-17 ', 3);
INSERT into student VALUES (NULL, ' King old Four ', ' female ', ' 1990-04-12 ', 3);
INSERT into student VALUES (NULL, ' Li Liu ', ' Male ', ' 1990-09-12 ', 1);
Class table Student Table
The foreign key of the CNO field in the student table is the CID in the class table
So we use the connection query will produce (class table number of bars * Student table number of bars =4*9=36)
Plus constraints to filter out useless data using the WHERE keyword
SELECT COUNT (*) from classes c,student s WHERE c.cid=s.cno;
To filter out invalid data, so we don't use the Union in general.
This results in internal and external connection queries
The inner connection is divided into: (feature query conditions must be met)
explicit INNER JOIN (inner join ... on) where the inner keyword can be omitted
SELECT * FROM classes C INNER joins student s on C.cid=s.cno;
SELECT * FROM classes C joins student s on C.CID=S.CNO;/* Omit inner keywords */
Implicit in-connection
Is the connection query we just mentioned.
SELECT * FROM classes c,student s WHERE c.cid=s.cno;
External connection
Left OUTER join: Left table as base
Left OUTER JOIN on
SELECT * FROM classes C left OUTER joins student s on C.cid=s.cno;
The result of the class table as a base query is:
Right outer join: Right table as base
Right outer join on
SELECT * FROM classes C right OUTER joins student s on C.cid=s.cno;
The result of the student table as the base query is:
Summarize left and right connections using a single graph
3. Sub-query
Subquery: A SELECT statement contains another SELECT statement
Location of sub-query
Where: As a part of a condition that is queried as a bar
From after: Make table;
You can also use the following keywords when a subquery appears as a condition after the where:
-
- Any
- All
The form of a subquery result set:
-
-
- Single row (for conditions)
- Single-row multi-column (for conditions)
- Multiline single row (for conditions)
- Multi-row multiple columns (for tables)
Check the student's birthday after 91 years of class information. SELECT * FROM classes where CID in (SELECT CNO from student where birthday > ' 1991-01-01 ');
Subquery with exists? Query the student's birthday is greater than January 1, 91, if the record exists, the preceding SQL statement executes the SELECT * from classes where exists (select CNO from student where birthday > ' 1991-01-01 ');
subquery with any SELECT * from classes WHERE CID > No (select CNO from student)
Subquery with all SELECT * from classes WHERE CID > All (select CNO from student)
Transaction
features of the transaction (interview questions)
Atomicity : A transaction is an indivisible unit of work that either occurs in a transaction or does not occur.
Consistency: A transaction must transform the database from one consistent state to another. The total amount before and after the transfer is not changed.
Isolation: Transaction isolation is when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.
Persistence: When a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.
4, the isolation level of the transaction
Stolen read: Refers to a transaction that reads uncommitted data from another transaction.
non-REPEATABLE read: Reads a row of data from a table within a transaction, with different read results. One transaction reads the data after the commit of another transaction. (update)
Virtual Read (Phantom Read): Refers to the data that is inserted into another transaction within a transaction, resulting in inconsistent read and backward reads. (insert)
The database prevents this from occurring by setting the isolation level of the transaction:
* 1,readuncommitted: stolen, non-repeatable reading, virtual reading is possible.
* 2,readCOMMITTED: avoid stolen. The non-repeatable read and the false read are all possible. (Oracle default)
* 4,repeatable READ: avoid dirty reading, non-repeatable reading. Virtual reading can happen. (mysql default)
* 8,SERIALIZABLE: Avoid dirty reading, non-repeatable reading, virtual reading.
Higher levels, lower performance, more secure data
in MySQL:
to view the current transaction isolation level: SELECT @ @TX_ISOLATION;
to change the current transaction isolation level: SET TRANSACTION Isolation level one of four levels.
Setting the isolation level must precede the transaction
Exercise: Set the isolation level of a transaction to READ UNCOMMITTED
Time |
Thread 1 |
Thread 2 |
Description |
T1 |
Begin |
|
|
T2 |
SELECT * from account where name= ' ZS '; Result block |
|
|
T3 |
|
Begin |
|
T4 |
|
Update account set money=money+100 where name= ' ZS '; |
|
T5 |
SELECT * from account where name= ' ZS '; Result 1100 block |
|
Read data from another thread that did not commit the transaction. The stolen reading happened. |
T6 |
|
Commit |
|
T7 |
SELECT * from account where name= ' ZS '; Result 1100 block |
|
Read the update data for another thread to commit the transaction . Non-repeatable reads have occurred |
T8 |
|
Insert into account values (4, ' Zl ', 1000); Executes An INSERT statement that inserts data, and the transaction is automatically committed |
|
T9 |
SELECT * from Account; 4 data found |
|
Read the Insert statement data for another thread that commits the transaction automatically . The virtual reading took place |
T10 |
Commit |
|
|
DAY15 (MySQL multi-table query, transaction)