MySQL database operations (7) Table query, transaction

Linked table Query

Internal connection ' [inner| Cross] JOIN '

Unconditional Internal connection:
Unconditional internal connection, aka Cross connect/Cartesian connection
Each item in the first table will be combined with each of the other tables in turn

Conditional Intra-connection:
On the basis of an unconditional inner join, add an ON clause
When connecting, filter out rows of meaningful records for stitching

Note whether the column names of two tables are the same when writing conditions,
If the time is the same, you need to precede the table name, tb_name.colname this form exists

Cases:
Unconditional internal Connection

SELECT *  from INNER JOIN ' Department ';

+------+--------+---------+----+----------+| s_id | name | dept_id | ID |    Name |+------+--------+---------+----+----------+| 1 |       David |  1 | 1 |    中文版 | | 1 |       David |  1 | 2 |    Art | | 1 |       David |  1 | 3 |    Computer | | 1 |       David |  1 | 4 |    Alchemy | | 2 |       Lucy |  3 | 1 |    中文版 | | 2 |       Lucy |  3 | 2 |    Art | | 2 |       Lucy |  3 | 3 |    Computer | | 2 |       Lucy |  3 | 4 |    Alchemy | | 3 |       Lily |  3 | 1 |    中文版 | | 3 |       Lily |  3 | 2 |    Art | | 3 |       Lily |  3 | 3 |    Computer | | 3 |       Lily |  3 | 4 |    Alchemy | | 4 |       John |  4 | 1 |    中文版 | | 4 |       John |  4 | 2 |    Art | | 4 |       John |  4 | 3 |    Computer | | 4 |       John |  4 | 4 |    Alchemy | | 5 |       Jack |  2 | 1 |    中文版 | | 5 |       Jack |  2 | 2 |    Art | | 5 |       Jack |  2 | 3 |    Computer | | 5 |       Jack |  2 | 4 |    Alchemy | | 6 | AlFred |  3 | 1 |    中文版 | | 6 |       Alfred |  3 | 2 |    Art | | 6 |       Alfred |  3 | 3 |    Computer | | 6 |       Alfred |  3 | 4 | Alchemy |+------+--------+---------+----+----------+

Conditional Intra-connection:

SELECT *  from INNER JOIN ' Department '  on dept_id=ID;

+------+--------+---------+----+----------+| s_id | Name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | 中文版  | |    2 | Lucy   |       3 |  3 | Computer | |    3 | Lily   |       3 |  3 | Computer | |    4 | John   |       4 |  4 | Alchemy  | |    5 | Jack   |       2 |  2 | Art      | |    6 | Alfred |       3 |  3 | Computer |+------+--------+---------+----+----------+

　　

Check out the name of the student, and the name of the school:

SELECT  from ' student ' s INNER JOIN ' Department ' d  on dept_id=ID;

+---------+------------+| Student | Department |+---------+------------+| David   | 中文版    | | Lucy    | Computer   | | Lily    | Computer   | | John    | Alchemy    | | Jack    | Art        | | Alfred  | Computer   |+---------+------------+

Outer JOIN ' {left| Right} [OUTER] JOIN '

Left outer connection: (Based on left table)
When two tables are connected, the connection conditions do not match.
Leave the data in the left table, and the data in the right table is filled with null

Right outer join: (The right table is the benchmark)
When connecting to two tables, when the connection condition does not match
Leave the data in the right table, and the data in the left table is filled with null

Cases:
Add data to student tables, add only names, resulting in dept_id null data in student tables

INSERT  into Student (name) VALUES ('xixi');

Add data to the college table, resulting in a college without students

INSERT  into  VALUES('Sport');

View all student table data

SELECT *  from student;

+------+--------+---------+| s_id | Name   | dept_id |+------+--------+---------+|    1 | David  |       1 | |    2 | Lucy   |       3 | |    3 | Lily   |       3 | |    4 | John   |       4 | |    5 | Jack   |       2 | |    6 | Alfred |       3 | |    7 | Xixi   |    NULL |+------+--------+---------+

The use of internal connection conditions can only see the students who have allocated a good college information, no newly added xixi and sport;

SELECT *  from INNER JOIN Department  on dept_id=ID;

+------+--------+---------+----+----------+| s_id | Name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | 中文版  | |    2 | Lucy   |       3 |  3 | Computer | |    3 | Lily   |       3 |  3 | Computer | |    4 | John   |       4 |  4 | Alchemy  | |    5 | Jack   |       2 |  2 | Art      | |    6 | Alfred |       3 |  3 | Computer |+------+--------+---------+----+----------+

Use the left link to take all the student's data out, the student does not have the college information with null padding, no sport Academy

SELECT *  from  Left JOIN Department  on dept_id=ID;

+------+--------+---------+------+----------+| s_id | Name   | dept_id | ID   | name     |+------+--------+---------+------+----------+|    1 | David  |       1 |    1 | 中文版  | |    5 | Jack   |       2 |    2 | Art      | |    2 | Lucy   |       3 |    3 | Computer | |    3 | Lily   |       3 |    3 | Computer | |    6 | Alfred |       3 |    3 | Computer | |    4 | John   |       4 |    4 | Alchemy  | |    7 | Xixi   |    NULL | NULL | NULL     |+------+--------+---------+------+----------+

Using the right outer connection to show the data of the college that is not currently available to students, there are sport schools, no students Xixi

SELECT *  from  Right JOIN Department  on dept_id=ID;

+------+--------+---------+----+----------+| s_id | Name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | 中文版  | |    2 | Lucy   |       3 |  3 | Computer | |    3 | Lily   |       3 |  3 | Computer | |    4 | John   |       4 |  4 | Alchemy  | |    5 | Jack   |       2 |  2 | Art      | |    6 | Alfred |       3 |  3 | Computer | | NULL | NULL   |    NULL |  5 | Sport    |+------+--------+---------+----+----------+

As a class teacher, I would like to see the student's (name, selected course name, affiliated college)

 select  s.name ' student ', c.name ' course ', d.name ' deparment '  left  join  ' select  ' se on  se.s_id =   s.s_id  left  join  course C  On  se.c_id =   c.id  left  join  Department D on  s.dept_id =  d.id; 

+---------+--------+-----------+| Student | Course | Deparment |+---------+--------+-----------+| Lucy    | Math   | Computer  | | John    | Math   | Alchemy   | | David   | Python | 中文版   | | John    | Python | Alchemy   | | David   | Music  | 中文版   | | Lucy    | Music  | Computer  | | John    | Music  | Alchemy   | | Lily    | NULL   | Computer  | | Jack    | NULL   | Art       | | Alfred  | NULL   | Computer  | | Xixi    | NULL   | NULL      |+---------+--------+-----------+

Group the course according to students and colleges, showing each student's course selection, and optimizing results

SELECTS.name Student,group_concat (C.name SEPARATOR',') Course,d.name Deparment from' student ' s Left JOIN`Select' SE ons.s_id=se.s_id Left JOIN' Course ' C onse.c_id=c.id Left JOIN' Department ' d ons.dept_id=d.idGROUP  byS.name,d.name;

+---------+-------------------+-----------+| Student | Course            | deparment |+---------+-------------------+-----------+| Alfred  | NULL              | Computer  | | David   | Python,music      | 中文版   | | Jack    | NULL              | Art       | | John    | Math,music,python | Alchemy   | | Lily    | NULL              | Computer  | | Lucy    | Music,math        | Computer  | | Xixi    | NULL              | NULL      |+---------+-------------------+-----------+

As a homestay tube, see Student's (name, age, gender, affiliated college)

 select  s.name, Stu.age,stu.sex,d.name deparment from   student s  left  join  ' stu_details ' stu on  s.s_id =   stu.s_id  left  Span style= "COLOR: #808080" >join  ' department ' d on  s.dept_id =  d.id; 

+--------+------+------+-----------+| Name   | age  | sex  | deparment |+--------+------+------+-----------+| David  |   18 | M    | 中文版   | | Lucy   |   19 | F    | Computer  | | Lily   |   16 | F    | Computer  | | John   |   20 | M    | Alchemy   | | Jack   | NULL | NULL | Art       | | Alfred | NULL | NULL | Computer  | | Xixi   | NULL | NULL | NULL      |+--------+------+------+-----------+

Transaction

Transaction: is a logical unit of work in a database run.
#原子性
The transaction must be an atomic unit of work, either all executed or not executed for its data modification.

#一致性
When a transaction is complete, you must keep all data in a consistent state.

#隔离性
Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction.
```

To ensure that updates to database records change from one consistent state to another
It is very necessary to use transactions for processing.

Cases:
Create a table of bank accounts

CREATE TABLE  INTPRIMARYKEYVARCHAR (not NULL INT);  

Add two user and user's deposit information

INSERT  into ' account ' (' name ', ' balance ') VALUES ('Max',10000), ('Van',2000 );

View Status:

SELECT *  from Account;

+----+------+---------+| ID | name | Balance |+----+------+---------+|  1 | Max  |   10000 | |  2 | Van  |    |+----+------+---------+

Suppose now that the user van bought 500 dollars in the store, now to transfer to the store, then you need to subtract 500 from the account of the small Ming, and then add 500 to the store's user max, but if there is a system failure in the process of reducing 500, and then restart to find Van's money buckle, But the store Max did not receive it, and this time there will be inconsistent data changes. For this kind of data modification we need to either modify the success at the same time, or modify the failure at the same time, so this needs to be done with a transaction.

Start TRANSACTION: Starting a new transaction
Commit: Commit the current transaction and make a permanent change
ROLLBACK: Rollback current transaction, discard modification

Start a transaction

TRANSACTION;

Van Buckle Money

UPDATE SET ' balance '= ' balance '-WHERE='Van ';

Check status, the money has been deducted:

SELECT *  from Account;

+----+------+---------+| ID | name | Balance |+----+------+---------+|  1 | Max  |   10000 | |  2 | Van  |    |+----+------+---------+

Use rollback to make changes to the data do not take effect back to the state before the transaction:

ROLLBACK;

View status, Money has been restored:

SELECT *  from Account;

+----+------+---------+| ID | name | Balance |+----+------+---------+|  1 | Max  |   10000 | |  2 | Van  |    |+----+------+---------+

Do a proper operation:

STARTTRANSACTION;UPDATE' Account 'SET' Balance '=' Balance '- - WHERE' Name '='Van';UPDATE' Account 'SET' Balance '=' Balance '+ - WHERE' Name '='Max';COMMIT;

View status, transaction completion:

SELECT *  from Account;

+----+------+---------+| ID | name | Balance |+----+------+---------+|  1 | Max  |   10500 | |  2 | Van  |    |+----+------+---------+

After the commit, the data modified successfully, rollback can not return to the previous.

ROLLBACK ; SELECT *  from Account;

+----+------+---------+| ID | name | Balance |+----+------+---------+|  1 | Max  |   10500 | |  2 | Van  |    |+----+------+---------+

　　

MySQL database operations (7) Table query, transaction