MySQL (5), mysql

MySQL (5), mysql
1 connection query Overview

• Join multiple tables (which can be greater than 2) for record (concatenate data according to a specified condition ).
• Final result: the number of records may change, and the field book will definitely increase (at least two tables will be merged ).

 

• Join query: join. Usage: Left table join right table
  • Left table: table on the left of the join keyword
  • Right table: table on the right of the join keyword

 

• Connection query: When you view data, the data to be displayed comes from multiple tables.

 

• Category of connection query:
  • Cross join
  • Internal Connection
  • External Connection
  • Natural connection

 

2. Cross join

• Cross join: cross join. Each record is retrieved cyclically from one table. Each record is matched in another table and unconditionally matched, and the fields of the join itself are increased, the final result is Cartesian product.

 

• Basic Syntax: Left table cross join right table ==== from left table and right table.

Select * from left table and right table;

Select * from left table cross join right table;

 

• My_class table

CREATE TABLE my_class(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    room VARCHAR(20));

• My_student table

 CREATE TABLE my_student(    id INT PRIMARY KEY AUTO_INCREMENT,    number VARCHAR(20),    sex CHAR(1),    NAME VARCHAR(10),    age INT,    height INT,    cid INT,    CONSTRAINT fk_id FOREIGN KEY (cid) REFERENCES my_class(id));

• Insert data

-- INSERT class insert into my_class VALUES (NULL, 'java001 class', 'a05 '); insert into my_class VALUES (NULL, 'linux class', 'b26 '); insert into my_class VALUES (NULL, 'class C ', 'd11'); -- insert into my_student VALUES (NULL, 'haha002', 'male', 'zhangsan ', 20,185, 1); insert into my_student VALUES (NULL, 'haha003 ', 'femal', 'lily', 15,175, 2); insert into my_student VALUES (NULL, 'hahaha004 ', 'Female, 'wang 5', 35,180, 3); insert into my_student VALUES (NULL, 'hahaha005 ', 'mal', 'zhao 6', 19,181, 3 ); insert into my_student VALUES (NULL, 'haha006 ', 'female', 'tian 7', 34,164, 2); insert into my_student VALUES (NULL, 'haha007 ', 'femal ', 'bastard, 11,1519, 1 );

 

• Cross join

SELECT * FROM my_student CROSS JOIN my_class;

 

• Cartesian Product has no significance: Avoid it as much as possible (cross join is useless ).
• The value of cross-join: to ensure the integrity of the connection structure.

 

3 inner connection

• Inner join: [inner] join: Extracts each record from the left table and matches all records in the right table, the matching results are retained only when a condition is the same in the left and right tables. Otherwise, the results are not retained.

 

• Basic Syntax:

Select * from left table [inner] join right table on left table. Field = right table. field;

• • On indicates the connection condition. The condition fields indicate the same service meaning (for example, my_student.cid and my_class.id)

 

• Internal Connection

SELECT * FROM my_student INNER JOIN my_class ON my_student.`cid` = my_class.`id`;

 

• Use of field aliases and table aliases: When querying data, different tables have fields with the same name. In this case, you need to add a table name to distinguish them. The table name is too long and can usually be used as an alias.

 

SELECT c. 'id' class id, c. 'name' class name, c. 'Room 'class classroom, s. 'id' student id, s. 'name' Student name, s. 'Sex 'Student gender, s. 'number' student ID, s. 'age' student age, s. 'height' student height FROM my_student s inner join my_class c ON s. 'cid' = c. 'id ';

 

• Set cid = null for id = 5

update my_student set cid = null where id = 5;

SELECT c. 'id' class id, c. 'name' class name, c. 'Room 'class classroom, s. 'id' student id, s. 'name' Student name, s. 'Sex 'Student gender, s. 'number' student ID, s. 'age' student age, s. 'height' student height FROM my_student s inner join my_class c ON s. 'cid' = c. 'id ';

 

• Internal Connections can also use where to replace the on keyword (the efficiency of where without on is high)

SELECT c. 'id' class id, c. 'name' class name, c. 'Room 'class classroom, s. 'id' student id, s. 'name' Student name, s. 'Sex 'Student gender, s. 'number' student ID, s. 'age' student age, s. 'height' student height FROM my_student s inner join my_class c WHERE s. 'cid' = c. 'id ';

 

4 external connections

• Outer join: outer join, which is dominated by a table. All records in the table are retrieved and connected to another table. No matter whether the above conditions can be matched, the table will be retained, it can be matched. It is retained correctly and cannot be matched. Fields in other tables are left blank.

 

• External join is divided into two types: A table location: A master table
  • Left outer join: left outer join. The left table is the primary table.
  • Right outer join: right outer join. The right table is the primary table.

 

• Basic Method: left table left/right join right table on left table. Field = right table. field;

 

• Left Outer Join-left table as the master table: the number of final records is at least not less than the number of existing records in the left table

SELECT s.*,c.name ,c.room FROM my_student s LEFT OUTER JOIN my_class c ON s.`cid` =  c.`id`;

 

• Outer right connection-opposite to outer left connection.

 

 

5. natural connection

• Natural join: natural join, natural join, that is, automatic matching of the connection condition: the system uses the field name and word as the matching mode (fields with the same name are used as the condition, and multiple fields with the same name are used as the condition)

 

• Natural connection: natural inner connection and natural outer link.

 

• Natural inner join: fields with the same name are automatically used as the join conditions. fields with the same name are merged after the connection.

SELECT * FROM my_student NATURAL JOIN my_class;

 

 

• Natural outer join: left table natural left/right join right table;

SELECT * FROM my_student NATURAL LEFT JOIN my_class;

 

6 tips

• Normally, we use the most internal connections and external links. There is nothing to do with cross-connections and natural connections.