MySQL multi-table connection query

MySQL multi-table connection query

Multi-table Connection query:

CREATE TABLE Class (

CID int primary Key auto_increment,

CNAME varchar (20)

) default charset= ' UTF8 ';

Create Tablr Stu (

Sid Int Primary Key auto_increment,

sname varchar (20).

CID int

);d efault charset= ' UTF8 ';

INSERT into Class (name) values (' one shift ');

INSERT into Class (name) values (' Class two ');

INSERT into Class (name) values (' Class three ');

INSERT into Class (name) values (' Class four ');

INSERT into Class (name) VALUES (' Zhang San ', 1);

INSERT into Class (name) VALUES (' John Doe ', 2);

INSERT into Class (name) VALUES (' Harry ', 2);

INSERT into class values (' Yang Six ', 3);

INSERT into Class (name) values (' Zhao Qi ', 5);

INSERT into Class (name) values (' Week eight ', 6);

SELECT * from class;

SELECT * from Stu;

-------------------------------------

Cross join:--no conditions, just to show all the data in multiple tables

SELECT * from Class,stu;

Internal connection: Established between two or more tables, there is a correlation between the data

INNER JOIN

SELECT * FROM Table a INNER join table B on join condition;

Equivalent to

SELECT * FROM Table A, table B where table A. field = table B. field;

Two tables:

SELECT * FROM class INNER joins Stu on Class.cid=stu.cid;

SELECT * from Class,stu, table C WHERE class.cid=stu.cid and table A. field = Table C. Field;

Three sheets:

SELECT * FROM student s,course C,score SC WHERE s.sid=sc.sid and c.cid=sc.cid;

External connection:

Left outer connection Join/left OUTER join

Right outer connection Join/right OUTER join

Outer joins: In the case of the left or right table as the primary table, the entire contents of the main table are displayed, and the other table does not have the corresponding data to fill the null

SELECT * FROM class C left joins Stu S on C.cid=s.cid;

SELECT * FROM class C Right joins Stu S on C.cid=s.cid;

MySQL multi-table connection query