MySQL - detailed introduction and examples of multi-table query

1. The relationship between tables

One-to-one: user table and identity information table, the user table is the main table
For example: man table, woman table

  create table man( 
   mid int primary key  auto_increment,
        mname varchar(32),
        wid int unique
    );

 create table woman(
        wid  int primary key  auto_increment,
        wname varchar(32)
    );

One-to-many: the most common table relationship, user table and order table
For example: employee table, department table

 create table emp(
            empno int primary key  auto_increment,
            ename varchar(32),
            deptno int
        );

 create table dept(
            deptno int primary key  auto_increment,
            dname varchar(32)
        );

Many-to-many: for example In student tables and course schedules, the many-to-many relationship is usually split into a one-to-many or many-to-one relationship.

create table student(            sid  int primary key  auto_increment,
            sname varchar(32)
        );

insert into student (sname) values ('大拿');     
insert into student (sname) values ('唐嫣');
insert into student (sname) values ('王健林');

 create table course(
            cid int primary key  auto_increment,
            cname varchar(32)
        );

insert into course (cname) values ('语文');    
insert into course (cname) values ('数学');    
insert into course (cname) values ('英语');    
insert into course (cname) values ('化学');

create table s_c(
       cid int,        sid int
   );

 insert into s_c (sid,cid) values (1,1);    
 insert into s_c (sid,cid) values (1,2);    
 insert into s_c (sid,cid) values (1,3);    
 insert into s_c (sid,cid) values (1,4);    
 insert into s_c (sid,cid) values (2,2);    
 insert into s_c (sid,cid) values (2,4);    
 insert into s_c (sid,cid) values (3,1);    
 insert into s_c (sid,cid) values (3,3);

2. Why use multiple tables

To avoid a large amount of data redundancy.
It is not that the more tables can be split, the better. Split them according to the actual situation.

3. Concept

Query multiple tables simultaneously

4.Classification

<1>Merge query
union, union all

Merging result sets means merging the query results of two select statements together. (Equivalent to union)
The two merged results, the number of columns, the order of the columns, and the class need to be consistent

create table emp(
       empno int primary key  auto_increment,
       ename varchar(32)
   );

create table dept(
        deptno int primary key  auto_increment,
        dname varchar(32)
    );

select * from emp  union select * from dept;    
select * from emp  union all select * from dept;

<2>Connection query
Employee table

  create table emp(
            empno int primary key auto_increment, # 员工编号
            ename varchar(32),  #员工姓名
            job varchar(32),        #员工职位
            mgr  int,                       #上级编号
            hiredate date,          #入职时间
            sal double,                 #薪水
            comm double,                #奖金
            deptno int                  #所属部门
        );

Department table

 create table dept(
            deptno int primary key auto_increment,  #部门编号
            dname varchar(32),      #部门名称
            loc varchar(32)             #部门地址
        );

Inner join: inner join....on , join , ,
inner join is a comparison operator and only returns rows that meet the conditions

For example:

select * from emp inner join  dept  on emp.deptno=dept.deptno;        
select * from emp e ,dept d where e.deptno = d.deptno;        
select * from emp e join dept d where e.deptno = d.deptno;

Outer join:
Left outer join: LEFT OUTER JOIN | left join ... on
Represents the query, all the rows on the left, if there is no row on the right, null

select * from emp e LEFT OUTER JOIN  dept d ON e.deptno = d.deptno;

Right outer join: right join ... on or right outer join .... on
The right join contains all the rows of the right table in the right join. If a row in the left table does not match the right table, Then the corresponding departments of the left table in the result are all empty (null)

 select * from emp e right OUTER JOIN  dept d ON e.deptno = d.deptno;

Self-join:
Self-join means that in the same data table, it is regarded as two tables, It means to find the leader of each person. If there is no leader, it will display no leader
Think of one table as two tables, one employee table and one leader table, both are emp tables

select e.ename,el.ename from emp e left join emp el on e.mgr = el.empno;

Natural join: natural join (join) | natural left join (same as left join) | natural right join (same as right join)

Natural join will automatically judge and use the same fields in the two tables as the connection Condition, return query results.

select * from emp natural join dept;       
select * from emp NATURAL left join dept;        
select * from emp NATURAL right join dept;

Note: If the inner connection does not write the connection condition, the result of the Cartesian product will appear. This situation should be avoided, and if the outer connection does not write the connection condition, an error will be reported.

##<3>Subquery (ANY subquery, IN subquery, SOME subquery, ALL subquery)

Problem solved by subquery:
Whose salary Taller than Cong Hao? ? ?

 select * from emp where sal >(select sal from emp where ename='从浩');

Definition: Subquery allows one query to be nested in another query

Subquery is also called an internal query, which is equivalent to an internal query. A query that contains an inner query is called an outer query. The results of the subquery are used by the main query.

Attention issues:        

1. Parentheses  

2. You can use subqueries after where select having from in the main query  

3. Subqueries cannot be used after group by

4. The main query and the subquery do not need to be in the same table; only the value returned by the subquery can be used by the main query.

Requirement: Query employee information whose department name is human resources

The first way: using subquery

select * from emp where deptno=(select deptno from dept where dname='人力部');

The second way: using related query

 select * from emp e,dept d where e.deptno = d.deptno and d.dname='人力部';

SQL Optimization: Try to use multiple table query

vast majority of sub -query and finally execute it into a multi -table query to execute. It can be seen through the SQL execution plan.
Through the SQL execution plan, you will find that the execution of the two methods is the same.

5.from后面的子查询
需求：
查询员工号 姓名 月薪

select empno,ename,sal from emp;

6.一般不在子查询中排序

7.一般先执行子查询，再去执行主查询

ANY关键字

假设any内部的查询返回结果个数是三个，如：result1，result2，result3，那么

select .... from .. where a > any(...);
->select ..... from ... where a > result1 or a >result2  or a >result3;

需求：
查询工资比1号部门中任意一个员工高的信息

select * from emp where sal > any(select sal from emp where deptno = 1);

ALL关键字

ALL关键字与any关键字类似，只不过上面的or改成and ：

select .... from .. where a > all(...);
->select ..... from ... where a > result1 and a >result2  and a >result3;

需求:
查询工资比1号部门中所有员工号的员工信息

   select * from emp where sal > all(select sal from emp where deptno = 1);

SOME关键字

some 关键字和any关键字是一样的功能。所以：

select .... from .. where a > any(...);

->select ..... from ... where a > result1 or a >result2  or a >result3;

IN关键字

IN运算符用于where表达式中，以列表向的形式支持多个选择。语法如下：

where column in (v1,v2,v3,.....);        
where column not in (v1,v2,v3,.....);

当in前面加上not运算符时候，表示与in相反的意思，既不在这写列表项中选择。

案例：
查询部门名称是人力和研发的员工

 select * from emp where deptno in   (select deptno from dept where dname='人力部' or dname='研发部')
分类: MySQL数据库

想了解更多相关问题请访问PHP中文网：MySQL视频教程

The above is the detailed content of MySQL - detailed introduction and examples of multi-table query. For more information, please follow other related articles on the PHP Chinese website!