热门
立即入驻

《MySQL基础教程》笔记7

Web1年前发布 biumall.com站长
202 00
目录

前言

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

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

正文

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

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

biumall.com
  1. D:\phpstudy_pro\Extensions\MySQL5.7.26\bin
复制

进入正文

创建tb1和tb2

biumall.com
  1. create table tb1 (empid varchar(30), name varchar(30), age int);
  2. create table tb2 (empid varchar(30), name varchar(30), age int);
复制
biumall.com
  1. # tb1
  2. mysql> select * from tb1;
  3. +-------+--------+------+
  4. | empid | name   | age  |
  5. +-------+--------+------+
  6. | A101  | 佐藤   |   40 |
  7. | A102  | 高桥   |   28 |
  8. | A103  | 中川   |   20 |
  9. | A104  | 渡边   |   23 |
  10. | A105  | 西泽   |   35 |
  11. +-------+--------+------+
  12. # tb2
  13. mysql> select * from tb2;
  14. +-------+--------+------+
  15. | empid | name   | age  |
  16. +-------+--------+------+
  17. | A106  | 中天   |   26 |
  18. | A107  | 铃木   |   24 |
  19. | A108  | 村警   |   25 |
  20. | A110  | 几天   |   27 |
  21. | A109  | 田中   |   23 |
  22. +-------+--------+------+
复制

tb3

biumall.com
  1. create table tb3 (empid varchar(30),sales int,month int);
复制
biumall.com
  1. # tb3
  2. mysql> select * from tb3;
  3. +-------+-------+-------+
  4. | empid | sales | month |
  5. +-------+-------+-------+
  6. | A103  |   101 |     4 |
  7. | A102  |    54 |     5 |
  8. | A104  |   181 |     4 |
  9. | A101  |   184 |     4 |
  10. | A103  |    17 |     5 |
  11. | A101  |   300 |     5 |
  12. | A102  |   205 |     6 |
  13. | A104  |    93 |     5 |
  14. | A103  |    12 |     6 |
  15. | A107  |    87 |     6 |
  16. +-------+-------+-------+
复制

进入正题。

union

使用union可以组合两个表。

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

biumall.com
  1. select 列名1 from 表名1 union select 列表2 from 表名2;
复制

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

biumall.com
  1. mysql> select * from tb1 union select * from tb2;
  2. +-------+--------+------+
  3. | empid | name   | age  |
  4. +-------+--------+------+
  5. | A101  | 佐藤   |   40 |
  6. | A102  | 高桥   |   28 |
  7. | A103  | 中川   |   20 |
  8. | A104  | 渡边   |   23 |
  9. | A105  | 西泽   |   35 |
  10. | A106  | 中天   |   26 |
  11. | A107  | 铃木   |   24 |
  12. | A108  | 村警   |   25 |
  13. | A110  | 几天   |   27 |
  14. | A109  | 田中   |   23 |
  15. +-------+--------+------+
复制

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

biumall.com
  1. # 把组合的tb1和tb2放入创建的tb3中
  2. create table tb3 select * from tb1 union select * from tb2;
复制

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

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

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

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

biumall.com
  1. mysql> select * from tb1 union all select * from tb2;
  2. +-------+--------+------+
  3. | empid | name   | age  |
  4. +-------+--------+------+
  5. | A101  | 佐藤   |   40 |
  6. | A102  | 高桥   |   28 |
  7. | A103  | 中川   |   20 |
  8. | A104  | 渡边   |   23 |
  9. | A105  | 西泽   |   35 |
  10. | A106  | 中天   |   26 |
  11. | A107  | 铃木   |   24 |
  12. | A108  | 村警   |   25 |
  13. | A110  | 几天   |   27 |
  14. | A109  | 田中   |   23 |
  15. | A101  | 佐藤   |   40 |
  16. +-------+--------+------+
复制

JOIN

使用JOIN连接两个表。

biumall.com
  1. select 查询列名 from 表名1 join 表名2 on 表名1.列名=表名2.列名;
复制

我们以tb1和tb3连接。

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

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

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

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

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

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

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

biumall.com
  1. mysql> select tb1.empid,tb1.name,tb3.sales from tb1 join tb3 on tb1.empid=tb3.empid;
  2. +-------+--------+-------+
  3. | empid | name   | sales |
  4. +-------+--------+-------+
  5. | A103  | 中川   |   101 |
  6. | A102  | 高桥   |    54 |
  7. | A104  | 渡边   |   181 |
  8. | A101  | 佐藤   |   184 |
  9. | A103  | 中川   |    17 |
  10. | A101  | 佐藤   |   300 |
  11. | A102  | 高桥   |   205 |
  12. | A104  | 渡边   |    93 |
  13. | A103  | 中川   |    12 |
  14. +-------+--------+-------+
复制
给表添加别名的方法

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

biumall.com
  1. # table1是tb1的别名，当然，这种别名执行完就消失了，并没有啥作用。
  2. select * from tb1 as table1;
复制

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

biumall.com
  1. 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;
  2. +-------+--------+-------+
  3. | empid | name   | sales |
  4. +-------+--------+-------+
  5. | A103  | 中川   |   101 |
  6. | A102  | 高桥   |    54 |
  7. | A104  | 渡边   |   181 |
  8. | A101  | 佐藤   |   184 |
  9. | A103  | 中川   |    17 |
  10. | A101  | 佐藤   |   300 |
  11. | A102  | 高桥   |   205 |
  12. | A104  | 渡边   |    93 |
  13. | A103  | 中川   |    12 |
  14. +-------+--------+-------+
  15. 9 rows in set (0.00 sec)
复制

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

using

上面join on

biumall.com
  1. select 查询列名 from 表名1 join 表名2 on 表名1.列名=表名2.列名;
复制

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

biumall.com
  1. select * from tb1 join tb3 on tb1.empid=tb3.empid;
复制

比较的列名都是empid。

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

biumall.com
  1. select 显示的列名 from 表1 join 表2 using(empid);
复制

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

来个例子

biumall.com
  1. mysql> select tb1.empid,tb1.name,tb3.sales from tb1 join tb3 using(empid);
  2. +-------+--------+-------+
  3. | empid | name   | sales |
  4. +-------+--------+-------+
  5. | A103  | 中川   |   101 |
  6. | A102  | 高桥   |    54 |
  7. | A104  | 渡边   |   181 |
  8. | A101  | 佐藤   |   184 |
  9. | A103  | 中川   |    17 |
  10. | A101  | 佐藤   |   300 |
  11. | A102  | 高桥   |   205 |
  12. | A104  | 渡边   |    93 |
  13. | A103  | 中川   |    12 |
  14. +-------+--------+-------+
复制
通过WHERE设置条件从连接表中提取记录

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

biumall.com
  1. mysql> select tb1.empid,tb1.name,tb3.sales from tb1 join tb3 using(empid) where tb3.sales>=100;
  2. +-------+--------+-------+
  3. | empid | name   | sales |
  4. +-------+--------+-------+
  5. | A101  | 佐藤   |   184 |
  6. | A101  | 佐藤   |   300 |
  7. | A102  | 高桥   |   205 |
  8. | A103  | 中川   |   101 |
  9. | A104  | 渡边   |   181 |
  10. +-------+--------+-------+
  11. 5 rows in set (0.00 sec)
复制

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

外连接

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

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

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

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

内连接 : join 或inner join

左外连接 : left join

右外连接 : right join

biumall.com
  1. # 左外连接
  2. mysql> select tb1.empid,tb1.name,tb3.sales from tb1 left join tb3 on tb1.empid=tb3.empid;
  3. +-------+--------+-------+
  4. | empid | name   | sales |
  5. +-------+--------+-------+
  6. | A103  | 中川   |   101 |
  7. | A102  | 高桥   |    54 |
  8. | A104  | 渡边   |   181 |
  9. | A101  | 佐藤   |   184 |
  10. | A103  | 中川   |    17 |
  11. | A101  | 佐藤   |   300 |
  12. | A102  | 高桥   |   205 |
  13. | A104  | 渡边   |    93 |
  14. | A103  | 中川   |    12 |
  15. | A105  | 西泽   |  NULL |
  16. +-------+--------+-------+
  17. 10 rows in set (0.00 sec)
  18. # 右外连接
  19. mysql> select tb1.empid,tb1.name,tb3.sales from tb1 right join tb3 on tb1.empid=tb3.empid;
  20. +-------+--------+-------+
  21. | empid | name   | sales |
  22. +-------+--------+-------+
  23. | A101  | 佐藤   |   184 |
  24. | A101  | 佐藤   |   300 |
  25. | A102  | 高桥   |    54 |
  26. | A102  | 高桥   |   205 |
  27. | A103  | 中川   |   101 |
  28. | A103  | 中川   |    17 |
  29. | A103  | 中川   |    12 |
  30. | A104  | 渡边   |   181 |
  31. | A104  | 渡边   |    93 |
  32. | NULL  | NULL   |    87 |
  33. +-------+--------+-------+
  34. 10 rows in set (0.00 sec)
复制

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

where in(select)
biumall.com
  1. select 查询的列 from 表名 where 列名 in (通过只查询select语句提取的列);
复制
max

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

这个如何查询呢？

biumall.com
  1. # 错误写法
  2. # 只写MAX(sales)是不会计算出列sales的最大值的！
  3. mysql> select * from tb3 where sales=max(sales);
  4. ERROR 1111 (HY000): Invalid use of group function
复制

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

查询最大的sales值的

biumall.com
  1. mysql> select max(sales) from tb3;
  2. +------------+
  3. | max(sales) |
  4. +------------
  5. |        300 |
  6. +------------+
  7. 1 row in set (0.06 sec)
复制

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

biumall.com
  1. mysql> select * from tb3 where sales in (select max(sales) from tb3);
  2. +-------+-------+-------+
  3. | empid | sales | month |
  4. +-------+-------+-------+
  5. | A101  |   300 |     5 |
  6. +-------+-------+-------+
  7. 1 row in set (0.00 sec)
复制
avg

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

biumall.com
  1. mysql> select * from tb1 where age >= (select avg(age) from tb1);
  2. +-------+--------+------+
  3. | empid | name   | age  |
  4. +-------+--------+------+
  5. | A101  | 佐藤   |   40 |
  6. | A105  | 西泽   |   35 |
  7. +-------+--------+------+
复制

参考文章

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

 历史上的今天

  1. 《《MySQL基础教程》笔记7》
  2. 《泡面菌》
  3. 《萌研社》
  4. 《C3动漫网》
  5. 《翼萌网》
  6. 《呲哩呲哩》
  7. 《C站》
  8. 《TopACG》
  9. 《亿星云》
© 版权声明
1、 名称： 笔友城堡
2、 网址： https://www.biumall.com/
3、 文章： 部分来源于网络，仅供站长学习，如有侵权，请留言
4、 打赏： 如果觉得不错，赏杯可乐，谢谢赏杯可乐
添加小工具
点此为“正文侧边栏”添加小工具

相关文章

暂无评论

none
暂无评论...

分类大全