DAY15 (MySQL multi-table query, transaction)

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:

1. 1. Any
   2. All

The form of a subquery result set:

• • 1. Single row (for conditions)
    2. Single-row multi-column (for conditions)
    3. Multiline single row (for conditions)
    4. 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)