Completely master mysql multi-table operations

This article brings you relevant knowledge about mysql, which mainly introduces issues related to multi-table operations, including multi-table relationships, foreign key constraints, multi-table joint queries, internal Connection query and outer connection query, etc. I hope it will be helpful to everyone.

Recommended learning: mysql tutorial

Multi-table relationship

One-to-one (usually merged tables) ,
One-to-many/many-to-one (departments and employees),
Many-to-many (students and courses) - using intermediate tables

Foreign key constraint concept

Specialized Constraints for multi-table relationships
Control the foreign keys of the slave table through the primary key of the master table

Foreign key constraints:

1. The master table must already exist, or Creating
2. The primary key column must be set for the main table
3. The primary key cannot include null values, but the foreign key can include null values
4. Specify the column after the table name of the main table or A combination of column names. This column or combination of columns must be the primary key or candidate key of the main table
5. 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 columns in the foreign key must be the same as the number of columns in the primary key The data types of the corresponding columns are the same

• Create foreign key constraintsforeign key
Add foreign key constraints before creating the table

Add foreign key constraints after creating the table

Verify the role of foreign key constraints

1. Data insertion:
You must first add it to the main table Constraints
Adding constraints from the slave table depends on the master table. Data that does not exist in the master 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
The data from the table can be deleted arbitrarily

eg:

delete from dept where deptno = '1001';-----不可以删除（被依赖了）delete from dept where deptno = '1004';
------可以删除delete from emp where eid = '7';  -----可以删除

Delete the foreign key constraint

After deletion, there will be a gap between the tables. It doesn’t matter anymore
Syntax:

alter table 表名字 drop foreign key 外键约束名alter table emp2 drop foreign key emp2_fk;

• Many-to-many relationship - constructing foreign key constraints
One row of table A corresponds to multiple rows of table B, and table B One row of corresponds to multiple rows of table A. At this time, you need to re-create an intermediate table to record the table relationship

Note:
Modify and When deleting, the middle slave table can be deleted and modified at will, but the data relied on by the slave tables on both sides and the master table cannot be deleted or modified.

Multi-table joint query (very important)

• Concept
is to query two or more tables at the same time, because sometimes users When viewing data, the data that needs to be displayed comes from multiple tables

Note:
Foreign key constraints only affect the addition, deletion and modification of data and have no effect on data query

select * from A,B;---redundant data will be generated

two tables The Cartesian product of all data rows 2. The Cartesian set can be understood as each row of one table matching any row of another table
3. Suppose table A has m rows of data , table B has n rows of data, then
m * n rows of data will be returned. 4. Cartesian product will produce a lot of
redundant data, and other subsequent queries can be done in this Conditional filtering based on the set

intersection between the two tables

inner You can omit

select * from A,B where 条件；

select * from A inner join B on 条件

－－－查询每个部门的所属员工  //隐式内连接

select* from dept3,emp3 where dept3.deptno = emp3.dept_id;  //这样写标准

select* from dept3  a ,emp3 b  where a.deptno = b.dept_id;

	－－－查询每个部门的所属员工  
	//显式内连接select *from dept3 inner join emp3  on dept3.deptno = emp3.dept_id;  
	//这样写标准

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

Left outer join
left outer join、

select* from A left outer join B on 条件；

right outer join、

select* from A right outer join B on 条件；

full outer join

select* from A full outer join B on 条件;

注意：
Oracle 里面有 full join ,可是在mysql 对 full join 支持的不好，我们可以使用 union来达到目的

－－－－外连接查询
－－－－查询哪些部门有员工，哪些部门没有员工

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

－－－－查询哪些员工有对应的部门，哪些没有

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

－－－－使用 union 关键字实现左外连接和右外连接的并集

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;

－－－－外连接查询
－－－－查询哪些部门有员工，哪些部门没有员工

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;

－－－－外连接多个表

select* from Aleft join B on 条件1left join C on 条件2left join D on 条件3；

－－－－查询哪些员工有对应的部门，哪些没有

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 Aright joinB on条件1，right joinC on条件2，right joinD on条件3；

－－－－实现满外连接： full join
－－－－使用 union 关键字实现左外连接和右外连接的并集
－－－－select * from dept3 a full join emp3 b on a.deptno = b.dept_id; －－不能执行

－－－－union是将两个查询结果上下拼接，并去重

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 是将两个查询结果上下拼接，不去重

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

• 基本子查询
• 子查询关键字－ALL
• 子查询关键字－ANY ,SOME
• 子查询关键字－IN
• 子查询关键字－EXISTS
• 自关联查询

推荐学习：mysql视频教程

The above is the detailed content of Completely master mysql multi-table operations. For more information, please follow other related articles on the PHP Chinese website!