导航号,我的单页导航
文章目录

前言

主要是记录一下join和where等命令的使用。根据《MySQL基础教程-西泽梦路》学习,简单的做一下笔记。

记录于此,方便自己回忆。

正文

我这以Window版的phpstudy软件验证。

需要进入这个目录,才可以使用mysql命令

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin

进入正文

创建tb1和tb2

create table tb1 (empid varchar(30), name varchar(30), age int);
create table tb2 (empid varchar(30), name varchar(30), age int);
# tb1
mysql> select * from tb1;
+-------+--------+------+
| empid | name   | age  |
+-------+--------+------+
| A101  | 佐藤   |   40 |
| A102  | 高桥   |   28 |
| A103  | 中川   |   20 |
| A104  | 渡边   |   23 |
| A105  | 西泽   |   35 |
+-------+--------+------+
# tb2
mysql> select * from tb2;
+-------+--------+------+
| empid | name   | age  |
+-------+--------+------+
| A106  | 中天   |   26 |
| A107  | 铃木   |   24 |
| A108  | 村警   |   25 |
| A110  | 几天   |   27 |
| A109  | 田中   |   23 |
+-------+--------+------+

tb3

create table tb3 (empid varchar(30),sales int,month int);
# tb3
mysql> select * from tb3;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A102  |    54 |     5 |
| A104  |   181 |     4 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
| A101  |   300 |     5 |
| A102  |   205 |     6 |
| A104  |    93 |     5 |
| A103  |    12 |     6 |
| A107  |    87 |     6 |
+-------+-------+-------+

进入正题。

union

使用union可以组合两个表。

PS: 就是多个select命令的组合

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

通过union,会把表名1和表名2根据条件进行组合。

mysql> select * from tb1 union select * from tb2;
+-------+--------+------+
| empid | name   | age  |
+-------+--------+------+
| A101  | 佐藤   |   40 |
| A102  | 高桥   |   28 |
| A103  | 中川   |   20 |
| A104  | 渡边   |   23 |
| A105  | 西泽   |   35 |
| A106  | 中天   |   26 |
| A107  | 铃木   |   24 |
| A108  | 村警   |   25 |
| A110  | 几天   |   27 |
| A109  | 田中   |   23 |
+-------+--------+------+

当然,上面的只是组合后临时展示的,如果要放入新的表中,可以用下面命令:

# 把组合的tb1和tb2放入创建的tb3中
create table tb3 select * from tb1 union select * from tb2;

加入tb1和tb2中有重复数据?

# 给tb2创建一条跟tb1一样的数据
insert into tb2 (empid,name,age) values("A101","佐藤",40);

然后通过union组合发现,默认情况组合后的数据不会有多条"A101",因为union默认会[消除重复记录]。

如果要允许重复,就需要使用 union all,组合所有的。

mysql> select * from tb1 union all select * from tb2;
+-------+--------+------+
| empid | name   | age  |
+-------+--------+------+
| A101  | 佐藤   |   40 |
| A102  | 高桥   |   28 |
| A103  | 中川   |   20 |
| A104  | 渡边   |   23 |
| A105  | 西泽   |   35 |
| A106  | 中天   |   26 |
| A107  | 铃木   |   24 |
| A108  | 村警   |   25 |
| A110  | 几天   |   27 |
| A109  | 田中   |   23 |
| A101  | 佐藤   |   40 |
+-------+--------+------+

JOIN

使用JOIN连接两个表。

select 查询列名 from 表名1 join 表名2 on 表名1.列名=表名2.列名;

我们以tb1和tb3连接。

mysql> select * from tb1 join tb3 on tb1.empid=tb3.empid;
+-------+--------+------+-------+-------+-------+
| empid | name   | age  | empid | sales | month |
+-------+--------+------+-------+-------+-------+
| A103  | 中川   |   20 | A103  |   101 |     4 |
| A102  | 高桥   |   28 | A102  |    54 |     5 |
| A104  | 渡边   |   23 | A104  |   181 |     4 |
| A101  | 佐藤   |   40 | A101  |   184 |     4 |
| A103  | 中川   |   20 | A103  |    17 |     5 |
| A101  | 佐藤   |   40 | A101  |   300 |     5 |
| A102  | 高桥   |   28 | A102  |   205 |     6 |
| A104  | 渡边   |   23 | A104  |    93 |     5 |
| A103  | 中川   |   20 | A103  |    12 |     6 |
+-------+--------+------+-------+-------+-------+
9 rows in set (0.00 sec)
内连接

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

如果要明确指出某一处理是内连接,可以将join部分写成inner join

join 默认是内连接,也就是可以不写inner

mysql> select * from tb1 inner join tb3 on tb1.empid=tb3.empid;
+-------+--------+------+-------+-------+-------+
| empid | name   | age  | empid | sales | month |
+-------+--------+------+-------+-------+-------+
| A103  | 中川   |   20 | A103  |   101 |     4 |
| A102  | 高桥   |   28 | A102  |    54 |     5 |
| A104  | 渡边   |   23 | A104  |   181 |     4 |
| A101  | 佐藤   |   40 | A101  |   184 |     4 |
| A103  | 中川   |   20 | A103  |    17 |     5 |
| A101  | 佐藤   |   40 | A101  |   300 |     5 |
| A102  | 高桥   |   28 | A102  |   205 |     6 |
| A104  | 渡边   |   23 | A104  |    93 |     5 |
| A103  | 中川   |   20 | A103  |    12 |     6 |
+-------+--------+------+-------+-------+-------+
选择列进行显示

上面是*显示所有的列(也就是表1和表2中组合的)。

假如只需要显示指定的呢?比如表1中的列和表2中的列?

mysql> select tb1.empid,tb1.name,tb3.sales from tb1 join tb3 on tb1.empid=tb3.empid;
+-------+--------+-------+
| empid | name   | sales |
+-------+--------+-------+
| A103  | 中川   |   101 |
| A102  | 高桥   |    54 |
| A104  | 渡边   |   181 |
| A101  | 佐藤   |   184 |
| A103  | 中川   |    17 |
| A101  | 佐藤   |   300 |
| A102  | 高桥   |   205 |
| A104  | 渡边   |    93 |
| A103  | 中川   |    12 |
+-------+--------+-------+
给表添加别名的方法

之前学习过,添加别名是用as

# table1是tb1的别名,当然,这种别名执行完就消失了,并没有啥作用。
select * from tb1 as table1;

但在join连接中就可以作用。

mysql> select table_x.empid,table_x.name,table_y.sales from tb1 as table_x join tb3 as table_y on table_x.empid=table_y.empid;
+-------+--------+-------+
| empid | name   | sales |
+-------+--------+-------+
| A103  | 中川   |   101 |
| A102  | 高桥   |    54 |
| A104  | 渡边   |   181 |
| A101  | 佐藤   |   184 |
| A103  | 中川   |    17 |
| A101  | 佐藤   |   300 |
| A102  | 高桥   |   205 |
| A104  | 渡边   |    93 |
| A103  | 中川   |    12 |
+-------+--------+-------+
9 rows in set (0.00 sec)

效果跟上面不使用别名一样。

using

上面join on

select 查询列名 from 表名1 join 表名2 on 表名1.列名=表名2.列名;

中用了[表名1.列名=表名2.列名]进行比较。而且上面例子中

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

比较的列名都是empid。

这种相同列名情况下,可以使用using(相同的列名)替换。

select 显示的列名 from 表1 join 表2 using(empid);

这种是相同列名下才可以使用。

来个例子

mysql> select tb1.empid,tb1.name,tb3.sales from tb1 join tb3 using(empid);
+-------+--------+-------+
| empid | name   | sales |
+-------+--------+-------+
| A103  | 中川   |   101 |
| A102  | 高桥   |    54 |
| A104  | 渡边   |   181 |
| A101  | 佐藤   |   184 |
| A103  | 中川   |    17 |
| A101  | 佐藤   |   300 |
| A102  | 高桥   |   205 |
| A104  | 渡边   |    93 |
| A103  | 中川   |    12 |
+-------+--------+-------+
通过WHERE设置条件从连接表中提取记录

join on或join using() 后还可以跟where设置条件。

mysql> select tb1.empid,tb1.name,tb3.sales from tb1 join tb3 using(empid) where tb3.sales>=100;
+-------+--------+-------+
| empid | name   | sales |
+-------+--------+-------+
| A101  | 佐藤   |   184 |
| A101  | 佐藤   |   300 |
| A102  | 高桥   |   205 |
| A103  | 中川   |   101 |
| A104  | 渡边   |   181 |
+-------+--------+-------+
5 rows in set (0.00 sec)

更复杂的这里不介绍了,具体请看书吧。

外连接

上面介绍的就是内连接,只会提取与连接键相同匹配的记录。

细心的你会发现,之前拼接的表中少了部分员工

外连接就是所有的记录,包括与连接键不相同的记录。

外连接氛围左外连接(left join)和右外连接(right join)。

内连接 : join 或inner join

左外连接 : left join

右外连接 : right join

# 左外连接
mysql> select tb1.empid,tb1.name,tb3.sales from tb1 left join tb3 on tb1.empid=tb3.empid;
+-------+--------+-------+
| empid | name   | sales |
+-------+--------+-------+
| A103  | 中川   |   101 |
| A102  | 高桥   |    54 |
| A104  | 渡边   |   181 |
| A101  | 佐藤   |   184 |
| A103  | 中川   |    17 |
| A101  | 佐藤   |   300 |
| A102  | 高桥   |   205 |
| A104  | 渡边   |    93 |
| A103  | 中川   |    12 |
| A105  | 西泽   |  NULL |
+-------+--------+-------+
10 rows in set (0.00 sec)
# 右外连接
mysql> select tb1.empid,tb1.name,tb3.sales from tb1 right join tb3 on tb1.empid=tb3.empid;
+-------+--------+-------+
| empid | name   | sales |
+-------+--------+-------+
| A101  | 佐藤   |   184 |
| A101  | 佐藤   |   300 |
| A102  | 高桥   |    54 |
| A102  | 高桥   |   205 |
| A103  | 中川   |   101 |
| A103  | 中川   |    17 |
| A103  | 中川   |    12 |
| A104  | 渡边   |   181 |
| A104  | 渡边   |    93 |
| NULL  | NULL   |    87 |
+-------+--------+-------+
10 rows in set (0.00 sec)

发现还有其他的连接啥的,这里暂时不深究哈。

where in(select)
select 查询的列 from 表名 where 列名 in (通过只查询select语句提取的列);
max

查询表tb3中列sales最大值的记录?

这个如何查询呢?

# 错误写法
# 只写MAX(sales)是不会计算出列sales的最大值的!
mysql> select * from tb3 where sales=max(sales);
ERROR 1111 (HY000): Invalid use of group function

看是合理,其实却显示异常。

查询最大的sales值的

mysql> select max(sales) from tb3;
+------------+
| max(sales) |
+------------
|        300 |
+------------+
1 row in set (0.06 sec)

如果要查询最大sales的记录信息,就需要使用where in进行组合

mysql> select * from tb3 where sales in (select max(sales) from tb3);
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A101  |   300 |     5 |
+-------+-------+-------+
1 row in set (0.00 sec)
avg

查询tb1中age大于等于平均年龄的记录

mysql> select * from tb1 where age >= (select avg(age) from tb1);
+-------+--------+------+
| empid | name   | age  |
+-------+--------+------+
| A101  | 佐藤   |   40 |
| A105  | 西泽   |   35 |
+-------+--------+------+

参考文章

  1. 《MySQL基础教程-西泽梦路》

© 版权声明
导航号,我的单页导航

暂无评论

暂无评论...