MySQL 查询探索（三）多表查询

连接查询

#默认不写，内连接查询
#对于连接点，两表都有数据才显示
SELECT
  suppliers.s_id,
  s_name,
  f_name,
  f_price
FROM
  fruits,
  suppliers
WHERE
	fruits.s_id = suppliers.s_id;
#写：内链接
SELECT
  suppliers.s_id,
  s_name,
  f_name,
  f_price
FROM
  fruits
  INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;

#LEFT JOIN左连接（left outer join 的简写）
#对于连接点，若右边没有，则空值显示，右连接反之。
SELECT
  f_id,
  suppliers.s_id,
  s_name,
  f_name,
  f_price
FROM
  fruits
  LEFT JOIN suppliers ON fruits.s_id = suppliers.s_id;

子查询

CREATE table tbl1
(
  num1 INT NOT NULL
);
CREATE table tbl2
(
  num2 INT NOT NULL
);
INSERT INTO tbl1
values(1),
  (5),
  (13),
  (27);
INSERT INTO tbl2
values(6),
  (14),
  (11),
  (20);

and,all

#ANY
#返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何的1个值，即为符合查询条件的结果。
SELECT
  *
FROM
  tbl1
WHERE
	num1 > ANY ( SELECT num2
FROM tbl2 );

#等同于：
SELECT
  *
FROM
  tbl1
WHERE
	num1 > ( SELECT min( num2 )
FROM tbl2 );

#返回tbl1表中比tbl2表num2 列所有值都大的值：
SELECT
  *
FROM
  tbl1
WHERE
	num1 > ALL ( SELECT num2
FROM tbl2 );
#等同于：
SELECT
  *
FROM
  tbl1
WHERE
	num1 > ( SELECT max( num2 )
FROM tbl2 );

EXISTS

#查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录中s_id=107的值：
SELECT
  *
FROM
  fruits
WHERE
	s_id = 107
  AND EXISTS ( SELECT s_name
  FROM suppliers
  WHERE s_id = 107 );

#NOT EXISTS
#查询suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录：
SELECT *
FROM fruits
WHERE NOT EXISTS
     (SELECT s_name
FROM suppliers
WHERE s_id = 107);

#in
#在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id：
SELECT c_id
FROM orders
WHERE o_num IN
     (SELECT o_num
FROM orderitems
WHERE f_id = 'c0');
#not in
SELECT c_id
FROM orders
WHERE o_num
NOT IN

#UNION ALL：拼接多个独立的sql，一起返回。
#查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息：
  SELECT s_id, f_name, f_price
  FROM fruits
  WHERE f_price < 9.0
UNION ALL
  SELECT s_id, f_name, f_price
  FROM fruits
  WHERE s_id IN(101,103);

#查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这两个字段值，并取列别名为suppliers_title。
SELECT
  CONCAT( TRIM( s_name ), ' (', TRIM( s_city ), ')' ) AS suppliers_title
FROM
  suppliers
ORDER BY
	s_name;
	```