Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to master mysql multi-table operation," interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "how to master mysql multi-table operation"!

multitable relation

One-to-one (usually combined tables),

One-to-many/many-to-one (departments and employees),

many-to-many (students and courses)-use middle table

Foreign key constraint concept

Constraints specific to multi-table relationships

Control the foreign key of the slave table through the primary key of the master table

Foreign key constraints:

The master table must already exist or be being created.

2. Primary key columns must be set for the primary table

Primary keys cannot contain null values, but foreign keys can.

4. The combination of the list or column names specified after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the primary table

The number of columns in the foreign key must be the same as the number of columns in the primary key.

6. The data type of the column in the foreign key must be the same as that of the corresponding column in the primary key.

·Create foreign key constraints

Add foreign key constraints before creating tables

Add foreign key constraints after creating tables

Verify the role of foreign key constraints

1. Data insertion:

To add constraints to the master table

Adding constraints from a table depends on the main table. Data that is not in the main table cannot be added.

2. Data deletion

When the data of the master table is dependent on the slave table, it cannot be deleted; otherwise, it can be deleted.

Data from the table can be deleted at will

eg：

delete from dept where deptno = '1001';----cannot delete (is dependent on) delete from dept where deptno = ' 1004';----can delete from emp where eid = '7';----can delete delete foreign key constraints

After deletion, there is no relationship between tables

Grammar:

alter table name drop foreign key foreign key constraint name alter table emp2 drop foreign key emp2_fk;

·Many-to-many relationships-building foreign key constraints

One row in Table A corresponds to multiple rows in Table B, and one row in Table B corresponds to multiple rows in Table A. At this time, an intermediate table needs to be re-established to record the table relationship.

Note:

When modifying and deleting, the middle slave table can be deleted and modified at will, but the data dependent on the master table of the two slave tables cannot be deleted or modified.

Multi-table union queries (very important)

·Concept

That is, querying two or more tables at the same time, because sometimes when users view data, the data they need to display comes from multiple tables.

·Data preparation

Note:

Foreign key constraints only affect the addition, deletion and modification of data, and have no effect on data queries.

·Cross-join queries select * from A, B;---will generate redundant data

1. The cross-join query returns the Cartesian product of all the data rows of the two joined tables.

A Cartesian set can be understood as each row of a table matching any row of another table.

3. If table A has m rows of data and table B has n rows of data, m * n rows of data are returned.

4. Cartesian product will produce a lot of redundant data, and other queries in the later stage can be conditional filtered on the basis of this set.

inner join query

Find the intersection of two tables

Inner can be omitted

Implicit inner join (SQL92 standard):

select * from A,B where condition;

explicit inner joins (SQL99 standard);

select * from A inner join B on Condition---Query the employees of each department//Implicit inner join select* from dept3,emp3 where dept3.deptno = emp3.dept_id; //Write the standard like this

You can also alias the table, such as;

select* from dept3 a ,emp3 b where a.deptno = b.dept_id; ---Query the employees of each department //select *from dept3 inner join emp3 on dept3.deptno = emp3.dept_id; //Write the standard like this

You can also alias the table, such as;

select *from dept3 a join emp3 b on a.deptno = b.dept_id;

outer join query

The outer part can be omitted.

Left outer join,

select* from A left outer join B on condition;

Right outer join,

select* from A right outer join B on condition;

Full outer join

select* from A full outer join B on condition;

Note:

Oracle has a full join , but mysql is not good for full join support, we can use union to achieve the goal

----outer join query

---Query which departments have employees and which departments do not have employees

use mydb3;select* from dept3 left outer join emp3 on dept3.deptno =emp3.dept_id;

---Query which employees have corresponding departments and which do not

select* from dept3 right outer join emp3 on dept3.deptno =emp3.dept_id;

----Union of left and right outer joins using union keyword

select* from dept3 left outer join emp3 on dept3.deptno=emp3.dept_idunionselect* from dept3 right outer join emp3 on dept3.deptno =emp3.dept_id;

----outer join query

---Query which departments have employees and which departments do not have employees

usemydb3;select* from dept3 a left outer join emp3 b on a.deptno = b.dept.idselect* from dept3 a left join emp3 b on a.deptno = b.dept_id;

----External join multiple tables

select* from Aleft join B on Condition 1left join C on Condition 2left join D on Condition 3;

---Query which employees have corresponding departments and which do not

select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;select* from dept3 a right join emp3 b on a.deptno = b,dept_id;select*from Right joinB on condition 1, right joinC on condition 2, right joinD on condition 3;

----Full-join

----Union of left and right outer joins using union keyword

---select * from dept3 a full join emp3 b on a.deptno = b.dept_id; --cannot execute

---union is to concatenate two query results from top to bottom and remove duplication

select* from dept3 a left join emp3 b on a.deptno = b.dept_idunionselect* from dept3 a right join emp3 b on a.deptno = b.dept_id

---union all is to concatenate two query results from top to bottom without duplication

select* from dept3 a left join emp3 b on a.deptno = b.dept_idunion allselect* from dept3 a right join emp3 b on a.deptno= b.dept_id

·Basic subqueries

·Subquery Keyword-ALL

Subquery keywords-ANY ,SOME

·Subquery Keyword-IN

·Subquery Keyword-EXISTS

·Self-associative queries

At this point, I believe everyone has a deeper understanding of "how to master mysql multi-table operation," so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.