小渣渣学习笔记 python day57【MySql 多表操作】

多表联接

• 笛卡尔积

  select * from table1,table2 -- 显示的记录数是table1 和table2记录数量的积 
  

• 内连接

  select * from table1 inner join table2 on 条件 ; --把不匹配条件的都过滤掉 【相当于where字句】
  

• 左外连接

  select * from table1 left join table2 on 条件; -- table1的所有记录都显示，对应table2的没有记录就置null
  

• 右外连接

  select * from table1 right join table2 on 条件;  --table2全显示
  

• 【测试】 如果有2个表t_product 产品表 和 t_price 价格表，列出所有产品名，最新日期，和响应的价格

  create table t_product(product_id int primary key,name char(20));
  create table t_price(id int primary key,dt datetime,price float(8,2),product_id int);
   
  insert into t_product values(1,'扳手'),(2,'螺丝刀'),(3,'钳子');
  insert into  t_price values(1,'2019-08-11 10:00:00',25,1),(2,'2019-12-11 10:00:00',26.8,1),(3,'2019-10-11 10:00:00',18,2),(4,'2020-05-11 10:00:00',17,2),(5,'2020-06-21 10:50:00',19,2),(6,'2020-04-21 11:50:00',19,3);
  

  mysql> select * from t_product;
  +------------+-----------+
  | product_id | name      |
  +------------+-----------+
  |          1 | 扳手      |
  |          2 | 螺丝刀    |
  |          3 | 钳子      |
  +------------+-----------+
  
  mysql> select * from t_price;
  +----+---------------------+-------+------------+
  | id | dt                  | price | product_id |
  +----+---------------------+-------+------------+
  |  1 | 2019-08-11 10:00:00 | 25.00 |          1 |
  |  2 | 2019-12-11 10:00:00 | 26.80 |          1 |
  |  3 | 2019-10-11 10:00:00 | 18.00 |          2 |
  |  4 | 2020-05-11 10:00:00 | 17.00 |          2 |
  |  5 | 2020-06-21 10:50:00 | 19.00 |          2 |
  |  6 | 2020-04-21 11:50:00 | 19.00 |          3 |
  +----+---------------------+-------+------------+
  
  

  【方法一】mysql> select b.name,a.mydt,a.price from
  (select pid,mydt,d.price from 
   	(select product_id as pid,max(dt) as mydt from t_price group by product_id) as c 	 left join t_price as d on c.pid = d.product_id and c.mydt = d.dt) a 
   	left join t_product b on a.pid = b.product_id; --嵌套了3层select 你们有没有更简洁的办法？
  +-----------+---------------------+-------+
  | name      | mydt                | price |
  +-----------+---------------------+-------+
  | 扳手      | 2019-12-11 10:00:00 | 26.80 |
  | 螺丝刀    | 2020-06-21 10:50:00 | 19.00 |
  | 钳子      | 2020-04-21 11:50:00 | 19.00 |
  +-----------+---------------------+-------+
  

  【方法二】mysql> select t_product.name,a.dt mydt,a.price from (select * from t_price order by product_id,dt desc) a inner join t_product on a.product_id = t_product.product_id group by t_product.name ; --或者先把t_price按照product_id排序，然后按日期时间 dt 倒叙排序，然后对产品表t_product 做内连接，并分组group by 分组会取最上面的那个
  +-----------+---------------------+-------+
  | name      | mydt                | price |
  +-----------+---------------------+-------+
  | 扳手      | 2019-12-11 10:00:00 | 26.80 |
  | 螺丝刀    | 2020-06-21 10:50:00 | 19.00 |
  | 钳子      | 2020-04-21 11:50:00 | 19.00 |
  +-----------+---------------------+-------+