mysql - 进阶语句

分组 - group by

---

连表查询 - join…on…

语法

# join 关键字左右各有一个表
# inner join - 查找满足 condition 的条目(即 A B 交集)并显示, join 默认处理方式是这个
# left join - 取 A 全部显示, B 没有对应值显示为 null
# right join - 取 B 全部显示, A 没有对应值显示为 null
# full join - 取并集, 没有对应值显示为 null

select <select_list> from T1 [inner|left|right|full] join T2 on condition [where condition is null]

---

图示

---

示例表结构和数据内容

# 这是示例的表结构和数据内容

create table stu(
  name varchar(20),
  age int default 18,
  num int unique
);

create table score(
  num int unique,
  math int,
  chinese int
);

insert into stu(name, num) values
("Tom", 1001),
("Tim", 1002),
("Aki", 1003);

insert into score values
(1001, 100, 99),
(1002, 100, 100),
(1004, 99, 99),
(1005, 98, 100);

---

查询示例

inner join

select stu.name, s.math, s.chinese from stu inner join score as s on stu.num = s.num;

# 结果
# name	math	chinese
# Tom	100		99
# Tim	100		100

---

left join

select stu.name, s.math, s.chinese from stu left join score as s on stu.num = s.num;

# 结果
# name	math	chinese
# Tom	100		99
# Tim	100		100
# Aki	(null)	(null)			# 取 A 表全部, B 表没有对应值显示为 null

# ----------

# 后边加上 where T2.column is null, 只显示没有对应值的部分
select stu.name, s.math, s.chinese from stu left join score as s on stu.num = s.num\
where s.num is null;

# 结果
# Aki	(null)	(null)

---

right join

select stu.name, s.math, s.chinese from stu right join score as s on stu.num = s.num;

# 结果
# name		math	chinese
# Tom		100		99
# Tim		100		100
# (null)	99		99			# 取 B 表全部, A 表没有对应值显示为 null
# (null)	98		100

# ----------

# 后边加上 where T1.column is null, 只显示没有对应值的部分
select stu.name, s.math, s.chinese from stu left join score as s on stu.num = s.num\
where stu.num is null;

# 结果
# (null)	99		99
# (null)	98		100

---

full outer join (存疑, 有时候会报错)

# left join 和 right join 结果的并集
select stu.name, s.math, s.chinese from stu full outer join score as s on stu.num = s.num;

# 结果
# name	math	chinese
# Tom	100		99
# Tim	100		100
# Aki	(null)	(null)
# (null)	99		99
# (null)	98		100

# ----------

# 后边加上 where T1.column is null or T2.column is null, 只显示没有对应值的部分
select stu.name, s.math, s.chinese from stu full outer join score as s on stu.num = s.num\
where s.num is null or stu.num is null;

# 结果
# Aki	(null)	(null)
# (null)	99		99
# (null)	98		100

---

合并结果集 - union & union all

# 用于合并多个 select 语句的结果集
# 结果集 列 数量和对应数据类型相同

# 合并结果集, 去重
select * from stu1 union select * from stu2;

# 合并结果集, 不去重
select * from stu1 union all select * from stu2;

---

复制表内容 - insert into … select

# 需要先创建新表, 并且需要复制的数据类型对应一致

# 全部复制
insert into NEWTABLE select * from OLDTABLE;

# 条件复制
insert into NEWTABLE select * from OLDTABLE where condition;