前言
记录于此，方便自己回忆。
正文
我这以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 | +-------+--------+------+
参考文章
历史上的今天
- 2019: 《陈忠实:马罗大叔(0条评论)》
- 2018: 《linux禁止root远程登录(0条评论)》
- 2018: 《替换preg_replace(0条评论)》
相关文章
暂无评论...
91易搜，阅读是一种生活方式。收录新闻、小说和电影等优秀网址，提供安全的、绿色的和无广告的导航服务。高品质生活，从91es.com开始!
粤公网安备44030002002531号