MySQL学习笔记四

目录

多表连接查询--有两种

一、内连接--inner join...on...

把两张表连接起来，不做任何设定，得到的是笛卡尔积--inner join

把两张表连接起来，按照某个字段连接在一起--inner join ... on ...

把两张表连接起来，按照某个字段连接在一起，按要求查询--inner join ... on ... where ...

二、外连接-- left join...on... 或 right join...on...

子查询

整理

查询出所有的玩具商品

查询出所有的玩具商品和文具商品

练习：

三重嵌套子查询

一个where+一个子链接表

 子链接大表

内连接嵌套的另一种写法：

---

多表连接查询--有两种

一、内连接--inner join...on...

内连接是sql中连接查询的一种;按照在意义层面相同的两个字段，连接两个表；

表和表的连接是乘的关系，如果不对字段进行设定，则得到的是笛卡尔积。

把两张表连接起来，不做任何设定，得到的是笛卡尔积--inner join

select * from commodity inner join commoditytype;

把两张表连接起来，按照某个字段连接在一起--inner join ... on ...

select * from commodity inner join commoditytype on c_type=ct_id;

把两张表连接起来，按照某个字段连接在一起，按要求查询--inner join ... on ... where ...

查询所有的玩具商品，需要连接【商品表】和【商品类型表】，从【商品类型表】中找到玩具的id再从【商品表】中取出；相当于先把两个表连接起来得到一个“新”表，从“新”表中按照“新”表中有的字段进行where条件筛选

select * from commodity 
inner join commoditytype on c_type=ct_id 
where ct_name='玩具';

二、外连接-- left join...on... 或 right join...on...

select * table_name left join join_table on...; 将左表定义为主表，显示数据时以左表为准，主表有数据就显示，如果主表没数据就不显示即使副表有这些数据也不显示；主表有的数据一定会显示出来，那么如果副表没有，mysql会用null来进行拼接；

select * table_name right join join_table on..

select * from commodity left join commoditytype on c_type=ct_id;
select * from commodity right join commodity on c_type=ct_id;

假设A、B是两个表也是两个集合，可以用集合的关系来理解内连接和外连接

A inner join B on .... ------  A∩B

A   left  join B on .... ------  A-B

A  right join B on .... ------  B-A

子查询

子查询就是查询语句的嵌套，把一个查询语句嵌套在where后，作为where的条件

查询结果为1个时，可以用=，查询结果为多个时，用in

select * from commodity 
where c_type=(select ct_id from commoditytype where ct_name='玩具');

select * from commodity 
where c_type in 
(select ct_id from commoditytype where ct_name='玩具' or ct_name='文具');

整理

查询出所有的玩具商品

#①
select ct_id from commoditytype where ct_name='玩具';
select * from commodity where c_type=1;
#子查询
select * from commodity 
where c_type=(select ct_id from commoditytype where ct_name='玩具');
#连接查询
select * from commodity inner join commoditytype on c_type=ct_id 
where ct_name='玩具';

查询出所有的玩具商品和文具商品

#①
select ct_id from commoditytype where ct_name='玩具' or ct_name='文具';
select * from commodity where c_type=1 or c_type=2;
#子查询
select * from commodity 
where c_type in 
(select ct_id from commoditytype where ct_name='玩具' or ct_name='文具');
#连接查询
select * from commodity inner join commoditytype on c_type=ct_id 
where ct_name='玩具' or ct_name='文具';

练习：

三重嵌套子查询

select c_name from commodity where c_id in 
(select o_cid from `order` where o_cuid in 
(select cu_id from customer where cu_name='刘德华'));

一个where+一个子链接表

select c_name from commodity where c_id in
(select o_cid from customer inner join `order` 
on cu_id=o_cuid where cu_name='刘德华');

 子链接大表

派生表需要给每个表取一个名字，取字段的时候要看是哪一张表的，a的还是c的？

#连接
select c.c_name from commodity as c 
inner join 
(select * from customer inner join `order` on cu_id=o_cuid) as a 
on c.c_id=a.o_cid;

#连接先取刘德华
select c.c_name from commodity as c 
inner join 
(select * from customer inner join `order` on cu_id=o_cuid where cu_name='刘德华') as a 
on c.c_id=a.o_cid;

#连接后取刘德华
select * from commodity as c 
inner join 
(select * from customer inner join `order` on cu_id=o_cuid ) as a 
on c.c_id=a.o_cid 
where a.cu_name='刘德华';

内连接嵌套的另一种写法：

连接字段的顺序乱没关系，但每个字段要点名出自于哪个表

 select *
 from commoditytype as ct, commodity as c,`order` as o,customer as cu
 where ct.ct_id=c.c_type and c.c_id=o.o_cid and o.o_cuid=cu.cu_id;

完成需求

select *
from commoditytype as ct, commodity as c,`order` as o,customer as cu
where ct.ct_id=c.c_type and c.c_id=o.o_cid and o.o_cuid=cu.cu_id and cu.cu_name='刘德华';

select c.c_name
from commoditytype as ct, commodity as c,`order` as o,customer as cu
where o.o_cuid=cu.cu_id and ct.ct_id=c.c_type and c.c_id=o.o_cid and cu.cu_name='张学友';

需求1：买过c_id=41号商品的有哪些人？

#单步查询
select o_cuid from `order` where o_cid=41;
select cu_name from customer where cu_id=o_cuid;

#子查询
select cu_name from customer where cu_id in (select o_cuid from `order` where o_cid=41);

#表连接查询
select cu.cu_name from customer as cu inner join (select * from commodity inner join `order` on c_id=o_cid) as a on cu.cu_id=a.o_cuid where o_cid=41;

#表连接查询二
select cu.cu_name
from customer as cu,`order` as o,commodity as c
where c.c_id=o.o_cid and o.o_cuid=cu.cu_id and o.o_cid=41;

需求2：买过c_id=41和c_id=22号商品的有哪些人？

分别查询出否买41和22有哪些人，利用内连接取交的特点形成新表；

查出买41的人o_cuid作为a表，查出买22的人o_cuid作为b表，inner join on把a和b连接起来，on的条件是两个o_cuid相等，即两个表中若出现了都相等的o_cuid，代表o_cuid为都买过41和22的人

mysql> select o_cuid from `order` where o_cid=22;
+--------+
| o_cuid |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

mysql> select o_cuid from `order` where o_cid=41;
+--------+
| o_cuid |
+--------+
|      3 |
|      4 |
+--------+
2 rows in set (0.02 sec)

mysql> select * from
    -> (select o_cuid from `order` where o_cid=41) as a,
    -> (select o_cuid from `order` where o_cid=22) as b;
+--------+--------+
| o_cuid | o_cuid |
+--------+--------+
|      3 |      4 |
|      4 |      4 |
+--------+--------+
2 rows in set (0.02 sec)


mysql> select a.o_cuid from
    -> (select o_cuid from `order` where o_cid=41) as a,
    -> (select o_cuid from `order` where o_cid=22) as b
    -> where a.o_cuid=b.o_cuid
    -> ;
+--------+
| o_cuid |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

其他写法

#内连接普通写法
select cu_name from customer where cu_id in
(
select a.o_cuid from 
(select o_cuid from `order` where o_cid=41) as a 
inner join 
(select o_cuid from `order` where o_cid=22) as b 
on a.o_cuid=b.o_cuid
);

#内连接另一种写法
select cu_name from customer where cu_id in 
(
select a.o_cuid from
(select o_cuid from `order` where o_cid=41) as a,
(select o_cuid from `order` where o_cid=22) as b 
where a.o_cuid=b.o_cuid
);

#查两遍，先查o_cid=22的o_cuid，从这里找到o_cid=41的a.o_cuid
select cu_name from customer where cu_id in
(
select a.o_cuid from (select o_cuid from `order` where o_cid=41) as a
where a.o_cuid in (select o_cuid from `order` where o_cid=22)
);

需求3：买过c_id=41或c_id=22号商品的有哪些人？

select cu_name from customer where cu_id in
(select o_cuid from `order` where o_cid=41 or o_cid=22);

需求4：有哪些商品从来没有被买过？

select c_id from commodity where c_id not in (select distinct o_cid from `order`);