MySQL：基础教程-10（个人笔记）

目录

显示多个表的记录

连接多个表并显示（内连接）

显示多个表的所有记录（外链接）

自连接

从select的记录中select（子查询）

使用in

---

显示多个表的记录

格式：将两个表的记录合并起来显示

select 列名1 from表名1 union select 列名2 from表名2;

格式：将两个表的记录合并起来显示（换行+缩进）

select
    列名1
from
    表名1
union
    select
        列名2
    from
        表名2；

ex：

合并具有相同列结构的表tb1和tb2的记录

select* from tb1 union select * from tb2;

或者

(select * from tb1) union (select * from tb2);

使用union合并3个以上的表

ex：

将empid为A102、A103、A104、A107的记录一个一个地SELECT出来，然后使用UNION进行合并的示例如下所示

(select * from tb where empid = 'A102')
union
(select * from tb where empid = 'A103')
union
(select * from tb where empid = 'A104')
union
(select * from tb where empid = 'A107');

或者：

select * 
	from tb
where empid
    not in ('A101');

或者

select * 
	from tb
where empid
	in ('A102','A103','A104','A107');
    

按条件合并多条提取结果进行显示

ex：

表tb中销售额（sales）大于等于200万元的员工的员工号（empid）

表tb1中年龄（age）大于等于35岁的员工的员工

将它们合并起来

(select empid from tb where sales >= 200)
union
(select empid from tb1 where age >=35);

合并显示多条提取结果（允许重复）

使用union加上all 来省去消除重复记录的操作

(select empid from tb where sales >= 200)
union all
(select empid from tb1 where age >=35);

---

连接多个表并显示（内连接）

格式：连接两个表

on的后面要写上作为连接键的列条件；

select 列名
    from 表1
join 要连接的表2
    on表1的列 = 表2的列；

select* from tb join tb1 on tb.empid = tb1.empid;

内连接

把不同的表中相匹配的记录提取出来的连接方式称为内连接：

格式：

select * from 列表名1 inner join 列表名2 on 列表1名.列 = 列表2名.列

选择列进行显示

select tb.empid, tb1.name, tb.sales
    from tb
inner join tb1
    on tb.empid = tb1.empid;

给表添加别名

格式：

表名 AS 别名

ex：

给表tb添加别名 ‘x'

select * from tb as x;

ex:

将表tb的列empid与表tb1的列empid相匹配的记录进行连接，然后显示表tb的列empid、表tb1的列name和表tb的列sales。但是，这些处理要在给表tb添加别名“x”和给表tb1添加别名“y”的前提下进行。

select x.empid, y.name, x.sales
from tb as x
join tb1 as y
on x.empid = y.empid;

在使用相同列名进行指定的情况下，可以使用using（作为连接键的列名）；

select tb.empid, tb1.name, tb.sales
    from tb
join tb1
    using(empid);

通过where设置条件从连接中提取记录

ex：

将表tb和表b1进行连接，显示表tb的列sales中值大于等于100的记录。显示列empid、列name和列sales，并给它们分别加上别名“员工号”“姓名”和“销售额”。

select x.empid as 员工号, y.name as 姓名, x.sales as 销售额
from tb as x
join tb1 as y
on x.empid = y.empid
where x.sales >= 100;

---

提取多个表中的记录

格式：对多个表进行内连接

select ~ from
表1
join 表2 连接条件
join 表3 连接条件
...
；

ex：

表tb、表tb1和表tb3的列empid中存在共同的值。那么我们以列empid为连接键，试着连接销售信息表tb、员工信息表tb1和员工出生地信息表tb3，显示员工号（表tb的empid）、销售额（表tb的sales）、姓名（表tb1的name）和出生地（表tb3的region）这4个列。

select x.empid, x.sales, y.name, z.region
from tb as x
join tb1 as y
using (empid)
join tb3 as z
using (empid);

使用 join on 而不是using 方法：

select x.empid, x.sales, y.name, z.region
from tb as x
join tb1 as y
on x.empid = y.empid
join tb3 as z
on y.empid = z.empid;

---

显示多个表的所有记录（外链接）

使用join on 连接tb和tb1，显示empid，name，sales，连接键条件 tb1和tb中empid；

select x.empid, y.name, x.sales
    from tb as x
join tb as y
    on x.empid = y.empid;

使用内连接- 表tb和表tb1

（使用了JOIN（或者INNER JOIN）的“内连接”只会提取与连接键相匹配的记录）

select tb.empid, tb1.name, tb.sales
    from tb
inner join tb1
    on tb.empid = tb1.empid;

外链接：即使与连接键不匹配，外连接也会提取另一个表中的所有记录

外连接- 左外连接（left join）

格式：左外连接

select 列名
    from 表1
left join 要连接的表2
    on表1的列=表2的列；

ex：

select tb.empid, tb1.name
from tb
left join tb1
on tb.empid = tb1.empid 
order by empid;

使用右外连接

格式：右外连接

select 列名
    from 表1
right join 要连接的表2
    on 表1的列 = 表2的列；

ex：

select tb.empid, tb1.name
from tb
right join tb1
using (empid);

加上outer后的书法方式

# 右链接
right outer join


# 左连接
left outer join

---

自连接

将表与其自身，也就是和同名的表进行连接，称为自连接

格式：自连接

select 列名 from 表名 as 别名1 join 表名 as 别名2；

ex：对员工信息表tbl进行自连接，并把所有的列显示出来。

select * 
from tb1 as x
join tb1 as y;

排序的技巧：必须通过组合使用order和group等关键字来完成处理

例子：

select a.name, a.age, count(*)
from tb1 as a 
join tb1 as b
	where a.age <= b.age
group by a.empid;

---

从select的记录中select（子查询）

ex：

显示销售信息表tb中的销售额最高（sales最大）的员工记录

select * from tb
where sales in 
(select max(sales) from tb);

group by 函数： max、avg、sum等聚合函数称为‘group by函数’，这类函数用于处理分组后的值；

ex：

计算员工信息表tb1中员工的平均年龄，并提取大于等于平均年龄的员工的记录

select * from tb1
where age >= (select avg(age) from tb1);

---

使用in

格式：子查询的语句

select 显示的列 from 表名
    where 列名 in (通过子查询select 语句提取的列);

ex:
 

select * from tb1
where empid in
(select empid from tb where sales >= 200);

使用exists 仅以存在的记录为对象

select * from tb1
where exists
(select * from tb where tb.empid = tb1.empid);

not exists 以子查询不踢去的记录为对象进行处理

select * from tb1
where not exists
(select * from tb where tb.empid = tb1.empid);

排序的技巧二：

create table tb_rank like tb;
alter table tb_rank add c_rank int auto_increment primary key;
insert into tb_rank (empid, sales, month)
(select empid,sales,month from tb order by sales desc);
select * from tb_rank;